JDBC PreparedStatement interface example

The java.sql.PreparedStatement interface object represents a precompiled SQL statement.
This interface is used to efficiently execute SQL statements multiple times. We want to insert a record in a table by putting different values at runtime. It increases the performance of execution of SQL statements also. It is derived from java.sql.Statement interface.

To use PreparedStatement interface in source code, first we need to create object of PreparedStatement by calling prepareStatement() method. The prepareStatement() is available in java.sql.Connection interface.

The prepareStatement() method takes SQL statement in java format.
Syntax : prepareStatement("insert into emp values(?,?,?,?)").

The each ? represent the column index number in the table. If table EMP has code, name, city and salary columns, then 1st ? refer to code, 2nd ? refer to name, 3rd ? refer to city and 4th ? refer to salary.

You can insert partial record in the table.
Syntax : prepareStatement("insert into emp(code,name,salary) values(?,?,?)").
Here 1st ? refer to code,2nd ? refer to name, 3rd ? refer to salary. So ? represent the columns in java SQL statement. If column/s name is not specified with table name, it means we will use whole columns of the table and ? (question mark) index dependes on column index of table.

Afterthat we need to set the value to each ? by using the setter method from PreparedStatement interface as follows :
Syntax : setXXX(ColumnIndex,value)

The following table describe the SQL data type and respective setter methods

SQL datatype Method used
char/varchar/varchar2 setString()
int/number setInt()
float/number setFloat()
double/Float setDouble()
long/int setLong()
int/short setShort()
time setTime()
datetime/date setDate()
blob setBlob()


Insert, Update , Delete & Query a record using PreparedStatement interface

. Create a table customer in MySQL database server as follows -

Table - Customer
Column Name Data type
custId varchar(10) PK
custname varchar(40)
custaddress varchar(40)
custcontactno varchar(20)

Firstly establish connection with database     [ Type1 driver ]         [ Type4 driver ]

SQL Syntax for creating table -

use test;
drop table customer;
create table customer
(
custId varchar(10) primary key,
custName varchar(30),
custAddress varchar(50),
custContact varchar(20)
);
insert into customer values("a001",'Ram Kumar','Delhi','4445556666');
select * from customer;
Java Source Code
import java.sql.*;
import java.io.*;
public class PreparedStatementDemo1 {
    Connection con;
    PreparedStatement ps;
    public PreparedStatementDemo1() {
    try {
    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection("jdbc:mysql://localhost/
                test?user=root&password=root");
       catch (Exception e) {
         e.printStackTrace();
     }
    }
public String addCustomer(String custid, String name, String address,
        String contact) {
String status = "";
try {
    ps = con.prepareStatement("insert into Customer values(?,?,?,?)");
    ps.setString(1, custid);
    ps.setString(2, name);
    ps.setString(3, address);
    ps.setString(4, contact);
    int i = ps.executeUpdate();
    if (i != 0) {
        status = "Inserted";
    else {
        status = "Not Inserted";
    }

catch (Exception e) {
    e.printStackTrace();
}
return status;
}

public String editCustomer(String custid, String address, String contact) {
String status = "";
try {
    ps = con.prepareStatement("update Customer set custaddress=?,
            custcontactno=? where custid=?");
    ps.setString(1, address);
    ps.setString(2, contact);
    ps.setString(3, custid);
    int i = ps.executeUpdate();
    if (i != 0) {
        status = "Customer details updated successfully";
    else {
        status = "Customer details not updated ";
    }
catch (Exception e) {
    e.printStackTrace();
}
return status;
}

public void searchCustomer(String custid) {
String sql = "";
if (custid.trim().length() == 0) {
    sql = "select * from Customer";
else {
    sql = "select * from Customer where custid='" + custid + "'";
}
try {
    ps = con.prepareStatement(sql);
    ResultSet res = ps.executeQuery();
    while (res.next()) {
        System.out.print(res.getString(1));
        System.out.print(res.getString(2));
        System.out.print(res.getString(3));
        System.out.println(res.getString(4));
    }
catch (SQLException e) {
    e.printStackTrace();
}
}

public String deleteCustomer(String custId) {
String status = "";
try {
    ps = con.prepareStatement("delete from Customer where custid=?");
    ps.setString(1, custId);
    int i = ps.executeUpdate();
    if (i != 0) {
        status = "Customer details deleted";
    else {
        status = "Customer details not deleted";
    }
catch (Exception e) {
    e.printStackTrace();
}
return status;
}

public void menuDisplay() {
try {
    BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
    int ch = 0;
    while (true) {
        System.out.println("== Customer Management System ========= \n"
                "1. Add Customer \n "
                "2. Edit Customer details \n "
                "3. Delete Customer \n "
                "4. Display Customer's record \n "
                "5. Exit \n"
                "Enter Choice \n");
        String str1 = br.readLine().toString();
        ch = Integer.parseInt(str1);
        switch (ch) {
            case 1{
                System.out.println("Enter Customer Id");
                String custId = br.readLine();
                System.out.println("Enter Customer Name");
                String custName = br.readLine();
                System.out.println("Enter  Customer Address");
                String custAddress = br.readLine();
                System.out.println("Enter Customer Contact No.");
                String custContact = br.readLine();
                System.out.println(addCustomer(custId, custName, 
           custAddress, custContact));

                break;
            }
            case 2{
                System.out.println("Customer address and contact no and id");
                System.out.println("Enter Customer Id");
                String custId = br.readLine();
                System.out.println("Enter New Address");
                String custAddress = br.readLine();
                System.out.println("Enter New Contact No.");
                String custContact = br.readLine();
                System.out.println(editCustomer(custId, custAddress,custContact));
                break;
            }
            case 3{
                System.out.println("Enter Customer Id to delete from database");
                String custId = br.readLine();
                System.out.println(deleteCustomer(custId));
                break;
            }
            case 4{
                System.out.println("Enter Customer Code to display record");
                String custId = br.readLine();
                searchCustomer(custId);
                break;
            }
            case 5{
                System.exit(0);
            }
            default:
                break;
        }
    }
catch (Exception e) {
    e.printStackTrace();
}
}
public static void main(String[] args) {
PreparedStatementDemo1 obj = new PreparedStatementDemo1();
obj.menuDisplay();
}
}
Download Source Code

Previous Next

Comment by : Babar khanzada December 2nd, 2013
Comment : This will help me alot. thanks

Comment by : it's too good October 27th, 2013
Comment : it's good

Comment by : Ram October 19th, 2013
Comment : Create a java program that inserts numbers in table (numeral) starting with 100 and ending with 300 and also randomly inserts letters into table(letter). Use prepared Statement to insert.

Comment by : Ram October 19th, 2013
Comment : Create a java program that inserts numbers in table (numeral) starting with 100 and ending with 300 and also randomly inserts letters into table(letter).

Comment by : Shashikumar July 30th, 2013
Comment : This Tutorial with Example was too good it helps to all Thanks

Comment by : Shashikumar July 30th, 2013
Comment : this description with an example was too good Thanks

Comment by : sachin manohar kanar January 24th, 2013
Comment : it is very helpful tutorial


Write your Comment

Name :
Email Id : [ We will not publish your email id ]
Your Comment : [ Maximum 200 chars ]
Enter Security code