Hi, i have a query generated via Criteria that is working well, but i like to improve its performance.
I have 2 entities in this case.
These are "Question" and "Disease".
Each question has a disease.
My query should return a list of all diseases that exists in at least one question.
Right, here are the .hbm.xml of involved entities.
Code:
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="entity.Question" table="QUESTION"
dynamic-insert="true" dynamic-update="true">
<id name="idQuestion" type="java.lang.Long" column="ID_QUESTION" unsaved-value="0">
<generator class="sequence">
<param name="sequence">SEQ_QUESTION</param>
</generator>
</id>
<property name="dsQuestion" type="java.lang.String" not-null="true">
<column name="DS_QUESTION" />
</property>
<many-to-one name="disease" class="entity.Disease" not-null="true" lazy="false">
<column name="ID_DISEASE" />
</many-to-one>
</class>
</hibernate-mapping>
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="entidade.Disease" table="DISEASE" dynamic-insert="true" dynamic-update="true">
<id name="idDisease" type="java.lang.Long" unsaved-value="0">
<column name="ID_Disease"/>
<generator class="sequence">
<param name="sequence">SEQ_Disease</param>
</generator>
</id>
<property name="dsDisease" type="java.lang.String" not-null="true">
<column name="DS_DISEASE" />
</property>
</class>
</hibernate-mapping>
Ok .. based on these mappings, i have the following method.
Code:
public List<Disesase> getListOfDiseaseWithQuestion() {
Criteria criteria = getSession().createCriteria(
Question.class);
criteria.createAlias("disease", "disease");
criteria.setProjection(Projections.property("disease"));
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.addOrder(Order.asc("disease.dsDisease"));
return criteria.list();
}
The result generated is what i need ... BUT !!!
The genearted SQL is the following:
Code:
Hibernate: select this_.ID_DISEASE as y0_ from QUESTION this_, DISEASE di1_,
where this_.ID_DISEASE=di1_.ID_DISEASE order by di1_.DS_DISEASE asc
FOR EACH DISEASE FOUND A NEW SELECT IS GENERATED !!!!
Hibernate: select ID_DISEASE, DS_DISEASE from DISEASE where ID_DISEASE=?
09:58:57 INFO DiseaseServiceImpl:71 - EXECUTADO -> getListOfDiseaseWithQuestion
Well .. I like to generate a SQL like this :Code:
select
distinct DiSEASE.*
from QUESTION,
DISEASE,
where QUESTION.ID_DISEASE=DISEASE.ID_DISEASE
order by DiSEASE.DS_DISEASE asc
Generated SQL should return all data about disease, eliminating the need to generate a new SELECT for each disease found.
PS: I need a solution that uses criteria API, cause its a pattern her in my company.
Sorry for my bad english.
Thank in advance.
Login
FAQ
Search