主类:
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
public class Test {
private static Connection connection;
private static Statement statement;
private Table currentTable;
private static int count=0;
public static void main(String[] args){
Test test = new Test();
test.getConn();
for (int i = 2; i<= 288; i++){
String index;
if (i<10) {
index = "00" + i;
}else if (i<100){
index = "0" + i;
} else {
index = "" + i;
}
String tableName = "20170301_" + index;
System.out.println(tableName + " is running...");
test.createTable(tableName);
test.readFile(tableName);
System.out.println("sum: " + test.countTable(tableName));
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private void getConn() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/taxi";
String username = "?????";
String password = "??????";
Connection conn = null;
try {
Class.forName(driver); //classLoader,加载对应驱动
connection = DriverManager.getConnection(url, username, password);
statement = connection.createStatement();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
private void createTable(String tableName){
currentTable = new Table(tableName);
currentTable.addItem("id", "int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT");
currentTable.addItem("date", "date");
currentTable.addItem("time", "time");
currentTable.addItem("placeholder", "varchar(5)");
currentTable.addItem("taxi_id", "varchar(50)");
currentTable.addItem("longitude", "decimal");
currentTable.addItem("latitude", "decimal");
currentTable.addItem("speed", "decimal");
currentTable.addItem("direction", "int");
currentTable.addItem("state", "tinyint");
currentTable.addItem("validity", "tinyint");
String createTableSql = currentTable.makeCreateTableStatement();
try {
boolean rs = statement.execute(createTableSql);
} catch (SQLException e) {
e.printStackTrace();
}
}
private void insert(String line){
String insertSql = currentTable.makeInsertStatement(line);
// System.out.println(insertSql);
try {
statement.executeUpdate(insertSql);
count++;
} catch (SQLException e) {
e.printStackTrace();
System.out.println(count);
System.exit(1);
}
}
private int countTable(String tableName){
int res = 0;
try {
ResultSet resultSet = statement.executeQuery("select count(*) from "+ tableName + ";");
if(resultSet.next()) {
res=resultSet.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
private void readFile(String tableName){
try {
String rootPath = "E:\\CODA数据\\3 出租 -- 出租车轨迹样本\\01\\";
FileReader fileReader = new FileReader(rootPath + tableName + ".txt");
BufferedReader br = new BufferedReader(fileReader); // 建立一个对象,它把文件内容转成计算机能读懂的语言
String line;
//网友推荐更加简洁的写法
ArrayList<String> list = new ArrayList();
int count = 0;
int per = 500;
while ((line = br.readLine()) != null) {
// 一次读入一行数据
list.add("(" + addQuote(line) + ")");
count++;
if (count == per){
insertArray(list);
count=0;
}
}
if (!list.isEmpty()){
insertArray(list);
}
} catch (IOException e) {
e.printStackTrace();
}
}
private String addQuote(String line){
StringBuilder sb = new StringBuilder(line);
sb.deleteCharAt(18).deleteCharAt(18);
sb.insert(16, "'").insert(18,"'").insert(20,"'").insert(31, "'");
return sb.toString();
}
private void insertArray(ArrayList<String> list){
String sql = "";
for (String item : list){
sql = sql + item + ",";
}
// System.out.println(sql);
insert(sql.substring(0, sql.length()-1));
list.clear();
}
}
Table类
import java.util.LinkedHashMap;
import java.util.Map;
public class Table {
private LinkedHashMap<String, String> items = new LinkedHashMap<String, String>();
private String tableName;
Table(String name){
this.tableName = name;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public void addItem(String name, String restrain){
this.items.put(name, restrain);
}
public String makeCreateTableStatement(){
String sql="CREATE TABLE IF NOT EXISTS " + this.tableName + " (";
for (Map.Entry<String, String> entry: items.entrySet()){
sql += entry.getKey() + " " + entry.getValue() + ",";
}
sql = sql.substring(0, sql.length()-1);
return sql + ");";
}
public String makeInsertStatement(String line){
String sql = "Insert Into " + this.tableName + "(";
for (Map.Entry<String, String> entry: items.entrySet()){
if (entry.getKey().equals("id")) continue;
sql += entry.getKey() + ",";
}
sql = sql.substring(0, sql.length()-1) + ") Values " + line ;
return sql + ";";
}
}