DB operations - Using prepared Statement

Prepared statement

• In prepared statement SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute statements multiple times.
• PreparedStatement is extension of Statement interface.

Advantages 

1. Improves performance of application - If same query is executed multiple times.
2. It is easy to insert and update SQL 99 datatype like CLOB, BLOB or OBJECT.
3. Provides programmatic approach for setting values.

-----------------------------------------------------------------------------------------------------------------------

package tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test_prepared {

Connection con=null;
PreparedStatement prstm=null;
ResultSet resultSet=null;


//Constructor that loads the driver manager and establishes connection with DB mentioned in URL
Test_prepared() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "password");
}


//This method is called to close connection to DB once all the transaction are processed.
void closeConnection() throws SQLException{
if(con!=null)
con.close();
}


//Code to insert records into DB using PreparedStatement
void insertData(int id,String name) throws SQLException{
try{
prstm=con.prepareStatement("insert into Student values(?,?)");
prstm.setInt(1, id);
prstm.setString(2, name);
int count=prstm.executeUpdate();
System.out.println("Number of rows affected due to insertion = "+count);
}
catch(SQLException e){
System.out.println(e.getMessage());
}
finally{
if(prstm!=null){
prstm.close();
}
}
}


//Code to delete records into DB using PreparedStatement
void deleteData(int id) throws SQLException{
try{
prstm=con.prepareStatement("delete from Student where id = ?");
prstm.setInt(1, id);
int count=prstm.executeUpdate();
System.out.println("Number of rows affected due to deletion = "+count);
}
catch(SQLException e){
System.out.println(e.getMessage());
}
finally{
if(prstm!=null){
prstm.close();
}
}
}


//Code to select records from DB using PreparedStatement
void selectData(int id) throws SQLException{
try{
prstm=con.prepareStatement("select * from Student where id = ?");
prstm.setInt(1, id);
ResultSet resultSet=prstm.executeQuery();

while(resultSet.next()){
System.out.print(resultSet.getInt(1)+"\t"+resultSet.getString(2));
}
}
catch(SQLException e){
System.out.println(e.getMessage());
}
finally{
if(prstm!=null){
prstm.close();
}
}
}


//Code to insert records into DB using PreparedStatement and using addBatch()
void batchUpdate(int [] ids,String [] names) throws SQLException{
try{
int i=0;
prstm=con.prepareStatement("insert into Student values(?,?)");
prstm.setInt(1, ids[i]);
prstm.setString(2, names[i]);
prstm.addBatch();
i++;

prstm.setInt(1, ids[i]);
prstm.setString(2, names[i]);
prstm.addBatch();
i++;

prstm.setInt(1, ids[i]);
prstm.setString(2, names[i]);
prstm.addBatch();

int [] count=prstm.executeBatch();

for(int j=0;j<count.length;j++){
System.out.println("Number of rows affected due to query "+j+" = "+count[j]);
}
}
catch(SQLException e){
System.out.println(e.getMessage());
}
finally{
if(prstm!=null){
prstm.close();
}
}
}


public static void main(String[] args) {
try {
Test_prepared tp=new Test_prepared();

  tp.insertData(4, "Roark");
tp.insertData(5, "John");
tp.insertData(7, "Galt");
tp.batchUpdate(new int[]{10,11,12}, new String[]{"Jack","Reacher","Tom"});

tp.deleteData(4);
tp.selectData(10);

tp.closeConnection();
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e.getMessage());
}
}
}