Support insert multiple rows and write-back id.
Multirow inserts
A SQL feature (since SQL-92) is the use of row value constructors to insert multiple rows at a time in a single SQL statement:
INSERT INTO tablename (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
('value-2a', ['value-2b', ...]),
...
This feature is supported by DB2, SQL Server (since version 10.0 - i.e. 2008), PostgreSQL (since version 8.2), MySQL, sqlite (since version 3.7.11) and H2.
More about insert see here:
http://en.wikipedia.org/wiki/Insert_(SQL)
More details of this pull request see here: https://github.com/mybatis/mybatis-3/pull/324
Comment From: abel533
When it is useful?
Here is a sample.
A simple table,named country:
CREATE TABLE `country` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`countryname` varchar(255) DEFAULT NULL,
`countrycode` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=184 DEFAULT CHARSET=utf8;
Mapper.xml:
<insert id="insertList" useGeneratedKeys="true" keyProperty="id">
INSERT INTO country (countryname,countrycode )
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.countryname},#{item.countrycode})
</foreach>
</insert>
Note:keyProperty="id" and collection="list"
A CountryList:
List<Country> countries = new ArrayList<Country>();
Country country = new Country(null,"cn1","cc1");
countries.add(country);
Country country2 = new Country(null,"cn2","cc2");
countries.add(country2);
Country country3 = new Country(null,"cn3","cc3");
countries.add(country3);
Multirow inserts:
Use NameSpace
int result = sqlSession.insert("insertList", countries);
Assert.assertEquals(3, result);
for (Country c : countries) {
Assert.assertNotNull(c.getId());
}
Use Mapper Interface
Mapper.java
public interface Mapper {
int insertList(List<Country> countryList);
}
Use Interface:
Mapper mapper = sqlSession.getMapper(Mapper.class);
int result = mapper.insertList(countries);
Assert.assertEquals(3, result);
for (Country c : countries) {
Assert.assertNotNull(c.getId());
}
After insert,all contry's id will be write-back.
Comment From: PavelTurk
@abel533 Can you provide example with using mapper interface, as using sqlSession.insert is old practice, I mean mapper.insertList.
Comment From: abel533
@PashaTurok I have updated the content and add the interface use.
Comment From: PavelTurk
@abel533 So what now? Have the developers accepted your commit?
Comment From: jeffgbutler
I took a quick look at this. Some comments:
There are no tests to show that it works. Nevertheless, I tried it and see that it works in some cases. Please add tests.
It is very easy to break - use a collection like a Set instead of a List and it will break. Put the list inside a Map with other parameters and it will break.
My suggestion would be to limit the situations we try to support to the cases where a collection was passed as the only parameter. You could check for DefaultSqlSession.StrictMap rather than Map. This would be an indication that the user passed some kind of collection as the only parameter.
General Collection support is more difficult because it might be hard to guarantee that the keys get matched to the correct objects - but it would probably work in most cases. Or we could just not support general collections (like this PR does). The problem being that MyBatis supports using a general collection, so that could be confusing to the users.
If we want to support this, it would also be good to add some documentation about the limited set of circumstances where it will work.
Comment From: abel533
@jeffgbutler
Test
Most memory databases do not support multiple inserts.Mybatis use derby and hsqldb,so I can't add multiple insert test.You can use the above example as test.
About Collection
Mybatis support List,not Collection.You can view the source code.
Source code:Jdbc3KeyGenerator
At this line:
public void processBatch(MappedStatement ms, Statement stmt, List<Object> parameters)
I found Mybatis began supporting Collection(mybatis-3.3.0-SNAPSHOT):
https://github.com/mybatis/mybatis-3/commit/636ca74e05364f9c40d4fb0ae569610399b22723
I'll add the support for Collection(This feature has been added.).
Comment From: velykov
try to use useGeneratedKeys, but get a error Caused by: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [collection, list]
How can I get id of each of list item?
Comment From: abel533
I will add a test.
Comment From: abel533
I pull a new request #547 which contains a unit test
Comment From: kazuki43zoo
fixed via #547
Comment From: Dreampie
@abel533 how can use in annotation?
Comment From: abel533
@Dreampie use @Options(useGeneratedKeys = true, keyProperty="id")
Comment From: Dreampie
@abel533
public class Allocate implements Serializable {
private Long id;
...
}
@Insert("xxx")
@Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
int saveBatch(@Param("allocates") List<Allocate> allocates);
Caused by: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [allocates, param1]
at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:71)
at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:45)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:50)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy148.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
... 58 more
Caused by: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [allocates, param1]
@Insert("xxx")
@Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
int save(@Param("allocate") Allocate allocate);
allocate.getId() is null
Comment From: lizihua
@Dreampie Maybe delete @Param("allocates") I just encountered this problem , same exception message , I got through after delete @Param
Comment From: Dreampie
@lizihua why？ bug？
Comment From: abel533
@Dreampie Need to use the default value list or collection.
Comment From: Dreampie
@abel533 If param name==list @Param("list"),it's ok,so strange
Comment From: abel533
@Dreampie The default value comes from the following code:
@Override
public void select(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler) {
try {
MappedStatement ms = configuration.getMappedStatement(statement);
executor.query(ms, wrapCollection(parameter), rowBounds, handler);
} catch (Exception e) {
throw ExceptionFactory.wrapException("Error querying database. Cause: " + e, e);
} finally {
ErrorContext.instance().reset();
}
}
wrapCollection:
private Object wrapCollection(final Object object) {
if (object instanceof Collection) {
StrictMap<Object> map = new StrictMap<Object>();
map.put("collection", object);
if (object instanceof List) {
map.put("list", object);
}
return map;
} else if (object != null && object.getClass().isArray()) {
StrictMap<Object> map = new StrictMap<Object>();
map.put("array", object);
return map;
}
return object;
}
Jdbc3KeyGenerator:
private Collection<Object> getParameters(Object parameter) {
Collection<Object> parameters = null;
if (parameter instanceof Collection) {
parameters = (Collection) parameter;
} else if (parameter instanceof Map) {
Map parameterMap = (Map) parameter;
if (parameterMap.containsKey("collection")) {
parameters = (Collection) parameterMap.get("collection");
} else if (parameterMap.containsKey("list")) {
parameters = (List) parameterMap.get("list");
} else if (parameterMap.containsKey("array")) {
parameters = Arrays.asList((Object[]) parameterMap.get("array"));
}
}
if (parameters == null) {
parameters = new ArrayList<Object>();
parameters.add(parameter);
}
return parameters;
}
Comment From: guanrongYang
I've tried for a whole day, but it does not work.
Caused by: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'imageId' not found. Available parameters are [list]
at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:63)
at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:42)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355)
... 71 more
Settings of MyBatis
<settings>
<!-- set SIMPLE executor for a batch of insert which was expected to return self-growing id -->
<setting name="defaultExecutorType" value="SIMPLE" />
<!-- <setting name="defaultExecutorType" value="BATCH" /> -->
<setting name="cacheEnabled" value="true" />
<setting name="lazyLoadingEnabled" value="true" />
<setting name="aggressiveLazyLoading" value="false" />
<setting name="multipleResultSetsEnabled" value="true" />
<setting name="useColumnLabel" value="true" />
<setting name="useGeneratedKeys" value="true" />
<setting name="autoMappingBehavior" value="FULL" />
<setting name="defaultStatementTimeout" value="25000" />
<setting name="logImpl" value="LOG4J" />
</settings>
Mapper interface
public interface imageMapper {
public void insertList(List<Image> images) throws Exception;
}
Mapper.xml
<insert id="insertList" useGeneratedKeys="true" keyProperty="imageId" parameterType="java.util.List">
insert into Image (name)
values
<foreach collection="list" item="item" separator=",">
(#{item.name})
</foreach>
</insert>
Comment From: abel533
@guanrongYang what about Image class?
Comment From: guanrongYang
@abel533 remove not important fields, it can be simplified like this:
public class Image{
private long imageId;
private String name;
// getter and setter
...
}
It's very stange that I has already set defaultExecutorType SIMPLE and used List type. But the errors show me that the program still invoke the method Jdbc3KeyGenerator.processBatch which will package the List into Map and loss id of the object after insert.
Comment From: abel533
@guanrongYang use Long instead of long!
Comment From: exinglang
i have a question, i want to use 2 param in xxxDAO like this
xxxDao:
void insertSchedulePankou(@Param("scheduleId") int scheduleId,@Param("pankoulist")ArrayList
xxxMapper.xml:
insert ignore into t_schedule_pankou (schedule_id,pankou_name)
values
<foreach collection ="pankoulist" item="pankou" index= "index" separator =",">
(
#{scheduleId}, #{pankou.name}
)
</foreach >
</insert>
i must delete @param to use your code.
if i deleted @param .will not found "scheduleId" and "pankoulist"....
To summarise , i want use 2 param in xxxDAO.
楼主应该是中国人啊...不知道我说的看得懂不..英文不好..
Comment From: abel533
@Param("pankoulist") change to @Param("list").
collection ="pankoulist" change to collection ="list".
原因在这里：
private Collection<Object> getParameters(Object parameter) {
Collection<Object> parameters = null;
if (parameter instanceof Collection) {
parameters = (Collection) parameter;
} else if (parameter instanceof Map) {
Map parameterMap = (Map) parameter;
if (parameterMap.containsKey("collection")) {
parameters = (Collection) parameterMap.get("collection");
} else if (parameterMap.containsKey("list")) {
parameters = (List) parameterMap.get("list");
} else if (parameterMap.containsKey("array")) {
parameters = Arrays.asList((Object[])parameterMap.get("array"));
}
}
if (parameters == null) {
parameters = new ArrayList<Object>();
parameters.add(parameter);
}
return parameters;
}
Comment From: alisanguo
我批量插的时候只能得到list第一个元素的id，其他的id还是为空。这是啥原因啊
Comment From: alisanguo
I only get the id of the first item in list,but the id of other items is null。why?
Comment From: abel533
@alisanguo Only MySQL's JDBC driver support it.
Comment From: alisanguo
@abel533 The driver I use is MySQL's JDBC driver .
Comment From: nuccch
I have the same problem！
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [list]
Mapper.xml：
<insert id="addDigitalTwinAttr"
parameterType="java.util.List"
useGeneratedKeys="true"
keyProperty="id">
INSERT INTO digitaltwin(daddyid,sonname,digitaltwinname,describemessage,unit,metric,tagkv,createtimestamp,updatetimestamp) VALUES
<foreach collection="list" index="index" item="dtAttr" separator=",">
(#{dtAttr.daddyid},#{dtAttr.sonname},#{dtAttr.digitaltwinname},#{dtAttr.describemessage},#{dtAttr.unit},#{dtAttr.metric},#{dtAttr.tagkv},now(),now())
</foreach>
</insert>
Mapper Interface：
public Integer addDigitalTwinAttr(List<DigitalTwinAttrParam> list);
DigitalTwinAttrParam.class:
public class DigitalTwinAttrParam{
protected Long id =0L;
protected Long daddyid = 0L;
...
}
Comment From: harawata
Hi all,
There is a backward incompatible change in 3.5.0. Please test your solutions with the latest 3.5.0-SNAPSHOT before its final release and let us know if there is any problem. Please see #1249 for the details about the change.
Thank you!
Comment From: dawwin
I have a question regarding this change. Lets assume that my code for batch inserts looks like this:
<insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="entry.id">
INSERT INTO table (some_column) values
<foreach collection="list" item="entry" separator=",">
(#{entry.someColumn})
</foreach>
</insert>
If I understand correctly this won't work with 3.5.0 and I need to change it to
<insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="entries.id">
INSERT INTO table (some_column) values
<foreach collection="entries" item="entry" separator=",">
(#{entry.someColumn})
</foreach>
</insert>
I assume this will work the same way when I use annotations.
I also wanted to ask if there is a release date for 3.5.0, because I would love to see https://github.com/mybatis/mybatis-3/pull/1321 merged as well before JDK 11 is released. It will be long term support release and a lot of people will migrate to new JDK. I will do some more tests as I promised, but I haven't had time for this yet.
Comment From: harawata
Thank you, @dawwin for the comment!
To avoid confusion, let's differentiate between batch insert and multi-row insert.
The snippets in your comment is too incomplete to understand the context. Also, I may need to see the Java method (the number of the method parameter is important).
As there is a working test case in the repo, why don't you try modifying the parameter name, etc. and see if it works as you expect?
Regarding 3.5.0 release, there still are some open issues, so it may take some time.
Comment From: Jackslip
您好，我使用mybatis3.3.1+mybatis-spring1.3.1，batchInsert(@Param("list") List