package org.pasif.utils;
import java.io.File;
import java.sql.PreparedStatement;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JLabel;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.XPP3Reader;
import org.pas.database.DbHelper;
public class ImportData {
private DbHelper dbHelper;
private Document document;
private XPP3Reader reader = new XPP3Reader();
private List<String> files = new ArrayList<String>(0);
private String importFolder;
private JLabel currentFile;
private Long t = 0L;
public ImportData(DbHelper dbHelper, String importFolder, JLabel currentFile) {
this.dbHelper = dbHelper;
this.importFolder = importFolder;
this.currentFile = currentFile;
}
public void parseIndex(String index) throws Exception {
t = System.currentTimeMillis();
document = reader.read(new File(importFolder + index));
for (Object obj : document.getRootElement().selectNodes("//file")) {
Element element = (Element) obj;
files.add(element.getText());
}
document.clearContent();
System.out.println(System.currentTimeMillis() - t);
String filename = null;
int times = files.size();
if (index.toLowerCase().trim().contains("cartable")) {
t = System.currentTimeMillis();
for (int i = 0; i <= times - 1; i++) {
filename = files.get(i);
currentFile.setText("正导入文件：(" + (i + 1) + "/" + times + ")" + filename);
importCarTable(filename);
}
System.out.println(System.currentTimeMillis() - t);
currentFile.setText("导入完毕，共耗时：" + (System.currentTimeMillis() - t) / 1000 + " s");
} else if (index.toLowerCase().trim().contains("trafficillgal")) {
t = System.currentTimeMillis();
for (int i = 0; i <= times - 1; i++) {
filename = files.get(i);
currentFile.setText("正导入文件：(" + (i + 1) + "/" + times + ")" + filename);
importTrafficIllgal(filename);
}
System.out.println(System.currentTimeMillis() - t);
currentFile.setText("导入完毕，共耗时：" + (System.currentTimeMillis() - t) / 1000 + " s");
} else if (index.toLowerCase().trim().contains("roadaccident")) {
t = System.currentTimeMillis();
for (int i = 0; i <= times - 1; i++) {
filename = files.get(i);
currentFile.setText("正导入文件：(" + (i + 1) + "/" + times + ")" + filename);
importRoadAccident(filename);
}
System.out.println(System.currentTimeMillis() - t);
currentFile.setText("导入完毕，共耗时：" + (System.currentTimeMillis() - t) / 1000 + " s");
}
}
private String getFirstValueOrNull(Element element, String xpath) {
List<?> list = element.selectNodes(xpath);
if (list != null && list.size() >= 1) {
Element resultElement = (Element) list.get(0);
if (resultElement != null) {
return resultElement.getTextTrim();
} else {
return null;
}
} else {
return null;
}
}
private void importCarTable(String filename) throws Exception {
java.sql.Date date = null;
PreparedStatement pstmt = dbHelper.getConnection().prepareStatement("INSERT /* APPEND */ INTO external_data (comefrom_id,department_id,operated_date,operator_id,primary_idx,secondary_idx,service_type_id)" + "values(?,?,?,?,?,?,?)");
document = reader.read(new File(importFolder + filename));
int i = 0;
List<?> cartables = document.getRootElement().selectNodes(
"//CarTable");
for (Object obj : cartables) {
Element carTable = (Element) obj;
date = new java.sql.Date(DateFormat.getDateInstance().parse(
getFirstValueOrNull(carTable, "./clrq")).getTime());
pstmt.setLong(1, 0);
pstmt.setLong(2, 1000000L);
pstmt.setDate(3, date);
pstmt.setLong(4, 1);
pstmt.setString(5, getFirstValueOrNull(carTable, "./lsh"));
pstmt.setDate(6, date);
pstmt.setLong(7, 6);
pstmt.addBatch();
if (i % 100 == 0 & i != 0) {
pstmt.executeBatch();
dbHelper.getConnection().commit();
pstmt.clearBatch();
}
i++;
}
pstmt.executeBatch();
dbHelper.getConnection().commit();
pstmt.clearBatch();
document.clearContent();
pstmt.close();
}
private void importRoadAccident(String filename) throws Exception {
java.sql.Date date = null;
PreparedStatement pstmt = dbHelper.getConnection().prepareStatement("INSERT /* APPEND */ INTO external_data (comefrom_id,department_id,operated_date,operator_id,primary_idx,secondary_idx,service_type_id)" + "values(?,?,?,?,?,?,?)");
document = reader.read(new File(importFolder + filename));
int i = 0;
List<?> cartables = document.getRootElement().selectNodes(
"//RoadAccident");
for (Object obj : cartables) {
Element carTable = (Element) obj;
date = new java.sql.Date(DateFormat.getDateInstance().parse(
getFirstValueOrNull(carTable, "./jlsj")).getTime());
pstmt.setLong(1, 0);
pstmt.setLong(2, 1000000L);
pstmt.setDate(3, date);
pstmt.setLong(4, 1);
pstmt.setString(5, getFirstValueOrNull(carTable, "./idx"));
pstmt.setDate(6, date);
pstmt.setLong(7, 6);
pstmt.addBatch();
if (i % 100 == 0 & i != 0) {
pstmt.executeBatch();
dbHelper.getConnection().commit();
pstmt.clearBatch();
}
i++;
}
pstmt.executeBatch();
dbHelper.getConnection().commit();
pstmt.clearBatch();
document.clearContent();
pstmt.close();
}
private void importTrafficIllgal(String filename) throws Exception {
java.sql.Date date = null;
PreparedStatement pstmt = dbHelper.getConnection().prepareStatement("INSERT /* APPEND */ INTO external_data (comefrom_id,department_id,operated_date,operator_id,primary_idx,secondary_idx,service_type_id) values(?,?,?,?,?,?,?)");
document = reader.read(new File(importFolder + filename));
int i = 0;
List<?> cartables = document.getRootElement().selectNodes(
"//TrafficIllgal");
for (Object obj : cartables) {
Element carTable = (Element) obj;
date = new java.sql.Date(DateFormat.getDateInstance().parse(
getFirstValueOrNull(carTable, "./zyzj")).getTime());
pstmt.setLong(1, 0);
pstmt.setLong(2, Long.parseLong(getFirstValueOrNull(carTable, "./departmentcode")));
pstmt.setDate(3, date);
pstmt.setLong(4, 1L);
pstmt.setString(5, getFirstValueOrNull(carTable, "./cxdh"));
pstmt.setDate(6, date);
pstmt.setLong(7, 6);
pstmt.addBatch();
if (i % 100 == 0 & i != 0) {
pstmt.executeBatch();
dbHelper.getConnection().commit();
pstmt.clearBatch();
}
i++;
}
pstmt.executeBatch();
dbHelper.getConnection().commit();
pstmt.clearBatch();
document.clearContent();
pstmt.close();
}
}