FileToMYSQL - TuPengXiong/TuPengXiong.github.io GitHub Wiki

package test;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class FileToMysql {
	private static FileToMysql fileToMysql = new FileToMysql();
	private static final String driverName = "com.mysql.jdbc.Driver";
	private static final String user = "root";

	private static final String password = "mysql";

	private static final String url = "jdbc:mysql://localhost:3306/credit?characterEncoding=utf8";

	private static Connection conn = null;

	private static String tableName = "txt";
	// 表的属性
	private List<String> tableProp = new ArrayList<String>();

	private static String fileName = "C:/Users/fangjunlong/Desktop/2.txt";

	// 分隔符
	private static String spiltChar = "\\|";

	// 批量插入数据
	private static int batchSize = 1000;

	private static FileToMysql getInstance() {
		return fileToMysql;
	}

	private void init() throws ClassNotFoundException, SQLException {
		Class.forName(driverName);
		conn = DriverManager.getConnection(url, user, password);
	}

	public static void main(String[] args) throws ClassNotFoundException, SQLException {

		BufferedReader br;
		String str = null;
		try {
			FileInputStream fis = new FileInputStream(fileName);
			InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
			br = new BufferedReader(isr);
			List<String[]> values = new ArrayList<String[]>();
			while ((str = br.readLine()) != null) {
				// Test.getInstance().insert(str.split("\\|"));
				values.add(str.split(spiltChar));
				if (values.size() % batchSize == 0) {
					FileToMysql.getInstance().insertBatch(values);
					values = new ArrayList<String[]>();
				}
			}
			if (values.size() % batchSize != 0) {
				FileToMysql.getInstance().insertBatch(values);
				values = new ArrayList<String[]>();
			}
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public List<String> getTableProp() throws SQLException, ClassNotFoundException {
		if (conn == null) {
			init();
			PreparedStatement pstate = conn.prepareStatement("show full fields from " + tableName);
			ResultSet results = pstate.executeQuery();
			while (results.next()) {
				tableProp.add(results.getString("FIELD"));
			}
			System.out.println(tableName + ":" + tableProp.size());
		}
		return tableProp;
	}

	public int insert(String[] list) throws ClassNotFoundException, SQLException {
		List<String> props = getTableProp();
		StringBuffer sql = new StringBuffer("insert into ").append(tableName).append("(");

		for (int i = 0; i < props.size(); i++) {
			sql.append(props.get(i));
			if (props.size() - 1 != i) {
				sql.append(",");
			} else {
				sql.append(")");
			}
		}
		sql.append(" values(NULL,");
		for (int i = 0; i < list.length; i++) {
			sql.append("'").append(list[i]).append("'");
			if (list.length - 1 != i) {
				sql.append(",");
			} else {
				sql.append(")");
			}
		}
		System.out.println(sql.toString());
		PreparedStatement sta = conn.prepareStatement(sql.toString());
		int r = sta.executeUpdate();
		return r;
	}

	public int insertBatch(List<String[]> list) throws ClassNotFoundException, SQLException {
		List<String> props = getTableProp();
		StringBuffer sql = new StringBuffer("insert into ").append(tableName).append("(");

		for (int i = 0; i < props.size(); i++) {
			sql.append(props.get(i));
			if (props.size() - 1 != i) {
				sql.append(",");
			} else {
				sql.append(")");
			}
		}
		sql.append(" values");
		for (int i = 0; i < list.size(); i++) {
			String[] values = list.get(i);
			sql.append("(NULL,");
			for (int j = 0; j < values.length; j++) {
				sql.append("'").append(values[j]).append("'");
				if (values.length - 1 != j) {
					sql.append(",");
				} else {
					sql.append(")");
				}
			}
			if (list.size() - 1 != i) {
				sql.append(",");
			}
		}
		System.out.println(sql.toString());
		PreparedStatement sta = conn.prepareStatement(sql.toString());
		int r = sta.executeUpdate();
		return r;
	}

}