Geek Answers Handbook

Sleep criteria

I am currently working on a project for passing some deprecated select jdbc statements to use Hibernate and its api criteria.

The two corresponding table columns and the SQL query are as follows:

-QUERIES- primaryId -QUERYDETAILS- primaryId linkedQueryId -> Foreign key references queries.primaryId value1 value2 select * from queries q where q.primaryId not in (SELECT qd.linkedQueryId FROM querydetails qd WHERE (qd.value1 LIKE 'PROMPT%' OR qd.value2 LIKE 'PROMPT%'));

My relationship with the entity is as follows:

 @Table("queries") public class QueryEntity{ @Id @Column private Long primaryId; @OneToMany(targetEntity = QueryDetailEntity.class, mappedBy = "query", fetch = FetchType.EAGER) private Set<QueryDetailEntities> queryDetails; //..getters/setters.. } @Entity @Table(name = "queryDetails") public class QueryDetailEntity { @Id @Column private Long primaryId; @ManyToOne(targetEntity = QueryEntity.class) private QueryEntity query; @Column(name="value1") private String value1; @Column(name="value2") private String value2; //..getters/setters.. }

I am trying to use api criteria as follows:

 Criteria crit = sessionFactory.getCurrentSession().createCriteria(QueryEntity.class); DetachedCriteria subQuery = DetachedCriteria.forClass(QueryDetailEntity.class); LogicalExpression hasPrompt = Restrictions.or(Restrictions.ilike("value1", "PROMPT%"), Restrictions.ilike("value2", "PROMPT%")); subQuery.add(hasPrompt); Criterion subQueryCrit = Subqueries.notIn("queryDetails", subQuery); crit.add(subQueryCrit); List<QueryMainEntity> entities = (List<QueryMainEntity>) crit.list(); System.out.println("# of results = " + entities.size());

I get a NullPointerException in the crit.list () line, which looks like

 Exception in thread "main" java.lang.NullPointerException at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:362) at org.hibernate.criterion.SubqueryExpression.createAndSetInnerQuery(SubqueryExpression.java:153) at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:69) at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:380) at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:114) at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:83) at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:92) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1687) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)

Now I think its pretty safe to say that I am using Criti Api / Detached Query Api incorrectly, but I'm not sure what the “right” way to do this is because Hibernate Docs briefly cover the api subquery criteria.

I understand that this is a rather long question, but I believe that it seems to pose all the relevant aspects of the question (the query I'm trying to present through API criteria, tables, entities).

+4
source share
1 answer

Take a picture:

 DetachedCriteria d = DetachedCriteria.forClass(QueryDetailEntity.class, "qd"); d.setProjection(Projections.projectionList().add(Projections.property("qd.query"))); d.add(Restrictions.or(Restrictions.like("qd.value1", "PROMPT%"), Restrictions.like("qd.value2", "PROMPT%"))); criteria = session.createCriteria(QueryEntity.class, "q"); criteria.add(Subqueries.propertyNotIn("q.primaryId", d)); criteria.list();

The following are property names, not column names:

 qd.query qd.value1 qd.value2 q.primaryId

As a side note, if this is not a dynamically generated query, have you considered using HQL?

+7
source

More articles:


All Articles