Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Create a Java Batch Insert Without Using POJO Code

DZone 's Guide to

How to Create a Java Batch Insert Without Using POJO Code

Learn how to create a Java batch insert without using POJO code.

· Java Zone ·
Free Resource

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 methodsetString().

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, COLUMN HEAD NAME should not be a value from the row either. 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.

Topics:
batch insert ,database ,java ,sql ,mysql ,rows ,string ,pojo ,pojo code

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}