I have previously posted about the SQL MERGE statement, and how powerful it is here: https://blog.jooq.org/arcane-magic-with-the-sql2003-merge-statement/ Unfortunately, not all databases support this statement. Also, very often it is quite a bit of overkill, when what you want to do is to simply INSERT or UPDATE a single record, depending on whether it already exists. MySQL's … Continue reading How to simulate MySQL’s INSERT statement extensions
Category: sql
This category contains posts about SQL in general or some RDBMS’s SQL dialect
ORM vs. SQL, compared to C vs. ASM
History is repeating itself. This is nothing new, but it takes wisdom (and Elephant memory) to remember when and how things had already happened in a similar way. When you feel that the whole SQL versus ORM debate is a bit boring and you may have seen it before, you're probably right. It's another religious … Continue reading ORM vs. SQL, compared to C vs. ASM
jOOQ users’ most frequently used databases
I have recently started an enquiry about which databases jOOQ users are most frequently using. The poll is here: https://blog.jooq.org/which-database-are-you-using-jooq-with/ Of course, such a poll is best analysed using jOOQ's OLAP features. When dumped into a POLL table in our database, we can query the database for the ranking as such: System.out.println( create.select( denseRank().over().orderBy(POLL.VOTES.desc()), POLL.VOTES … Continue reading jOOQ users’ most frequently used databases
jDBI: A simple convenience layer on top of JDBC
I'm always looking out for similar tools like jOOQ, or at least tools that work in the same domain - the domain of database access abstraction. jDBI looks lovely. It provides simple solutions for what JDBC is lacking in general. Here are a couple of features (taken from the intro): Fluent API JDBC is quite … Continue reading jDBI: A simple convenience layer on top of JDBC
SQL tooling, the ranking
When you need to get up and running quickly with your database, the tooling becomes very important. When developing jOOQ and adding integrations for new databases, I really love those ones that provide me with simple ways to create new databases, schemata, users, roles, grants, whatever is needed, using simple dialogs where I can click … Continue reading SQL tooling, the ranking
CROSS JOIN, a nice example for a rarely used operation
In 95% of the cases, cartesian products originate from accidental cross join operations and cause unnecessary high load on a database. Maybe the results aren't even wrong, as someone may have applied a UNION or a DISTINCT keyword, to remove unwanted duplicates. But there are those 5% of SQL queries, where the cartesian product is … Continue reading CROSS JOIN, a nice example for a rarely used operation
Advanced SQL: Relational division in jOOQ
Relational algebra has its treats. One of the most academic features is the relational division. It is hardly ever used, but comes in handy every now and then. And when you need it, you'll probably hate yourself for having slept during the relevant classes at the university. What is relational division? Relational division is the … Continue reading Advanced SQL: Relational division in jOOQ
jOOQ compared to iBatis
Nice feedback from a jOOQ user comparing it with iBATIS: jOOQ rocks! We implemented a large scale ETL service with it, and couldn't be happier. I've used iBATIS, and jOOQ gives all the same advantages of low level SQL without the reams of XML and lack of type safety. [...] one of the best features … Continue reading jOOQ compared to iBatis
Subtle SQL differences: IDENTITY columns
As I'm mostly using Oracle, IDENTITY columns were not so important to me up until I started to support them in jOOQ. Then, I found that yet again, there are many differences between various databases in how they handle IDENTITY columns in DDL and in DML. In SQL, there are essentially three orthogonal concepts of … Continue reading Subtle SQL differences: IDENTITY columns
Binary data in SQL, more trivia
Yesterday, I blogged about inlining boolean literals in SQL. This goes on with other data types. Let's look at BLOBs and BINARY data in general. This is also defined in SQL standards, not in SQL 1992, though: <binary string literal> ::= X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> … Continue reading Binary data in SQL, more trivia