向s_emp批量插入10000条数据随机产生的数据

引用

向s_emp批量插入10000条数据随机产生的数据
每200条一批
// 如果执行一批时出现异常，捕获，并继续下一批
id  [1, 9999999]
first_name 3-16个随机字符
last_name 3-16个随机字符
salary 2000-15000随机数字

package com.test.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Random;

import com.tarena.util.DBUtil;

public class InsertRandom {
	static int times;
	static void insertRan() throws Exception {
		Connection con = DBUtil.openInThread();
		PreparedStatement ps = null;
		String sql = "insert into s_emp(id,first_name,last_name,salary) value(?,?,?,?)";
		ps = con.prepareStatement(sql);//注意别放到循环里面去，否则这会每200次执行一条语句
		try{
			while (times <= 10000) {
				ps.setInt(1, randomInt());
				ps.setString(2, randomString());
				ps.setString(3, randomString());
				ps.setDouble(4, randomDouble());
				ps.addBatch();//将该sql语句添加到批处理列表中
				if (times % 200 == 0) {					
						ps.executeBatch();//执行批处理列表中的所有命令
						System.out.println(times+ "条插入成功！！");
				}
				times++;
				}
		}finally{
			ps.close();
			DBUtil.closeInThread();
		}
	}
	static Random ran = new Random();
	private static double randomDouble() {
		return ran.nextInt(13000) + 2000;	
	}
	private static String randomString() {
		StringBuffer str = new StringBuffer();
		for (int a = 0; a <= ran.nextInt(14) + 3; a++) {
			char c = (char) ('a' + ran.nextInt(26));
			str.append(c);
		}
		String s = str.toString();
		return s;
	}
	
	static int i = 0;//静态方法中使用类变量才能使变量值使之共享
	private static int randomInt() {
		//int i = ran.nextInt(999999999) + 1;//随机范围尽可能大些，因为有可能有重复的ID，会引发异常
		return i++;
	}
	public static void main(String[] args) throws Exception {
		insertRan();
	}

}

DBUtil类

package com.tarena.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBUtil {

	private static final String URL;
	private static final String USERNAME;
	private static final String PASSWORD;
	private static final String DRIVER;

	private static ThreadLocal threadLocal = new ThreadLocal();
	
	static {
		InputStream in = DBUtil.class.getResourceAsStream("/db.properties");
		Properties prpe = new Properties();
		try {
			prpe.load(in);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				in.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		URL = prpe.getProperty("url");
		DRIVER = prpe.getProperty("driver");
		USERNAME = prpe.getProperty("username");
		PASSWORD = prpe.getProperty("password");
		// 注册驱动，创建OracleDriver实例；并注册到DriverManager；
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 开启数据库连接；
	public static Connection open() throws Exception {
		// 得到数据库的连接对象；
		return DriverManager.getConnection(URL, USERNAME, PASSWORD);
	}

	// 关闭数据库连接；
	public static void closed(ResultSet rs, Statement st, Connection con) {
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		if(st != null){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(con != null){
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
	}
	
	@SuppressWarnings("unchecked")
	public static Connection openInThread() throws Exception{
		Connection con = (Connection)threadLocal.get();
		if(null == con){
			con = open();
			threadLocal.set(con);
		}
		return con;
	}
	
	public static void closeInThread(){
		Connection con;
		try {
			con = openInThread();
			con.close();
		} catch (Exception e) {}
		threadLocal.remove();
	}	
}

总结：