Prepared Statement Example

In this section we will discuss about the JDBC PreparedStatement.

PreparedStatement is an interface declared in the java.sql package. PreparedStatement is used to make the SQL statement execution efficient. In Java, when we use the JDBC to work with database java.sql provides two interfaces for executing the SQL queries, java.sql.Statement and java.sql.PreparedStatement. We can use either of the interfaces but, using these two interfaces are different in implementation. Statement is used to execute the static SQL statement whereas, PreparedStatement corresponds a precompiled SQL statement. When we use the PreparedStatement to execute the SQL statement, the statement is first compiled into the DBMS and then stored in PreparedStatement object. This object can then be used to execute this statement several times.

PreparedStatement has several setter methods for setting IN parameter values. Some of the setter methods are as follows :

• setByte(int parameterIndex, byte x)
• setBytes(int parameterIndex, byte[] x)
• setDate(int parameterIndex, Date x)
• setDouble(int parameterIndex, double x)
• setFloat(int parameterIndex, float x)
• setInt(int parameterIndex, int x)
• setLong(int parameterIndex, long x)
• setString(int parameterIndex, String x)
• setShort(int parameterIndex, short x)
• setArray(int parameterIndex, Array x)

PreparedStatement has several methods that helps developer to do additional work with the SQL statement these are as :

• execute()
• executeQuery()
• executeUpdate()
• getMetaData()
• addBatch()
• clearParameters()

Example

Here I am giving a simple example which will demonstrate you about the PreaparedStatement. In this example we will execute a SQL statement using PreparedStatement object. In this example we will use "INSERT INTO" SQL statement. An INSERT INTO statement is used to insert the value into the database table. In this example we will first create a database table using MySQL and then we will create a Java classes into which we will use the classes and interfaces of java.sql package for making connection with database and to insert value into the database table.

Database table

student

CREATE TABLE `student` (                
           `rollno` varchar(15) NOT NULL,        
           `name` varchar(20) DEFAULT NULL,      
           `class` varchar(5) DEFAULT NULL,      
           `section` varchar(5) DEFAULT NULL,    
           PRIMARY KEY (`rollno`)                
         ) ENGINE=InnoDB

PreparedStatementExample.java

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

public class PreparedStatementExample
{       
       public static void main(String args[])
         {
		   String driver="com.mysql.jdbc.Driver";
		   String url = "jdbc:mysql://localhost:3306/record";
           String user = "root";
           String password = "root";
           Connection con = null;
           PreparedStatement ps = null;
            try
               {
                  Class.forName(driver);
               }
             catch(ClassNotFoundException c)
               {
                  System.out.println(c);
               }
              try
               {
                  con = DriverManager.getConnection(url, user, password);
                  String sql = "INSERT INTO student(rollno, name, class, section) VALUES(?,?,?,?)";
				  ps = con.prepareStatement(sql);
                   ps.setString(1, "126");
                   ps.setString(2, "shashi");
                   ps.setString(3, "mca");
                   ps.setString(4, "8");
                  
                   int i = ps.executeUpdate();
                   if(i >0)
                    {
                       System.out.println("\nData inserted into table successfully");
                    }
                   else
                     System.out.println("Data can't be inserted");                 
               }
             catch(SQLException sqe)
               {
                  sqe.printStackTrace();
               }
         }
}

Output :

Database table before inserting a new record

When you will compile and execute the above Java code then the output will be as follows :

Database table after inserting a new record

Download Source Code