java - 通过合并CASE和LIMIT的MySQL语法错误(仅在某些情况下)-由JPA生成

我正在使用Java EE上下文中的带有MySQL数据库和JPA的JSF Web应用程序。

我正在使用以下JPQL从数据库中获取一些数据。 EntityGenerierteAufgabe具有字段createTime和changeTime。写入数据库条目时设置CreateTime。在这种情况下，changeTime为NULL。数据库条目更新时，还将设置changeTime。现在，我想从该表中选择并按changeTime（如果已设置）对条目进行排序，否则按createTime进行排序。而且我想限制结果的数量。

String query = "SELECT ga FROM EntityGenerierteAufgabe ga "
        + "WHERE ga.benutzer=:benutzer "
        + "ORDER BY CASE WHEN (ga.changeTime > ga.createTime) THEN ga.changeTime ELSE ga.createTime END DESC";
aufgaben = em.createQuery(query)
        .setParameter("benutzer", benutzer)
        .setMaxResults(6)
        .getResultList();

此JPQL对于一个用户（BrowserSession）正常工作。如果我使用jMeter做一些压力测试，并且使用多个线程（多个BrowserSession）并调用JSF-Page（调用此JPQL），则多次会导致SOMETIMES出现以下错误：

Call: SELECT ID AS a1, BEARBEITUNGSZEIT AS a2, CHANGETIME AS a3, CREATETIME AS a4, FORTSCHRITT AS a5, ISTBEENDET AS a6, AUFGABENTYP_ID AS a7, LEVEL_ID AS a8, BENUTZER_ID AS a9 FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = ?) ORDER BY CASE  WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN  DESC LIMIT ?, ?
bind => [3 parameters bound]
Query: ReadAllQuery(referenceClass=EntityGenerierteAufgabe sql="SELECT ID AS a1, BEARBEITUNGSZEIT AS a2, CHANGETIME AS a3, CREATETIME AS a4, FORTSCHRITT AS a5, ISTBEENDET AS a6, AUFGABENTYP_ID AS a7, LEVEL_ID AS a8, BENUTZER_ID AS a9 FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = ?) ORDER BY CASE  WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN  DESC LIMIT ?, ?")
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.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.querie
Information:   s.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168)
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.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
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)
... 124 more
Caused by: 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 'DESC LIMIT 0, 6' at line 1
at sun.reflect.GeneratedConstructorAccessor204.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2030)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1007)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642)
... 144 more

如您所见，SQL查询的构建不正确。内部错误：ORDER BY CASE何时（CHANGETIME> CREATETIME）则CHANGETIME何时CREATETIME THEN DESC LIMIT？

这很奇怪，因为它有时会工作，但有时却无法工作。

一些额外的信息：
我正在使用JSF 2.2。实例化相关的ViewController时，将调用带有JPQL的方法：

@javax.faces.view.ViewScoped
@Named
public class ControllerErfolgskontrolle implements Serializable {

  @Inject
  private SvEnGenerierteAufgabe svEnGenerierteAufgabe;

  private List<EntityGenerierteAufgabe> generierteAufgabenBenutzer;

  @PostConstruct
  private void doInit() {
    generierteAufgabenBenutzer = svEnGenerierteAufgabe.getByLimitedSorted(
        getControllerSession().getBenutzer());
  }

  ...
}

以及相关的SvEnGenerierteAufgabe-Class和创建JPQL的方法：

@Stateless
public class SvEnGenerierteAufgabe {

  @PersistenceContext
  EntityManager em;

  public List<EntityGenerierteAufgabe> getByLimitedSorted(EntityBenutzer benutzer) {

    List<EntityGenerierteAufgabe> aufgaben = new ArrayList<>();
    String query = "SELECT ga FROM EntityGenerierteAufgabe ga "
            + "WHERE ga.benutzer=:benutzer "
            + "ORDER BY CASE WHEN (ga.changeTime > ga.createTime) THEN ga.changeTime ELSE ga.createTime END DESC";
    aufgaben = em.createQuery(query)
            .setParameter("benutzer", benutzer)
            .setMaxResults(6)
            .getResultList();

    return aufgaben;
  }
}

我正在使用以下版本：
-Glassfish 4.1（内部版本13）
-Eclipse Persistence Services-2.5.2.v20140319-9ad6abd
-MySQL版本：5.5.27
-mysql-connector-java-5.1.34

当我未设置限制（setMaxResults（...））或删除订单时，我可以使用来自不同jMeter-Test-Threads的多个调用来工作。两者在某些情况下（如前所述）一起无法使用。

更新1：

我之前也测试过Alex JPQL。我现在又做了一次。结果将生成以下SQL：

SELECT ID AS a1, ..., CHANGETIME AS a3, CREATETIME AS a4, ...,
CASE  WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME ELSE CREATETIME END
FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = ?) ORDER BY CASE
WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME ELSE CREATETIME END DESC LIMIT ?, ?

这也很好。但是在进行渗透测试时我遇到了同样的问题。有时，超过1600个请求（两个并行线程，每个发送800个请求）工作正常，某些问题我遇到了这个问题：

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 'FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = 2) ORDER BY CASE  WHEN (CHANGE' at line 1

和生成的SQL（在第一个THEN CHANGETIME之后是WHEN CREATETIME）：

SELECT ID AS a1, BEARBEITUNGSZEIT AS a2, CHANGETIME AS a3, CREATETIME AS a4, FORTSCHRITT AS a5, ISTBEENDET AS a6, AUFGABENTYP_ID AS a7, LEVEL_ID AS a8, BENUTZER_ID AS a9, CASE  WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN  FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = ?) ORDER BY CASE  WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN  DESC LIMIT ?, ?

我认为某个地方存在同步问题或事务问题，有时会导致错误的SQL，这时会发送来自不同客户端的许多请求。但是我没有发现问题。
有人知道吗？我会测试它更新此问题。

最佳答案

 String query = "SELECT ga , "
            + " CASE WHEN (ga.changeTime > ga.createTime) THEN ga.changeTime ELSE ga.createTime END as myorder"
            + " FROM EntityGenerierteAufgabe ga"
            + " WHERE ga.benutzer=:benutzer "
            + " ORDER BY myorder DESC";