Hibernate version: 3.2rc1 i try also with 3.0.5 and 3.1.3
Name and version of the database you are using:
the originaly happen in 0racle 9i but the example i made it with HSQLDB
Hi, I'm migrating a big application from HB2 to HB3, and I follow all the instruction of the migration guide, testing the application I found that one of the list was taking longer time to load than it was before, I activate the show sql feature of hibernate, and I saw that for each row of the results it was generating more queries , the real query is very complex to put it here so I made an small example with JUnit to show you the problem, the example use two classes , Document and I18N each one mapped to a table with HB and a test class,
The test has 3 main hqls:
Code:
hql 1 = "select doc ,i from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language"
This generates:
Code:
Hibernate: select document0_.Id as Id40_0_, i18n1_.Id as Id41_1_, i18n1_.languageId as languageId41_1_, document0_.texti18nId as texti2_40_0_, i18n1_
.Description as Descript3_41_1_ from Document document0_, I18N i18n1_ where (document0_.texti18nId=i18n1_.Id )and(i18n1_.languageId=? )
the hql1 returns the results in a array, it generates only one sql query with or without the AST parse activated but see the next query
Code:
hql2 = "select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language"
the hql2 is the same query as the hql1 except that it returns the result in a bean, see the generated queries
Code:
Hibernate: select document0_.Id as col_0_0_, i18n1_.Id as col_1_0_, i18n1_.languageId as col_1_1_ from Document document0_, I18N i18n1_ where documen
t0_.texti18nId=i18n1_.Id and i18n1_.languageId=?
Hibernate: select document0_.Id as Id16_0_, document0_.texti18nId as texti2_16_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id17_0_, i18n0_.languageId as languageId17_0_, i18n0_.Description as Descript3_17_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
Hibernate: select document0_.Id as Id16_0_, document0_.texti18nId as texti2_16_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id17_0_, i18n0_.languageId as languageId17_0_, i18n0_.Description as Descript3_17_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
Hibernate: select document0_.Id as Id16_0_, document0_.texti18nId as texti2_16_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id17_0_, i18n0_.languageId as languageId17_0_, i18n0_.Description as Descript3_17_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
It now generates 7 sql queries, the main difference bettween the the first sql generated by hql2 and the sql generated by the hql1, is that the generated by the hql2 is only fetching the primary keys of the Document and I18N tables instead of all the fields, the rest of the queries are a select by id of each of the Document and I18N objects for each row. If i run this but using the classic hql parser, it will generate the same sql as the hql1.
If I use this hql:
Code:
hql3 = "select new map(doc as doc,i as i18n) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language"
is the same as hql2 but instead of using a bean i use a map, this query generates the same 7 querys of the hql2
I think the 3 querys should generate the same sql, Is this a bug in HB3? or something change and I need to add something to the mapping or hql ?, it seems like HB3 where loading the objects as lazy objects but i try setting both classes as lazy=false and it didn't work.
I have the example in a zip file and I set it to be run with maven in case you need to run the example
Thanks in advance for the help.
Note: Sorry if I wrote something wrong, english is not me native language.
Mapping documents:Code:
<class name="Document" table="Document" >
<id name="id" column="Id" type="long">
<generator class="native" />
</id>
<property name="texti18nId" type="java.lang.Long" >
<column name="texti18nId" />
</property>
</class>
<class name="I18N" table="I18N" >
<composite-id name="id" class="I18NId" >
<key-property name="code" column="Id" type="long"/>
<key-property name="languageId" column="languageId" type="string" />
</composite-id>
<property name="description" type="string" >
<column name="Description" length="4000" />
</property>
</class>
This a part of the test class the rest of the tests are similar the showed here
Code:
private static SessionFactory sessionFactory = null;
//this query return the document and i18n object in an array
private static String hql1 = "select doc ,i from Document doc,I18N i" +
" where doc.texti18nId = i.id.code and i.id.languageId = :language";
//this query return the document and i18n object in a bean
private static String hql2 = "select new DocumentBean(doc,i) from Document doc,I18N i" +
" where doc.texti18nId = i.id.code and i.id.languageId = :language";
//this query return the document and i18n object in a map
private static String hql3 = "select new map(doc as doc,i as i18n) from Document doc,I18N i" +
" where doc.texti18nId = i.id.code and i.id.languageId = :language";
public void testHql1WithAstParser() {
log.info("\n----------------- begin test hql1 with AST HQL parser -----------------");
log.info("hql = "+hql1);
initHibernateSessionFactoryWithAstParser();
Session session = sessionFactory.openSession();
List list = session.createQuery(hql1).setParameter("language","EN").list();
log.info("----------------- end test hql1 -----------------\n");
}
public void testHql2WithAstParser() {
log.info("\n----------------- begin test hql2 with AST HQL parser -----------------");
log.info("hql = "+hql2);
initHibernateSessionFactoryWithAstParser();
Session session = sessionFactory.openSession();
List list = session.createQuery(hql2).setParameter("language","EN").list();
log.info("----------------- end test hql2 -----------------\n");
}
The generated SQL (show_sql=true):This is the log of the 5 tests
Code:
----------------- begin test hql1 with AST HQL parser -----------------
hql = select doc ,i from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language
Hibernate: select document0_.Id as Id4_0_, i18n1_.Id as Id5_1_, i18n1_.languageId as languageId5_1_, document0_.texti18nId as texti2_4_0_, i18n1_.Des
cription as Descript3_5_1_ from Document document0_, I18N i18n1_ where document0_.texti18nId=i18n1_.Id and i18n1_.languageId=?
----------------- end test hql1 -----------------
----------------- begin test hql2 with AST HQL parser -----------------
hql = select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language
Hibernate: select document0_.Id as col_0_0_, i18n1_.Id as col_1_0_, i18n1_.languageId as col_1_1_ from Document document0_, I18N i18n1_ where documen
t0_.texti18nId=i18n1_.Id and i18n1_.languageId=?
Hibernate: select document0_.Id as Id16_0_, document0_.texti18nId as texti2_16_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id17_0_, i18n0_.languageId as languageId17_0_, i18n0_.Description as Descript3_17_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
Hibernate: select document0_.Id as Id16_0_, document0_.texti18nId as texti2_16_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id17_0_, i18n0_.languageId as languageId17_0_, i18n0_.Description as Descript3_17_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
Hibernate: select document0_.Id as Id16_0_, document0_.texti18nId as texti2_16_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id17_0_, i18n0_.languageId as languageId17_0_, i18n0_.Description as Descript3_17_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
----------------- end test hql2 -----------------
----------------- begin test hql3 with AST HQL parser -----------------
hql = select new map(doc as doc,i as i18n) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language
Hibernate: select document0_.Id as col_0_0_, i18n1_.Id as col_1_0_, i18n1_.languageId as col_1_1_ from Document document0_, I18N i18n1_ where documen
t0_.texti18nId=i18n1_.Id and i18n1_.languageId=?
Hibernate: select document0_.Id as Id28_0_, document0_.texti18nId as texti2_28_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id29_0_, i18n0_.languageId as languageId29_0_, i18n0_.Description as Descript3_29_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
Hibernate: select document0_.Id as Id28_0_, document0_.texti18nId as texti2_28_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id29_0_, i18n0_.languageId as languageId29_0_, i18n0_.Description as Descript3_29_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
Hibernate: select document0_.Id as Id28_0_, document0_.texti18nId as texti2_28_0_ from Document document0_ where document0_.Id=?
Hibernate: select i18n0_.Id as Id29_0_, i18n0_.languageId as languageId29_0_, i18n0_.Description as Descript3_29_0_ from I18N i18n0_ where i18n0_.Id=?
and i18n0_.languageId=?
----------------- end test hql3 -----------------
----------------- begin test hql1 with classic HQL parser -----------------
hql = select doc ,i from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language
Hibernate: select document0_.Id as Id40_0_, i18n1_.Id as Id41_1_, i18n1_.languageId as languageId41_1_, document0_.texti18nId as texti2_40_0_, i18n1_
.Description as Descript3_41_1_ from Document document0_, I18N i18n1_ where (document0_.texti18nId=i18n1_.Id )and(i18n1_.languageId=? )
----------------- end test hql1 -----------------
----------------- begin test hql2 with classic HQL parser -----------------
hql = select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language
Hibernate: select document0_.Id as Id52_0_, i18n1_.Id as Id53_1_, i18n1_.languageId as languageId53_1_, document0_.texti18nId as texti2_52_0_, i18n1_
.Description as Descript3_53_1_ from Document document0_, I18N i18n1_ where (document0_.texti18nId=i18n1_.Id )and(i18n1_.languageId=? )
----------------- end test hql2 -----------------
Debug level Hibernate log excerpt:This is part of the the log of the test for hql2 with hibernate debug activated
Code:
unable to locate HQL query plan in cache; generating (select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language)
parse() - HQL: select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language
--- HQL AST ---
\-[QUERY] 'query'
+-[SELECT_FROM] 'SELECT_FROM'
| +-[FROM] 'from'
| | +-[RANGE] 'RANGE'
| | | +-[IDENT] 'Document'
| | | \-[ALIAS] 'doc'
| | \-[RANGE] 'RANGE'
| | +-[IDENT] 'I18N'
| | \-[ALIAS] 'i'
| \-[SELECT] 'select'
| \-[CONSTRUCTOR] '('
| +-[IDENT] 'DocumentBean'
| +-[IDENT] 'doc'
| \-[IDENT] 'i'
\-[WHERE] 'where'
\-[AND] 'and'
+-[EQ] '='
| +-[DOT] '.'
| | +-[IDENT] 'doc'
| | \-[IDENT] 'texti18nId'
| \-[DOT] '.'
| +-[DOT] '.'
| | +-[IDENT] 'i'
| | \-[IDENT] 'id'
| \-[IDENT] 'code'
\-[EQ] '='
+-[DOT] '.'
| +-[DOT] '.'
| | +-[IDENT] 'i'
| | \-[IDENT] 'id'
| \-[IDENT] 'languageId'
\-[COLON] ':'
\-[IDENT] 'language'
throwQueryException() : no errors
select << begin [level=1, statement=select]
FromClause{level=1} : Document (doc) -> document0_
FromClause{level=1} : I18N (i) -> i18n1_
Resolved : doc -> document0_.Id
Resolved : i -> (i18n1_.Id, i18n1_.languageId)
Resolved : doc -> document0_.Id
getDataType() : texti18nId -> org.hibernate.type.LongType@da3a1e
Resolved : doc.texti18nId -> document0_.texti18nId
Resolved : i -> (i18n1_.Id, i18n1_.languageId)
getDataType() : id -> org.hibernate.type.ComponentType@180f96c
Unresolved property path is now 'id.code'
Resolved : i.id -> i18n1_.Id
getDataType() : id.code -> org.hibernate.type.LongType@da3a1e
Resolved : i.id.code -> i18n1_.Id
Resolved : i -> (i18n1_.Id, i18n1_.languageId)
getDataType() : id -> org.hibernate.type.ComponentType@180f96c
Unresolved property path is now 'id.languageId'
Resolved : i.id -> i18n1_.languageId
getDataType() : id.languageId -> org.hibernate.type.StringType@2af081
Resolved : i.id.languageId -> i18n1_.languageId
select : finishing up [level=1, statement=select]
processQuery() : ( SELECT ( {select clause} ( ( DocumentBean document0_.Id (i18n1_.Id, i18n1_.languageId) ) ) ( FromClause{level=1} Document document0_ I18N i18n1_ ) ( where ( and ( = ( document0_.texti18nId document0_.Id texti18nId ) ( i18n1_.Id ( i18n1_.Id (i18n1_.Id, i18n1_.languageId) id ) code ) ) ( = ( i18n1_.languageId ( i18n1_.languageId (i18n1_.Id, i18n1_.languageId) id ) languageId ) ? ) ) ) )
Using FROM fragment [I18N i18n1_]
Using FROM fragment [Document document0_]
select >> end [level=1, statement=select]
--- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (Document,I18N)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| \-[CONSTRUCTOR] ConstructorNode: '('
| +-[IDENT] IdentNode: 'DocumentBean' {originalText=DocumentBean}
| +-[ALIAS_REF] IdentNode: 'document0_.Id as col_0_0_' {alias=doc, className=Document, tableAlias=document0_}
| \-[ALIAS_REF] IdentNode: 'i18n1_.Id as col_1_0_, i18n1_.languageId as col_1_1_' {alias=i, className=I18N, tableAlias=i18n1_}
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[i, doc], fromElementByTableAlias=[document0_, i18n1_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| +-[FROM_FRAGMENT] FromElement: 'Document document0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=doc,role=null,tableName=Document,tableAlias=document0_,origin=null,colums={,className=Document}}
| \-[FROM_FRAGMENT] FromElement: 'I18N i18n1_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=i,role=null,tableName=I18N,tableAlias=i18n1_,origin=null,colums={,className=I18N}}
\-[WHERE] SqlNode: 'where'
\-[AND] SqlNode: 'and'
+-[EQ] BinaryLogicOperatorNode: '='
| +-[DOT] DotNode: 'document0_.texti18nId' {propertyName=texti18nId,dereferenceType=4,propertyPath=texti18nId,path=doc.texti18nId,tableAlias=document0_,className=Document,classAlias=doc}
| | +-[ALIAS_REF] IdentNode: 'document0_.Id' {alias=doc, className=Document, tableAlias=document0_}
| | \-[IDENT] IdentNode: 'texti18nId' {originalText=texti18nId}
| \-[DOT] DotNode: 'i18n1_.Id' {propertyName=code,dereferenceType=4,propertyPath=id.code,path=i.id.code,tableAlias=i18n1_,className=I18N,classAlias=i}
| +-[DOT] DotNode: 'i18n1_.Id' {propertyName=code,dereferenceType=2,propertyPath=id.code,path=i.id,tableAlias=i18n1_,className=I18N,classAlias=i}
| | +-[ALIAS_REF] IdentNode: '(i18n1_.Id, i18n1_.languageId)' {alias=i, className=I18N, tableAlias=i18n1_}
| | \-[IDENT] IdentNode: 'id' {originalText=id}
| \-[IDENT] IdentNode: 'code' {originalText=code}
\-[EQ] BinaryLogicOperatorNode: '='
+-[DOT] DotNode: 'i18n1_.languageId' {propertyName=languageId,dereferenceType=4,propertyPath=id.languageId,path=i.id.languageId,tableAlias=i18n1_,className=I18N,classAlias=i}
| +-[DOT] DotNode: 'i18n1_.languageId' {propertyName=languageId,dereferenceType=2,propertyPath=id.languageId,path=i.id,tableAlias=i18n1_,className=I18N,classAlias=i}
| | +-[ALIAS_REF] IdentNode: '(i18n1_.Id, i18n1_.languageId)' {alias=i, className=I18N, tableAlias=i18n1_}
| | \-[IDENT] IdentNode: 'id' {originalText=id}
| \-[IDENT] IdentNode: 'languageId' {originalText=languageId}
\-[NAMED_PARAM] ParameterNode: '?' {name=language, expectedType=org.hibernate.type.StringType@2af081}
throwQueryException() : no errors
HQL: select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language
SQL: select document0_.Id as col_0_0_, i18n1_.Id as col_1_0_, i18n1_.languageId as col_1_1_ from Document document0_, I18N i18n1_ where document0_.texti18nId=i18n1_.Id and i18n1_.languageId=?
throwQueryException() : no errors
HQL param location recognition took 0 mills (select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language)
located HQL query plan in cache (select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language)
find: select new DocumentBean(doc,i) from Document doc,I18N i where doc.texti18nId = i.id.code and i.id.languageId = :language
named parameters: {language=EN}
about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
opening JDBC connection
Hibernate: select document0_.Id as col_0_0_, i18n1_.Id as col_1_0_, i18n1_.languageId as col_1_1_ from Document document0_, I18N i18n1_ where document0_.texti18nId=i18n1_.Id and i18n1_.languageId=?
preparing statement
bindNamedParameters() EN -> language [1]
Login
FAQ
Search