JDBC Statement interface example

The java.sql.Statement interface object represent static SQL statement. It is base object type of SQL statement in java. Only one ResultSet object is associated with one Statement object. The PreparedStatement interface is the derived from it.

To use Statement interface in source code, first we need to create object of Statement by calling createStatement() method from java.sql.Connection interface. The createStatement() is available in java.sql.Connection interface. It does not take any argument.

Syntax :- Statement stmt=con.createStatement()

After creating object of Statement type object, we call executeUpdate() or executeQuery() method. It depends whether database is updating or getting result by SQL statement. If database is updating (Insert, Update, Delete or Create) call int executeUpdate() and record is fetching call ResultSet executeQuery().

Syntax :- int executeUpdate("SQL Statement") esultSet executeQuery("SQL Statement")
The executeUpdate() return number of rows effetcted by SQL statement and executeQuery() return the records from table based on SQL statement supplied.
 

Here is the example of Statement interface for Insert, Update, Delete and Query record from database

I have used validations also on necessary fields like, custId and custName.

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

SQL Statement for creating table in MySQL database Server -

use test;
create table Emp
(
code varchar(10) primary key,
name varchar(40) null,
city varchar(20),
salary int
);
insert into Emp values('a001','Ram Kumar','Noida',10000);
Download

Here is Java Source code

import java.sql.*;
import java.io.*;
public class StatementDemo1 {
Connection con;
Statement stmt;
public StatementDemo1() 
{
   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 = "", sql = "insert into Customer(custId,custName)
values('" + custId + "','" + name + "')";
if (address.trim().length() != 0) {
sql = "insert into Customer(custId,custName,custAddress)
values('" + custId + "','" + name + "','" + address + "')";
}
if (contact.trim().length() != 0) {
 sql = "insert into Customer(custId,custName,custAddress,custContact)
values('" + custId + "','" + name + "','" + address + "','" + contact + "')";
}
try {
    stmt = con.createStatement();
    int i = stmt.executeUpdate(sql);
    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 = "", sql = "";
if (address.trim().length() != 0) {
    sql = "update Customer set custAddress='" + address + "' 
where custId='" + custid + "'";
}
if (contact.trim().length() != 0) {
    sql = "update Customer set custcontact='" + contact + "' 
where custId='" + custid + "'";
}
if ((contact.trim().length() != 0&& (address.trim().length() != 0)) {
    sql = "update Customer set custAddress='" + address + "',
custcontact='" + contact + "' where custId='" + custid + "'";
}
if (sql.trim().length() == 0) {
    status = "Please provide new values";
else {
    try {
        stmt = con.createStatement();
        int i = stmt.executeUpdate(sql);
        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 {
    stmt = con.createStatement();
    ResultSet res = stmt.executeQuery(sql);
    while (res.next()) {
        System.out.print(res.getString(1));
        System.out.print("\t" + res.getString(2));
        System.out.print("\t" + res.getString(3));
        System.out.println("\t" + res.getString(4));
    }
catch (SQLException e) {
    e.printStackTrace();
}
}

public String deleteCustomer(String custId) {
String status = "";
String sql = "delete from Customer where custid='" + custId + "'";
try {
    stmt = con.createStatement();
    int i = stmt.executeUpdate(sql);
    if (i != 0) {
        status = "Customer details deleted";
    else {
        status = "Customer details not deleted";
    }
catch (Exception e) {
    e.printStackTrace();
}
return status;
}

public void menuDisplay() {
String custId = "", custName = "", custAddress = "", custContact = "";
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{
         // Customer Id can be left blank
         do {
         System.out.println("Enter Customer Id [ It can not be left blank ]");
         custId = br.readLine();
          while (custId.trim().length() == 0);

         // Customer name can be left blank
          do {
            System.out.println("Enter Customer Name");
            custName = br.readLine();
             while (custName.trim().length() == 0);

         System.out.println("Enter  Customer Address or Enter ]");
         custAddress = br.readLine()
         System.out.println("Enter Customer Contact No. or Enter ]");
         custContact = br.readLine();
         System.out.println(addCustomer(custId, custName, custAddress, custContact));
         break;
            }
            case 2{
           System.out.println("Customer address and contact no.");
                do {
                    System.out.println("Enter Customer Id");
                    custId = br.readLine();
                while (custId.trim().length() == 0);

                System.out.println("Enter New Address or Enter ]");
                custAddress = br.readLine();
                System.out.println("Enter New Contact No. or Enter ]");
                custContact = br.readLine();
                System.out.println(editCustomer(custId, custAddress, custContact));
                break;
            }
            case 3{
                do {
                    System.out.println("Enter Customer Id to delete");
                    custId = br.readLine();
                while (custId.trim().length() == 0);
                System.out.println(deleteCustomer(custId));
                break;
            }
            case 4{
                System.out.println("Enter Customer Id or Enter]");
                custId = br.readLine();
                searchCustomer(custId);
                break;
            }
            case 5{
                System.exit(0);
            }
            default:
                break;
        }
    }
catch (Exception e) {
    e.printStackTrace();
}
}

public static void main(String[] args) {
StatementDemo1 obj = new StatementDemo1();
obj.menuDisplay();
}
}
Download Source Code

Previous Next

Comment by : Viris October 3rd, 2015
Comment : top 20 party dresses for the seoasn I have tried to put this idea into a working format before, but you seem to have done it in a very professional manner and made it easy to understand.

Comment by : mateus November 20th, 2013
Comment : not helpful


Write your Comment

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