More usable alternative to Criteria API

Question

Criteria has some advantages over using JPQL or raw SQL, as described in this answer: type safety; refactoring friendliness; less dependence on strings (but there still is some). And one very big disadvantage: they are less readable and simply ugly. Is there a (non-JPA) Java API for accessing relational databases which is both typesafe and readable?

Answer 1

Timo Westkämper has done a good job with QueryDSL. This library provides a DSL for querying different persistence providers (JPA, MongoDB, Lucene...).

But I often have used hand made solutions which simplifies most common queries (list an entity restricting some of its fields), preventing me to write always the same lines. For most complex queries I switched to unreadable and verbose Criteria API.

Answer 2

I have found "the ultimate" solution to a easier JPA search in the face of the following utility class : DynamicQueryBuilder

1. It gives meta model, so you won't need to describe relations using joins.

2. It searches by template pojo !!! Just put the values in a entity instance and they will be used as criteria !

3. It uses builder pattern so it is VERY readable !

   Bank bank = new Bank();
   bank.setId(12L);
   bank.setAchCode("1213");
   bank.setCbeCode("1234");
   bank.setStatus(new Lookups(1L));
   bank.setAchShortName("121");
   
   List<integer> ids = new ArrayList<integer>();
   ids.add(1);
   ids.add(2);
   ids.add(3);
   ids.add(4);
   ids.add(5);
   
   List<string> cbeCodes = new ArrayList<string>();
   cbeCodes.add("1111");
   cbeCodes.add("2222");
   
   DynamicQueryBuilder queryDyncBuilder1 =
   new DynamicQueryBuilder.Builder(null).select(bank).withOperType(Operator.OperType.AND).
   withAdvancedParam("cbeCode", LIKE, PERCENT_AROUND).withAdvancedParam("id", IN, ids)
   .withAdvancedParam("achCode", BETWEEN, cbeCodes).withAdvancedParam("achShortName", GT)
   .orderBy("id").orderBy("cbeCode", true).orderBy("status.code", true).build();
   
   System.out.println(queryDyncBuilder1.getQueryString());
   

If you run the above call the component will construct the following resulted JPQL query:

SELECT b 
FROM Bank b 
WHERE b.status = :status 
AND b.cbeCode LIKE :cbeCode 
AND b.achShortName > :achShortName 
AND b.id IN :id 
AND (b.achCode BETWEEN :achCodeFrom AND :achCodeTo) 
ORDER BY b.status.code DESC, b.id ASC, b.cbeCode DESC

Answer 3

MyBatis.

MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.

Or, as nobeh suggested: jOOQ.