jdbc百万数据批处理遇到的问题和处理
首先总结哈
1 关于PreparedStatement 和statement
更新和插入 一定要用PreparedStatement 防止字段带有‘’ ,即sql注入
ex:
StringBuffer sql = new StringBuffer("UPDATE CH_T_SONG_INFO");
sql.append(" set " );
sql.append(" SEQ=? , SONG_ID=? , CONTENT_NAME=? ,SINGER_ID=?, SINGER=? where SONG_ID='"+ chTMusicSourceInfo.getSongId() + "' and SINGER_ID='" + chTMusicSourceInfo.getSingerId()+ "' and status<>'2'" );
Connection conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql.toString());
ps.setObject(1, chTMusicSourceInfo.getSeq());
ps.setObject(2, chTMusicSourceInfo.getSongId());
ps.setObject(3, chTMusicSourceInfo.getContentName());
ps.setObject(4, chTMusicSourceInfo.getSingerId());
ps.executeUpdate();
查询用statement
ex:
Statement stm = JdbcUtil.getStm();
ResultSet rs=stm.executeQuery("select SEQ_SONG_ALBUM_INFO.nextval from dual");
if (rs.next())
return rs.getLong(1);
推荐用PreparedStatement
2 出现游标超出问题是因为执行了sql语句,资源没关闭,
Connect可以执行一个循环时在清理,但是必须得清理,不然也会出现游标问题,而像PreparedStatement 和statement 用一次 必须关一次 否则超出游标
3 批处理(一定要用事务)
static void createBatch() throws SQLException { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); conn setAutoCommit(false); String sql = "insert into person(pid,name,birthday, money) values (?,?, ?, ?) "; ps = conn.prepareStatement(sql); for (int i = 0; i < 100; i++) { ps.setInt(1, 3000+i); ps.setString(2, "person" + i); ps.setDate(3, new Date(System.currentTimeMillis())); ps.setFloat(4, 100f + i); ps.addBatch(); } int[] is = ps.executeBatch(); JdbcUtil.getConnection().commit(); } finally { JdbcUtils.free(rs, ps, conn); } } }