Once you write saved procedures and capabilities in your database, you need to guarantee their correctness, identical to together with your Java code. In Java, that is executed with unit exams, 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 may write a take a look at 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 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 extra views corresponding to in Eclipse/IntelliJ
- There may not be any such utility on your database merchandise in any respect
- You may need to assist a number of database merchandise and have to take care of exams for all of them, ideally written in Java
- Your process integration exams could work together with some Java code, so that you need 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 trouble of binding to procedures by way of JDBC immediately.
Utilizing jOOQ with testcontainers
Testcontainers is an more and more fashionable framework for database integration testing in Docker. You may 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 may resolve 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 possibly can 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 loads of handbook 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() technique. 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 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:
@Take a look at
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 exams 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 advocate utilizing testcontainers as nicely, as a part of your construct. You may then re-use the code generator testcontainers occasion on your integration exams, in order for you.
