mybatis 批量插入，返回自增主键的主键值

TroubleIsMyFriend 2013-07-29 04:24:02

请教各位达人，在mybatis3中，我要批量插入一批数据到mysql数据库，使用的foreach标签，这个表的主键是自动递增的，我想获取插入的这些数据的主键ID，请问怎么获得？用selectKey标签也获取不到。样例如下：
<insert id="saveBatchRechActivity" parameterType="java.util.List">
<selectKey keyProperty="id" resultType="int" order="AFTER">
select last_insert_id()
</selectKey>
insert into td_rech_act_conf(card_type,channel_id,rech_amount,acct_amount,gift_amount,eff_time,exp_time,act_name,introduction)
values
<foreach collection="list" item="item" separator=",">
(#{item.cardType},#{item.channelId},#{item.rechAmount}*100,#{item.acctAmount}*100,#{item.giftAmount}*100,#{item.effTime},#{item.expTime},#{item.activityName},#{item.introduction})
</foreach>
</insert>

...全文

7131 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

考拉fm 2015-08-20

打赏
举报

使用mybaits批量插入数据，返回主键int类型的数据，xml里面怎么配置？我的xml如下：主键字段是在数据库中自增的： <insert id="insert_mysql" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id"> INSERT INTO monitor_interface (interface_id, interface_name, interface_source, interface_group, step_type, step_from, step_to, step_sum, step_ok, step_fail, step_avg, step_min, step_max, flag, remark, deal_version, create_time, create_by, update_time, update_by) VALUES <foreach collection="list" item="item" index="index" separator=","> (#{item.interfaceId},#{item.interfaceName},#{item.interfaceSource},#{item.interfaceGroup}, #{item.stepType},#{item.stepFrom},#{item.stepTo},#{item.stepSum},#{item.stepOk},#{item.stepFail},#{item.stepAvg},#{item.stepMin} ,#{item.stepMax},#{item.flag},#{item.remark},#{item.dealVersion},#{item.createTime},#{item.createBy},#{item.updateTime},#{item.updateBy}) </foreach> </insert> 查询语句如下： public int insert(List<MonitorInterface> monitorInterfaceList) { DataSourceHandle.setDataSourceType(DataSourceConst.MYSQL); // 指向哪个库 int mil = mbDao.insert("mysql_monitor.insert_mysql",monitorInterfaceList); System.out .println("成功插入数据库。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。"); return 0; } 报错如下：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] at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:365) at com.sun.proxy.$Proxy7.insert(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:237) at cn.com.jtcrm.common.mybatis.dao.MbBaseDao.insert(MbBaseDao.java:26) at com.jtcrm.web.monitor.service.TestService.insert(TestService.java:181) at com.jtcrm.web.monitor.service.TestService.staticicsResultTotal(TestService.java:158) at com.jtcrm.web.monitor.logic.Test_BUZLogic.processLogic(Test_BUZLogic.java:40) at com.jtcrm.web.common.service.logic.AbstractCSBBizServiceLogic.process(AbstractCSBBizServiceLogic.java:29) at com.jtcrm.web.monitor.logic.Test_BUZLogicTimerLogic.processLogic(Test_BUZLogicTimerLogic.java:39) at com.jtcrm.web.common.timer.AbstractTimerBusTaskLogic.processTimerLogic(AbstractTimerBusTaskLogic.java:123) at com.jtcrm.web.common.timer.AbstractTimerBusTaskLogic.process(AbstractTimerBusTaskLogic.java:75) at com.jtcrm.web.common.task.WorkerRunable.run(WorkerRunable.java:38) at com.jtcrm.web.common.task.ThreadServerPool$Worker.run(ThreadServerPool.java:115) 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 [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:108) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:134) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355) ... 12 more Caused by: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [list] at org.apache.ibatis.session.defaults.DefaultSqlSession$StrictMap.get(DefaultSqlSession.java:254) at org.apache.ibatis.reflection.wrapper.MapWrapper.getSetterType(MapWrapper.java:75) at org.apache.ibatis.reflection.MetaObject.getSetterType(MetaObject.java:98) at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.getTypeHandlers(Jdbc3KeyGenerator.java:79) at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:58) ... 25 more 求解决？主键在mysql数据库中自增。。。。。。。。。。。。。

lhltolerate 2014-12-23

打赏
举报

同问，顶起，批量不知道怎样获取自增id

jankin-l 2014-08-22

打赏
举报

同问，顶起。

virtualhaha 2014-04-06

打赏
举报

1.<insert > 块中加入 useGeneratedKeys="true" keyProperty="id" 2.程序里边判断插入成功与否，直接返回参数model, 返回的model中就包含了主键代码展示：


<insert id="insertStudent" parameterType="xxx.model.Student" useGeneratedKeys="true" keyProperty="id">
      insert into student..........
</insert>


	public Student insertStudent(Student stu){
		int ret=this.getSqlSession().insert("insertStudent", stu);
		if(ret>0){
			return stu;
		}
		return null;
	}