Types of statements

There are 3 types of Statements, as given below:

Statement:

• It can be used for general-purpose access to the database. 
• It is useful when you are using static SQL statements at runtime.
• Example-01:
• Batch update is nothing but executing a set of queries at a time. 
• Batch updates reduces the number of database calls. 
• In batch processing, batch should not contain a select query. 
• You can add queries by calling the addBatch() method and can execute the bunch of queries by calling the executeBatch() method. 
• When using batch updates with Statement object, you can use multiple types of queries that can be acceptable in the executeUpdate() method.

Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Edu","root","root");

con.setAutoCommit(true);

st = con.createStatement();

st.addBatch("update emp set age=99 where id=21");

st.addBatch("insert into emp values (51,"Anita",32)");

st.addBatch("update emp set name='Ram' where id=1");

int count[] = st.executeBatch();

for(int i=1;i<=count.length;i++){

System.out.println("Query "+i+" has effected "+count[i]+" times");

}

con.commit();

PreparedStatement:

• It can be used when you plan to use the same SQL statement many times.
• The PreparedStatement interface accepts input parameters at runtime.
• It is used to handle the precompiled query. 
• If we want to execute the same query with different values for more than one time then precompiled queries will reduce the no of compilations. 
• Connection.prepareStatement() method can provide you PreparedStatment object. 
• This object provides setXXX() methods to provide query values. 
• Example - 01:

Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Edu","root","root");

String query = "select * from emp where id=?";
pst = con.prepareStatement(query);
pst.setInt(1, 11);
rs = pst.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("name")+" -- "+rs.getInt("age"));
}
rs.close();
pst.setInt(1, 21);
rs = pst.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("name")+" -- "+rs.getInt("age"));
}

• Example - 02:

Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Edu","root","root");

String query = "insert into emp(id, name, age) values(?,?,?)";
pst = con.prepareStatement(query);
pst.setInt(1, 11);
pst.setString(2,"Sarthak");
pst.setInt(3, 7);

//count will give you how many records got updated
int count = pst.executeUpdate();

//Run the same query with different values
pst.setInt(1, 21);
pst.setString(2,"CDPatel");
pst.setInt(3, 35);

count = pst.executeUpdate();
for(int i=1;i<=count.length;i++){
System.out.println("Query "+i+" has effected "+count[i]+" times");
}

• Example-03:

• Batch update is nothing but executing a set of queries at a time. 
• Batch updates reduces the number of database calls. 
• In batch processing, batch should not contain the select query. 
• When we are using PreparedStatement to execute batch update, we have to run the same query multiple times. 

Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Edu","root","root");

con.setAutoCommit(true);

pst = con.prepareStatement("update emp set age=? where empid=?");

pst.setInt(1, 300);

pst.setInt(2, 11);

pst.addBatch();

pst.setInt(1, 400);

pst.setInt(2, 21);

pst.addBatch();

int count[] = pst.executeBatch();

for(int i=1;i<=count.length;i++){

System.out.println("Query "+i+" has effected "+count[i]+" times");

}

con.commit();

CallableStatement:

• CallableStatement can be used when you want to access database stored procedures, functions and cursor, also.
• A CallableStatement object provides a way to call stored procedures using JDBC. Connection.prepareCall() method provides you CallableStatement object.

• Example-01:

Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Edu","root","root");

callSt = con.prepareCall("{call myprocedure(?,?,?)}");

callSt.setInt(1, 11);

callSt.setString(2,"Sarthak");

callSt.setInt(3, 7);

callSt.execute();

System.out.println("Executed stored procedure!!!");

• Example-02:
• Below example shows how to call stored procedure with out parameters. 
• You can register output parameter data types by using the registerOutParameter() method.

Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Edu","root","root");

callSt = con.prepareCall("{call myprocedure(?,?,?)}");

callSt.setInt(1,11);

callSt.setString(2,"Sarthak");

//below method used to register data type of the out parameter

callSt.registerOutParameter(3, Types.DOUBLE);

callSt.execute();

Double output = callSt.getDouble(3);

System.out.println("The output returned from stored procedure: "+output);

• Example-03:
• We can do batch execution with CallableStatement. 
• Below example shows how to do batch execution using addBatch() method and executeBatch() method.

Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Edu","root","root");

callSt = con.prepareCall("{call myprocedure(?,?,?)}");

callSt.setInt(1, 11);

callSt.setString(2,"Sarthak");

callSt.setInt(3, 7);

callSt.addBatch();

callSt.setInt(1, 21);

callSt.setString(2,"CDPatel");

callSt.setInt(3, 35);

callSt.addBatch();

int[] updateCounts = callSt.executeBatch();

• Example-04
• A function is a named PL/SQL block which is similar to a procedure. 
• The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value. 
• The below example shows how to execute a SQL function using CallableStatement. 

Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Edu","root","root");

callSt = con.prepareCall("{?=call myfunction(?,?)}");

callSt.setInt(1,11);

callSt.setString(2,"Sarthak");

//below method used to register data type of the out parameter

callSt.registerOutParameter(3, Types.DOUBLE);

callSt.execute();

Double output = callSt.getDouble(3);

System.out.println("The output returned from sql function: "+output);

Difference between Statement, Prepared and Callable Statement: