1、连接mysql
private String driver = "com.mysql.jdbc.Driver";
private Connection conn = null;
private Statement state = null;
public MysqlConnection(String url, String user, String psw){
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, psw);
state = conn.createStatement();
} catch (ClassNotFoundException e) {
e.printStackTrace();
return ;
} catch (SQLException e) {
e.printStackTrace();
return ;
}
System.out.println("connect successful");
}
2、简单批处理,要批处理插入,大多数想到的是逐条执行insert语句,代码如下
public boolean insertIntoTempUser(TempUser tu){
String sqlStr = "insert into TEMP_USER (cardno, count, amount) value (";
sqlStr +="\'" + tu.getCardno() +"\',\'" + tu.getCount()+"\',\'"+tu.getAmount()+"\')";
try {
state.clearBatch();
state.addBatch(sqlStr);
state.executeBatch();
//System.out.println("insert into temp_user successful");
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
但如果要插入成千上万的数据,这样的速度非常慢,因为执行一条insert都要提交一次数据库,速度自然会变慢。
这时候就会想到不一条条执行,同时也注意到addBatch()可以添加多条sql语句,可以将要插入的语句先都添加,然后最后全部提交。
public boolean insertByBatch(List<TempUser> users){
for(int i = 0 ; i < users.size() ; i++){
TempUser tu = users.get(i);
String sqlStr = "insert into TEMP_USER (cardno, count, amount) value ("+
"\'" + tu.getCardno() +"\',\'" + tu.getCount()+"\',\'"+tu.getAmount()+"\')";
try {
state.clearBatch();
state.addBatch(sqlStr);
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
try {
state.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
但是,有个问题是不能忽略的,上述代码中的sql语句是动态创建的,这样就很容易受到sql注入攻击,所以上述还不是很好的插入方案。可以用PreparedStatement来解决
public boolean batchInsert(List<TempUser> users){
String sqlStr = "insert into TEMP_USER (cardno, count, amount) value (?,?,?)";
try {
PreparedStatement ps = conn.prepareStatement(sqlStr);
conn.setAutoCommit(false);//自动提交记得关了....
for(TempUser tu: users){
ps.setString(1, tu.getCardno());
ps.setLong(2, tu.getCount());
ps.setString(3, String.valueOf(tu.getAmount()));
ps.addBatch();
}
ps.executeBatch();
conn.commit();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
但问题又来了,如果你需要插入上十万条数据,可能jvm这时可能就会生气了,指不定就给你抛出
java.lang.OutOfMemoryError: Java heap space异常了,这时候,就需要设置批量处理大小了。
public boolean batchInsert(List<TempUser> users){
String sqlStr = "insert into TEMP_USER (cardno, count, amount) value (?,?,?)";
try {
PreparedStatement ps = conn.prepareStatement(sqlStr);
conn.setAutoCommit(false);//自动提交记得关了....
int count = 0;
for(TempUser tu: users){
ps.setString(1, tu.getCardno());
ps.setLong(2, tu.getCount());
ps.setString(3, String.valueOf(tu.getAmount()));
ps.addBatch();
count ++;
if(count % batchSize == 0){
ps.executeBatch();
conn.commit();
ps.clearBatch();
}
}
ps.executeBatch();
conn.commit();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
参考:http://www.cnblogs.com/kahreman/archive/2012/08/06/2625827.html