Sending SQL Statements

The connection object sends SQL statements to the database engine. There are different methods to perform this, depending on the kind of operation needed. In this chapter, I focus on sending normal SQL statements. Unlike prepared statements or calls to stored procedures, which are discussed in

Building statements

The step that directly follows the creation of the connection is the creation of a SQL statement. This doesn’t mean that it is forbidden to build a SQL query string before opening the connection. You are free to do this. The exact meaning is that a JDBC statement is an object associated to a connection, and it will be used later to request an execution of a SQL string within this connection environment space in the DBMS.

As shown in Figure 6-3, the step order is very simple.

Figure 6-3: Overview: Building a SQL statement.

Closing the SQL statement releases all the data associated with the statement. The following is the method to build a statement object:

Connection’s Factory Method for Statement Objects

Statement createStatement();

The statement object is obtained by calling this method on the connection instance, as shown in Listing 6-7.

Listing 6-7: Creating a Statement

... ... Connection myConnection = DriverManager.getConnection(url,                           "javauser", "hotjava"); Statement myStatement = myConnection.createStatement(); myConnection.close(); ... ...

Sending statements

The SQL statement is sent to the DBMS, where it is parsed, optimized, and executed. But you haven’t yet built the statement text. Indeed, the SQL string passes to the database when the call for execution of the statement is issued, as shown in Figure 6-4.

Figure 6-4: Sending a SQL statement.

The driver converts this SQL string into the DBMS native SQL grammar. It is possible to see the converted string without sending it to the database, as shown in the following. Usually, you won’t use this facility, but sometimes it is necessary to know what the native translation of a query is prior to sending it.

Connection’s Method to Print Native SQL

String nativeSQL(String sql);

The method is applied to the connection object because it is DBMS-dependent. Indeed, a connection is associated to one and only one DBMS through its driver.

The string passed as an argument is the “user” SQL statement; nativeSQL() returns the native form of this statement. The native form is the form understood by the underlying database. Drivers usually translate SQL statements to native forms when necessary. Listing 6-8 shows the language to obtain the native SQL translation.

Listing 6-8: Native SQL Translation

... ... Connection myConnection = DriverManager.getConnection(url, "javauser", "hotjava"); Statement myStatement = myConnection.createStatement(); String myQuery("SELECT * FROM employees ORDER BY salary DESCENDING"); System.out.println("This query: " + myQuery); System.out.println("is sent to the DBMS as: " + myConnection.nativeSQL(myQuery)); myConnection.close(); ... ...

All the previous steps, opening a connection and creating a statement, are necessary before executing a query. None of them can be skipped. Figure 6-5, although still somewhat simple, shows the current action sequence.

Figure 6-5: An overview of sending a SQL statement.

The method chosen to send a SQL statement to the database depends on the type of statement and type of data it returns. Statements can be composed of SQL queries, SQL updates, or both. There are specific methods to handle each case, as shown in the following:

Statement’s Execute methods

ResultSet executeQuery(String sql); int executeUpdate(String sql); boolean execute(String sql);

If the query returns rows of data, you should use executeQuery(). In this case, the query is typically a static SQL SELECT statement. The SQL text is simply passed as a string argument. It doesn’t have to be translated to the native form with nativeSQL(). This method returns a ResultSet object, which is discussed in the section "Handling Results," later in this chapter.

The method executeUpdate(String sql) is used for SQL statements that return an update count, such as SQL updates, inserts, and deletes. If the SQL statement returns nothing (returning nothing is different than returning zero [0] rows) or returns an integer value, as is the case with SQL INSERT, UPDATE, or DELETE clauses, you should use executeUpdate, as in the second line of the preceding. The call returns the integer value or 0 (zero) for statements that return nothing.

When a SQL statement returns more than one result, execute() has to be used to request execution of the statement, as in the third line of the preceding (boolean execute(String sql);). The subsection "Managing multiple result types" discusses this issue in detail.

Listing 6-9 shows how to execute a query.

Listing 6-9: Executing a Query

// how to execute a query import java.sql.*; class SimpleExample {          public static void main(String args[]) {                   String url = "jdbc:odbc:mysource";                   try {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                                   DriverManager.getConnection(url,                                   "javauser", "hotjava");                           Statement myStatement =                                   myConnection.createStatement();                           ResultSet rs = myStatement.executeQuery(                                   "SELECT name, id, salary FROM employees" +                                   " ORDER BY salary DESC");                           myConnection.close();                   } catch(java.lang.Exception ex) {                           ex.printStackTrace();                   }          } } 

The example in Listing 6-10 illustrates the executeUpdate() method:

Listing 6-10: Performing an Update

// how to execute an update import java.sql.*; class SimpleExample {          public static void main(String args[]) {                   String url = "jdbc:odbc:mysource";                   try {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                                   DriverManager.getConnection(url,                                   "javauser", "hotjava");                           Statement myStatement =                                   myConnection.createStatement();                           int res = myStatement.executeUpdate("UPDATE" +                                   "employees SET salary = salary*1.1" +                                   "WHERE id = 1");                           myConnection.close();                   } catch(java.lang.Exception ex) {                           ex.printStackTrace();                   }          } }

Closing statements

It is often necessary to close explicitly a statement after the DBMS executes it. Indeed, database context and JDBC statement resources stay open until the connection is closed or the statement is garbage-collected, depending on the driver. It isn’t desirable to consume resources when there is no reason to do so. Closing the statement also closes the returned result set.

Statement’s Closing Method

void close();

The close() method is simply called on a statement object and takes no arguments.

Storing BLOBs and CLOBs

It is sometimes necessary to record pictures, sound, or other multimedia files in a database. This kind of object is called a binary large object (BLOB). Although BLOB has absolutely nothing to do with Java objects (or C++ or any other object-oriented programming [OOP] language object), BLOB isn’t restricted to images, sound, and multimedia content. For example, a multikilobyte or multimegabyte text can be a BLOB. BLOBs don’t have to be ASCII or Unicode; they can be pure binary. Character-only large objects are called character large objects (CLOBs).

Insertion, update, or retrieval of very large values is usually performed by passing values in small chunks of data. This approach is often more convenient for programmers. JDBC, however, uses data streams. JDBC provides three kinds of streams: ASCII streams, Unicode streams, and binary streams.

Because there is no way to send streams within a simple SQL statement, the streams are passed as parameters. Refer to the dynamic SQL section to find out how to send BLOBs to the database.

Adjusting properties

There are properties that change the default behavior of JDBC or of the driver. They can be set before sending the SQL statement to the database.

Data truncation happens when inserting or updating data in the database. This truncation is dependent on the DBMS and driver that fixes the maximum size for data types. An error usually happens if the truncation occurs during a database write. JDBC provides a method to limit the size of a field to a maximum value, which is shown in the following; if the limit is exceeded, JDBC raises a SQLException.

Statement’s Maximum Field Size

void setMaxFieldSize(int max); int getMaxFieldSize();

The method setMaxFieldSize() enables the programmer to set a maximum field size that is valid for the current statement. The parameter is the number of bytes allowed.

The method getMaxFieldSize(), as shown in the second line of the preceding, returns the maximum size allowed for the current statement.

Listing 6-11 shows data truncation on write.

Note 

JDBC drivers perform data truncation on write when too many bytes are passed to a table’s column.

Listing 6-11: Data Truncation on Write

// data truncation on write import java.sql.*; class SimpleExample {          public static void main(String args[]) {                  String url = "jdbc:odbc:mysource";                  try {                          ...                          ...                          Connection myConnection =                                  DriverManager.getConnection(url, "javauser",                                  "hotjava");                          Statement myStatement = myConnection.createStatement();                          myStatement.setMaxFieldSize(12);                          int res = myStatement.executeUpdate("UPDATE                                  employees SET comment = ‘The quick br...’ WHERE                                  name=’jones’");                          ...                          ...                  } catch(java.lang.Exception ex) {                          ex.printStackTrace();                  }          } }

Setting time-outs

The execution of a statement may be delayed for one of several reasons. A mechanism called a time-out exists so that programmers have a way to handle such situations. The query time-out is the time the JDBC driver waits for a statement to execute. If the limit is exceeded, the driver raises an exception. This exception may be caught and one or more retries initiated. The following methods are available for dealing with time-outs:

Statement’s Time-outs

void setQueryTimeout(int seconds); int getQueryTimeout();

void setQueryTimeout(int seconds) is the method used to set a limit to the time-out mechanism. The parameter is the number of seconds that it waits before raising the SQLException. A zero value means no limit.

The method getQueryTimeout(), as shown in the second line of the preceding, returns the number of seconds corresponding to the time-out limit. A zero value again means that there is no limit and that the driver can wait forever if necessary.

Listing 6-12 provides an example of a time-out being set on the statement.

Listing 6-12: Setting a Statement Time-out

// time-outs ... ... Connection myConnection =          DriverManager.getConnection(url, "javauser", "hotjava");          Statement myStatement = myConnection.createStatement();          // we do not want to wait forever          myStatement.setQueryTimeout(10);          int res = myStatement.executeUpdate("UPDATE          employees SET salary = 1000000 WHERE name=’jones’"); ... ...

Another property may prove useful when sending data to the database: the setting for the escape sequence, as shown in the following.

Statement’s Escape Processing

void setEscapeProcessing(boolean enable);

This statement enables or disables escape substitution by the driver. Escape substitution is the default behavior and occurs before sending the SQL statement to the database. When enabled, the driver translates escape syntax strings to native SQL (see Chapter 7).