JDBC Statement Interface

In JDBC the Statement is an interface that represents a SQL statement. We execute Statement objects, and for select queries, ResultSet objects will be generated, which is a table of data representing a database result set. We need a Connection object to create a Statement object.

There are three types of JDBC statements
1) Statement
2) PreparedStatement
3) CallableStatement

The first type i.e. Statement is also called as Simple Statement because it is used with simple SQL statements without parameters. The PreparedStatement and CallableStatement both are extended from Statement (or, Simple Statement). The PreparedStatement is used for precompiling SQL statements that might contain input parameters, and CallableStatement is used to execute stored procedures/functions that may contain both input and output parameters. In this post, we will discuss the Statement interface (Simple Statement).

JDBC Statement object acts as a vehicle between Java application and database software to send the SQL query from Java application to database software and to bring SQL query results back to Java application from database software.

JDBC Statement object is the object of underlying JDBC driver software supplied Java class that implements java.sql.Statement(I).

Types of SQL Queries

According to Oracle database specification, there are four types of SQL queries.
1) DRL/DQL queries (select queries)
2) DML queries (insert, update, delete queries)
3) DDL queries (create, alter, drop queries)
4) TCL/DCL queries (commit, savepoint, rollback)

But According to JDBC programming, these SQL queries can be divided into only two catogeries.
1) Select queries (DRL queries)
2) Non select queries (DDL, DML, TCL queries)

The main difference between Select and non-select queries is the number of return values. When a select query is executed then we get a bunch of records from database software. But when the non-select query is executed then we get the numeric value which represents the number of records that are affected. Due to this difference, separate methods are given to execute the select and non-select queries.

SQL> select * from student;

It will give multiple records.

SQL> insert into student values
    (10, ‘name’, ‘address’, 50);

It will give result as record inserted or error came. Similarly, for remaining non-select queries.

Important Points to be Remember

The non-select query executed from SQL prompt can be committed or rollback explicitly because they will be executed in database software by disabling auto-commit mode. But when it comes to JDBC application then explicit commit or rollback can’t be done. The non-select queries that are executing in database software by coming from front-end applications like Java application can’t be committed or rollback because they will be executed in database software by enabling auto-commit mode.

For select queries, the JDBC application can’t read and manipulate uncommitted records. For example, from SQL prompt we created a table, inserted some records in the table but not committed, then since the create table is auto committed in the database but records are inserted by disabling auto-commit mode. The DDL command (create, alter, drop, truncate) are auto committed in the database and it can’t be rollbacked, but DML command (insert, update, delete) need explicit commit or rollback. When our JDBC application tries to fetch the record then it will hang up until the records become stable in the database i.e. application will hang until we don’t explicitly commit or rollback the records in the database from SQL prompt.

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.

In real time programmers will not do DDL operations through JDBC application. Every company contains database teams who design database tables based on normalization rules.

Every Java project contains three types of developers:- UI developers, Java developers, and SQL developers. The SQL developers are responsible to design tables, PL/SQL procedures and functions that are required for the project.

JDBC applications are useful to send and execute SQL query in database software to manipulate database data. In this process JDBC acts as front-end helping end-user and database software acts as back-end. The back-end response is based on the instruction.

Methods of JDBC Statement interface to execute query in Java

Mainly four methods of Statement interface are used to execute the SQL query. Those methods are,
1) execute(-)
2) executeQuery(-)
3) executeUpdate(-)
4) executeBatch(-)

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.

Except for the above two methods, the execute(-) method is also given which can be used to execute both select and non-select queries. The return type of the execute(-) method is boolean.

The executeBatch(-) submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. This method will be discussed in detail in Batch Processing.

Other important methods of Statement interface which are used in almost every JDBCE program are,

• getConnection():- It retrieves the Connection object that produced this Statement object. Before executing the query, we need to get a Connection.
• close():- It releases the Statement object’s from the database and JDBC resources immediately instead of waiting for automatically closed.

JDBC Statement example

In this post, we will develop a JDBC program to select a query using a Statement. In the next post, we will develop a JDBC program for insert, update, and delete queries using Statement object. Also see:- JDBC Statement example.

In this program, we will use the Oracle database. Before developing a JDBC program we must have a table. The JDBC program will fetch the record of the table and display it to the output screen.

Create table in Oracle database,

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

Table created. Now, insert some record into the table,

SQL> insert into student values
     (100, 'SOPHIA', 'LONDON', 85);
SQL> insert into student values
     (101, 'William', 'Boise', 80);
SQL> insert into student values
     (110, 'Alex',' Washington', 90);
SQL> insert into student values
     (200, 'Amelia', 'Manchester', 72);

Now, execute the commit command.

SQL> commit;

Commit complete.

Note:- Don’t forget to commit, Otherwise inserted data will not be saved into the database and our Java application can’ t retrieve the data which doesn’t exist in the database. The application may hang up until the records become stable.

Displaying the table,

SQL> select * from student;

SNO SNAME    SADD        AVG
--- -------- ----------- ---
100 SOPHIA   LONDON      85
101 William  Boise       80
110 Alex     Washington  90
200 Ameila   Manchester  72

JDBC Select Query Example

Q) Write a JDBC program using Statement interface to select record from “student” table based on “sno”. Take input “sno” from end-user and display the details of that student to the console.

For this, we have to use the select query with a condition,
SQL> SELECT SNO, SNAME, SADD, AVG FROM STUDENT WHERE SNO = 100;

On success it should fetch the record and display it on the console. On failure like wrong sno, it should display appropriate message.

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 SelectTest {

   private static final String URL = 
         "jdbc:oracle:thin:@localhost:1521:knowprogram";
   private static final String USER = "scott";
   private static final String PASSWORD = "tiger";

   public static void main(String[] args) {

      // declare variables
      Scanner scan = null;
      String query = null;
      int sno = 0;
      Connection con = null;
      Statement st = null;
      ResultSet rs = null;
      boolean flag = false;

      try {
         // read input from end-user
         scan = new Scanner(System.in);
         if(scan != null) {
            System.out.print("Enter SNO:: ");
            sno = scan.nextInt();
         }

         // prepare SQL query
         query =  "SELECT SNO, SNAME, SADD, AVG FROM STUDENT "+ 
                                         " WHERE SNO = " + sno;

         // establish the connection 
         con = DriverManager.getConnection(URL, USER, 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 object
         if(rs != null) {
            while(rs.next()) {
               flag = true;
               System.out.println( rs.getInt("sno")+" "
                                 + rs.getString("sname") + " "
                                 + rs.getString("sadd") + " "
                                 + rs.getFloat("avg")
                                 );
            } // while
         } // if
			
         // display result
         if (flag == true) {
            System.out.println("\nRecords retrived and displayed");
         } else {
            System.out.println("Records not found");
         }
			
      } catch(SQLException se) {
            se.printStackTrace();
      } catch(Exception e) {
            e.printStackTrace();
      } // try-catch
		
      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 for test-case1:-

Enter SNO:: 200
200 Ameila Manchester 72.0

Records retrieved and displayed

Output for test-case2:-

Enter SNO:: 300
Records not found

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

Also see:-

• How to Connect MySQL Database in Java Using Eclipse
• MySQL Database Connection in Java with Example
• Oracle Database JDBC Connection with Example
• Coding Standards and Guidelines for JDBC Application

Search