Creating dynamic query using jpa criteria

Question

I created dynamic query using jpa criteria but an extra pair of parentheses gets added to columns to be selected when I do userGroupSubquery.select(userGroupsRoot);

generated query

select (securitygr3_.group_name, securitygr3_.user_name) from gm.security_groupings securitygr3_ where 1=1 and securitygr3_.user_name='xxx' and (securitygr3_.group_name in ('XYZ'))

expected query:

select securitygr3_.group_name, securitygr3_.user_name from gm.security_groupings securitygr3_ where 1=1 and securitygr3_.user_name='xxx' and (securitygr3_.group_name in ('XYZ'))

Subquery<SecurityGroupings> userGroupSubquery = secUsersQuery.subquery(SecurityGroupings.class);
            Root<SecurityGroupings> userGroupsRoot = userGroupSubquery.from(SecurityGroupings.class);
            Path<SecurityGroupingsId> secGroupId = userGroupsRoot.get("id");
            Path<SecurityUsers> secUsers = secGroupId.get("securityUsers_1");
            Path<SecurityUsers> securityUsers = secGroupId.get("securityUsers");
            Path<String> su_name = secUsers.get("name");
            Path<String> name = securityUsers.get("name");
            userGroupSubquery.select(userGroupsRoot);
            userGroupSubquery.getCompoundSelectionItems();



//userGroupSubquery.where(criteriaBuilder.equal(pet.get(SecurityGroupingsId_.id), root.<String>get("name")));
            Predicate restrictions3 = criteriaBuilder.conjunction();
            restrictions3 = criteriaBuilder.and(restrictions3, criteriaBuilder.and(criteriaBuilder.equal(su_name, dto.getUserId().trim().toUpperCase())));
            restrictions3 = criteriaBuilder.and(restrictions3, criteriaBuilder.and(name.in(userGroups)));
            userGroupSubquery.where(restrictions3);
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(userGroupSubquery));
        }
        secUsersQuery.where(restrictions);

Its just that I get an extra pair of parentheses at select (securitygr3_.group_name, securitygr3_.user_name) from which gives me ora-00907 missing right parenthesis error. I am sure it is coming from userGroupSubquery.select(userGroupsRoot) but I am not sure why. Please help

Bhanu Mittal · Accepted Answer · 2019-07-31 13:41:26Z

1

I got the solution for the above. When the entity has a composite key, then in JPA criteria instead of doing

userGroupSubquery.select(userGroupsRoot);

we should do

userGroupSubquery.select(userGroupsRoot.get("id"));

where id is the composite id.

answered Jul 31, 2019 at 13:41

Bhanu Mittal

813 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating dynamic query using jpa criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related