ANTI JOIN is a very useful operator from relational algebra. Regrettably, only few dialects support it in terms of SQL syntax, as we’ve written earlier. In jOOQ, you can write it as follows: If your RDBMS supports this natively (e.g. ClickHouse, Databricks), then it is rendered as such. Otherwise, jOOQ will translate this to: But … Continue reading Simplifying ANTI…
#jooq
125 posts
7 May
27 Mar
One of jOOQ’s most popular feature is the out-of-the-box debug logging experience. jOOQ developers find this feature very useful when developing their applications. Assuming you run a jOOQ query and configure your logger to print DEBUG log output: When this query is executed, your log output might contain something like this: Executing query : select … Continue reading Managing Sensitive…
28 Feb 2025
Every product manager knows this situation: This is such a common pattern, and while it’s perfectly understandable for such a user to request this, it is so terribly wrong to give in to this user’s request. Why is it wrong? The features are unrelated Most of the time, the two features X1 and X2 are … Continue reading Resisting the…
6 Dec 2023
jOOQ’s DAO API is one of jOOQ’s most controversial features. When it was first implemented, it was implemented merely: There’s a strong hint about the third bullet given how popular Spring Data’s repository “pattern” is. A lot of developers just want to quickly fetch and store data, without giving individual queries much thought. A fun … Continue reading To DAO…
28 Jun 2023
Java’s package private visibility is an underrated feature. When you omit any visibility modifier in Java, then the default (for most objects) is package private, i.e. the object is visible only to types in the same package: In fact, a compilation unit (the .java file) can contain multiple such classes. You don’t have to create … Continue reading How to…
21 Jun 2023
Introduction In this article, we are going to see how we can fetch multiple to-many relationships with jOOQ MULTISET so that we avoid bumping into an unintentional Cartesian Product. The approach taken by jOOQ is truly revolutionary since it offers a solution that allows you to fetch multiple to-many relationships using a single type-safe query. Domain Model Let’s assume that…
25 May 2023
Introduction In this article, we are going to see how we can achieve cache synchronization with the help of jOOQ and PostgreSQL functions. By using Change Data Capture, we can track how table records change over time and synchronize the application-level cache entries that were built from the table records in question. Domain Model Let’s assume we are building a…
19 Apr 2023
Introduction In this article, we are going to see what is the best way to call SQL Server stored procedures with jOOQ. I decided to write this article because stored procedures and database functions are extremely useful for data-intensive applications, and sometimes, they are the only solution to process data efficiently. While SQL remains the de-facto way to query data,…
2 Mar 2023
jOOQ 3.15 introduced the concept of an ad-hoc converter, a converter that is applied “ad-hoc” to a single query. It uses the same underlying mechanisms as any ordinary Converter that is attached to generated code for use in every query. An example of such an ad-hoc converter is this: While there are other ways to … Continue reading How to…
24 Feb 2023
One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: In jOOQ: And that’s pretty much it. The question usually arises from the fact that there’s a surprising lack of type safety when working … Continue reading How to…
17 Jan 2023
jOOQ already has a LoggingConnection (see also the manual), which acts as a JDBC proxy Connection to log all SQL statements that are executed by any JDBC client (including Hibernate, MyBatis, JdbcTemplate, native JDBC, etc.). Starting from jOOQ 3.18.0, 3.17.7, and 3.16.13, a LoggingConnection is now also available for R2DBC clients to log all reactive … Continue reading jOOQ’s R2DBC…
8 Dec 2022
A frequently encountered doubt people have when using jOOQ is to decide when a “complex” query should be written using jOOQ API vs. when it should be implemented using native SQL. The jOOQ manual is full of side by side examples of the same query, e.g. Using jOOQ: Using native SQL: In the native SQL … Continue reading When to…
21 Oct 2022
Using jOOQ’s code generator to call stored procedures is a popular reason to use jOOQ. For example, when you have a procedure like the following Oracle PL/SQL procedure: jOOQ will generate code for you to call very simply, like this: This will execute the following, taking care of binding all IN and OUT parameters for … Continue reading Calling Procedures…
13 Sept 2022
Starting with jOOQ 3.11, type safe implicit JOIN have been made available, and they’ve been enhanced to be supported also in DML statements in jOOQ 3.17. Today, I’d like to focus on a somewhat weird but really powerful use-case for implicit JOIN, when joining additional tables from within an explicit JOIN‘s ON clause. The use … Continue reading Using jOOQ’s…
6 Sept 2022
For new users working with jOOQ for the first time, the number of types in the jOOQ API can be overwhelming. The SQL language doesn’t have many such “visible” types, although if you think about SQL the way jOOQ does, then they’re there just the same, but hidden from users via an English style syntax. … Continue reading A Brief…
30 Aug 2022
I’ve just stumbled upon this great post by Vlad Mihalcea, titled The Best Way to Fetch a Spring Data JPA DTO Projection. It got some nice traction on reddit, too. This is such a nice use-case and apt solution, I wanted to quickly show the second best way of doing the same, with jOOQ this … Continue reading The Second…
Starting with jOOQ 3.16 and #12601, there may be a compilation error with a message like this in your jOOQ generated code: [ERROR] …/DefaultCatalog.java:[53,73] cannot find symbol[ERROR] symbol: variable VERSION_3_17[ERROR] location: class org.jooq.Constants Typically, this error is mixed with other compilation errors in generated code. Its purpose is to help troubleshoot these other compilation errors. … Continue reading Cannot resolve…
25 Aug 2022
Since jOOQ 3.11, implicit joins have been supported. An implicit join is a JOIN (mostly a LEFT JOIN) that is generated implicitly because of the presence of a path expression. If SQL supported the syntax natively, it would look like this: All that is is convenience for a bunch of explicitly written LEFT JOIN expressions: … Continue reading jOOQ 3.17…
24 Aug 2022
Starting with jOOQ 3.17, the Condition type extends the Field<Boolean> type. Because, that’s what the SQL standard thinks it is, in sorts: The exact definition contains intermediate rules, but you get the idea. A <predicate> (which is a Condition in jOOQ) can be used wherever a <boolean value expression> can be used, which again can … Continue reading A Condition…
23 Aug 2022
Probably the hardest thing to standardise in SQL is RETURNING data from DML statements. In this article, we’ll look at various ways of doing that with jOOQ, in many of jOOQ’s supported dialects, and with JDBC directly. How to do it with jOOQ Assuming the usual table from the sakila database: jOOQ took syntactic inspiration … Continue reading The Many…
22 Aug 2022
When you write stored procedures and functions in your database, you want to ensure their correctness, just like with your Java code. In Java, this is done with unit tests, typically with JUnit. For example, if you have the following code in Java: Then, you might write a test like this: But how do we … Continue reading How to…
19 Aug 2022
The H2 database is an immensely popular in-memory database product mostly used by Java developers for testing. If you check out the DB-Engines ranking, it ranks 50th, which is quite impressive, as this rank outperforms products like: CockroachDB Ignite Single Store (previously MemSQL) Interbase (which was forked as Firebird) Ingres (which is a predecessor to … Continue reading Using H2…
28 Jul 2022
jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it for stored procedures (possibly exclusively for stored procedures). Stored procedures are powerful ways of moving complex data processing logic to the server. This … Continue reading The Best…
30 Jun 2022
One of jOOQ 3.17‘s coolest new features are client side computed columns. jOOQ 3.16 already added support for server side computed columns, which many of you appreciate for various reasons. What’s a computed column? A computed column is a column that is derived (“computed”) from an expression. It cannot be written to. It works like … Continue reading Create Dynamic…
10 Jun 2022
I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ’s MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, … Continue reading How to…
9 Jun 2022
It’s been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long overdue and which I promised on twitter a few times is to run a few benchmarks comparing the performance of various approaches to nesting to-many relationships with jOOQ. This article will … Continue reading The Performance…
31 May 2022
Every now and then, I see folks lament the SQL syntax’s peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical order of operations (FROM .. SELECT) Most recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let’s look at why jOOQ didn’t fall into this trap of trying … Continue reading Changing SELECT…
9 May 2022
A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ. In the past, I’ve blogged about the powerful MULTISET operator many times, which allows for nesting collections in jOOQ. This time, instead of nesting data into a List<UserType>, why not nest it in … Continue reading How to…
28 Apr 2022
Since jOOQ 3.4, we have an API that simplifies transactional logic on top of JDBC in jOOQ, and starting from jOOQ 3.17 and #13502, an equivalent API will also be made available on top of R2DBC, for reactive applications. As with everything jOOQ, transactions are implemented using explicit, API based logic. The implicit logic implemented … Continue reading Nested Transactions…
1 Mar 2022
A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: The following is how you could fetch a value from this sequence, using jOOQ, assuming you’re using the code generator: The sequence expression translates to a variety of dialects: You can also embed the S.nextval() … Continue reading How to…
22 Feb 2022
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we’re using the sakila database for this example. What would be a possible way to solve this with SQL (for example, PostgreSQL, to be … Continue reading Use MULTISET…
21 Feb 2022
A long standing feature request has seen little love from the jOOQ community, despite a lot of people probably wanting it. It goes by the unimpressive title Let Table<R> extend SelectField<R>: https://github.com/jOOQ/jOOQ/issues/4727 What does the feature mean, specifically? The awesome PostgreSQL Let’s have a look at a really cool PostgreSQL feature. In PostgreSQL, it is … Continue reading Projecting Type…
2 Feb 2022
Starting from jOOQ 3.16, we’re investing a lot into opening up our internal query object model (QOM) as a public API. This is mainly useful for people who use jOOQ’s parser and wish to access the parsed expression tree, or to transform SQL, e.g. to implement row level security in jOOQ. But occasionally, even with … Continue reading Traversing jOOQ…
28 Jan 2022
A while ago, jOOQ has added the org.jetbrains:annotations dependency to the jOOQ API, in order to annotate return types with nullability information. For example, the entire DSL is non-nullable: It makes sense to give this guarantee especially to kotlin users, as they can get rid of some of the more complex types involving things like … Continue reading Detect Accidental…
7 Jan 2022
One of the coolest things about using and making jOOQ is that we get to discover the best extensions to the standard SQL language by vendors, and add support for those clauses in jOOQ via emulations. One of these syntaxes is BigQuery’s * EXCEPT syntax. Everyone who ever wrote ad-hoc SQL queries would have liked … Continue reading The Useful…
5 Jan 2022
3.16.0 Release with a new Public Query Object Model API, Spatial Support, YugabyteDB Support and Much More
jOOQThis release tackles two long standing and complex feature requests that usershave asked us to offer for a long time: a public API for manipulating jOOQ’squery object model (QOM), and spatial support. New Query Object Model (QOM) Every jOOQ query is modeled as an expression tree constructed via our intuitiveDSL. For some use-cases there exist … Continue reading 3.16.0 Release…
16 Dec 2021
Starting from Spring Boot 2.5, there’s a handy new callback that you can implement, called DefaultConfigurationCustomizer, where the word DefaultConfiguration corresponds to jOOQ’s DefaultConfiguration. You can simply create a class like this in your project: The above callback receives the DefaultConfiguration at its initialisation stage, during which you can still safely mutate it to change … Continue reading How to…
9 Dec 2021
In classic SQL (i.e. before jOOQ’s awesome MULTISET operator), nested collections were fetched using ordinary (outer) joins. An example of such a query would be a query running against the sakila database to fetch actors and their films. Using jOOQ: The result from the jOOQ debug log would look something like this: +--------+----------+---------+-------+---------------------+ |actor_id|first_name|last_name|film_id|title | … Continue reading Using JDK…
15 Nov 2021
The upcoming jOOQ 3.16 will finally offer support for the various RDBMS GIS extensions via issue #982. This is great news per se, and will be covered in a future blog post, when the integration is ready. This post here is about something else. Adding support for such a feature is a great source of … Continue reading Fun with…
19 Oct 2021
jOOQ’s parser can’t parse every possible SQL syntax. Try this random PostgreSQL syntax: And the jOOQ parser will complain: DOMAIN, INDEX, SCHEMA, SEQUENCE, SESSION, TABLE, TYPE, or VIEW expected: [1:7] ALTER [*]SYSTEM RESET ALL That’s perfectly fine. The goal of the jOOQ parser isn’t to understand all vendor specific syntax. The goal is to offer … Continue reading The jOOQ…
27 Aug 2021
Database first is at the core of jOOQ’s design. jOOQ has been made primarily for classic systems the database is always there and always has been and will never leave. This is because we think “data have mass” This not only translates to moving logic closer to the data (see our previous posts about the … Continue reading Using Testcontainers…
26 Aug 2021
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 … Continue reading Using jOOQ…
23 Aug 2021
MySQL’s JDBC connector has a security feature called allowMultiQueries, which defaults to false. When turned off, it prevents using a useful, but potentially dangerous feature in MySQL via JDBC: By default, the above produces a syntax error: Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds … Continue reading MySQL’s allowMultiQueries…
20 Aug 2021
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. 1. eq, ne, gt, ge, lt, le are inspired by XSLT … Continue reading 10 Things…
27 Jul 2021
It’s been almost 1 year now since jOOQ 3.14 was released in October 19, 2020 with SQL/JSON (and SQL/XML) support. Half a year later, we’ve released jOOQ 3.15 with MULTISET support, which builds on top of these features to offer type-safe nested collections, the way every ORDBMS should implement them. Building (dogfooding) on top of … Continue reading Standard SQL/JSON…
20 Jul 2021
jOOQ 3.15 shipped with a ton of new features, the most important ones being: MULTISET support (type safe, nested collections) Reactive SQL support via R2DBC A very useful, lesser known new feature is “ad-hoc data type conversion”. Data type converters and bindings have been around in jOOQ for a long time. Their goal is to … Continue reading Ad-hoc Data…
15 Jul 2021
One of the biggest new features of the recently released jOOQ 3.15 is its new support for reactive querying via R2DBC. This has been a highly popular feature request, and we finally delivered on it. You can continue using jOOQ the way you were used to, providing you with type safe, embedded SQL in Java, … Continue reading Reactive SQL…
6 Jul 2021
This is how SQL should have been used all along. They called it The Third Manifesto, ORDBMS, or other things. Regrettably, it never really took off. Because most vendors didn’t adopt it. And those who did, didn’t agree on syntax. But this is about to change. Thanks to the now ubiquitous SQL/JSON support (which jOOQ … Continue reading jOOQ 3.15’s…
11 Mar 2021
When paginating results in SQL, we use standard SQL OFFSET .. FETCH or a vendor specific version of it, such as LIMIT .. OFFSET. For example: As always, we’re using the Sakila database for this example. This is rather straightforward. It will give us page 2 out of N pages, with a page size of … Continue reading Calculating Pagination…
15 Feb 2021
I’ve run across a fun little trick to simulate latency in your development environments when testing some SQL queries. Possible use-cases including to validate that backend latency won’t bring down your frontend, or that your UX is still bearable, etc. The solution is PostgreSQL and Hibernate specific, though to doesn’t have to be. Besides, it … Continue reading Simulating Latency…
4 Feb 2021
Over the past 13 years, jOOQ has accrued quite some internal features, which you, the user, are not exposed to. One very interesting feature is the capability for any arbitrary jOOQ expression tree element to push a SQL fragment up to a higher level. How does it work? The jOOQ expression tree model When you … Continue reading jOOQ Internals:…
20 Oct 2020
jOOQ 3.14 has been released with support for SQL/XML, SQL/JSON, Kotlin code generation, embeddable types, and domain types, synthetic constraints, better MERGE support, and more SQL transformations. In this release, we’ve sorted our github issues according to user feedback and finally implemented some of the most wanted features, which include better Kotlin support, embeddable types, … Continue reading jOOQ 3.14…
13 Oct 2020
jOOQ has supported one of JPQL’s most cool features for a while now: implicit joins. Using jOOQ, you can navigate your to-one relationships in a type safe way, generating LEFT JOIN operations implicitly without the effort of having to keep thinking about join predicates, and the correct join order. Consider this Sakila database query here, … Continue reading Using jOOQ…
9 Oct 2020
One of the main features of ORMs is M as in Mapping. Libraries like jOOQ help auto-mapping flat or nested database records onto Java classes that have the same structure as the SQL result set. The following has always been possible in jOOQ, assuming PostgreSQL’s INFORMATION_SCHEMA (using the generated code from the jOOQ-meta module): The … Continue reading Nesting Collections…
3 Apr 2020
One of jOOQ’s biggest strength is the fact that it is a type safe SQL API. “Type safe”, in this context, means that every object that you put in a jOOQ query has a well defined type, such as: Condition Field Table These can be used in jOOQ in a type safe way as such: … Continue reading What’s a…
6 Mar 2020
When using jOOQ to create dynamic SQL statements (one of jOOQ’s core value propositions), it is often necessary to add query elements conditionally, with a default “No-op” behaviour. For first time users, this default “no-op” behaviour is not always obvious as the jOOQ API is vast, and as with any vast API, there are many … Continue reading Create Empty…
5 Mar 2020
Most jOOQ users use the jOOQ DSL API, which provides compile time type safety and an easy way to write dynamic SQL. But occasionally, this DSL get in the way, because it might be In such cases, you can still benefit from jOOQ’s many secondary features, including for example its nice integration with the Stream … Continue reading Using Java…
4 Mar 2020
jOOQ supports a vast amount of SQL syntax out of the box. As such, most users will not think of resorting to string concatenation like in the old days when writing dynamic SQL with JDBC. But every now and then, a vendor specific feature is not supported by jOOQ (yes, it happens). In that case, … Continue reading Never Concatenate…
25 Feb 2020
Deprecation notice After encountering numerous problems building the jOOQ-refaster module in various JDK versions and after receiving no feedback from the community about this feature, we have decided to remove it again in jOOQ 3.15: https://github.com/jOOQ/jOOQ/issues/10803 Starting with jOOQ 3.13, we’re offering a new module called jOOQ Refaster, which provides refaster templates for automatic API … Continue reading Use the…
14 Feb 2020
jOOQ 3.13 has been released with CockroachDB support, much more API and tooling for DDL management, and SQL:2011 temporal table support Starting with this release, we will further embrace our support for parsing, translating, executing, and now also interpreting DDL statements. The driving force is better code generation support, but in the future, also better … Continue reading jOOQ 3.13…
5 Sept 2019
Quantified comparison predicates One of SQL’s weirdes features are quantified comparison predicates. I’ve hardly ever seen these in the wild: The above example is equivalent to using the much more readable IN predicate: This equivalence is defined in the SQL standard. There are more esoteric cases that could be solved using such quantified comparison predicates … Continue reading Quantified LIKE…
29 Aug 2019
jOOQ 3.12 has been released with a new procedural language API, new data types, MemSQL support, formal Java 11+ support, a much better parser, and reactive stream API support In this release, we’ve focused on a lot of minor infrastructure tasks, greatly improving the overall quality of jOOQ. We’ve reworked some of our automated integration … Continue reading jOOQ 3.12…
26 Jun 2019
Spring Boot is great to get started very quickly with what the Spring Boot authors have evaluated to be useful defaults. This can be a lot of help when you’re doing things for the first time, and have no way to copy paste working Maven pom.xml files from existing projects, for example. When working with … Continue reading How to…
16 Apr 2019
One of the most wanted features in the Oracle database is the BOOLEAN type. The SQL standard specified it a while ago, and RDBMS like PostgreSQL show how powerful it can be, e.g. when using the EVERY() aggregate function. Before you move on reading this article, note that starting with Oracle 23c, the standard BOOLEAN … Continue reading Calling an…
14 Mar 2019
When configuring a jOOQ runtime Configuration, you may add an explicit Settings instance, which contains a set of useful flags that change jOOQ’s SQL generation behaviour and other things. Example settings include: Object qualification (generate schema.table.column or just table.column) Identifier style (to quote or not to quote) Keyword style (UPPER, lower, or Pascal Case for … Continue reading How to…
7 Dec 2018
Annotation processors can be useful as a hacky workaround to get some language feature into the Java language. jOOQ also has an annotation processor that helps validate SQL syntax for: Plain SQL usage (SQL injection risk) SQL dialect support (prevent using an Oracle only feature on MySQL) You can read about it more in detail … Continue reading How to…
22 Nov 2018
Reporting a bug takes time, and trust me, every vendor appreciates your reporting of a bug! Your voice counts as many voices, for all the other customers of a product who do not want to or cannot take the time to report the same bug are numerous. So, first off, thanks for taking that time … Continue reading How to…
5 Nov 2018
While jOOQ is not a full fledged ORM (as in an object graph persistence framework), there is still some convenience available to avoid hand-writing boring SQL for every day CRUD. That’s the UpdatableRecord API. It has a few very useful features, including: A 1:1 mapping to the underlying table Every UpdatableRecord is mapped on a … Continue reading How to…
10 Apr 2018
In a previous blog post, I’ve shown how the programmatic MockDataProvider can be used to mock the entire JDBC API through a single functional interface: Writing the provider manually can be tedious in some cases, especially when a few static SQL strings need to be mocked and constant result sets would be OK. In that … Continue reading Mocking JDBC…
13 Mar 2018
The SQL standard is a nice thing. But it’s a standard. And as such, while it may provide good guidelines on how to implement some SQL features, most dialects deviate from it in one way or another (sometimes drastically, cheers, MySQL). But that doesn’t have to be a bad thing. Innovation is not driven by … Continue reading Top 10…
12 Jan 2018
jOOQ’s main value proposition is obvious: Type safe embedded SQL in Java. People who actively look for such a SQL builder will inevitably stumble upon jOOQ and love it, of course. But a lot of people don’t really need a SQL builder – yet, jOOQ can still be immensely helpful in other situations, through its … Continue reading Top 5…
20 Nov 2017
After the successful release of JDK 9, we can already look forward, and play around with early access releases of JDK 10. The list of JEPs currently targeted for JDK 10 is quite manageable so far. JEP 286 is probably the most exciting one for most Java developers: Local variable type inference (which we’ve blogged … Continue reading Using JDK…
25 Jul 2017
jOOQ has been around for a while now (since 2009!) and by now we can say we’ve seen quite a bit of things about the SQL and Java languages. Some of our design decisions are particular in the way jOOQ thinks about programming with SQL. These include: Nullability (let’s stop fighting it) Value types (let’s … Continue reading 5 Things…
24 Jul 2017
In recent months, there had been some really exciting news from the MySQL team: (Recursive) Common Table Expressions in MySQL Introducing Window Functions These two SQL standard language features are among the most powerful SQL features that are available from most other databases. I frequently include them in conference talks about SQL (see my article … Continue reading jOOQ 3.10…
13 Jul 2017
You know JDBC, right? It’s that really easy, concise API that we love to use to work with virtually any database, relational or not. It has essentially three types that you need to care about: Connection Statement (and its subtypes) ResultSet All the other types some sort of utilities. Now, with the above three, we … Continue reading How I…
28 Jun 2017
In this article, I will establish how the SQL language and its implementations distinguish between changed values and modified values, where a changed value is a value that has been “touched”, but not necessarily modified, i.e. the value might be the same before and after the change. Many ORMs, unfortunately, either update all of a … Continue reading ORMs Should…
9 Jun 2017
It was hard to limit ourselves to 10 Nice Examples of Writing SQL in Kotlin With jOOQ, recently, because the Kotlin language has many nice little features that really help a lot when working with Java libraries. We’ve talked about the nice with() stdlib function, which allows to “import” a namespace for a local scope … Continue reading Using Kotlin’s…
18 May 2017
Kotlin is the next big thing. With Google announcing official support for Kotlin on Android, we’ll see a lot more traction for this lovely language. After today's #kotlin announcement, we're betting this tag will blow up just like Swift did. #io17. https://t.co/whOcJlJfWe pic.twitter.com/2QKEJIRYVi — Stack Overflow (@StackOverflow) May 17, 2017 We’ve already blogged about the … Continue reading 10 Nice…
16 Jan 2017
Typesafe embedded DSLs like jOOQ are extremely powerful for dynamic SQL, because the query you’re constructing with the jOOQ DSL is a dynamic query by nature. You’re constructing a query expression tree using a convenient API (the “DSL”), even if you think your SQL statement is static. For instance: The above query looks like a … Continue reading A Functional…
10 Jan 2017
SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance: The above function creates a cross … Continue reading jOOQ 3.10…
6 Jan 2017
One of jOOQ‘s most powerful features is the capability of introducing custom data types, pretending the database actually understands them. For instance, when working with SQL TIMESTAMP types, users mostly want to use the new JSR-310 LocalDateTime, rather than the JDBC java.sql.Timestamp type. In jOOQ 3.9+, this is a no brainer, as we’ve finally introduced … Continue reading How to…
5 Jan 2017
In a recent consulting gig, I was analysing a client’s connection pool issue in a productive system, where during some peak loads, all the Java processes involving database interactions just started queueing up until nothing really worked anymore. No exceptions, though, and when the peak load was gone in the evening, everything returned back to … Continue reading How to…
5 Dec 2016
As long as we allow ourselves to write string-based dynamic SQL embedded in other programming languages like Java, we will have a certain risk of being vulnerable to SQL injection. That’s a fact. Don’t believe it? Check out this website exposing all vulnerabilities on Stack Overflow for PHP questions: https://laurent22.github.io/so-injections In a previous blog post, … Continue reading Prevent SQL…
8 Nov 2016
Some of the biggest limitations when working with Oracle PL/SQL from Java is the lack of support for a variety of PL/SQL features through the JDBC interface. This lack of support is actually not limited to JDBC, but also extends to Oracle SQL. For instance, if you’re using the useful PL/SQL BOOLEAN type as such: … Continue reading Use jOOQ…
10 Aug 2016
Pagination is one of those things that almost everyone gets wrong for two reasons: Here’s why. What’s wrong with pagination? Most applications blindly produce pagination like this: This is how GMail implements pagination. With my current settings, it displays 100 E-Mails at a time and also shows how many E-Mails there are in total, namely … Continue reading Why Most…
7 Jul 2016
Catchy headline, yes. But check out this Stack Overflow question by user Mike: (I’m duplicating it here on the blog, as it might be deleted soon) It’s a pretty open ended question. I’ll be starting out a new project and am looking at different ORMs to integrate with database access. Do you have any favorites? … Continue reading “What Java…
19 Apr 2016
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. We have the pleasure … Continue reading jOOQ Tuesdays:…
8 Feb 2016
One of the most awesome features of the Oracle database is Oracle AQ: Oracle Database Advanced Queuing. The AQ API implements a full fledged, transactional messaging system directly in the database. In a classic architecture where the database is at the center of your system, with multiple applications (some of which written in Java, others … Continue reading Using Oracle…
28 Jan 2016
Just now, we implemented a nice little feature in jOOQ’s code generator: https://github.com/jOOQ/jOOQ/issues/4974 It detects whenever the jOOQ code generator runs a slow query to reverse engineer schema meta information. Why? In our development and integration test environment, we don’t have huge schemas with all the different performance edge cases put in place. For instance, … Continue reading How to…
14 Jan 2016
Notice that the examples in this article may be outdated, as Typesafe’s Activator works differently now. The blog post will not be maintained to provide up-to-date Activator examples. We’re very happy to continue our a guest post series on the jOOQ blog by Manuel Bernhardt. In this blog series, Manuel will explain the motivation behind … Continue reading Reactive Database…
30 Dec 2015
Like many old databases, Oracle has legacy data types, which are rather nasty to work with in every day SQL. Usually, you don’t run into wild encounters of LONG and LONG RAW data types anymore, but when you’re working with an old database, or with the dictionary views, you might just have to deal with … Continue reading Oracle LONG…
30 Sept 2015
Exciting times are ahead for Java/SQL developers. SQL is strong and popular as ever. It is the right language for relational databases just as much as for Big Data storage systems. Just this week, Pivotal has announced their open sourcing HAWQ and MADlib as a part of their Hadoop Native SQL strategy. At Data Geekery, … Continue reading Let’s Meet…
17 Jun 2015
Before you read on, please note that since jOOQ 3.19, policies are supported out of the box, to implement the same functionality in a much simpler way than what this article suggests! Some time ago, we’ve promised to follow up on our Constraints on Views article with a sequel showing how to implement client-side row-level … Continue reading Implementing Client-Side…
9 Apr 2015
You’re not into the functional mood yet? Then the title might not resonate with you – but the article will! Trust me. Essentially, we want this: +------+------+------+ | col1 | col2 | col3 | +------+------+------+ | A | B | C | row 1 | D | E | F | row 2 | G … Continue reading How to…
7 Apr 2015
Sometimes you simply cannot avoid it: Pessimistic locking via SQL. In fact, it’s an awesome tool when you want to synchronise several applications on a shared, global lock. Some may think this is abusing the database. We think use the tools you have if they can solve the problem you have. For instance, the RDBMS … Continue reading How to…
24 Mar 2015
Hibernate has become a de-facto standard in the Java ecosystem, and after the fact, also an actual JavaEE standard implementation if standards matter to you, and if you put the JCP on the same level with ISO, ANSI, IEEE, etc. This article does not intended to discuss standards, but visions. Hibernate shares JPA’s vision of … Continue reading jOOQ vs.…
10 Mar 2015
Introduction Grails is a web framework aimed to boost development productivity. One of the main features is domain centric database schema generation. Applications built with Grails are able to update existing schema just before they start. To do this, Grails is using built-in domain mappers or migrations in more advanced cases. The goal of the … Continue reading Integrating jOOQ…
2 Mar 2015
We’ve published an article in the German magazine www.java-aktuell.de, which is published by the iJUG e.V.. You can read and download the article free of charge from our blog! In Java gibt es kein Standard-API, das die Ausdrucksstärke und Mächtigkeit von SQL direkt unterstützt. Alle Aufmerksamkeit ist auf objekt-relationales Mapping und andere höhere Abstraktionslevel gerichtet, … Continue reading jOOQ –…
24 Feb 2015
Every framework introduces a new compromise. A compromise that is introduced because the framework makes some assumptions about how you’d like to interact with your software infrastructure. An example of where this compromise has struck users recently is the discussion “Are Slick queries generally isomorphic to the SQL queries?“. And, of course, the answer is: … Continue reading jOOQ vs.…
6 Jan 2015
Some databases are awesome enough to implement the MEDIAN() aggregate function. Remember that the MEDIAN() is sligthly different from (and often more useful than) the MEAN() or AVG() (average). While the average is calculated as the SUM(exp) / COUNT(exp), the MEDIAN() tells you that 50% of all values in the sample are higher than the … Continue reading How to…
30 Dec 2014
Sometimes when aggregating data with SQL, we’d love to add some additional filters. For instance, consider the following world bank data: GDP per capita (current US$) 2009 2010 2011 2012 CA 40,764 47,465 51,791 52,409 DE 40,270 40,408 44,355 42,598 FR 40,488 39,448 42,578 39,759 GB 35,455 36,573 38,927 38,649 IT 35,724 34,673 36,988 33,814 … Continue reading The Awesome…
14 Nov 2014
We’ve recently encountered this interesting use-case on the jOOQ user group. How do you discover all primary keys of your schema via the jOOQ API? There are two ways: Using the generated meta data Using runtime meta data Let’s see how it works: Using the generated meta data This is straightforward. If you’re using Java … Continue reading jOOQ Tip…
7 Nov 2014
If you’re using a commercial database or PostgreSQL / Firebird / CUBRID, you will be able to take advantage of the full power of window functions. We’ve blogged about window functions’ awesomeness a couple of times, in particular about ROW_NUMBER(), RANK(), DENSE_RANK(). Today, we’re going to look into some awesome window functions that produce values … Continue reading Don’t Miss…
2 Oct 2014
More and more people are catching up with the latest update to our platform by adopting functional programming also for their businesses. At Data Geekery, we’re using Java 8 for our jOOQ integration tests, as using the new Streams API with lambda expressions makes generating ad-hoc test data so much easier. However, we don’t feel … Continue reading Don’t Miss…
30 Sept 2014
We’ve been in business for more than one year now with our dual-licensing strategy for jOOQ. While this strategy has worked very well for us, it has also been a bit of a challenge for some of our customers. Today, we’re going to show you what caveats of dual-licensing we’ve run into. Our dual-licensing strategy … Continue reading The Caveats…
22 Sept 2014
What do people do when they run out of topics? They recycle previous topics and create top 10 lists. Here is a list of the top 10 most popular articles from the jOOQ blog: Top 10 Very Very VERY Important Topics to Discuss A fun, not so serious parody on what is being discussed on reddit’s … Continue reading Top…
8 Sept 2014
This recent question on Stack Overflow made me think. Why does jOOQ suggest to put generated code under “/target” and not under “/src”? … and I’m about to give you the final answer to “Where to Put Generated Code?” This isn’t only about jOOQ Even if you’re not using jOOQ, or if you’re using jOOQ … Continue reading Look no…
5 Sept 2014
ERD (Entity Relationship Diagrams) are a great way of designing and visualising your database model. There is a variety of vendors offering free and commercial ERD tools. Vertabelo by E-Point is a SaaS product where you can design and manage your database schema online. For instance, the jOOQ example database can be modelled as such: … Continue reading Stop Manually…
26 Aug 2014
Introduction jOOQ is a great framework when you want to work with SQL in Java without having too much ORM in your way. At the same time, it can be integrated into many environments as it is offering you support for many database-specific features. One such database-specific feature is partitioning in PostgreSQL. Partitioning in PostgreSQL … Continue reading Integrating jOOQ…
14 Aug 2014
Have you ever wondered about the use-case behind SQL’s ANY (also: SOME) and ALL keywords? You have probably not yet encountered these keywords in the wild. Yet they can be extremely useful. But first, let’s see how they’re defined in the SQL standard. The easy part: 8.7 <quantified comparison predicate> Function Specify a quantified comparison. … Continue reading A Wonderful…
11 Aug 2014
There are those people that have a strong, dogmatic belief in what they call “Free” or “Standard” or “Open” software. One of those individuals is Jimmie (let’s call him Jimmie in this article) who has responded to an article about Java persistence by Marco Behler on TheServerSide. Let me cite Jimmie’s response here: JPA is … Continue reading The “Free”,…
30 Jul 2014
Starting with jOOQ 3.5 we’ve included an awesome new feature for those of you using the Oracle database: Native support for Oracle AQ! And your client code will be so easy to write, you’ll be putting those AQs all over your database immediately. How does it work? jOOQ rationale The biggest reason why many of … Continue reading Using Oracle…
23 Jul 2014
subscribe to this newsletter here Only 8 Days Left in jOOLY Time is running so fast! The month of jOOLY is almost over – have you taken advantage of our limited-time promotional discount of 20% that we’re offering to all of your purchases in July 2014? And that’s not it, you will also get a … Continue reading jOOQ Newsletter:…
7 Jul 2014
Table-valued functions are an awesome thing. Many databases support them in one way or another and so does PostgreSQL. In PostgreSQL, (almost) everything is a table. For instance, we can write: … and believe it or not, this is a table! We can write: And the above will return: +----+ | v2 | +----+ | … Continue reading PostgreSQL’s Table-Valued…
13 Jun 2014
At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem. Java 8 Friday Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and … Continue reading Java 8…
9 Jun 2014
I’m pretty sure you’ve gotten it wrong in numerous ways, so far. And you probably won’t get it right any time soon. So why waste your precious time on SQL tweaking, when you could be implementing business logic? Let me explain… It hasn’t been until the recent SQL:2008 standard that what MySQL users know as … Continue reading Stop Trying…
6 Jun 2014
At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem. Java 8 Friday Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and … Continue reading Java 8…
21 May 2014
Subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ with the world. Here are: Chris Martin who has switched jobs and is now really missing jOOQ. Too bad we can’t offer jOOQ for Python, Chris! Moutaz Salem who had been waiting for jOOQ for all these … Continue reading…
7 May 2014
I’ve recently stumbled upon this interesting Stack Overflow question, where the user essentially wanted to ensure that resulting records are delivered in a well-defined order. They wrote They got CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 GE526OTACCD3ANPH-149839 NI564FAACJSFANPH-162605 They wanted CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 NI564FAACJSFANPH-162605 GE526OTACCD3ANPH-149839 Very often, according to your business rules, sorting orders are not “natural”, as in numeric sortin
30 Apr 2014
Subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ to the world. Here are: Santiago M. Mola who appreciates jOOQ’s affinity to SQL features and its correctness @LucioIO Something I love about @JavaOOQ is that it (correctly) supports almost any type or feature supported by … Continue reading jOOQ…
15 Apr 2014
Haven’t we all been wondering: How can I do this? I have these data in Excel and I want to group / sort / assign / combine … While you could probably pull up a Visual Basic script doing the work or export the data to Java or any other procedural language of choice, why … Continue reading How can…
2 Apr 2014
subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ to the world. Here are: Arturo Tena who simply loves jOOQ 3.3, and expresses this with a creative transformation of our version numbering scheme: https://twitter.com/arturotena/status/434502197217202176 Florin T.Pătraşcu who cannot stop integrating jOOQ with MicroMVC, because he discovers more … Continue…
24 Mar 2014
When doing reporting or statistics with SQL, you better know your window functions. There are many of them, and few SQL developers know about them. CUME_DIST() is one such function. We’ve recently re-discovered it on Stack Overflow. The following query yields two times the same result for fraction1 and fraction2: The above query then yields: … Continue reading CUME_DIST(), a…
12 Mar 2014
Subscribe to the newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ to the world. Here are: Dominik Dorn who finds jOOQ awesome. As simple as that! #JOOQ is awesome! — Dominik Dorn (@domdorn) February 14, 2014 Mariusz Nosiński who cannot believe he hasn’t discovered jOOQ before. How's … Continue reading jOOQ…
19 Feb 2014
Recently, we’ve added support for the MS Access database in jOOQ 3.3. This may well have been our hardest integration so far. The MS Access database has its ways, and many of them, too. But luckily, jOOQ’s internal SQL transformation abilities are already very advanced, as we’ve shown previously in a blog post about the … Continue reading An MS…