. 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语句:
<insert id="foreachSelectKeyInsert" parameterType="java.util.List"> <selectKey resultType ="java.lang.Integer" keyProperty= "iid" order= "AFTER"> SELECT LAST_INSERT_ID() </selectKey > insert into foreach_test (name, age, idx) values <foreach item="st" collection="list" index="idx" open="" separator="," close=""> (#{st.name, jdbcType=VARCHAR}, #{st.age, jdbcType=INTEGER}, #{idx}) </foreach> </insert>
dao层的接口:
int foreachSelectKeyInsert(List<Du> dud);
java业务逻辑:
@GET @Path("/foreach/selectkey/insert") public String foreachSelectKeyInsert(@Context HttpServletRequest req){ List<Du> dud = new ArrayList<Du>(); for(int i=1; i < 5; i++){ Du du1 = new Du(); 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 * from foreach_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 id="foreachUseGeneratedKeysInsert1" keyProperty="id" useGeneratedKeys="true"> insert into foreach_test (name, age, idx) values <foreach item="st" collection="list" index="idx" open="" separator="," close=""> (#{st.name, jdbcType=VARCHAR}, #{st.age, jdbcType=INTEGER}, #{idx}) </foreach> </insert>
dao接口:
int foreachUseGeneratedKeysInsert1(List<Du> dud);
java业务逻辑:
@GET @Path("/foreach/usegeneratedkeys/insert1") public String foreachUseGeneratedKeysInsert1(@Context HttpServletRequest req){ List<Du> dud = new ArrayList<Du>(); for(int i=6; i < 10; i++){ Du du1 = new Du(); 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
结果爆出错误:
这个错误,折腾了我好久,在stackoverflow网站找到了一个老外的类似问题,得到线索,说是mybatis的版本问题,3.3.1之后(我当前的研究环境是3.2.7),这个问题才得以解决。在没有验证是否正确之前,继续当前环境下的研究。前端报错了,依旧查看数据库,看是否有数据写入:
mysql> select * from foreach_test; +----+--------------------+------+------+ | id | name | age | idx | +----+--------------------+------+------+ | 1 | UseGeneratedKeys16 | 36 | 0 | | 2 | UseGeneratedKeys17 | 37 | 1 | | 3 | UseGeneratedKeys18 | 38 | 2 | | 4 | UseGeneratedKeys19 | 39 | 3 | +----+--------------------+------+------+ 4 rows in set (0.00 sec)
说明,数据是写入了库,但是前端这个错误很不友好。
虽然报错,但是从插入数据库的结果得出结论:传入给mybatis的list(单独参数),collection的值,默认是list,此时,item的内容为list的元素,index的值为遍历list的序号,从0开始递增。
3. useGeneratedKeys=”true“方案的插入,数据采用map传入
mapper的sql语句:
<insert id="foreachUseGeneratedKeysInsert2" keyProperty="id" useGeneratedKeys="true"> insert into foreach_test (name, age, idx) values <foreach item="st" collection="dudkey" index="idx" open="(" separator="),(" close=")"> #{st.name, jdbcType=VARCHAR}, #{st.age, jdbcType=INTEGER}, #{idx} </foreach> </insert>
dao接口:
int foreachUseGeneratedKeysInsert2(HashMap<String, List<Du>> dumap);
java业务逻辑:
@GET @Path("/foreach/usegeneratedkeys/insert2") public String foreachUseGeneratedKeysInsert2(@Context HttpServletRequest req){ List<Du> dud = new ArrayList<Du>(); for(int i=6; i < 10; i++){ Du du1 = new Du(); du1.setName("UseGeneratedKeys2" + i); du1.setAge(30+i); dud.add(du1); } HashMap<String, List<Du>> dumap = new HashMap<String, List<Du>>(); dumap.put("dudkey", dud); pes.foreachUseGeneratedKeysInsert2(dumap); return "UseGeneratedKeys2 Insert OK"; }
在地址栏输入:
http://10.90.9.20:8080/ecs/demo/foreach/usegeneratedkeys/insert2
查看数据库:
mysql> select * from foreach_test; +----+--------------------+------+------+ | id | name | age | idx | +----+--------------------+------+------+ | 1 | UseGeneratedKeys26 | 36 | 0 | | 2 | UseGeneratedKeys27 | 37 | 1 | | 3 | UseGeneratedKeys28 | 38 | 2 | | 4 | UseGeneratedKeys29 | 39 | 3 | +----+--------------------+------+------+ 4 rows in set (0.00 sec)
奇怪吧,3.2.7的环境(mybatis-spring版本是1.2.4)下,传入map,批量插入不报前面出现的id找不到的错误。
从这个案例,可以看出:传入mybatis的map,若给collection的值是入参map中的key的话,那么item的值将是map的value,此时的index值,就是map中value遍历的序号,从0开始递增。
到此,将mybatis的版本进行升级,升级到3.3.1,去maven仓库下载该版本:http://mvnrepository.com/artifact/org.mybatis/mybatis/3.3.1,更新到项目中,然后启动项目进行验证,哎,不幸运啊,又有新错误:
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shiroRealm': Injection of resource dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sysMenuService': Invocation of init method failed; nested exception is java.lang.AbstractMethodError: org.mybatis.spring.transaction.SpringManagedTransaction.getTimeout()Ljava/lang/Integer; at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:307) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1148) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:293) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:290) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:191) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:328) ... 65 more Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sysMenuService': Invocation of init method failed; nested exception is java.lang.AbstractMethodError: org.mybatis.spring.transaction.SpringManagedTransaction.getTimeout()Ljava/lang/Integer; at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:133) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:396) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1507) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:521) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:293) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:290) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195) at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:449) at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:423) at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:551) at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:169) at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88) at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:304) ... 73 more Caused by: java.lang.AbstractMethodError: org.mybatis.spring.transaction.SpringManagedTransaction.getTimeout()Ljava/lang/Integer; at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(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.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198) at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) at com.sun.proxy.$Proxy17.getListWithUrlNotNull(Unknown Source) at com.tg.ecs.system.service.impl.SysMenuService.getListWithUrlNotNull(SysMenuService.java:101) at com.tg.ecs.system.service.impl.SysMenuService.initFilterChain(SysMenuService.java:106) 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.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:344) at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:295) at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:130) ... 87 more
看这个错误,应该是mybatis-spring的版本与mybatis不匹配的原因造成的。于是,我去maven上查看mybatis-spring支持的mybatis的版本,我选择mybatis-spring 1.2.5的版本:再次启动软件,上述mybatis-spring的错误解除。
到目前为止,我的研究环境,变成了mybatis 3.3.1,mybatis-spring 1.2.5. 继续后续研究!
在这个新的环境下,继续上述案例2的测试,在地址栏输入 http://10.90.9.20:8080/ecs/demo/foreach/usegeneratedkeys/insert1, 这次不再报错。所以,上面案例2的错误,还真是stackoverflow上人家描述的问题导致的。
4. useGeneratedKeys=”true“方案的插入,数据采用map传入,注意与案例3的不同,在于传入的数据,此处是纯map,collection就是一个注解指定名称了的map,而案例3中,foreach collection其实是map的key
mapper层的sql:
<insert id="foreachUseGeneratedKeysInsert3" useGeneratedKeys="true"> <!-- <selectKey resultType ="java.lang.Integer" keyProperty= "iid" order= "AFTER"> SELECT LAST_INSERT_ID() </selectKey > --> insert into foreach_test (name, age, idx) values <foreach item="dt" collection="map" index="key" open="(" separator="),(" close=")"> #{dt.name, jdbcType=VARCHAR}, #{dt.age, jdbcType=INTEGER}, #{key} </foreach> </insert>
dao层的接口:
int foreachUseGeneratedKeysInsert3(@Param("map") HashMap<Integer, Du> dumap);
java层的业务逻辑:
@GET @Path("/foreach/usegeneratedkeys/insert3") public String foreachUseGeneratedKeysInsert3(@Context HttpServletRequest req){ List<Du> dud = new ArrayList<Du>(); HashMap<Integer, Du> dumap = new HashMap<Integer, Du>(); for(int i=30; i < 33; i++){ Du du = new Du(); du.setName("UseGeneratedKeys3" + i); du.setAge(30+i); dud.add(du); dumap.put(i, du); } int cnt = pes.foreachUseGeneratedKeysInsert3(dumap); System.out.println(cnt); return "UseGeneratedKeys3 Insert OK"; }
在地址栏输入:
mysql> select * from foreach_test; +----+---------------------+------+------+ | id | name | age | idx | +----+---------------------+------+------+ | 1 | UseGeneratedKeys332 | 62 | 32 | | 2 | UseGeneratedKeys331 | 61 | 31 | | 3 | UseGeneratedKeys330 | 60 | 30 | +----+---------------------+------+------+ 3 rows in set (0.00 sec)
这个案例:可以看出,传入map给mybatis时,foreach的collection,采用dao层接口注解指定的变量名,然后,index指的是map的key值,而item的内容,就是map中index所对应的key值指向的value,即du对象。
5. useGeneratedKeys=”true“方案的插入,数据采用array传入
mapper的sql:
<insert id="foreachUseGeneratedKeysInsert4" useGeneratedKeys="true"> insert into foreach_test (name, age, idx) values <foreach item="dt" collection="array" index="seq" open="(" separator="),(" close=")"> #{dt.name, jdbcType=VARCHAR}, #{dt.age, jdbcType=INTEGER}, #{seq} </foreach> </insert>
dao接口:
int foreachUseGeneratedKeysInsert4(Du[] duarray);
java应用层的逻辑:
@GET @Path("/foreach/usegeneratedkeys/insert4") public String foreachUseGeneratedKeysInsert4(@Context HttpServletRequest req){ Du [] dud = new Du[4]; for(int i=0; i < dud.length ; i++){ Du du = new Du(); du.setName("UseGeneratedKeys4" + i); du.setAge(30+i); dud[i] = du; } int cnt = pes.foreachUseGeneratedKeysInsert4(dud); System.out.println(cnt); return "UseGeneratedKeys4 Insert OK"; }
在地址栏输入:
http://10.90.9.20:8080/ecs/demo/foreach/usegeneratedkeys/insert4
查看数据库得到下面的结果:
mysql> select * from foreach_test; +----+--------------------+------+------+ | id | name | age | idx | +----+--------------------+------+------+ | 1 | UseGeneratedKeys40 | 30 | 0 | | 2 | UseGeneratedKeys41 | 31 | 1 | | 3 | UseGeneratedKeys42 | 32 | 2 | | 4 | UseGeneratedKeys43 | 33 | 3 | +----+--------------------+------+------+ 4 rows in set (0.00 sec)
上述运行一切正常,从结果来看:当传入mybatis的数据为Array时,collection的值默认就是array,除非在dao层通过注解@Param("xxx")指定其他名称。item的值为数组的元素,index的值为遍历数组的序号,从0开始,依次递增。
6.foreach用于批量的查询
mapper的sql:
<select id="foreachSelect" resultType="com.tg.ecs.ucc.model.Du"> select name, age from foreach_test where idx in <foreach item="st" collection="map" index="seq" open="(" separator="," close=")"> #{st, jdbcType=INTEGER} </foreach> </select>
dao接口:
List<Du> foreachSelect(@Param("map") HashMap<String, Integer> idxMap);
java的业务逻辑实现:
@GET @Path("/foreach/select") public String foreachSelect(@Context HttpServletRequest req){ HashMap<String, Integer> idxMap = new HashMap<String, Integer>(); for(int i=0; i<5; i++){ idxMap.put("key" + i, i); } List<Du> res = pes.foreachSelect(idxMap); for(Du du: res){ System.out.println(du.getName() + " -- " + du.getAge()); } return "Select OK"; }
运行web应用前,查看数据库内容:
mysql> select * from foreach_test; +----+---------------------+------+------+ | id | name | age | idx | +----+---------------------+------+------+ | 1 | UseGeneratedKeys40 | 30 | 0 | | 2 | UseGeneratedKeys41 | 31 | 1 | | 3 | UseGeneratedKeys42 | 32 | 2 | | 4 | UseGeneratedKeys43 | 33 | 3 | | 5 | UseGeneratedKeys332 | 62 | 32 | | 6 | UseGeneratedKeys331 | 61 | 31 | | 7 | UseGeneratedKeys330 | 60 | 30 | | 8 | UseGeneratedKeys16 | 36 | 0 | | 9 | UseGeneratedKeys17 | 37 | 1 | | 10 | UseGeneratedKeys18 | 38 | 2 | | 11 | UseGeneratedKeys19 | 39 | 3 | +----+---------------------+------+------+ 11 rows in set (0.00 sec)
运行web应用,在地址栏输入:
http://10.90.9.20:8080/ecs/demo/foreach/select
eclipse控制台打印的结果:
UseGeneratedKeys40 -- 30 UseGeneratedKeys41 -- 31 UseGeneratedKeys42 -- 32 UseGeneratedKeys43 -- 33 UseGeneratedKeys16 -- 36 UseGeneratedKeys17 -- 37 UseGeneratedKeys18 -- 38 UseGeneratedKeys19 -- 39
结合上面的java代码和sql,输出结果是正确的。
最后,总结一下:
1. mybatis的insert操作,这里主要指批量插入操作,要注意键值的生成问题,对于mysql,有selectKey和useGeneratedKeys=“true”两种方案,例如本案例中,主键是auto_increment的,所以,用useGeneratedKeys=“true”相对简单(前提是数据库支持自动生成键值)。
2. foreach的使用,主要是搞清楚item,collection,index,open,separator,close几个属性的使用。其中,最最重要的是collection和index的含义。本博文中,针对collection支持的三种类型:list,array以及map都做了介绍,而map相对复杂点,原则上map支持任何参数的传入。要结合map的构造方式,以及dao层给map参数指定的参数名。对于map,collection的值若是map的key,则index是个序号,遍历map的次序编号;若collection的值给的是map,那么,index的值就是map的key,item的值就是key对于的value。
3. foreach中的open,separator以及close,配合使用,用于构造SQL语句。例如in指令的语句中,in后面是一个序列,所以习惯用open=“(”,separator=“,”,close=“)”。当然也不一定,这个的值,还和foreach标签体内的SQL的写法有关系。例如本博文案例2和案例3的这open,separator和close的值,就可以看出门道。