本篇文章为你整理了MyBatis Dynamic SQL – Insert Statements（）的详细内容，包含有 MyBatis Dynamic SQL – Insert Statements，希望能帮助你了解 MyBatis Dynamic SQL – Insert Statements。
An insert for multiple rows with a single statement
An insert for multiple rows with a JDBC batch
A general insert statement
An insert with a select statement
Single Row Insert
A single record insert is a statement that inserts a single record into a table. This statement is configured differently than other statements in the library so that MyBatis support for generated keys will work properly. To use the statement, you must first create an object that will map to the database row, then map object attributes to fields in the database. For example:
...
SimpleTableRecord row = new SimpleTableRecord();
row.setId(100);
row.setFirstName( Joe
row.setLastName( Jones
row.setBirthDate(new Date());
row.setEmployed(true);
row.setOccupation( Developer
InsertStatementProvider SimpleTableRecord insertStatement = insert(row)
.into(simpleTable)
.map(id).toProperty( id )
.map(firstName).toProperty( firstName )
.map(lastName).toProperty( lastName )
.map(birthDate).toProperty( birthDate )
.map(employed).toProperty( employed )
.map(occupation).toProperty( occupation )
.build()
.render(RenderingStrategies.MYBATIS3);
int rows = mapper.insert(insertStatement);
Notice the map method. It is used to map a database column to an attribute of the record to insert. There are several different mappings available:
map(column).toNull() will insert a null into a column
map(column).toConstant(constant_value) will insert a constant into a column. The constant_value will be written into the generated insert statement exactly as entered
map(column).toStringConstant(constant_value) will insert a constant into a column. The constant_value will be written into the generated insert statement surrounded by single quote marks (as an SQL String)
map(column).toProperty(property) will insert a value from the record into a column. The value of the property will be bound to the SQL statement as a prepared statement parameter
map(column).toPropertyWhenPresent(property, Supplier ? valueSupplier) will insert a value from the record into a column if the value is non-null. The value of the property will be bound to the SQL statement as a prepared statement parameter. This is used to generate a selective insert as defined in MyBatis Generator.
Annotated Mapper for Single Row Insert Statements
The InsertStatementProvider object can be used as a parameter to a MyBatis mapper method directly. If you
are using an annotated mapper, the insert method should look like this (with @Options added for generated values if necessary):
import org.apache.ibatis.annotations.InsertProvider;
import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
@InsertProvider(type=SqlProviderAdapter.class, method= insert )
int insert(InsertStatementProvider SimpleTableRecord insertStatement);
XML Mapper for Single Row Insert Statements
We do not recommend using an XML mapper for insert statements, but if you want to do so the InsertStatementProvider object can be used as a parameter to a MyBatis mapper method directly.
If you are using an XML mapper, the insert method should look like this in the Java interface:
import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
int insert(InsertStatementProvider SimpleTableRecord insertStatement);
The XML element should look like this (with attributes added for generated values if necessary):
insert id= insert
${insertStatement}
/insert
Generated Values
MyBatis supports returning generated values from a single row insert, or a batch insert. In either case, it is simply a matter of configuring the insert mapper method appropriately. For example, to retrieve the value of a calculated column configure your mapper method like this:
...
@InsertProvider(type=SqlProviderAdapter.class, method= insert )
@Options(useGeneratedKeys=true, keyProperty= row.fullName )
int insert(InsertStatementProvider GeneratedAlwaysRecord insertStatement);
The important thing is that the keyProperty is set correctly. It should always be in the form row. attribute where attribute is the attribute of the record class that should be updated with the generated value.
Multiple Row Insert Support
A multiple row insert is a single insert statement that inserts multiple rows into a table. This can be a convenient way to insert a few rows into a table, but it has some limitations:
Since it is a single SQL statement, you could generate quite a lot of prepared statement parameters. For example, suppose you wanted to insert 1000 records into a table, and each record had 5 fields. With a multiple row insert you would generate a SQL statement with 5000 parameters. There are limits to the number of parameters allowed in a JDBC prepared statement - and this kind of insert could easily exceed those limits. If you want to insert many records, you should probably use a JDBC batch insert instead (see below)
The performance of a giant insert statement may be less than you expect. If you have many records to insert, it will almost always be more efficient to use a JDBC batch insert (see below). With a batch insert, the JDBC driver can do some optimization that is not possible with a single large statement
Retrieving generated values with multiple row inserts can be a challenge. MyBatis currently has some limitations related to retrieving generated keys in multiple row inserts that require special considerations (see below)
Nevertheless, there are use cases for a multiple row insert - especially when you just want to insert a few records in a table and dont need to retrieve generated keys. In those situations, a multiple row insert will be an easy solution.
A multiple row insert statement looks like this:
try (SqlSession session = sqlSessionFactory.openSession()) {
GeneratedAlwaysAnnotatedMapper mapper = session.getMapper(GeneratedAlwaysAnnotatedMapper.class);
List GeneratedAlwaysRecord records = getRecordsToInsert(); // not shown
MultiRowInsertStatementProvider GeneratedAlwaysRecord multiRowInsert = insertMultiple(records)
.into(generatedAlways)
.map(id).toProperty( id )
.map(firstName).toProperty( firstName )
.map(lastName).toProperty( lastName )
.build()
.render(RenderingStrategies.MYBATIS3);
int rows = mapper.insertMultiple(multiRowInsert);
Annotated Mapper for Multiple Row Insert Statements
The MultiRowInsertStatementProvider object can be used as a parameter to a MyBatis mapper method directly. If you
are using an annotated mapper, the insert method should look like this:
import org.apache.ibatis.annotations.InsertProvider;
import org.mybatis.dynamic.sql.insert.render.MultiRowInsertStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
@InsertProvider(type=SqlProviderAdapter.class, method= insertMultiple )
int insertMultiple(MultiRowInsertStatementProvider SimpleTableRecord insertStatement);
XML Mapper for Multiple Row Insert Statements
We do not recommend using an XML mapper for insert statements, but if you want to do so the MultiRowInsertStatementProvider object can be used as a parameter to a MyBatis mapper method directly.
If you are using an XML mapper, the insert method should look like this in the Java interface:
import org.mybatis.dynamic.sql.insert.render.MultiInsertStatementProvider;
int insertMultiple(MultiRowInsertStatementProvider SimpleTableRecord insertStatement);
The XML element should look like this:
insert id= insertMultiple
${insertStatement}
/insert
Generated Values
MyBatis supports returning generated values from a multiple row insert statement with some limitations. The main
limitation is that MyBatis does not support nested lists in parameter objects. Unfortunately, the
MultiRowInsertStatementProvider relies on a nested List. It is likely this limitation in MyBatis will be removed at
some point in the future, so stay tuned.
Nevertheless, you can configure a mapper that will work with the MultiRowInsertStatementProvider as created by this
library. The main idea is to decompose the statement from the parameter map and send them as separate parameters to the
MyBatis mapper. For example:
...
@InsertProvider(type=SqlProviderAdapter.class, method= insertMultipleWithGeneratedKeys )
@Options(useGeneratedKeys=true, keyProperty= records.fullName )
int insertMultipleWithGeneratedKeys(String insertStatement, @Param( records ) List GeneratedAlwaysRecord records);
default int insertMultipleWithGeneratedKeys(MultiRowInsertStatementProvider GeneratedAlwaysRecord multiInsert) {
return insertMultipleWithGeneratedKeys(multiInsert.getInsertStatement(), multiInsert.getRecords());
The first method above shows the actual MyBatis mapper method. Note the use of the @Options annotation to specify
that we expect generated values. Further, note that the keyProperty is set to records.fullName - in this case,
fullName is a property of the objects in the records List. The library supplied adapter method will simply
return the insertStatement as supplied in the method call. The adapter method requires that there be one, and only
one, String parameter in the method call, and it assumes that this one String parameter is the SQL insert statement.
The parameter can have any name and can be specified in any position in the methods parameter list.
The @Param annotation is not required for the insert statement. However, it may be specified if you so desire.
The second method above decomposes the MultiRowInsertStatementProvider and calls the first method.
Batch Insert Support
A batch insert is a collection of statements that can be used to execute a JDBC batch. A batch is the preferred method of doing bulk inserts with JDBC. The basic idea is that you configure the connection for a batch insert, then execute the same statement multiple times, with different values for each inserted record. MyBatis has a nice abstraction of JDBC batches that works well with statements generated from this library. A batch insert looks like this:
...
try(SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
List SimpleTableRecord records = getRecordsToInsert(); // not shown
BatchInsert SimpleTableRecord batchInsert = insert(records)
.into(simpleTable)
.map(id).toProperty( id )
.map(firstName).toProperty( firstName )
.map(lastName).toProperty( lastName )
.map(birthDate).toProperty( birthDate )
.map(employed).toProperty( employed )
.map(occupation).toProperty( occupation )
.build()
.render(RenderingStrategies.MYBATIS3);
batchInsert.insertStatements().forEach(mapper::insert);
session.commit();
It is important to open a MyBatis session by setting the executor type to BATCH. The records are inserted on the commit. You can call commit multiple times if you want to do intermediate commits.
Notice that the same mapper method that is used to insert a single record is now executed multiple times. The map methods are the same with the exception that the toPropertyWhenPresent mapping is not supported for batch inserts.
General Insert Statement
A general insert is used to build arbitrary insert statements. The general insert does not require a separate record object to hold values for the statement - any value can be passed into the statement. This version of the insert is not convenient for retrieving generated keys with MyBatis - for that use case we recommend the single record insert . However the general insert is perfectly acceptable for Spring JDBC template or MyBatis inserts that do not return generated keys. For example
GeneralInsertStatementProvider insertStatement = insertInto(animalData)
.set(id).toValue(101)
.set(animalName).toStringConstant( Fred )
.set(brainWeight).toConstant( 2.2 )
.set(bodyWeight).toValue(4.5)
.build()
.render(RenderingStrategies.MYBATIS3);
Notice the set method. It is used to set the value for a database column. There are several different possibilities:
set(column).toNull() will insert a null into a column
set(column).toConstant(constant_value) will insert a constant into a column. The constant_value will be written into the generated insert statement exactly as entered
set(column).toStringConstant(constant_value) will insert a constant into a column. The constant_value will be written into the generated insert statement surrounded by single quote marks (as an SQL String)
set(column).toValue(value) will insert a value into a column. The value of the property will be bound to the SQL statement as a prepared statement parameter
set(column).toValueWhenPresent(property, Supplier ? valueSupplier) will insert a value into a column if the value is non-null. The value of the property will be bound to the SQL statement as a prepared statement parameter.
Annotated Mapper for General Insert Statements
The GeneralInsertStatementProvider object can be used as a parameter to a MyBatis mapper method directly. If you
are using an annotated mapper, the insert method should look like this:
import org.apache.ibatis.annotations.InsertProvider;
import org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
@InsertProvider(type=SqlProviderAdapter.class, method= generalInsert )
int generalInsert(GeneralInsertStatementProvider insertStatement);
XML Mapper for General Insert Statements
We do not recommend using an XML mapper for insert statements, but if you want to do so the GeneralInsertStatementProvider object can be used as a parameter to a MyBatis mapper method directly.
If you are using an XML mapper, the insert method should look like this in the Java interface:
import org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider;
int generalInsert(GeneralInsertStatementProvider insertStatement);
The XML element should look like this:
insert id= generalInsert
${insertStatement}
/insert
Insert with Select
An insert select is an SQL insert statement the inserts the results of a select statement. For example:
InsertSelectStatementProvider insertSelectStatement = insertInto(animalDataCopy)
.withColumnList(id, animalName, bodyWeight, brainWeight)
.withSelectStatement(
select(id, animalName, bodyWeight, brainWeight)
.from(animalData)
.where(id, isLessThan(22)))
.build()
.render(RenderingStrategies.MYBATIS3);
int rows = mapper.insertSelect(insertSelectStatement);
The column list is optional and can be removed if the selected columns match the layout of the table.
Annotated Mapper for Insert Select Statements
The InsertSelectStatementProvider object can be used as a parameter to a MyBatis mapper method directly. If you
are using an annotated mapper, the insert method should look like this:
import org.apache.ibatis.annotations.InsertProvider;
import org.mybatis.dynamic.sql.insert.render.InsertSelectStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
@InsertProvider(type=SqlProviderAdapter.class, method= insertSelect )
int insertSelect(InsertSelectStatementProvider insertSelectStatement);
Note that MyBatis does not support overloaded mapper method names, so the name of the method should be different than the single record insert in a mapper.
XML Mapper for Insert Select Statements
We do not recommend using an XML mapper for insert statements, but if you want to do so the InsertSelectStatementProvider object can be used as a parameter to a MyBatis mapper method directly.
If you are using an XML mapper, the insert method should look like this in the Java interface:
import org.mybatis.dynamic.sql.insert.render.InsertSelectStatementProvider;
int insertSelect(InsertSelectStatementProvider insertSelectStatement);
The XML element should look like this:
insert id= insertSelect
${insertStatement}
/insert
以上就是MyBatis Dynamic SQL – Insert Statements（）的详细内容，想要了解更多 MyBatis Dynamic SQL – Insert Statements的内容，请持续关注盛行IT软件开发工作室。
郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。