Database Access with JDBC

Advanced Database Concepts

Prepared Statements

A prepared statement is an SQL statement that is precompiled by the database. Through precompilation, prepared statements improve the performance of SQL commands that are executed multiple times (given that the database supports prepared statements). Once compiled, prepared statements can be customized prior to each execution by altering predefined SQL parameters.

A PreparedStatement object is created using the Connection object's prepareStatement() method. Question marks (" ? ") in the SQL are used as placeholders for dynamic parameters that may change each time the prepared statement is executed. Use the appropriate setXxx() method (based on the parameter's SQL data type) to set the value of each parameter prior to executing the statement (see Table 16.4). The following code demonstrates how to create and execute a prepared statement.

Table 16.4 Typical setXxx() Methods of the PreparedStatement Object

String sql = "insert into EMPLOYEE (DEPARTMENT_ID, " +
"FIRST_NAME, LAST_NAME, PHONE, EMAIL, HIRE_DATE) " + 
"values (?, ?, ?, ?, ?, ?)"; 
//create the prepared statement 
PreparedStatement pstmt = dbConn.prepareStatement(sql); 

//get java.sql.Date for HIRE_DATE field 
GregorianCalendar cal = 
(GregorianCalendar)Calendar.getInstance();
cal.set(2000, Calendar.DECEMBER, 28); 
java.sql.Date hireDate =(java.sql.Date)cal.getTime(); 

pstmt.clearParameters(); //clear previous parameter values 
pstmt.setInt(1, 1); 
pstmt.setString(2, "John"); 
pstmt.setString(3, "Public"); 
pstmt.setString(4, "555-2222"); 
pstmt.setString(5, "johnp@email.com"); 
pstmt.setDate(6, hireDate);  
pstmt.executeUpdate(); //execute the prepared statement

The previous code inserts a new row into the EMPLOYEE table using the PreparedStatement object. The clearParameters() method clears all previously set parameter values. The six setXxx() methods set the value of each of the insert parameters according to the specified parameter index. Each parameter is indexed, starting at one, according to the order in which it appears in the SQL statement (moving from left to right). Finally, the prepared statement is executed using the executeUpdate() method. In contrast, if the SQL statement had represented a SELECT query, the PreparedStatement object's executeQuery()method would have been used.

In addition to improving the performance of frequently executed SQL statements, prepared statements are useful in other areas. For instance, prepared statements allow large amounts of text and binary data to be stored in the database using the setXxxStream() methods. To illustrate, consider the EMPLOYEE_PHOTO database table, defined as follows:

CREATE TABLE EMPLOYEE_PHOTO (
EMPLOYEE_ID int NOT NULL, 
FIRST_NAME varchar(20) NULL, 
LAST_NAME varchar(20) NULL,  
PHOTO blob NULL);

Notice that the EMPLOYEE_PHOTO table contains a field called PHOTO that is defined as type blob . The MySQL database presented in this chapter supports a BLOB data type for the storage of binary data. The following code demonstrates how a binary image file can be stored in the EMPLOYEE_PHOTO table using the PreparedStatement object's setBinaryStream() method and then extracted from the database using the ResultSet object's getBinaryStream() method (java.io.* and java.sql.* are imported).

//INSERT ROW INTO EMPLOYEE_PHOTO TABLE (including photo)
String sql = "insert into EMPLOYEE_PHOTO values (?, ?, ?, ?)"; 

//create the prepared statement 
PreparedStatement pstmt = dbConn.prepareStatement(sql); 

 __//get handle to the image file to be sent to the database 
File file = new File("/photo.gif"); 
FileInputStream fileIn = new FileInputStream(file); 

//initialize prepared statement parameters 
pstmt.setInt(1, 1); 
pstmt.setString(2, "John"); 
pstmt.setString(3, "Public"); 
pstmt.setBinaryStream(4, fileIn, (int)file.length()); 
pstmt.executeUpdate(); //execute prepared statement 

fileIn.close(); //close input stream 

//EXTRACT PHOTO FROM DATABASE AND WRITE TO LOCAL FILE 
sql = "select PHOTO from EMPLOYEE_PHOTO where EMPLOYEE_ID = 1"; 
Statement stmt = dbConn.createStatement(); 
ResultSet rs = stmt.executeQuery(sql); 

if (rs.next()) 
{ 
//get input stream for reading binary data from the PHOTO field 
InputStream in = rs.getBinaryStream("PHOTO"); 

//get output stream used to write photo to the file system 
FileOutputStream fileOut = new FileOutputStream("/copy.gif"); 

int bytesRead = 0; 
byte[] buffer = new byte[4096]; //4k buffer for reading image 
while ((bytesRead = in.read(buffer)) "` -1) 
{ 
fileOut.write(buffer, 0, bytesRead); //write image to file 
} 
fileOut.close(); //close output stream  
}

Prepared statements are also useful for writing text containing special characters to the database without having to convert the characters to their appropriate escape sequences. For example, the apostrophe (') is commonly used to convey text field boundaries within an SQL statement as shown here:

insert into COMPANY values ('Sun Microsystems');

Imagine that this SQL was dynamically constructed by a servlet in response to a user entering his company name on a Web page. Now imagine the problem would result if the company name included an apostrophe. The resulting SQL that might look like this:

insert into COMPANY values ('Crazy Bob's Software Shack');

Since apostrophes are used as string delimiters in SQL, the database will assume that the quoted string was not terminated properly (since it appears to terminate twice), resulting in a syntax error. One way to resolve this problem would be to replace the apostrophe with its escape sequence. Most databases recognize two consecutive apostrophes as the escape sequence for a single embedded apostrophe (i.e., embedded in a text field). Therefore, the following rewritten SQL statement solves this problem.

insert into COMPANY values ('Crazy Bob''s Software Shack');

The database will automatically convert the double apostrophe into a single apostrophe and there is no ambiguity regarding the text field boundaries. Listing 16.6 presents a simple Java class containing a single static method that converts all single apostrophes within a String to double apostrophes.

Listing 16.6 DBQuote converts single apostrophes to double apostrophes.

public class DBQuote 
{ 
/** 
* Replaces all single quotes(') with double quotes ("). This 
* method is useful when dynamically building SQL statements. 
* 
* @param text String to check for single quotes 
* @return String after converting single quotes to double 
*     quotes 
*/ 
public static String parse(String text) 
{ 
StringBuffer textBuffer = new StringBuffer(); 

int pos = 0; 
while (true) 
{ 
int quoteIndex = text.indexOf("'", pos); 

if (quoteIndex == -1) 
{ 
textBuffer.append(text.substring(pos)); 
break; 
} 

textBuffer.append(text.substring(pos, quoteIndex)); 
textBuffer.append("''"); 

pos = quoteIndex + 1; 
} 
return textBuffer.toString(); 
} 
}

Passing all SQL text fields to the parse() method of the DBQuote class resolves the problem created by apostrophes within text fields that are used to construct an SQL statement. The DBQuote class can be used like this:

String name = "Crazy Bob's Software Shack";
String sql = "insert into COMPANY values ('" +  
DBQuote.parse(name) + "')";

The only problem with the DBQuote class is that it escapes only the apostrophes in a String . What about other special characters (such as ")? We could write a class that would search for every special character and replace it with its escape sequence, but fortunately, there is a simpler way. As mentioned earlier, prepared statements allow text to be sent to the database without escaping special characters. This characteristic of prepared statements can greatly simplify the database code within your servlets by automatically solving the problems normally associated with inserting special characters into the database. To illustrate, the following code uses a prepared statement to insert text into a database without concern for any special characters that may be included.

String sql = "insert into COMPANY values (?)";
PreparedStatement pstmt = dbConn.prepareStatement(sql); 
pstmt.setString(1, "Crazy Bob's Software Shack");  
pstmt.executeUpdate();

Transactions

There are times when a set of SQL statements must all be executed as one atomic action (i.e., as a single unit) in order to ensure database integrity. For instance, consider a banking example similar to the one presented in Chapter 11. Imagine a transfer between two bank accounts that requires two rows in a database table to be updated using the SQL UPDATE command. The first UPDATE debits some amount from one account and the second UPDATE credits a separate account for the same amount. Now imagine that an error occurs in the system between the first and second update operations that prevents the second update from completing successfully. If this happens, the database will be left in an invalid state. One account was debited but the other account was not credited. Therefore, the money that was to be transferred is lost. Fortunately, this type of database integrity problem can be remedied using database transactions.

A transaction is a set of SQL statements that are grouped such that all statements are guaranteed to be executed or the entire operation will fail. If all statements execute successfully, the results are committed to the database; otherwise, all changes are rolled back. Thus, the indeterminate state resulting from the partial execution of a group of SQL statements is avoided.

When created, a JDBC connection begins in autocommit mode. This mode treats each SQL statement as an individual transaction, committing the action as soon as it is performed. To execute multiple SQL statements as an atomic transaction, the autocommit feature must be deactivated by passing false to the Connection object's setAutoCommit() method. Once autocommit is disabled, all SQL statements executed against this connection are automatically added to a transaction. Once all statements have been executed, the Connection object's commit() method is called to commit the results to the database. If an error occurs while executing any of the SQL statements, the entire transaction can be rolled back using the Connection object's rollback() method. Though it is a good practice to explicitly call rollback() if an error occurs, this is not absolutely necessary. Any transaction that is not explicitly committed (using the commit() method) is automatically rolled back. Listing 16.7 demonstrates how transactions can be employed to guarantee that both of the SQL statements are committed to the database or neither is committed.

Listing 16.7 Servlet using transactions to enforce database integrity.

import javax.servlet.*; 
import javax.servlet.http.*; 
import java.io.*; 
import java.sql.*; 

/** 
 * FundsTransfer transfers $1000 from one account to another. 
 * Transactions are employed to guarantee that the appropriate 
 * amount is debited from or credited to each account. If an 
 * error occurs during the transfer, the entire process is rolled 
 * back. 
 */ 

public class FundsTransfer extends HttpServlet 
{ 
private static final long TRANSFER_AMOUNT =1000; 
private static final long FROM_ACCOUNT = 100001; 
private static final long TO_ACCOUNT = 100002; 

Connection dbConn = null;
/** 
 * Creates a persistent connection to an Oracle database.
 */ 
public void init() throws ServletException 
{ 
String jdbcDriver = "oracle.jdbc.driver.OracleDriver"; 
String dbURL ="jdbc:oracle:thin:@localhost:1521:orcl"; 

try 
{ 
Class.forName(jdbcDriver).newInstance(); 

dbConn = DriverManager.getConnection(dbURL, "transfer", 
 	"transfer"); 
} 
catch (ClassNotFoundException e) 
{ 
throw new UnavailableException("JDBC driver not found:" + 
jdbcDriver); 
} 
catch (SQLException e) 
{ 
throw new UnavailableException("Unable to connect to: " + 
dbURL); 
} 
} 

/** 
 * Alters two rows within a database table in order to transfer 
 * funds from one account to another. Both SQL UPDATE commands 
 * are executed within a transaction in order to guarantee that 
 	 * both commands succeed or both fail. 
 	 */ 
public void doGet(HttpServletRequest request, 
HttpServletResponse response) throws ServletException, 
IOException 
{ 
response.setContentType("text/plain"); 

PrintWriter out= response.getWriter(); 

try 
{ 
dbConn.setAutoCommit(false); //disable auto-commit mode 

Statement stat = dbConn.createStatement(); //get statement 

//create SQL to debit the FROM account 
String debitSQL = "update ACCOUNT set ACCOUNT_VALUE = " + 
"(ACCOUNT_VALUE - " + TRANSFER_AMOUNT + ") where " +
"ACCOUNT_ID = " + FROM_ACCOUNT; 

stat.executeUpdate(debitSQL); //debit FROM_ACCOUNT 

//create SQL to credit the TO account 
String creditSQL = "update ACCOUNT set ACCOUNT_VALUE = " + 
"(ACCOUNT_VALUE + " + TRANSFER_AMOUNT + ") where " +
"ACCOUNT_ID = " + TO_ACCOUNT; 

stat.executeUpdate(creditSQL); //credit TO_ACCOUNT 

 			dbConn.commit(); //commit transaction 

//inform client that the money was transferred successfully 
out.println("$" + TRANSFER_AMOUNT + " transferred from " + 
"account " + FROM_ACCOUNT + " to " + TO_ACCOUNT); 

out.close(); 
} 
catch (Exception e) 
{ 
try 
{ 
dbConn.rollback();//rollback transaction if error occurs 
} 

catch (SQLException ignored) {} 
} 
} 
}

Stored Procedures

A stored procedure is an SQL operation that is stored on the database server. Stored procedures are usually written in an SQL dialect that has been expanded to include conditional statements, looping constructs, and other procedural programming features. Oracle's PL/SQL and SQL Server's Transact-SQL are examples of SQL languages in which stored procedures are commonly written. Stored procedures are used for a number of reasons, including the following:

• Stored procedures are precompiled on the server so they have a performance advantage over dynamic SQL.

• Because stored procedures are stored on the database server, they are available to all clients.

• Encapsulating business rules within a stored procedure allows business logic to be altered in one place (on the server) without requiring modification to any client.

• A stored procedure may execute any number of SQL operations without requiring the client to transmit each statement to the server.

Listing 16.8 presents an Oracle stored procedure written in PL/SQL. This procedure accepts three input parameters--the transfer amount ( transfer_ amount ), the account from which funds should be debited ( from_account ), and the account to which funds should be credited ( to_account ). Lastly, an output parameter ( transfer_date ) is defined. This procedure transfers the specified amount ( transfer_amount ) from from_account to to_account . The transfer_ date output parameter returns the date (according to the database) that the transaction took place.

Listing 16.8 Oracle PL/SQL stored procedure used to transfer funds.

CREATE or REPLACE procedure SP_TRANSFER 
(transfer_amount IN INTEGER,
 	from_account IN INTEGER, 
 	to_account IN INTEGER, 
transfer_date OUT VARCHAR) IS 
BEGIN 
update ACCOUNT set ACCOUNT_VALUE =(ACCOUNT_VALUE - 
transfer_amount) where ACCOUNT_ID = from_account; 

update ACCOUNT set ACCOUNT_VALUE = (ACCOUNT_VALUE + 
transfer_amount) where ACCOUNT_ID = to_account; 

select SYSDATE into transfer_date from DUAL; 
END;

JDBC provides support for calling stored procedures via the CallableStatement object. CallableStatement extends PreparedStatement and, therefore, inherits its functionality. In addition, CallableStatement provides methods to define input parameters and output parameters and retrieve return values. CallableStatement uses the following syntax to call a stored procedure:

{call PROCEDURE_NAME(?, ?, ?, ?)}

Or, for stored procedures that return a value:

{? = call PROCEDURE_NAME(?, ?, ?, ?)}

Similar to prepared statements, the question marks represent input and output parameters. The input parameters can be set using the CallableStatement object's setXxx() methods. Output parameters that have been registered using the CallableStatement object's registerOutParameter() method can be retrieved using the appropriate getXxx() method. The registerOutParameter() method defines the output parameter's type (see Listing 16.9). Since the SP_TRANSFER stored procedure presented in Listing 16.8 defines four parameters, the {call PROCEDURE_NAME()} statement shown previously contains four question marks (one for each parameter).

Input and output parameters are referenced by an index number, starting with one, that corresponds to the order in which the parameters were defined in the stored procedure. For example, SP_TRANSFER defines four parameters (three input and one output). The first parameter, transfer_amount , is referenced by index number one, from_account is index number two, and so on. Listing 16.9 demonstrates how to call the SP_TRANSFER stored procedure from within a servlet.

Listing 16.9 Uses a stored procedure to transfer funds between accounts.

import javax.servlet.*; 
import javax.servlet.http.*; 
import java.io.*; 
import java.sql.*; 

/** 
 * StoredProcFundsTransfer transfers funds from one account to 
 * another by calling a stored procedure. 
 */ 
public class StoredProcFundsTransfer extends HttpServlet 
{ 
private static final long TRANSFER_AMOUNT =1000; 
private static final long FROM_ACCOUNT = 100001; 
private static final long TO_ACCOUNT = 100002; 

Connection dbConn = null;

/** 
 * Creates a persistent connection to an Oracle database.
 */ 
public void init() throws ServletException 
{ 
String jdbcDriver = "oracle.jdbc.driver.OracleDriver"; 
String dbURL ="jdbc:oracle:thin:@localhost:1521:orcl"; 

try 
{ 
Class.forName(jdbcDriver).newInstance(); 

dbConn = DriverManager.getConnection(dbURL, "transfer", 
"transfer"); 
} 
catch (ClassNotFoundException e) 
{ 
throw new UnavailableException("JDBC driver not found:" + 
jdbcDriver); 
} 
catch (SQLException e) 
{ 
throw new UnavailableException("Unable to connect to: " + 
dbURL); 
} 
catch (Exception e) 
{ 
throw new UnavailableException("Error: " + e); 
} 
} 

/** 
 * Calls a stored procedure that alters two rows within a 
 * database table in order to transfer funds from one account 
 	 * to another. The stored procedure returns the date (according 
 * to the database) that the transfer occurred. 
 */ 
public void doGet(HttpServletRequest request, 
HttpServletResponse response) throws ServletException, 
IOException 
{ 
response.setContentType("text/plain"); 
  
PrintWriter out = response.getWriter(); 

try 
{ 
//create callable statement for stored procedure having 
//four parameters (three input and one output) 
CallableStatement cstmt = dbConn.prepareCall( 
"{call SP_TRANSFER(?, ?, ?, ?)}"); 

//set values of stored procedure's input parameters 
cstmt.setLong(1, TRANSFER_AMOUNT); 
cstmt.setLong(2, FROM_ACCOUNT); 
cstmt.setLong(3, TO_ACCOUNT); 

 			//register stored procedure's output parameter 
cstmt.registerOutParameter(4, java.sql.Types.VARCHAR); 

cstmt.execute(); //execute stored procedure 

//return transaction summary (including date) to the client 
out.println("$" + TRANSFER_AMOUNT+ " transferred from " + 
"account " + FROM_ACCOUNT + " to " + TO_ACCOUNT+ 
" on " + cstmt.getString(4)); 

out.close(); 
} 
catch (Exception e) 
{ 
out.println(e.getMessage()); 
} 
}
}

Database and Result Set Metadata

Metadata is defined as information (or data) about data. JDBC provides specific information about a database or a result set via metadata objects. A database metadata object, called DatabaseMetaData , can be retrieved using the java.sql.Connection object's getMetaData() method. Table 16.5 presents a small sample of the extensive amount of information that can be retrieved about the database from the DatabaseMetaData object.

Table 16.5 Small Sample of Information Available from the DatabaseMetaData Object

Similar to the manner in which the DatabaseMetaData object describes a database, the ResultSetMetaData object describes a result set. The ResultSetMetaData object can be retrieved using the ResultSet object's getMetaData() method. Table 16.6 illustrates some of the information about a result set that can be obtained from the ResultSetMetaData object.

Table 16.6 Sample of Information Available from the ResultSetMetaData Object

In servlet programming, the ResultSetMetaData object is commonly used to dynamically construct an HTML table given any ResultSet object. For example, listing 16.10 presents a servlet that queries the database for employee information and dynamically constructs an HTML table containing the results. this method of displaying database information is very flexible. If more information is desired, only the SQL statement that creates the result set need be updated to include another database column. The HTML table will automatically adapt to accomodate the new ResultSet. This servlet reads from the database created by the script presented in Listing 16.1. Figure 16.8 displays the browser-rendered output created by Listing 16.10.

Figure 16.8 Dynamically Generated Table Returned from the EmployeeInfo Servlet

Listing 16.10 Dynamically builds an HTML table to show a ResultSet .

import javax.servlet.*; 
import javax.servlet.http.*; 
import java.io.*; 
import java.sql.*; 

/** 
 * EmployeeInfo returns employee data presented in an HTML table 
 * that is dynamically created with help from the 
 * ResultSetMetaData object. 
 */ 
public class EmployeeInfo extends HttpServlet 
{ 
Connection dbConn = null; 
/** 
 * Creates a persistent connection to a MySQL database. 
 */
public void init() throws ServletException 
{ 
String jdbcDriver = "org.gjt.mm.mysql.Driver"; 
String dbURL ="jdbc:mysql://localhost/phonebook"; 

try 
{ 
Class.forName(jdbcDriver).newInstance(); 

dbConn = DriverManager.getConnection(dbURL); 
} 
catch (ClassNotFoundException e) 
{ 
throw new UnavailableException("JDBC driver not found:" + 
jdbcDriver); 
} 
catch (SQLException e) 
{ 
throw new UnavailableException("Unable to connect to: " + 
dbURL); 
} 
catch (Exception e) 
{ 
throw new UnavailableException("Error: " + e); 
} 
} 

/** 
 * Dynamically constructs an HTML table from a ResultSet object 
 * containing employee information. 
 */ 
public void doGet(HttpServletRequest request, 
HttpServletResponse response) throws ServletException, 
IOException 
{ 
response.setContentType("text/html"); 

PrintWriter out = response.getWriter(); 

try 
{ 
Statement stmt = dbConn.createStatement(); 

//create ResultSet containing employee information 
String sql = "select LAST_NAME, FIRST_NAME, PHONE, " + 
"EMAIL from EMPLOYEE order by LAST_NAME, FIRST_NAME"; 
ResultSet rs = stmt.executeQuery(sql); 

//get ResultSetMetaData object from ResultSet 
ResultSetMetaData rsMeta = rs.getMetaData(); 

//get number of columns in ResultSet 
int cols = rsMeta.getColumnCount(); 

out.println("<TABLE BORDER=\"1\">"); //begin dynamic table 

//create header row containing column titles 
out.println("<TR>"); 
for (int i = 1; i <= cols; i++) 
{ 
out.println("<TH>" + rsMeta.getColumnLabel(i) + "</TH>"); 
} 
out.println("</TR>"); 

//create a row for each row in result set 
while (rs.next()) 
{
out.println("<TR>"); 
for (int i = 1; i <= cols; i++) 
{ 
out.println("<TD>" + rs.getString(i) + "</TD>"); 
} 
out.println("</TR>"); 
} 

out.println("</TABLE>"); //end dynamic table 

out.close(); 
} 
catch (Exception e) 
{ 
System.out.println(e.getMessage()); 
} 
} 
}

SQL Escape Syntax

JDBC enables cross-database development by defining a single database API capable of interfacing with any database for which a JDBC driver is available. This ability to support numerous vendor's databases from a single codebase helps database applications realize Java's "Write Once, Run Anywhere" promise. Unfortunately, strictly adhering to the JDBC API does not, in itself, guarantee cross-database compatability. This is due to the fact that although most databases share a common SQL syntax for simple functions, they are not consistent when specifying more advanced functionality. One way to solve the problems presented by inconsistent SQL syntax is to use database-specific SQL in your JDBC applications. However, writing database-specific SQL into a JDBC application destroys its database independence by tying it to a particular vendor's product. Fortunately, there is a way to take advantage of many advanced database functions while maintaining database independence. This is accomplished through the use of JDBC escape clauses. An escape clause defines a standard JDBC syntax for common SQL features whose syntax may vary among databases. Escape clauses are translated into the proper database-specific SQL by the JDBC driver. Proper use of escape clauses can help ensure that your JDBC application will run using any JDBC-compliant database.

The JDBC escape syntax consists of a keyword followed by any number of parameters. The entire escape clause is always enclosed within braces as follows:

{KEYWORD PARAMETERS}

You might recognize this syntax from the section on stored procedures. This section demonstrated how to use a JDBC escape clause to call a stored procedure in a database-independent way. Again, the escape syntax for calling a stored procedure looks like this:

{call PROCEDURE_NAME(?, ?, ?, ?)}

Or, for stored procedures that return a value:

{? = call PROCEDURE_NAME(?, ?, ?, ?)}

In addition to stored procedures, there are many other areas where database-specific SQL diverges. For example, the syntax used to specify dates and times varies widely among databases. Fortunately, there is a very simple escape syntax for specifying dates and times within SQL statements. For example, the following escape clauses specify a date, a time, and a timestamp, respectively.

{d 'yyyy-mm-dd'}
{t 'hh:mm:ss'}  
{ts 'yyyy-mm-dd hh:mm:ss'}

To illustrate, the following UPDATE statement updates a customer's birth date in a database-independent way.

update CUSTOMER set BIRTHDAY = {d '1970-01-26'} where ID = 1;

JDBC defines an escape syntax for performing numeric, string, time, date, system, and conversion functions. These escape clauses use the fn keyword followed by the function name and any function parameters like this:

{fn FUNCTION(PARAM1, PARAM2, ..., PARAMn)}

Not all databases support all of the functions defined by the JDBC specification. To determine which functions are supported by a particular database, use the DatabaseMetaData object's getNumericFunctions() and getStringFunctions() methods. These methods return a comma-delimited list of all supported numeric or string functions, respectively.

JDBC escape functions can be used in place of their database-specific counterparts rather easily. For example, the following SQL statements demonstrate a database-specific SQL statement (using Oracle syntax) followed by its database-independent equivalent (using JDBC escape syntax).

update CUSTOMER set LAST_VISIT = SYSDATE where ID = 1;
update CUSTOMER set LAST_VISIT = {fn CURDATE()} where ID = 1;

The following paragraphs present the functions supported by the JDBC escape syntax.

JDBC defines the following numeric functions: ABS(number), ACOS(float), ASIN(float), ATAN(float), ATAN2(float1,float2), CEILING(number), COS(float), COT(float), DEGREES(number) , EXP(float), FLOOR(number), LOG(float), LOG10(float), MOD(integer1,integer2), PI(), POWER(number, power), RADIANS(number), RAND(integer), ROUND(number,places), SIGN (number), SIN(float), SQRT(float), TAN(float) , and TRUNCATE(number, places).

JDBC defines the following string functions: ASCII(string), CHAR(code), CONCAT(string1,string2), DIFFERENCE(string1,string2), INSERT(string1, start,length,string2) , LCASE(string), LEFT(string,count), LENGTH (string) , LOCATE(string1,string2,start), LTRIM(string), REPEAT(string, count) , REPLACE(string1,string2,string3) , RIGHT(string,count), RTRIM (string), SOUNDEX(string), SPACE(count), SUBSTRING(string,start, length), and UCASE(string).

JDBC defines the following date and time functions: CURDATE(), CURTIME(), DAYNAME(date), DAYOFMONTH(date), DAYOFWEEK(date), DAYOFYEAR (date), HOUR(time), MINUTE(time), MONTH(date), MONTHNAME(date), NOW(), QUARTER(date), SECOND(time), TIMESTAMPADD(interval,count,timestamp), TIMESTAMPDIFF(interval,timestamp1,timestamp2), WEEK(date) , and YEAR (date).

JDBC defines the following system and conversion functions: DATABASE(), IFNULL(expression,value) , USER() , and the conversion function CONVERT (value,SQLtype), where SQLtype is BIGINT, BINARY, BIT, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARBINARY, LONGVARCHAR, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY , or VARCHAR.

Outer joins are another area where SQL syntax varies widely among databases. The escape syntax for an outer join is as follows:

{oj TABLE_NAME LEFT OUTER JOIN TABLE_NAME ON SEARCH_CRITIRIA}

Use the DatabaseMetaData object's supportsOuterJoins(), supportsLimitedOuterJoins() , and supportsFullOuterJoins() to determine the extent to which outer joins are supported by a particular database.

Lastly, JDBC escape syntax allows special characters to be escaped so that they are interpreted literally rather than according to some special meaning. For example, the characters "%" and "_" have special meaning within an SQL LIKE clause. To interpret these characters literally, precede them with an escape character defined by this JDBC escape clause:

{escape 'ESCAPE_CHARACTER'}

For example, the following SQL statement searches for any rows where the customer's first name begins with an underscore.

select * from CUSTOMER where FIRST_NAME like '\_%' {escape '\'}