今天在使用executeBatch时,使用一个很简单的表
CREATE TABLE IF NOT EXISTS `fnbl_dummy` ( `id` varchar(32) NOT NULL, `userid` bigint(20) NOT NULL, `last_update` bigint(20) NOT NULL, `status` char(1) NOT NULL, `p_content` varchar(200) NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
做批量插入
String SQL_INSERT_INTO_FNBL_DUMMY = "INSERT INTO fnbl_dummy(id,userid,last_update,status,p_content) VALUES (?,?,?,?,?);"; Connection con = null; PreparedStatement ps = null; try { con = getUserDataSource().getRoutedConnection(userId); con.setAutoCommit(false); ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_DUMMY); for (DummyWrapper item : items) { Timestamp lastUpdate = item.getLastUpdate(); if (lastUpdate == null) { lastUpdate = new Timestamp(System.currentTimeMillis()); } ps.setString(1, item.getId()); ps.setLong(2, Long.parseLong(this.userId)); ps.setLong(3, lastUpdate.getTime()); ps.setString(4, String.valueOf(Def.PIM_STATE_NEW)); ps.setString(5, StringUtils.left(item.getContent(), SQL_LASTNAME_DIM)); ps.addBatch(); } int []len = ps.executeBatch(); con.commit(); return len.length; } catch (Exception e) { log.error(e.toString()); throw new DAOException("Error adding dummy items.", e); } finally { DBTools.close(con, ps, null); }
结果运行是单条测试一直OK,多条测试一直提示“java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”,查找原因竟然是因为SQL语句最后的";"分号问题,无语