Wednesday 1 October 2014

Execute Batch Of statements

You can execute a batch of statements in SQL. Java.sql.Statement interface provides 'addBatch(String sql)', 'executeBatch()', 'executeLargeBatch()' and 'clearBatch()' methods to support batch processing.

Method
Description
void addBatch(String sql)
Adds the given SQL command to the current list of commands for this Statement object.
void clearBatch()
Empties this Statement object's current list of SQL commands.
int[] executeBatch()
Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
long[] executeLargeBatch()
Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

/* Step 1: Import sql package */
import java.sql.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException{     
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
                
        stmt.addBatch("CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))");
        
        stmt.addBatch("INSERT INTO employee values(1, 'Krishna')");
        stmt.addBatch("INSERT INTO employee values(2, 'Arjun')");
        stmt.addBatch("INSERT INTO employee values(3, 'PTR')");
         
        stmt.executeBatch();
       
        String query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Output
Loading/Registering driver
Connecting to database
1 Krishna
2 Arjun
3 PTR
Dropping table employee



Prevoius                                                 Next                                                 Home

No comments:

Post a Comment