1

I'm using Apache Derby as an in-memory mock database for unit testing some code that works with MySQL using jOOQ.

The production database uses enums for certain fields (this is a given and out of scope of this question - I know enums are bad but I can't change this part now), so jOOQ generates code to handle the enums.

Unfortunately, Derby does not support enums and when I try to create the database in Derby (from jOOQ SQL generator), I get errors.

My solution was to user-defined types that mimic the enum by wrapping the relevant jOOQ generated enum Java class. So, for example, if I have an enum field kind in the table stuffs, jOOQ SQL generator creates Derby table creation SQL that talks about stuffs_kind.

To support this I created the class my.project.tests.StuffsKindDebyEnum that wraps the jOOQ generated enum type my.project.model.StuffsKind. I then run the following SQL through Derby, before running the jOOQ database creation SQL:

CREATE TYPE stuffs_kind EXTERNAL NAME 'my.project.tests.StuffsKindDerbyEnum' LANGUAGE JAVA

When I then use jOOQ to insert new records, jOOQ generates SQL that looks somewhat like this:

insert into "schema"."stuffs" ("text", "kind") 
  values (cast (? as varchar(32672)), cast(? as stuffs_kind)

But binds a string value to the kind argument (as expected), and it work for MySQL but with Derby I get an exception:

java.sql.SQLDataException: An attempt was made to get a data value of type
  '"APP"."STUFFS_KIND"' from a data value of type 'VARCHAR'

After looking at all kinds of ways to solve this problem (including trying to treat enums as simple VARCHARs), and before I give up on being able to test my jOOQ-using code, is there a way to get Derby to "cast" varchar into user-defined types? If could put some Java code that can handle that, it will not be a problem as I can simply do StuffsKind.valueOf(value) to convert a string to the correct enum type, but after perusing the (very minimal) Derby documentation, I can't figure out if it is even should be possible.

Any ideas are welcome!

Guss
  • 30,470
  • 17
  • 104
  • 128
  • Once you start using vendor-specific features, using an "in memory mock database" will always be painful. What about using an actual MySQL database (e.g. in Docker) for your integration tests? – Lukas Eder Jul 26 '17 at 08:35
  • Unless I find some other solution, I'd have to go that way, but I'd really prefer not to because it will cause a lot of headache (can't run tests in parallel, painful setup process etc). – Guss Aug 02 '17 at 08:19
  • Another option, maybe, is this thing: https://github.com/vorburger/MariaDB4j . I haven't tried it yet, but it looks interesting for my use case. – Guss Aug 02 '17 at 08:25
  • Why can't you run tests in parallel on Docker and/or [Testcontainers](http://testcontainers.org) or similar? And what's painful about the setup process? – Lukas Eder Aug 02 '17 at 10:37
  • I'm not familiar with Testcontainers, I'll check it out. When I say painful, I mean compared to JUnit - which is basically: write your standalone test, don't worry about a thing and everything will work out. Most things are a headache compared to that. – Guss Aug 02 '17 at 15:35
  • 1
    I've reviewed Testcontainers, and it looks very promising. MariaDB4j would probably be a better choice if I had needed Windows support, but as that is not the case I'll try to move forward with Testcontainers, but I'll also try to see if your answer below can solve my problem. – Guss Aug 02 '17 at 16:41

1 Answers1

1

Implementing a dialect sensitive custom data type binding:

The proper way forward here would be to use a dialect sensitive, custom data type binding: https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

The binding could then implement, e.g. the bind variable SQL generation as follows:

@Override
public void sql(BindingSQLContext<StuffsKindDerbyEnum> ctx) throws SQLException {
    if (ctx.family() == MYSQL)
        ctx.render().visit(DSL.val(ctx.convert(converter()).value()));
    else if (ctx.family() == DERBY)
        ctx.render()
           .sql("cast(
           .visit(DSL.val(ctx.convert(converter()).value()))
           .sql(" as varchar(255))");
    else
        throw new UnsupportedOperationException("Dialect not supported: " + ctx.family());
}

You'd obviously also have to implement the other methods that tell jOOQ how to bind your variable to a JDBC PreparedStatement, or how to fetch it from a ResultSet

Avoiding the MySQL enum

Another, simpler way forward might be to avoid the vendor-specific feature and just use VARCHAR in both databases. You can still map that VARCHAR to a Java enum type using a jOOQ Converter that will work the same way in both databases.

Simplify testing by avoiding Derby

A much simpler way forward is to test your application directly on MySQL, e.g. on an in-memory docker virtualisation. There are a lot of differences between database vendors and their features, and at some point, working around those differences just to get slightly faster tests doesn't seem reasonable.

The exception is, of course, if you have to support both Derby and MySQL in production, in case of which the data type binding is again the best solution.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509