Saturday, October 1, 2022
HomeProgrammingIntegration Check Saved Procedures with jOOQ – Java, SQL and jOOQ.

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


If you write saved procedures and capabilities in your database, you wish to guarantee their correctness, identical to together with your Java code. In Java, that is executed with unit exams, sometimes 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 take a look at like this:

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

However how can 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 might endure from the next limitations:

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

We’d prefer to re-use our Java take a look at infrastructure as an alternative, however with out the effort of binding to procedures through JDBC immediately.

Utilizing jOOQ with testcontainers

Testcontainers is an more and more widespread 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 so forth. For instance, you would possibly determine 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 can name this perform with JDBC, as such:

strive (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 a whole lot 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 need to 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() methodology. 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");

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

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

@Check
public void testAdd() {
    strive (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 exams right into a @Earlier than block. Not a lot totally different from what you’re used to, proper? Be aware 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 to your integration exams, if you’d like.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments