How to Create a Java Batch Insert Without Using POJO Code

微信扫一扫,分享到朋友圈

How to Create a Java Batch Insert Without Using POJO Code

POJO boilerplate code is commonly used over a variety of functions in Java programs, be it comparison using the comparator
, comparable interfaces, or something as simple as storing large amounts of data in "Generic Data Structures," like Lists, Sets, Maps, etc.

Yes, it’s very difficult to manage it and requires more alterations as the requirements change over time. However, in this situation, no POJO code is used. Let’s look at the code below.

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();
}
}  

Explanation

The program first examines the excel sheet to check if the rows and columns form a perfect matrix or not, i.e. header row length, row length, and the number of columns should match the last row in the excel sheet with this information. This is required for avoiding an SQLException while inserting a row with data more or less than the specified index of the method setString()
.

Once checked, a DDL statement is issued to the database to create a table with fields equivalent to the number of columns in the first row. Thus, the first row should carry COLUMN HEAD NAME to prevent an SQLEXCEPTION while inserting a row, i.e. a field of a table cannot contain white spaces; also, logically, it should not be a value from the row as well. The SetValue
method prepares the row to be inserted into the list by passing the value for every iterated index.

Thus, a linked list with a size equivalent to the last row of excel carrying the data is allocated in the memory, and every row is read as an array after splitting values based on comma-separated values.

While inserting data from the excel sheet, for every row, the index is iterated forward only, from the first to last index. Therefore, a row gets inserted with full columns that are obtained from the array index. This program inserts, on average, about 30 rows per second.

If using MySQL, use MyISAM instead of InnoDB, as MySQL won’t slow down the batch insert process and process every row with relational constraint checks.

微信扫一扫,分享到朋友圈

How to Create a Java Batch Insert Without Using POJO Code

观察+ | “早到”的5G商用牌照,可能会让电视成为在线教育的中心

上一篇

The New Look of IT

下一篇

你也可能喜欢

How to Create a Java Batch Insert Without Using POJO Code

长按储存图像,分享给朋友