MyBatis support for multiple databases

Question

I have different customers that use different database vendores (postgres, oracle, mysql etc.)

I would like to write my code once and be able to run against different db's.

What would be the "mybatis" way of achieving this?

Issues I've found so far for example:

• postgres has a notion of "if not exist" in the create sql statements. oracle doesn't support that.
• oracle has no "limit" and "offset" support in the sql syntax while others db's do.
• text (postgres) vs clob (oracle and others) jdbc types in DDL statements.

I don't want to duplicate my queries (which is what I did so far). Probably there's a more elegant way to do this.

I am using mybatis java annotaions.

Answer 1

You're saying you're using annotations, but I'd suggest using XML for this. I generally find it clearer, especially because there are parts of the query you'd need to change completely anyway.

Here is a DDL example where you get two separate XML elements for the same method, but different database IDs. Those tables are very similar, but because of the way you check whether the table already exists and the types are substantially different, you can't really avoid having distinct SQL code for this:

<update id="createTables" databaseId="postgresql">
    DO $$
    BEGIN
        CREATE TABLE IF NOT EXISTS item (
            id SERIAL PRIMARY KEY,
            content TEXT,
            creation_datetime TIMESTAMPTZ DEFAULT NOW(),
            modification_datetime TIMESTAMPTZ
        );
    END$$
</update>
<update id="createTables" databaseId="sqlserver">
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'item')
    BEGIN
        CREATE TABLE item (
            id INT IDENTITY PRIMARY KEY,
            content NVARCHAR(MAX),
            creation_datetime DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET(),
            modification_datetime DATETIMEOFFSET
        );
    END
</update>

You can also have the query in a SQL element but have different sections change using <if> (or <choose>) depending on the database ID. That works fine if the changes are small:

<delete id="deleteItem">
    DELETE FROM my_item
    WHERE gid=CAST(#{gid} AS <if test="_databaseId == 'postgresql'">UUID</if><if test="_databaseId == 'sqlserver'">UNIQUEIDENTIFIER</if>)
</delete>

<select id="getLatestSomething" resultType="test.Something">
    SELECT <if test="_databaseId == 'sqlserver'">TOP 1</if> *
    FROM something
    ORDER BY creation_datetime DESC
    <if test="_databaseId == 'postgresql'">
    LIMIT 1
    </if>
</select>

Choosing between having separate query elements for different database IDs or just conditional fragments within the same query element is a matter of readability. It can be quite subjective depending on the complexity of the query.

For example, I find the following "upsert" using PostgreSQL and SQL Server rather difficult to read. It would be better in separate elements:

<insert id="insertStuff" parameterType="somestuff.Stuff">
    <if test="_databaseId == 'postgresql'">
        INSERT INTO my_stuff (...)
    </if>
    <if test="_databaseId == 'sqlserver'">
        MERGE INTO my_stuff WITH (HOLDLOCK) AS t USING (
    </if>
            VALUES (#{...},
                    <if test="_databaseId == 'postgresql'">
                        CAST(#{jsonData} AS JSONB)
                    </if>
                    <if test="_databaseId == 'sqlserver'">
                        #{jsonData}
                    </if>
            )
    <if test="_databaseId == 'postgresql'">
            ON CONFLICT DO NOTHING
    </if>
    <if test="_databaseId == 'sqlserver'">
        )
              AS s (...)
              ON t....=s....
                 AND t....=s....
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (...)
            VALUES (s...., s....);
    </if>
</insert>

There's more about all this in the MyBatis Dynamic SQL documentation.

---

Assuming your XML mapper file is in mypackage/MyMapper.xml, you can use a MyMapper Java interface in a package matching that directory. Nothing specific to the database ID there.

package mypackage;

public interface MyMapper {
    void createTables();
    void deleteItem(@Param("gid") UUID gid);
    Something getLatestSomething();
}

When using the MyBatis with Spring, you can set the vendor configuration like this:

@Bean
public VendorDatabaseIdProvider vendorDatabaseIdProvider() {
    Properties vendorProperties = new Properties();
    vendorProperties.setProperty("PostgreSQL", "postgresql");
    vendorProperties.setProperty("SQL Server", "sqlserver");
    // Add others as required, this will look for the substring in the product name coming
    // from the database metadata.

    // ...
    VendorDatabaseIdProvider dbIdProvider = new VendorDatabaseIdProvider();
    dbIdProvider.setProperties(vendorProperties);
    return dbIdProvider;
}

@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource, ApplicationContext appContext,
        VendorDatabaseIdProvider vendorDatabaseIdProvider) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setDatabaseIdProvider(vendorDatabaseIdProvider);

    SqlSessionFactory factory = bean.getObject();
    return factory;
}

If you're not using Spring, you should be able to configure the DatabaseIdProvider using the XML configuration.

You don't strictly need a database ID provider. All it's doing is setting the database ID in the configuration, based on the product name it gets from the DataSource. Something along those lines:

String databaseId = databaseIdProvider.getDatabaseId(dataSource);
configuration.setDatabaseId(databaseId);

(If you look at the code of org.apache.ibatis.mapping.VendorDatabaseIdProvider, you'll see that databaseIdProvider.getDatabaseId(...) is just looking for a matching substring in what's returned from DatabaseMetaData.getDatabaseProductName(). You could equally do this manually via other settings if needed.)

Note that, when databaseId="" is used directly as an attribute on an XML element, there is no underscore, but when it's used as a test condition, it's called _databaseId.