Turn Desktop View Off
by Dinesh Thakur

The Statement object executes a normal SQL statement to update or query the database. The java.sql.Statement interface is a part of JDBC API that describes a standard abstraction for statement object which is implemented as part of JDBC driver.

   A Statement object sends the required SQL statement as a query and then returns a set of results. We can use the createStatement() method of the connection object to obtain Statement object because we cannot create object of interface.

Exploring the Methods of Statement

We can create multiple objects by using a single connection object. The following method of the connection objects is used to retrieve a statement object:

• CreateStatement():

   It retrieves a statement that can produce a ResultSet of the Forward only type and read concurrency.

The following are some methods supported by Statement object:

1. public ResuItSet execute Query(String query):

It executes the given SQL select statement, which returns ResultSet object.

2. public int execute Update (String query):

It execute the given SQL non-select statement which may be an Insert, Update or Delete statement or an SQL statement which returns nothing, such as SQL DDL statement.

3. public boolean execute (String query):

This method is used for executing both select and non-select statements (Insert, Update, Delete) statement.

When the execute method is called, we need two helper methods to read the data output of command from the database.

(a) GetResuItSet(): This method is called when select operation is done on database.

(b) GetUpdateCount(): This method is called when non-select is done in database.

Steps to write JDBC program

Step-1 Load JDBC driver class into JVM

Syntax to load driver:

Class. forName (" fully qualified classname");

Class. forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Step-2 Establish the connection with database

To obtain connection with database, a Java program asks for the same DriverManager class. By calling static method called getconnection(). While calling getconnection().

A Java program will pass three parameters:

I. URL

II. User name

III. Password

A URL is used to identify a driver's register with DriverManager.Username and Password is used for authentication.

Syntax:

 

Connection cn=DriverManager.getconnection ("URL" ,"Username" ,"Password");

For Type-1:

Connection cn=DriverManager.getconnection("Jdbc:odbc:dsn-name","system", "manager");

Step-3  Create statement object (same for all driver)

Syntax:

  Statement st=cn.createStatement(); // {cn= Connection Object}

Step-4 Execute the SQL queries

Syntax:

 ResultSe trs = st.executeQuery("select * from emp");

Step-5 Print the result

Step-6 Close the connection

I. The executeQuery(String query) Method

It is used to execute select statement which returns ResultSet object.

 

Example

 

import java.sql.*;

public class Test

{

     public static void main(String args []) throws Exception

    {

             //step-l load the driver (Type-l)

             Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;

             //step-2 get the connection

             Connection cn=DriverManager.getConnection ("jdbc:odbc:dinesh","system"," manager") ;

             //step-3 create Statement, object

             Statement st=cn.createStatement();

            //step-4 execute sql statement

            ResultSet  rs=st .executeQuery ("select * from emp");

            //step-5 print the output

           while(rs.next ())

                  {

                        int empno=rs.getInt(1);

                        String ename=rs.getString(2);

                        System.out .println ("Employee no "+empno+"Employee name "+ename);

                   }

                         //step-6 close connection and Statement and .Resultset

                         st.close ();

                         rs.close () ;

                         cn.close () ;

       }

}

                         How to Work with Statement Interface?

II. The executeUpdate(String query) Method

 

This method is executed for DDL or DML operation on database. It means non-select operation on database. When DDL operation is executed on database the result will be negative integer and for DML result is positive integer.

DML operation= Insert, Update, Delete

DDL operation= create, Truncate, drop, alter

Example

 

import java.sql.*;

public class CreateTable

{

            public static void main(String args[]) throws Exception

           {

               //step-1 load the driver(Type - 2)

              Class.forName ("oracle.jdbe.OracleDriver") ;

              //step-2 get the connection

              Connection cn=DriverManager.getConnection("jdbc:oracle:oci:@XE","system","manager");

              //step-3 create Statement object

              Statement st=cn.createStatement () ;

              //step-4 execute sql statement

              int k=st.executeUpdate("create table student(name varchar2(20),roll number (7) )");

              if(k<0)

                {

                      System.out.println ("Table created");

                }

                else

                    {

                          System.out.println("tahle is not created");

                    }

                        st.close();

                        cn.close();

            }

}

 

Output:

Table created

     • In the same way, perform Insert operation:

       int k=stint.executeUpdate("insert into student values("sai,1")");

 

III. The execute(String query) Method

This method is used for executing both select and non-select operation.

boolean b=st.execute("select * from emp");

• If we execute select operation then return true.

boolean b=st.execute("create table student(name varchar2(15),roll name(7))");

• If we execute non-select operation then return false.

 

Example

 

import java.sql.* ;

public class Test2

{

              public static void main(String args [ ]) throws Exception

             {

                     //step-l load the driver(Type-4)

                     Class.forName ("oracle.jdbc.driver.OracleDriver");

                     //Step-2 get the connection

                     Connection cn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system","manager");

                     //step-3 create Statement object

                     Statement st=cn.createStatement( ) ;

                     //step-4 execute sql statement

                     boolean b=st.execute("select * from emp");

                     if(b==true)

                        {

                                System.out.println ("select operation done") ;

                                ResultSet rs=st.getResultSet ();

                                //step-5 print the output

                                while(rs.next( ))

                                       {

                                            int k=rs.getInt(1);

                                            String name=rs.getString (2) ;

                                            System.out.println("ROll No "+k+" Name :" +name);

                                       }

                                            rs. close( ) ;

                         }

                         else

                              {

                                  System.out.println( "non-select operation is done");

                                  int k=st.executeUpdateCount() ;

                              }

                              //step-6 close connection and Statement and ResultSet

                              st.close () ;

                              cn.close () ;

             }

}

 

Limitation with Simple Statement Object

Framing SQL query with variable for simple statement object is a complex process.

  If simple statement is used to execute same query multiple times with same or different values:

(a) The same query goes to database software multiple times.

(b) Same query will be parsed in database software multiple times.

(c) Same query will be executed in database software multiple times with same or different value.

(d) The same query output will be fetched out multiple times.

In the above operation (a) and (b) are unnecessary operations for multiple times on single query. But they cannot be avoided while working with simple Statement object. This makes database software perform unnecessary operations which may degrade the performance of the operation.