Executing SQL Statements

The easiest kind of SQL statements to use are DDL statements, including CREATE TABLE, ALTER TABLE, and DROP TABLE, and the DML statements INSERT, UPDATE, and DELETE that is, everything except SELECT. SELECT, because it returns data and sometimes a lot of data will be covered in a later section.

To execute an SQL statement, we will first obtain a Statement object from our Connection object. Statement has a number of methods that let us execute an SQL statement. The easiest method to use for nonquery SQL statements is executeUpdate(). The following code, which assumes we have a valid Connection object, conn, demonstrates how we can create and populate a table with a row of data.

 Statement stmt = conn.createStatement(); stmt.executeUpdate("CREATE TABLE EXAMPLE" +                    "(COL1 VARCHAR2(30)," +                    " COL2 VARCHAR2(30)," +                    "NUM1 NUMBER)"); int rows; rows = stmt.executeUpdate("INSERT INTO EXAMPLE VALUES" +                           "('Column 1', 'Column 2', 1)");      System.out.println("Rows updated: " + rows); 

The executeUpdate() method returns an int, indicating how many rows were updated by the SQL statement. In the case of a DDL, however, such as our CREATE TABLE statement, it always returns 0, so we can ignore the return value in that case.

Notice in the examples, also, that the SQL statement is not terminated with a semicolon. It is the responsibility of the JDBC driver to provide the proper termination.

The easiest way to include variables in SQL statements when using JDBC is to generate the SQL statement dynamically. Suppose that we wish to add another row to our table above and that we have the values in Java variables. We can compose the SQL statement and execute it as follows:

 String column1 = "ABC"; String column2 = "XYZ"; int i = 120; String sql = "INSERT INTO EXAMPLE VALUES ('" +      column1 + "', '" + column2 + "', " + i + ")";    rows = stmt.executeUpdate(sql); System.out.println("Updated: " + rows); 

This approach is straightforward but coding is somewhat tedious, and composing the strings requires careful attention to delimiters, especially because the strings will get parsed twice, according to two different sets of rules. The first thing to consider is how the Java compiler will interpret the string. Literal double quotes that are to be included as part of the SQL statement need to be escaped with a slash (\), for example. Let's suppose that we want to insert the string: He said "Here's Johnny!"

Java requires that we escape the double quotes like this:

 String s = "He said \"Here's Johnny!\"" 

The next thing to consider is that SQL expects a string to be delimited by single quotes and requires that a literal single quote (or apostrophe) within the constant be escaped by another single quote, like this:

 INSERT INTO EXAMPLE (COL1) VALUES ('He said "Here''s Johnny!"'); 

Writing this as a JDBC query means we need to take both sets of rules into account.

[View full width]
 rows = stmt.executeUpdate("INSERT INTO EXAMPLE (COL1) VALUES ('He said \"Here''s Johnny!\ "')"); 

Needless to say, this makes for strings that are hard to compose and interpret. One thing that can help is a helper method that will escape single quotes for SQL.

 public static String escSingleQuote(String sql) {   int start= 0;   int end = sql.indexOf('\'');   if(end<0)   {      return sql; // short-circuit   }   StringBuffer sb = new StringBuffer();   while(end>=0)   {     sb.append(sql.substring(start,end));     sb.append('\'');     start = end;     end = sql.indexOf("'", start + 1);   }   sb.append(sql.substring(start,sql.length()));   return sb.toString(); } 

Notice that this method uses a StringBuffer to construct the new string. This is because a regular String object is immutable. There are no methods that allow us to change it. If we are going to perform repeated concatenations on a string, it is better to use a StringBuffer, which is not immutable, and call the toString() method at the end.

Concatenating Strings Efficiently

When String objects in Java are joined using the "+" operator, the Java compiler creates a temporary StringBuffer object and calls the StringBuffer's append() method to perform the concatenation for each string being joined. At the end, it calls the Stringbuffer's toString() method to get back a String object. Consider the following code:

 String sql = "INSERT INTO EXAMPLE VALUES ("; sql = sql + "'ABC', "; sql = sql + "'XYZ',"; sql = sql + 123 + ")"; 

Though it is clear and easy to follow, it is inefficient because the Java compiler turns it into the equivalent of the following code:

 String sql = "INSERT INTO EXAMPLE VALUES ("; sql = new StringBuffer(sql).append("'ABC', ").toString(); sql = new StringBuffer(sql).append("'XYZ',").toString(); sql = new StringBuffer(sql).append(123).append(")").toString(); 

It is more efficient to do the concatenation in as few statements as possible.

 String sql = "INSERT INTO EXAMPLE VALUES (" +         + "'ABC', ";         + "'XYZ',";         + 123 + ")"; 

Alternatively, we can use a StringBuffer ourselves to construct the string in parts.

 StringBuffer sqlBuffer = "INSERT INTO EXAMPLE VALUES ("; SqlBuffer.append("'ABC', "); SqlBuffer.append(+ "'XYZ',"); SqlBuffer.append(123 + ")"); String sql = ssqlBuffer.toString(); 

This latter example isn't as efficient as the all-in-one statement, but if we need to construct an SQL statement piecemeal, based on intermediate results from other methods or queries, this is an adequate approach it's still an improvement over simply concatenating strings.

PreparedStatement

Yet another alternative to using a Statement object that includes variables by concatenating strings is to use a PreparedStatement instead. A PreparedStatement allows us to embed placeholders for variables in the SQL statement.

The placeholders for variables in a PreparedStatement are called bind variables and are similar to the host variables we used in SQLJ, but instead of specifying the Java host variable directly in the SQL statement, we use a question mark (?) for each variable, then call set methods to set the value of each one, using an index. This is called a parameterized SQL statement.

We define the parameterized SQL statement at the time we create the PreparedStatement, not when we execute it, as with Statement. So although a Statement object can be used repeatedly to execute arbitrary, different SQL statements, a PreparedStatement can be used to execute only a single SQL statement. Because the prepared SQL statement contains bind variables, it can be executed multiple times with different values.

 PreparedStatement ps = conn.prepareStatement(    "INSERT INTO EXAMPLE  VALUES( ?, ?, ?)"); 

To set the variables in the statement, there are a large number of set methods, of the form setXXX(), where XXX is a Java primitive type or class, such as int or String. Each set method takes a first parameter, an integer, that indicates which bind variable is to be set where the first bind variable is 1 and a second parameter, the value. Set methods include setInt(), setLong(), setFloat(), setDouble(), setString(), setDate(), and setTime().

Depending on the types of the columns in the EXAMPLE table, we might be able to set the three bind variables in the PreparedStatement above as follows:

 ps.setString(1, "Henry"); ps.setString(2, "Cow"); ps.setInt(3, 1968); 

Once all the variables are bound, the prepared statement can be executed.

 ps.executeUpdate(); 

Bind variables aren't used only for values to be inserted; they can also be used anywhere a text or numeric constant is valid in an SQL statement, such as in a WHERE clause:

 PreparedStatement ps = conn.prepareStatement(    "UPDATE EXAMPLE SET COL1=? WHERE COL2=?"); ps.setString(1,"Don''t have a"); ps.setString(2,"Cow"); ps.executeUpdate(); 

Statement versus PreparedStatement

Statement and PreparedStatement both have advantages. In general, Statement is simple, efficient, and is to be preferred, with the following exceptions:

When many variables are involved, PreparedStatement offers an advantage over Statement, because using a series of set methods is a better structured, less error-prone, and more efficient approach than converting everything to a string and concatenating strings. Because of this, PreparedStatements are most commonly used with nonquery statements such as INSERT and UPDATE.

When the same SQL statement can be reused many times by using bind variables, PreparedStatement offers a performance advantage, because the database needs to parse the SQL statement only once. (On the other hand, if a statement will be called only once or twice, Statement is more efficient because of the higher overhead associated with a PreparedStatement.)

In any case, do not let performance be the single guiding force in your choice. If performance is critical, you should test to ensure that the difference is enough to justify using an approach that isn't as good from a design, testing, or maintainability point of view.

CallableStatements

A CallableStatement is similar to a PreparedStatement, but it is used for calling functions and stored procedures in the database, rather than standard SQL DDL or DML statements. You may remember that both functions and stored procedures may have input parameters, output parameters, or parameters that are used for both input and output. (It is bad practice, however, to use output or input/output parameters with functions.) The difference between the two is that a function has a return value but a procedure does not. To allow the use of both input and output parameters, CallableStatement not only has methods for setting parameters in the SQL statement, such as a PreparedStatement, but it also has methods for registering and obtaining output variables.

The Oracle CallableStatement implementation accepts two types of syntax: Oracle-specific PL/SQL block syntax and the standard SQL-92 call syntax. Assuming that we want to call a function, func, that takes one parameter, the Oracle PL/SQL block syntax looks like this:

 "begin ? := func(?); end;" 

The notable features are that it begins with the keyword begin, followed by the assignment and function call, a semicolon, and the keyword end. Input and output parameters are indicated with question marks.

The SQL-92 call syntax for the same function looks like this:

 "{? = call func(?)}" 

The notable features about this syntax are that the entire statement is enclosed by braces and that the function name is immediately preceded by the keyword call. Parameters are indicated with question marks, as with the PL/SQL syntax.

In the examples that follow, we'll use the standard SQL-92 syntax.

Calling a Procedure

Suppose we want a procedure that looks up someone's first name if we provide the last name. Further suppose that we want to know the first name only if it is unambiguous if there is only one person with that last name. We'll define a PL/SQL stored procedure with three parameters, one input parameter so that we can provide the last name and two output parameters: one for the first name (if unique) and one for the count of people having the last name we provided. To create the procedure, enter the following at an SQL prompt:

 CREATE OR REPLACE PROCEDURE GET_FIRST_NAME(                             IN_LNAME  IN  VARCHAR2,                             OUT_FNAME  OUT VARCHAR2,                             OUT_COUNT OUT NUMBER) AS BEGIN   SELECT COUNT(*) INTO OUT_COUNT FROM NAMES         WHERE LAST_NAME LIKE IN_LNAME;   IF (OUT_COUNT = 1) THEN     SELECT FIRST_NAME INTO OUT_FNAME FROM NAMES         WHERE LAST_NAME LIKE IN_LNAME;   END IF; END; / 

To call this procedure from Java, we first create a CallableStatement object as follows using the SQL-92 syntax:

 CallableStatement cs = conn.prepareCall(       "{call GET_FIRST_NAME(?, ?, ?)}"); 

We set the first parameter, which is an input parameter.

 cs.setString(1, "Smith"); 

To set the output parameters, we use the registerOutputParameter() method. This takes two parameters, the position of the output parameter and the type of the output parameter. The types are defined in the java.sql.Types class. For the name variable, because Oracle's VARCHAR2 is not a standard SQL type, we'll use Types.CHAR.

 cs.registerOutParameter(2, Types.CHAR); 

We'll register the count variable as Types.INTEGER.

 cs.registerOutParameter(3, Types.INTEGER); 

Now we're prepared to call the procedure by calling the execute() method.

 cs.execute(); 

To allow us to obtain the values of the output parameters and function call return values, the CallableStatement has a large number of getXXX(), where XXX represents the type, such as String or int. Here is how we obtain the count of matches that the procedure returned and the name, if a single unique match was found.

 int count = cs.getInt(3); System.out.println("Count: " + count); String name=null; if(count==1) {   name = cs.getString(2); } System.out.println("name: " + name); 

Finally, at the end we close the CallableStatement and the connection.

 cs.close(); 

Calling a Function

The steps for calling a function are the same as for calling a procedure. We create the CallableStatement, using either the PL/SQL block syntax or the SQL-92 call syntax, set the input parameters with the appropriate getXXX() methods, and register the function's return variable, using the registerOutputParameter() method. Here we'll call an existing SQL function, SOUNDEX(), which, for a given name, returns a character string representing a phonetic approximation of the name (represented by a letter followed by a three-digit number). This is often used, for example, to look up a name in an electronic directory when the exact spelling is unknown.

 cs=conn.prepareCall("{? = call SOUNDEX(?)}"); cs.registerOutParameter(1, Types.CHAR); cs.setString(2,"HELLO"); cs.execute(); String soundex=cs.getString(1); System.out.println("Soundex=" + soundex); cs.close(); 

When to Use CallableStatements

CallableStatements have a significant overhead beyond that of straight SQL calls not to mention the added complexity of developing and maintaining a stored procedure or function in the database. They should generally be used only if the operation they perform requires multiple SQL statements or if the operation is one that is to be shared with other database client applications. See Chapter 5, "PL/SQL," for more information about creating and using stored procedures and Java stored procedures.