Practical Database Programming With Java

4.2 JDBC Applications Fundamentals 119

4.2.2.2 Using the DriverManager.getConnection() Method

When using the first method DriverManager.getConnection() to establish a database connection, it does not immediately try to do this connection; instead, in order to make this connection more robust, it performs a two-step process. The getConnection() method first checks the driver and Uniform Resource Locator (URL) by running a method called acceptsURL()via DriverManager class to test the first driver in the driver list; if no matched driver returns, the acceptURL()method will go to test the next driver in the list. This process continues until each driver is tested or until a matched driver is found. If a matched driver is found, the Driver.connect() method will be executed to establish this connection. Otherwise, a SQLException is raised.

It looks like that this two-step connection is not efficient enough; however, a more robust connection can be set if more than one driver is available in the driver list.

The purpose of the acceptsURL() method is to check whether the current driver is able to open a valid connection to the given URL or not. This method does not create a real connection or test the actual database connections; instead, it merely examines the subprotocol of the URL and determine if it understands its syntax. In this way, it can effectively reduce the chance of the misconnection and make sure the correctness of an established connection.

4.2.2.3 Using the Driver.connect() Method

The Driver.connect()method enable you to create a actual connection to the desired database and returns an associated Connection object. This method accepts the database URL string and a Properties object as its argument. An URL indicates the protocol and location of a data source, while the properties object normally contains the user login information. One point to be noted is that the only time you can use this Driver. connect()method directly is when you have created a new instance of the Driver class.

A null will be returned if an exception occurs when this Driver.connect() method is executed, which means that something went wrong during this connection operation.

Comparing the DriverManager.getConnection() method with this Driver. connect() method, the following conclusions can be obtained:

•The DriverManager.getConnection() method can perform checking and testing each driver in the driver list automatically for all loaded drivers. As soon as a matched driver is found, it can be connected to the database directly by using Driver.connect() method. This automatic process will greatly reduce the processing time.

•The DriverManager.getConnection()method has looser requirements for the arguments passed with this method. When applying the Driver.connect()method, you have to pass two arguments, the URL as a string, and the login properties as a Properties object with strict syntax and grammar requirements. However, when using the DriverManager.getConnection()method, you can define login properties as either String, a Properties object, or even a null string, since the DriverManager can handle the converting these arguments to the appropriate Properties object when it is applied.

From this comparison, it can be found that the DriverManager. getConnection()method is over the Driver.connect()method; therefore, we will use this method to do our database connection in all example projects in this book.

120 Chapter 4 JDBC Application Design Considerations

After a driver has been loaded and registered, the next step is to establish a database connection using a URL. Before we can continue on the database connection, we need to have a clear picture and understanding about the JDBC connection URL.

4.2.2.4 The JDBC Connection URL

The JDBC URL provides all information for applications to access to a special resource, such as a database. Generally, a URL contains three parts or three segments: protocol name, sub-protocol and subname for the database to be connected. Each of these three segments has different function when they worked together to provide unique information for the target database.

The syntax for a JDBC URL can be presented as:

protocol:sub-protocol:subname

The protocol name works as an identifier or indicator to show what kind of protocol should be adopted when connected to the desired database. For a JDBC driver, the name of the protocol should be jdbc. The protocol name is used to indicate what kind of items to be delivered or connected.

The subprotocol is generally used to indicate the type of the database or data source to be connected, such as sqlserver or oracle.

The subname is used to indicate the address to which the item is supposed to be delivered or the location of the where database resides. Generally, a subname contains the following information for an address of a resource:

•Network host name/IP address

•The database server name

•The port number

•The name of the database

An example of a subname for our SQL Server database is:

localhost\\SQLEXPRESS:5000

The network host name is localhost, and the server name is SQLEXPRESS, and the port number the server used is 5000. You need to use a double slash, either forward or back, to represent a normal slash in this URL string since this is a DOS-style string.

By combining all three segments together, we can get a full JDBC URL. An example URL that is using a SQL Server JDBC driver is:

jdbc:sqlserver//localhost\\SQLEXPRESS:5000

The database’s name works as an attribute of the connected database.

Now that we have a clear picture about the JDBC URL, next, let’s connect our application to our desired database.

4.2.2.5 Establish a Database Connection

Now,we have a clear picture and understanding about the fundamentals in DriverManager and Driver classes, as well as related database connection methods. As we discussed in

4.2 JDBC Applications Fundamentals 121

……..

//A driver has been successfully loaded and registered

String url = "jdbc:sqlserver://localhost\\SQLEXPRESS:5000;databaseName=CSE_DEPT;";

//String url = "jdbc:sqlserver://localhost\\SQLEXPRESS:5000;

//databaseName=CSE_DEPT;user=cse;password=mack8000";

//Establish a connection try {

con = DriverManager.getConnection(url,"cse","mack8000");

//con = DriverManager.getConnection(url); con.close();

}

catch (SQLException e) {

System.out.println("Could not connect! " + e.getMessage()); e.printStackTrace();

}

Figure 4.4. An example coding for the database connection.

the previous sections, to connect to a database, two methods, DriverManager.getConnection() and Driver.connect(), can be used. However, as we know, the first method is better than the second one; therefore, in this section, we will concentrate on the use of the first method to establish a database connection.

Figure 4.4 shows a piece of example codes to establish a connection using the DriverManager.getConnection() method. This piece of codes should be a follow-up of the codes shown in Figure 4.3; in other words, a valid driver has been loaded and registered before the following connection can be established.

Since the DriverManager.getConnection() method is an overloading method with three different signatures, here we used two of them, and the first one is highlighted in bold and the second one is commented out.

To establish a database connection, a valid JDBC URL is defined in the first coding line with the following components:

•The protocol name jdbc

•The subprotocol sqlserver

•The subname localhost\\SQLEXPRESS:5000

•The database name CSE_DEPT

Then, a try…catch block is used to try to establish a connection using the getConnection() method with three arguments: URL, username and password. After a valid connection is established, a Connection object is returned, and this returned object has the following functions and properties:

1.The Connection object represents an SQL session with the database.

2.The Connection object provides methods for the creation of Statement objects that will be used to execute SQL statements in the next step.

3.The Connection object also contains methods for the management of the session, such as transaction locking, catalog selection, and error handling.

122 Chapter 4 JDBC Application Design Considerations

Table 4.3. Methods defined in the Connection interface

Method	Function
close()	Close the connection to the database
createStatement()	Create a Statement object for the execution of static SQL statements
getMetaData()	Retrieve all database-related information stored in the
	DatabaseMetaData object for the current connection
isClosed()	Determine if the referenced Connection has been
	closed—True = closed
prepareCall(String	Create a CallableStatement object for use with SQL stored
sqlString)	procedures
prepareStatement(String	Create a PreparedStatement object for use with SQL dynamic
sqlString)	queries
commit()	Immediately commits all transactions to the database. All updates
	and changes are made permanent

By definition, the responsibility of a Connection object is to establish a valid database connection with your Java application, and that is all. The Connection object has nothing to do with the SQL statement execution. The SQL statement execution is the responsibility of the Statement, PreparedStatement, and CallableStatement objects. As we mentioned, both PreparedStatement and CallableStatement are subclasses of the Statement class, and they play different roles for the statement execution.

In the next coding line in Figure 4.4, a close() method that belongs to the Connection class is called to try to close a connection. In fact, it is unnecessary to close a connected database in actual applications. However, we used this method here to show users a complete picture of using the Connection object, which means that you must close a connection when it is no longer to be used in your application (even in the connection pooling situation, but it will not be really closed instead it is placed into a pool), otherwise a running error may be encountered when you reuse this connection in the future.Therefore, this coding line is only for the testing purpose, and should be removed in a real application.

The catch block is used to detect any possible exception and display them if any of them occurred.

A Connection class contains 19 methods, and Table 4.3 lists the seven most popular methods.

Now that a valid database connection has been established, the next step is to execute the SQL statements to perform data actions against our connected database.

4.2.3 Executing Statements

To successfully execute an appropriate Statement object to perform SQL statements, the following operational sequence should be followed:

1.Creating a Statement object based on the requirement of the data actions

2.Calling the appropriate execution method to run the SQL statements

4.2 JDBC Applications Fundamentals 123

In a simple word, the Statement object is used for executing a static SQL statement and returning the results stored in a ResultSet object.

4.2.3.1 Overview of Statement Objects and Their Execution Methods

By using the Connection object, three separate statement objects can be created, and they are:

•Statement object

•PreparedStatement object

•CallableStatement object

The Statement object is used to execute static SQL queries. The so-called static statements do not include any IN or OUT parameters in the query string and do not contain any parameters passing to or from the database.

The Statement interface contains more than 18 methods, and Table 4.4 lists the 10 most popular methods.

Among those 10 methods in the Statement interface, three execution methods, including the executeQuery(), executeUpdate() and execute(), and getResultSet() method, are often used in Java database applications.

Table 4.4. Methods defined in the Statement interface

Method	Function
close()	Close the Statement and release all resources including the ResultSet
	associated with it
execute(String	Execute an SQL statement that may have an unknown number of
sqlString)	results. A return of True means that the first set of results from the
	sqlString execution is a ResultSet. If the execution resulted in either
	no results or an update count, a False is returned
executeQuery(String	Execute an SQL Select statement. A ResultSet object that contained
sqlString)	the query results from the database will be returned
executeUpdate(String	Execute an SQL Update, Insert or Delete statement. An integer will be
sqlString)	returned to indicate the number of rows that have been affected
getMaxRows()	Determine the maximum number of rows that can be returned in a
	ResultSet object
getMoreResults()	Move to the Statements next result. Only in conjunction with the
	execute statement and where multiple results are returned by the
	SQL statement. A False is returned if the next result is null or the
	results are an update count
getResultSet()	Return the current result set for the Statement. Only used in
	conjunction with execute() method. The current ResultSet object will
	be returned
getUpdateCount()	Return the number of rows affected by the last SQL statement. Is only
	meaningful for INSERT, UPDATE, or DELETE statements
setCursorName(String	Set the cursor name to be used by the Statement. Only useful for
name)	databases that support positional updates and deletes
setMaxRows(int rows)	Set the maximum number of rows that can be returned in a ResultSet.
	If more results are returned by the query, they are truncated

124 Chapter 4	JDBC Application Design Considerations
Table 4.5. Methods defined in the PreparedStatement interface
Method	Function

clearParameters()

execute()

executeQuery()

executeUpdate()

getMetaData()

getParameterMetaData()

setBoolean(int index, Boolean value) setByte(int index, Byte value) setDouble(int index, double value) setFloat(int index, float value) setInt(int index, int value) setLong(int index, long value) setNull(int index, int sqlType) setObject(int index, Object obj)

setShort(int index, short value) setString(int index, String value) setTime(int index, Time value)

Clear all parameters associated with a PreparedStatement. After execution of this method, all parameters have the value null

Execute the associated SQL Statement when the number of results returned is unknown. A False is returned if the returned result is null

Execute an SQL Select statement. A ResultSet object that contained the query results from the database will be returned

Execute an SQL Update, Insert, or Delete statement. An integer will be returned to indicate the number of rows that have been affected

Return a set of metadata for the returned ResultSet object

Return the number, types and properties of this PreparedStatement object’s parameters

Bind a Boolean value to an input parameter Bind a byte value to an input parameter Bind a double value to an input parameter

Bind a floating point value to an input parameter Bind an integer value to an input parameter Bind a long value to an input parameter

Bind a null value to an input parameter

Bind an Object to an input parameter. The Object will be converted to an SQL data type before being sent to the database

Bind a short value to an input parameter Bind a String value to an input parameter Bind a Time value to an input parameter

The PreparedStatement is a subclass of the Statement, and it is mainly used to execute dynamic SQL queries with IN parameter involved. These kind of statements can be preparsed and precompiled by the database, and therefore have faster processing speed and lower running loads for the database server.

The PreparedStatement interface contains more than 20 methods, and Table 4.5 lists 17 most popular methods.

It can be found from Table 4.5 that three execution methods, execute(), executeQuery(), and executeUpdate(), look like a duplication with those methods defined in the Statement interface. However, a significant difference is: all of these three methods defined in the Statement interface have their query strings as an argument when these methods are executed, which means that the SQL statements have to be defined in those query strings, and should be passed into the database as the arguments of those methods. In contrast, all three methods defined in the PreparedStatement interface have no any argument to be passed into the database when they are executed. This means

4.2 JDBC Applications Fundamentals 125

that the SQL statements have been built and passed into the database by using the

PreparedStatement object before these three methods are executed.

Two methods belong to the getters that are used to retrieve the metadata for the

ResultSet and the ParameterMetaData objects. Both methods are very useful when the developer wants to get more detailed structure and properties information about a returned ResultSet object or ParameterMetaData object.

More than 10 methods defined in the PreparedStatement interface are setter method, which means that these methods are used to set up an appropriate value to an input parameter with different data types. These methods are especially useful when a dynamic query is built, with one or more dynamic input parameters need to be determined in the SQL statements.

The CallableStatement is also a subclass of the Statement and the PreparedStatement classes, and it is mainly used to execute the stored procedures with both IN and OUT parameters involved. As we know, stored procedures are built and developed inside databases, and therefore have higher running and responding efficiency in data queries and processing.

This interface is used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter, and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output, or both. Parameters are referred to sequentially, by number or position, with the first parameter being 1.

{?= call <procedure-name>[(<arg1>,<arg2>, . . . )]} {call <procedure-name>[(<arg1>,<arg2>, . . . )]}

The IN parameter values are set using the setXXX() methods inherited from the interface PreparedStatement. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the getXXX() methods defined in this CallableStatement interface.

A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from the Statement interface.

The CallableStatement interface contains more than 30 methods, and Table 4.6 lists 15 most popular methods.

The registerOutParameter() method is an overloading method with two signatures, and these methods are used to declare what SQL type the OUT parameter will return when a CallableStatement method is executed.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a Statement’s current ResultSet object if an open one exists.

The Statement interface contains three important query methods with different functions; executeQuery(), executeUpdate(), and execute(). For each method, different operations can be performed, and different results will be returned.

Generally, the query methods can be divided into two categories; (1) the query method that needs to perform data query, such as executeQuery(), which returns an

126 Chapter 4 JDBC Application Design Considerations

Table 4.6. Methods defined in the CallableStatement interface

Method	Function
getBigDecimal(int index,	Return the value of parameter specified by the parameter index
int scale)	number as a BigDecimal
getBoolean(int index)	Return the value of parameter specified by the parameter index
	number as a Boolean
getByte(int index)	Return the value of parameter specified by the parameter index
	number as a byte
getBytes(int index)	Return the value of parameter specified by the parameter index
	number as an array of bytes
getDouble(int index)	Return the value of parameter specified by the parameter index
	number as a double
getFloat(int index)	Return the value of parameter specified by the parameter index
	number as a floating point number
getInt(int index)	Return the value of parameter specified by the parameter index
	number as an integer
getLong(int index)	Return the value of parameter specified by the parameter index
	number as a long integer
getObject(int index)	Return the value of parameter specified by the parameter index
	number as an Object. The object type is determined by the default
	mapping of the SQL data type to Java data type
getShort(int index)	Return the value of parameter specified by the parameter index
	number as a short integer
getString(int index)	Return the value of parameter specified by the parameter index
	number as a String object
getTime(int index)	Return the value of parameter specified by the parameter index
	number as a Time object
registerOutParameter(int	Register the specified output parameter to receive the SQL data
index, int slqType)	type indicated by the argument passed.
registerOutParameter(int	Register the specified output parameter to receive the SQL data
index, int slqType, int	type indicated by the argument passed. If the output is registered
scale)	as either DECIMAL or NUMERIC, the scale of the value may
	also be specified
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

instance of ResultSet that contained the queried results, and (2) the query method that does not perform data query and only return an integer, such as executeUpdate(). An interesting method is the execute(), which can be used in either ways.

Let’s first concentrate on the creation of the Statement objects based on the different requirements of data actions.

4.2.3.2 Using the Statement Object

As we discussed in the last section, three separate statement objects can be created based on three different data actions: Statement, PreparedStatement and CallableStatement. Let’s discuss how to create a Statement object first.

4.2 JDBC Applications Fundamentals 127

4.2.3.2.1 Creating the Statement Object The Statement object is the most common type of object and is easy to use in a static data query. The shortcoming of using this Statement object is that all SQL statements must be predefined with definite parameters when a Statement object is created. In other words, by using a Statement object to execute a SQL statement, no parameter can be passed into or from the database.

The Statement object is created by using the createStatement() method defined in the Connection interface (refer to Table 4.3). Figure 4.5 shows an example coding for the creation of a Statement object.

The coding line that is used to create a Statement object has been highlighted in bold. All other lines are prerequisite codes that are used to load and register a driver, establish a connection using the URL, and build a SQL query string.

4.2.3.2.2 Executing the Statement Object To execute the created Statement object to perform a data action, you need to call one of the execution methods defined in the Statement interface shown in Table 4.4. Figure 4.6 shows an example coding for the execution of an SQL query with this Statement object.

The coding line that is used to execute a Statement object has been highlighted in bold. All other lines are prerequisite codes that are used to load and register a driver, establish a connection using the URL, build a SQL query string, and create a Statement object. It can be found from this piece of codes that no parameter can be passed to or from the database when this query is executed. Therefore the Statement object can only be used to perform static queries.

To overcome this shortcoming, we need to use PreparedStatement objects to perform dynamic queries with varied input parameters.

4.2.3.3 Using the PreparedStatement Object

To perform dynamic SQL statements, we need to use a PreparedStatement object. Generally to use a PreparedStatement object to perform a dynamic SQL statement includes the following steps:

String url = "jdbc:sqlserver://localhost\\SQLEXPRESS:5000;databaseName=CSE_DEPT;";

//Establish a connection try {

con = DriverManager.getConnection(url,"cse","mack8000");

}

catch (SQLException e) {

System.out.println("Could not connect! " + e.getMessage()); }

String query = "SELECT user_name, pass_word FROM LogIn”; try{

Statement stmt = con.createStatement();

}

catch (SQLException e) {

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

Figure 4.5. An example coding for the creation of a Statement object.

128 Chapter 4 JDBC Application Design Considerations

import java.sql.*; static Connection con; try {

//Load and register SQL Server driver Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

}

catch (Exception e) {

System.out.println("Class not found exception!" + e.getMessage()); }

String url = "jdbc:sqlserver://localhost\\SQLEXPRESS:5000;databaseName=CSE_DEPT;";

//Establish a connection try {

con = DriverManager.getConnection(url,"cse","mack8000");

}

catch (SQLException e) {

System.out.println("Could not connect! " + e.getMessage()); }

String query = "SELECT user_name, pass_word FROM LogIn”; try{

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(query);

}

catch (SQLException e) {

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

Figure 4.6. An example coding for the execution of a Statement object.

1.Create a PreparedStatement object

2.Set data types and values to the associated input parameters in the query string

3.Call appropriate execution method to perform this dynamic query

Let’s first concentrate on the creation of a PreparedStatement object.

4.2.3.3.1 Creating the PreparedStatement Object Refer to Table 4.3; the prepare Statement() method defined in the Connection interface is used to create a

PreparedStatement object. An example code to create a PreparedStatement object looks like:

PreparedStatement pstmt = con.prepareStatement(query);

Unlike Statement objects, the PreparedStatement object takes the SQL statement to be executed as an argument. For dynamic SQL statements that contain input parameters to be passed into the database, you need to define a position for each input parameter in the query string. Regularly, a placeholder is used to inform the database that it can expect a variable in that position. Each placeholder that holds a position for a variable in the query string is represented by a question mark “?”, which holds a place for the associated variable during compiling time. When compiled, the placeholder is part of the statement and therefore appears static to the compiler. In this way, no matter what value is later assigned to the variable, the database does not need to recompile the statement. At the runtime, you can assign values to the variables by using any setXXX() method defined in the PreparedStatement interface shown in Table 4.5.