Hibernate Community • View topic - Please help from HQL to Criteria

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Please help from HQL to Criteria

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

superkei

Post subject: Please help from HQL to Criteria

Posted: Tue Aug 07, 2007 12:05 am

Newbie

Joined: Mon Aug 06, 2007 11:58 pm
Posts: 4

query="select distinct p from User as p " +
"JOIN fetch p.Office o JOIN fetch p.UserRoles r JOIN fetch r.SystemRole s " +
"where " +
"lower(p.staffNo) LIKE :staffNo " +
"AND lower(p.badgeNo) LIKE :badgeNo " +
"AND p.Office.officeId = :officeid "+
"AND s.roleId = :roleid "+
"AND ( lower(p.lastName) LIKE :name "+
"OR lower(p.firstName) LIKE :name) "+
"order by o.officeName"

I tried many many days but cannot convert this HQL to Criteria.

Please help ! thx

Top

thatmikewilliams

Post subject:

Posted: Tue Aug 07, 2007 4:52 am

Expert

Joined: Fri Jul 13, 2007 8:18 am
Posts: 370
Location: london

This was as close as I could get:

Code:

      Criteria c = s.createCriteria(User.class)
      .createAlias("office", "o")
      .add(Restrictions.ilike("staffNo", "%staffNo%"))
      .add(Restrictions.ilike("badgeNo", "%badgeNo%"))
      .add(Restrictions.or(
            Restrictions.ilike("firstName", "%name%"),
            Restrictions.ilike("lastName", "%name%")))
      .createCriteria("userRoles")
      .createCriteria("systemRole")
      .add(Restrictions.eq("roleId", new Long(1)))
      .add(Restrictions.eq("o.officeId", new Long(1)))
      .addOrder(Order.asc("o.officeName"))
      .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
      ;

Differences:
- distinct applied in java instead of in the database
- collections lazy populated BUT will contain all items

Top

superkei

Post subject:

Posted: Tue Aug 07, 2007 9:39 am

Newbie

Joined: Mon Aug 06, 2007 11:58 pm
Posts: 4

However, if i use Criteria.DISTINCT_ROOT_ENTITY),

it causes error when i use these for paging purpose
criteria.setFirstResult(offset);
criteria.setMaxResults(size);

any idea? thx

thatmikewilliams wrote:

This was as close as I could get:

Code:

      Criteria c = s.createCriteria(User.class)
      .createAlias("office", "o")
      .add(Restrictions.ilike("staffNo", "%staffNo%"))
      .add(Restrictions.ilike("badgeNo", "%badgeNo%"))
      .add(Restrictions.or(
            Restrictions.ilike("firstName", "%name%"),
            Restrictions.ilike("lastName", "%name%")))
      .createCriteria("userRoles")
      .createCriteria("systemRole")
      .add(Restrictions.eq("roleId", new Long(1)))
      .add(Restrictions.eq("o.officeId", new Long(1)))
      .addOrder(Order.asc("o.officeName"))
      .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
      ;

Differences:
- distinct applied in java instead of in the database
- collections lazy populated BUT will contain all items

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum