0

Im trying to build using CriteriaBuilder the following query :

SELECT * FROM job j, asset a, asset_users au
    where j.JOB_ID = a.ASSET_ID and a.ASSET_ID = au.ASSET_ID and au.USER_ID = 6

Where job has an asset and asset has a list of users... I want to return just the list of jobs which have a asset that contains a given user...

I saw some guys doing it like that :

Session session = this.sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(Company.class);
criterion = Restrictions.eq("companyRoles.name", "ADMIN");
criteria.add(criterion);
List<Company> companyList = criteria.list();

Tried to replicate that to criteriabuilder but got no luck. All i was getting is that it couldn't find my user id inside an object list (userList). Guess my example is harder because you have to access the object like (job).asset.userList.id . BTW, tried this as well :

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Job> cq = cb.createQuery(Job.class);
Root<Job> jobRoot = cq.from(Job.class);
Join<Job, User> assetJoin = jobRoot.join("asset.userList");
cq.where(assetJoin.get("id").in(id));

got same issue... couldn't find the path.

Any help is very much appreciated! Thanks

TW.
  • 27
  • 1
  • 12

1 Answers1

1

I think you are missing a step. You need the next level of joining.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Job> cq = cb.createQuery(Job.class);
Root<Job> jobRoot = cq.from(Job.class);

// join the asset
Join<Job, Asset> assetJoin = jobRoot.join("asset");

// join the list of users
Join<Asset, User> assetUserJoin = assetJoin.join("userList");

cq.where(assetUserJoin.get("id").in(id));

The type of assetUsrJoin.get("id") is Path<Long> or something similar. JPA Criteria API

carbontax
  • 2,164
  • 23
  • 37
  • OMG you are awesome... made me cry really... worked like... like... rockets do... Taught me a great deal! Thank you very much! – TW. Jun 13 '14 at 14:24