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.

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 {
}

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);
    }
}

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;
        });
    }
}

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)));