spring-mvc - 运行 JPA 查询的 SQL 语句中的语法错误

Question

我在 Spring MvC 项目中有这个 JPA 查询

  @Query(value = "with\n"
        + " jupiter_courante ( \n"
        + "             jupiter_id\n"
        + ")"
        + "as (\n"
        + " select pa.jupiter_id from\n"
        + "jupiter_microfusa pa\n"
        + "inner join jupiter_composition pc on pa.JUPITER_ID = pc.JUPITER_ID\n"
        + "inner join jupiter_composition_type pct on pc.TYPEJUPITERCOMPOSITION_ID = pct.TYPEJUPITERCOMPOSITION_ID\n"
        + "inner join donnees_contact dc on pc.microfusa_id = dc.microfusa_Id\n"
        + "inner join donnees_contact_type dct on dc.TYPE_DONNEE_CONTACT_ID = dct.TYPE_DONNEE_CONTACT_ID\n"
        + "where pct.code = 'kimi'\n"
        + "),\n"
        + "aquarius_du_menage_courant as (\n"
        + "select microfusa_id from jupiter_composition ac\n"
        + "inner join jupiter_composition_type act on ac.TYPEJUPITERCOMPOSITION_ID = act.TYPEJUPITERCOMPOSITION_ID\n"
        + "where jupiter_id = (select jupiter_id from jupiter_courante)\n"
        + "),\n"
        + "parents_du_menage_courant as (\n"
        + "select microfusa_id from jupiter_composition ac\n"
        + "inner join jupiter_composition_type act on ac.TYPEJUPITERCOMPOSITION_ID = act.TYPEJUPITERCOMPOSITION_ID\n"
        + "where jupiter_id = (select jupiter_id from jupiter_courante)\n"
        + "and act.code in ('oko', 'aqa')\n"
        + "),\n"
        + "toutes_autorisations as\n"
        + "(\n"
        + "select aut.dt_debut, aut.dt_Fin, aut.AQUARIUS_ID, aut.autorisation_id, aut_p.*, decode(aut_ty.desc_long, 'interdiction', 1, 0) as interdiction from autorisation aut\n"
        + "inner join autorisation_microfusa aut_p on aut.AUTORISATION_PERSONNE_ID = aut_p.AUTORISATION_PERSONNE_ID\n"
        + "inner join autorisation_type aut_ty on aut.autorisation_type_id = aut_ty.autorisation_type_id\n"
        + "),\n"
        + "phone as (\n"
        + "select microfusa_id, contenu as telephone from (\n"
        + "select dc.microfusa_id, dc.contenu, row_number() over (partition by microfusa_id order by (case when code = 'W' then 1 when code = 'ER' then 2 when code = 'FG' then 3 when code = 'TES' then 4 else 10 end) ) rown from donnees_contact dc\n"
        + "inner join donnees_contact_type dct on dc.TYPE_DONNEE_CONTACT_ID = dct.TYPE_DONNEE_CONTACT_ID\n"
        + "where microfusa_id in (select microfusa_id from parents_du_menage_courant)\n"
        + "and dct.code in ('AW', 'GSSM','TMNB', 'TPOL')\n"
        + ") where rown = 1\n"
        + "),\n"
        + "email as (\n"
        + "select microfusa_id, contenu as email from (\n"
        + "select dc.microfusa_id, dc.CONTENU, dct.CODE, row_number() over (partition by microfusa_id order by (case when code = 'EMAIL' then 1 when code = 'EMAIL PRIVE' then 2 else 10 end) ) rown from donnees_contact dc\n"
        + "inner join donnees_contact_type dct on dc.TYPE_DONNEE_CONTACT_ID = dct.TYPE_DONNEE_CONTACT_ID\n"
        + "where microfusa_id in (select microfusa_id from parents_du_menage_courant)\n"
        + "and dct.code in ('EMAIL', 'EMAIL2')\n"
        + ") where rown = 1\n"
        + ")\n"
        + " \n"
        + "select * from\n"
        + "(\n"
        + "select distinct p.microfusa_Id as aquarius_id, p.nom as aquarius_nom, p.prenom as aquarius_prenom , tas.nom as authorised_nom\n"
        + ", tas.prenom as authorised_prenom , tas.interdiction as IPerAut_interdiction, tas.telephone, tas.email, tas.AUTORISATION_PERSONNE_ID as microfusaautoriseeID, 1 as modifiable\n"
        + ", tas.interdiction as IPerAutEx_interdiction, tas.dt_debut, tas.dt_Fin, tas.autorisation_id as autorisationID\n"
        + "from aquarius_du_menage_courant emc\n"
        + "inner join microfusa p on emc.microfusa_id = p.microfusa_id\n"
        + "inner join toutes_autorisations tas on p.microfusa_Id = tas.aquarius_id\n"
        + " \n"
        + "union\n"
        + " \n"
        + "select distinct p.microfusa_Id as aquarius_id, p.nom as aquarius_nom, p.prenom as aquarius_prenom , par.nom as authorised_nom\n"
        + ", par.prenom as authorised_prenom , 0 as IPerAut_interdiction, par.telephone, par.email, null as microfusaautoriseeID, 0 as modifiable\n"
        + ", 0 as IPerAutEx_interdiction, null as dt_debut, null as dt_Fin, null as autorisationID\n"
        + "from aquarius_du_menage_courant emc\n"
        + "inner join microfusa p on emc.microfusa_id = p.microfusa_id\n"
        + "inner join (\n"
        + "select p.NOM, p.PRENOM, ph.telephone, e.* from microfusa from microfusa p\n"
        + "left outer join phone ph on p.microfusa_id = ph.microfusa_id\n"
        + "left outer join email e on p.microfusa_id = e.microfusa_id\n"
        + "where p.microfusa_id in (select * from parents_du_menage_courant)\n"
        + ") par on 1 = 1\n"
        + ")\n"
        + "order by aquarius_id;")

但是当我在测试中运行查询时出现此错误，但没有消息：

EL Warning]: 2020-10-17 20:16:38.297--UnitOfWork(522173599)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "with....


at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.getSyntaxError(DbException.java:243)
at org.h2.command.Parser.getSyntaxError(Parser.java:1053)
at org.h2.command.Parser.read(Parser.java:4995)
at org.h2.command.Parser.readTableFilter(Parser.java:1893)
at org.h2.command.Parser.readJoin(Parser.java:2412)
at org.h2.command.Parser.parseJoinTableFilter(Parser.java:2839)
at org.h2.command.Parser.parseSelectFromPart(Parser.java:2828)
at org.h2.command.Parser.parseSelect(Parser.java:2959)
at org.h2.command.Parser.parseQuerySub(Parser.java:2817)
at org.h2.command.Parser.parseSelectUnion(Parser.java:2666)
at org.h2.command.Parser.readTableFilter(Parser.java:1892)
at org.h2.command.Parser.parseSelectFromPart(Parser.java:2827)
at org.h2.command.Parser.parseSelect(Parser.java:2959)
at org.h2.command.Parser.parseQuerySub(Parser.java:2817)
at org.h2.command.Parser.parseSelectUnion(Parser.java:2649)
at org.h2.command.Parser.parseWithQuery(Parser.java:6800)
at org.h2.command.Parser.parseWith1(Parser.java:6752)
at org.h2.command.Parser.parseWith(Parser.java:6722)
at org.h2.command.Parser.parseWithStatementOrQuery(Parser.java:2633)
at org.h2.command.Parser.parsePrepared(Parser.java:872)
at org.h2.command.Parser.parse(Parser.java:843)
at org.h2.command.Parser.parse(Parser.java:819)
at org.h2.command.Parser.prepareCommand(Parser.java:738)
at org.h2.engine.Session.prepareLocal(Session.java:657)
at org.h2.engine.Session.prepareCommand(Session.java:595)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:352)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1595)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1544)
at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:806)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:628)
... 85 more

标签： spring-mvcjpaeclipselinkh2dbunit

Answer 1

你有一个错字

+ "and dct.code in ('EMAIL', 'EMAIL2)\n"

它应该是

+ "and dct.code in ('EMAIL', 'EMAIL2')\n"

可能还有其他问题，但您需要发布完整的错误消息，在您的问题中它被截断并且几乎没有用。例如，您可以尝试直接使用 JDBC 来执行这个查询（有固定的错字）。

---

在编辑后的问题中，您还有另一个错误。from microfusa from microfusa p应该是from microfusa p。