Practical Database Programming With Java

4.2 JDBC Applications Fundamentals 139

String sqlString = (“INSERT INTO LogIn”

+ “VALUES (‘Tom Baker’, ‘come123’, 100078, ‘David Tim’, ‘test55’, 100080)”;

Statement stmt = con.createStatement();

stmt.execute(sqlString);

Figure 4.13. An example coding to insert data into the LogIn table using JDBC statement.

String query = "SELECT user_name, pass_word FROM LogIn " + "WHERE user_name = ? AND pass_word = ?";

try{

PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, “cse”);

pstmt.setString(2, “mack8000”);

ResultSet rs = pstmt.executeQuery();

}

catch (SQLException e) {

System.out.println("Error in PreparedStatement! " + e.getMessage()); }

Figure 4.14. An example coding to perform a SQL query using JDBC statement.

Figure 4.14 shows a piece of example codes to illustrate how to perform a select query to retrieve the desired username and password from the LogIn table.

4.2.3.6.3 JDBC Escape Syntax When JDBC perform a SQL statement, it does not check the SQL grammar, and you can send any SQL statement to your database. This gives you flexibility to allow you to use some extended functions that are not included in the entry level SQL92 standard and provided by particular vendors. To support these extensions in a database independent manner, JDBC implements an ODBC-style escape syntax for many of these extensions. By using escape syntax, applications can achieve total database independence and still take advantages of the additional functionalities provided by those extensions.

Escape syntax works much like the escape character, which contains a keyword and parameters all enclosed in curly braces.

{ keyword [parameter], . . . . }

As JDBC finds a set of curly braces in an executable string, the driver maps the enclosed keyword and parameters to the database-specified syntax, and the mapped syntax is then sent to the database for execution.

JDBC escape syntax supports seven keywords; each of them indicates the type of extension that is enclosed within the braces. Table 4.7 shows a collection of the keywords and their syntax.

So far, we have discussed most Statement components and interfaces in JDBC data actions and applications; now let’s take care of the retrieving the execution results.

140 Chapter 4 JDBC Application Design Considerations

Table 4.7. Keywords and their syntax supported by JDBC escape syntax

Keyword	Function	Syntax
Call	Execute stored procedures	{ call procedure_name [arg1, . . . ]}
? = call	Execute stored functions	{ ? = call function_name [arg1, . . . ] }
d	Define a date	{ d ‘yyy-mm-dd’ }
escape	Define the databases escape character	{ escape ‘escape character’ }
fn	Execute a scalar function	{ ‘fn function [ arg1, . . . ] }
oj	Define an outer join	{ oj outer-join }
t	Define a time	{ ‘hh:mm:ss’ }
ts	Define a time stamp	{ ‘yyyy-mm-dd hh:mm:ss.f….’ }

4.2.4 Retrieving Results

Based on the different SQL statements, three execution methods can be used to run an associated SQL statement. As we discussed in Section 4.2.3.1, each execution method performs different data actions:

•The executeQuery()method is used to run a data query, and the expected returning result is a result set stored in a ResultSet object.

•The executeUpdate() method is used to perform a insert, update, or delete data action, and the returning result should be an integer that equals to the number of rows that have been affected by running this data manipulation.

•The execute()method can be used in either way, but this method never returns any result, and you need to use special methods to pick up the running results.

To pick up the running results for different methods, the following rules should be followed:

1.For the executeQuery()method, the getResultSet()method defined in the Statement interface should be used, since the running result is a result set stored in a

ResultSet object.

2.For the executeUpdate()method, the getUpdateCount()method defined in the Statement interface should be used since the running result is an integer that equals to the number of rows that have been affected.

3.For the execute()method, since this method can handle both ResultSet and integer, it also never returns any result, you need to use special methods to retrieve the running result for the execution of this method.

Relatively speaking, for the first two methods, it is relatively easy to pick the running result since the result is known and definite.The challenge is the third method, execute(), since the result of execution of this method can be either a ResultSet or an integer.

Another challenge is that this method can be used where the SQL statement to be executed is not known at the compile time or there is a possibility of multiple results being returned by a stored procedure. Unlike the first two methods, the execute()method never returns any result, and you must use either the getResultSet()or getUpdateCount() method to retrieve the running results.

		4.2 JDBC Applications Fundamentals 141
Table 4.8. Methods used to determine the types of returned result
Method	Return Value	Testing Result
getUpdateCount()	>0	The result is an update count
getUpdateCount()	=−1	The result is not an update count
getUpdateCount()	=0	Either the update count is zero or a data
		definition language (DDL) statement is
		executed, such as CREATE TABLE.
getResultSet()	=null	The result is not a ResultSet
getResultSet()	=−1	The result is a ResultSet
getUpdateCount()	! = null

To distinguish what kind of result is returned, we can use the method we discussed in the last section to do that. To handle multiple results, we need to use the getMoreResults() method defined in the Statement interface (refer to Table 4.4). When executing this method, a True will be returned if a ResultSet object is returned. If the result retrieved is an integer, then the getMoreResults() method returns a False. The confusing issue is that this method will also return a False if no result is received. In order to solve this confusion, you must use the getUpdateCount() method to test the possible results. Table 4.8 shows a full picture with associated testing condition and possible testing results.

It is easy to get the result of the execution of the executeUpdate()method since only an integer is returned as the result for this method. However, it needs more work to do for the result of the execution of the executeQuery()and execute()methods since a ResultSet object that contains a tabular set is returned. We will concentrate on the methods used to retrieve and process the actual data contained in the ResultSet object. First, let’s have a closer look at the ResultSet interface.

4.2.4.1 The ResultSet Interface

Data stored in a ResultSet are returned by the database in a tabular format. Each field of the database can be described by a unique combination of a row ID and a column ID. A column can be mapped to an array, since all data in a single column have the same data type. Similarly, a row can be mapped to a Vector since all elements in a single row may have the different data types.

The ResultSet interface has more than 25 methods, and Table 4.9 lists some most often used methods.

All getXXX()methods defined in this ResultSet interface, except the getMetaData(), are overloading methods with two signatures, which means that all of those methods can pass two types of arguments, either a column index that is an integer or a column name that is a String. To save space, here we only list the first signature for each of those methods.

Now that we have a clear picture about the ResultSet interface, next we need to get the running results from the execution of an execute method. First, let’s take care of how to get a ResultSet object after an execute method has been done.

142 Chapter 4 JDBC Application Design Considerations

Table 4.9. Methods defined in the ResultSet interface

Method	Function
close()	Close the ResultSet and release all resources associated with it
findColumn(String	Return the column index number corresponding to the column name
colName)	argument
getAsciiStream(int	Retrieve the value of the specified column from the current row as an
index)	ASCII stream. The column can be represented by either the column
	index or the column name
getBigDecimal(int	Return the value of the referenced column from the current row as a
index)	BigDecimal object
getBoolean(int	Return the value of the referenced column from the current row as a
index)	Boolean
getByte(int index)	Return the value of the referenced column from the current row as a byte
getBytes(int index)	Return the value of the referenced column from the current row as an
	array of bytes
getDouble(int	Return the value of the referenced column from the current row as a
index)	double
getFloat(int index)	Return the value of the referenced column from the current row as a
	floating point number
getInt(int index)	Return the value of the referenced column from the current row as an
	integer
getLong(int index)	Return the value of the referenced column from the current row as a long
	integer
getObject(int	Return the value of the referenced column from the current row as an
index)	Object. The object type is determined by the default mapping of the
	SQL data type
getShort(int index)	Return the value of the referenced column from the current row as a
	short integer
getString(int index)	Return the value of the referenced column from the current row as a
	String object
getTime(int index)	Return the value of the referenced column from the current row as a
	java.sql.Time object
getMetaData()	Return a metadata object from the ResultSet object
next()	Move the ResultSet row cursor to the next row
wasNull()	Determine if the last value read by a getXXX() method was a SQL null
	value. A True is returned if the last read value contained a null value

4.2.4.2 Getting and Processing the ResultSet Object

When a SQL data query is executed, the returned result is stored in a ResultSet object, and this ResultSet object can be created by one of the following two methods:

•The executeQuery() method

•The getResultSet() method

When an executeQuery()method is executed, the result of the queried data is stored in a ResultSet object and returned. However, when an execute()method is

4.2 JDBC Applications Fundamentals 143

used to retrieve a data query result, it will not return any result directly; instead, you need to use the getResultSet() method to create a ResultSet to pick up the returned result.

Once the ResultSet object is created by using either method, an appropriate getXXX() method defined in the ResultSet interface can be used to access and retrieve data. Since the data is in a tabular format, any data can be retrieved by using the column and row ordinals. Two different ways can be used to select and access each column and row in a ResultSet object:

1.Using either column index or column name to select the desired column

2.Using the cursor that points to the current row to select a desired row

In order to scan the entire table in a ResultSet object,you can use the next()method defined in the ResultSet interface to move the cursor row by row until the last record. To pick up a specified column from a given row, you can use an appropriate getXXX()method defined in the ResultSet interface with a column index or column name as the argument.

Let’s have a closer look at accessing and processing each row and column from a ResultSet object with a little more discussion in the following sections.

4.2.4.2.1 Fetching by Row In a ResultSet object, a cursor is used as a pointer to point to each row, and each row of data must be processed in the order in which they can be returned. At the beginning time, after an execution method is executed and a ResultSet object is returned, the cursor points the initial row, which is an empty row (refer to Fig.4.15). To move the cursor to point to the first row of data, as we mentioned, the next()method can be used. Then, an appropriate getXXX()method can be used to pick up the desired column from the current row based on the column index or the column name as the argument of that method. Figure 4.15 shows a structure of a ResultSet object with a row pointer positioning diagram.

Figure 4.15a shows an initial cursor position of a ResultSet object, in which an execution method is just completed and a ResultSet object is created. The cursor now points to the initial row, row 0, and it is an empty row with no data included.

	ResultSet		Cursor		ResultSet
			Row Pointer				Cursor
		Row 0 – Empty row				Row 0 – Empty row
							Row Pointer
		Data - Row 1				Data - Row 1
		Data - Row 2				Data - Row 2
		Data - Row N				Data - Row N
		a				b

Figure 4.15. The structure of a ResultSet with a row pointer positioning diagram.

144 Chapter 4 JDBC Application Design Considerations

String query = "SELECT user_name, pass_word FROM LogIn " + "WHERE user_name = ? AND pass_word = ?";

PreparedStatement pstmt = con.prepareStatement(query);

pstmt.setString(1, “cse”); pstmt.setString(2, “mack8000”);

ResultSet rs = pstmt.executeQuery();
while (rs.next()){
username = rs.getString(1);	// username = rs.getString(“user_name”);
password = rs.getString(2);	// password = rs.getString(“pass_word”);
}

Figure 4.16. An example coding of using the looped next() method.

To access and retrieve a row of data, the next()method is executed to move the cursor to point to the next row, row 1 (shown in Fig.4.15b), in which the first row of data is stored. An appropriate getXXX() method can be used to retrieve the desired column with the column index or column name as the argument. To navigate through the entire ResultSet and process each row, you can use the next() method again until the last row. A true will be returned from this next() method if it points to a row containing data, and a false will be returned if the cursor points to a null row, which means that the bottom of the ResultSet has arrived, and no more data are available in this object.

In an actual program development and coding process, a while loop can be used to execute the next() method to advance the cursor from the current row to point to the next row, until a false is returned, which means that the bottom of the ResultSet object has arrived.

Figure 4.16 shows a piece of example codes to illustrate how to use a while loop with the next() method to retrieve the related username and password from the LogIn table in our sample database CSE_DEPT.

Those nonhighlighted codes are prerequisite codes used to create an SQL statement query string, create a PreparedStatement object, and set input parameters for the query string. The codes in bold are key codes used to create a ResultSet object and perform a while loop with the next() method to retrieve all related username and password from the LogIn table in our sample database. Since most getXXX() methods defined in the ResultSet interface are overloading methods, alternatively, you can use the column name as an argument to pick up the desired column. Those alternative codes are shown in the right side with the comment out symbol in front of them.

4.2.4.2.2 Fetching by Column When a valid data row has been retrieved, we need to get each column from that row. To do that, a different getXXX()method should be used based on the different data type of the returned data. One can use either the name of a column or the index of that column to get the data value. Inside the while loop in Figure 4.16, we used a column index as the argument for the getString() method to retrieve the username and password columns from our LogIn table.As you know, the data type for both the user_name and the pass_word are String in our LogIn table; therefore, a getString()method is used with the index of each column. A point to be noted is that the first column has an index of 1, not 0. If the name of each column, not an index, is used for the getString()method in this while loop, the codes can be re-written as

4.2 JDBC Applications Fundamentals 145

while (rs.next()){

username = rs.getString(″user_name″); password = rs.getString(″pass_word″);

}

One of the most important methods in ResultSet class is the getObject(). The advantage of using this method is that a returned datum, which is stored in a ResultSet object and its data type is unknown (a datum is dynamically created), can be automatically converted from its SQL data type to the ideal Java data type. This method outperform any other getXXX()method, since the data type of returned data must be known before a suitable getXXX()method can be used to fetch the returned data.

The findColumn()method is used to find the index of a column if the name of that column is given, and the close()method is used to close a ResultSet instance.

The getMetaData()method is a very good and convenient method, and it allows users to have a detailed and clear picture about the structure and properties of data returned to a ResultSet. A ResultSetMetaData object, which contains all pieces of necessary information about the returned data stored in a ResultSet instance, is returned when this method is executed. By using different methods of the ResultSetMetaData interface, we can obtain a clear picture about the returned data. For example, by using the getColumnCount()method, we can know totally how many columns have been retrieved and stored in the ResultSet. By using getTableName(), getColumnName(), and getColumnType(), we can know the name of the data table we queried, the name of column we just fetched, and the data type of that column. A more detailed discussion about the ResultSetMetaData component will be given in the following sections.

4.2.5 Using JDBC MetaData Interfaces

In addition to general and popular data information provided by three statement interfaces and execution methods, JDBC also provides useful and critical information and descriptions about the database, running result set and parameters related to the JDBC drivers and database applications. All of these properties, structures and descriptions can be categorized into three interfaces of so-called metadata interfaces, or

1.ResultSetMetaData interface

2.DatabaseMetaData interface

3.ParameterMetaData interface

In the following sections, we will concentrate on these three interfaces to illustrate how to use these interfaces to retrieve detailed descriptions and structures, as well as properties related to the data action components, such as ResultSet, database, and parameters to facilitate database applications.

Let’s start from the ResultSetMetaData interface.

4.2.5.1 Using the ResultSetMetaData Interface

In Section 4.2.4, we discussed how to retrieve running result stored in a ResultSet object and important methods of this interface. By using different fetching methods, either fetching by rows or columns, we can easily retrieve a whole set of returned results stored in a

146 Chapter 4 JDBC Application Design Considerations

ResultSet object. However, in some applications, we may need more detailed information and properties about the returned result set, such as the total number of columns returned, each column name and data type, as well as some other structure information related to the returned result set. By using these structure information and properties, we can get a clear and full picture about the returned ResultSet, and furthermore enable us to retrieve our desired data information more directly and conveniently. With the help of the metadata provided by the ResultSetMetaData, you can develop entire database applications without even knowing what RDBMS, table, or type of data to be accessed.

The ResultSetMetaData interface provides a collection of information about the structure and properties related to the returned ResultSet object, and this give us a possibility to perform the functions we described above.

The ResultSetMetaData interface contains more than 20 methods, and Table 4.10 shows 16 most popular methods.

It can be found from Table 4.10 that the top 10 methods in a ResultSetMetaData object are mainly used to retrieve the structure and properties for the specified column with the column index as an argument. The rest of methods that return a Boolean value are used to determine some important properties that describe special functions provided by the database engine for the selected column. One of the advantages of using this metadata is that you can build dynamic applications that are independent of the data source. One possible way to achieve this is to remove the need for all direct column name references.

Table 4.10. Methods defined in the ResultSetMetaData interface

Method	Function
getCatalogName(int index)	Determine the name of the catalog that contains the
	referenced column
getColumnCount()	Return the total number of columns contained in the
	ResultSet object
getColumnDisplaySize(int index)	Return the maximum display width for the selected column
getColumnLabel(int index)	Return the preferred display name for the selected column
getColumnName(int index)	Return the name of the column for the selected column
getColumnType(int index)	Return the SQL data type for the selected column
getPrecision(int index)	Return the precision used for the selected column
getScale(int index)	Return the scale used for the selected column
getSchemaName(int index)	Return the name of the schema that contains the selected
	column
getTableName(int index)	Return the name of the table that contains the selected
	column
isAutoIncrement(int index)	Determine if the column is automatically numbered by the
	database (autonumber)
isCurrency(int index)	Determine if the column represents currency
isNullable(int index)	Determine if the column is able to accept null values
isSigned(int index)	Determine if the column contains signed numbers
isWritable(int index)	Determine if the column is writable by the user
isReadOnly(int index)	Determine if the column is read-only

4.2 JDBC Applications Fundamentals 147

ResultSet rs = pstmt.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

While (rs.next()){

for (int m = 1; m< rsmd.getColumnCount(); m ++)

{

System.out.println( rs.getString(m));

}

}

Figure 4.17. An example coding of using the getColumnCount() method.

Because of the space limitation, we can only provide a brief discussion for some important methods that are widely implemented in most database applications.

After a data query is executed and a ResultSet object is returned, before we can retrieve our desired data from the ResultSet, we may need to get some structure information and properties related to columns we preferred. One of the most important properties is the total number of columns returned in the ResultSet object. By using the getColumnCount() method, we can get not only the total number of columns, but also the content of each column easily. Figure 4.17 shows a piece of example codes to illustrate how to use this method to scan the entire ResultSet to retrieve each column from it.

The first coding line is used to create a ResultSet object by executing the executeQuery() method. Then a ResultSetMetaData object rsmd is created by calling the getMetaData() method defined by the ResultSet interface. To pick up each returned column, a while loop is used combined with the next()method. By using this piece of codes, you even do not need to know how many columns returned in that ResultSet, and what are the names for each column; in other words, you do not have to have prior knowledge about the table and database—you can retrieve all columns with their exact names! Yes, that is easy and fancy.

In some applications, you may need to know some other useful information about the columns, such as the data type of each column, the width of each column, the precision and scale of the selected column if a floating point or double data is stored in that column. To get those properties, you can call the appropriate methods, such as getColumnType(), getColumn-DisplaySize(), getPrecision() and getScale().

Besides to get some important information and properties about the returned ResultSet, sometimes, we may need to get similar information for the connected database. In that case, you may need to use the DatabaseMetaData interface.

4.2.5.2 Using the DatabaseMetaData Interface

Compared with other metadata interfaces, the DatabaseMetaData is the largest metadata interface, with over 150 methods.This interface is mainly used for by those developers who are building database applications that need to be fully RDBMS independent, which means that the developers do not need to know anything about the database or do not have prior knowledge about the database they are using. In this way, the users can discover and retrieve structures and properties of the RDBMS dynamically as the application runs.

148 Chapter 4 JDBC Application Design Considerations

To create a DatabaseMetaData object, one needs to call the getMetaData() method defined in the Connection interface.

Relatively speaking, the ResultSetMetaData interface allows you to discover the structure of tables and properties of columns, but the DatabaseMetaData interface enables you to dynamically determine properties of the RDBMS. Table 4.11 shows some 16 most popular and important methods widely implemented by the DatabaseMetaData interface.

These 16 methods can be divided into seven groups based on their functionalities:

1.Catalog Identification Methods

2.Database Identification Methods

3.Driver Identification Methods

4.Stored Procedure-Related Methods

5.Schema Identification Methods

6.Table Identification Methods

7.Database-Related Parameters Methods

To get the name and version of the current database being used, the getDatabaseProduct-Name() and getDatabaseProductVersion() methods can

Table 4.11. Popular methods defined in the DatabaseMetaData interface

Method	Function
getCatalogs()	Return a ResultSet containing a list of all catalogs available in
	the database
getCatalogTerm()	Determine what the database specific name for Catalog is
getDatabaseProductName()	Return the name of the database product
getDatabaseProductVersion()	Return the database revision number
getDriverName()	Return the name of the driver
getDriverVersion()	Return the revision number of the driver
getPrimaryKeys(String catalog,	Return a ResultSet describing all of the primary keys within a
String schema, String table)	table
getProcedures(string catalog,	Return a ResultSet describing all stored procedures available
String schPatt, String proPatt)	in the catalog
getProcedureTerm()	Determine the database specific term for procedure
getSchemas()	Return a ResultSet containing a list of all schemas available in
	the database
getSchemaTerm()	Determine the database specific term for schema
getTables(String catalog, String	Return a ResultSet containing a list of all tables available
schePatt, String tablePatt,	matching the catalog, schema, and table type selection
String[] types)	criteria
getTableTypes()	Return a ResultSet listing the table types available
getTypeInfo()	Return a ResultSet describing all of the standard SQL types
	supported by the database
getURL()	Return the current URL for the database
getUserName()	Return the current user name used by the database