Methods to Integration Take a look at Saved Procedures with jOOQ – Java, SQL and jOOQ.

If you write saved procedures and features in your database, you need to guarantee their correctness, similar to along with your Java code. In Java, that is accomplished with unit assessments, usually with JUnit. For instance, when you have the next code in Java:

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

Then, you would possibly write a check like this:

@Take a look at
public void testAdd() {
    assertEquals(3, add(1, 2));
}

However how can we do that when writing saved procedures? Whereas there exist some unit check libraries for some database merchandise (e.g. utPLSQL for Oracle) they might undergo 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 equivalent to in Eclipse/IntelliJ
• There won’t be any such utility to your database merchandise in any respect
• You may need to help a number of database merchandise and have to take care of assessments for all of them, ideally written in Java
• Your process integration assessments might work together with some Java code, so that you need to write the check in Java anyway.

We’d wish to re-use our Java check infrastructure as an alternative, however with out the trouble of binding to procedures through JDBC straight.

Utilizing jOOQ with testcontainers

Testcontainers is an more and more widespread framework for database integration testing in Docker. You’ll be able to shortly spin up a database occasion and deploy your database schema together with your saved procedures, features, packages, consumer outlined varieties, and so on. For instance, you would possibly resolve to maneuver the above methodology 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 possibly can name this perform with JDBC, as such:

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

The above prints

3

However that’s numerous guide plumbing. Everytime you refactor your process, your check fails at runtime, slightly than at compile time. And also you’ll should tediously replace the check code above.

So, why not simply use jOOQ’s code generator to generate a Routines class for you, containing an add() methodology. That one, you’ll be able to 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 check like this, for instance, e.g. utilizing JUnit’s ClassRule:

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

An alternative choice of the best way to configure testcontainers with JUnit is described right here.

And use db.getJdbcUrl() to connect with the PostgreSQL testcontainers database with jOOQ. Your closing check then simply seems like this:

@Take a look at
public void testAdd() {
    attempt (CloseableDSLContext ctx = DSL.utilizing(
        db.getJdbcUrl(), "postgres", "check"
    )) {
        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? Be aware that even for code technology, we advocate utilizing testcontainers as effectively, as a part of your construct. You’ll be able to then re-use the code generator testcontainers occasion to your integration assessments, in order for you.