Batch JDBC con oggetto PrepareStatement
Ecco una tipica sequenza di passaggi per utilizzare l'elaborazione in batch con l'oggetto PrepareStatement:
Crea istruzioni SQL con segnaposto.
Crea un oggetto PrepareStatement utilizzando uno dei metodi prepareStatement () .
Imposta il commit automatico su false usando setAutoCommit () .
Aggiungi tutte le istruzioni SQL che desideri in batch utilizzando il metodo addBatch () sull'oggetto istruzione creato.
Eseguire tutte le istruzioni SQL utilizzando il metodo executeBatch () sull'oggetto istruzione creato.
Infine, eseguire il commit di tutte le modifiche utilizzando il metodo commit () .
Questo codice di esempio è stato scritto in base all'ambiente e alla configurazione del database eseguita nei capitoli precedenti.
Copia e incolla il seguente esempio in JDBCExample.java, compila ed esegui come segue:
// Import required packages
import java.sql.*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
// Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement stmt = null;
try{
// Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
// Create SQL statement
String SQL = "INSERT INTO Employees(id,first,last,age) " +
"VALUES(?, ?, ?, ?)";
// Create preparedStatemen
System.out.println("Creating statement...");
stmt = conn.prepareStatement(SQL);
// Set auto-commit to false
conn.setAutoCommit(false);
// First, let us select all the records and display them.
printRows( stmt );
// Set the variables
stmt.setInt( 1, 400 );
stmt.setString( 2, "Pappu" );
stmt.setString( 3, "Singh" );
stmt.setInt( 4, 33 );
// Add it to the batch
stmt.addBatch();
// Set the variables
stmt.setInt( 1, 401 );
stmt.setString( 2, "Pawan" );
stmt.setString( 3, "Singh" );
stmt.setInt( 4, 31 );
// Add it to the batch
stmt.addBatch();
// Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();
// Again, let us select all the records and display them.
printRows( stmt );
// Clean-up environment
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
public static void printRows(Statement stmt) throws SQLException{
System.out.println("Displaying available rows...");
// Let us select all the records and display them.
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
System.out.println();
rs.close();
}//end printRows()
}//end JDBCExample
Ora compiliamo l'esempio sopra come segue:
C:\>javac JDBCExample.java
C:\>
Quando corri JDBCExample, produce il seguente risultato:
C:\>java JDBCExample
Connecting to database...
Creating statement...
Displaying available rows...
ID: 95, Age: 20, First: Sima, Last: Chug
ID: 100, Age: 35, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
ID: 110, Age: 20, First: Sima, Last: Chug
ID: 200, Age: 30, First: Zia, Last: Ali
ID: 201, Age: 35, First: Raj, Last: Kumar
Displaying available rows...
ID: 95, Age: 20, First: Sima, Last: Chug
ID: 100, Age: 35, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
ID: 110, Age: 20, First: Sima, Last: Chug
ID: 200, Age: 30, First: Zia, Last: Ali
ID: 201, Age: 35, First: Raj, Last: Kumar
ID: 400, Age: 33, First: Pappu, Last: Singh
ID: 401, Age: 31, First: Pawan, Last: Singh
Goodbye!
C:\>