Utilizing jOOQ to jot down vendor agnostic SQL with JPA’s native question or @System – Java, SQL and jOOQ.

In case your legacy JPA software is utilizing occasional native queries or Hibernate @System or Spring Information @Question annotation with vendor particular native SQL embedded in it, you should utilize jOOQ’s parsing connection and parsing information supply to translate between dialects, with out having to go all in in your jOOQ adoption – although I believe it’s inevitable when you see what jOOQ can do for you.

Now, let’s design a desk like this:

CREATE TABLE writer (
  id INT NOT NULL,
  first_name TEXT,
  last_name TEXT NOT NULL,

  CONSTRAINT pk_author PRIMARY KEY (id)
);

Now, chances are you’ll need to write a local question on this desk utilizing JPA’s EntityManager.createNativeQuery(), mapping that to entities. You would be utilizing jOOQ’s DSL API for this, however let’s say you’re not prepared for a migration to jOOQ but, otherwise you need to use precise SQL as offered by your DBA, not jOOQ’s DSL.

So, in MariaDB, you may be writing one thing like this:

Checklist<Writer> consequence =
em.createNativeQuery("""
    choose a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
    from t_author as a
    order by a.id
    """, Writer.class)
  .getResultList();

The place your entity is outlined like this:

@Entity
@Desk(identify = "writer")
public class Writer {
    @Id
    public int id;

    @Column(identify = "first_name")
    public String firstName;

    @Column(identify = "last_name")
    public String lastName;

    // Constructors, getters, setters, equals, hashCode, and so on
}

The above runs simply superb and produces all of the authors in MariaDB, which carried out assist for Oracle’s NVL() operate. However what about Oracle itself? The question fails on Oracle with:

ORA-00933: SQL command not correctly ended

It’s because in Oracle, you may’t use the AS key phrase to alias tables, solely to alias columns. Positive, you may take away that, however what about NVL()? You need this to work on MySQL and SQL Server, as effectively, however they complain:

MySQL

SQL Error [1305] [42000]: FUNCTION check.nvl doesn’t exist

SQL Server

SQL Error [195] [S0010]: ‘nvl’ is just not a acknowledged built-in operate identify.

Now, you could have these choices:

• Use jOOQ to generate the SQL string for you, utilizing the DSL
• Use JPQL as a substitute of a local question (however then closely rewrite it, as a result of JPQL is way much less highly effective than SQL)
• Attempt your luck writing precise vendor agnostic SQL, manually
• Or…

jOOQ’s parsing connection

You would use jOOQ’s parsing connection, which acts as a proxy to your precise connection, intercepting each SQL assertion on the JDBC degree as a way to translate it to the goal dialect.

This is so simple as wrapping your current JDBC Connection or DataSource as follows:

DataSource originalDataSource = ...
DataSource parsingDataSource = DSL
    .utilizing(originalDataSource, dialect)
    .parsingDataSource();

That’s it! I imply, you can cross some extra configuration Settings after the dialect, however that’s about as simple because it will get. The brand new DataSource can now run your SQL question on all of the aforementioned dialects, e.g. you may see this in your DEBUG logs:

On MySQL:

-- org.hibernate.SQL
   choose a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name 
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translating from:
   choose a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translation cache miss: 
   choose a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translating to: 
   choose a.id, ifnull(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id

On SQL Server:

-- org.hibernate.SQL
   choose a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name 
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translating from:
   choose a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translation cache miss: 
   choose a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection] Translating to: 
   choose a.id, coalesce(a.first_name, 'N/A') first_name, a.last_name 
   from writer a 
   order by a.id

Hibernate was tricked by jOOQ! The NVL operate was translated to MySQL’s IFNULL or SQL Server COALESCE, and the AS key phrase was faraway from the SQL Server question. These are simply easy examples, your precise SQL could also be far more advanced. Mess around with the function set on-line, right here.

Additionally, the Settings.cacheParsingConnectionLRUCacheSize flag, which defaults to 8192, makes positive the identical question doesn’t get re-translated on a regular basis so that you gained’t spend an excessive amount of time in jOOQ’s parser.

@System too, not simply native queries

A fast win in Hibernate once you need to challenge extra values, akin to SQL’s personal computed columns, which can be found in lots of SQL dialects, is the @System annotation, which could be added to any entity, like so. Assuming this extra column:

ALTER TABLE writer ADD year_of_birth INT;

We’d have the next amended entity:

@Entity
@Desk(identify = "writer")
public class Writer {
    @Id
    public int id;

    @Column(identify = "first_name")
    public String firstName;

    @Column(identify = "last_name")
    public String lastName;

    @Column(identify = "year_of_birth")
    public Integer yearOfBirth;

    @System("year_of_birth between 1981 and 1996")
    public Boolean millenial;

    // Constructors, getters, setters, equals, hashCode, and so on
}

However sadly, there are nonetheless so many RDBMS that don’t really assist boolean varieties, and the @System annotation is only static, and doesn’t permit for vendor-specific overrides. Are we going to rewrite that SQL question manually to ensure a SQL-92, vendor agnostic native SQL fragment is current that works on all dialects?

Or will we simply plug in jOOQ’s parsing connection once more? Let’s attempt the latter with:

Writer writer = em.discover(Writer.class, 1);

The MySQL log comprises:

-- org.hibernate.SQL
   choose 
     jpaauthorw0_.id as id1_4_0_,
     jpaauthorw0_.first_name as first_na2_4_0_, 
     jpaauthorw0_.last_name as last_nam3_4_0_, 
     jpaauthorw0_.year_of_birth between 1981 and 1996 as formula1_0_ 
   from writer jpaauthorw0_ 
   the place jpaauthorw0_.id=?
-- org.jooq.impl.ParsingConnection Translating from: [...]
-- org.jooq.impl.ParsingConnection Translation cache miss: [...]
-- org.jooq.impl.ParsingConnection Translating to: 
   choose 
     jpaauthorw0_.id as id1_4_0_, 
     jpaauthorw0_.first_name as first_na2_4_0_, 
     jpaauthorw0_.last_name as last_nam3_4_0_, 
     jpaauthorw0_.year_of_birth between 1981 and 1996 as formula1_0_ 
   from writer as jpaauthorw0_ 
   the place jpaauthorw0_.id = ?

As you may see, jOOQ re-added the AS key phrase to alias the on MySQL for good measure, as a result of we prefer to be specific about aliasing, and since that’s the default for Settings.renderOptionalAsKeywordForTableAliases

Whereas the SQL Server log comprises:

-- org.hibernate.SQL 
   choose 
     jpaauthorw0_.id as id1_4_0_, 
     jpaauthorw0_.first_name as first_na2_4_0_, 
     jpaauthorw0_.last_name as last_nam3_4_0_, 
     jpaauthorw0_.year_of_birth between 1981 and 1996 as formula1_0_ 
   from writer jpaauthorw0_ 
   the place jpaauthorw0_.id=?
-- org.jooq.impl.ParsingConnection Translating from: [...]
-- org.jooq.impl.ParsingConnection Translation cache miss: [...]
-- org.jooq.impl.ParsingConnection Translating to: 
   choose
     jpaauthorw0_.id id1_4_0_, 
     jpaauthorw0_.first_name first_na2_4_0_, 
     jpaauthorw0_.last_name last_nam3_4_0_, 
     case 
       when jpaauthorw0_.year_of_birth between 1981 and 1996 
         then 1 
       when not (jpaauthorw0_.year_of_birth between 1981 and 1996) 
         then 0 
     finish formula1_0_ 
   from writer jpaauthorw0_ 
   the place jpaauthorw0_.id = ?

A NULL-safe BOOLEAN sort emulation (as a result of if YEAR_OF_BIRTH is NULL (i.e. UNKNOWN), then MILLENIAL have to be NULL, too (i.e. UNKNOWN))

Spring Information @Question annotation

One other case the place native SQL can seem in JPA integrations is Spring Information JPA with its @Question annotation, particularly when used with @Question(nativeQuery = true). Similar to Hibernate’s @System, this annotation is compile-time static, with no approach to override the worth of the native question at runtime, wanting possibly subtyping repositories per dialect.

However why undergo all that trouble. It’s at all times the identical factor. Simply patch the DataSource with jOOQ’s parsing connection or parsing information supply, and also you’re set.

Conclusion

Even when you’re not utilizing jOOQ’s DSL API, you may revenue from jOOQ in some ways in your current JDBC, R2DBC, JPA, MyBatis, and so on. based mostly purposes by hooking within the jOOQ parsing connection and translating your vendor particular enter dialect to any variety of configurable output dialects.

If jOOQ’s parser can’t deal with a function, likelihood is, you may work round this limitation utilizing the ParseListener SPI, e.g. once you need to assist a hypothetical LOGICAL_XOR predicate (which is supported natively in MySQL):

Question question = configuration
    .derive(ParseListener.onParseCondition(ctx -> {
        if (ctx.parseFunctionNameIf("LOGICAL_XOR")) {
            ctx.parse('(');
            Situation c1 = ctx.parseCondition();
            ctx.parse(',');
            Situation c2 = ctx.parseCondition();
            ctx.parse(')');

            return CustomCondition.of(c -> {
                change (c.household()) {
                    case MARIADB:
                    case MYSQL:
                        c.go to(situation("{0} xor {1}", c1, c2));
                        break;
                    default:
                        c.go to(c1.andNot(c2).or(c2.andNot(c1)));
                        break;
            });
        }

        // Let the parser take over if we do not know the token
        return null;
    })
    .dsl()
    .parser()
    .parseQuery(
        "choose * from t the place logical_xor(t.a = 1, t.b = 2)"
    );
  
System.out.println(DSL.utilizing(SQLDialect.MYSQL).render(question));
System.out.println(DSL.utilizing(SQLDialect.ORACLE).render(question));

The above program will print:

-- MYSQL:
choose * 
from t
the place (t.a = 1 xor t.b = 2);

-- ORACLE:
choose * 
from t 
the place (t.a = 1 and never (t.b = 2)) or (t.b = 2 and never (t.a = 1));

So, revenue from utilizing jOOQ emigrate your software’s vendor particular SQL off one RDBMS onto one other, or to assist a number of RDBMS merchandise in a single software, with or with out utilizing jOOQ’s DSL!

Aspect be aware: Question transformation

This isn’t the subject of this weblog submit, however upon getting jOOQ parse your each SQL assertion, you can too use jOOQ to remodel this SQL and tamper with the expression tree, for instance by implementing shopper facet row degree safety. The chances are infinite!