时间戳

首页 > 解决方案 > 从 SQL 转换为 JPA 查询

java - 从 SQL 转换为 JPA 查询

问题描述

我有这个 SQL 查询

SELECT 'variable'                CATEGORY,
       'Referral Received in '    VARIABLE,
       SUM (OPD)                 OPD,
       SUM (IPD)                 IPD,
       SUM (DC)                  DAYCARE,
       SUM (PROC)                PROCEDURE,
       SUM (SUR)                 SURGERY,
         SUM (NVL (OPD, 0))
       + SUM (NVL (IPD, 0))
       + SUM (NVL (DC, 0))
       + SUM (NVL (PROC, 0))
       + SUM (NVL (SUR, 0))      AS TOTAL
  FROM (SELECT CASE WHEN B.ID = 1 THEN 1 END     AS OPD,
               CASE WHEN B.ID = 2 THEN 1 END     AS IPD,
               CASE WHEN B.ID = 3 THEN 1 END     AS DC,
               CASE WHEN B.ID = 4 THEN 1 END     AS PROC,
               CASE WHEN B.ID = 5 THEN 1 END     AS SUR
          FROM REF_TB_APP_TRANSACTIONS A, REF_VW_VISIT_TYPE B
         WHERE REQ_VISIT_TYPE = B.ID AND A.TO_EST_CODE = 20068)

我想将它转换为 JPA 查询我试过了，但它给了我很多错误（这是我第一次写 JPA 查询）

SELECT 'variable'                CATEGORY," + 
            "                 'Referral Received in '    VARIABLE," + 
            "                 SUM (OPD)                 OPD," + 
            "                 SUM (IPD)                 IPD," + 
            "                 SUM (DC)                  DAYCARE," + 
            "                 SUM (PROC)                PROCEDURE," + 
            "                 SUM (SUR)                 SURGERY," + 
            "                    SUM (NVL (OPD, 0))" + 
            "                 + SUM (NVL (IPD, 0))" + 
            "                  + SUM (NVL (DC, 0))" + 
            "                  + SUM (NVL (PROC, 0))" + 
            "                  + SUM (NVL (SUR, 0))      AS TOTAL" + 
            "             FROM (SELECT CASE WHEN B.id = 1 THEN 1 END     AS OPD," + 
            "                          CASE WHEN B.id = 2 THEN 1 END     AS IPD," + 
            "                          CASE WHEN B.id = 3 THEN 1 END     AS DC," + 
            "                         CASE WHEN B.id = 4 THEN 1 END     AS PROC," + 
            "                          CASE WHEN B.id = 5 THEN 1 END     AS SUR" + 
            "                    FROM model.central.RefTbAppTransaction A, model.central.RefVwVisitType B" + 
            "                   WHERE A.reqVisitType.id = B.id AND A.toEstCode = :toEstCode)

这两个实体RefTbAppTransaction和RefVwVisitType

如果有任何帮助，这是一个错误

[ERROR] 2021-03-31 11:02:07 [] ErrorTracker - line 1:1016: unexpected token: CASE
[ERROR] 2021-03-31 11:02:07 [] ErrorTracker - line 1:1016: unexpected token: CASE
antlr.NoViableAltException: unexpected token: CASE
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1519) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1363) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1063) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:748) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:319) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]

这另一个

[ERROR] 2021-03-31 11:02:07 [] ErrorTracker - line 1:1284: expecting EOF, found ')'
[ERROR] 2021-03-31 11:02:07 [] ErrorTracker - line 1:1284: expecting EOF, found ')'
antlr.MismatchedTokenException: expecting EOF, found ')'
    at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar:?]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:215) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:290) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:155) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:600) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:709) ~[hibernate-core-5.4.8.Final.jar:5.4.8.Final]

我认为这是语法错误或 JPA 中未定义的某些关键字

标签： javasqlspring-bootspring-mvcjpa

解决方案

从您的堆栈跟踪来看，您的查询似乎被解释为 hql（请参阅 参考资料HqlBaseParser）。如果你想写 sql 而不是 hql 你需要使用createNativeQuery而不是createQuery.

请参阅https://vladmihalcea.com/the-jpa-entitymanager-createnativequery-is-a-magic-wand/了解如何使用createNativeQuery.

推荐阅读