数据库有六十万数据,要进行批量更新
执行程序报错
java.lang.OutOfMemoryError: Java heap space
idea内存已改
-Xms1024m
-Xmx4096m
-XX:ReservedCodeCacheSize=1024m
设置了heap最大内存为4096之后
使用visualVM查看内存发现只用了2048就溢出了,,为啥没到4096呢。
代码如下
@Test
public void testDBUpdateBatch() throws SQLException {
//三十万数据
//OK,查询数据用时:235 OK,查询数据用时:348 OK,查询数据用时:214
//OK,用时:6389 OK,用时:5179 OK,用时:5737
//OK,查询数据用时:165
//OK,用时:47915 大量时间消耗在for语句里面随机赋值
Connection conn = DBUtil.getConnection();
PreparedStatement psmt = null;
Long startTime1 = System.currentTimeMillis();
Statement stmt = DBUtil.getConnection().createStatement();
ResultSet rs = stmt.executeQuery("select id from Table1 where id > 7");
List<Integer> list = new ArrayList<>();
while(rs.next()){//如果对象中有数据,就会循环打印出来
// System.out.println(rs.getInt("id")+","+rs.getString("column2"));
list.add(rs.getInt("id"));
}
Long endTime1 = System.currentTimeMillis();
System.out.println("OK,查询数据用时:" + (endTime1 - startTime1));
Long startTime = System.currentTimeMillis();
Random random =new Random();
float min = 1f;
float max = 100000000f;
try {
conn.setAutoCommit(false);
String sql = "update Table1 " +
"set idn = ? ,column2 = ? ,column2n = ? ,column3 = ?,column3n = ?,column4 = ?,column4n=?,column5=?,column5n=?,column6=?,column6n=?,column7=?,column7n=?,column8=?,column8n=?,column9=?,column9n=?,column10=?,column10n=?,column11=?,column11n=? " +
"where id = ?";
psmt= conn.prepareStatement(sql);
for (Integer i : list){
int a = random.nextInt(1000000);
int b = random.nextInt(1000000);
psmt.setInt(1, a);
psmt.setString(2, UUID.randomUUID().toString());
psmt.setString(3, UUID.randomUUID().toString());
java.util.Date date = new java.util.Date();
psmt.setObject(4, new Timestamp(date.getTime()));
psmt.setObject(5, new Timestamp(date.getTime()));
psmt.setDate(6, new Date(date.getTime()));
psmt.setDate(7, new Date(date.getTime()));
psmt.setBigDecimal(8, new BigDecimal(a+"."+b));
psmt.setBigDecimal(9, new BigDecimal(a+"."+b));
psmt.setBigDecimal(10, new BigDecimal(a+"."+b));
psmt.setBigDecimal(11, new BigDecimal(a+"."+b));
psmt.setFloat(12, min + random.nextFloat() * (max - min));//随机float
psmt.setFloat(13, min + random.nextFloat() * (max - min));
psmt.setString(14, "char"+UUID.randomUUID().toString());
psmt.setString(15, "char"+UUID.randomUUID().toString());
psmt.setInt(16, random.nextInt(100000000)*100);
psmt.setInt(17, random.nextInt(100000000)*100);
psmt.setInt(18, random.nextInt(2));
psmt.setInt(19, random.nextInt(2));//[0,2) 生成0或者1
psmt.setBigDecimal(20, new BigDecimal(a+"."+b));
psmt.setBigDecimal(21, new BigDecimal(a+"."+b));
//psmt.setInt(1, 666);
//psmt.setString(2, "齐天大圣孙悟空");
psmt.setInt(22, i);
psmt.addBatch();
}
psmt.executeBatch();
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("OK,用时:" + (endTime - startTime));
}catch (Exception e){
e.printStackTrace();
}finally {
if(psmt!=null)psmt.close();
if(conn!=null)conn.close();
}
}