JDBC Processing Queries

Java database connectivity defines a set of API objects and methods to communicate with databases. In the process of communicating with databases, a Java program does the following:

• Opens a connection to a database

• Makes a Statement object

• Passes SQL statements to the DBMS through the Statement Object, and

• Retrieves the results of the SQL statements, along with information about the result sets.

There are three different ways of constructing and executing SQL statements in JDBC. They use the following classes:

• Statement : This class allows the execution of static SQL command in a string format.

• PreparedStatement :    This class allows repeated execution of a compiled statement. We can also use it to execute a parameter-less stored procedure.

• CallableStatement :      This class allows executing the stored procedures and takes advantage of optimization by the database system.

PreparedStatement and CallableStatement are the sub-classes of the Statement class.

The Statement class

A class by name Statement is created using the createStatement () method of the Connection class, which returns a Statement object. The syntax is the following:

Statement stmt = dbCon.createStatement () ;

The execution of the above statement gives the instance stmt of the Statement object but it does not have any SQL statement to pass on to the DBMS at that point of time.

The Statement class is used to build SQL statements for database operations such as creation, updation, retrieval and deletion. Methods of the Statement class take a parameter string containing the SQL statement to be executed.

There are three possible Statement methods which can be used to execute an SQL statement string:

• executeQuery (String sql) :  This method is used for SELECT statements. Returns the ResultSet object. The result set consists of the rows returned by the select statement.

• executeUpdate (String sql) :  This method is used for SQL statements such as INSERT, UPDATE or DELETE, which will update the database. It returns an integer value, which specifies number of rows affected. This statement can also be used for executing DDL statements (for creating, altering and deleting tables)

• execute (String sql) : This method is used when the SQL statement may be an update or query. (This statement can be used in place of executeQuery() as well as executeUpdate().)

The following is an example showing how to execute a query:

Statement stmt = dbCon.createStatement ();

stmt.executeQuery (“select *from Emp_info”);

Alternately, it can also be written as follows:

Statement stmt = dbCon.createStament ();

String sql = “select * from Emp_info”;

stmt.executeQuery (sql);

The Statement object stmt that was created can be (re)used more than once for different SQL statements. Some of the methods in the statements class are listed in Table.

Data can be inserted or updated with SQL insert and update statements using the method executeUpdate. The following example shows to insert data into the Emp_info table:

Statement stmt = dbCon.createStatement();

String sql=”insert into Emp_info values (1223,’raja’,’technicalwriter’,5000)”;

stmt.executeUpdate (sql);

                                      TableMethods In the statement class.

Note that if an SQL statement does not fit in one line, it can be split into strings concatenated by a plus sign (+). Otherwise, the statement can not be compiled. Also note that there is a single space after the name of the table. The programmer must be careful while concatenating strings so that after concatenation it gives a complete SQL statement.

An example that shows how to update the Emp _info table is the following:

stmt.executeUpdate (“update Emp_Info set designation = ‘Analyst’ where emp no = 2371);

The executeUpdate () method returns the number of rows that are affected by the SQL statement. For example, the following statement assigns the return value of executeUpdate to the variable n. This information is useful to find how many rows have been actually updated.

int n = stmt.executeUpdate (“update Emp_info set designation = ‘Analyst’ where emp no = 2371);

As mentioned earlier, executeUpdate method can also be used for DDL statements. For instance, the Emp_info table can be created as follows:

Stmt.executeUpdate (“create table Emp_info as (emp no varchar2 (10) primary key,” +”emp name varchar2 (10), designation varchar2 (10),” +”date_of_join date, salary float)”;

The return value of executeUpdate is zero when a DDL statement is passed as an argument to it.

Accessing rows returned from a query

Rows produced by an executeQuery () will be returned within an object of type ResultSet, which is of use in acting on it later. The following code shows the assignment of the result of the execution of the select statement to the instance rs of the ResultSet object.

Statement stmt =dbCon.createStatement ();

String sql = “select *from Emp_info”;

ResultSet rs = stmt.executeQuery (sql);

Note that the executeQuery method returns a ResultSet object that contains the results of

the query, whereas the executeUpdate method returns an int that represents the number of rows updated by the SQL statement.

Rows are retrieved from a ResultSet object by iterating through each of the rows and requesting information on the contents of each column. Iteration through its rows is similar to iterating through a set of elements in an Enumeration object.

The ResultSet class provides a cursor (also called handle) that can be used to access each row. The method next () of the ResultSet class is used to move the cursor that points to a row in the result set. Once the cursor is positioned on a row, we can request the data for each of its columns. Initially the cursor is set just before the first row. The first invocation of the method next sets the cursor on the first row. That is, to access the first row in the result set, the method next must be called once. Each invocation of next makes the cursor move to the next row. The method next returns a boolean value based on whether the next row is present. If a row exists, it returns true, otherwise, it returns false. Table gives a description of the methods in the ResultSet class.

Accessing column data

The method getXXX is used to access the columns in a row that are being pointed by the cursor currently. Here, XXX represents a Java data type. such as, for example, int As each column may contain a different data type, we need a ResultSet method specific to that data type. The method getInt (int column) can be used to retrieve a column with an integer value and getString (int column) can be used to retrieve a column with a string value. Under usual circumstances, we can use the getString(int column) method to get all types of database value. In such cases the retrieved values will be converted to a string type in Java.

                                             Table Methods in the ResultSet class.

A call to the next method sets the cursor to the next row whereas a call to getXXX method accesses a particular column in a row.

To retrieve a column value, the column name (or column number) is to be specified as a

parameter to the getXXX method. The use of the method getInt (2) is shown in the following example:

Statement stmt= con.createStatement ()

String sql=”select * from Emp_info”;

ResultSet rs=stmt.executeQuery ();

while (rs.next ())

{

     String name = rs.getString (1);

     int age = rs.getInt (2);

     System.out.println (name+” ” +age);

}

In the above example, the parameter 1 in the line rs.getString (1) indicates that it refers to the first column and rs.getInt(2) refers to the second column. Specifying columns is allowed by JDBC in the ResultSet and is done by using either the column name or the column number in the result set. For instance, since emp no is the first column in the Emp_info table, the results of using the following two statements are identical.

String name = rs.getString (“emp no”) and

String name = rs.getString (1).

Note that the column number (in the above statement, 1 is the column number) refers to the column number in the result set and not in the database table.

In some cases, data accessing methods will return null values, which looks ambiguous. The class ResultSet provides a method wasNull () that can be used after each column access. The method getObject () can be used to avoid the need for calls to wasNull (). The method getObject () returns the contents of the row according to the Java type which corresponds to its SQL type (the method uses metadata to determine this).

The programmer must know the format of the rows for the table or view they are working

with. This format of table can be known by accessing the metadata related to the database. A number of methods are provided by the JDBC to know the cursor position in the result set: these are getRow (), isFirst (), isBeforeFirst (), isLast () and isAfterLast ().

By default, JDBC allows the cursor only to scroll forward and the retrieved columns values are read-only. Further, JDBC 2.0 API (Java2 SDK, Standard Edition, version 1.2) provides a set of updater methods (to update the table) to the interface and has made it capable of being scrolled also (that is, it moves in any direction). Here is an example of how to create a result set

Statement stmt = dbCon.createStatement (ResultSet.TYPEFORWARD_ONLY,

ResultSet.CONCUR_READ_ONLY);

The other parameter options available are presented in Table

                        Table Parameter options for create statement.

In addition to the next () method, the scrollable cursors in JDBC 2.0 API provide options such as absolute, previous and relative. That is, in a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position or to a position relative to the current row. Examples of these are given below:

rs.absolute (5); // move the cursor to the fifth row of rs (absolute position from the first row)

rs.relative (5); // move forwards five rows from the current row

rs.relative (-2); // move backwards two rows from the current row

rs.previous () // move one row backwards from the current position.

With the help of JDBC 2.0 API, result sets can also be used to update a data source by

positioning on the row and specifying the replacement value then calling updateRow (). A similar set of methods can be used to insert rows.

The updater methods may be used in two ways: (i) to update a column value in the current row and (ii) insert a row with column values into a table.

The following code fragment updates the NAMEcolumn in the fifth row of the ResultSet

object rs and then uses the method updateRow to update the data source table from which rs was derived.

rs.absolute (5);

rs.updateString (“emp name”, “Kumar”);

rs.updateRow () ;

An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a two-column row and inserts it into rs and into the data source table using the method insertRow.

rs.moveToInsertRow ();

rs.updateString (1, “Kumar”);

rs.updateString (2, “2384”);

rs.insertRow ();

rs.moveToCurrentRow ();

The method Prepared Statement

In some cases, there will be a situation in which the SQL statement can be executed more than  once. So, to avoid constructing a new execution plan for each SQL statement we can use the PreparedStatement method. Using this, the execution plan needs to be prepared only once. That is, this statement allows the creation of a parameterized statement for which values can be inserted during the run-time, thereby allowing the same SQL statement to be used many times, each with different parameters.

Look at the following example of PreparedStatement:

String sql = “insert into Emp_info values (?,?,?,?,?)”;

PreparedStatement pstmt = dbCon.prepareStatement (sql);

boolean finished=false;

while (!finished)

{

      if (emp name.equals (“end”))

      finished = true;

      else

       {

            pstmt.setString (1, emp no);

            pstmt.setInt (2, emp name);

            pstmt.setString (3,designation );

            pstmt.setDate (4,date);

            pstmt.setFloat (5,salary);

            pstmt.executeUpdate ();

       }

}

pstmt.c1ose ();

The question marks in the above example indicate positions where parameter values are to be placed (these are also called place holders). The code pstmt.setString(1, emp no) sets the value of the first parameter to the string contained in the emp no variable. When accessing values returned from a query, the programmer needs to know the type of the database field the parameter corresponds to.

While using PreparedStatment, there is no need to pass an argument to the executeUpdate method as it already contains the SQL statement that is to be executed.

In the previous example, to repeat the execution of the prepared statement we used the variable finished. First, it is set to false, then the other variables employee number, name, designation, date of joining and salary_in_rupees are read and then the prepared statement is executed. When the name variable ‘end’ is read then the program comes out of the loop by resetting the value of the variable finished as true.

The c1earParametersO method (not shown in the above example) can be called to clear the values that are set using setXXX method.]

The method CallableStatement

The method CallableStatement is used to execute the stored procedure in order to access the database.

The stored procedure calculate_netsal () calculates the net salary based on gross salary stored in the salary field of the Emp_info table stored in SQL server database. The input to this procedure is employee number and the percentage of deductions. Let the stored procedure be the following set of statements:

CREATE PROC calculate_netsal @id varchar (10),

@percentage int ,

@net float OUTPUT

AS

declare @gross float

SELECT@gross= gross from Emp_info where emp no=@id>

SET @net=@gross-(((@gross)*(@percentage))/100)

RETURN (0)

The CallableStatement can be used for the above program as follows:

CallableStatement cstmt = dbCon.prepareCall (“{call calculate_netsal(?, ?, ?)}”);

cstmt.registerOutParameter (3, java.sql.FLOAT);

cstmt.setString (1, emp no);

csmt.setInt (2, percentage)

cstmt.execute ();

out.println (“Net salary: ” + cstmt.getFloat (3));

The method registerOutputParameter () registers the third parameter as an OUT parameter (or a call-by-reference parameter) of type float.

The three JDBC statement types, namely Statement, PreparedStatementand CallableStatement, are used to pass the SQL statements to the DBMS; however, all these three statements differ from each other in the timing of the SQL statement preparation and the statement execution. We first look into how an SQL statement in a programming language code is executed by a DBMS. A Java program first builds the SQL statement in a string buffer and hands over the same to the DBMS through API calls. The SQL statement need to be checked for correctness of syntax and then converted to an executable form.

The DBMS first parses the statement. If the statement is syntactically not correct, the DBMS returns an error condition to the driver, which generates an SQLException. Otherwise, the DBMS generates different query plans and selects an optimal plan based on the cost of execution. This optimal plan in turn gets converted into a binary execution plan. After the execution of the binary code, the DBMS returns an identifier to the application program.

Here is how each JOBC Statement is prepared and executed.

• The Statement object-the SQL statement is prepared and executed in one step in the Java code.

• The PreparedStatement object-The driver stores the execution plan handler for later use.

• The CalledStatement object-The SQL statement is actually making a call to a stored

procedure that is usually already optimized.

When using a simple Statement object, the optimal query plan is generated each time an SQL statement is executed. In the case of the PreparedStatment object, it is required to generate the  optimal query plan for an SQL statement once and it can be used several times. For instance, when inserting a number of rows in a table, we have to execute the same Statement object more than once (with a different set of row values). Thus, to execute an SQL statement several times, it takes less time if the PreparedStatement is used. In the case of the CalledStatement object, the binary code is executed which was previously optimized and stored in the database.