Java利用JDBC对数据库批量插入数据(大数据)

http://www.javait.org/?p=877

package com.test.BigdataToDB;

import javax.swing.JFrame;
import javax.swing.JButton;
import javax.swing.ImageIcon;
import javax.swing.JLabel;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.JPasswordField;
import javax.swing.JComboBox;
import javax.swing.JRadioButton;
import javax.swing.ButtonGroup;
 


import java.awt.FlowLayout;
import java.awt.Color;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
 
/**
 * @author Administrator
 * 国信安百杰高端Java培训
 */
public class Test extends JFrame {
	public final String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=MasDB";
	public final String username = "sa";
	public final String password = "sa";
	public final String classdriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 
	public Connection conn;
	public Statement stmt;
	public ResultSet rs;
 
	public Test() {
		try {
			Class.forName(classdriver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
 
	/**
	 * 利用JDBC来创建表
	 */
	public void createTable() {
		String sql = "create table batch (a1 varchar(10),a2 varchar(500))";
		try {
			conn = DriverManager.getConnection(url, username, password);
			stmt = conn.createStatement();
			boolean f = stmt.execute(sql);
			System.out.println(f);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 
	/**
	 * 对数据库进行批量插入数据操作
	 * 执行次数100万
	 * @throws SQLException 
	 */
	public void insertBatch() throws SQLException {
		//思路：将100万条数据分成n等份，1等份为1000条数据
		//如何实现？
		//1、必须将Connection接口的自动提交方式改为手动
		//2、利用Statement接口中的如下三个方法：addBatch、clearBath、executeBatch
		System.out.println("开始插入数据："+getTodayTime());
		
		try {
			conn = DriverManager.getConnection(url, username, password);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (int i = 0; i < 1000000; i++) {
				String sql = "insert into batch values ('"+i+"', '第"+i+"条数据')";
				//利用addBatch方法将SQL语句加入到stmt对象中
				stmt.addBatch(sql);
				if (i % 1000 == 0 && i != 0) {
					//利用executeBatch方法执行1000条SQL语句
					stmt.executeBatch();
					stmt.clearBatch();
					conn.commit();
				}
			}
			stmt.executeBatch();
			stmt.clearBatch();
			conn.commit();
			
			close(); //关闭资源
			
		} catch (SQLException e) {
			conn.rollback(); //回滚
			e.printStackTrace();
		}finally{
			System.out.println("结束插入数据："+getTodayTime());
		}
	}
 
	 /**
     * 获得今天的时间
     * @return
     */
    public static String getTodayTime(){
    	Date date=new Date();//取时间
		Calendar calendar = new GregorianCalendar();
		calendar.setTime(date);
		date=calendar.getTime(); //这个时间就是日期往后推一天的结果
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = formatter.format(date);

		return dateString;
    }
	public void close() {
		try {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Test t = new Test();
//		t.createTable();                     //测试第一步，首先用该方法创建表
		try {
			t.insertBatch();             //测试第二步，向表中批量插入数据
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 
}