I'm knew to hibernate. I have a sql statement
SELECT VERSION_ID,D_BEGIN,D_END,
(SELECT NAME FROM zvit where ZVIT_ID=version.ZVIT_ID) as name
FROM version
where VERSION_ID in
(SELECT term.VERSION_ID FROM term
where term.PERIOD_MONTH= :periodMonth and term.PERIOD_TYPE= :periodType and term.PERIOD_YEAR= :periodYear )
order by 1 ;
I tried to implement it with createCriteria and HQL.
DetachedCriteria subQuery = DetachedCriteria.forClass(TermData.class)
.add(Restrictions.eq("periodmonth", periodMonth))
.add(Restrictions.eq("periodtype", periodType))
.add(Restrictions.eq("periodyear", periodYear))
.setProjection(Projections.projectionList()
.add(Projections.property("versionId.versionId"))
);
Criteria versionCriteria = session.createCriteria(VersionData.class)
.addOrder(Order.asc("versionId"))
.add(Subqueries.propertyIn("versionId", subQuery))
.createAlias("zvitId", "zvitId", org.hibernate.sql.JoinType.INNER_JOIN)
.setProjection(Projections.projectionList()
.add(Projections.property("versionId"))
.add(Projections.property("dbegin"))
.add(Projections.property("dend"))
.add(Projections.property("zvitId.name"), "name")
);
HQL:
Query query = session.createQuery(""
+ "from VersionData as version "
// + "inner join version.zvitId as zvit "
+ "where version.versionId in "
+ "(select term.versionId from TermData as term "
+ "where term.periodmonth= :periodmonth and term.periodtype= :periodtype and term.periodyear= :periodyear)");
The problem is that this HQL takes 10 times longer to execute. And does a lot of unnecessary queries. I tried to do it with commented string and it improved a bit, but still works 5 time longer than createCriteria query and in addition I couldn't do this convertion
List<VersionData> queryResult = query.list();
as i get VersionData and ZvitData objects. That doesn't seems a problem, but i still don't get why I don't receive just VersionData Object.
But there is more important question is there any way to improve this HQL statement for it to execute about the same time as mysql or createCriteria query.
VersionData defenition
@Entity
@Table(name = "version")
public class VersionData implements Serializable
{
/**
*
*/
private static final long serialVersionUID = 7355281418627668744L;
@Id
@Column(name="VERSION_ID")
private String versionId;
/**
* user dbegin
*/
@Column(name = "D_BEGIN")
@Type(type = "date")
private Date dbegin;
/** user dend */
@Column(name = "D_END")
@Type(type = "date")
private Date dend;
/**
* user zvitId
*/
@ManyToOne
@JoinColumn(name="ZVIT_ID")
private ZvitData zvitId;
@OneToMany(targetEntity=TermData.class, mappedBy = "versionId", cascade=javax.persistence.CascadeType.ALL, fetch=FetchType.LAZY)
private List<TermData> terms;
//getters, setters, hashcode, equals
}
Well, with the criteria query you posted, you only fetch the fields you need, while with your HQL query you fetch the whole Hibernate managed entity (VersionData). Depending on how this entity is configured, you might see then other queries fired to populate the fields of this entity.
You may do the same thing as you do with criteria also with HQL, and in my opinion it would be more understandable and therefore maintainable than working with criteria. Just define a java class MyClass for the fields you wish to fetch, with appropriate constructor, and call an HQL query which looks something like:
"select new path.to.myclass.MyClass ( "
+ " alias1.field1, "
+ " alias2.field2, "
+ " ... "
where aliases are the names you are giving to the entities in your HQL query (e.g. version is the alias of VersionData in the query you wrote). No projections, no extra abstractions, just an SQL-like query.
Instead of defining a new class to hold the fields returned from the HQL query (which I find more convenient), you may fetch the fields directly, then processing the result returned, as in the link below: