MyBatis and Oracle Database

While I was using MyBatis with the Oracle Database, I ran into two problems:

1. Generating primary keys when inserting database rows
2. Mapping Java boolean type to a database type

The next sections describe how I solved these problems.

Inserting new rows

Unlike other databases like MySQL, the Oracle database does not provide the functionality to generate primary keys automatically when inserting rows. Normally, I would create a sequence together with a trigger in the Oracle database. The sequence is a thread-safe number generator, and the trigger is custom code that is called on certain type of events. In this case an "insert" event, which will update the row with the primary key.

With MyBatis, we still need the sequence. The trigger functionality is provided by MyBatis. Let's get started by creating an example class in Java. I left the "getters" and "setters" out, but we still need them in the actual code ofcouse.

public class Client {
    private Integer id;
    private String name;

    // Setters and Getters for the attributes here.
}

Next, we have to prepare the database by create a table that corresponds with the "Client" class, and a sequence.

CREATE TABLE client (
    id INTEGER primary key,
    name VARCHAR2(100)
);

CREATE SEQUENCE client_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

MyBatis uses "mapper" interfaces (defined and provided by us) to map the class to actual SQL-statements. Additional configuration can be provided by either XML, or annotations. When the configuration is simple, I prefer using annotations. Complex configurations are often only possible by using XML. The following mapper interface provides mapping for inserting the "Client" class. The interface is only used by MyBatis, we don't have to provide an actual implementation of it.

public interface ClientMapper {
  @Insert("INSERT INTO client (id, name) VALUES (#{id}, #{name})")
  @SelectKey(
            keyProperty = "id",
            before = true,
            resultType = Integer.class,
            statement = { "SELECT client_seq.nextval AS id FROM dual" })
  public Integer insertClient(Client client);
}

The "@SelectKey" annotation tells MyBatis to get the primary key value from the sequence, and use it as primary key when executing the insert statement.

Mapping boolean types

The final example illustrates how we deal with boolean types when using MyBatis. We extend the "Client" class with a boolean attribute "admin".

public class Client {
    private Integer id;
    private String name;
    private boolean admin;

    // Setters and Getters for the attributes here.
}

Add a "VARCHAR(1)" column that acts like a boolean attribute to the corresponding "Client" database table. The column value "Y" corresponds to boolean value true, and "N" to boolean false.

CREATE TABLE client (
    id INTEGER primary key,
    name VARCHAR2(100),
    admin varchar(1)
);

MyBatis doesn't know how the Java boolean is mapped to a database type, as there's no primitive database boolean type. We have to provide MyBatis with a converter, which implements the "TypeHandler" interface. This converter will then be used to convert a Java boolean to a database type, and vice versa.

package com.javaeenotes;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

public class BooleanTypeHandler implements TypeHandler {

    private static final String YES = "Y";
    private static final String NO = "N";


    @Override
    public Boolean getResult(ResultSet resultSet, String name)
            throws SQLException {

        return valueOf(resultSet.getString(name));
    }


    @Override
    public Boolean getResult(CallableStatement statement, int position)
            throws SQLException {

        return valueOf(statement.getString(position));
    }


    @Override
    public void setParameter(
            PreparedStatement statement,
            int position,
            Boolean value,
            JdbcType jdbcType) throws SQLException {

        statement.setString(position, value.booleanValue() ? YES : NO);
    }


    private Boolean valueOf(String value) throws SQLException {

        if (YES.equalsIgnoreCase(value)) {
            return new Boolean(true);
        } else if (NO.equalsIgnoreCase(value)) {
            return new Boolean(false);
        } else {
            throw new SQLException("Unexpected value "
                    + value
                    + " found where "
                    + YES
                    + " or "
                    + NO
                    + " was expected.");
        }
    }
}

Now, configure MyBatis to use the type handler for booleans in the mapper interface where needed.

    @Insert("INSERT INTO client (id, name, admin) VALUES (#{id}, #{name}, #{admin,typeHandler=com.javaeenotes.BooleanTypeHandler})")
    @SelectKey(
            keyProperty = "id",
            before = true,
            resultType = Integer.class,
            statement = { "SELECT client_seq.nextval AS id FROM dual" })
    public Integer insertClient(Client client);

    @Select("SELECT * FROM client WHERE id = #{clientId}")
    @Results(
            value = {
                @Result(
                    property = "id",
                    column = "id"),

                @Result(
                    property = "name",
                    column = "name"),

                @Result(
                    property = "admin",
                    column = "admin",
                    typeHandler = BooleanTypeHandler.class)
             })
    public Client getClient(int clientId);

    @Update("UPDATE client SET name = #{name}, admin = #{admin,typeHandler=com.javaeenotes.BooleanTypeHandler} WHERE id = #{id}")
    public void updateClient(Client client);