编写Mysql JDBC程序导入数据到mysql

package com.cxz.test;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC_Test {
	
	static Connection conn;
	static Statement stmt;
	static PreparedStatement pstmt;

	public static void main(String[] args) {
		JDBC_Test test = new JDBC_Test();
		conn = test.getConnection();
		test.insert(conn);
		test.insert2(conn);
		test.insert3(conn);
		test.insert4(conn);
		test.insert5(conn);
		test.query(conn);
		try {
			conn.close();
		} catch (SQLException e) {
			System.out.println("Close connection failed: " + e.getMessage());
		}
	}
	
	public void insert(Connection conn) {
		String sql = "INSERT INTO tb1 (id, value) values (1, 'a')";
		int count = -1;
		try {
			stmt = conn.createStatement();
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println("insert data failed: "+e.getMessage());
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				
				stmt = null;
			}
		}
		
		System.out.println("insert into table tb1 "+count+" items.");
	}
	
	public void insert2(Connection conn) {
		String sql = "INSERT INTO tb1 (id, value) values (?, ?)";
		//int count = -1;
		int BATCH_SIZE = 10;
		
		try {
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < BATCH_SIZE; i++) {
				pstmt.clearParameters();
				pstmt.setInt(1, 2);
				pstmt.setString(2, "b");
				pstmt.execute();
			}
			//count = pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("insert data failed: "+e.getMessage());
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				
				stmt = null;
			}
		}
		
		System.out.println("insert into table tb1 "+1+" items.");
	}
	
	public void insert3(Connection conn) {
		String sql = "INSERT INTO tb1 (id, value) values (?, ?)";
		int count = -1;
		int COMMIT_SIZE = 10;
		
		try {
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < COMMIT_SIZE; i++) {
				pstmt.clearParameters();
				pstmt.setInt(1, 3);
				pstmt.setString(2, "c");
				count = pstmt.executeUpdate();
				if (i % COMMIT_SIZE == 0) {
					conn.commit();
				}
			}
			
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			System.out.println("insert data failed: "+e.getMessage());
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				
				stmt = null;
			}
		}
		
		System.out.println("insert into table tb1 "+count+" items.");
	}
	
	public void insert4(Connection conn) {
		String sql = "INSERT INTO tb1 (id, value) values (?, ?)";
		int BATCH_SIZE = 10;
		int COMMIT_SIZE = 10;
		int i = 0;
		
		try {
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			for (i = 0; i < 20; i += BATCH_SIZE) {
				pstmt.clearBatch();
				for (int j = 0; j < BATCH_SIZE; j++) {
					pstmt.setInt(1, 4);
					pstmt.setString(2, "d");
					pstmt.addBatch();
				}
				pstmt.executeBatch();
				if ((i + BATCH_SIZE) % COMMIT_SIZE == 0) {
					conn.commit();
				}
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			System.out.println("insert data failed: "+e.getMessage());
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				
				stmt = null;
			}
		}
		
		System.out.println("insert into table tb1 "+20+" items.");
	}
	
	public void insert5(Connection conn) {
		String sql = "load data local infile '' into table tb1 fields terminated by '#'";
		StringBuilder sb = new StringBuilder();
		int COMMIT_SIZE = 10;
		
		try {
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < 20; i++) {
				sb.append(5+"#"+"e"+"\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();
			sb.setLength(0);
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			System.out.println("insert data failed: "+e.getMessage());
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				
				stmt = null;
			}
		}
		
		System.out.println("insert into table tb1 "+20+" items.");
	}
	
	public void query(Connection conn) {
		String sql = "select id, value from tb1";
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			System.out.println("The query results as follows:");
			while(rs.next()) {
				int id = rs.getInt("id");
				String value = rs.getString("value");
				
				System.out.println(id + "\t" + value);
			}
		} catch (SQLException e) {
			System.out.println("query data failed: "+e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {}
				
				rs = null;
			}
			
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				
				stmt = null;
			}
		}
	}
	
	public Connection getConnection() {
		Connection conn = null;
		String conn_uri = "jdbc:mysql://192.168.1.10:3306/cxz_db";
		String username = "cxz";
		String passwd = "cxz123";
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			System.out.println("Load jdbc driver failed: " + e.getMessage());
		}
		
		try {
			conn = DriverManager.getConnection(conn_uri, username, passwd);
		}catch(SQLException e) {
			System.out.println("Connect to mysql DB failed!");
			System.out.println("SQLException: " + e.getMessage());
		    System.out.println("SQLState: " + e.getSQLState());
		    System.out.println("VendorError: " + e.getErrorCode());
		}
		return conn;
	}
}

此程序参考了blog： http://www.cnblogs.com/end/archive/2012/10/25/2738818.html