java - eclipselink 出现 MySQL 语法错误异常

Question

当创建包含联合的查询时，我将 jpa eclipselink 与 mysql 5.7 数据库一起使用，出现错误

Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))' at line 1

查询代码在哪里

EntityManager entityManager = DataMain.createEntityManager();

    String queryString = "";

    queryString = "" +
        "select " +
        "   PatientTable.id, PatientTable.fullName " +
        "from " +
        "   PatientTable as PatientTable " +
        "UNION ALL " +
        "select " +
        "   PatientTable.id, PatientTable.fullName " +
        "from " +
        "   PatientTable as PatientTable";


    Query query = entityManager.createQuery(queryString);
    query.getResultList().forEach(System.out::println);

完整的错误堆栈

Thu Apr 06 14:34:56 EDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Exception in thread "main" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))' at line 1
Error Code: 1064
Call: (SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))
Query: ReportQuery(referenceClass=PatientTable sql="(SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
    at com.tawaaq.app.data.persistence.query.patient.PatientQuery.main(PatientQuery.java:49)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))' at line 1
Error Code: 1064
Call: (SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))
Query: ReportQuery(referenceClass=PatientTable sql="(SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:250)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2675)
    at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:848)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
    at org.eclipse.persistence.tools.profiler.PerformanceProfiler.profileExecutionOfQuery(PerformanceProfiler.java:426)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1802)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
    ... 2 more

Answer 1

我只能使用 Oracle 重新创建它，因为我现在没有 MySql 实例。

当我在分配别名时删除 as 时，它起作用了。

使用您的示例:

queryString = "" +
    "select " +
    "   PatientTable.id, PatientTable.fullName " +
    "from " +
    "   PatientTable PatientTable " +
    "UNION ALL " +
    "select " +
    "   PatientTable.id, PatientTable.fullName " +
    "from " +
    "   PatientTable PatientTable";

顺便说一下，您联合的两个语句完全相同，这是有意的吗？

编辑:Nvm，根据此线程 JPA 不支持联合: Union in JPA query - from the same table

在此线程中描述了一种解决方法: JPQL equivalent of SQL query using unions and selecting constants