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, some of 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 some of the 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.
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 two 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 second method will reuse this method and supply everything needed to build the select statement except the where clause:
default long count(CountDSLCompleter completer) {
return MyBatis3Utils.countFrom(this::count, person, completer);
}This method shows 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());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. 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());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 two methods are the standard MyBatis Dynamic SQL method that will execute an insert:
@InsertProvider(type=SqlProviderAdapter.class, method="insert")
int insert(InsertStatementProvider<PersonRecord> insertStatement);
@InsertProvider(type=SqlProviderAdapter.class, method="insertMultiple")
int insertMultiple(MultiRowInsertStatementProvider<PersonRecord> insertStatement);These two methods are standard methods for MyBatis Dynamic SQL. They execute a single row insert and a multiple row insert.
These methods can be used to implement simplified insert methods:
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")
);
}In the mapper, only the column mappings need to be specified and no other boilerplate code is needed.
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 MyBatis3SelectListHelper 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.selectByExample(SelectDSLCompleter.allRows());The following query will select all rows in a specified order:
List<PersonRecord> rows =
mapper.selectByExample(SelectDSLCompleter.allRowsOrderedBy(lastName, firstName));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. 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 record,
UpdateDSL<UpdateModel> dsl) {
return dsl.set(id).equalToWhenPresent(record::getId)
.set(firstName).equalToWhenPresent(record::getFirstName)
.set(lastName).equalToWhenPresent(record::getLastName)
.set(birthDate).equalToWhenPresent(record::getBirthDate)
.set(employed).equalToWhenPresent(record::getEmployed)
.set(occupation).equalToWhenPresent(record::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)));Prior to version 1.1.3, it was also possible to write reusable methods, but they were a bit inconsistent with other helper methods. Mappers of this style are deprecated and the support classes for mappers of this style will be removed in a future version of this library.
For example, it is possible to write a mapper interface like this:
import static examples.simple.PersonDynamicSqlSupport.*;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.dynamic.sql.select.MyBatis3SelectModelAdapter;
import org.mybatis.dynamic.sql.select.QueryExpressionDSL;
import org.mybatis.dynamic.sql.select.SelectDSL;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
@Mapper
public interface LegacyPersonMapper {
@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, typeHandler=YesNoTypeHandler.class),
@Result(column="occupation", property="occupation", jdbcType=JdbcType.VARCHAR)
})
List<PersonRecord> selectMany(SelectStatementProvider selectStatement);
default QueryExpressionDSL<MyBatis3SelectModelAdapter<List<PersonRecord>>> selectByExample() {
return SelectDSL.selectWithMapper(this::selectMany, id.as("A_ID"), firstName, lastName, birthDate, employed, occupation)
.from(simpleTable);
}
}Notice the selectByExample method - it specifies the column list and table name and returns the intermediate builder that can be used to finish the WHERE clause. It also reuses the selectMany mapper method. Mapper methods built using this added support all finish with an execute method that builds the statement and executes the mapper method.
The code is used like this:
List<PersonRecord> rows = mapper.selectByExample()
.where(id, isEqualTo(1))
.or(occupation, isNull())
.build()
.execute();