mysql jdbc批量更新_jdbc批量更新数据

{

boolean add = true;

Connection conn = null;

PreparedStatement pst = null;

String sql = null;

try

{

conn = this.getConnection();

conn.setAutoCommit(false);

// sql = "MERGE INTO PUSH_NUMBER T1 "

// + " USING (SELECT ? AS MOBILE, ? AS PKG_ID FROM DUAL) T2 "

// + " ON (T2.MOBILE=T1.USER_NUMBER AND T2.PKG_ID=T1.PKG_ID) " + " WHEN NOT MATCHED THEN "

// + " INSERT VALUES(SEQ_PUSH_NUMBER.NEXTVAL,?,?)";

sql = "insert into PUSH_NUMBER(NUMBER_ID,USER_NUMBER,PKG_ID) values(null,?,?)";

pst = conn.prepareStatement(sql);

int num = 0;

for (String mobile : numbers)

{

num++;

pst.setString(1, mobile);

pst.setInt(2, pkgId);

pst.addBatch();

if (num % 30000 == 0)

{

pst.executeBatch();

pst.clearBatch();

}

}

if (num > 0)

{

pst.executeBatch();

}

conn.commit();

}

catch (Exception e)

{

add = false;

logger.error("addNumber was failed!", e);

}

finally

{

if (null != pst)

{

pst.close();

}

if (null != conn)

{

conn.close();

}

}

return add;

}

spring jdbc批量入库

//采用insert into replace 检测手机号码是否存在，存在更新，不存在新增

String sql = "insert into push_token(token,mobile) values(?,?) on DUPLICATE KEY UPDATE token = ?";

List args = new ArrayList();

for (int i = 0; i < tokens.size(); i++) {

Object[] objects = new Object[]{tokens.get(i), mobiles.get(i), tokens.get(i)};

args.add(objects);

}

//3000条提交一次

ArrayList temp = new ArrayList(3000);

List copy = null;

for (Object[] objects : args) {

temp.add(objects);

if (temp.size() >= 3000) {

copy = (List) temp.clone();

temp.clear();

if (temp.isEmpty() && copy.size() >= 3000) {

getJdbcTemplate().batchUpdate(sql, copy);

}

}

}

if (temp.size() > 0) {

copy = (List) temp.clone();

temp.clear();

if (temp.isEmpty() && copy.size() > 0) {

getJdbcTemplate().batchUpdate(sql, copy);

}

}