Simpler criteria query like JOOQ DSL

[trajano]

One of the reasons I like JPQL is it simplifies the work needed to translate to SQL in my head.

But it does introduce the notion of adding another language inside another language. Plus that language was designed for reporting https://x.com/unclebobmartin/status/1917410469150597430

Anyway I know we have the criteria API, but that doesn't translate easily to SQL when reading through it.

I was thinking maybe we can have an API like JOOQ DSL to reduce the boiler plate

Using ChatGPT I just asked it to create a comparison of SQL, JPQL, JPA Criteria API and JOOQ

SELECT id, name, age
FROM users
WHERE age > 18;

JPQL API

String jpql = "SELECT u FROM User u WHERE u.age > :age";
List<User> adults = em.createQuery(jpql, User.class)
                      .setParameter("age", 18)
                      .getResultList();

Criteria API

var cb = em.getCriteriaBuilder();
var cq = cb.createQuery(User.class);
var user = cq.from(User.class);
cq.select(user)
  .where(cb.gt(user.get(User_.age), 18));

List<User> adults = em.createQuery(cq).getResultList();

JOOQ

import static com.example.jooq.Tables.USERS;

DSLContext dsl = DSL.using(dataSource, SQLDialect.POSTGRES);

List<UserRecord> adults = dsl
    .select(USERS.ID, USERS.NAME, USERS.AGE)
    .from(USERS)
    .where(USERS.AGE.gt(18))
    .fetchInto(UserRecord.class);

So given the choices, JPQL for me is the most favorable.

Conceptual idea

So my conceptual idea is rather than having CriteriaBuilder, CriteriaQuery we manage everything through a dsl() method and let the code generator generate similar API to JOOQ

List<UserRecord> adults = em.dsl()
    .from(UserRecord.class)
    .where(User_.age.gt(18))
    .stream() //returns a stream
    .toList()

or

Optional<UserRecord> adults = em.dsl()
    .from(UserRecord.class)
    .where(User_.age.gt(18))
    .fetch() // returns an optional (I intentionally excluded something that would return null

[gavinking]

@trajano

Look, the criteria API isn't perfect, but it's about as good as you can get given the following design constraints:

1. allow multiple root entities in the query, and
2. allow multiple implementations.

JOOQ, naturally, doesn't have to deal with the verbosity of cb.gt(user.get(User_.age), 18) because it has only one implementation.

And your proposal doesn't allow multiple roots. And your JOOQ example is also not using multiple roots. (It's possible in JOOQ, but it's more verbose.)

So, yes, relaxing these requirements leads to a much more streamlined API. And that's why we've already introduced precisely such an API in Jakarta Data spec.

I don't think that Persistence should introduce an API that competes with Data.