mysql 一次性导入数据库_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 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();

}

分享到：

2013-12-29 15:15

浏览 5003

分类:数据库

评论