|
How to exceute a batch process from PreparedStatement |
|
|
PreparedStatement represents precompiled SQL statement that can be used
mulitple times by setting parameters to this object.
In order to successfully execute Batch in a database, it is advised to keep autocommit
flag OFF and commit the transaction after Batch is executed.
The example below executes Batch of SQL statement from PreparedStatement object.
Commented line executes Successfully, but the line with comment throws
BatchUpdateException and points out the error in a particular transaction.
import java.sql.*;
public class testPrepareBatch
{
public static void main(String[] args)
{
DB db = new DB();
Connection conn=db.dbConnect(
"jdbc:jtds:sqlserver://localhost:1433/tempdb","sa","");
db.performBatch(conn);
}
}
class DB
{
public DB() {}
public Connection dbConnect(String db_connect_string,
String db_userid, String db_password)
{
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection conn = DriverManager.getConnection(
db_connect_string, db_userid, db_password);
System.out.println("connected");
return conn;
}
catch (Exception e)
{
e.printStackTrace();
return null;
}
}
public void performBatch(Connection conn)
{
String query;
PreparedStatement stmt;
Statement st;
try
{
conn.setAutoCommit(false);
stmt = conn.prepareStatement(
"insert into customer (name,category,age) values (?,?,?)");
stmt.clearParameters();
stmt.setString(1,"name2");
stmt.setString(2,"cat2");
stmt.setInt(3,25);
stmt.addBatch();
stmt.clearParameters();
stmt.setString(1,"name3");
stmt.setString(2,"cat3");
stmt.setInt(3,25);
stmt.addBatch();
stmt.clearParameters();
stmt.setString(1,"name4");
stmt.setString(2,"cat2");
stmt.setInt(3,25);
stmt.addBatch();
stmt.clearParameters();
stmt.setString(1,"name4");
stmt.setString(2,"cat2");
//stmt.setInt(3,25);
//this is parameter with false input
stmt.setString(3,"null");
stmt.addBatch();
stmt.clearParameters();
stmt.setString(1,"name5");
stmt.setString(2,"cat5");
stmt.setInt(3,25);
stmt.addBatch();
stmt.clearParameters();
int [] updateCounts = stmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
stmt.close();
conn.close();
}
catch(BatchUpdateException b)
{
System.err.println("-----BatchUpdateException-----");
System.err.println("SQLState: " + b.getSQLState());
System.err.println("Message: " + b.getMessage());
System.err.println("Vendor: " + b.getErrorCode());
System.err.print("Update counts: ");
int [] updateCounts = b.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) {
System.err.print(updateCounts[i] + " ");
}
System.err.println("");
}
catch (Exception e)
{
e.printStackTrace();
}
}
};
|
Related Tips
|
Page 1 of 0 ( 0 comments )
Other API Tips 
You can share your information about this topic using the form below!
Please do not post your questions with this form! Thanks.