JDBC 造数据、批量数据保存

package luna.platform.frame.utils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import luna.platform.frame.utils.DBUtils;

/**
 * 批量数据操作
 * 
 * @author xl
 *
 */
public class BatchDataOperation {
	private int allCount = 1000000;
	private int count = 20000;
	private String pssql = "INSERT INTO testtable(id,userName,age) values(?,?,?)";

	public static void main(String[] args) {
		BatchDataOperation o = new BatchDataOperation();
		o.BbatchSave();
	}
	public void BbatchSave() {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = null;
		try {
			conn = DBUtils.getConnection();

			// 保存当前自动提交模式
			boolean autoCommit = conn.getAutoCommit();
			// 关闭自动提交
			conn.setAutoCommit(false);

			long beginTime = System.currentTimeMillis();
			// sql = "INSERT INTO t_user values(?,?,?)";
			ps = conn.prepareStatement(pssql);
			Date date = new Date(new java.util.Date().getTime());
			for (int i = 0; i < allCount; i++) {
				ps.setString(1, i + 1 + "");
				ps.setString(2, "name_" + i);
				ps.setInt(3, i);

				// 积攒SQL
				ps.addBatch();

				// 当积攒到一定程度,就执行一次,并且清空记录
				if ((i + 1) % count == 0) {
					ps.executeBatch();
					ps.clearBatch();
					long xte = System.currentTimeMillis();
					System.out.println("the num:"+ i + "-----the time:" + (xte -beginTime)/1000 + "s");
				}
			}
			// 总条数不是批量值整数倍,则还需要在执行一次
			if (100000 % count != 0) {
				ps.executeBatch();
				ps.clearBatch();
			}
			long endTime = System.currentTimeMillis();
			System.out.println("the exceute time : " + (endTime - beginTime));
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			DBUtils.closeRec(conn, ps);
		}
	}

}

package luna.platform.frame.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBUtils {
	 public static Connection getConnection (){  
	        Connection conn = null;  
	        try {  
	            Class.forName("com.mysql.jdbc.Driver");  
	            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jeesite","root","123456");  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  
	        return conn;  
	    }  
	      
	    public static void closeRec(Connection conn,PreparedStatement ps){  
	        try {  
	            if(ps!=null){  
	                ps.close();  
	            }  
	            if(conn!=null){  
	                conn.close();  
	            }  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  
	    }  
	      
	    public static void closeRec(Connection conn,PreparedStatement ps,ResultSet rs){  
	        try {  
	            if(ps!=null){  
	                ps.close();  
	            }  
	            if(conn!=null){  
	                conn.close();  
	            }  
	            if(rs!=null){  
	                rs.close();  
	            }  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  
	    }  
}