src/site/markdown/docs/spring.md · 陈振远/mybatis-dynamic-sql

# 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:

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

## Limitations

MyBatis3 is a higher level abstraction over JDBC than Spring JDBC templates. While most functions in the library will work with Spring, there are some functions that do not work:

1. Spring JDBC templates do not have anything equivalent to a type handler in MyBatis3. Therefore it is best to use data types that can be automatically understood by Spring
1. The multiple row insert statement *will not* render properly for Spring. However, batch inserts *will* render properly for Spring

## 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:

```java
    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,
            new RowMapper<GeneratedAlwaysRecord>(){
                @Override
                public GeneratedAlwaysRecord mapRow(ResultSet rs, int rowNum) throws SQLException {
                    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 General Insert Statements
General insert statements do not require a POJO object matching a table row. Following is a complete example:

```java
    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:

```java
    NamedParameterJdbcTemplate template = getTemplate();  // not shown

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

## Executing Insert Record Statements
Insert record statements are a bit different - MyBatis Dynamic SQL generates a properly formatted SQL string for Spring, but instead of a map of parameters, the parameter mappings are created for the inserted record itself.  So the parameters for the Spring template are created by a `BeanPropertySqlParameterSource`.  Generated keys in Spring are supported with a `GeneratedKeyHolder`.  The following is a complete example:

```java
    NamedParameterJdbcTemplate template = getTemplate();  // not shown

GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
    record.setId(100);
    record.setFirstName("Bob");
    record.setLastName("Jones");
        
    InsertStatementProvider<GeneratedAlwaysRecord> insertStatement = insert(record)
            .into(generatedAlways)
            .map(id).toProperty("id")
            .map(firstName).toProperty("firstName")
            .map(lastName).toProperty("lastName")
            .build()
            .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
        
    SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(insertStatement.getRecord());
    KeyHolder keyHolder = new GeneratedKeyHolder();
        
    int rows = template.update(insertStatement.getInsertStatement(), parameterSource, keyHolder);
    String generatedKey = (String) keyHolder.getKeys().get("FULL_NAME");
```

## Executing Batch Inserts
Batch insert support in Spring is a bit different than batch support in MyBatis3 and Spring does not support returning generated keys from a batch insert.  The following is a complete example of a batch insert (note the use of `SqlParameterSourceUtils` to create an array of parameter sources from an array of input records):

```java
    NamedParameterJdbcTemplate template = getTemplate();  // not shown

List<GeneratedAlwaysRecord> records = new ArrayList<>();
    GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
    record.setId(100);
    record.setFirstName("Bob");
    record.setLastName("Jones");
    records.add(record);
        
    record = new GeneratedAlwaysRecord();
    record.setId(101);
    record.setFirstName("Jim");
    record.setLastName("Smith");
    records.add(record);

SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(records.toArray());
        
    BatchInsert<GeneratedAlwaysRecord> batchInsert = insert(records)
            .into(generatedAlways)
            .map(id).toProperty("id")
            .map(firstName).toProperty("firstName")
            .map(lastName).toProperty("lastName")
            .build()
            .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
        
    int[] updateCounts = template.batchUpdate(batchInsert.getInsertStatementSQL(), batch);
```

## Executing Delete and Update Statements
Updates and deletes use the `MapSqlParameterSource` as with select statements, but use the `update` method in the template.  For example:

```java
    NamedParameterJdbcTemplate template = getTemplate();  // not shown

UpdateStatementProvider updateStatement = update(generatedAlways)
            .set(firstName).equalToStringConstant("Rob")
            .where(id, isIn(1, 5, 22))
            .build()
            .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
        
    SqlParameterSource parameterSource = new MapSqlParameterSource(updateStatement.getParameters());
        
    int rows = template.update(updateStatement.getUpdateStatement(), parameterSource);
```

陈振远/mybatis-dynamic-sql

简介

发行版

贡献者

近期动态

陈振远/mybatis-dynamic-sql .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

陈振远/mybatis-dynamic-sql