如何在不使用POJO程式碼的情況下建立Java批處理插入 -DZone Java

banq發表於2021-01-15
POJOJava

POJO樣板程式碼通常用於Java程式的各種功能中：無論是使用comparator（comparable的介面）進行比較，還是諸如在“通用資料結構”中儲存大量資料（例如列表，集合，地圖等）之類的簡單操作。但是，POJO管理起來非常困難，並且隨著需求的不斷變化，需要進行更多更改。但是，在這種情況下，不使用POJO程式碼。讓我們看看下面的程式碼。
該程式透過執行以下步驟將ExcelSheet複製到SQL表。 
  1. 從提供位置的檔案中獲取網格資料。
  2. 從資料庫獲取連線。
  3. 該程式獲取列數並分配該長度的陣列。
  4. 拾取第一行並獲取列名稱，並將其儲存在陣列中。
  5. 檢查網格中矩陣的一致性，指出不相等的行，否則返回true標誌，指示行長度等於標題長度。
  6. 透過傳遞帶有列標題作為表屬性的DDL語句來建立表。
  7. 複製網格表中的所有行，並將它們傳遞給LinkedList。
  8. 將批處理大小初始化為100。在為每個獲得的陣列進行批處理插入迴圈期間，迭代其索引並將陣列載入到批處理容器中。一旦迴圈達到100，則執行批處理。繼續批次插入迴圈，直到LinkedList結束。 


public class DBFromExcel {
static FileInputStream excelFile = null;
static Workbook workbook = null;
static XSSFSheet datatypeSheet = null;
static Row row = null;
static PreparedStatement pr_stmt = null;
static Connection conn = null;
static String[] headers = null;
static String[] row_data = null;
static List<String[]> lst = null;
static String formattedDate = null;
public DBFromExcel() {
try {
excelFile = new FileInputStream(new File(<PATH TO EXCEL FILE>));
workbook = new XSSFWorkbook(excelFile);
Calendar cal = Calendar.getInstance();
Date date=cal.getTime();
DateFormat dateFormat = new SimpleDateFormat("YY");
formattedDate=dateFormat.format(date);
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/<table_name>",  "<db_user>", "<db_pass>");
} catch (IOException | ClassNotFoundException | SQLException e) { 
e.printStackTrace();

}
datatypeSheet = (XSSFSheet) workbook.getSheetAt(0);
row = ((org.apache.poi.ss.usermodel.Sheet) datatypeSheet).getRow(0);
StringBuilder strbld = new StringBuilder();
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
if (cell != null) {
strbld.append(cell.getStringCellValue() + ",");
}
}
headers = strbld.toString().substring(0, strbld.toString().length() - 1).split(",");
if (check_matrix_consistency(datatypeSheet) == true)
{
try {
pr_stmt = conn.prepareStatement(create_table(headers));
pr_stmt.execute();
} catch (SQLException e1) { 
e1.printStackTrace();
}
try {
System.out.println("INSERT INTO T_"+formattedDate+" (" + strbld.toString().substring(0, strbld.toString().length() - 1)+ ") VALUES ("+generate_containers(headers)+")");
pr_stmt = conn.prepareStatement("INSERT INTO T_"+formattedDate+" (" + strbld.toString().substring(0, strbld.toString().length() - 1)+ ") VALUES ("+generate_containers(headers)+");");
} catch (SQLException e) {
e.printStackTrace();
}
try {
getRows(datatypeSheet);
} catch (SQLException e) {
e.printStackTrace();
}
try {
insert_rows();
} catch (SQLException e) { 
e.printStackTrace();
}
} else {
System.out.println("Matrix config error");
}
}
private String create_table(String[] headers) {
StringBuffer strbuff = new StringBuffer();
strbuff.append("CREATE TABLE T_"+formattedDate+" ("+"\n");
int counter=0;
while(counter<headers.length)
{
strbuff.append(headers[counter]+" VARCHAR(50) NULL DEFAULT NULL,"+"\n");
counter++;
}
System.out.println(strbuff.substring(0,strbuff.toString().length()-2)+")COLLATE='utf8_general_ci' ENGINE=MyISAM;");
return strbuff.substring(0,strbuff.toString().length()-2)+")COLLATE='utf8_general_ci' ENGINE=MyISAM;";
}
private String generate_containers(String[] headers) {
StringBuffer strbuff = new StringBuffer();
int counter = 0;
while (counter <= headers.length-1) {
strbuff.append("?,");
counter++;
}
return strbuff.substring(0, strbuff.toString().length() - 1);
}
private static boolean check_matrix_consistency(XSSFSheet sheet) {
boolean flag = true;
for (int i = 0; i < sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (headers.length != sheet.getRow(1).getLastCellNum()) {
System.out.println("Error at row number" + i);
flag = false;
break;
} else {
if (headers.length != row.getLastCellNum()) {
System.out.println("Error at row number" + i);
flag = false;
continue;
}
}
}
return flag;
}

private static void insert_rows() throws SQLException {
int batch = 100;
for (int i = 0; i < lst.size(); i++) {
System.out.println(i);
String[] obtained_row = lst.get(i);
int counter = 0;
for (int j = 0; j < obtained_row.length; j++) {
counter++;
pr_stmt.setString(counter, obtained_row[j]);
}
pr_stmt.addBatch();
if (i % batch == 0 || i < lst.size()) {
pr_stmt.executeBatch();
}
}
}
private static void getRows(XSSFSheet sheet) throws SQLException {
lst = new LinkedList<String[]>();
for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
Row row = sheet.getRow(i);
int counter = 0;
row_data = new String[headers.length];
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (cell.getCellType() == CellType.STRING) {
setValue(counter, cell.getStringCellValue());
}
if (cell.getCellType() == CellType.NUMERIC) {
setValue(counter, cell.getNumericCellValue());
}
counter++;
}
lst.add(row_data);
}
}
public static void setValue(int position, String value) {
row_data[position] = value;
}
public static void setValue(int position, double value) {
row_data[position] = Double.toString(value);
}
public static void main(String args[]) {
new DBFromExcel();
}
}  

 

相關文章