How one can Integration Check Saved Procedures with jOOQ – Java, SQL and jOOQ.

Once you write saved procedures and capabilities in your database, you wish to guarantee their correctness, identical to along with your Java code. In Java, that is performed with unit assessments, sometimes with JUnit. For instance, if in case you have the next code in Java:

public static int add(int a, int b) {
    return a + b;
}

Then, you would possibly write a take a look at like this:

@Check
public void testAdd() {
    assertEquals(3, add(1, 2));
}

However how will we do that when writing saved procedures? Whereas there exist some unit take a look at libraries for some database merchandise (e.g. utPLSQL for Oracle) they could endure from the next limitations:

• They may not be as tightly built-in along with your Maven/Gradle construct as JUnit
• They may not be supported by your IDE with extra views resembling in Eclipse/IntelliJ
• There may not be any such utility in your database merchandise in any respect
• You might need to help a number of database merchandise and have to keep up assessments for all of them, ideally written in Java
• Your process integration assessments could work together with some Java code, so that you wish to write the take a look at in Java anyway.

We’d wish to re-use our Java take a look at infrastructure as a substitute, however with out the trouble of binding to procedures through JDBC instantly.

Utilizing jOOQ with testcontainers

Testcontainers is an more and more well-liked framework for database integration testing in Docker. You possibly can rapidly spin up a database occasion and deploy your database schema together with your saved procedures, capabilities, packages, person outlined sorts, and many others. For instance, you would possibly determine to maneuver the above technique into your database utilizing PostgreSQL:

CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS
$$
BEGIN 
  RETURN a + b;
END;
$$
LANGUAGE PLPGSQL;

Now, you may name this operate with JDBC, as such:

attempt (CallableStatement s = connection.prepareCall(
    "{ ? = name add(?, ?) }"
)) {
    s.registerOutParameter(1, Sorts.INTEGER);
    s.setInt(2, 1);
    s.setInt(3, 2);
    s.executeUpdate();
    System.out.println(s.getInt(1));
}

The above prints

3

However that’s plenty of guide plumbing. Everytime you refactor your process, your take a look at fails at runtime, moderately than at compile time. And also you’ll should tediously replace the take a look at code above.

So, why not simply use jOOQ’s code generator to generate a Routines class for you, containing an add() technique. That one, you may name like this:

System.out.println(Routines.add(configuration, 1, 2));

The place configuration is a jOOQ sort wrapping your JDBC Connection. Now you can arrange your JUnit take a look at like this, for instance, e.g. utilizing JUnit’s ClassRule:

@ClassRule
public static PostgreSQLContainer<?> db = 
    new PostgreSQLContainer<>("postgres:14")
        .withUsername("postgres")
        .withDatabaseName("postgres")
        .withPassword("take a look at");

An alternative choice of easy methods to configure testcontainers with JUnit is described right here.

And use db.getJdbcUrl() to connect with the PostgreSQL testcontainers database with jOOQ. Your ultimate take a look at then simply appears to be like like this:

@Check
public void testAdd() {
    attempt (CloseableDSLContext ctx = DSL.utilizing(
        db.getJdbcUrl(), "postgres", "take a look at"
    )) {
        assertEquals(3, Routines.add(ctx.configuration(), 1, 2));
    }
}

You’d clearly transfer the DSLContext logic out of the person assessments right into a @Earlier than block. Not a lot completely different from what you’re used to, proper? Observe that even for code era, we suggest utilizing testcontainers as nicely, as a part of your construct. You possibly can then re-use the code generator testcontainers occasion in your integration assessments, in order for you.