21-01-15
banq
POJO样板代码通常用于Java程序的各种功能中:无论是使用comparator(comparable的接口)进行比较,还是诸如在“通用数据结构”中存储大量数据(例如列表,集合,地图等)之类的简单操作。但是,POJO管理起来非常困难,并且随着需求的不断变化,需要进行更多更改。但是,在这种情况下,不使用POJO代码。让我们看看下面的代码。
该程序通过执行以下步骤将ExcelSheet复制到SQL表。
- 从提供位置的文件中获取网格数据。
- 从数据库获取连接。
- 该程序获取列数并分配该长度的数组。
- 拾取第一行并获取列名称,并将其存储在数组中。
- 检查网格中矩阵的一致性,指出不相等的行,否则返回true标志,指示行长度等于标题长度。
- 通过传递带有列标题作为表属性的DDL语句来创建表。
- 复制网格表中的所有行,并将它们传递给LinkedList。
- 将批处理大小初始化为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(); } } |
猜你喜欢