DB2 Universal Database implements two standards-based Java programming APIs: Java Database Connectivity (JDBC) and embedded SQL for Java (SQLJ). This chapter provides an overview of JDBC and SQLJ programming, but focuses on the aspects specific to DB2. See the DB2 Universal Database Java Web site for links to the JDBC and SQLJ specifications.
The JDBC API allows you to write Java programs that make dynamic SQL calls to databases. SQLJ applications use JDBC as a foundation for such tasks as connecting to databases and handling SQL errors, but can also contain embedded static SQL statements in the SQLJ source files. You must translate a SQLJ source file with the SQLJ translator before you can compile the resulting Java source code.
For information on building JDBC and SQLJ applications, refer to the Application Building Guide.
Programming languages containing embedded SQL are called host languages. Java differs from the traditional host languages C, COBOL, and FORTRAN, in ways that significantly affect how it embeds SQL:
By default, a JDBC program executes SQL statements with the privileges assigned to the person who runs the program. In contrast, an SQLJ program executes SQL statements with the privileges assigned to the person who created the database package.
Source files have the following extensions:
The corresponding output files have the following extensions:
For an example of how to compile and run an SQLJ program, see Compiling and Running SQLJ Programs.
DB2 Universal Database provides class libraries for JDBC and SQLJ support, which you must provide in your CLASSPATH or include with your applets as follows:
To use the class libraries included with DB2 in your own applications, you must include the appropriate import package statements at the top of your source files. You can use the following packages in your Java applications:
Table 31 shows the Java equivalent of each SQL data type, based on the JDBC specification for data type mappings. Note that some mappings depend on whether you use the JDBC version 1.22 or 2.0 driver. The JDBC driver converts the data exchanged between the application and the database using the following mapping schema. Use these mappings in your Java applications and your PARAMETER STYLE JAVA stored procedures and UDFs. For information on data type mappings for PARAMETER STYLE DB2GENERAL stored procedures and UDFs, see Supported SQL Data Types.
| Note: | There is no host variable support for the DATALINK data type in any of the programming languages supported by DB2. |
Table 31. SQL Data Types Mapped to Java Declarations
| SQL Column Type | Java Data Type | SQL Column Type Description |
|---|---|---|
|
SMALLINT (500 or 501) | short | 16-bit, signed integer |
|
INTEGER (496 or 497) | int | 32-bit, signed integer |
|
BIGINT (492 or 493) | long | 64-bit, signed integer |
|
REAL (480 or 481) | float | Single precision floating point |
|
DOUBLE (480 or 481) | double | Double precision floating point |
|
DECIMAL(p,s) (484 or 485) | java.math.BigDecimal | Packed decimal |
|
CHAR(n) (452 or 453) | String | Fixed-length character string of length n where n is from 1 to 254 |
|
VARCHAR(n) (448 or 449) | String | Variable-length character string |
|
LONG VARCHAR (456 or 457) | String | Long variable-length character string |
|
CHAR(n) FOR BIT DATA | byte[] | Fixed-length character string of length n where n is from 1 to 254 |
|
VARCHAR(n) FOR BIT DATA | byte[] | Variable-length character string |
|
LONG VARCHAR FOR BIT DATA | byte[] | Long variable-length character string |
|
BLOB(n) (404 or 405) |
JDBC 1.22: byte[] JDBC 2.0: java.sql.Blob | Large object variable-length binary string |
|
CLOB(n) (408 or 409) |
JDBC 1.22: String JDBC 2.0: java.sql.Clob | Large object variable-length character string |
|
DBCLOB(n) (412 or 413) |
JDBC 1.22: String JDBC 2.0: java.sql.Clob | Large object variable-length double-byte character string |
|
DATE (384 or 385) | java.sql.Date | 10-byte character string |
|
TIME (388 or 389) | java.sql.Time | 8-byte character string |
|
TIMESTAMP (392 or 393) | java.sql.Timestamp | 26-byte character string |
If an SQL error occurs, JDBC and SQLJ programs throw an SQLException. To retrieve the SQLSTATE, SQLCODE, or SQLMSG values for an instance of an SQLException, invoke the corresponding instance method as follows:
For example:
int sqlCode=0; // Variable to hold SQLCODE
String sqlState="00000"; // Variable to hold SQLSTATE
try
{
// JDBC statements may throw SQLExceptions
stmt.executeQuery("Your JDBC statement here");
// SQLJ statements may also throw SQLExeptions
#sql {..... your SQLJ statement here ......};
}
/* Here's how you can check for SQLCODEs and SQLSTATE */
catch (SQLException e)
{
sqlCode = e.getErrorCode() // Get SQLCODE
sqlState = e.getSQLState() // Get SQLSTATE
if (sqlCode == -190 || sqlState.equals("42837"))
{
// Your code here to handle SQLCODE -190 or SQLSTATE 42837
}
else
{
// Your code here to handle other errors
}
System.err.println(e.getMessage()) ; // Print the exception
System.exit(1); // Exit
}
Both the CLI/ODBC/JDBC trace facility and the DB2 trace facility, db2trc, can be used to diagnose problems related to JDBC or SQLJ programs. Details on how to take the above traces are explained in the Troubleshooting Guide.
You can also install run-time call tracing capability into SQLJ programs. The utility operates on the profiles associated with a program. Suppose a program uses a profile called App_SJProfile0. To install call tracing into the program, use the command:
profdb App_SJProfile0.ser
The profdb utility uses the Java Virtual Machine to run the main() method of class sqlj.runtime.profile.util.AuditorInstaller. For more details on usage and options for the AuditorInstaller class, visit the DB2 Java Web site .
Whether your application or applet uses JDBC or SQLJ, you need to familiarize yourself with the JDBC specification, which is available from Sun Microsystems. See the DB2 Java Web site for links to JDBC and SQLJ resources. This specification describes how to call JDBC APIs to access a database and manipulate data in that database.
You should also read through this section to learn about DB2's extensions to JDBC and its few limitations (see JDBC 2.0). If you plan to create UDFs or stored procedures in Java, see Creating and Using Java User-Defined Functions and Java Stored Procedures and UDFs, as there are considerations that are different for Java than for other languages.
To build and run JDBC and SQLJ applications and applets, you must set up your operating system environment according to the instructions in the Application Building Guide.
DB2's Java enablement has three independent components:
Figure 21 illustrates how a DB2 JDBC application works. You can think of a DB2 JDBC application as a DB2 CLI application, only you write it using the Java language. Calls to JDBC are translated to calls to DB2 CLI through Java native methods. JDBC requests flow from the DB2 client through DB2 CLI to the DB2 server.
SQLJ applications use this JDBC support, and in addition require the SQLJ run-time classes to authenticate and execute any SQL packages that were bound to the database at the precompiling and binding stage.
Figure 21. DB2's Java Application Implementation
|
Figure 22 illustrates how the JDBC applet driver, also known as the net driver, works. The driver consists of a JDBC client and a JDBC server, db2jd. The JDBC client driver is loaded on the Web browser along with the applet. When the applet requests a connection to a DB2 database, the client opens a TCP/IP socket to the JDBC server on the machine where the Web server is running. After a connection is set up, the client sends each of the subsequent database access requests from the applet to the JDBC server though the TCP/IP connection. The JDBC server then makes corresponding CLI (ODBC) calls to perform the task. Upon completion, the JDBC server sends the results back to the client through the connection.
SQLJ applets add the SQLJ client driver on top of the JDBC client driver, but otherwise work the same as JDBC applets.
For information on starting the DB2 JDBC server, refer to the db2jstrt command in the Command Reference.
Figure 22. DB2 Java Applet Implementation
 |