×
☰ See All Chapters

JDBC PreparedStatement

A prepared statement is an SQL statement that contains parameters, and the java.sql.PreparedStatement interface is used to represent a prepared SQL statement. You can create PreparedStatement as below:

PreparedStatement pst = con.prepareStatment(SQL);

Before a prepared statement can be executed, each parameter needs to be assigned using one of the set methods in the PreparedStatement interface. A question mark is used to denote a parameter. For example, the following prepared statement inserts a new row in a table called Employees:

INSERT INTO Employees VALUES (?, ?, ?, ?)

This prepared statement contains four parameters. When the Prepared- Statement object is created using the Connection object, this statement is sent to the database and precompiled, allowing the database to execute the statement at a faster rate.

Prepared statements are preferred over simple statements for two good reasons:

  • Prepared statements execute faster because they are precompiled. 

  • Prepared statements are easier to code because you do not have to worry about things like single quotes around text or missing commas.  

Using a prepared statement involves the following steps:

  • Create a PreparedStatement object using one of the prepareStatement() methods of the connection. The following code demonstrates preparing a statement using a connection: 

PreparedStatement pstmt = connection.prepareStatement( “INSERT INTO Employees VALUES (?, ?, ?, ?)”);

Each question mark in a prepared statement denotes a parameter. The order in which the parameters appear determines their index, with the first parameter being index 1, the second parameter index 2, and so on. This is important when you go to set the values using the various set methods in the PreparedStatement interface.

  • Use the appropriate set methods of the PreparedStatement interface to set each of the parameters of the prepared statement. 

  • Invoke one of the execute() methods of the PreparedStatement interface to execute the statement.  

Method

Description

void addBatch(String sql)

Adds a set of parameters to this PreparedStatement object's batch of commands.

void clearParameters()

Clears the current parameter values immediately.

boolean execute(String sql)  

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

public int executeUpdate()

Executes the query. It is used for create, drop, insert, update, delete etc.

public ResultSet executeQuery()

Executes the select query. It returns an instance of ResultSet.

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.

DB script

   CREATE TABLE EMP(EMP_ID INT(5), NAME VARCHAR(30), SALARY INT(6));

   INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES (103,'Manu',3000);

   INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(104,'Tyagraj',4000);

   INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(105,'Likitha',5000);

   INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(106,'Advith',6000);

   COMMIT;

JdbcMysqlDemo.java

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public class JdbcMysqlDemo {

        public static void main(String[] args) {

 

                Connection con = null;

                PreparedStatement pst1 = null;

                PreparedStatement pst2 = null;

                PreparedStatement pst3 = null;

                ResultSet rs = null;

 

                try {

                        // Class.forName("com.mysql.jdbc.Driver"); //for mysql versions lesser than 8.0

                        Class.forName("com.mysql.cj.jdbc.Driver");// for mysql version 8.0

                        System.out.println("Driver is loaded");

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

                        System.out.println("got database connection");

 

                        pst1 = con.prepareStatement("INSERT INTO EMP (EMP_ID, NAME, SALARY) VALUES(?,?,?)");

 

                        // inserting a row

                        pst1.setInt(1, 107);

                        pst1.setString(2, "Sakshi");

                        pst1.setInt(3, 7000);

                        int i = pst1.executeUpdate();

                        System.out.println("Value of i is: " + i);

 

                        pst1.addBatch();

 

                        pst1.setInt(1, 108);

                        pst1.setString(2, "Manjunatha");

                        pst1.setInt(3, 8000);

                        pst1.executeUpdate();

                        pst1.addBatch();

                        pst1.setInt(1, 109);

                        pst1.setString(2, "Sanjith");

                        pst1.setInt(3, 9000);

                        pst1.executeUpdate();

                        pst1.addBatch();

                        int a[] = pst1.executeBatch();

 

                        // updating a row

                        pst2 = con.prepareStatement("UPDATE EMP SET SALARY = ? WHERE NAME = ?");

 

                        pst2.setInt(1, 3333);

                        pst2.setString(2, "GANESH");

                        boolean b = pst2.execute();

                        System.out.println("Value of b is: " + b);

                        pst2.addBatch();

 

                        pst2.setInt(1, 5555);

                        pst2.setString(2, "Srinivas");

                        pst2.execute();

                        pst2.addBatch();

                        pst2.executeBatch();

 

                        // getting a scrollable resultset

                        pst3 = con.prepareStatement("SELECT EMP_ID, NAME, SALARY FROM EMP WHERE EMP_ID > ?");

                        pst3.setInt(1, 104);

                        rs = pst3.executeQuery();

 

                        while (rs.next()) {

                                System.out.print(rs.getInt("EMP_ID") + "\t");

                                System.out.print(rs.getString("NAME") + "\t");

                                System.out.println(rs.getString("SALARY"));

                        }

 

                } catch (ClassNotFoundException e) {

                        System.out.println("Exception caught: " + e);

                        e.printStackTrace();

                } catch (SQLException e) {

                        System.out.println("Exception caught: " + e);

                        e.printStackTrace();

                } finally {

                        try {

                                if (rs != null) {

                                        rs.close();

                                }

                                if (pst1 != null) {

                                        pst1.close();

                                }

                                if (pst2 != null) {

                                        pst2.close();

                                }

                                if (pst3 != null) {

                                        pst3.close();

                                }

                                if (con != null) {

                                        con.close();

                                }

                        } catch (SQLException e) {

                                e.printStackTrace();

                        }

 

                }

        }

}

Output

got database connection

Value of i is: 1

Value of b is: false

105        Likitha        5000

106        Advith        6000

107        Sakshi        7000

108        Manjunatha        8000

109        Sanjith        9000

107        Sakshi        7000

108        Manjunatha        8000

109        Sanjith        9000

 

All Chapters
Author