JDBC 批量插入数据

package com.web.test;

import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileWriter;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;

public class TestMysql {

	
	public static int COMMIT_SIZE = 25000; // 插入数量
	public static void main(String args[]) {

	//	writeTestTxt(COMMIT_SIZE);
		//battchInsertByFile();//加载文件后插入
		  battchInsertByStream();//数据生成后插入
	}
	
	public static void  battchInsertByFile() {
		try {
			System.out.println("开始写入数据");
			Connection conn = getConnection();// 取得数据库的连接
			conn.setAutoCommit(false);
			long starTime = System.currentTimeMillis();
            //String sql = "load data infile 'c:/t0.txt' into table t0 fields terminated by ',' enclosed by '\\'' lines terminated by '\\r\\n'";
             String sql = "load data infile 'c:/t0.txt' replace into table t0 character set GBK fields terminated by ',' enclosed by '\\'' lines terminated by '\\r\\n' (`name`,`age`,`description`)";
            PreparedStatement  pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			conn.commit();
			long endTime = System.currentTimeMillis();
			System.out.println("program runs " + (endTime - starTime) + "ms");
			
			// 关闭声明和连接
			pstmt.close();
			conn.close();
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
			// 显示数据库连接错误或者查询错误
		}
		
	}
	
	public static void  battchInsertByStream() {

		// 一共多少个
		int COUNT = 100000;

		long a = System.currentTimeMillis();
		Connection conn = null;

		try {

			conn = getConnection();
			long starTime = System.currentTimeMillis();

			conn.setAutoCommit(false);
			PreparedStatement pstmt = conn.prepareStatement("load data local infile '' "
			        + "into table t0 fields terminated by ',' (`name`,`age`,`description`)");
			StringBuilder sb = new StringBuilder();
			for (int i = 1; i <= COUNT; i++) {
				//sb.append(i + "," + i + "abc" + "\n");
				sb.append("name_a"+i+",age_a,english_a\n");
				if (i % COMMIT_SIZE == 0) {
					InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
					((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
					pstmt.execute();
					conn.commit();
					sb.setLength(0);
				}
			}
			InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
			((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
			pstmt.execute();
			conn.commit();

			long endTime = System.currentTimeMillis();
			System.out.println("program runs " + (endTime - starTime) + "ms");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// 在最好的一行加上:
		System.out.println("\r插入数据条数：" + COUNT + ",提交的阀值：" + COMMIT_SIZE
						+ ",执行耗时 : " + (System.currentTimeMillis() - a) / 1000f
						+ " 秒 ");
	}

	public static Connection getConnection() {
		Connection con = null;
		// 取得连接的url
		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true";
		// 加载MySQL的jdbc驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// 使用能访问MySQL数据库的用户名root
			String userName = "root";// 使用口令
			String password = "root";// 打开数据库连接
			con = (Connection) DriverManager.getConnection(url, userName,
					password);
			return con;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}
	
	public static void  writeTestTxt(int count){
		try{
		     BufferedWriter writer = new BufferedWriter(new FileWriter(new File("c:\\t0.txt")));
		      for(int i=0 ;i<count;i++){
		     writer.write("name2"+i+",age2,english2\r\n");
		     //writer.write("\"101\",\"英语\",\"english\",\"100001\"\r\n");
		      }
		     writer.close();

		}catch(Exception e){

		 }
	}

}

 

 

CREATE TABLE `t0` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  `age` varchar(50) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;