问题描述
JPA / Hibernate查询中允许的连接数是否有限制?
由于Hibernate ,我必须在我的JPA / Hibernate查询中明确指定连接。例如,人有地址,地址有状态。以下查询检索地址和状态完全加载的人员:
select p,a,s from person p left join p.address a left join a.state s where ...
当我不停地添加连接时,我最终(在12-13左连接之后)达到Hibernate生成无效SQL的限制:
原因:java.sql.SQLException :未找到列'something69_2_'。
我的Hibernate的方言设置为我的数据库实现MySQL:
< property name =hibernate.dialect> org.hibernate.dialect.MySQLInnoDBDialect< / property>
Hibernate在单个查询中可以处理的连接数是否有限制?
编辑1:以下是日志文件:
无法从结果集中读取列值:something69_2_;未找到列'something69_2_'。
然而, something69_2 _
不会出现在SQL查询。这就像Hibernate生成了一个SQL查询并且期望 something69_2 _
在结果中,而不是。
编辑2:类似的问题记录为
编辑3:这是一份记录,已经修复,但不是任何发行版的一部分。
编辑4:我构建了hibernate-core 3.3.2-SNAPSHOT,其中包含错误修复HHH-3636,并未解决此问题。
编辑5:在ManyToMany或OneToMany关系中,错误行为似乎是由多个 LEFT JOIN FETCH
触发的。一个会工作,两三个结果的bug。
编辑6:这里是堆栈跟踪:
javax.persistence.PersistenceException:org.hibernate.exception.SQLGrammarException:无法在org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException中执行查询
(AbstractEntityManagerImpl .java:629)
在org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
引起:org.hibernate.exception.SQLGrammarException:无法执行查询
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
位于org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
位于org.hibernate.loader.Loader。 doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)在org.hibernate.loader.hql.QueryLoader.l上
ist(QueryLoader.java:388)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan。
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
... 69 more
导致:java.sql.SQLException:找不到列'something69_2_'。
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql .jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1136)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl .java:2777)
at org.hibernate.type.IntegerType.get(IntegerType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
在org.hibernate.loader。org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
。 Loader.getRowFromResultSet(Loader.java:553)
org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java: 224)
在org.hibernate.loader。 Loader.doList(Loader.java:2211)
... 77 more
编辑7:所有这些连接的原因是避免Hibernate执行n + 1个查询,请参阅的文档提供了一些建议。
Is there a limit to the number of joins permitted in a JPA/Hibernate query?
Since Hibernate doesn't automatically join, I have to explicitly specify the joins in my JPA/Hibernate query. For example, person has an address, an address has a state. The following query retrieves person(s) with address and state fully loaded:
select p, a, s from person p left join p.address a left join a.state s where ...
As I keep adding joins, I eventually (after 12-13 left joins) reach a limit where Hibernate generates invalid SQL:
Caused by: java.sql.SQLException: Column 'something69_2_' not found.
I do have Hibernate's dialect set to my database implementation, MySQL:
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
Is there an limit to the number joins Hibernate can handle in a single query?
Edit 1: The following is in the log file:
could not read column value from result set: something69_2_; Column 'something69_2_' not found.
However, something69_2_
doesn't appear in the SQL query. It's like Hibernate generated a SQL query and is expecting something69_2_
to be in the results, which is not.
Edit 2: Similar problem documented as an unfixed Hibernate bug HHH-3035
Edit 3: This is a documented Hibernate bug HHH-3636, which has been fixed but is not part of any release yet.
Edit 4: I built hibernate-core 3.3.2-SNAPSHOT which includes bug fix HHH-3636 and it did not address this problem.
Edit 5: The bug behavior seems to be triggered by multiple LEFT JOIN FETCH
on ManyToMany or OneToMany relationships. One will work, two or three results in the bug.
Edit 6: Here's the stack trace:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
... 69 more
Caused by: java.sql.SQLException: Column 'something69_2_' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1136)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2777)
at org.hibernate.type.IntegerType.get(IntegerType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:553)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
... 77 more
Edit 7: The reason for all these joins is to avoid Hibernate doing n+1 queries, see Hibernate FAQ on How can I avoid n+1 SQL SELECT queries when running a Hibernate query?
The question is why are you trying to make such a complex query in the first place?
Have you considered different approaches? The documentation on improving performance makes some suggestions.
这篇关于JPA / Hibernate的最大连接数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!