JDBC Statement Example Programs

In the previous post, we had developed a JDBC program to select records from a table using Statement Interface. Now in this post, we will develop the JDBC Statement example program for insert, update, and delete queries. Here we will use Oracle database software, but you can work with any database with a suitable JDBC driver, URL, username, and password. For more see:- How to establish connection with any database in JDBC using Eclipse IDE

Pre-requisite:- JDBC Statement Interface

The executeQuery(-) on Statement object, is used to send and execute the select query in database software from Java application. The return type of executeQuery(-) method is ResultSet. Similarly, the executeUpdate(-) method on Statement object is used to send and execute the non-select query in the database from Java application. The return type of executeUpdate(-) method is int.

While executing insert, create table, drop table queries we will get exception if the query execution is failed. But while dealing with delete, update queries we will get zero if the query execution is failed and no records are effected.

Basic details of Oracle JDBC driver to develop the program.

JDBC Driver Class Name:: oracle.jdbc.driver.OracleDriver
URL:: jdbc:oracle:thin:@<ip-address/host-name>:<port-no-of-oracle-db>:<sid>
Username:: <username_of_database>
Password:: <password_of_database>
  • On a local machine generally, Oracle database is located at the port no => 1521
  • If you are developing JDBC application on a local computer then use, host-name => localhost

Now let us begin with the insert query, but before developing any JDBC program first create a table in the database which will be used in all application given in this post.

Create a table in the Oracle database.

SQL>
create table student
(
  sno number(5) primary key,
  sname varchar2(15),
  sadd varchar2(15),
  avg float
);

JDBC Statement Example to Insert Record

Develop a JDBC program to insert record into the table using Statement object. Previously, we had created a table “student”. We will develop a JDBC program to take input for each records from end-user and insert those records into the “student” table.

The required SQL query to insert values into the table is,
SQL> INSERT INTO STUDENT VALUES (1000, 'Sophia', 'London', 70);

We will take input for records from the end-user, but it will not be in the format as required for the SQL query. For example, we take input for the student name and stored in a "sname" variable of String type. Note that while inserting character value to the table, it should be in a single quote. Therefore, to store this "sname" value to the query we have to convert this variable as sname = "\'" + sname + "\'"

// take input
int sno = scan.nextInt();
String sname = scan.next();
String sadd = scan.next();
float avg = scan.nextFloat();

// convert sname and sadd
// as required for SQL query
sname = "\'" + sname + "\'";
sadd = "\'" + sadd + "\'";
// prepare query to insert record
String query = "INSERT INTO STUDENT VALUES (" + sno + "," + sname + ","+ sadd + "," + avg + ")";

Or, we can directly convert the string at the time of query preparation as shown below,

// prepare SQL query
query = "INSERT INTO STUDENT VALUES (" + sno + ", " + "\'" + sname + "\' ," + "\'" + sadd + "\' ," + avg + ") ";

Here semicolon required to complete the Java statement, not for SQL query. This conversion is only required while working with simple Statement interface, when we are working with PreparedStatement interface then we don’t need to do this conversion.

package com.know.jdbc;

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

public class InsertTest {

   // values specific to each database
   private static final String URL = 
      "jdbc:oracle:thin:@localhost:1521:knowprogram";
   private static final String USERNAME = "scott";
   private static final String PASSWORD = "tiger"; 

   // main method
   public static void main(String[] args ) {

     // declare variables
     Scanner scan = null;
     int sno = 0;
     String sname = null, sadd = null;
     float avg = 0.0f;
     String query = null;
     Connection con = null;
     Statement st = null;
     int count = 0;

     try {
        // create Scanner class object
        scan = new Scanner(System.in);

        // read input 
        if(scan != null) {
           System.out.print("Enter student number: ");
           sno = scan.nextInt();
           System.out.print("Enter student name: ");
           sname = scan.next();
           System.out.print("Enter student address: ");
           sadd = scan.next();
           System.out.print("Enter student avg: ");
           avg = scan.nextFloat();
        }

        // prepare SQL query
        query = "INSERT INTO STUDENT VALUES ("
                + sno + ", " + "\'" + sname +"\' ,"
                +"\'"+ sadd + "\' ," + avg + ") ";

        // establish the connection
        con = DriverManager.getConnection(
                    URL, USERNAME, PASSWORD);

        // create JDBC statement object
        if(con != null) {
           st = con.createStatement();
        }

        // execute the SQL query
        if(st != null) {
           count = st.executeUpdate(query);
        }

        // display result
        System.out.println(count + " record inserted.");

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

     finally {
        // close JDBC objects
        try {
           if(st != null) st.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();
        }
     } // finally
   } //main()
} //class

Output:-

Enter student number: 1000
Enter student name: Olivia
Enter student address: Chicago
Enter student avg: 69
1 record inserted.

Now, to verify that the records are inserted or not, open the SQL plus tool or SQL developer and display the table. You can see that records are inserted into the table.

JDBC Statement Example to Update Record

Task:- Write a JDBC program to update record in a table using Statement object.

Previously, we have created a table “student” and then written a JDBC program to insert records. Now, we will develop a JDBC program to update records in the “student” table using the Statment object.

Required SQL query to update a record,
SQL> UPDATE STUDENT SET SNAME = 'name', AVG = 70 WHERE SNO = 1000;

package com.know.jdbc;

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

public class InsertTest {

   // values specific to each database
   private static final String URL = 
      "jdbc:oracle:thin:@localhost:1521:knowprogram";
   private static final String USERNAME = "scott";
   private static final String PASSWORD = "tiger"; 

   // main method
   public static void main(String[] args ) {

     // declare variables
     Scanner scan = null;
     int sno = 0;
     String sname = null;
     float avg = 0.0f;
     String query = null;
     Connection con = null;
     Statement st = null;
     int count = 0;

     try {
        // create Scanner class object
        scan = new Scanner(System.in);

        // read input 
        if(scan != null) {
           System.out.print("Enter student number: ");
           sno = scan.nextInt();
           System.out.print("Enter student name to update: ");
           sname = scan.next();
           System.out.print("Enter student avg to update: ");
           avg = scan.nextFloat();
        }

        // prepare SQL query
        query = "UPDATE STUDENT SET SNAME = "
                + "\'" + sname + "\', AVG = " 
                + avg + "WHERE SNO = " + sno;

        // establish the connection
        con = DriverManager.getConnection(
                    URL, USERNAME, PASSWORD);

        // create JDBC statement object
        if(con != null) {
           st = con.createStatement();
        }

        // execute the SQL query
        if(st != null) {
           count = st.executeUpdate(query);
        }

        // display result
        if(count != 0) 
           System.out.println(count + " record updated.");
        else
           System.out.println("Record updation failed");

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

     finally {
        // close JDBC objects
        try {
           if(st != null) st.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();
        }
     } // finally

   } // main()
} // class

Output:-

Enter student number: 1000
Enter student name to update: Emma
Enter student avg to update: 75
1 record updated.

Enter student number: 10
Enter student name to update: Olivia
Enter student avg to update: 88
Record updation failed

There student number 10 doesn’t exist in the table so, the record is not inserted. Open SQL plus tool or SQL Developer and verify that records are updated are not.


JDBC Statement Example to Delete Record

Task:- Write a JDBC program using Statement object to delete record from the table. Take one column value as input from the end-user and delete that row.

Before writing the program, first display the table. SQL> SELECT * FROM STUDENT;

The required query for this program is, SQL> DELETE FROM STUDENT WHERE SNO = 1000;

package com.know.jdbc;

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

public class DeleteTest {

   // values specific to each database
   private static final String URL = 
      "jdbc:oracle:thin:@localhost:1521:knowprogram";
   private static final String USERNAME = "scott";
   private static final String PASSWORD = "tiger"; 

   // main method
   public static void main(String[] args ) {

     // declare variables
     Scanner scan = null;
     int sno = 0;
     String query = null;
     Connection con = null;
     Statement st = null;
     int count = 0;

     try {
        // create Scanner class object
        scan = new Scanner(System.in);

        // read input 
        if(scan != null) {
           System.out.print("Enter student number: ");
           sno = scan.nextInt();
        }

        // prepare SQL query
        query = "DELETE FROM STUDENT WHERE SNO = " + sno;

        // establish the connection
        con = DriverManager.getConnection(
                    URL, USERNAME, PASSWORD);

        // create JDBC statement object
        if(con != null) {
           st = con.createStatement();
        }

        // execute the SQL query
        if(st != null) {
           count = st.executeUpdate(query);
        }

        // display result
        if(count != 0) 
           System.out.println(count + " record deleted.");
        else
           System.out.println("Record deletion failed");

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

     finally {
        // close JDBC objects
        try {
           if(st != null) st.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();
        }
     } // finally
   } //main()
} //class

Output:-

Enter student number: 1000
1 record deleted.

Enter student number: 1
Record deletion failed

Since sno = 1 is not available in the student table that’s why record deletion failed. In these three JDBC programs, we can observe that we don’t use the Resultset object. The ResultSet object is required only for Select queries i.e. DRL queries. Select queries return multiple results, so we need ResultSet object to fetch them but Non-select queries always return an integer value.

Examples on Select Queries

While establishing JDBC connection to the database we had written a simple JDBC program to select records from the table and display it to the console, for this you can refer here Oracle, MySQL, Using Eclipse. Once again In the JDBC Statement interface we developed a JDBC program to select the record but this time it was condition based and we used “WHERE” clause in the SQL query. Those two JDBC program were the great example of using Statement object while working with select queries. Now, we will develop some more JDBC program on select query using Statement object.

Count of Employees

Task:- Write a JDBC application to get the count of employees from the emp table.

The emp is a pre-defined table in Oracle database under the scott user. Required query is, SQL> SELECT COUNT(*) FROM EMP;

package com.know.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CountEMPTest {

   // values specific to each database
   private static final String URL = 
      "jdbc:oracle:thin:@localhost:1521:knowprogram";
   private static final String USERNAME = "scott";
   private static final String PASSWORD = "tiger"; 

   // main method
   public static void main(String[] args ) {

     // declare variables
     String query = null;
     Connection con = null;
     Statement st = null;
     ResultSet rs = null;
     int count = 0;

     try {

        // prepare SQL query
        query = "SELECT COUNT(*) FROM EMP";

        // establish the connection
        con = DriverManager.getConnection(
                    URL, USERNAME, PASSWORD);

        // create JDBC statement object
        if(con != null) {
           st = con.createStatement();
        }

        // execute the SQL query
        if(st != null) {
           rs = st.executeQuery(query);
        }
        
        // process the resultset
        if(rs != null) {
           rs.next();
           count = rs.getInt("COUNT(*)"); 
           // or, getInt(1)
        }
        
        // display result
       System.out.println("Count of Employees"+
                " in emp table: "+ count );

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

     finally {
        // close JDBC objects
        try {
           if(rs != null) rs.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(st != null) st.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
     } // finally
   } //main()
} //class

Output:-

Count of Employees in emp table: 14

Employee Details Having Highest Salary

Task:- Write a JDBC application to get the Employee details whose has having highest salary.

Query to display all details of the emp table, SQL> SELECT * FROM EMP;

The SQL Query to display only empno, ename, job, sal information from emp table. SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP;

Query to display the max sal from emp table, SQL> SELECT MAX(SAL) FROM EMP;

The final query to display the employee details whose have maximum sal.

SQL> SELECT EMPNO, ENAME, JOB, SAL
FROM EMP WHERE SAL =
(SELECT MAX(SAL) FROM EMP);

package com.know.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CountEMPTest {

   // values specific to each database
   private static final String URL = 
      "jdbc:oracle:thin:@localhost:1521:knowprogram";
   private static final String USERNAME = "scott";
   private static final String PASSWORD = "tiger"; 

   // main method
   public static void main(String[] args ) {

     // declare variables
     String query = null;
     Connection con = null;
     Statement st = null;
     ResultSet rs = null;

     try {

        // prepare SQL query
        query = "SELECT EMPNO, ENAME, JOB, SAL "+
                " FROM EMP WHERE SAL = "+
                " (SELECT MAX(SAL) FROM EMP)";

        // establish the connection
        con = DriverManager.getConnection(
                    URL, USERNAME, PASSWORD);

        // create JDBC statement object
        if(con != null) {
           st = con.createStatement();
        }

        // execute the SQL query
        if(st != null) {
           rs = st.executeQuery(query);
        }
        
        // process the resultset
        if(rs != null) {
           rs.next();
           System.out.println( 
                    rs.getInt("EMPNO") + " " +
                    rs.getString("ENAME") + " " +
                    rs.getString("JOB") + " " +
                    rs.getFloat("SAL") );
        }

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

     finally {
        // close JDBC objects
        try {
           if(rs != null) rs.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(st != null) st.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
     } // finally
   } //main()
} //class

Output:-

7839 KING PRESIDENT 5000

In this application, the query can give 1 or more than one results (when more than one emp have the same max sal). So, to process the resultset, for more than one result we should use a while loop but for one result we should use if block. There is no chance that the record is not found. So, the flag is also not required.

Employee Details having Nth Highest Salary

Task:- Write a JDBC application that gives employee details who is having given nth highest salary?

Required SQL Query,

SQL> select * from(
 select ename, sal, dense_rank()
 over(order by sal desc)r from emp)
 where r = &n;

Employees having 2nd highest salary,

SQL> /
Enter value for n: 2
old   4: WHERE R = &N
new   4: WHERE R = 2
ENAME             SAL          R
---------- ---------- ----------
SCOTT            3000          2
FORD             3000          2

Employees having 5th highest salary,

SQL> /
Enter value for n: 5
old   4: WHERE R = &N
new   4: WHERE R = 5

ENAME             SAL          R
---------- ---------- ----------
CLARK            2450          5

There can be only one employee having nth highest salary or there can be multiple employee having nth highest salary so write program accordingly.

package com.know.jdbc;

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

public class CountEMPTest {

   // values specific to each database
   private static final String URL = 
      "jdbc:oracle:thin:@localhost:1521:knowprogram";
   private static final String USERNAME = "scott";
   private static final String PASSWORD = "tiger"; 

   // main method
   public static void main(String[] args ) {

     // declare variables
     Scanner scan = null;
     int n = 0;
     String query = null;
     Connection con = null;
     Statement st = null;
     ResultSet rs = null;

     try {
        // create Scanner class object
        scan = new Scanner(System.in);

        // take input
        if(scan != null) {
           System.out.print("Enter N value: ");
           n = scan.nextInt();
        }

        // prepare SQL query
        query = "SELECT * FROM ( " +
                "SELECT ENAME, SAL, DENSE_RANK()"+
                "OVER(ORDER BY SAL DESC) R FROM EMP)"+
                "WHERE R = " + n;

        // establish the connection
        con = DriverManager.getConnection(
                    URL, USERNAME, PASSWORD);

        // create JDBC statement object
        if(con != null) {
           st = con.createStatement();
        }

        // execute the SQL query
        if(st != null) {
           rs = st.executeQuery(query);
        }
        
        // process the resultset
        if(rs != null) {
           while(rs.next()) {
              System.out.println( 
                    rs.getString("ENAME") + " " +
                    rs.getFloat("SAL") + " " + 
                    rs.getInt("R") );
           }
        }

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

     finally {
        // close JDBC objects
        try {
           if(rs != null) rs.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(st != null) st.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();
        }
     } // finally
   } //main()
} //class

Output for different test-cases:-

Enter N value: 2
SCOTT 3000.0 2
FORD 3000.0 2

Enter N value: 7
TURNER 1500.0 7

Enter N value: 9
WARD 1250.0 9
MARTIN 1250.0 9

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!

Also See:-

Leave a Comment

Your email address will not be published. Required fields are marked *