All geek questions in one place

Hibernate Critieria join two tables with a condition on the 2nd table and lead to the 1st table

I have a question using Hibernate Criteria, I need to convert this query using criteria.

SELECT * FROM A a_ INNER JOIN B b_ ON a_.column1 = b_.column1 AND b_.column2 IN (X, Y) AND active = 'Y';

I need the result in the form of table A.

+4
source share
2 answers

If associations are defined, see http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-associations

In case associations are not specified in the definition of objects, you cannot use criteria. You can use HQL for internal connections (you need to write it in implicit connection notations ), to perform left joins you must use your own SQL.

+1
source

I just solved this problem, here is my code

Criteria criteria = session.createCriteria(ProductOffer.class); criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY); Date effDate = TypeConvertUtil.toDate(param.get("effDate")); criteria.add(Restrictions.le("effDate", effDate)); criteria.add(Restrictions.gt("expDate", effDate)); criteria.createAlias("productOfferPropDefs", "def",JoinType.LEFT_OUTER_JOIN); criteria.setFetchMode("productOfferPropDefs", FetchMode.JOIN); criteria.add(Restrictions.le("def.effDate", effDate)); criteria.add(Restrictions.gt("def.expDate", effDate)); criteria.createAlias("def.productOfferProps", "prop",JoinType.LEFT_OUTER_JOIN); criteria.setFetchMode("def.productOfferProps", FetchMode.JOIN); criteria.add(Restrictions.le("prop.effDate", effDate)); criteria.add(Restrictions.gt("prop.expDate", effDate)); productOfferList = criteria.list();

note that

 criteria.createAlias("productOfferPropDefs", "def",JoinType.LEFT_OUTER_JOIN);

This parameter is important:

 JoinType.LEFT_OUTER_JOIN

if you have not used it, and your one-to-many relationship, it will fall into the 1: N classic problem for sleep mode

+1
source

Source: https://habr.com/ru/post/1346996/

More articles:


All Articles