How to Create a Java Batch Insert Without Using POJO Code
Learn how to create a Java batch insert without using POJO code.
Join the DZone community and get the full member experience.
Join For FreePOJO 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.
xxxxxxxxxx
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
This program replicates the ExcelSheet to a SQL table via performing the following steps.
- Acquire grid data from the file in the provided location.
- Acquire connection from the database.
- The program obtains the number of columns and assigns an array of that length.
- Picks up the first Row and fetches the column names and stores them in the array.
- Check the consistency of the matrix in the grid, point out rows with inequalities else return true flag indicating row length is equal to header length.
- Create table via passing DDL statement with column headers as table attributes.
- Copy all rows from the grid table and pass them to the LinkedList.
- Initialize batch size to 100.During batch insert loop for every array obtained, iterate it's indices and load the array to the batch container. Once the loop reaches 100 execute the batch. Continue batch insert loop till end of LinkedList.
Please Note: Use ISAM table with MySQL as it's a flat file data container and has no relational integrity checks that execute for every row in InnoDB table.
InnoDB batch insert is slower as each row is checked via the database for relational integrity.
Opinions expressed by DZone contributors are their own.
Comments