Using Stored Procedures With JPA, JDBC… Meh, Just Use jOOQ

The current edition of the Java magazine has an article about Big Data Best Practices for JDBC and JPA by Josh Juneau:
http://www.javamagazine.mozaicreader.com/MayJune2016

The article shows how to use a stored procedure with JDBC (notice how resources aren’t closed, unfortunately. This is commonly forgotten, even in Java Magazine articles)

// Using JDBC to call upon a database stored
// procedure
CallableStatement cs = null;
try {
    cs = conn.prepareCall("{call DUMMY_PROC(?,?)}");
    cs.setString(1, "This is a test");
    cs.registerOutParameter(2, Types.VARCHAR);
    cs.executeQuery();

    // Do something with result
    String returnStr = cs.getString(2);
} catch (SQLException ex){
    ex.printStackTrace();
}

And with JPA:

// Utilize JPA to call a database stored procedure
// Add @NamedStoredProcedureQuery to entity class
@NamedStoredProcedureQuery(
    name="createEmp", procedureName="CREATE_EMP",
    parameters = {
        @StoredProcedureParameter(
            mode= ParameterMode.IN,
            type=String.class,
            name="first"),
        @StoredProcedureParamter(
            mode = ParameterMode.IN,
            type=String.class,
            name="last")
    })

// Calling upon stored procedure
StoredProcedureQuery qry =
    em.createStoredProcedureQuery("createEmp");
qry.setParameter("first", "JOSH");
qry.setParameter("last","JUNEAU");
qry.execute();

Specifically the latter was also recently discussed in blog posts by Vlad Mihalcea and Thorben Janssen.

Do you like verbosity and complexity?

No? We neither. This is why we give you a third option instead: Just use jOOQ. Here’s the equivalent jOOQ code:

// JDBC example:
String returnStr = Routines.dummyProc(
    config, "This is a test");

// JPA example
Routines.createEmp(config, "JOSH", "JUNEAU");

Yes! That’s it. Don’t waste time manually configuring your bind variables with JDBC API calls, or JPA annotations. No one likes writing annotations for stored procedures. With jOOQ and jOOQ’s code generator, procedure calls are:

  • A one-liner
  • A no-brainer
  • A way to bring back the fun to stored procedures

Learn more about using Oracle stored procedures with nested collections and object types here:
https://blog.jooq.org/2014/11/04/painless-access-from-java-to-plsql-procedures-with-jooq

It is all about the JDBC Basics

We’re very happy to announce a guest post by Marco Behler, who has been blogging about jOOQ in the past.

img31Marco started out in programming (reverse-engineering, actually) and now mainly programmes on the JVM in his day-to-day work. He also always had a sweet tooth for strategy and marketing. Marco Behler GmbH is the result of that hybrid role.

It is all about the JDBC Basics

It is one of the days.

You are reading the Spring documentation’s @Transactional section and still don’t understand the difference between logical and physical transaction scopes. Simultaneously your app throws an
LazyInitializationException and you have no idea why. To top it off you see spontaneous database deadlocks in production and you suspect your connection pool is leaking connections..somehow.

Know what most likely would have helped instead of banging your head against the wall? Spending a couple (literally) of hours on learning the JDBC basics. Let’s find out why:

What are the JDBC basics?

The basics are opening up/closing database connections and then working with transactions. Also understanding how deadlocks, pessimistic and optimistic locking work on a plain JDBC level. A bit of isolation levels and savepoints and then directly on to connection pools and jdbc driver logging. That’s it. Seriously.

Why are the basics so important?

Everything you will encounter in frameworks like Spring, Hibernate, jOOQ etc. builds up on these basics. For example, there are a gazillion topics on the internet regarding Hibernate’s LazyInitializationException and I was scared of that particular exception myself many years ago. But what else would you expect trying to query the database without having a connection to the database open (which is basically all that this exeception is) ?

The same with Spring’s “transaction framework”. There is so much content, or shall we say (F)ear/(U)ncertainty/(D)oubt, out there on how to open up transactions with spring, be it programmatically, with annotations or xml. But what if you knew that under the hood, there is only one way (and actually one line of code) to open up transactions in the JDBC world?

Let me not even get started on the various (mis)configurations of connection pools you see in production in the wild. Or the unawareness of JDBC (driver) logging, which usually leads to debugging in the wild. All basics, which you can master in a couple of hours and which will help you for a lifetime!

Why do people not just learn the basics?

In every middle-sized project there is a ton of technologies involved and there usually is no clear-cut path on how to learn all of them or how they all work together. It simply takes a lot of time and effort to dig through everything.

There’s JPA sessions and JDBC connections and then Spring somehow provides those transactional proxies in 5 different ways and then some other colleague just put jOOQ into the mix, but then somehow my session doesn’t flush and my objects don’t get persisted and the HibernateTransactionManager is not working as expected.

With all of this, I would also hope for my database transactions just to commit – god forbid what happens on rollback :)

But in the end, everything technology mentioned is just a layer on top of JDBC. If you understand transactions or deadlocks or savepoints on the basic level, then Spring or Hibernate or jOOQ will not throw you off.

So what do you recommend ?

If you want to get miles ahead in your day-to-day database programming, you have to start with the basics. Step-by-Step. And then you will see most of your problems automatically evaporate.

Out of my extensive database programming experience, I created an ebook with a ton of ready-to-run exercises, which will take you from Java database novice to expert. At your own pace. You can literally copy the source code of every chapter into your IDE, run it and (hopefully) learn from it. It covers plain JDBC, Spring, Hibernate, jOOQ (soon) and also distributed transactions.

You can read the whole book for free online here, and I would love to get your feedback! I would really like to let the community feedback flow back into future editions of the book. In addition, If you like what you see and the exercises help you, you can also show your support by getting a paid digital version (pdf, epub, mobi).

In any case…

…learn your JDBC basics – and you will profit from them for a lifetime!

What’s Even Harder Than Dates and Timezones? Dates and Timezones in SQL / JDBC!

(Notice, this post has been written a while ago. jOOQ now supports JSR 310 data types)

There was an interesting discussion recently on the jOOQ mailing list about jOOQ’s current lack of out-of-the-box support for TIMESTAMP WITH TIME ZONE data types.

No one said that date, time and timezones are easy! There’s an amusing piece here, which I recommend reading:
Falsehoods programmers believe about time

And when that’s not enough, read also:
More falsehoods programmers believe about time

I personally like the bit about programmers erroneously believing that “Unix time is the number of seconds since Jan 1st 1970.”… unix time doesn’t have a way to represent leap seconds ;)

Back to JDBC

Here’s an interesting Stack Overflow answer by Mark Rotteveel, the Jaybird developer (Firebird JDBC driver):
Is java.sql.Timestamp timezone specific?

Mark’s explanation can be observed as follows (I’m using PostgreSQL here):

Connection c = getConnection();
Calendar utc = Calendar.getInstance(
    TimeZone.getTimeZone("UTC"));

try (PreparedStatement ps = c.prepareStatement(
    "select"
  + "  ?::timestamp,"
  + "  ?::timestamp,"
  + "  ?::timestamp with time zone,"
  + "  ?::timestamp with time zone"
)) {

    ps.setTimestamp(1, new Timestamp(0));
    ps.setTimestamp(2, new Timestamp(0), utc);
    ps.setTimestamp(3, new Timestamp(0));
    ps.setTimestamp(4, new Timestamp(0), utc);

    try (ResultSet rs = ps.executeQuery()) {
        rs.next();

        System.out.println(rs.getTimestamp(1) 
                 + " / " + rs.getTimestamp(1).getTime());
        System.out.println(rs.getTimestamp(2, utc)
                 + " / " + rs.getTimestamp(2, utc).getTime());
        System.out.println(rs.getTimestamp(3) 
                 + " / " + rs.getTimestamp(3).getTime());
        System.out.println(rs.getTimestamp(4, utc)
                 + " / " + rs.getTimestamp(4, utc).getTime());
    }
}

The above program uses all permutations of using timezones and not using timezones in Java and in the DB, and the output is always the same:

1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0

As you can see, in each case, the UTC timestamp 0 was correctly stored and retrieved from the database. My own locale is Switzerland, thus CET / CEST, which was UTC+1 at Epoch, which is what is getting output on Timestamp.toString().

Things get interesting when you use timestamp literals, both in SQL and/or in Java. If you replace the bind variables as such:

Timestamp almostEpoch = Timestamp.valueOf("1970-01-01 00:00:00");

ps.setTimestamp(1, almostEpoch);
ps.setTimestamp(2, almostEpoch, utc);
ps.setTimestamp(3, almostEpoch);
ps.setTimestamp(4, almostEpoch, utc);

This is what I’m getting on my machine, again in CET / CEST

1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000

I.e. not Epoch, but the timestamp literal that I sent to the server in the first place. Observe that the four combinations of binding / fetching still always produce the same timestamp.

Let’s see what happens if the session writing to the database uses a different timezone (let’s assume you’re in PST) than the session fetching from the database (I’m using again CET or UTC). I’m running this program:

Calendar utc = Calendar.getInstance(
    TimeZone.getTimeZone("UTC"));

Calendar pst = Calendar.getInstance(
    TimeZone.getTimeZone("PST"));

try (PreparedStatement ps = c.prepareStatement(
    "select"
  + "  ?::timestamp,"
  + "  ?::timestamp,"
  + "  ?::timestamp with time zone,"
  + "  ?::timestamp with time zone"
)) {

    ps.setTimestamp(1, new Timestamp(0), pst);
    ps.setTimestamp(2, new Timestamp(0), pst);
    ps.setTimestamp(3, new Timestamp(0), pst);
    ps.setTimestamp(4, new Timestamp(0), pst);

    try (ResultSet rs = ps.executeQuery()) {
        rs.next();

        System.out.println(rs.getTimestamp(1)
                 + " / " + rs.getTimestamp(1).getTime());
        System.out.println(rs.getTimestamp(2, utc)
                 + " / " + rs.getTimestamp(2, utc).getTime());
        System.out.println(rs.getTimestamp(3)
                 + " / " + rs.getTimestamp(3).getTime());
        System.out.println(rs.getTimestamp(4, utc)
                 + " / " + rs.getTimestamp(4, utc).getTime());
    }
}

It yields this output:

1969-12-31 16:00:00.0 / -32400000
1969-12-31 17:00:00.0 / -28800000
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0

The first timestamp was Epoch stored as PST (16:00), then the timezone information was removed by the database, which turned Epoch into the local time you had at Epoch (-28800 seconds / -8h), and that’s the information that is really stored.

Now, when I’m fetching this time from my own timezone CET, I will still want to get the local time (16:00). But in my timezone, this is no longer -28800 seconds, but -32400 seconds (-9h). Quirky enough?

Things go the other way round when I’m fetching the stored local time (16:00), but I’m forcing the fetch to happen in UTC, which will produce the timestamp that you’ve stored, originally in PST (-28800 seconds). But when printing this timestamp (-28800 seconds) in my timezone CET, this will now be 17:00.

When we use the TIMESTAMP WITH TIME ZONE data type in the database, the timezone is maintained (PST), and when I fetch the Timestamp value, no matter if using CET or UTC, I will still get Epoch, which was safely stored to the database, printed out as 01:00 in CET.

Whew.

TL;DR:

(Notice, this post has been written a while ago. jOOQ now supports JSR 310 data types)

When using jOOQ’s, if the correct UTC timestamp matters to you, use TIMESTAMP WITH TIMEZONE, but you’ll have to implement your own data type Binding, because jOOQ currently doesn’t support that data type. Once you use your own data type Binding, you can also use Java 8’s time API, which better represents these different types than java.sql.Timestamp + the ugly Calendar.

If the local time matters to you, or if you’re not operating across time zones, you’re fine using TIMESTAMP and jOOQ’s Field<Timestamp>.

Lucky you, if you’re like me, operating in a very small country with a single time zone where most local software just doesn’t run into this issue.

Let’s Review How to Insert Clob or Blob via JDBC

LOBs are a PITA in all databases, as well as in JDBC. Handling them correctly takes a couple of lines of code, and you can be sure that you’ll get it wrong eventually. Because you have to think of a couple of things:

  • Foremost, LOBs are heavy resources that need special lifecycle management. Once you’ve allocated a LOB, you better “free” it as well to decrease the pressure on your GC. This article shows more about why you need to free lobs
  • The time when you allocate and free a lob is crucial. It might have a longer life span than any of your ResultSet, PreparedStatement, or Connection / transaction. Each database manages such life spans individually, and you might have to read up the specifications in edge cases
  • While you may use String instead of Clob, or byte[] instead of Blob for small to medium size LOBs, this may not always be the case, and may even lead to some nasty errors, like Oracle’s dreaded ORA-01461: can bind a LONG value only for insert into a LONG column

So, if you’re working on a low level using JDBC (instead of abstracting JDBC via Hibernate or jOOQ), you better write a small utility that takes care of proper LOB handling.

We’ve recently re-discovered our own utility that we’re using for jOOQ integration testing, at least in some databases, and thought this might be very useful to a couple of our readers who operate directly with JDBC. Consider the following class:

public class LOB implements AutoCloseable {

    private final Connection connection;
    private final List<Blob> blobs;
    private final List<Clob> clobs;

    public LOB(Connection connection) {
        this.connection = connection;
        this.blobs = new ArrayList<>();
        this.clobs = new ArrayList<>();
    }

    public final Blob blob(byte[] bytes) 
    throws SQLException {
        Blob blob;

        // You may write more robust dialect 
        // detection here
        if (connection.getMetaData()
                      .getDatabaseProductName()
                      .toLowerCase()
                      .contains("oracle")) {
            blob = BLOB.createTemporary(connection, 
                       false, BLOB.DURATION_SESSION);
        }
        else {
            blob = connection.createBlob();
        }

        blob.setBytes(1, bytes);
        blobs.add(blob);
        return blob;
    }

    public final Clob clob(String string) 
    throws SQLException {
        Clob clob;

        if (connection.getMetaData()
                      .getDatabaseProductName()
                      .toLowerCase()
                      .contains("oracle")) {
            clob = CLOB.createTemporary(connection, 
                       false, CLOB.DURATION_SESSION);
        }
        else {
            clob = connection.createClob();
        }

        clob.setString(1, string);
        clobs.add(clob);
        return clob;
    }


    @Override
    public final void close() throws Exception {
        blobs.forEach(JDBCUtils::safeFree);
        clobs.forEach(JDBCUtils::safeFree);
    }
}

This simple class has some nice treats:

  • It’s AutoCloseable, so you can free your lobs with the try-with-resources statement
  • It abstracts over the creation of LOBs across SQL dialects. No need to remember the Oracle way

To use this class, simply write something like the following:

try (
    LOB lob = new LOB(connection);
    PreparedStatement stmt = connection.prepareStatement(
        "insert into lobs (id, lob) values (?, ?)")
) {
    stmt.setInt(1, 1);
    stmt.setClob(2, lob.clob("abc"));
    stmt.executeUpdate();
}

That’s it! No need to keep references to the lob, safely freeing it if it’s not null, correctly recovering from exceptions, etc. Just put the LOB container in the try-with-resources statement, along with the PreparedStatement and done.

If you’re interested in why you have to call Clob.free() or Blob.free() in the first place, read our article about it. It’ll spare you one or two OutOfMemoryErrors

How to FlatMap a JDBC ResultSet with Java 8?

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    | H    | I    | row 3
+------+------+------+

to be “flat mapped” into this:

+------+
| cols |
+------+
| A    |\ 
| B    | | row 1
| C    |/
| D    |\
| E    | | row 2
| F    |/
| G    |\
| H    | | row 3
| I    |/
+------+

How to do it with Java 8?

It’s easy, when you’re using jOOQ. Let’s create the database first:

CREATE TABLE t (
  col1 VARCHAR2(1),
  col2 VARCHAR2(1),
  col3 VARCHAR2(1)
);

INSERT INTO t VALUES ('A', 'B', 'C');
INSERT INTO t VALUES ('D', 'E', 'F');
INSERT INTO t VALUES ('G', 'H', 'I');

Now let’s add some jOOQ and Java 8!

List<String> list =
DSL.using(connection)
   .fetch("SELECT col1, col2, col3 FROM t")
   .stream()
   .flatMap(r -> Arrays.stream(r.into(String[].class)))
   .collect(Collectors.toList());

System.out.println(list);

… and that’s it! The output is:

[A, B, C, D, E, F, G, H, I]

(I’ve also given this solution to this Stack Overflow question)

How do you read the above? Simply like this:

List<String> list =

// Get a Result<Record>, which is essentially a List
// from the database query
DSL.using(connection)
   .fetch("SELECT col1, col2, col3 FROM t")

// Stream its records
   .stream()

// And generate a new stream of each record's String[]
// representation, "flat mapping" that again into a
// single stream
   .flatMap(r -> Arrays.stream(r.into(String[].class)))
   .collect(Collectors.toList());

Note that if you’re not using jOOQ to render and execute your query, you can still use jOOQ to transform the JDBC ResultSet into a jOOQ Result to produce the same output:

try (ResultSet rs = ...) {
    List<String> list =
    DSL.using(connection)
       .fetch(rs) // unwind the ResultSet here
       .stream()
       .flatMap(r -> Arrays.stream(r.into(String[].class)))
       .collect(Collectors.toList());

    System.out.println(list);
}

Bonus: The SQL way

The SQL way to produce the same result is trivial:

SELECT col1 FROM t UNION ALL
SELECT col2 FROM t UNION ALL
SELECT col3 FROM t
ORDER BY 1

Or, of course, if you’re using Oracle or SQL Server, you can use the magic UNPIVOT clause (the opposite of the PIVOT clause):

SELECT c
FROM t
UNPIVOT (
  c FOR col in (col1, col2, col3)
)

Hack up a Simple JDBC ResultSet Cache Using jOOQ’s MockDataProvider

Some queries shouldn’t hit the database all the time. When you query for master data (such as system settings, languages, translations, etc.), for instance, you may want to avoid sending the same silly query (and the results) over the wire all the time. For example:

SELECT * FROM languages

Most databases maintain buffer caches to accelerate these queries, so you don’t always hit the disk. Some databases maintain result set caches per cursor, or their JDBC drivers might even implement result set caches directly in the driver – a little known feature in Oracle, for instance:

SELECT /*+ RESULT_CACHE */ * FROM languages

But you may not be using Oracle, and because patching JDBC is a pain, you might have resorted to implementing the cache one or two layers up in the data access or service layer:

class LanguageService {
    private Cache cache;

    List<Language> getLanguages() {
        List<Language> result = cache.get();

        if (result == null) {
            result = doGetLanguages();
            cache.put(result);
        }

        return result;
    }
}

Doing it in the JDBC layer, instead

While this might work fine on a per-service-and-method level, it might quickly become tedious when you query only parts of those results. E.g. what happens when you add an additional filter? Should you cache that query as well? Should you perform the filter on the cache, or hit the database at least once per filter?

class LanguageService {
    private Cache cache;

    List<Language> getLanguages() { ... }
    List<Language> getLanguages(Country country) {
        // Another cache?
        // Query the cache only and delegate to
        //     getLanguages()?
        // Or don't cache this at all?
    }
}

wouldn’t it be nice if we had a cache of the form:

Map<String, ResultSet> cache;

… which caches re-usable JDBC ResultSets (or better: jOOQ Results) and returns the same results every time an identical query string is encountered.

Use jOOQ’s MockDataProvider for this

jOOQ ships with a MockConnection, which implements the JDBC Connection API for you, mocking all other objects, such as PreparedStatement, ResultSet, etc. We’ve already introduced this useful tool for unit testing in a previous blog post.

But you can “mock” your connection also in order to implement a cache! Consider the following, very simple MockDataProvider:

class ResultCache implements MockDataProvider {
    final Map<String, Result<?>> cache = 
        new ConcurrentHashMap<>();
    final Connection connection;

    ResultCache(Connection connection) {
        this.connection = connection;
    }

    @Override
    public MockResult[] execute(MockExecuteContext ctx)
    throws SQLException {
        Result<?> result;

        // Add more sophisticated caching criteria
        if (ctx.sql().contains("from language")) {

            // We're using this very useful new Java 8
            // API for atomic cache value calculation
            result = cache.computeIfAbsent(
                ctx.sql(),
                sql -> DSL.using(connection).fetch(
                    ctx.sql(),
                    ctx.bindings()
                )
            );
        }

        // All other queries go to the database
        else {
            result = DSL.using(connection).fetch(
                ctx.sql(), 
                ctx.bindings()
            );
        }

        return new MockResult[] { 
            new MockResult(result.size(), result)
        };
    }
}

Obviously, this is a very simplistic example. A real cache would involve invalidation (time-based, update-based, etc.) as well as more selective caching criteria than just matching on from language.

But the fact is that using the above ResultCache, we can now wrap all JDBC connections and prevent hitting the database more than once for all queries that query from the language table! An example using jOOQ API:

DSLContext normal = DSL.using(connection);
DSLContext cached = DSL.using(
    new MockConnection(new ResultCache(connection))
);

// This executs a select count(*) from language query
assertEquals(4, cached.fetchCount(LANGUAGE));
assertEquals(4, normal.fetchCount(LANGUAGE));

// Let's add another language (using normal config):
LanguageRecord lang = normal.newRecord(LANGUAGE);
lang.setName("German");
lang.store();

// Checking again on the language table:
assertEquals(4, cached.fetchCount(LANGUAGE));
assertEquals(5, normal.fetchCount(LANGUAGE));

The cache works like a charm! Note that the current cache implementation is merely SQL string based (as it should be). If you modify the SQL string even only slightly, you’ll experience another cache miss and the query goes back to the database:

// This query is not the same as the cached one, it
// fetches two count(*) expressions. Thus we go back
// to the database and get the latest result.
assertEquals(5, (int) cached
    .select(
        count(),
        count())
    .from(LANGUAGE)
    .fetchOne()
    .value1());

// This still has the "stale" previous result
assertEquals(4, cached.fetchCount(LANGUAGE));

Conclusion

Caching is hard. Very hard. Apart from concurrency, naming things and off-by-one errors, it’s one of the three hardest problems in software.

This article doesn’t recommend to implement a cache at the JDBC level. You may or may not make that decision yourself. But when you do, then you can see how easy it is to implement such a cache using jOOQ.

jOOQ is the best way to write SQL in Java

And the best thing is that you don’t have to use jOOQ in all of your application. You can use it just for this particular use-case (and for mocking JDBC), and continue using JDBC, MyBatis, Hibernate, etc, as long as you patch other framework’s JDBC Connections with the jOOQ MockConnection.

How to Integrate Commercial Third-Party Artefacts into Your Maven Build

According to a recent survey by ZeroTurnaround’s RebelLabs, Maven is still the leading Java build platform. The current market share distribution, according to RebelLabs is:

  • Maven with 64%
  • Ant + Ivy with 16.5%
  • Gradle with 11%

Yet, at the same time, Maven is often criticised for being a bit obscure and intrusive. Compared to runner-ups Ant and Gradle, Maven allows for only little flexibility with respect to interpretation and thus custom adaptation of the build model. Or as Tim Berglund from Data Stax would put it:

But let’s cut the jokes and have a look at a real-world issue:

Integrating Third-Party Commercial Artefacts

Not all third party artefacts that you would like to depend upon are available for free from Maven Central. Examples for this are commercial JDBC drivers, or the commercial jOOQ editions. There are essentially three ways to integrate such artefacts into your build:

Quick-and-dirty

Often, you only need the commercial dependency for a small test project or demo. You want to be sure that it works when you run it without depending on your local repository setup, or on network connectivity. This is a good use-case for <scope>system</scope>:

For instance: jOOQ

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>${jooq.version}</version>
  <scope>system</scope>
  <systemPath>${basedir}/lib/jooq-${jooq.version}.jar</systemPath>
</dependency>

For instance: Microsoft SQL JDBC

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>sqljdbc4</artifactId>
  <version>3.0</version>
  <scope>system</scope>
  <systemPath>${basedir}/lib/sqljdbc4.jar</systemPath>

  <!-- Notice that we can still put "optional"
       on commercial JDBC driver dependencies -->
  <optional>true</optional>
</dependency>

Advantages of this approach

This is really a very easy solution when you want to have a local, self-contained module that is guaranteed to run immediately after checkout from source control, without additional configuration and setup. Don’t forget to check in the libraries into source control first, of course.

Disadvantages of this appraoch

The system dependencies are never transitively inherited. If your module depends on jOOQ this way, your module’s dependencies won’t see the jOOQ API.

Details about system dependencies can be seen in the Maven documentation. Citing from the documentation:

Dependencies with the scope system are always available and are not looked up in repository. They are usually used to tell Maven about dependencies which are provided by the JDK or the VM. Thus, system dependencies are especially useful for resolving dependencies on artifacts which are now provided by the JDK, but where available as separate downloads earlier. Typical example are the JDBC standard extensions or the Java Authentication and Authorization Service (JAAS).

A bit more robust

An approach that might appear to be a bit more robust is to check out the dependencies from your version control system and then “manually” import them to your local repository. This will make them available to your own local build. The following shell scripts show how you can import, for instance, the jOOQ artefacts into your local repository

Windows Batch

@echo off
set VERSION=3.4.4

if exist jOOQ-javadoc\jooq-%VERSION%-javadoc.jar (
  set JAVADOC_JOOQ=-Djavadoc=jOOQ-javadoc\jooq-%VERSION%-javadoc.jar
  set JAVADOC_JOOQ_META=-Djavadoc=jOOQ-javadoc\jooq-meta-%VERSION%-javadoc.jar
  set JAVADOC_JOOQ_CODEGEN=-Djavadoc=jOOQ-javadoc\jooq-codegen-%VERSION%-javadoc.jar
  set JAVADOC_JOOQ_CODEGEN_MAVEN=-Djavadoc=jOOQ-javadoc\jooq-codegen-maven-%VERSION%-javadoc.jar
  set JAVADOC_JOOQ_SCALA=-Djavadoc=jOOQ-javadoc\jooq-scala-%VERSION%-javadoc.jar
)

if exist jOOQ-src\jooq-%VERSION%-sources.jar (
  set SOURCES_JOOQ=-Dsources=jOOQ-src\jooq-%VERSION%-sources.jar
  set SOURCES_JOOQ_META=-Dsources=jOOQ-src\jooq-meta-%VERSION%-sources.jar
  set SOURCES_JOOQ_CODEGEN=-Dsources=jOOQ-src\jooq-codegen-%VERSION%-sources.jar
  set SOURCES_JOOQ_CODEGEN_MAVEN=-Dsources=jOOQ-src\jooq-codegen-maven-%VERSION%-sources.jar
  set SOURCES_JOOQ_SCALA=-Dsources=jOOQ-src\jooq-scala-%VERSION%-sources.jar
)

call mvn install:install-file -Dfile=jOOQ-pom\pom.xml                          -DgroupId=org.jooq -DartifactId=jooq-parent        -Dversion=%VERSION% -Dpackaging=pom
call mvn install:install-file -Dfile=jOOQ-lib\jooq-%VERSION%.jar               -DgroupId=org.jooq -DartifactId=jooq               -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ%               %SOURCES_JOOQ%              -DpomFile=jOOQ-pom\jooq\pom.xml
call mvn install:install-file -Dfile=jOOQ-lib\jooq-meta-%VERSION%.jar          -DgroupId=org.jooq -DartifactId=jooq-meta          -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ_META%          %SOURCES_JOOQ_META%         -DpomFile=jOOQ-pom\jooq-meta\pom.xml
call mvn install:install-file -Dfile=jOOQ-lib\jooq-codegen-%VERSION%.jar       -DgroupId=org.jooq -DartifactId=jooq-codegen       -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ_CODEGEN%       %SOURCES_JOOQ_CODEGEN%      -DpomFile=jOOQ-pom\jooq-codegen\pom.xml
call mvn install:install-file -Dfile=jOOQ-lib\jooq-codegen-maven-%VERSION%.jar -DgroupId=org.jooq -DartifactId=jooq-codegen-maven -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ_CODEGEN_MAVEN% %SOURCES_JOOQ_CODEGEN_META% -DpomFile=jOOQ-pom\jooq-codegen-maven\pom.xml
call mvn install:install-file -Dfile=jOOQ-lib\jooq-scala-%VERSION%.jar         -DgroupId=org.jooq -DartifactId=jooq-scala         -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ_SCALA%         %SOURCES_JOOQ_SCALA%        -DpomFile=jOOQ-pom\jooq-scala\pom.xml

Linux Shell

#!/bin/sh
VERSION=3.4.4

if [ -f jOOQ-javadoc/jooq-$VERSION-javadoc.jar ]; then
  JAVADOC_JOOQ=-Djavadoc=jOOQ-javadoc/jooq-$VERSION-javadoc.jar
  JAVADOC_JOOQ_META=-Djavadoc=jOOQ-javadoc/jooq-meta-$VERSION-javadoc.jar
  JAVADOC_JOOQ_CODEGEN=-Djavadoc=jOOQ-javadoc/jooq-codegen-$VERSION-javadoc.jar
  JAVADOC_JOOQ_CODEGEN_MAVEN=-Djavadoc=jOOQ-javadoc/jooq-codegen-maven-$VERSION-javadoc.jar
  JAVADOC_JOOQ_SCALA=-Djavadoc=jOOQ-javadoc/jooq-scala-$VERSION-javadoc.jar
fi

if [ -f jOOQ-src/jooq-$VERSION-sources.jar ]; then
  SOURCES_JOOQ=-Dsources=jOOQ-src/jooq-$VERSION-sources.jar
  SOURCES_JOOQ_META=-Dsources=jOOQ-src/jooq-meta-$VERSION-sources.jar
  SOURCES_JOOQ_CODEGEN=-Dsources=jOOQ-src/jooq-codegen-$VERSION-sources.jar
  SOURCES_JOOQ_CODEGEN_MAVEN=-Dsources=jOOQ-src/jooq-codegen-maven-$VERSION-sources.jar
  SOURCES_JOOQ_SCALA=-Dsources=jOOQ-src/jooq-scala-$VERSION-sources.jar
fi

mvn install:install-file -Dfile=jOOQ-pom/pom.xml                         -DgroupId=org.jooq -DartifactId=jooq-parent        -Dversion=$VERSION -Dpackaging=pom
mvn install:install-file -Dfile=jOOQ-lib/jooq-$VERSION.jar               -DgroupId=org.jooq -DartifactId=jooq               -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ               $SOURCES_JOOQ              -DpomFile=jOOQ-pom/jooq/pom.xml
mvn install:install-file -Dfile=jOOQ-lib/jooq-meta-$VERSION.jar          -DgroupId=org.jooq -DartifactId=jooq-meta          -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ_META          $SOURCES_JOOQ_META         -DpomFile=jOOQ-pom/jooq-meta/pom.xml
mvn install:install-file -Dfile=jOOQ-lib/jooq-codegen-$VERSION.jar       -DgroupId=org.jooq -DartifactId=jooq-codegen       -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ_CODEGEN       $SOURCES_JOOQ_CODEGEN      -DpomFile=jOOQ-pom/jooq-codegen/pom.xml
mvn install:install-file -Dfile=jOOQ-lib/jooq-codegen-maven-$VERSION.jar -DgroupId=org.jooq -DartifactId=jooq-codegen-maven -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ_CODEGEN_MAVEN $SOURCES_JOOQ_CODEGEN_META -DpomFile=jOOQ-pom/jooq-codegen-maven/pom.xml
mvn install:install-file -Dfile=jOOQ-lib/jooq-scala-$VERSION.jar         -DgroupId=org.jooq -DartifactId=jooq-scala         -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ_SCALA         $SOURCES_JOOQ_SCALA        -DpomFile=jOOQ-pom/jooq-scala/pom.xml

More up to date versions of these scripts can be found here:
https://github.com/jOOQ/jOOQ/tree/master/jOOQ-release/release/template

The above scripts essentially check if any of Javadoc, Sources, and/or binaries are available in the distribution, and then install:

  • The parent pom.xml
  • The various artefact binaries, sources, javadocs, and pom.xml files

Advantages of this approach

Dependencies can now be referenced like any other type of dependency, as the artefacts are registered in your local repository. Moreover, they’re also available to your module’s own dependencies, transitively – which is probably what you want when you’re using jOOQ. Here’s how you’d then specify the dependencies:

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>${jooq.version}</version>
</dependency>

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>sqljdbc4</artifactId>
  <version>3.0</version>
  <scope>provided</scope>
</dependency>

Disadvantages of this approach

There is a manual step involved in the installation of the dependencies. If you don’t have the above scripts readily available, it can be quite tedious to figure out exactly how to import all those dependencies step by step into your repository. Specifically if you’re running a demo or prototype, this may lead to unexpected compilation failure in the worst moments.

Deploying

If, instead of installing, you’d like to deploy the artifacts to some remote repository, use these adapted scripts instead:

Windows Batch

@echo off
set VERSION=3.4.4
set REPOSITORY=[set your repository name here, as in settings.xml]
set URL=[set your server URL here]

if exist jOOQ-javadoc\jooq-%VERSION%-javadoc.jar (
  set JAVADOC_JOOQ=-Djavadoc=jOOQ-javadoc\jooq-%VERSION%-javadoc.jar
  set JAVADOC_JOOQ_META=-Djavadoc=jOOQ-javadoc\jooq-meta-%VERSION%-javadoc.jar
  set JAVADOC_JOOQ_CODEGEN=-Djavadoc=jOOQ-javadoc\jooq-codegen-%VERSION%-javadoc.jar
  set JAVADOC_JOOQ_CODEGEN_MAVEN=-Djavadoc=jOOQ-javadoc\jooq-codegen-maven-%VERSION%-javadoc.jar
  set JAVADOC_JOOQ_SCALA=-Djavadoc=jOOQ-javadoc\jooq-scala-%VERSION%-javadoc.jar
)

if exist jOOQ-src\jooq-%VERSION%-sources.jar (
  set SOURCES_JOOQ=-Dsources=jOOQ-src\jooq-%VERSION%-sources.jar
  set SOURCES_JOOQ_META=-Dsources=jOOQ-src\jooq-meta-%VERSION%-sources.jar
  set SOURCES_JOOQ_CODEGEN=-Dsources=jOOQ-src\jooq-codegen-%VERSION%-sources.jar
  set SOURCES_JOOQ_CODEGEN_MAVEN=-Dsources=jOOQ-src\jooq-codegen-maven-%VERSION%-sources.jar
  set SOURCES_JOOQ_SCALA=-Dsources=jOOQ-src\jooq-scala-%VERSION%-sources.jar
)

call mvn deploy:deploy-file -Dfile=jOOQ-pom\pom.xml                          -DgroupId=org.jooq -DartifactId=jooq-parent        -DrepositoryId=%REPOSITORY% -Durl=%URL% -Dversion=%VERSION% -Dpackaging=pom
call mvn deploy:deploy-file -Dfile=jOOQ-lib\jooq-%VERSION%.jar               -DgroupId=org.jooq -DartifactId=jooq               -DrepositoryId=%REPOSITORY% -Durl=%URL% -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ%               %SOURCES_JOOQ%              -DpomFile=jOOQ-pom\jooq\pom.xml
call mvn deploy:deploy-file -Dfile=jOOQ-lib\jooq-meta-%VERSION%.jar          -DgroupId=org.jooq -DartifactId=jooq-meta          -DrepositoryId=%REPOSITORY% -Durl=%URL% -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ_META%          %SOURCES_JOOQ_META%         -DpomFile=jOOQ-pom\jooq-meta\pom.xml
call mvn deploy:deploy-file -Dfile=jOOQ-lib\jooq-codegen-%VERSION%.jar       -DgroupId=org.jooq -DartifactId=jooq-codegen       -DrepositoryId=%REPOSITORY% -Durl=%URL% -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ_CODEGEN%       %SOURCES_JOOQ_CODEGEN%      -DpomFile=jOOQ-pom\jooq-codegen\pom.xml
call mvn deploy:deploy-file -Dfile=jOOQ-lib\jooq-codegen-maven-%VERSION%.jar -DgroupId=org.jooq -DartifactId=jooq-codegen-maven -DrepositoryId=%REPOSITORY% -Durl=%URL% -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ_CODEGEN_MAVEN% %SOURCES_JOOQ_CODEGEN_META% -DpomFile=jOOQ-pom\jooq-codegen-maven\pom.xml
call mvn deploy:deploy-file -Dfile=jOOQ-lib\jooq-scala-%VERSION%.jar         -DgroupId=org.jooq -DartifactId=jooq-scala         -DrepositoryId=%REPOSITORY% -Durl=%URL% -Dversion=%VERSION% -Dpackaging=jar %JAVADOC_JOOQ_SCALA%         %SOURCES_JOOQ_SCALA%        -DpomFile=jOOQ-pom\jooq-scala\pom.xml

Linux Shell

#!/bin/sh
VERSION=3.4.4
REPOSITORY=[set your repository name here, as in settings.xml]
URL=[set your server URL here]

if [ -f jOOQ-javadoc/jooq-$VERSION-javadoc.jar ]; then
  JAVADOC_JOOQ=-Djavadoc=jOOQ-javadoc/jooq-$VERSION-javadoc.jar
  JAVADOC_JOOQ_META=-Djavadoc=jOOQ-javadoc/jooq-meta-$VERSION-javadoc.jar
  JAVADOC_JOOQ_CODEGEN=-Djavadoc=jOOQ-javadoc/jooq-codegen-$VERSION-javadoc.jar
  JAVADOC_JOOQ_CODEGEN_MAVEN=-Djavadoc=jOOQ-javadoc/jooq-codegen-maven-$VERSION-javadoc.jar
  JAVADOC_JOOQ_SCALA=-Djavadoc=jOOQ-javadoc/jooq-scala-$VERSION-javadoc.jar
fi

if [ -f jOOQ-src/jooq-$VERSION-sources.jar ]; then
  SOURCES_JOOQ=-Dsources=jOOQ-src/jooq-$VERSION-sources.jar
  SOURCES_JOOQ_META=-Dsources=jOOQ-src/jooq-meta-$VERSION-sources.jar
  SOURCES_JOOQ_CODEGEN=-Dsources=jOOQ-src/jooq-codegen-$VERSION-sources.jar
  SOURCES_JOOQ_CODEGEN_MAVEN=-Dsources=jOOQ-src/jooq-codegen-maven-$VERSION-sources.jar
  SOURCES_JOOQ_SCALA=-Dsources=jOOQ-src/jooq-scala-$VERSION-sources.jar
fi

mvn deploy:deploy-file -Dfile=jOOQ-pom/pom.xml                         -DgroupId=org.jooq -DartifactId=jooq-parent        -DrepositoryId=$REPOSITORY -Durl=$URL -Dversion=$VERSION -Dpackaging=pom
mvn deploy:deploy-file -Dfile=jOOQ-lib/jooq-$VERSION.jar               -DgroupId=org.jooq -DartifactId=jooq               -DrepositoryId=$REPOSITORY -Durl=$URL -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ               $SOURCES_JOOQ              -DpomFile=jOOQ-pom/jooq/pom.xml
mvn deploy:deploy-file -Dfile=jOOQ-lib/jooq-meta-$VERSION.jar          -DgroupId=org.jooq -DartifactId=jooq-meta          -DrepositoryId=$REPOSITORY -Durl=$URL -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ_META          $SOURCES_JOOQ_META         -DpomFile=jOOQ-pom/jooq-meta/pom.xml
mvn deploy:deploy-file -Dfile=jOOQ-lib/jooq-codegen-$VERSION.jar       -DgroupId=org.jooq -DartifactId=jooq-codegen       -DrepositoryId=$REPOSITORY -Durl=$URL -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ_CODEGEN       $SOURCES_JOOQ_CODEGEN      -DpomFile=jOOQ-pom/jooq-codegen/pom.xml
mvn deploy:deploy-file -Dfile=jOOQ-lib/jooq-codegen-maven-$VERSION.jar -DgroupId=org.jooq -DartifactId=jooq-codegen-maven -DrepositoryId=$REPOSITORY -Durl=$URL -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ_CODEGEN_MAVEN $SOURCES_JOOQ_CODEGEN_META -DpomFile=jOOQ-pom/jooq-codegen-maven/pom.xml
mvn deploy:deploy-file -Dfile=jOOQ-lib/jooq-scala-$VERSION.jar         -DgroupId=org.jooq -DartifactId=jooq-scala         -DrepositoryId=$REPOSITORY -Durl=$URL -Dversion=$VERSION -Dpackaging=jar $JAVADOC_JOOQ_SCALA         $SOURCES_JOOQ_SCALA        -DpomFile=jOOQ-pom/jooq-scala/pom.xml

Again, more up to date versions of these scripts can be found here:
https://github.com/jOOQ/jOOQ/tree/master/jOOQ-release/release/template

The way to go

In an actual project setup, obviously, neither of the above approaches will be sufficient, and you’ll probably import the libraries into your local Nexus or Bintray or whatever repository you’re using. Just beware of potential restrictions on distribution that commercial deliverables may have.

A small tutorial about how to install artefacts into Nexus can be found here.