Java JDBC – How to Use PreparedStatement in Programming

In this chapter you will learn

1. What is PreparedStatement in Java?
2. How it is used for storing or updating data?
3. Programming Example

What is PreparedStatement?

The PreparedStatement object is derived from Statement class. Statement is used for sending and executing query in database but it is more convenient to use PreparedStatement to send and execute command in database. PreparedStatement uses parameterized query and keeps precompiled sql statement so it is faster than Statement class because it is compiled once and can be used multiple times.

PreparedStatement ps=con.prepareStatement("INSERT INTO ITEM VALUES(?,?)");

PreparedStatement is used to execute parameterized query. It uses ? parameter at the place of value and later store value in it. As we initialize PreparedStatement, it gets compiled and then can be used so many times directly. Once database engine compiles PreparedStatement, It doesn’t get compiled next time and executed directly. It makes it much efficient and faster than Statement class.

Method Description
executeQuery This method executes sql query in PreparedStatement and returns ResultSet.
updateQuery This method executes sql query in PreparedStatement and used for Only with Insert, Update or Delete command or sql statement that return nothing.
public void setInt(int paramIndex, int value) sets the integer value to the given parameter index.
public void setString(int paramIndex, String value) sets the String value to the given parameter index.
public void setFloat(int paramIndex, float value) sets the float value to the given parameter index.
public void setDouble(int paramIndex, double value) sets the double value to the given parameter index.

INSERT RECORD using PreparedStatement

package JavaStatements;
import java.sql.*;

public class PreparedStatement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	//Statement stmt = null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    
	    //Step 2 : Initialize PreparedStatement
	    ps= con.prepareStatement("INSERT INTO ITEM(PRODUCT,PRICE) VALUES(?,?)");
	    
	    //Step 3 : Set Value for ? Parameter    
	    //executing 1st query
	    ps.setString(1,"Java eBook");
	    ps.setString(2, "460");	    
	    ps.executeUpdate();
	    System.out.println("Java eBook Stored Successfully");
	    
	    //executing 2nd query
	    ps.setString(1, "JDBC eBook");
	    ps.setString(2, "830");
	    ps.executeUpdate();
	    System.out.println("JDBC eBook Stored Successfully");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


Java eBook Stored Successfully
JDBC eBook Stored Successfully
Closing the connection.


UPDATE RECORD using PreparedStatement

package JavaStatements;
import java.sql.*;

public class PreparedStatement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	//Statement stmt = null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    
	    //Step 2 : Initialize PreparedStatement
	    ps= con.prepareStatement("UPDATE ITEM SET PRODUCT = ? WHERE ID = ?");
	    
	    //Step 3 : Set Value for ? Parameter    
	    //executing 1st query
	    ps.setString(1,"MySQL eBook");
	    ps.setInt(2, 13);    
	    ps.executeUpdate();
	    System.out.println("MySQL eBook Updated Successfully");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


MySQL eBook Updated Successfully
Closing the connection.


DELETE RECORD using PreparedStatement

package JavaStatements;
import java.sql.*;

public class PreparedStatement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	//Statement stmt = null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    
	    //Step 2 : Initialize PreparedStatement
	    ps= con.prepareStatement("DELETE FROM ITEM WHERE ID = ?");
	    
	    //Step 3 : Set Value for ? Parameter   	    
	    ps.setInt(1, 13);    
	    ps.executeUpdate();
	    System.out.println("MySQL eBook Deleted Successfully");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


MySQL eBook Deleted Successfully
Closing the connection.


SELECT Record in ResultSet using PreparedStatement

package JavaStatements;
import java.sql.*;

public class PreparedStatement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	//Statement stmt = null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    
	    //Step 2 : Initialize PreparedStatement
	    ps= con.prepareStatement("SELECT * FROM ITEM");
	    ResultSet rset=ps.executeQuery();
	    
	    while(rset.next())
	    {
	    	System.out.println("ID : " + rset.getInt(1) + ", PRODUCT : " + rset.getString(2) + ", PRICE : " + rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


ID : 1, PRODUCT : MousePad, PRICE : 190
ID : 2, PRODUCT : Stationary, PRICE : 2870
ID : 3, PRODUCT : Books, PRICE : 765
ID : 4, PRODUCT : HardDisk, PRICE : 3887
ID : 5, PRODUCT : Ram, PRICE : 953
ID : 6, PRODUCT : Printer, PRICE : 8746
ID : 7, PRODUCT : Keyboard, PRICE : 646
ID : 8, PRODUCT : Mouse, PRICE : 947
ID : 9, PRODUCT : HDMI CABLE, PRICE : 850
ID : 11, PRODUCT : Charger, PRICE : 1800
ID : 12, PRODUCT : MainJava Logo, PRICE : 900
ID : 14, PRODUCT : JDBC eBook, PRICE : 830
Closing the connection.


Summary

In this tutorial you learned how to use PreparedStatement for Storing, Retrieving, Updating or Deleting row in MySQL table using JDBC. This chapter includes all the programming examples that demonstrate all the theory well.


Leave a Reply

Your email address will not be published. Required fields are marked *