JDBC Program Example Using PreparedStatement

In the previous JDBC tutorial, we had developed the JDBC program to insert, update, select, and display the records using the PreparedStatement object. Now in this tutorial, we will develop some more JDBC or Java program example using the PreparedStatement object.

We are using the Oracle database, but you can work with any database with required information like Jar file, URL syntax, username, password. In the Oracle database, we have an emp table under the Scott user. We will use this table.

JDBC PreparedStatement Example to Select Record

Program description:- Write a JDBC application that gives employee details based on the given department number.

Prepare the SQL query

SQL> SELECT * FROM emp;

It will display all the details of emp table.

SQL> SELECT empno, ename, job, sal, deptno FROM emp;

Above query will display only empno, ename, job, sal and deptno columns from the emp table.

SQL> SELECT empno, ename, job, sal, 
     deptno FROM emp 
     WHERE deptno IN (10, 20);

It displays empno, ename, job, sal, and deptno columns from the emp table, only for whose department number is 10 or 20.

SQL> SELECT empno, ename, job, sal,
     deptno FROM emp
     WHERE deptno IN (10, 20) 
     ORDER BY deptno;

It displays empno, ename, job, sal, and deptno columns from the emp table, only for whose department number is 10 or 20, in the order of deptno.

Now, our query is ready. Let us develop the application.

Develop the application

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class SelectTest {

   // SQL query
   private static final String SELECT_EMPLOYEE_QUERY = 
      "SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP "
      + " WHERE DEPTNO IN (?,?) ORDER BY DEPTNO";

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      int deptno1 = 0, deptno2 = 0;
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      boolean flag = false;

      try {
    	 // read input
    	 scan = new Scanner(System.in);
    	 if(scan != null) {
             System.out.print("Enter deptno1: ");
             deptno1 = scan.nextInt();
             System.out.print("Enter deptno2: ");
             deptno2 = scan.nextInt();
    	 }

         // establish the connection
         con = DriverManager.getConnection(
           "jdbc:oracle:thin:@localhost:1521:knowprogram",
           "scott", "tiger");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
         ps = con.prepareStatement(SELECT_EMPLOYEE_QUERY);

         // set input value to query parameter
         if(ps != null) {
            ps.setInt(1, deptno1);
            ps.setInt(2, deptno2);
         }

         // execute the query
         if(ps != null) 
         rs = ps.executeQuery();

         // process the result
         if(rs != null) {
            while(rs.next()) {
               flag = true;
               // SELECT EMPNO, ENAME, JOB
               // SAL, DEPTNO FROM EMP
               System.out.println(""
                     + rs.getInt("EMPNO") +" "
                     + rs.getString("ENAME") +" "
                     + rs.getString("JOB") +" "
                     + rs.getInt("SAL") +" "
                     + rs.getInt("DEPTNO"));
            }
        }

        if(flag)
        System.out.println("Records fetched & displayed");
        else
        System.out.println("Records not found");

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // end of try-catch block 

     finally {
        // close JDBC objects
        try {
           if(rs != null) rs.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(ps != null) ps.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(scan != null) scan.close();
        } catch(Exception e) {
           e.printStackTrace();
        }
     }

   } //end of main
} //end of class

Output:-

Enter deptno1: 10
Enter deptno2: 20
7934 MILLER CLERK 1300 10
7782 CLARK MANAGER 2450 10
………
7902 FORD ANALYST 2850 20
7876 ADAMS CLERK 1100 20
………
Records fetched & displayed

Enter deptno1: 10
Enter deptno2: 30
7782 CLARK MANAGER 2450 10
7839 KING PRESIDENT 5000 10
………
7698 BLAKE MANAGER 2850 30
7844 TURNER SALESMAN 1500 30
………
Records fetched & displayed

Enter deptno1: 40
Enter deptno2: 80
Records not found

JDBC program to display Nth highest salary

Program description:- Write a JDBC application example using PreparedStatement that gives employees details from emp table who has having nth highest sal.

From the emp table we have to fetch the employee having nth highest salary and then display those records.

Prepare the SQL query,

SQL> SELECT * FROM
    (SELECT ename, sal, dense_rank()
    over(ORDER BY sal desc) r 
    FROM emp) WHERE r=&n;

Enter value for n: 9
ENAME    SAL     R
-------- ------- ---
WARD     1250    9
MARTIN   1250    9

Develop the application,

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class SelectTest {

   // SQL query
   private static final String SELECT_EMPLOYEE_QUERY = 
      " SELECT * FROM " +
      " (SELECT ENAME, SAL, DENSE_RANK() OVER " +
      " (ORDER BY SAL DESC) R FROM EMP) WHERE R = ? ";

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      int n = 0;
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      boolean flag = false;

      try {

    	 // read input
    	 scan = new Scanner(System.in);
    	 if(scan != null) {
    	     System.out.print("Enter n value: ");
             n = scan.nextInt();
    	 }

         // establish the connection
         con = DriverManager.getConnection(
           "jdbc:oracle:thin:@localhost:1521:knowprogram",
           "scott", "tiger");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
         ps = con.prepareStatement(SELECT_EMPLOYEE_QUERY);

         // set input value to query parameter
         if(ps != null) {
            ps.setInt(1, n);
         }

         // execute the query
         if(ps != null) 
            rs = ps.executeQuery();

         // process the result
         if(rs != null) {
            while(rs.next()) {
               flag = true;
               // SELECT ENAME, SAL, DENSE_RANK()
               System.out.println(""
                    + rs.getString("ENAME") +" "
                    + rs.getInt("SAL") +" "
                    + rs.getInt("R"));
            }
        }

        if(flag)
        System.out.println("Records fetched & displayed");
        else
        System.out.println("Records not found");

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // end of try-catch block 

     finally {
        // close JDBC objects
        try {
           if(rs != null) rs.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(ps != null) ps.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(scan != null) scan.close();
        } catch(Exception e) {
           e.printStackTrace();
        }
    }

  } //end of main
} //end of class

Output:-

Enter n value: 1
KING 5000 1
Records fetched & displayed

Enter n value: 9
WARD 1250 9
MARTIN 1250 9
Records fetched & displayed

Enter n value: 20
Records not found

JDBC Program Example for Non-select SQL Queries using PreparedStatement

To send and execute non-select SQL query in database software use executeUpdate(-) or executeLargeUpdate(-). The executeLargeUpdate(-) method is given from java8 version.

  • public int executableUpdate(String query) throws SQLException
  • public long executableLargeUpdate(String query) throws SQLException

Program description:- Write a JDBC application to delete student details based on given student city/address using PreparedStatement object.

In Oracle database create table with some records,

SQL> select * from student;

 SNO  SNAME    SADD        AVG
----  -------  ----------- ----------
2000  Sophia   Manchester  89
3000  William  London      75
1000  Alex     Boise       80
1005  Ameila   Washington  59

In SQL plus tool, by default autocommit mode is off. So, whenever we delete any record from the table then it doesn’t deleted permanently. We can get those delete records by using “rollback” command. To delete them permanently we should use “commit” command.

But in the JDBC application, by default the auto-commit mode is on. So, whenever we delete records from JDBC application then it deletes permanently. We can’t get back those deleted records.

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

public class DeleteStudent {

   // SQL query
   private static final String DELETE_STUDENT_QUERY = 
              "DELETE FROM STUDENT WHERE SADD = ?";

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      String address = null;
      Connection con = null;
      PreparedStatement ps = null;
      int result = 0;

      try {
        // read input 
        scan = new Scanner(System.in);
        if(scan != null) {
           System.out.print("Enter address: ");
           address = scan.next();
        }

        // establish the connection
        con = DriverManager.getConnection(
           "jdbc:oracle:thin:@localhost:1521:knowprogram",
           "scott", "tiger");

        // compile SQL query and 
        // store it in PreparedStatemet object
        if(con != null)
        ps = con.prepareStatement(DELETE_STUDENT_QUERY);

        // set input values and execute query
        if(ps != null) {
           // set input values to query parameters
           ps.setString(1, address);
           // execute the query
           result = ps.executeUpdate();
        }

        // process the result
        if(result == 0)
        System.out.println("No records found for deletion");
        else
        System.out.println(result + " records deleted");

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // end of try-catch block

     finally {
        // close JDBC objects
        try {
           if(ps != null) ps.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(scan != null) scan.close();
        } catch(Exception e) {
           e.printStackTrace();
        }
     }

  } //end of main
} //end of class

Output:-

Enter address: London
1 records deleted

Enter address: London
No records found for deletion

In SQL plus tool by default auto-commit mode is off, and in JDBC application auto-commit mode is on. Whenever we delete records using the SQL* Plus tool and didn’t commit it, then the JDBC application can’t access those data, and the deadlock situation cames. JDBC application hangs until we didn’t either commit or rollback the data in the SQL plus tool, then only JDBC application takes the further decision.

To demonstrate it, delete sadd = Washington in SQL plus tool, and then without commit or rollback, execute the above JDBC application and from JDBC application try to delete sadd = Washington. What happens?

Deletion from SQL Plus tool,

SQL> delete from student 
   where sadd = ‘Washington’;
1 row deleted.

SQL> select * from student;

Now, executing JDBC program
Enter address: Washington

The JDBC program reached into the hang mode. Currently the records are in stream so, it can’t say that records not found, but it can’t delete because the records is not in the table.

Now, from SQL tool if we execute “commit” command then the jdbc program will display “No records found for deletation”. Or, from SQL tool if we execute “rollback” command then JDBC program will display “1 record deleted”.

Important Points From this Application

  • While dealing with the non-select query, there is no need of working with ResultSet object.
  • All operations in the JDBC app takes place on database software through JDBC driver.

Points on Auto-commit mode

  • The non-select SQL queries executed from SQL prompt can be committed or rolled back explicitly because they will be executed in database software by disabling auto-commit mode.
  • The non-select SQL queries that are executing in database software by coming from front-end like java application can’t be committed or rolled back because they will execute in database software by enabling auto-commit mode. The query execution result will be committed immediately so that we can’t rollback it later.
  • JDBC applications can’t read and manipulate uncommitted records. If such records are coming for manipulation then the application will hang up until the records become stable in the database.

Delete Records based on Start and End Range

Program description:- Write a JDBC application to delete the student details based on the given start, end range of the student number.

Prepare the SQL query,

SQL> DELETE FROM student WHERE
    sno>=100 AND sno<=1000;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class DeleteStudent {

   // SQL query
   private static final String DELETE_STUDENT_QUERY = 
         "DELETE FROM STUDENT WHERE" +
         " SNO >= ? AND SNO <= ?";

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      int startSno = 0, endSno = 0;
      Connection con = null;
      PreparedStatement ps = null;
      int result = 0;

      try {
         // read input 
         scan = new Scanner(System.in);
         if(scan != null) {
            System.out.print("Enter start sno: ");
            startSno = scan.nextInt();
            System.out.print("Enter end sno: ");
            endSno = scan.nextInt();
         }

         // establish the connection
         con = DriverManager.getConnection(
           "jdbc:oracle:thin:@localhost:1521:knowprogram",
           "scott", "tiger");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
         ps = con.prepareStatement(DELETE_STUDENT_QUERY);

         // set input values and execute query
         if(ps != null) {
           // set input values to query parameters
           ps.setInt(1, startSno);
           ps.setInt(2, endSno);
           // execute the query
           result = ps.executeUpdate();
         }

         // process the result
         if(result == 0)
         System.out.println("No records found"+ 
                            " for deletion");
         else
         System.out.println(result + " records deleted");

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // end of try-catch block 

     finally {
        // close JDBC objects
        try {
           if(ps != null) ps.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(scan != null) scan.close();
        } catch(Exception e) {
           e.printStackTrace();
        }
     }

   } //end of main
} //end of class

Output:-

Enter start sno: 1000
Enter end sno: 2000
3 records deleted

Enter start sno: 100
Enter end sno: 1000
No records found for deletion

Execute both select and non-select query using single method

Can we execute both the Select and non-select query by using a single method?
Yes, we can execute both select and non-select queries by using a single method execute(-).

Prototype/signature of execute(-) method:-
public boolean execute(String qry) throws Exception

  • This method returns true when a select query is executed and we should use getResultSet() to get the records given by select query.
  • It returns false when a non-select query is executed and we should use st.getUpdateCount(-) to get the numeric value that represents the number of records that are affected.
  • This method is not industry standard because it makes the programmer execute query separately or to gather results of the query separately so use executeQuery(-) for select queries and use executeUpdate(-) for non-select queries.
  • While executing the insert, create the table, drop table queries we will get an exception if the query execution is failed.
  • While dealing with delete, update queries we will get zero if the query execution is effected.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class DeleteStudent {

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      String query = null;
      Connection con = null;
      PreparedStatement ps = null;
      boolean flag = false; 
      ResultSet rs = null;
      int count = 0;

      try {
         // read input 
         scan = new Scanner(System.in);
         if(scan != null) {
            System.out.print("Enter query: ");
            query = scan.nextLine();
         }

         // establish the connection
         con = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:knowprogram",
            "scott", "tiger");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
            ps = con.prepareStatement(query);

         // execute the query
         if(ps != null)
            flag = ps.execute();

         // process the result
         if(flag) {
           System.out.println("Select query is executed");
           rs = ps.getResultSet();

           // process the resultSet
           while(rs.next()) {
               System.out.println(rs.getString(1)+" "
                                +rs.getString(2)+" "
                                +rs.getString(3) );
           }

         } else {
           System.out.println("The non-select query"
                                    " is executed");
           count = ps.getUpdateCount();
           System.out.println("Number of records that"+ 
                    " are affected = "+ count);
         }

      } catch(SQLException se) {
         se.printStackTrace();
      } catch(Exception e) {
         e.printStackTrace();
      } // end of try-catch block

      finally {
         // close JDBC objects
         try {
             if(rs != null) rs.close();
         } catch(SQLException se) {
             se.printStackTrace();
         }
         try {
            if(ps != null) ps.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
         try {
            if(con != null) con.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
         try {
            if(scan != null) scan.close();
         } catch(Exception e) {
            e.printStackTrace();
         }
      }

   } //end of main
} //end of class

Output:-

Enter query: select sno, sname, avg from student
Select query is executed
2000 Sophia 89
3000 William 75
1000 Alex 80
1005 Amelia 65

Enter query: update student set avg = 95 where sno = 2000
The non-select query is executed
Number of records that are affected = 1

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or you find anything incorrect? Let us know in the comments. Thank you!

Leave a Reply