How to Use the JDBC Driver to Perform Batch Operations?
The JDBC provides the ability to submit multiple update SQL statements as a single unit of work, referred to as a batch update. The addBatch method is used to add a command. The clearBatch method is used to clear the list of commands. The executeBatch method is used to submit all commands for processing. Only Data Definition Language (DDL) and Data Manipulation Language (DML) statements that return a simple update count can be run as part of a batch.
The executeBatch method returns an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch. If one of the commands fails, a BatchUpdateException is thrown, and you should use the getUpdateCounts method of the BatchUpdateException class to retrieves the update count for each update statement in the batch update that executed successfully before this exception occurred. If a command fails, the driver continues processing the remaining commands. However, if a command has a syntax error, the statements in the batch fail.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
public class JDBCBatch {
private static final String DBURL =
"jdbc:mysql://localhost:3306/mydb?user=usr&password=sql"+
"&useUnicode=true&characterEncoding=UTF-8";
private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
static {
try {
Class.forName(DBDRIVER).newInstance();
} catch (Exception e){
e.printStackTrace();
}
}
private static Connection getConnection()
{
Connection connection = null;
try {
connection = DriverManager.getConnection(DBURL);
}
catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void createEmployees()
{
Connection con = getConnection();
Statement stmt =null;
String createString;
createString = "CREATE TABLE `mydb`.`employees` ("+
"`EmployeeID` int(10) unsigned NOT NULL default '0',"+
"`Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',"+
"`Office` varchar(10) collate utf8_unicode_ci NOT NULL default '',"+
"`CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,"+
"PRIMARY KEY (`EmployeeID`)"+
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
try {
stmt = con.createStatement();
stmt.executeUpdate(createString);
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
private static void dropEmployees()
{
Connection con = getConnection();
Statement stmt =null;
String createString;
createString = "DROP TABLE IF EXISTS `mydb`.`employees`;";
try {
stmt = con.createStatement();
stmt.executeUpdate(createString);
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
private static void insertEmployee()
{
Connection con = getConnection();
Statement stmt = null;
try {
stmt = con.createStatement();
stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) "
+ "VALUES(1001, 'David Walker', 'HQ101')");
stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) "
+ "VALUES(1002, 'Paul Walker', 'HQ202')");
stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) "
+ "VALUES(1003, 'Scott Warner', 'HQ201')");
int [] updateCounts = stmt.executeBatch();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void showEmployee()
{
Connection con = getConnection();
Statement stmt = null;
try {
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * from Employees");
while (rs.next()) {
System.out.println("ID : " + rs.getInt("EmployeeID") +
"; Name : " + rs.getString("Name")+
"; Office : " + rs.getString("Office"));
}
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void main(String[] args) {
createEmployees();
insertEmployee();
showEmployee();
dropEmployees();
}
}
0 comments:
Post a Comment