How To: RESTRICTIONS ?
allforjava.allforjava.aol.in Jul 3, 2009 3:25 PMDear Team,
1. Is there a way to use RESTRICTIONS/EntityQuery with:
a. Sub-query
b. On collection
c. How to use OR and group by? Its throwing exception
Note: using Seam.2.1.2CR
2. I need to know the feasible way we can use the RESTRICTIONS. Following is one workout, How is it working?
ProjectRole has NRole so the
public class ProjectRoleList extends EntityQuery<ProjectRole> {
private static final String[] RESTRICTIONS = {
"projectRole.projects.id = #{projectRoleList.projectsId}",
"lower(NRole.roleName) like concat(lower(#{nRoleList.NRole.roleName}),'%')",
"lower(NRole.roleId) like concat(lower(#{nRoleList.NRole.roleId}),'%')"};
.
.
}
Generated Hibernate Query:
select
top 11 projectrol0_.ID as ID21_,
projectrol0_.ROLE_ID as ROLE7_21_,
projectrol0_.CREATED_BY as CREATED2_21_,
projectrol0_.CREATED_DATE as CREATED3_21_,
projectrol0_.MODIFIED_BY as MODIFIED4_21_,
projectrol0_.MODIFIED_DATE as MODIFIED5_21_,
projectrol0_.PROJECT_ID as PROJECT6_21_
from
PORTALNEW.dbo.PROJECT_ROLE projectrol0_,
PORTALNEW.dbo.N_ROLE nrole1_
where
projectrol0_.ROLE_ID=nrole1_.ID
and (
lower(nrole1_.ROLE_ID) like (lower(?)+'%')
)
17:26:01,778 INFO [STDOUT] Hibernate:
select
projects0_.ID as ID27_0_,
projects0_.ACTIVE as ACTIVE27_0_,
projects0_.CREATED_BY as CREATED3_27_0_,
projects0_.CREATED_DATE as CREATED4_27_0_,
projects0_.CUSTOMER_ID as CUSTOMER17_27_0_,
projects0_.CUSTOMER_ACCOUNT_ID as CUSTOMER5_27_0_,
projects0_.DESCRIPTION as DESCRIPT6_27_0_,
projects0_.EFFORT as EFFORT27_0_,
projects0_.END_DATE as END8_27_0_,
projects0_.MODIFIED_BY as MODIFIED9_27_0_,
projects0_.MODIFIED_DATE as MODIFIED10_27_0_,
projects0_.NAME as NAME27_0_,
projects0_.ORGANIZATION_ID as ORGANIZ16_27_0_,
projects0_.PROJECT_ID as PROJECT12_27_0_,
projects0_.TYPE as TYPE27_0_,
projects0_.PROJECT_TYPE as PROJECT13_27_0_,
projects0_.PARENT_ID as PARENT20_27_0_,
projects0_.SHORT_NAME as SHORT14_27_0_,
projects0_.START_DATE as START15_27_0_,
projects0_.USER_ID as USER18_27_0_
from
PORTALNEW.dbo.PROJECTS projects0_
where
projects0_.ID=?
17:26:01,794 INFO [STDOUT] Hibernate:
select
nrole0_.ID as ID20_0_,
nrole0_.ACTIVE as ACTIVE20_0_,
nrole0_.ROLE_ID as ROLE3_20_0_,
nrole0_.ROLE_NAME as ROLE4_20_0_
from
PORTALNEW.dbo.N_ROLE nrole0_
where
nrole0_.ID=?
17:26:01,809 INFO [STDOUT] Hibernate:
select
count(projectrol0_.ID) as col_0_0_
from
PORTALNEW.dbo.PROJECT_ROLE projectrol0_,
PORTALNEW.dbo.N_ROLE nrole1_
where
projectrol0_.ROLE_ID=nrole1_.ID
and (
lower(nrole1_.ROLE_ID) like (lower(?)+'%')
)
Thank you in advance!
1. Is there a way to use RESTRICTIONS/EntityQuery with:
a. Sub-query
b. On collection
c. How to use OR and group by? Its throwing exception
Note: using Seam.2.1.2CR
2. I need to know the feasible way we can use the RESTRICTIONS. Following is one workout, How is it working?
ProjectRole has NRole so the
public class ProjectRoleList extends EntityQuery<ProjectRole> {
private static final String[] RESTRICTIONS = {
"projectRole.projects.id = #{projectRoleList.projectsId}",
"lower(NRole.roleName) like concat(lower(#{nRoleList.NRole.roleName}),'%')",
"lower(NRole.roleId) like concat(lower(#{nRoleList.NRole.roleId}),'%')"};
.
.
}
Generated Hibernate Query:
select
top 11 projectrol0_.ID as ID21_,
projectrol0_.ROLE_ID as ROLE7_21_,
projectrol0_.CREATED_BY as CREATED2_21_,
projectrol0_.CREATED_DATE as CREATED3_21_,
projectrol0_.MODIFIED_BY as MODIFIED4_21_,
projectrol0_.MODIFIED_DATE as MODIFIED5_21_,
projectrol0_.PROJECT_ID as PROJECT6_21_
from
PORTALNEW.dbo.PROJECT_ROLE projectrol0_,
PORTALNEW.dbo.N_ROLE nrole1_
where
projectrol0_.ROLE_ID=nrole1_.ID
and (
lower(nrole1_.ROLE_ID) like (lower(?)+'%')
)
17:26:01,778 INFO [STDOUT] Hibernate:
select
projects0_.ID as ID27_0_,
projects0_.ACTIVE as ACTIVE27_0_,
projects0_.CREATED_BY as CREATED3_27_0_,
projects0_.CREATED_DATE as CREATED4_27_0_,
projects0_.CUSTOMER_ID as CUSTOMER17_27_0_,
projects0_.CUSTOMER_ACCOUNT_ID as CUSTOMER5_27_0_,
projects0_.DESCRIPTION as DESCRIPT6_27_0_,
projects0_.EFFORT as EFFORT27_0_,
projects0_.END_DATE as END8_27_0_,
projects0_.MODIFIED_BY as MODIFIED9_27_0_,
projects0_.MODIFIED_DATE as MODIFIED10_27_0_,
projects0_.NAME as NAME27_0_,
projects0_.ORGANIZATION_ID as ORGANIZ16_27_0_,
projects0_.PROJECT_ID as PROJECT12_27_0_,
projects0_.TYPE as TYPE27_0_,
projects0_.PROJECT_TYPE as PROJECT13_27_0_,
projects0_.PARENT_ID as PARENT20_27_0_,
projects0_.SHORT_NAME as SHORT14_27_0_,
projects0_.START_DATE as START15_27_0_,
projects0_.USER_ID as USER18_27_0_
from
PORTALNEW.dbo.PROJECTS projects0_
where
projects0_.ID=?
17:26:01,794 INFO [STDOUT] Hibernate:
select
nrole0_.ID as ID20_0_,
nrole0_.ACTIVE as ACTIVE20_0_,
nrole0_.ROLE_ID as ROLE3_20_0_,
nrole0_.ROLE_NAME as ROLE4_20_0_
from
PORTALNEW.dbo.N_ROLE nrole0_
where
nrole0_.ID=?
17:26:01,809 INFO [STDOUT] Hibernate:
select
count(projectrol0_.ID) as col_0_0_
from
PORTALNEW.dbo.PROJECT_ROLE projectrol0_,
PORTALNEW.dbo.N_ROLE nrole1_
where
projectrol0_.ROLE_ID=nrole1_.ID
and (
lower(nrole1_.ROLE_ID) like (lower(?)+'%')
)
Thank you in advance!