日期:2014-05-16  浏览次数:20636 次

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();
		}