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

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.

Answer 3

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 4

I have a project where I needed a sane way to put together lots of complex WHERE clauses dynamically, but I didn't need all the extra features of Hibernate and the attendant complexity that comes with that (like schema management & schema generation, secondary caches, bean mapping, object state tracking, etc etc etc). After finding this question I was still not satisfied with the alternatives, so I whipped this up instead:

public class Pred {

    private String opcode;

    private Pred left;

    private Pred right;

    private Pred() {
    }

    public Pred(String opcode, Pred left, Pred right) {
        super();
        this.opcode = opcode;
        this.left = left;
        this.right = right;
    }

    private static Collection<Pred> collect(Pred left, Pred ... right) {
        List<Pred> all = new ArrayList<>();
        all.add(left);
        all.addAll(Arrays.asList(right));
        return all;
    }

    private static Pred lambda(Supplier<String> out) {
        return new Pred() {
            @Override
            public String toSql() {
                return "(" + out.get() + ")";
            }
        };
    }

    private static Pred quoted(String constantValue) {
        return lambda(() -> "'" + constantValue + "'");
    }

    private static Pred unquoted(String constantValue) {
        return lambda(() -> constantValue);
    }

    public static Pred column(String constantValue) {
        return unquoted(constantValue);
    }

    public static Pred eq(String colname, String val) {
        return new Pred("=", column(colname), quoted(val));
    }

    public static Pred eq(String colname, Number val) {
        return new Pred("=", column(colname), unquoted(val.toString()));
    }

    public static Pred gt(String colname, Number val) {
        return new Pred(">", column(colname), unquoted(val.toString()));
    }

    public static Pred lte(String colname, Number val) {
        return new Pred("<=", column(colname), unquoted(val.toString()));
    }

    public static Pred lt(String colname, Number val) {
        return new Pred("<", column(colname), unquoted(val.toString()));
    }

    public static Pred gte(String colname, Number val) {
        return new Pred(">=", column(colname), unquoted(val.toString()));
    }

    public static Pred and(Collection<Pred> subpreds) {
        return lambda(() -> subpreds.stream().map(Pred::toSql).collect(Collectors.joining(" AND ")));
    }

    public static Pred and(Pred left, Pred ... right ) {
        return Pred.and(collect(left,right));
    }

    public static Pred or(Collection<Pred> subpreds) {
        return lambda(() -> subpreds.stream().map(Pred::toSql).collect(Collectors.joining(" OR ")));
    }

    public static Pred or(Pred left, Pred ... right ) {
        return Pred.or(collect(left,right));
    }

    public static <T extends Number> Pred in(String column, Collection<T> list) {
        return lambda(() -> column + " IN (" + list.stream().map(String::valueOf).collect(Collectors.joining(",")) + ")");
    }

    public String toSql() {
        return "(" + left.toSql() + opcode + right.toSql() + ")";
    }
}

I recommend using it in conjunction with spring-jdbc. Typical usage:

Pred pred = Pred.and(Pred.gt(VALIDUNTIL, cutoffTime.toEpochMilli()),
  Pred.lte(FIRSTSEEN, cutoffTime.toEpochMilli()),
  Pred.gt(JSON.ListPrice, minPrice),
  Pred.eq(JSON.Status, "Active"));
...
List<...> = jdbcTemplate.query("select ...  from ... where " + pred.toSql(), new BeanPropertyRowMapper(...);

Use at your own risk - there is no protection against SQL Injection (not a requirement in my project).

This was hastily written and I honestly wish someone would "borrow" it and turn it into a popular library :-)