Do you need to add a JDBC driver to your application, and don't know its Maven coordinates? This blog post lists the most popular drivers from the jOOQ integration tests. Look up the latest versions directly on https://central.sonatype.com/ with parameters g:groupId a:artifactId, for example, the H2 database and driver: https://central.sonatype.com/search?q=g%3Acom.h2database+a%3Ah2 The list only includes drivers … Continue reading Maven Coordinates of the most popular JDBC Drivers
Tag: JDBC
JDBC Connection URLs of the Most Popular RDBMS
Need to connect to your RDBMS with JDBC and don't have the JDBC connection URL or driver name at hand? No problem, just look up your RDBMS below: // BigQuery driver = "com.simba.googlebigquery.jdbc42.Driver"; url = "jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<project-id>;OAuthType=0;OAuthServiceAcctEmail=<service-account>;OAuthPvtKeyPath=path-to-key.json"; // CockroachDB driver = "org.postgresql.Driver"; url = "jdbc:postgresql://<host>/<database>"; // Db2 driver = "com.ibm.db2.jcc.DB2Driver"; url = "jdbc:db2://<host>:50000/<database>"; // Derby driver … Continue reading JDBC Connection URLs of the Most Popular RDBMS
How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
Microsoft T-SQL supports a language feature called table-valued parameter (TVP), which is a parameter of a table type that can be passed to a stored procedure or function. For example, you may write: CREATE TYPE u_number_table AS TABLE (column_value INTEGER); CREATE FUNCTION f_cross_multiply ( @numbers u_number_table READONLY ) RETURNS @result TABLE ( i1 INTEGER, i2 … Continue reading How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
Why You Should Execute jOOQ Queries With jOOQ
Previously on this blog, I've written a post explaining why you should use jOOQ's code generator, despite the possibility of using jOOQ without it. In a similar fashion, as I've answered numerous jOOQ questions on Stack Overflow, where someone used jOOQ to build a query, but then executed it elsewhere, including on: JPA JDBC / … Continue reading Why You Should Execute jOOQ Queries With jOOQ
When to Use jOOQ and When to Use Native SQL
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: ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count()) .from(AUTHOR) .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)) .groupBy(AUTHOR.ID, AUTHOR.FIRST_NAME, … Continue reading When to Use jOOQ and When to Use Native SQL
The Many Ways to Return Data From SQL DML
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: CREATE TABLE actor ( … Continue reading The Many Ways to Return Data From SQL DML
The Best Way to Call Stored Procedures from Java: With jOOQ
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 Way to Call Stored Procedures from Java: With jOOQ
The Many Different Ways to Fetch Data in jOOQ
The jOOQ API is all about convenience, and as such, an important operation (the most important one?) like fetch() must come with convenience, too. The default way to fetch data is this: Result<Record1<String>> result = ctx.select(BOOK.TITLE) .from(BOOK) .fetch(); for (Record1<String> record : result) { // ... } It fetches the entire result set into memory … Continue reading The Many Different Ways to Fetch Data in jOOQ
Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: https://twitter.com/lukaseder/status/1407662449331949568 The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint as to the number of rows that … Continue reading Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
Nested Transactions in jOOQ
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 in jOOQ