NutzCN Logo
精华 批量插入时因为唯一索引抛异常问题
发布于 2690天前 作者 LuoYY 5438 次浏览 复制 上一个帖子 下一个帖子
标签: dao batch

使用dao.fastInsert()批量插入一个List 的时候因为List中存在与表中记录重复的对象,会抛异常,这会影响List 中其他对象的插入么?
另外,怎么根据一个全是id的List获取对应对象的List,对逐个进行修改后,然后批量更新

6 回复

前者,我建议亲自测试一下

后者,逐一fetch出来就好了嘛,用in查询的话不走索引了

@wendal 测试了一下,插入的时候有和唯一索引的话会抛异常然后会让这个线程直接崩掉,有什么办法可以直接一个检测一个对象是否和唯一索引重复
类似于

Dao.check(T obj)

异常信息如下

Exception in thread "Timer-0" org.nutz.dao.DaoException: !Nutz SQL Error: 'INSERT INTO t_timecount(deviceId,countId,date,startTime,countTime) VALUES('9c879c33','activeTime',20161116,1479260866464,70063) '
PreparedStatement: 
'INSERT INTO t_timecount(deviceId,countId,date,startTime,countTime) VALUES(?,?,?,?,?) '
CaseMessage=Duplicate entry 'ac00bf13-activeTime-1479215437639' for key 'index_timecount1'
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:104)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66)
	at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64)
	at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:147)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:114)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:81)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:70)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:266)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:274)
	at org.nutz.dao.impl.EntityOperator.exec(EntityOperator.java:50)
	at org.nutz.dao.impl.NutDao.fastInsert(NutDao.java:203)
	at pub.mysci.ds.utils.SyncQueueService.insertList2db(SyncQueueService.java:65)
	at pub.mysci.ds.utils.SyncQueueService.access$1(SyncQueueService.java:64)
	at pub.mysci.ds.utils.SyncQueueService$1.run(SyncQueueService.java:57)
	at java.util.TimerThread.mainLoop(Unknown Source)
	at java.util.TimerThread.run(Unknown Source)
Caused by: java.sql.BatchUpdateException: Duplicate entry 'ac00bf13-activeTime-1479215437639' for key 'index_timecount1'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1161)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773)
	at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257)
	at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:958)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeBatch(FilterChainImpl.java:2596)
	at com.alibaba.druid.filter.FilterAdapter.statement_executeBatch(FilterAdapter.java:2474)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_executeBatch(FilterEventAdapter.java:279)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeBatch(FilterChainImpl.java:2594)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeBatch(StatementProxyImpl.java:192)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:559)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runPreparedStatement(NutDaoExecutor.java:322)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
	... 17 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'ac00bf13-activeTime-1479215437639' for key 'index_timecount1'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751)
	... 27 more

dao.count(...) == 0

@wendal 这个不是泛型啊,我想要的是输入参数是 对象,根据对象生成索引键值,然后去和表的索引比较是否重复,返回布尔值

   private <T> boolean check(T obj){
		// do something
		return true;// true or false;
	}
private <T> boolean check(T obj) {
    Entity<T> en = dao.getEntity(obj.getClass());
	switch (en.getPkType()) {
	case ID:
	    MappingField mf = en.getIdField();
	    return dao.count(obj.getClass(), Cnd.where(mf.getName(), mf.getValue(obj))) == 0;
    case NAME:
	    // 自行补充...
	}
}

@wendal 根据你代码的启发写出来了

private static <T> boolean isFresh(T obj) {
		Cnd cnd = Cnd.where(null);
	    Entity<?> en = dao.getEntity(obj.getClass());
		List<EntityIndex> entityIndexs = en.getIndexes();
		boolean isFresh = true;
		for(EntityIndex entityIndex:entityIndexs){
			if (entityIndex.isUnique()) {
				List<EntityField> entityFields= entityIndex.getFields();
				for(EntityField entityField:entityFields){
					cnd.and(entityField.getName(),"=",entityField.getValue(obj));
				}
				isFresh = dao.count(obj.getClass(), cnd) == 0;
			}
			if (!isFresh){
				break;
			}
		}
		return isFresh;
	}

这个方法是检索出唯一索引然后查询,写了这个之后就对Dao.fastInsert() 进行了测试,证实了不会影响 List 中其他对象的插入,但是使用Dao.fastInsert()的时候必须人为用 try catch 去捕捉处理掉异常,不然会引起线程直接崩溃

添加回复
请先登陆
回到顶部