MYSQL 一次性千万条带BLOB数据库批量插入经验
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();}