Some basic questions on Criteria from JPA 2.0

Question

I discovered JPA 2.0 Criteria API today and want to learn it. Just went through some examples and try to do a hands on. I have a table fruit with columns:

• id,
• name,
• color,
• size,
• taste.

The regular stuff:

EntityManagerFactory emf = Persistence.createEntityManagerFactory("fruitManager");
EntityManager em = emf.createEntityManager();

//get the criteria builder
CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<Fruit> c = cb.createQuery(Fruit.class);

1. How will the following query get built using criteria:

   select id, name, color where name like 'XY%' and  color='orange'
   

   I.e. how to:

   • fetch only some columns,
   • include wild card search,
   • include AND, OR where conditions.
     
     
2. Also, its always better to get count of the result first before fetching the actual data. How can I fetch the result count first?

Also Is there any place where I can get more examples on 'Criteria' with several different kinds of SQL queries?

Answer 1

First, you operate to Entities, not to tables, so I will assume following mapping for Fruit:

@Entity
public class Fruit {
    @Id int id;
    String name;
    String color;
}

When result consists of separate columns, type argument to query is Object[].class (result will be List of Object[]). Other possibility is to use Tuple. You can build query you described with following. Just for being clear about type of argument for the sake of example, intermediate variables are created for Predicates and ParameterExpressions. You can also inline those to the query creation.

CriteriaQuery<Object[]> myquery = cb.createQuery(Object[].class);
Root<Fruit> root = myquery.from(Fruit.class);

ParameterExpression<String> nameParamExp = cb.parameter(String.class, "name");
ParameterExpression<String> colorParamExp = cb.parameter(String.class, "color");

Predicate namePredicate = cb.like(root.<String>get("name"), colorParamExp);
Predicate colorPredicate = cb.equal(root.get("color"), nameParamExp);

myquery.multiselect(root.get("id"), root.get("name"), root.get("color"))
        .where(cb.and(namePredicate, colorPredicate));

TypedQuery<Object[]> someFruits = em.createQuery(myquery);
someFruits.setParameter("name", "XY%");
someFruits.setParameter("color", "orange");

someFruits.getResultList();

You can also build same query by inlining everything:

myquery.multiselect(root.get("id"), root.get("name"), root.get("color"))
        .where(cb.and(
                cb.like(root.<String>get("name"), "XY%"),
                cb.equal(root.get("color"), "orange")));

Or use Tuple as result type:

CriteriaQuery<Tuple> myquery = cb.createQuery(Tuple.class);
Root<Fruit> root = myquery.from(Fruit.class);

myquery.select(cb.tuple(root.get("id").alias("id"),
                        root.get("name").alias("name"),
                        root.get("color").alias("color")))
       .where(cb.and(
                cb.like(root.<String>get("name"), "XY%"),
                cb.equal(root.get("color"), "orange")));

TypedQuery<Tuple> someFruits = em.createQuery(myquery);

for (Tuple t: someFruits.getResultList()) {
    //access your results by alias set in query instead of using array index
    Integer id = t.get("id", Integer.class);
    String name = t.get("name", String.class);
    String color = t.get("color", String.class);
}

If you go for canonical metamodel, then you need following class in same package with your fruit. It is up to you do you write it by yourself or generate with some tool (for example with org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor). First time it makes sense to write it by yourself:

@StaticMetamodel(Fruit.class)
public abstract class Fruit_ {
    public static volatile SingularAttribute<Fruit, Integer> id;
    public static volatile SingularAttribute<Fruit, String> color;
    public static volatile SingularAttribute<Fruit, String> name;
}

Then you can go for strongly typed arguments and replace query in former tuple example with following:

myquery.select(cb.tuple(root.get(Fruit_.id).alias("id"),
                        root.get(Fruit_.name).alias("name"),
                        root.get(Fruit_.color).alias("color")))
       .where(cb.and(
                cb.like(root.get(Fruit_.name), "XY%"),
                cb.equal(root.get(Fruit_.color), "orange")));