MYSQL 一次性千万条带BLOB数据库批量插入经验

yanchuen

DbUtil db = new DbUtil();
try {

db.connect(
"com.mysql.jdbc.Driver",
"jdbc:mysql://192.168.0.151/ztzx?useUnicode=true&characterEncoding=UTF-8",
"app", "app");
String sql = "select max(sfzhm) from idc";
String uuid = "";
String sfzhm = "0";
Vector<Object[]> vec = db.select(sql);
if(vec==null || vec.size()==0){
sfzhm="0";
}else{
for(Object[] obj:vec){
sfzhm= String.valueOf(obj[0]);
}
}
if(StringUtils.isBlank(sfzhm)){
sfzhm = "0";
}
System.out.println("===start===sfzhm===="+sfzhm);
File file = new File("D:\\sfzhm.jpg");
InputStream in = new BufferedInputStream(new FileInputStream(file));
blob = FileUtils.readFileToByteArray(file);
//PreparedStatement  必须的
PreparedStatement ps = null;
//long sfz= 0;
for (int i = 0; i < 10000000; i++) {
sfzhm = String.valueOf(Long.parseLong(sfzhm)+1);
sfzhm = CommUtils.getFormatStr(sfzhm, 18);
sql = "insert into idc (enabled,name,sfzhm,img) values (?,?,?,?)";
ps = db.getCconOra().prepareStatement(sql);
ps.setInt(1, 0);
ps.setString(2, "测试姓名");
ps.setString(3, sfzhm);
ps.setBinaryStream(4, in, (int) file.length());
//ps.setBytes(4, blob);
ps.addBatch();
// 1000条批量提交一次
if (i % 100 == 0) {
ps.executeBatch();
}
if (i % 1000 == 0) {
db.commit();
ps.close();
ps = db.getCconOra().prepareStatement(sql);
}

System.out.println(i+"=====sfzhm===="+sfzhm);
}
in.close();
in = null;
db.commit();
long end = System.currentTimeMillis();
System.out.println((end - start)/1000);
} catch (Exception e) {
db.rollback();
e.printStackTrace();
} finally {
db.disConnect();
}