Spring Support

The library supports generating SQL that is compatible with the Spring Framework's named parameter JDBC templates.

The SQL statement objects are created in exactly the same way as for MyBatis - only the rendering strategy changes. For example:

    SelectStatementProvider selectStatement = select(id, firstName, lastName, fullName)
            .from(generatedAlways)
            .where(id, isGreaterThan(3))
            .orderBy(id.descending())
            .build()
            .render(RenderingStrategies.SPRING_NAMED_PARAMETER);

The generated SQL statement providers are compatible with Spring's NamedParameterJdbcTemplate in all cases. The only challenge comes with presenting statement parameters to Spring in the correct manner. To make this easier, the library provides a utility class org.mybatis.dynamic.sql.util.spring.NamedParameterJdbcTemplateExtensions that executes statements properly in all cases and hides the complexity of rendering statements and formatting parameters. All the examples below will show usage both with and without the utility class.

Type Converters for Spring

Spring JDBC templates do not have the equivalent of a type handler in MyBatis3. This is generally not a problem in processing results because you can build type conversions into your row handler. If you were manually creating the parameter map that is used as input to a Spring template you could perform a type conversion there too. But when you use MyBatis Dynamic SQL, the parameters are generated by the library, so you do not have the opportunity to perform type conversions directly.

To address this issue, the library provides a parameter type converter that can be used to perform a type conversion before parameters are placed in a parameter map.

For example, suppose we want to use a Boolean in Java to represent the value of a flag, but in the database the corresponding field is a CHAR field that expects values “true” or “false”. This can be accomplished by using a ParameterTypeConverter. First create the converter as follows:

public class TrueFalseParameterConverter implements ParameterTypeConverter<Boolean, String> {
    @Override
    public String convert(Boolean source) {
        return source == null ? null : source ? "true" : "false";
    }
}

The type converter is compatible with Spring's existing Converter interface. Associate the type converter with a SqlColumn as follows:

...
    public final SqlColumn<Boolean> employed = column("employed", JDBCType.VARCHAR)
            .withParameterTypeConverter(new TrueFalseParameterConverter());
...

MyBatis Dynamic SQL will now call the converter function before corresponding parameters are placed into the generated parameter map. The converter will be called in the following cases:

With a general insert statement when using the set(...).toValue(...) or set(...).toValueWhenPresent(...) mappings
With an update statement when using the set(...).equalTo(...) or set(...).equalToWhenPresent(...) mappings
With where clauses in any statement type that contain conditions referencing the field

Executing Select Statements

The Spring Named Parameter JDBC template expects an SQL statement with parameter markers in the Spring format, and a set of matched parameters. MyBatis Dynamic SQL will generate both. The parameters returned from the generated SQL statement can be wrapped in a Spring MapSqlParameterSource. Spring also expects you to provide a row mapper for creating the returned objects.

The following code shows a complete example without the utility class:

    NamedParameterJdbcTemplate template = getTemplate();  // not shown

    SelectStatementProvider selectStatement = select(id, firstName, lastName, fullName)
            .from(generatedAlways)
            .where(id, isGreaterThan(3))
            .orderBy(id.descending())
            .build()
            .render(RenderingStrategies.SPRING_NAMED_PARAMETER);

    SqlParameterSource namedParameters = new MapSqlParameterSource(selectStatement.getParameters());
    List<GeneratedAlwaysRecord> records = template.query(selectStatement.getSelectStatement(), namedParameters,
        (rs, rowNum) -> {
            GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
            record.setId(rs.getInt(1));
            record.setFirstName(rs.getString(2));
            record.setLastName(rs.getString(3));
            record.setFullName(rs.getString(4));
            return record;
        });

The following code shows a complete example with the utility class:

    NamedParameterJdbcTemplate template = getTemplate();  // not shown
    NamedParameterJdbcTemplateExtensions extensions = new NamedParameterJdbcTemplateExtensions(template);

    Buildable<SelectModel> selectStatement = select(id, firstName, lastName, fullName)
            .from(generatedAlways)
            .where(id, isGreaterThan(3))
            .orderBy(id.descending());

    List<GeneratedAlwaysRecord> records = extensions.selectList(selectStatement,
        (rs, rowNum) -> {
            GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
            record.setId(rs.getInt(1));
            record.setFirstName(rs.getString(2));
            record.setLastName(rs.getString(3));
            record.setFullName(rs.getString(4));
            return record;
        });

The utility class also includes a selectOne method that returns an Optional. An example is shown below:

    NamedParameterJdbcTemplate template = getTemplate();  // not shown
    NamedParameterJdbcTemplateExtensions extensions = new NamedParameterJdbcTemplateExtensions(template);

    Buildable<SelectModel> selectStatement = select(id, firstName, lastName, fullName)
            .from(generatedAlways)
            .where(id, isEqualTo(3));

    Optional<GeneratedAlwaysRecord> record = extensions.selectOne(selectStatement,
        (rs, rowNum) -> {
            GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
            record.setId(rs.getInt(1));
            record.setFirstName(rs.getString(2));
            record.setLastName(rs.getString(3));
            record.setFullName(rs.getString(4));
            return record;
        });

Executing Insert Statements

The library generates several types of insert statements. See the Insert Statements page for details.

Spring supports retrieval of generated keys for many types of inserts. This library has support for generated key retrieval where it is supported by Spring.

Executing General Insert Statements

General insert statements do not require a POJO object matching a table row. Following is a complete example:

    NamedParameterJdbcTemplate template = getTemplate();  // not shown

    GeneralInsertStatementProvider insertStatement = insertInto(generatedAlways)
            .set(id).toValue(100)
            .set(firstName).toValue("Bob")
            .set(lastName).toValue("Jones")
            .build()
            .render(RenderingStrategies.SPRING_NAMED_PARAMETER);

    int rows = template.update(insertStatement.getInsertStatement(), insertStatement.getParameters());

If you want to retrieve generated keys for a general insert statement the steps are similar except that you must wrap the parameters in a MapSqlParameterSource object and use a GeneratedKeyHolder. Following is a complete example of this usage:

    NamedParameterJdbcTemplate template = getTemplate();  // not shown

    GeneralInsertStatementProvider insertStatement = insertInto(generatedAlways)
            .set(id).toValue(100)
            .set(firstName).toValue("Bob")
            .set(lastName).toValue("Jones")
            .build()
            .render(RenderingStrategies.SPRING_NAMED_PARAMETER);

    MapSqlParameterSource parameterSource = new MapSqlParameterSource(insertStatement.getParameters());
    KeyHolder keyHolder = new GeneratedKeyHolder();

    int rows = template.update(insertStatement.getInsertStatement(), parameterSource, keyHolder);
    String generatedKey = (String) keyHolder.getKeys().get("FULL_NAME");