java - 无法从 Hibernate Native Query 的结果集中读取列值

Question

 SELECT
  CONCAT(CREG.FIRSTNAME, ' ', CREG.LASTNAME) AS NAME,
  CASE WHEN CR.CAMPAIGNTYPE = 'NPS'
    THEN NPSSCORE
  ELSE CSATSCORE END                         AS SCORE,
  IFNULL(cast(CC.TEXT AS CHAR(255)), '')     AS COMMENTS,
  CREG.ID                                    AS CLIENTID,
  CR.ID                                      AS CAMPAIGNRESPONSEID,
  CI.ID                                      AS ISSUEID
FROM CUSTOMER_ISSUES CI INNER JOIN (SELECT DISTINCT ISSUEID
                                    FROM ISSUE_DEPARTMENT_MAPPING
                                    WHERE CUSTOMERUSERID = 91 AND ISSUE_STATUS = 'New') IDM
    ON CAST(CI.FEEDBACK_DATE AS DATE) BETWEEN '2016-06-05' AND '2016-06-11' AND IDM.ISSUEID = CI.ID
  INNER JOIN CAMPAIGN_RESPONSE CR ON CR.ID = CI.CAMPAIGN_RESPONSE_ID
  INNER JOIN CLIENT_REGISTRATION CREG ON CREG.ID = CR.RESPONSECUSTOMERID
  LEFT OUTER JOIN CAMPAIGN_COMMENTS CC ON CC.CAMPAIGN_RESPONSE_ID = CR.ID;

上面的查询在mysql控制台中正常运行，但是当我与Hibernate集成时，Hibernate抛出以下错误。

[BigIntegerType] 无法从结果集中读取列值:ID；未找到列“ID”。

Answer 1

尝试删除 SQL 查询中的别名。

基本上，这里发生的事情是当你运行时

SELECT
  CONCAT(CREG.FIRSTNAME, ' ', CREG.LASTNAME) AS NAME,
  CASE WHEN CR.CAMPAIGNTYPE = 'NPS'
    THEN NPSSCORE
  ELSE CSATSCORE END                         AS SCORE,
  IFNULL(cast(CC.TEXT AS CHAR(255)), '')     AS COMMENTS,
  CREG.ID                                    AS CLIENTID,
  CR.ID                                      AS CAMPAIGNRESPONSEID,
  CI.ID                                      AS ISSUEID

在 JDBC 中，它将列返回为 CREG.ID，而不是 ClientID。因此，请尝试在没有别名的情况下运行查询，通常情况下，JDBC 中存在问题。如果您仍然坚持使用别名，请将以下条目添加到配置文件中的 JDBC URL

[useOldAliasMetadataBehavior=true]