Statement Interface in JDBC

Statement Interface

The Statement interface provides the method to execute the database queries. After making a connection, Java application can interact with database. The Statement interface contains the ResultSet object.

Statement Interface Methods

The Statement interface provides the following important methods:

Methods	Description
public boolean execute(String sql)	It executes the given SQL query, which may return multiple results.
public intexecuteBatch( )	It submits the batch of commands to the database and returns an array of update counts.
public ResultSetexecuteQuery( )	Executes the given SQL queries which return the single ResultSet object.
publicintexecuteUpdate(String sql)	It performs the execution of DDL (insert, update or delete) statements.
public Connection getConnection( )	It retrieves the connection object that produced the statement object.

Example : Performing select operation with Statement Interface

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SelectTest
{
     public static void main(String[] args) throws Exception
     {
          Class.forName("oracle.jdbc.OracleDriver");
          Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
          Statement st = con.createStatement();
          ResultSetrs = st.executeQuery("select * from student");
          while(rs.next()!=false)
          {
                System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));
          }
          rs.close();
          st.close();
          con.close();
     }
}

PreparedStatement Interface

The PreparedStatement interface extends the Statement interface. It represents precompiled SQL statements and stores it in a PreparedStatement object.
It increases the performance of the application because the query is compiled only once.
The PreparedStatement is easy to reuse with new parameters.

Creating PreparedStatement Object
String sql = "Select * from Student where rollNo= ?";
PreparedStatementps = con.prepareStatement(sql);

Note: All the parameter are represented by "?" symbol and each parameter is referred to by its origin position.

Example : Insert operation with PreparedStatement Interface

import java.sql.*;
class PreparedStatDemo
{
     public static void main(String args[])
     {
         try
         {
             Class.forName("oracle.jdbc.driver.OracleDriver");
             Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username", "password");
              PreparedStatementps = con.prepareStatement("insert into Student values(?, ?, ?)");
              ps.setInt(1, 101);
              ps.setString(2, "Surendra");
              ps.setString(3, "MCA");
              ps.executeUpdate();
              con.close();
         }
         catch(Exception e)
         {
              System.out.println(e);
         }
     }
}

CallableStatement Interface

The CallableStatement interface is used to execute the SQL stored procedure in a database. The JDBC API provides stored procedures to be called in a standard way for all RDBMS.

A stored procedure works like a function or method in a class. The stored procedure makes the performance better because these are precompiled queries.

Creating CallableStatement Interface
The instance of a CallableStatement is created by calling prepareCall() method on a Connection object.

For example:
CallableStatementcallableStatement = con.prepareCall("{call procedures(?,?)}");

Example : CallableStatement Interface using Stored procedure

Creating stored procedure

create or replace procedure "insertStudents"
(rollno IN NUMBER,
name IN VARCHAR2,
course IN VARCHAR2)
is
begin
insert into Students values(rollno, name, course);
end;
/

// ProcedureDemo.java

import java.sql.*;
classProcedureDemo
{
    public static void main(String args[])
    {
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
            CallableStatementstmt = con.prepareCall("{call insertStudents(?, ?) }");
            stmt.setInt(1, 101);
            stmt.setString(2, Vinod);
            stmt.setString(3, BE);
            stmt.execute();
            System.out.println("Record inserted successfully");
            con.close();
            stmt.close();
        }
        catch(Execption e)
        {
            e.printStackTrace();
        }
    }
}

Note: The ProcedureDemo.java file inserts the record in Students table in Oracle database by use of stored procedure.

« Previous

Tutorial