| |
JDBC Batch Processing
Batch processing add list of SQL statements in current Statement or preparedStatement object. Add batch help us to execute many SQL statements at single time, not every time. This is helpful in case of we are inserting or updating SQL query inside while or in loop having bulk records. Batch process is must faster and safer than normal executing method in SQL.
Suppose in class room of new session, we have to enter 50 student’s roll number and than assign each roll number to new students.
This form enter 50 record of students without name, we see how to use add batch work.
Example of Add batch with PreparedStatement
addBatch.jsp
<%@ page language="java" import="java.sql.*" errorPage="" %>
<%
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
String jdbcURL="jdbc:mysql://localhost:3306/jsp";
conn = DriverManager.getConnection(jdbcURL,"root", "");
PreparedStatement psInsertRecord=null;
String sqlInsertRecord=null;
int[] iNoRows=null;
%>
<html>
<head>
<title>Add Batch in JDBC</title>
</head>
<body>
<%
sqlInsertRecord="insert into class_room (sStudentName, sRollNo, sCourse) values(?,?,?)";
psInsertRecord=conn.prepareStatement(sqlInsertRecord);
for(int i=1;i<=50;i++)
{
psInsertRecord.setString(1,"New Student");
psInsertRecord.setString(2,"4"+i);
psInsertRecord.setString(3,"10th Class");
psInsertRecord.addBatch();
}
iNoRows=psInsertRecord.executeBatch();
%>
Row inserted into table is <b> <%=iNoRows.length%></b>
</body>
</html>
<%
try{
if(psInsertRecord!=null)
{
psInsertRecord.close();
}
if(conn!=null)
{
conn.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>
Simple Statement with add batch
addBatchStatement.jsp
<%@ page language="java" import="java.sql.*" errorPage="" %>
<%
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
String jdbcURL="jdbc:mysql://localhost:3306/jsp";
conn = DriverManager.getConnection(jdbcURL,"root", "");
Statement stInsertRecord=null;
String sqlInsertRecord=null;
int[] iNoRows=null;
%>
<html>
<head>
<title>Add Batch with Statement in JDBC</title>
</head>
<body>
<%
stInsertRecord=conn.createStatement();
for(int i=1;i<=50;i++)
{
sqlInsertRecord="insert into class_room (sStudentName, sRollNo, sCourse)" +
" values('New Student','4"+i+"','10th Class')";
stInsertRecord.addBatch(sqlInsertRecord);
}
iNoRows=stInsertRecord.executeBatch();
%>
Row inserted into table is <b> <%=iNoRows.length%></b>
</body>
</html>
<%
try{
if(stInsertRecord!=null)
{
stInsertRecord.close();
}
if(conn!=null)
{
conn.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>
|
|
