Criteria API retrieve pairs SELECT i, b FROM ...

Joerg.Schoemer · **Joined:** Thu Jan 03, 2008 8:39 am **Posts:** 5

Hi all,

I want to retrieve pairs with criteria...
like the following HQL statement:

Code:

SELECT i, b FROM Item i left join i.bids b WHERE b.amount > 100

is this generally possible?

Best regards
Joerg

Cameron McKenzie · **Posted:** Wed Jun 18, 2008 9:21 pm

Is it possible? That's the whole point of the Hibernate Criteria API.

Here's a little tutorial I put together on the Criteria API. It'll really help you understand Criteria quickly, with lots of simple examples.

Here's an example of a Criteria query that searches for a user with a LoginName of mj, and a password of abc123. I think you can easily see what's going on.

Code:

public static void main(String[] args) {

  User user = new User();
  user.setLoginName("mj");
  user.setPassword("abc123");
  Example example = Example.create(user);
  Session session = HibernateUtil.beginTransaction();
  Criteria criteria = 
                 session.createCriteria(User.class);
  criteria.add(example);

  User u = (User)criteria.uniqueResult();
  System.out.println(u);
}

You'll probably be best to use the Criteria Restriction class. Here's some sample code that does just that. The code gets all users who have an id of greater than (gt) 2 and less than (lt) 8, and a non-null emailAddress. It's real easy!

Code:

public static void main(String args[]) {
  Session session = HibernateUtil.beginTransaction();
  Criterion c1 = Restrictions.gt("id", (long)2);
  Criterion c2 = Restrictions.lt("id", (long)8);
  Criterion c3 = Restrictions.isNotNull("emailAddress");
  User user = new User();
  user.setEmailAddress(".com");
  Example c4 = Example.create(user);
  c4.enableLike(MatchMode.END);
  c4.ignoreCase();
  Criteria criteria = session.createCriteria(User.class);
  criteria.add(c1);
  criteria.add(c2);
  criteria.add(c3);
  criteria.add(c4);
  List results = criteria.list();
  HibernateUtil.commitTransaction();
  for (int i = 0; i<results.size(); i++) {
    System.out.println(results.get(i).toString());
  }

Both examples were from the tutorial I mentioned.

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi

Joerg.Schoemer · **Joined:** Thu Jan 03, 2008 8:39 am **Posts:** 5

Hi Cameron,
thanks for your reply.

The major problem what I want to be solved is the possibility to fetch several objects like I can do in HQL

Code:

SELECT i, b ...

The criteria API only provides createCriteria(Class clazz) on a single class and retrieves a list of this class.
But, what I want to retrieve is a List<Object[]>.
The example above was not quite good, lets try again

Code:

SELECT u, a, s 
    FROM User u 
        LEFT JOIN u.addresses a 
        LEFT JOIN u.shipments s 
    WHERE 
        a.street = s.street
        AND a.zip = s.zip

With the criteria API it would look like this

Code:

Criteria usersCrit = session.createCriteria(User.class);
usersCrit.createAlias("addresses", "a");
usersCrit.createAlias("shipments", "s");
usersCrit.add(Restrictions.eqProperty("a.street", "s.street");
List<User> users = usersCrit.list();

Now I have to search within the shipments and the addresses of the users returned by the list() for equality of zip and street.
In HQL I'll get an Object[] containing the user, address and shipment without searching.

How can I do this with the criteria API?

Best regards
Joerg

Joerg.Schoemer · **Joined:** Thu Jan 03, 2008 8:39 am **Posts:** 5

In the meanwhile if've found the solution.

Criteria.ALIAS_TO_ENTITY_MAP

Code:

Criteria usersCrit = session.createCriteria(User.class);
usersCrit.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
usersCrit.createAlias("addresses", "a");
usersCrit.createAlias("shipments", "s");
usersCrit.add(Restrictions.eqProperty("a.street", "s.street");
List<Map> users = usersCrit.list();

And it works fine :-))