Hi everybody,

I have an HQL query for multi search criteria... like this......

select u, r from com.mhhe.authoring.persistence.ContentUser u,
com.mhhe.authoring.persistence.ContentRole r,
com.mhhe.authoring.persistence.ContentUserRole ur
where u.userName = ur.contentUser.userName
and ur.contentRole.roleId = r.roleId
and lower(u.userName) like :userId
and lower(u.firstName) like :firstName
and lower(u.lastName) like :lastName
and u.createdBy like :userName


I just want to convert this into Criteria API code.. I am just confusing by using with 3 objs.( Prettty clear with a single obj. Here I have ContentUser, ContentRole, ContetUserRole)..


Please anybody can clarify me.


Thanks in advance......

Hi






Amila

(Don't forget to rate if helps)

Hi






Amila

(Don't forget to rate if helps)

Hey ,
Thanks for your reply.


Can I use setFetchMode as JOIN to avoid the

.add(Restrictions.sqlRestriction("lower(u.userName) like ?", userId , Hibernate.STRING))
.add(Restrictions.sqlRestriction("lower(u.firstName) like ?", firstName , Hibernate.STRING))
.add(Restrictions.sqlRestriction("lower(u.lastName) like ?", lastName , Hibernate.STRING))

line.....


Can I add like....

criteria.add(Expression.like("cu.firstName", "%"+firstName+"%"));
criteria.add(Expression.like("cu.userName", "manickaraju"));
criteria.add(Expression.like("cu.lastName", "%"+lastName+"%"));
criteria.add(Expression.like("cu.createdBy", "manicakraju"));


I do expect your reply

Hi


You didn't rate my answer

You can use this
criteria.add(Expression.like("cu.firstName", "%"+firstName+"%"));

but in your requirment there is lower(cu.firstName) you can't use this in above statement.



Amila

(Don't forget to rate if helps)

Thanks again,

But I get exception like....

org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)


Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 2


I couldn't resolve this. Please help me ......


Thank in advance.....

Hi


Run your application with hibernate property show-sql true and send your generated query back.

confg.setProperty("hibernate.show_sql","true");

Amila

(Don't forget to rate if helps)

Hi,
The query generated by Hibernate looks like....

Hibernate: select this_.USER_NAME as USER1_1_2_, this_.SEC_QUES_ID as SEC2_1_2_, this_.TYPE_ID as TYPE3_1_2_, this_.COUNTRY as COUNTRY1_2_, this
WORD as PASSWORD1_2_, this_.PASSWORD_UPDATED_ON as PASSWORD6_1_2_, this_.ACCOUNT_EXP_DATE as ACCOUNT7_1_2_, this_.FIRST_NAME as FIRST8_1_2_, thi
DLE_INITIAL as MIDDLE9_1_2_, this_.LAST_NAME as LAST10_1_2_, this_.ADDRESS as ADDRESS1_2_, this_.CITY as CITY1_2_, this_.STATE as STATE1_2_, thi
as ZIP1_2_, this_.BUS_PH_NUM as BUS15_1_2_, this_.BUS_FAX_NUM as BUS16_1_2_, this_.E_MAIL as E17_1_2_, this_.CREATED_ON as CREATED18_1_2_, this
TED_BY as CREATED19_1_2_, this_.UPDATED_ON as UPDATED20_1_2_, this_.UPDATED_BY as UPDATED21_1_2_, this_.PASSWORD_EXP_DURATION as PASSWORD22_1_2_
_.ADDRESS2 as ADDRESS23_1_2_, this_.COMPANY_NAME as COMPANY24_1_2_, this_.USER_PREF as USER25_1_2_, this_.STATUS as STATUS1_2_, this_.SEC_QUES_A
SEC27_1_2_, ur1_.ROLE_ID as ROLE1_7_0_, ur1_.USER_NAME as USER2_7_0_, ur1_.CREATED_ON as CREATED3_7_0_, ur1_.CREATED_BY as CREATED4_7_0_, ur1_.U
_ON as UPDATED5_7_0_, ur1_.UPDATED_BY as UPDATED6_7_0_, r2_.ROLE_ID as ROLE1_10_1_, r2_.ROLE_DESC as ROLE2_10_1_, r2_.CREATED_ON as CREATED3_10_
_.CREATED_BY as CREATED4_10_1_, r2_.UPDATED_ON as UPDATED5_10_1_, r2_.UPDATED_BY as UPDATED6_10_1_ from CONTENT_USER this_ inner join CONTENT_US
E ur1_ on this_.USER_NAME=ur1_.USER_NAME inner join CONTENT_ROLE r2_ on ur1_.ROLE_ID=r2_.ROLE_ID where this_.USER_NAME=? and ur1_.ROLE_ID=? and
USER_NAME like ? and this_.FIRST_NAME like ? and this_.LAST_NAME like ? and this_.CREATED_BY like ?



By running this will I be getting the Two obj. ()

Content User and Content Role .


Thanks

Hi

Send your db structure of that tables and mapping files


Amila

hi,

Thanks again to get back me......

ContentRole.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 20, 2006 9:49:59 AM by Hibernate Tools 3.2.0.beta8 -->
<hibernate-mapping><meta attribute='extends'>com.mhhe.common.persistence.PersistentObject</meta>
<class name="com.mhhe.authoring.persistence.ContentRole" table="CONTENT_ROLE">
<id name="roleId" type="short">
<column name="ROLE_ID" precision="3" scale="0" />
<generator class="assigned" />
</id>
<property name="roleDesc" type="string">
<column name="ROLE_DESC" length="30" not-null="true" />
</property>
<property name="createdOn" type="date">
<column name="CREATED_ON" length="7" not-null="true" />
</property>
<property name="createdBy" type="string">
<column name="CREATED_BY" length="35" not-null="true" />
</property>
<property name="updatedOn" type="date">
<column name="UPDATED_ON" length="7" not-null="true" />
</property>
<property name="updatedBy" type="string">
<column name="UPDATED_BY" length="35" not-null="true" />
</property>
<set name="contentUserRoles" inverse="true">
<key>
<column name="ROLE_ID" precision="3" scale="0" not-null="true" />
</key>
<one-to-many class="com.mhhe.authoring.persistence.ContentUserRole" />
</set>
</class>
</hibernate-mapping>



ContentUser.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 20, 2006 9:49:59 AM by Hibernate Tools 3.2.0.beta8 -->
<hibernate-mapping><meta attribute='extends'>com.mhhe.common.persistence.PersistentObject</meta>
<class name="com.mhhe.authoring.persistence.ContentUser" table="CONTENT_USER">
<id name="userName" type="string">
<column name="USER_NAME" length="35" />
<generator class="assigned" />
</id>
<many-to-one name="securityQuestion" class="com.mhhe.authoring.persistence.SecurityQuestion" fetch="select">
<column name="SEC_QUES_ID" precision="3" scale="0" />
</many-to-one>
<many-to-one name="employeeType" class="com.mhhe.authoring.persistence.EmployeeType" fetch="select">
<column name="TYPE_ID" precision="2" scale="0" />
</many-to-one>
<many-to-one name="country" class="com.mhhe.authoring.persistence.Country" fetch="select">
<column name="COUNTRY" length="3" not-null="true" />
</many-to-one>
<property name="password" type="string">
<column name="PASSWORD" length="15" not-null="true" />
</property>
<property name="passwdUpDatedOn" type="date">
<column name="PASSWORD_UPDATED_ON" length="7" not-null="true" />
</property>
<property name="passwdExpDate" type="java.sql.Timestamp">
<column name="ACCOUNT_EXP_DATE" length="7" not-null="false" />
</property>
<property name="firstName" type="string">
<column name="FIRST_NAME" length="30" not-null="true" />
</property>
<property name="middleInitial" type="string">
<column name="MIDDLE_INITIAL" length="1" />
</property>
<property name="lastName" type="string">
<column name="LAST_NAME" length="30" not-null="true" />
</property>
<property name="address" type="string">
<column name="ADDRESS" length="50" not-null="true" />
</property>
<property name="city" type="string">
<column name="CITY" length="30" not-null="true" />
</property>
<property name="state" type="string">
<column name="STATE" length="30" not-null="true" />
</property>
<property name="zip" type="string">
<column name="ZIP" length="12" not-null="true" />
</property>
<property name="busPhNum" type="string">
<column name="BUS_PH_NUM" length="12" not-null="true" />
</property>
<property name="busFaxNum" type="string">
<column name="BUS_FAX_NUM" length="12" />
</property>
<property name="EMail" type="string">
<column name="E_MAIL" length="50" not-null="true" />
</property>
<property name="createdOn" type="date">
<column name="CREATED_ON" length="7" not-null="true" />
</property>
<property name="createdBy" type="string">
<column name="CREATED_BY" length="35" not-null="true" />
</property>
<property name="updatedOn" type="date">
<column name="UPDATED_ON" length="7" not-null="true" />
</property>
<property name="updatedBy" type="string">
<column name="UPDATED_BY" length="35" not-null="true" />
</property>
<property name="passwordExpDuration" type="short">
<column name="PASSWORD_EXP_DURATION" not-null="true" />
</property>
<property name="address2" type="string">
<column name="ADDRESS2" length="50" />
</property>
<property name="companyName" type="string">
<column name="COMPANY_NAME" length="75" not-null="true" />
</property>
<property name="userPref" type="string">
<column name="USER_PREF" length="50" not-null="false" />
</property>
<property name="status" type="char">
<column name="STATUS" length="1" not-null="false" />
</property>
<property name="secQuesAns" type="string">
<column name="SEC_QUES_ANS" length="256" />
</property>
<set name="contentUserRoles" inverse="true" cascade="save-update, delete">
<key>
<column name="USER_NAME" length="35" not-null="true" />
</key>
<one-to-many class="com.mhhe.authoring.persistence.ContentUserRole" />
</set>
<set name="projects" inverse="true" table="USER_WORKING_PROJECTS">
<key>
<column name="USER_NAME" length="35" not-null="true" />
</key>
<many-to-many entity-name="com.mhhe.authoring.persistence.Project">
<column name="PROJECT_ID" precision="10" scale="0" not-null="true" />
</many-to-many>
</set>
<set name="userWorkingProjectses" inverse="true">
<key>
<column name="USER_NAME" length="35" not-null="true" />
</key>
<one-to-many class="com.mhhe.authoring.persistence.UserWorkingProjects" />
</set>
<set name="userCourses" inverse="true" cascade="save-update, delete">
<key>
<column name="USER_NAME" length="35" not-null="true" />
</key>
<one-to-many class="com.mhhe.authoring.persistence.UserCourse" />
</set>
</class>
</hibernate-mapping>



ContentUserRole.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 20, 2006 9:49:59 AM by Hibernate Tools 3.2.0.beta8 -->
<hibernate-mapping><meta attribute='extends'>com.mhhe.common.persistence.PersistentObject</meta>
<class name="com.mhhe.authoring.persistence.ContentUserRole" table="CONTENT_USER_ROLE">
<composite-id name="id" class="com.mhhe.authoring.persistence.ContentUserRoleId">
<key-property name="roleId" type="short">
<column name="ROLE_ID" precision="3" scale="0" />
</key-property>
<key-property name="userName" type="string">
<column name="USER_NAME" length="35" />
</key-property>
</composite-id>
<many-to-one name="contentRole" class="com.mhhe.authoring.persistence.ContentRole" update="false" insert="false" fetch="select">
<column name="ROLE_ID" precision="3" scale="0" not-null="true" />
</many-to-one>
<many-to-one name="contentUser" class="com.mhhe.authoring.persistence.ContentUser" update="false" insert="false" fetch="select">
<column name="USER_NAME" length="35" not-null="true" />
</many-to-one>
<property name="createdOn" type="date">
<column name="CREATED_ON" length="7" not-null="true" />
</property>
<property name="createdBy" type="string">
<column name="CREATED_BY" length="35" not-null="true" />
</property>
<property name="updatedOn" type="date">
<column name="UPDATED_ON" length="7" not-null="true" />
</property>
<property name="updatedBy" type="string">
<column name="UPDATED_BY" length="35" not-null="true" />
</property>
</class>
</hibernate-mapping>



MY DB STRUCTURE

CONTENT_USER Table

CREATE TABLE CONTENT_USER (
USER_NAME VARCHAR2 (35) NOT NULL,
PASSWORD VARCHAR2 (15) NOT NULL,
PASSWORD_UPDATED_ON DATE NOT NULL,
FIRST_NAME VARCHAR2 (30) NOT NULL,
MIDDLE_INITIAL CHAR (1),
LAST_NAME VARCHAR2 (30) NOT NULL,
ADDRESS VARCHAR2 (50) NOT NULL,
CITY VARCHAR2 (30) NOT NULL,
STATE VARCHAR2 (30) NOT NULL,
ZIP VARCHAR2 (12) NOT NULL,
BUS_PH_NUM VARCHAR2 (12) NOT NULL,
BUS_FAX_NUM VARCHAR2 (12),
E_MAIL VARCHAR2 (50) NOT NULL,
CREATED_ON DATE DEFAULT SYSDATE NOT NULL,
CREATED_BY VARCHAR2 (35) NOT NULL,
UPDATED_ON DATE DEFAULT SYSDATE NOT NULL,
UPDATED_BY VARCHAR2 (35) NOT NULL,
ADDRESS2 VARCHAR2 (50),
COMPANY_NAME VARCHAR2 (75) NOT NULL,
COUNTRY CHAR (3) NOT NULL,
USER_PREF VARCHAR2 (50),
STATUS CHAR (1) DEFAULT 'A' NOT NULL,
TYPE_ID NUMBER (2),
PASSWORD_EXP_DURATION NUMBER (2) NOT NULL,
ACCOUNT_EXP_DATE DATE,
SEC_QUES_ID NUMBER (3),
SEC_QUES_ANS VARCHAR2 (256),
CONSTRAINT PK_USERS
PRIMARY KEY ( USER_NAME ))
TABLESPACE USERS
PCTFREE 10 PCTUSED
INITRANS 1 MAXTRANS 255
STORAGE (
INITIAL 65536 NEXT PCTINCREASE
MINEXTENTS 1 MAXEXTENTS 2147483645 )
NOCACHE;

ALTER TABLE CONTENT_USER ADD CONSTRAINT FK_USER_EMP_TYPE
FOREIGN KEY (TYPE_ID)
REFERENCES NVLX.EMPLOYEE_TYPE (TYPE_ID) ;

ALTER TABLE CONTENT_USER ADD CONSTRAINT FK_USER_COUNTRY
FOREIGN KEY (COUNTRY)
REFERENCES NVLX.COUNTRY (CODE) ;

ALTER TABLE CONTENT_USER ADD CONSTRAINT FK_USER_SEC_QUES_ID
FOREIGN KEY (SEC_QUES_ID)
REFERENCES NVLX.SECURITY_QUESTION (QUES_ID) ;


2. CONTENT_ROLE Table

CREATE TABLE CONTENT_ROLE (
ROLE_ID NUMBER (3)
CONSTRAINT NN_ROLEID NOT NULL,
ROLE_DESC VARCHAR2 (30) NOT NULL,
CREATED_BY VARCHAR2 (35) NOT NULL,
CREATED_ON DATE DEFAULT SYSDATE NOT NULL,
UPDATED_ON DATE DEFAULT SYSDATE NOT NULL,
UPDATED_BY VARCHAR2 (35) NOT NULL,
CONSTRAINT PK_ROLES
PRIMARY KEY ( ROLE_ID ))
TABLESPACE USERS
PCTFREE 10 PCTUSED
INITRANS 1 MAXTRANS 255
STORAGE (
INITIAL 65536 NEXT PCTINCREASE
MINEXTENTS 1 MAXEXTENTS 2147483645 )
NOCACHE;


3. CONTENT_USER_ROLE Table

CREATE TABLE CONTENT_USER_ROLE (
CREATED_ON DATE DEFAULT SYSDATE NOT NULL,
CREATED_BY VARCHAR2 (35) NOT NULL,
UPDATED_ON DATE DEFAULT SYSDATE NOT NULL,
UPDATED_BY VARCHAR2 (35) NOT NULL,
ROLE_ID NUMBER (3) NOT NULL,
USER_NAME VARCHAR2 (35) NOT NULL,
CONSTRAINT PK_USER_ROLES
PRIMARY KEY ( USER_NAME, ROLE_ID ))
TABLESPACE USERS
PCTFREE 10 PCTUSED
INITRANS 1 MAXTRANS 255
STORAGE (
INITIAL 65536 NEXT PCTINCREASE
MINEXTENTS 1 MAXEXTENTS 2147483645 )
NOCACHE;

ALTER TABLE CONTENT_USER_ROLE ADD CONSTRAINT FK_USER_ROLE_USER
FOREIGN KEY (USER_NAME)
REFERENCES NVLX.CONTENT_USER (USER_NAME)
ON DELETE CASCADE;

ALTER TABLE CONTENT_USER_ROLE ADD CONSTRAINT FK_USER_ROLE_ROLE
FOREIGN KEY (ROLE_ID)
REFERENCES NVLX.CONTENT_ROLE (ROLE_ID) ;


Ultimately I need ContentUser and ContentRole Object as I am doing by "select" query (select cu, cr from ContentUser cu, ContentRole cr .....)

But I am confusing to get the combined cu and cr in the Criteria API



Thanks

Hi

There are two methods to achieve your requirment.

1. Using mapping file


You can use a set in ContentUser.hbm.xml many-to-many ContentRole.hbm.xml


and retrieve ContentUser from Criteria



2. You can get 2D array of ContentUser and ContentRole objects by using custom AliasToBeanResultTransformer.


I think the first one is easier.


Amila

(Don't forget to rate if helps)