Mybatis

21 February 2015

Unlike Hibernate, MyBatis lets developers think in terms of SQL. Mybatis’ essential functionalities are:

  • Automatic mapping between Java POJOs and table records.
  • Easy construction of SQL statements.

We only want to write one SQL statement for each type of SQL. We don’t consider complex SQL statements such as SQL JOIN and UNION here. The structures of SQL statements are as follows;

-- SELECT
select 
from
where
grouby
order by

-- INSERT
insert into 
values

-- UPDATE
update
set
where

-- DELETE
delete from
where

One MyBatis SQL statement only allows one input parameter. We have two options for the input parameter. One solution is to use a big Java POJO. But we usually want to reuse Java domain objects for this. We need to add properties to such Java domain objects. Such properties are meaningless in the problem domain. They polute the domain objects.

Another solution is to use a Java map. We can use such a Java map like a JSON object. We can dynamically manipulate the Java map. And we can still use Java POJO for such as an input parameter. We can use Jackson ObjectMapper do a convertion beforehand.

We can refer to map values recursively for input paramter. For example, key1.key2.key3 refer to key3 of key2 map of key1 map of the input parameter map.

In foreach, collection can be something like entries if entries is a key of the input parameter map.

In foreach, neither item attribute nor index attribute has a default value. They must be specified.

<foreach collection="campaigns" item="item" index="index" separator=",">

Tips

Parameter

For the following query:

  <delete id="delete" parameterType="int">
    delete from freight_house where id = #{id}
  </delete>

Both of the following two approaches work. One is with a int paramter:

session.delete("delete", 28);

The other is is with a map:

Map map = new HashMap();
map.put("id" 28);
session.delete("delete", 28);

Transaction

JDBC does not have a method to start a transaction. MyBatis automatically rollback failed transactions. For example, the following code will neither delete row 3 nor row 4.

    try(SqlSession s = Config.getSsf().openSession();) {
      s.delete("FreightHouse.deleteTest", 3);
      if (true) throw new RuntimeException();
      s.delete("FreightHouse.deleteTest", 4);
      s.commit();
    }

SqlSession openSession() does not have autocommit enabled.

Return Value

For the following query which contains two delete statemewnts, SqlSession.delete returns the rows affected by the first delete statement.

  <delete id="twoDeletes">
    delete from freight_house where id = 888;
    delete from freight_house where id = 5 or id = 6;
  </delete>

For multiple SQL statements which update database, delete, insert and update methods of SqlSession return the rows affected by the first SQL statement. For example, the row number affected by the insert SQL is returned.

  <update id="update">
    insert into category(name) values('golang'); 
    update category set name = 'two' where id = 10000;
  </update>

In the following list, any MyBatis statement in item 1 can contains any SQL statement in item 2.

  1. MyBatis insert, update and delete statements.
  2. SQL insert, update and delete statements.

White Spaces

For the following code, Mybatis will insert a while space between these two ifs:

<if test="name != null and name != ''">and name like surr_with_p(#{name})</if><if test="typeId != null and typeId != ''">and type_id = #{typeId}</if>

List

MyBatis only accepts List or native array. It does not accept Set.

Null Tests

typeId != null and typeId != '' is preferred over typeId != null. If a SQL statement containing such code is accessed from JS side and no type conversion is done on Java side, it is very possible that typeId is set to '' even typeId is a integer on Java side. The reason is that empty string is default value in many situations for JS.

In the following code, _parameter checking can be avoided.

<select id="select">
  <where>
    <if test="_parameter != null and name != null and name != ''">
      and name = #{name}
    </if>
  </where>
</select>

The following code works for a map input paramter. But it does not work a Java bean which does not have diameter property.

<if test="diameter != null and diameter != ''">
  and diameter = #{diameter}
</if>

Java Generics

MyBatis does not ensure java generics. Java code:

public class Item {
  // ...
}
public class Item_ extends Item {
  // ...
}
public class Seller {
  private List<Item_> items;

  public List<Item_> getItems() {
    return categories;
  }

  public void setItems(List<Item_> items) {
    this.items = items;
  }
}

XML:

  <resultMap id="Item" type="Item">
  </resultMap>
  <select id="selectItems" resultMap="Item">
    <!-- ... -->
  </select>

MyBatis will happily return an items which is a list of Item.

try-with-resources statement

The following code from Java API works:

try (SqlSession session = sqlSessionFactory.openSession()) {
    // following 3 lines pseudocode for "doing some work"
    session.insert(...);
    session.update(...);
    session.delete(...);
    session.commit();
}

The reason is that the same link says

Most of the time you won’t have to call rollback(), as MyBatis will do that for you if you don’t call commit.

XML Escaping in SQL

> can be processed correctly by Mybatis. But < must be escaped as &lt;. The reason is that < starts a XML tag.

It is better to use XML character entity references as demanded by XML all the time. So > should be escaped as &gt;.