Specialized Support for MyBatis3

Most of the examples shown on this site are for usage with MyBatis3 - even though the library does support other SQL runtimes like Spring JDBC templates. In addition to the examples shown elsewhere, the library has additional specialized support for MyBatis3 beyond what is shown in the other examples. This support mainly exists to support MyBatis Generator and the code generated by that tool. Even without MyBatis Generator, the techniques shown on this page may prove useful.

The goal of this support is to reduce the amount of boilerplate code needed for a typical CRUD mapper. For example, this support allows you to create a reusable SELECT method where the user only needs to specify a WHERE clause.

With version 1.1.3, specialized interfaces and utilities were added that can further simplify client code. This support enables the creation of methods that have similar functionality to methods generated in previous versions of MyBatis generator like countByExample, deleteByExample, and selectByExample. We no longer use the “by example” terms for these methods as this library has eliminated the Example class that was generated by prior versions of MyBatis Generator.

Common Mapper Support

The library includes several common mappers for MyBatis that can be injected into a MyBatis configuration as-is, or can be extended. These mappers can be used to eliminate repetitive boilerplate code for several operations - namely count queries, deletes, inserts, and updates. In addition, there is a common select mapper that can be used to avoid writing custom result maps for every query. The common select mapper provides a row mapper function that is very similar to Spring JDBC template.

Common Count, Delete, Insert, and Update Mappers

These mappers provide utility functions that execute simple queries. They can be used as-as, or can be extended. They provide methods as follows:

Mapper Methods(s)
org.mybatis.dynamic.sql.util.mybatis3.CommonCountMapper long count(SelectStatementProvider)
org.mybatis.dynamic.sql.util.mybatis3.CommonDeleteMapper int delete(DeleteStatementProvider)
org.mybatis.dynamic.sql.util.mybatis3.CommonInsertMapper<T> int insert(InsertStatementProvider<T>)
int generalInsert(GeneralInsertStatementProvider)
int insertSelect(InsertSelectStatementProvider)
int insertMultiple(MultiRowInsertStatementProvider<T>)
org.mybatis.dynamic.sql.util.mybatis3.CommonUpdateMapper int update(UpdateStatementProvider)

These mappers, as well as the common selectmapper, can be used to create a general purpose CRUD mapper as follows:

import org.apache.ibatis.annotations.Mapper;
import org.mybatis.dynamic.sql.util.mybatis3.CommonCountMapper;
import org.mybatis.dynamic.sql.util.mybatis3.CommonDeleteMapper;
import org.mybatis.dynamic.sql.util.mybatis3.CommonInsertMapper;
import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
import org.mybatis.dynamic.sql.util.mybatis3.CommonUpdateMapper;

@Mapper
public interface FooMapper extends CommonCountMapper, CommonDeleteMapper, CommonInsertMapper<Foo>, CommonSelectMapper,
        CommonUpdateMapper {
}

This mapper can be extended with default methods as shown below.

Common Select Mapper

MyBatis is very good at mapping result sets to objects - this is one of its primary differentiators. MyBatis also requires that you predefine the mappings for every possibility. This presents a challenge if you want very dynamic column lists in a query. This library provides a generalized MyBatis mapper that can assist with that problem.

The general mapper is org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper. This mapper can be injected into a MyBatis configuration as is, or it can be extended by an existing mapper.

The mapper contains three types of methods:

  1. The selectOneMappedRow and selectManyMappedRows methods allow you to use select statements with any number of columns. MyBatis will process the rows and return a Map of values, or a List of Maps for multiple rows.
  2. The selectOne and selectMany methods also allow you to use select statements with any number of columns. These methods also allow you to specify a function that will transform a Map of row values into a specific object.
  3. The other methods are for result sets with a single column. There are functions for many data types (Integer, Long, String, etc.) There are also functions that return a single value, and Optional value, or a List of values.

An example of using the mapped row methods follows:

package foo.service;
import static org.mybatis.dynamic.sql.SqlBuilder.*;

import java.util.List;
import java.util.Map;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;

public class MyService {
    public List<Map<String,Object>> generalSearch() {
        CommonSelectMapper mapper = getGeneralMapper(); // not shown

        SelectStatementProvider selectStatement = select(id, description)
            .from(foo)
            .where(description. isLike("%bar%"))
            .build()
            .render(RenderingStrategies.MYBATIS3);
        return  mapper.selectManyMappedRows(selectStatement);
    }
}

As you can see, the method returns a List of Maps containing the row values. The Map key will be the column name as returned from the database (typically in upper case), and the column value as returned from the ResultSet.getObject(). See your JDBC driver's documentation for details about how SQL types are mapped to Java types to determine the data type for your specific database.

This method works well, but usually it is better to marshal the result set into actual objects. This can be accomplished as follows:

package foo.service;
import static org.mybatis.dynamic.sql.SqlBuilder.*;

import java.util.List;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;

public class MyService {
    public List<TableCode> generalSearch() {
        CommonSelectMapper mapper = getGeneralMapper(); // not shown

        SelectStatementProvider selectStatement = select(id, description)
            .from(foo)
            .where(description. isLike("%bar%"))
            .build()
            .render(RenderingStrategies.MYBATIS3);
        return  mapper.selectMany(selectStatement, m -> {
            TableCode tc = new TableCode();
            tc.setId((Integer) m.get("ID"));
            tc.setDescription((String) m.get("DESCRIPTION"));
            return tc;
        });
    }
}

With this method you can centralize all the database specific operations in a single method.

If you only have a single column in the result set, the general mapper provides methods to retrieve the value directly. For example:

package foo.service;
import static org.mybatis.dynamic.sql.SqlBuilder.*;

import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;

public class MyService {
    public Long getAverageAge() {
        CommonSelectMapper mapper = getGeneralMapper(); // not shown

        SelectStatementProvider selectStatement = select(avg(age))
            .from(foo)
            .where(description. isLike("%bar%"))
            .build()
            .render(RenderingStrategies.MYBATIS3);
        return  mapper.selectOneLong(selectStatement);
    }
}

Count Method Support

The goal of count method support is to enable the creation of methods that execute a count query allowing a user to specify a where clause at runtime, but abstracting away all other details.

To use this support, we envision creating several methods on a MyBatis mapper interface. The first method is the standard MyBatis Dynamic SQL method that will execute a select:

@SelectProvider(type=SqlProviderAdapter.class, method="select")
long count(SelectStatementProvider selectStatement);

This is a standard method for MyBatis Dynamic SQL that executes a query and returns a long. The other methods will reuse this method and supply everything needed to build the select statement except the where clause. In lieu of writing this method, you could extend org.mybatis.dynamic.sql.util.mybatis3.CommonCountMapper instead. There are several variants of count queries that may be useful:

  1. count(*) - counts the number of rows that match a where clause
  2. count(column) - counts the number of non-null column values that match a where clause
  3. count(distinct column) - counts the number of unique column values that match a where clause

Corresponding mapper methods are as follows:

default long count(CountDSLCompleter completer) {  // count(*)
    return MyBatis3Utils.countFrom(this::count, person, completer);
}

default long count(BasicColumn column, CountDSLCompleter completer) { // count(column)
    return MyBatis3Utils.count(this::count, column, person, completer);
}

default long countDistinct(BasicColumn column, CountDSLCompleter completer) { // count(distinct column)
    return MyBatis3Utils.countDistinct(this::count, column, person, completer);
}

These methods show the use of CountDSLCompleter which is a specialization of a java.util.Function that will allow a user to supply a where clause. Clients can use the method as follows:

long rows = mapper.count(c ->
        c.where(occupation, isNull()));

There is a utility method that can be used to count all rows in a table:

long rows = mapper.count(CountDSLCompleter.allRows());

Delete Method Support

The goal of delete method support is to enable the creation of methods that execute a delete statement allowing a user to specify a where clause at runtime, but abstracting away all other details.

To use this support, we envision creating two methods on a MyBatis mapper interface. The first method is the standard MyBatis Dynamic SQL method that will execute a delete:

@DeleteProvider(type=SqlProviderAdapter.class, method="delete")
int delete(DeleteStatementProvider deleteStatement);

This is a standard method for MyBatis Dynamic SQL that executes a delete and returns an int - the number of rows deleted. In lieu of writing this method, you could extend org.mybatis.dynamic.sql.util.mybatis3.CommonDeleteMapper instead. The second method will reuse this method and supply everything needed to build the delete statement except the where clause:

default int delete(DeleteDSLCompleter completer) {
    return MyBatis3Utils.deleteFrom(this::delete, person, completer);
}

This method shows the use of DeleteDSLCompleter which is a specialization of a java.util.Function that will allow a user to supply a where clause. Clients can use the method as follows:

int rows = mapper.delete(c ->
        c.where(occupation, isNull()));

There is a utility method that can be used to delete all rows in a table:

int rows = mapper.delete(DeleteDSLCompleter.allRows());

Insert Method Support

The goal of insert method support is to remove some of the boilerplate code from insert methods in a mapper interfaces.

To use this support, we envision creating several methods on a MyBatis mapper interface. The first methods are the standard MyBatis methods that will execute an insert:

@InsertProvider(type=SqlProviderAdapter.class, method="insert")
int insert(InsertStatementProvider<PersonRecord> insertStatement);

@InsertProvider(type=SqlProviderAdapter.class, method="generalInsert")
int generalInsert(GeneralInsertStatementProvider insertStatement);

@InsertProvider(type=SqlProviderAdapter.class, method="insertMultiple")
int insertMultiple(MultiRowInsertStatementProvider<PersonRecord> insertStatement);

These methods are standard methods for MyBatis Dynamic SQL. They execute a single row insert, a general insert, and a multiple row insert. In lieu of writing these methods, you could extend org.mybatis.dynamic.sql.util.mybatis3.CommonInsertMapper instead.

These methods can be used to implement simplified insert methods:

default int insert(UnaryOperator<GeneralInsertDSL> completer) {
    return MyBatis3Utils.insert(this::generalInsert, person, completer);
}

default int insert(PersonRecord record) {
    return MyBatis3Utils.insert(this::insert, record, person, c -> 
        c.map(id).toProperty("id")
        .map(firstName).toProperty("firstName")
        .map(lastName).toProperty("lastName")
        .map(birthDate).toProperty("birthDate")
        .map(employed).toProperty("employed")
        .map(occupation).toProperty("occupation")
        .map(addressId).toProperty("addressId")
    );
}

default int insertMultiple(PersonRecord...records) {
    return insertMultiple(Arrays.asList(records));
}

default int insertMultiple(Collection<PersonRecord> records) {
    return MyBatis3Utils.insertMultiple(this::insertMultiple, records, person, c ->
        c.map(id).toProperty("id")
        .map(firstName).toProperty("firstName")
        .map(lastName).toProperty("lastName")
        .map(birthDate).toProperty("birthDate")
        .map(employed).toProperty("employed")
        .map(occupation).toProperty("occupation")
        .map(addressId).toProperty("addressId")
    );
}

The first insert method is a general insert and can be used to create arbitrary inserts with different combinations of columns specified. The other methods have the insert statements mapped to a POJO “record” class that holds values for the insert statement.

Select Method Support

The goal of select method support is to enable the creation of methods that execute a select statement allowing a user to specify a where clause and/or order by clause at runtime, but abstracting away all other details.

To use this support, we envision creating several methods on a MyBatis mapper interface. The first two methods are the standard MyBatis Dynamic SQL method that will execute a select:

@SelectProvider(type=SqlProviderAdapter.class, method="select")
@Results(id="PersonResult", value= {
        @Result(column="A_ID", property="id", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="first_name", property="firstName", jdbcType=JdbcType.VARCHAR),
        @Result(column="last_name", property="lastName", jdbcType=JdbcType.VARCHAR),
        @Result(column="birth_date", property="birthDate", jdbcType=JdbcType.DATE),
        @Result(column="employed", property="employed", jdbcType=JdbcType.VARCHAR),
        @Result(column="occupation", property="occupation", jdbcType=JdbcType.VARCHAR)
})
List<PersonRecord> selectMany(SelectStatementProvider selectStatement);
    
@SelectProvider(type=SqlProviderAdapter.class, method="select")
@ResultMap("PersonResult")
Optional<PersonRecord> selectOne(SelectStatementProvider selectStatement);

These two methods are standard methods for MyBatis Dynamic SQL. They execute a select and return either a list of records, or a single record.

We also envision creating a static field for a reusable list of columns for a select statement:

BasicColumn[] selectList =
    BasicColumn.columnList(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation, addressId);

The selectOne method can be used to implement a generalized select one method:

default Optional<PersonRecord> selectOne(SelectDSLCompleter completer) {
    return MyBatis3Utils.selectOne(this::selectOne, selectList, person, completer);
}

This method shows the use of SelectDSLCompleter which is a specialization of a java.util.Function that will allow a user to supply a where clause.

The general selectOne method can be used to implement a selectByPrimaryKey method:

default Optional<PersonRecord> selectByPrimaryKey(Integer id_) {
    return selectOne(c ->
        c.where(id, isEqualTo(id_))
    );
}

The selectMany method can be used to implement generalized select methods where a user can specify a where clause and/or an order by clause. Typically, we recommend two of these methods - for select, and select distinct:

default List<PersonRecord> select(SelectDSLCompleter completer) {
    return MyBatis3Utils.selectList(this::selectMany, selectList, person, completer);
}
    
default List<PersonRecord> selectDistinct(SelectDSLCompleter completer) {
    return MyBatis3Utils.selectDistinct(this::selectMany, selectList, person, completer);
}

These methods show the use of SelectDSLCompleter which is a specialization of a java.util.Function that will allow a user to supply a where clause and/or an order by clause.

Clients can use the methods as follows:

List<PersonRecord> rows = mapper.select(c ->
        c.where(id, isEqualTo(1))
        .or(occupation, isNull()));

There are utility methods that will select all rows in a table:

List<PersonRecord> rows =
    mapper.select(SelectDSLCompleter.allRows());

The following query will select all rows in a specified order:

List<PersonRecord> rows =
    mapper.select(SelectDSLCompleter.allRowsOrderedBy(lastName, firstName));

Update Method Support

The goal of update method support is to enable the creation of methods that execute an update statement allowing a user to specify values to set and a where clause at runtime, but abstracting away all other details.

To use this support, we envision creating several methods on a MyBatis mapper interface. The first method is a standard MyBatis Dynamic SQL method that will execute a update:

@UpdateProvider(type=SqlProviderAdapter.class, method="update")
int update(UpdateStatementProvider updateStatement);

This is a standard method for MyBatis Dynamic SQL that executes a query and returns an int - the number of rows updated. In lieu of writing this method, you could extend org.mybatis.dynamic.sql.util.mybatis3.CommonUpdateMapper instead. The second method will reuse this method and supply everything needed to build the update statement except the values and the where clause:

default int update(UpdateDSLCompleter completer) {
    return MyBatis3Utils.update(this::update, person, completer);
}

This method shows the use of UpdateDSLCompleter which is a specialization of a java.util.Function that will allow a user to supply values and a where clause. Clients can use the method as follows:

int rows = mapper.update(c ->
    c.set(occupation).equalTo("Programmer")
    .where(id, isEqualTo(100)));

All rows in a table can be updated by simply omitting the where clause:

int rows = mapper.update(c ->
    c.set(occupation).equalTo("Programmer"));

It is also possible to write a utility method that will set values. For example:

static UpdateDSL<UpdateModel> updateSelectiveColumns(PersonRecord row,
        UpdateDSL<UpdateModel> dsl) {
    return dsl.set(id).equalToWhenPresent(row::getId)
            .set(firstName).equalToWhenPresent(row::getFirstName)
            .set(lastName).equalToWhenPresent(row::getLastName)
            .set(birthDate).equalToWhenPresent(row::getBirthDate)
            .set(employed).equalToWhenPresent(row::getEmployed)
            .set(occupation).equalToWhenPresent(row::getOccupation);
}

This method will selectively set values if corresponding fields in a record are non null. This method can be used as follows:

rows = mapper.update(h ->
    updateSelectiveColumns(updateRecord, h)
    .where(id, isEqualTo(100)));