JSON type is handled incorrectly for H2 database

[justinplus]

MyBatis version

3.5.10

Database vendor and version

h2, version: 1.4.200

Description

MyBatis maps JSON type to Java String, but for h2, FORMAT JSON is required when inserting a JSON column as mentioned in its doc.

To set a JSON value with java.lang.String in a PreparedStatement use a FORMAT JSON data format (INSERT INTO TEST(ID, DATA) VALUES (?, ? FORMAT JSON)). Without the data format VARCHAR values are converted to a JSON string values.
link: https://h2database.com/html/datatypes.html#json_type

Also see issue in: h2database/h2database#3088

Is this a known issue?

[harawata]

Hello @justinplus ,

MyBatis does not add FORMAT JSON clause automatically.
You have to write it in your SQL.

[justinplus]

Hi @harawata,

Is that possible to do this when using dynamic sql?
Would MyBatis consider to support this case?

[jeffgbutler]

If you are referring to MyBatis Dynamic SQL, then we support this use case by using a custom rendering strategy. There's an example of doing something similar for PostgreSQL here: https://github.com/mybatis/mybatis-dynamic-sql/tree/master/src/test/java/examples/custom_render

[justinplus]

I can solve it by using customized rendering strategy, but it's not smart enough, since MyBatis cannot detect JSON column of H2 SQL provider and apply this strategy automatically.

public class H2JsonColumnRenderingStrategy extends MyBatis3RenderingStrategy {
    public static final RenderingStrategy INSTANCE = new H2JsonColumnRenderingStrategy();

    private H2JsonColumnRenderingStrategy() {};

    @Override
    public String getFormattedJdbcPlaceholder(BindableColumn<?> column, String prefix, String parameterName) {
        return super.getFormattedJdbcPlaceholder(column, prefix, parameterName) + " FORMAT JSON";
    }
}

[jeffgbutler]

Are you talking about MyBatis Generator, or MyBatis Dynamic SQL, or MyBatis core? We don't have enough context to help you.

[awxiaoxian2020]

Please give a complete demo to describe what you want :)

[harawata]

No response. Closing.