9 JDBC2

Pravin Jain

Executing aprecompiled SQL using Prepared Statement

An instance of Statement is used to execute any kind of SQL. There is a  sub-interface  of  Statement called PreparedStatement, which is used to execute precompiled SQL. An instance of PreparedStatement is also created from the instance of Connection by using the following methods of Connection   interface:

PreparedStatement prepareStatement(String sql)

PreparedStatement prepareStatement(String sql, int rstype, int rsconcur)

Here unlike the Statement the SQL is specified at the time of creating the instance of PreparedStatement. The SQL, which is passed as parameter can have place holders which are introduced by using the ’ ?’ character within the SQL string passed as parameter. These place holders are referred to as parameters for the PreparedStatement.

PreparedStatement ps = con.prepareStatement(“select * from account where acno=? and type = ?”);

PreparedStatement ps = con.prepareStatement(“insert into account values(?,?,?,?)”);

The first ’ ?’ in the SQL string is the first parameter of the PreparedStatement, and so on. So,  the number of parameters in a  PreparedSTatement will be  the number of ’ ?’  characters used in the SQL string. The parameters can only be used in place for values in the SQL statement. These  parameter values need to be setup before the precompiled SQL corresponding to the PreparedStatement can be executed.

Now the PreparedStatement interface has additional methods for executing the SQL. These are the same three methods as in Statement interface, but without any parameters. In Statement interface the parameter to the three execute methods was the SQL to be executed. In case of PreparedStatement this has already been specified at the time of creating the instance using the parameter in the prepareStatement() method. So, the methods for executing the precompiled SQL in a PreparedStatement are:

boolean execute() int executeUpdate()

ResultSet executeQuery()

Before invoking any of the execute methods on an instance of PreparedStatement, the values of all the parameters in the PreparedStatement, which are indicated using the ’ ?’ character, must be initialized. To set the values for the parameters in a PreparedStatement, the PreparedStatement interface has a set of methods. The method to be used depends on the type (SQL type) of the parameter(place holder) whose value is to be set. The general form of these methods is as given below:

void setXXX (int parno, XXX value)

where XXX is the type of parameter. eg. If the SQL type of the parameter whose value is to be set is a VARCHAR, then String type in Java would be more appropriate. and we would use the method

void set String ( int param , String value )

Similarly there are other methods for setting parameter values of other types.

So we first use the appropriate setXXX() methods to set values of the parameters in the PreparedStatement and then use the execute method  on  the  instance  of  PreparedStatement  to execute the precompiled SQL.

Executing Stored Procedures and functions using CallableStatement

There is also  a sub-interface  of PreparedStatement  called the  CallableStatement, this  is used to execute stored procedures and functions. The instance of CallableStatement is created by the method on Connection as

CallableStatement prepareCall(String callstatement)

CallableStatement prepareCall(String callstatement,int rstype,int rsconcur)

The CallableStatement inherits methods from the PreparedStatement and some more additional methods. let us look at how to create an instance of CallableStatement. In the above methods  the syntax for the callstatement is:

{ [?] = call <proc-name or function name> (?,…} }

eg. if it is a stored procedure which is to be invoked we create CallableStatement as

CallableStatement cs = con.prepareCall(“{ call myproc(?,?,?) }”);

to execute a stored procedure or

CallableStatement cs = con.prepareCall(“{ ? = call myfunctino(?, ?, ?) }”);

to execute a function, which returns some value.

In the above stored procedure invocation we assume that “myproc” stored procedure has 3 parameters required. Now, the parameters in storedprocedure are of 3 types, ie. there are some parameters which are IN parameters, some are OUT parameters and some are INOUT parameters. This is decided when the procedure has been created in the database. The IN parameters and the IN- OUT parameters need to be initialized before using the execute method on the instance of CallableStatement. This initialization can be done similar to the PreparedStatement. ie. by calling the setXXX methods corresponding to the parameters, which are IN and INOUT types. Now before calling the execute methods the OUT parameters need to be registered, this can be done by using the method:

void registerOut Parameter(int parno, int sqldatatype)

The value in the second parameter  of  the  registerOutParameter()  is  the  SQL  datatype  constants defined in the java.sql.Types class. Corresponding to the various standard ANSI SQL data types, there is a constant in the java.sql.Types class. We have the following kinds of declarations in the Types class.

public static final int VARCHAR = 12 public static final int CHAR = 1  public static final int NUMERIC = 2 public static final int DECIMAL = 3 public static final int INTEGER = 4

So the sequence for executing a stored procedure will be as follows:

• Have an instance of CallableStatement with the appropriate parameters.
• Then set up the values of the IN and the INOUT parameters using the setXXX methods.
• Register the OUT parameters using the registerOutParameter method.
• Then finally call the appropriate execute method.

After the execute method has finished, the stored procedure has executed in the database, and  the values of the OUT and the INOUT parameters are now available for reading. For reading the values of the OUT and the INOUT parameters we can use the getXXX methods which are available in the CallableStatement. So, after execute we may use the getXXX methods and read the values of the OUT and INOUT parameters. In case of executing a function. we can have parameter no 1 as an OUT parameter which is put before the call statement in the String parameter of the prepareCall() method.

This would be similar to executing a stored procedure, except for that the parameter no 1 is an OUT parameter whose value will be read using the getXXX method after calling the appropriate execute method.

Examining the results of a Query from the ResultSet

The resultSet contains data from the database. This data is made up of rows. The columns of the resultset give information about the data in the rows.

Examining the Meta Information from ResultSetMetaData

The information about the columns in the resultset is available  from  an  instance  of ResultSetMetaData which can be obtained from the ResultSet instance by using the  method getMetaData() on the ResultSet. ie. the ResultSet interface

has a method as follows:

ResultSetMetaData getMetaData()

Now to get information about the columns  from  the  instance  of  ResultSetMeta-  Data  we  have methods as follows:

int getColumnCount () // this returns the no. of

// columns in the ResultSet.

All other methods in the ResultSetMetaData have one parameter which is int, indicating the column number for which we want the information. The column number starts from column number 1 and not 0. The other methods inResultSetMetaData are:

String getColumnName(int colno) String getColumnLabel(int colno)  int getColumnDisplaySize(int colno) int getColumnType(int colno)

The getColumnType method returns the sql data type. like varchar, numeric, date, etc. For all the valid sql data types there is an integer constant available in the java.sql.Types class. This class contains only constants, for the SQL types.

The ResultSetMetaData also contains method called

String getColumnTypeName (int colno)

This would return the String name of the sql type. continuing with methods of ResultSetMetaData:

String    getColumnLabel(int colno)

int getScale(int colno)

int getPrecision(int colno)

String getSchemaName(int colno)

String getTableName(int colno)

boolean   isAutoIncrement(int colno)

boolean   isNullable(int colno)

String  getColumnClassName(int colno)

The data which is in the database is of SQL type, this has to be used from java. eg. we may have a column which is VARCHAR, and if we want to use it in Java application, we would use it as String in java. There are two things, one is the SQL data type that is used in database, another is java type that we want to use from java application. For every SQL data type, there is some java data type which is most appropriate for representing its value in Java. eg. for VARCHAR SQL type, String is most appropriate, for NUMERIC data type float may be most appropriate, for BLOB SQL type, we have java.sql.Blob. For a given column if we want to know the most appropriate Java data type, we can use the method getColumnClassName().

Examining the data from the ResultSet

Now coming back to ResultSet. This contains the data ie. the rows. When we want to get information from ResultSet, we first need to position on the row from which data is desired. In ResultSet  we always have a row position. Initially current row position is before the first row. We normally use the method next() on the ResultSet to move  to the next row. Following are the methods which help us to navigate within a ResultSet:

boolean next()

boolean previous() // cannot be used in forward only resultset. boolean first()

boolean last() void beforeFirst() void afterLast(0

boolean absolute(int rowno)

boolean relative(int count) // could be negative to move backwards.

We can know the current row number by using the method getRow().

int getRow() boolean isBeforeFirst() boolean isAfterlast() boolean isFirst() boolean isLast()

Once we are positioned on a row, we may now get the data from that row for any of its column. Again, depending on the type of column we have to fetch data from, we use different method to fetch the data. eg. if the column type is VARCHAR, then we use the method

String getString(int colno) or String getString(String colname)

The ResultSet interface has methods for various datatypes to get the data. like int getInt(…) and so on. we normally refer to these set of methods as getXXX methods. where XXX would be appropriate java datatype. for most of the data types, using getString(..) would work. so even if u have a numeric field or a data field u may still use getString(…) to extract data in String form. the getDate(…) method returns java.sql.Date. there is java.sql.Date class which is a subclass of java.util.Date. You also have java.sql.Time, and java.sql.TimeStame which are subclasses of java.util.Date. You also have interfaces like Array, Blob, Clob, Ref and Struct to take care of the corresponding sql data types. In ResultSet you also have a method

Object getObject(…)

This would use the appropriate getXXX(…) method and return its instance. The return type is Object and hence it may return any kind of instance. so if the column corresponds to data sql type it would return the object of type java.sql.Date So many times it might be appropriate to use getObject(…).

Updating an updatable ResultSet

Now in case the ResultSet is updatable. then we can also use methods which can update into the ResultSet. for updates we may go for either deleting row, updating row or inserting row into the resultset. If we want to delete row then we position on the row u want to delete and then call the method

void deleteRow()

If we want to update a row in the resultset then we position on the row we want to update, then we call the appropriate updateXXX(….) methods to update the various column values in the current row. The updateXXX(…) methods are:

void updateXXX(int colno, XXX value)

void updateXXX(String colname, XXX value)

now in order to update the values updated in this row we need to call the method void updateRow() on the resultSet, you may cancel the updateXXX by calling the method

void cancelUpdate() instead of the updateRow().

ie. after calling updateXXX(…) for changing the values in the current row u may use either cancelUpdate()  or  updateRow().

If u want to insert rows into the resultset, then there is special row called the insertrow, so the sequence will be.

call the method

void moveToInsertRow()

then call the appropriate updateXXX(..) to set up the values in the columns for the row to be inserted. and then call the method insertRow() to insert the row. you can now insert any no. of rows by simply calling updateXXX(…) followed by insertRow(). the method moveToInsertRow() is like moving into insert mode. when you want to come out of insert mode you call the method

void moveToCurrentRow() this will take you back from insert mode to normal mode.

Managing transactions

In SQL, you can have transactions. it requires  that  the  auto  commit  should  be  off.  The  transactions can be used from JDBC also. for this the Connection interface has methods like

void   setAutoCommit(boolean autocommit) boolean getAutoCommit()

you can call setAutoCommit(false) on an instance of Connection and then whatever DML you execute using this Connection, is in a transaction. The DML will be executed using the Statement or PreparedStatement. Now you may decide to either commit or rollback the transaction. for this Connection interface has methods like

void rollback() void commit()

You like to define savepoints in you transaction and then maybe rollback to a savepoint. To define savepoints in the transaction, you have methods like

Savepoint setSavepoint()

Savepoint setSavepoint(String name)

you can rollback to a specified savepoint by using the method

void rollback(Savepoint sp)

The Connection interface has also another method called getMetaData which returns an instance of DatabaseMetaData.

the DatabaseMetaData instance can be used to find information about the database, like which version of database, which jdbc driver version is being used, what are the capabilities of the database. for all this u can refer to the methods in the DatabaseMetaData in the api.

SQL Exception

Also almost all the methods in the java.sql package throw SQLException which is in the java.sql package. When we execute an sql, it can result in error. The SQLException instance also encapsulates the vendor specific error codes, which can be found by using the method

int getErrorCode()

for eg. in case of Oracle we get the ora error no. This will be returned in the getErrorCode method.

In Summary:

• instances of Statement, PreparedStatement and CallableStatement are used to execute any sql, precompile-sql and stored-procedure respectively. These are obtained from the  instance  of Connection.
• The statement instances provide the methods to execute the sql, precompiled-sql or the stored-procedure. The method executeQuery() is used to obtain the results of executing a select query. The result of executing a select query are available as instance of ResultSet.
• The information about the columns in a ResultSet are available from the instance of ResultSetMetaData associated with the ResultSet. The ResultSet has methods for row-wise navigation and for extracting the values in the various columns of the curren row. The result sets which are obtained, can be updatable, depending on the setting in the statement instance being used to execute the select query. The ResultSet has methods for making updates in the resultset in case it is updatable.

Suggested Reading:

1. Core Java Volume 2 by Cay Horstmann & Gary Cornell, Ninth Edition, Pearson Education.
2. The class of JAVA by Pravin Jain, Pearson Education.
3. Database programming with JDBC and Java by George Reese. O’Reilly Media
4. https://docs.oracle.com/javase/tutorial/jdbc/basics/