Java, SQL and jOOQ. Best Practices and Lessons Learned from Writing Awesome Java and SQL Code. Get

Time 2021-09-01 15:29:31

Web Name: Java, SQL and jOOQ. Best Practices and Lessons Learned from Writing Awesome Java and SQL Code. Get

WebSite: http://blog.jooq.org

ID:191159

Keywords:

Lessons,Practices,Learned,

Description:

Compared to how heavy data is, applications and UIs come and go. Speaking of go, maybe you ll replace all of your Java code tomorrow for some go code. But you will keep the database if it isn t trivial. !-- Start the container in any phase before the actual code generation is required, i.e. at the latest in generate-sources -- phase generate-sources /phase goals goal execute /goal /goals configuration source db = new org.testcontainers.containers.PostgreSQLContainer( postgres:latest ) .withUsername( ${db.username} ) .withDatabaseName( postgres ) .withPassword( ${db.password} db.start(); // After you've started the container, collect its generated // JDBC URL (which contains a random port) project.properties.setProperty('db.url', db.getJdbcUrl()); /source /configuration /execution /executions dependencies dependency groupId org.testcontainers /groupId artifactId postgresql /artifactId version 1.15.2 /version /dependency /dependencies /plugin !-- Same phase as above, but the previous plugins have already executed, so we're generating the db post migration -- phase generate-sources /phase goals goal generate /goal /goals configuration jdbc !-- Again, this URL has been set by groovy, above -- url ${db.url} /url user ${db.username} /user password ${db.password} /password /jdbc generator database inputSchema public /inputSchema /database target packageName org.jooq.example.db /packageName /target /generator /configuration /execution /executions /plugin groupId org.apache.maven.plugins /groupId artifactId maven-surefire-plugin /artifactId configuration systemPropertyVariables !-- Again, this URL has been set by groovy, above -- db.url ${db.url} /db.url db.username ${db.username} /db.username db.password ${db.password} /db.password /systemPropertyVariables /configuration /plugin There are many ways to achieve the same thing, this is one of them that works decently out of the box. You can check out a full example from github here, and play around with it:If your legacy JPA application is using occasional native queries or Hibernate @Formula or Spring Data @Query annotation with vendor specific native SQL embedded in it, you can use jOOQ s parsing connection and parsing data source to translate between dialects, without having to go all in on your jOOQ adoption though I think it s inevitable once you see what jOOQ can do for you.em.createNativeQuery( select a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name from t_author as a order by a.id , Author.class) .getResultList();The above runs just fine and produces all the authors in MariaDB, which implemented support for Oracle s NVL() function. But what about Oracle itself? The query fails on Oracle with:This is because in Oracle, you can t use the AS keyword to alias tables, only to alias columns. Sure, you can remove that, but what about NVL()? You want this to work on MySQL and SQL Server, as well, but they complain:Use jOOQ to generate the SQL string for you, using the DSLUse JPQL instead of a native query (but then heavily rewrite it, because JPQL is much less powerful than SQL)Try your luck writing actual vendor agnostic SQL, manuallyOr You could use jOOQ s parsing connection, which acts as a proxy to your actual connection, intercepting every SQL statement on the JDBC level in order to translate it to the target dialect.This is as simple as wrapping your existing JDBC Connection or DataSource as follows:That s it! I mean, you could pass some additional configuration Settings after the dialect, but that s about as easy as it gets. The new DataSource can now run your SQL query on all the aforementioned dialects, e.g. you might see this in your DEBUG logs:-- org.jooq.impl.ParsingConnection Translating from: select 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: select 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: select a.id, ifnull(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: select 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: select 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: select a.id, coalesce(a.first_name, 'N/A') first_name, a.last_name from author a order by a.idA quick win in Hibernate when you want to project additional values, akin to SQL s own computed columns, which are available in many SQL dialects, is the @Formula annotation, which can be added to any entity, like so. Assuming this additional column:But unfortunately, there are still so many RDBMS that don t actually support boolean types, and the @Formula annotation is purely static, and doesn t allow for vendor-specific overrides. Are we going to rewrite that SQL query manually to make sure a SQL-92, vendor agnostic native SQL fragment is present that works on all dialects? 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 author jpaauthorw0_ where jpaauthorw0_.id=?-- org.jooq.impl.ParsingConnection Translating from: ...]-- org.jooq.impl.ParsingConnection Translation cache miss: ...]-- org.jooq.impl.ParsingConnection Translating to: select 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 author as jpaauthorw0_ where jpaauthorw0_.id = ?As you can see, jOOQ re-added the AS keyword to alias the on MySQL for good measure, because we like to be explicit about aliasing, and because that s the default for Settings.renderOptionalAsKeywordForTableAliases 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 author jpaauthorw0_ where jpaauthorw0_.id=?-- org.jooq.impl.ParsingConnection Translating from: ...]-- org.jooq.impl.ParsingConnection Translation cache miss: ...]-- org.jooq.impl.ParsingConnection Translating to: select 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 end formula1_0_ from author jpaauthorw0_ where jpaauthorw0_.id = ?A NULL-safe BOOLEAN type emulation (because if YEAR_OF_BIRTH is NULL (i.e. UNKNOWN), then MILLENIAL must be NULL, too (i.e. UNKNOWN))Another case where native SQL can appear in JPA integrations is Spring Data JPA with its @Query annotation, especially when used with @Query(nativeQuery = true). Just like Hibernate s @Formula, this annotation is compile-time static, with no way to override the value of the native query at runtime, short of maybe subtyping repositories per dialect.But why go through all that hassle. It s always the same thing. Just patch the DataSource with jOOQ s parsing connection or parsing data source, and you re set.Even if you re not using jOOQ s DSL API, you can profit from jOOQ in many ways in your existing JDBC, R2DBC, JPA, MyBatis, etc. based applications by hooking in the jOOQ parsing connection and translating your vendor specific input dialect to any number of configurable output dialects. .derive(ParseListener.onParseCondition(ctx - { if (ctx.parseFunctionNameIf( LOGICAL_XOR )) { ctx.parse('('); Condition c1 = ctx.parseCondition(); ctx.parse(','); Condition c2 = ctx.parseCondition(); ctx.parse(')'); return CustomCondition.of(c - { switch (c.family()) { case MARIADB: case MYSQL: c.visit(condition( {0} xor {1} , c1, c2)); break; default: c.visit(c1.andNot(c2).or(c2.andNot(c1))); break; // Let the parser take over if we don't know the token return null; .dsl() .parser() .parseQuery( select * from t where logical_xor(t.a = 1, t.b = 2) System.out.println(DSL.using(SQLDialect.MYSQL).render(query));System.out.println(DSL.using(SQLDialect.ORACLE).render(query));So, profit from using jOOQ to migrate your application s vendor specific SQL off one RDBMS onto another, or to support multiple RDBMS products in a single application, with or without using jOOQ s DSL!One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code.Sometimes, however, an imperative 3GL is better suited for a given task. That s where stored procedures shine, or more specifically, procedural languages of RDBMS.BigQueryDb2ExasolFirebirdHANAHSQLDBInformixMariaDBMySQLOraclePostgreSQLSQL ServerVerticaUsing these statements via jOOQ may not be your every day use-case. You may prefer managing that logic via the native syntax, which is still more powerful than what jOOQ 3.15 supports (especially when you re using Oracle s PL/SQL), in case of which you ll u se jOOQ purely to call your procedure from Java in the usual type safe manner.You re a product vendor, and you profit from procedural logic being vendor agnostic in order to support multiple of your clients RDBMSYour procedural logic is dynamic, just like your SQL logic (and what other than jOOQ to use for that?)You don t have the necessary privileges to create procedures, functions, or triggers in your schemaVariable Integer i = variable(unquotedName( i ), INTEGER);Table ? t = table(unquotedName( t ));Field Integer col = field(unquotedName( col ), INTEGER);ctx.begin( declare(i).set(1), while_(i.le(10)).loop( insertInto(t).columns(c).values(i), i.set(i.plus(1))).execute();It produces the necessary emulations, if required, because regrettably, not all dialects support FOR:Of course, this particular SQL statement would be better implemented using a single bulk insertion statement, purely with SQL, not with procedural logicIf you have the necessary privileges, and your procedural logic isn t super dynamic, you may choose to store your logic in a procedure or function directly in your database. In some databases, this means a compiler will be able to eagerly translate the logic to something very efficient (e.g. machine code), instead of interpreting the logic on the fly.Take the above WHILE loop, for example. You may want to store that as a procedure P:And now, what better way to call this procedure than, again, an anonymous block?If you re using jOOQ in Flyway or Liquibase to generate procedures during your database migrations, you can obviously generate jOOQ procedure stubs to call in a more type safe manner, instead of the above dynamic procedure call.This jOOQ feature is not really exceptional. You can play around with our parser / translator here: https://www.jooq.org/translate. It can definitely help you translate your (simpler) stored procedures between dialects, such as PL/SQL, T-SQL, PL/pgSQL, etc.As a rule of thumb, if you can do it with SQL (the 4GL), do it with SQL alone. But sometimes, you can t. A 3GL is a better choice for an algorithm. When using jOOQ, you ll naturally think of using Java to implement that 3GL algorithm. But wait, you could move the logic to the server for (drastically) increased performance!Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into t values (2)' at line 2 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1333) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2106) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1243) at org.jooq.testscripts.JDBC.main(JDBC.java:34)We can t chain statements like that, unless we turn on allowMultiQueries=true in the JDBC connection URL:And now, suddenly, the statement batch completes normally, and two records are inserted into the table. The security feature helps prevent some SQL injection vulnerabilities. It is now much harder to append additional statements in case you have the bad idea of concatenating values to your string, such as:Because, what if value contained the string "1); drop table t;"? It would be syntactically correct, so it would execute as expected. That wouldn t be very nice.Don t get a false sense of security now. Turning off this capability will not prevent all SQL injection vulnerabilities. Just make this particular one harder. There are still various ways this particular lack of using a bind variable can lead to an attacker reading your data, e.g. through a time-based attack.The risk of SQL injection needs to be taken seriously. The best thing is to always write static SQL with bind variables (e.g. PreparedStatement, stored procedures, or jOOQ), or a SQL builder like jOOQ for dynamic SQL.When using jOOQ, the above situation is very unlikely to happen. The default usage of jOOQ is using:Only in rare cases would you use plain SQL templating to work around a specific lack of functionality in jOOQ, and in that case, the templating language will help you avoid concatenating strings and running into SQL injection vulnerabilities.So, the MySQL flag isn t really useful for your jOOQ usage. In fact, it s even a problem, because jOOQ internally relies on generating statement batches as the above. Here are some features that don t work correctly when you turn off allowMultiQueries=false (most of these also apply to MariaDB, btw):Whenever you use GROUP_CONCAT in jOOQ on MySQL, jOOQ assumes you haven t already changed MySQL s default value for @@group_concat_max_length. The default value is extremely low, namely 1024. And not only does that value prevent the string aggregation of larger data sets, it just fails silently, which produces wrong values!When emulating JSON_ARRAYAGG() in MySQL using GROUP_CONCAT, there s usually a detectable syntax error in the resulting JSON array, but this isn t the case when you just want to produce some string values, e.g. a comma separated list. (See this previous blog why we don t use the native JSON_ARRAYAGG() support yet).So, what jOOQ does every time you use GROUP_CONCAT explicitly (or jOOQ uses it internally for some emulation), jOOQ will prepend and append the following statements:If you already fixed the system or session variable yourself, you can turn off this feature by changing the Settings.renderGroupConcatMaxLenSessionVariable flag.Many SQL dialects have a CREATE OR REPLACE syntax for stored procedures, functions, triggers, and other stored objects that do not contain data. It s very useful syntax sugar for writing this, instead:But again, if you turn off allowMultiQueries=false, then this emulation in jOOQ won t work and you get a syntax error again. There s nothing jOOQ can do here for you. You d have to manually run the two statements, instead of using the convenience syntax.Many dialects have a FOR UPDATE WAIT n syntax that allows for specifying a WAIT timeout for pessimistic locks, e.g.Many procedural languages support anonymous blocks of procedural code, i.e. procedural code that is not stored in a procedure. It makes perfect sense. After all, we don t have to store all of our SQL in views either, so why do we have to store our PL/SQL, T-SQL, PL/pgSQL, etc. in a stored procedure? This can be very useful especially when you want to generate these blocks dynamically, using jOOQ to run some logic on the server rather than the client, reducing round trips.This translates to and executes the correct procedural anonymous blocks in those dialects that support them, but MySQL 8.0.26 unfortunately doesn t, yet, so what do we do? We generate an anonymous procedure, call it, and drop it again:I mean, why not. But again, this relies on allowMultiQueries=true, otherwise, the JDBC driver will reject this statement.MySQL s JDBC driver has a nice security feature that is intended to prevent some cases of SQL injection, especially when users use the JDBC connection for manual SQL execution. There s always that one poor soul on the team that doens t know about SQL injection yet, and thus gets it wrong, opening pandora s box. For those usages, allowMultiQueries=false is a reasonable default.When using jOOQ as jOOQ was intended to be used, SQL injection is much less likely. This excludes plain SQL templating usages, in case of which this article doesn t apply, though. On the other hand, jOOQ internally relies on allowMultiQueries=true to enable a few emulations that require multiple statements to be executed in a single round trip. A future jOOQ version will allow for configuring the execution model for multi queries, such that the above can be executed as multiple round trips. See #9645 for more details.jOOQ has been around for a while since around 2009 as a publicly available library, and since 2013 as a commercially licensed product.A lot of things have happened in 12 years. Here are 10 things that you maybe didn t know about jOOQ.Naming public API! The original jOOQ had methods like these to construct predicates:But words such as greaterOrEqual() and others are kinda heavy in the middle of what is supposed to be a very readable, SQL style DSL, so a short version needed to be added. But what to name the short version? There are different opinions.Since I love XSLT almost as much as I love SQL, it was natural to choose eq, ne, lt, le, gt, ge for these operators, just like in XSLT. Well, XPath, actually, to be precise. These are also available as HTML entities.Fun fact, starting with jOOQ, we support also parsing these abbreviations in the SQL parser to support alternative Teradata syntax, see https://github.com/jOOQ/jOOQ/issues/11844 (yes, the almighty Teradata!)Other comparable libraries use eq, ne, lt, loe, gt, goe. But I could never sleep at night knowing the inconsistent length of names of these very similar operators.Alas, there s no way these terms are ever going to be changed without breaking every jOOQ application out there for no good reason. Such is life of an API developer.Oh, if only Java were more like TypeScript. They have these beautiful first-class untagged union types, which we poor Java folks only know from exception catch blocks, where they don t exist as a first-class language feature, but just as syntactic sugar:If just we had those in Java (along with the above type aliases). Then all the troubles of maintaining a vast API like jOOQ would be gone, namely the ever present set of overloaded methods.And that isn t covering all possible permutations, by far. A pragmatic decision was made that it is not too likely for the first argument to be a string bind variable. We hardly ever support Name unless the argument is really about a column reference, not an arbitary column expression, and the scalar subquery case (Select ? extends Record1 T>>) well, that s just convenience.Where F is the above union type. This would add a lot of convenience to a DSL like jOOQ because now, every permutation of argument types is supported. Alas, here we are, hand rolling the union type emulation through heavily overloaded methods, hoping IDE auto-completion doesn t fail or become too slow (hello IntelliJ / Kotlin)

TAGS:Lessons Practices Learned 

<<< Thank you for your visit >>>

Best Practices and Lessons Learned from Writing Awesome Java and SQL Code. Get some hands-on insight on what's behind developing jOOQ.

Hot Websites