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.
Java
xxxxxxxxxx
1
148
1
public class DBFromExcel {
2
static FileInputStream excelFile = null;
3
static Workbook workbook = null;
4
static XSSFSheet datatypeSheet = null;
5
static Row row = null;
6
static PreparedStatement pr_stmt = null;
7
static Connection conn = null;
8
static String[] headers = null;
9
static String[] row_data = null;
10
static List<String[]> lst = null;
11
static String formattedDate = null;
12
public DBFromExcel() {
13
try {
14
excelFile = new FileInputStream(new File(<PATH TO EXCEL FILE>));
15
workbook = new XSSFWorkbook(excelFile);
16
Calendar cal = Calendar.getInstance();
17
Date date=cal.getTime();
18
DateFormat dateFormat = new SimpleDateFormat("YY");
19
formattedDate=dateFormat.format(date);
20
Class.forName("com.mysql.jdbc.Driver");
21
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/<table_name>", "<db_user>", "<db_pass>");
22
} catch (IOException | ClassNotFoundException | SQLException e) {
23
e.printStackTrace();
24
25
}
26
datatypeSheet = (XSSFSheet) workbook.getSheetAt(0);
27
row = ((org.apache.poi.ss.usermodel.Sheet) datatypeSheet).getRow(0);
28
StringBuilder strbld = new StringBuilder();
29
for (int i = 0; i < row.getLastCellNum(); i++) {
30
Cell cell = row.getCell(i);
31
if (cell != null) {
32
strbld.append(cell.getStringCellValue() + ",");
33
}
34
}
35
headers = strbld.toString().substring(0, strbld.toString().length() - 1).split(",");
36
if (check_matrix_consistency(datatypeSheet) == true)
37
{
38
try {
39
pr_stmt = conn.prepareStatement(create_table(headers));
40
pr_stmt.execute();
41
} catch (SQLException e1) {
42
e1.printStackTrace();
43
}
44
try {
45
System.out.println("INSERT INTO T_"+formattedDate+" (" + strbld.toString().substring(0, strbld.toString().length() - 1)+ ") VALUES ("+generate_containers(headers)+")");
46
pr_stmt = conn.prepareStatement("INSERT INTO T_"+formattedDate+" (" + strbld.toString().substring(0, strbld.toString().length() - 1)+ ") VALUES ("+generate_containers(headers)+");");
47
} catch (SQLException e) {
48
e.printStackTrace();
49
}
50
try {
51
getRows(datatypeSheet);
52
} catch (SQLException e) {
53
e.printStackTrace();
54
}
55
try {
56
insert_rows();
57
} catch (SQLException e) {
58
e.printStackTrace();
59
}
60
} else {
61
System.out.println("Matrix config error");
62
}
63
}
64
private String create_table(String[] headers) {
65
StringBuffer strbuff = new StringBuffer();
66
strbuff.append("CREATE TABLE T_"+formattedDate+" ("+"\n");
67
int counter=0;
68
while(counter<headers.length)
69
{
70
strbuff.append(headers[counter]+" VARCHAR(50) NULL DEFAULT NULL,"+"\n");
71
counter++;
72
}
73
System.out.println(strbuff.substring(0,strbuff.toString().length()-2)+")COLLATE='utf8_general_ci' ENGINE=MyISAM;");
74
return strbuff.substring(0,strbuff.toString().length()-2)+")COLLATE='utf8_general_ci' ENGINE=MyISAM;";
75
}
76
private String generate_containers(String[] headers) {
77
StringBuffer strbuff = new StringBuffer();
78
int counter = 0;
79
while (counter <= headers.length-1) {
80
strbuff.append("?,");
81
counter++;
82
}
83
return strbuff.substring(0, strbuff.toString().length() - 1);
84
}
85
private static boolean check_matrix_consistency(XSSFSheet sheet) {
86
boolean flag = true;
87
for (int i = 0; i < sheet.getLastRowNum(); i++) {
88
Row row = sheet.getRow(i);
89
if (headers.length != sheet.getRow(1).getLastCellNum()) {
90
System.out.println("Error at row number" + i);
91
flag = false;
92
break;
93
} else {
94
if (headers.length != row.getLastCellNum()) {
95
System.out.println("Error at row number" + i);
96
flag = false;
97
continue;
98
}
99
}
100
}
101
return flag;
102
}
103
104
private static void insert_rows() throws SQLException {
105
int batch = 100;
106
for (int i = 0; i < lst.size(); i++) {
107
System.out.println(i);
108
String[] obtained_row = lst.get(i);
109
int counter = 0;
110
for (int j = 0; j < obtained_row.length; j++) {
111
counter++;
112
pr_stmt.setString(counter, obtained_row[j]);
113
}
114
pr_stmt.addBatch();
115
if (i % batch == 0 || i < lst.size()) {
116
pr_stmt.executeBatch();
117
}
118
}
119
}
120
private static void getRows(XSSFSheet sheet) throws SQLException {
121
lst = new LinkedList<String[]>();
122
for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
123
Row row = sheet.getRow(i);
124
int counter = 0;
125
row_data = new String[headers.length];
126
for (int j = 0; j < row.getLastCellNum(); j++) {
127
Cell cell = row.getCell(j);
128
if (cell.getCellType() == CellType.STRING) {
129
setValue(counter, cell.getStringCellValue());
130
}
131
if (cell.getCellType() == CellType.NUMERIC) {
132
setValue(counter, cell.getNumericCellValue());
133
}
134
counter++;
135
}
136
lst.add(row_data);
137
}
138
}
139
public static void setValue(int position, String value) {
140
row_data[position] = value;
141
}
142
public static void setValue(int position, double value) {
143
row_data[position] = Double.toString(value);
144
}
145
public static void main(String args[]) {
146
new DBFromExcel();
147
}
148
}
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.
Topics:
batch insert,
java,
sql,
mysql,
string,
pojo,
jdbc,
excel
Opinions expressed by DZone contributors are their own.
Comments