鉴于上一篇博文一次修改mysql字段类型引发的技术探究提到的,要对foreach里面的collection相关的内容做一些介绍,今天就围绕foreach,做一些数据插入和查询相关的研究。
首先介绍一下我的环境:
1. linux redhat7
2. mysql 5.6
3. java7
4. mybatis 3.2.7 (后来遇到问题,更新到3.3.1)
第一步,在数据库中创建测试用的表 foreach_test。如下:
mysql>desc foreach_test;+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| age | int(8) | YES | | NULL | |
| idx | int(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
第二步,进行mybatis相关的mapper以及dao进行配置。今天研究和介绍的关于foreach的相关内容,将从insert以及select两个大类进行案例介绍,其中,insert是重点,因为批量数据插入,相对比较复杂点,涉及到键值的更新过程。对于mysql数据库而言,对于第一步中创建的数据表foreach_test,有一个主键id,是数字自增型的。这里,结合mybatis的官方文档介绍,将会有两种更新主键的方法:selectKey,以及useGeneratedKeys=“true”下面将结合这两种方法,以及foreach的collection能够支持的三种集合类型list,array以及map进行案例分析。
1. selectKey方案的插入,数据采用list传入
mapper的sql语句:
SELECT LAST_INSERT_ID()insert into foreach_test (name, age, idx) values(#{st.name, jdbcType=VARCHAR}, #{st.age, jdbcType=INTEGER}, #{idx})
dao层的接口:
int foreachSelectKeyInsert(List dud);
java业务逻辑:
@GET
@Path("/foreach/selectkey/insert")publicString foreachSelectKeyInsert(@Context HttpServletRequest req){
List dud = new ArrayList();for(int i=1; i < 5; i++){
Du du1= newDu();
du1.setName("SelectKey" +i);
du1.setAge(30+i);
dud.add(du1);
}
pes.foreachSelectKeyInsert(dud);return "SelectKey Insert OK";
}
在地址栏输入:
http://10.90.9.20:8080/ecs/demo/foreach/selectkey/insert
数据库中得到:
mysql> select * fromforeach_test;+----+------------+------+------+
| id | name | age | idx |
+----+------------+------+------+
| 1 | SelectKey1 | 31 | 0 |
| 2 | SelectKey2 | 32 | 1 |
| 3 | SelectKey3 | 33 | 2 |
| 4 | SelectKey4 | 34 | 3 |
+----+------------+------+------+
4 rows in set (0.00 sec)
2. useGeneratedKeys=”true“方案的插入,数据采用list传入
mapper的sql语句:
insert into foreach_test (name, age, idx) values(#{st.name, jdbcType=VARCHAR}, #{st.age, jdbcType=INTEGER}, #{idx})
dao接口:
int foreachUseGeneratedKeysInsert1(List dud);
java业务逻辑:
@GET
@Path("/foreach/usegeneratedkeys/insert1")publicString foreachUseGeneratedKeysInsert1(@Context HttpServletRequest req){
List dud = new ArrayList();for(int i=6; i < 10; i++){
Du du1= newDu();
du1.setName("UseGeneratedKeys1" +i);
du1.setAge(30+i);
dud.add(du1);
}
pes.foreachUseGeneratedKeysInsert1(dud);return "UseGeneratedKeys1 Insert OK";
}
在地址栏输入:
http://10.90.9.20:8080/ecs/demo/foreach/usegeneratedkeys/insert1
结果爆出错误:
org.apache.ibatis.binding.BindingException: Parameter 'id'not found. Available parameters are [list]
at org.apache.ibatis.session.defaults.DefaultSqlSession$StrictMap.get(DefaultSqlSession.java:261)
at org.apache.ibatis.reflection.wrapper.MapWrapper.getSetterType(MapWrapper.java:79)
at org.apache.ibatis.reflection.MetaObject.getSetterType(MetaObject.java:91)
at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.getTypeHandlers(Jdbc3KeyGenerator.java:82)
at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:61)
at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:45)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:48)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
at com.sun.proxy.$Proxy11.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$Proxy32.foreachUseGeneratedKeysInsert1(Unknown Source)
at com.tg.ecs.ucc.service.impl.PurchaseElementService.foreachUseGeneratedKeysInsert1(PurchaseElementService.java:116)
at com.tg.ecs.ucc.service.impl.PurchaseElementS