java - Hibernate/JPA + Derby - SELECT 语句在 GROUP BY、ORDER BY 或选择列表中有太多项目

Question

我使用 Hibernate 将 JPA 数据库映射到 Derby 数据库。对于复杂的对象结构，我得到“org.apache.derby.client.am.SqlException:SELECT 语句在 GROUP BY、ORDER BY 或选择列表中有太多项目”:

org.apache.derby.client.am.SqlException: SELECT statement has too many items in GROUP BY, ORDER BY or select list.
        org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
        org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
        org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
        org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
        org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
        org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
        org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
        org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
        org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
        org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
        org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
        org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
        org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
        org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
        org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
        org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
        org.hibernate.loader.Loader.doQuery(Loader.java:673)
        org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        org.hibernate.loader.Loader.loadEntity(Loader.java:1860)
        org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:48)
        org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:42)
        org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3044)
        org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:395)
        org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:375)
        org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:139)
        org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:195)
        org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:103)
        org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)
        org.hibernate.impl.SessionImpl.get(SessionImpl.java:815)
        org.hibernate.event.def.DefaultMergeEventListener.entityIsDetached(DefaultMergeEventListener.java:229)
        org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:120)
        org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:53)
        org.hibernate.impl.SessionImpl.fireMerge(SessionImpl.java:677)
        org.hibernate.impl.SessionImpl.merge(SessionImpl.java:661)
        org.hibernate.impl.SessionImpl.merge(SessionImpl.java:665)
        org.hibernate.ejb.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:228)
        sun.reflect.GeneratedMethodAccessor376.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:365)
        $Proxy74.merge(Unknown Source)
        sun.reflect.GeneratedMethodAccessor376.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
        $Proxy41.merge(Unknown Source)

有没有人遇到过这个问题，解决方案是什么？我在想

• 切换到 MySQL
• 延迟加载一些属性 (fetch=FetchType.LAZY)
• 重新设计数据库架构(现在可能很难，数据库是使用 XSD 架构中的 Hyperjaxb 生成的，必须更改 shemas)

您会选择哪种解决方案？你知道 Derby 对于 SQL 语句的限制是什么吗？

Answer 1

尝试使用 show_sql=true 调试它 doc
检查生成的 SQL 并尝试调试它。