Apache Derby中的用户定义类型可以替代ENUM [英] User-defined types in Apache Derby as ENUM replacements

问题描述

我使用Apache Derby作为内存中的模拟数据库，对使用jOOQ与MySQL一起工作的某些代码进行单元测试。

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

生产数据库在某些情况下使用枚举字段（这是给定的，超出了此问题的范围-我知道枚举是不好的，但现在不能更改此部分），因此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.

不幸的是，Derby不支持枚举，当我尝试在Derby中创建数据库（通过jOOQ SQL生成器）时，出现错误。

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

我的解决方案是通过包装相关的jOOQ生成的枚举Java类来模拟枚举的用户定义类型。因此，例如，如果我在表物品中具有枚举字段种类，则jOOQ SQL生成器将创建Derby表创建讨论 stuffs_kind 的SQL。

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.

为了支持这一点，我创建了 my.project.tests.StuffsKindDebyEnum 类，该类包装了jOOQ生成的枚举类型 my.project.model.StuffsKind 。然后，在运行jOOQ数据库创建SQL之前，我通过Derby运行以下SQL：

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

然后我使用jOOQ插入新记录时，jOOQ会生成SQL看起来有点像这样：

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)

但是绑定 kind 参数的字符串值（如预期的那样），它适用于MySQL，但对于Derby，我得到一个例外：

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'

在研究了解决此问题的各种方法之后（包括尝试将枚举视为简单的VARCHAR） ），在我放弃测试自己的能力之前使用jOOQ的代码，是否有办法让Derby将varchar投射为用户定义的类型？如果可以放置一些可以处理此问题的Java代码，那将不是问题，因为我可以简单地通过 StuffsKind.valueOf（value）将字符串转换为正确的枚举类型，但仔细阅读了（非常少量的）Derby文档之后，我不知道它是否应该有可能。

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.

欢迎任何想法！

推荐答案

实施对方言敏感的自定义数据类型绑定：

正确的解决方法将使用对方言敏感的自定义数据类型绑定：
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

然后可以实现绑定，例如绑定变量SQL生成如下：

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());
}

显然，您还必须实现其他方法告诉jOOQ如何将变量绑定到JDBC PreparedStatement ，或者如何从 ResultSet

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

另一种更简单的方法可能是避免使用特定于供应商的功能，而只需使用 VARCHAR 在两个数据库中。您仍然可以将 VARCHAR 映射到Java 枚举使用jOOQ Converter 键入，在两个数据库中都将以相同的方式工作。

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.

更简单的方法是直接在MySQL上测试您的应用程序，例如在内存泊坞窗虚拟化中。数据库供应商及其功能之间存在很多差异，并且在某些时候，解决这些差异以仅获得稍微更快的测试似乎并不合理。

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.

当然，唯一的例外是，如果必须在生产中同时支持Derby和MySQL，则在这种情况下，数据类型绑定再次是最佳解决方案。

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.

这篇关于Apache Derby中的用户定义类型可以替代ENUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！