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