Enhanced JDBC 4.0 in Java SE 6

Abstract:
Java SE 6 includes some enhancements to Java Database Interconnection (JDBC) APIs. The versions of these enhancements will be set to JDBC version 4.0. The purpose of the new JDBC features is to provide a simpler design and a better development experience. This article describes the enhanced features of JDBC 4.0 and the benefits of enterprise-level Java developers. We will explore the new features of JDBC through a loan processing application example using Apache Derby as the background database.
Ava SE 6 includes some enhancements to Java Database Interconnection (JDBC) APIs. The versions of these enhancements will be set to JDBC version 4.0. The purpose of the new JDBC features is to provide a simpler design and a better development experience. This article describes the enhanced features of JDBC 4.0 and the benefits of enterprise-level Java developers. We will explore the new features of JDBC through a loan processing application example using Apache Derby as the background database.

Java SE 6 includes some enhancements to Java Database Interconnection (JDBC) APIs. The versions of these enhancements will be set to JDBC version 4.0. The purpose of the new JDBC features is to provide a simpler design and a better development experience. This article describes the enhanced features of JDBC 4.0 and the benefits of enterprise-level Java developers. We will explore the new features of JDBC through a loan processing application example using Apache Derby as the background database.

Copyright Disclaimer: Any website authorized by matrix must retain the following author information and links for reprinting.
Author: feichangcai; feichangcai
Original article: http://www.matrix.org.cn/resource/article/2006-11-19/Mustang+JDBC_c8c66f03-77c2-11db-bdce-bdc029e475a1.html
Keyword: Mustang; JDBC

Java SE 6.0
Java SE 6.0 is designed for compatibility, stability, and quality. This version has many notable enhancements, especially support for JMX, Web Services, and scripting languages (using the rhino Script Engine JSR 223 to integrate javascript technology with Java source code), database connection, support for annotations and security. In addition, there are many new features in the jdbc api, including rowid support and the newly added sqlexception subclass.

Features of JDBC 4.0
Thanks to the Java SE Service Provider mechanism in Mustang, Java developers no longer need to register the JDBC driver with code similar to class. forname () to explicitly load JDBC. When the drivermanager. getconnection () method is called, The drivermanager class automatically sets the appropriate driver. This feature is backward compatible, so there is no need to make any changes to the existing JDBC code.

By simplifying the access to database code for Java applications, JDBC 4.0 has a better development experience. JDBC 4.0 also provides a tool class to improve the management of data sources and connection objects, as well as the JDBC driver loading and Detaching Mechanism.

With the metadata support function inherited from Java SE 4.0 (TIGER) in JDBC 5.0, Java developers can use annotations to explicitly specify SQL queries. Annotation-based SQL query allows us to correctly specify the SQL query string by using the annotation keyword in Java code. In this way, we do not have to check the JDBC code and the two different files of the called database. For example, you can use the getactiveloans () method to obtain an active loan list in the loan processing database, you can add the @ query (SQL = "select * From loanapplicationdetails where loanstatus = 'A'") annotation to modify this method.

In addition, the final version of the Java SE 6 Development Kit (JDK 6) and its corresponding execution stage Environment (JRE 6) will bind a Database Based on Apache derby. This allows Java developers to explore the new features of JDBC without downloading, installing, and configuring a separate database product.

The main features added in JDBC 4.0 include:
1. Automatic loading of JDBC driver classes
2. Enhanced connection management
3. Support for the rowid SQL type
4. the SQL dataset implementation uses annotations
5. enhanced SQL Exception Handling
6. SQL XML support

In addition, improvements to blob/clob and support for international character sets are also the feature of JDBC 4.0. These features will be discussed in detail in subsequent sections.

Automatic JDBC driver Loading
In JDBC 4.0, we do not need to use the class. forname () method to explicitly load the JDBC driver. When the getconnection method is called, drivermanager tries to select an appropriate driver from the JDBC driver library that has been loaded during initialization, in addition, he explicitly loads the used driver in the same class loader of the current application.
The getconnection and getdrivers methods in drivermanager have been improved to support the Java SE Service Provider mechanism (SPM ). According to SPM, a service is a set of well-known interfaces and abstract classes, and a service provider is a specific implementation of a service. SPM also specifies that the service provider's configuration files are stored in the META-INF/Services Directory. The driver library for JDBC 4.0 must contain the META-INF/services/Java. SQL. Driver file. This file contains the JDBC driver file name for Java. SQL. Driver. For example, if you connect to the Apache Derby database through a JDBC driver, the META-INF/services/Java. SQL. driver will contain the following path:
Org. Apache. Derby. JDBC. embeddeddriver

Let's take a quick look at how to use this new feature to load a JDBC driver management. The following shows the sample code for loading the JDBC driver. Here we assume that we are connected to the Apache Derby database, because the database will be used in the subsequent sample application in this article:
Class. forname ("org. Apache. Derby. JDBC. embeddeddriver"); connection conn = drivermanager. getconnection (jdbcurl, jdbcuser, jdbcpassword );
However, in JDBC 4.0, we do not need to write the class. forname () line. We only need to call the getconnection () method to obtain the database connection.
Note that you can use this method to obtain the database connection only in completely independent mode. If you use technologies like database connection pools to manage connections, the code will be different.

Connection Management
Before JDBC 4.0, jdbc url was used to define a data source connection. Now with JDBC 4.0, we only need to provide a set of parameters for the standard connection factory mechanism to obtain the connection to any data source. The new methods added to the connection and statement interfaces provide a better connection status tracking mechanism and greater flexibility for managing statement objects in the pool environment. A metadata tool (JSR-175) is used to manage active connections. We can also obtain metadata information, such as the active connection status, and specify that a connection of an Xa transaction is standard (connection, in the case of independent applications), and pooledconnection) or distributed (xaconnection ). This interface is only used in transaction management of Java EE application servers such as WebLogic, websphere, and JBoss.

Rowid support
The rowid interface is added to JDBC 4.0 to support the rowid data type. Oracle and DB2 databases support this data type. Rowid is useful when you need to put a large number of query records without a unique identifier field into a collection container (such as hashtable) that does not allow repeated objects. We can use the getrowid () method of resultset to obtain rowid, and use the setrowid () method of preparedstatement to use rowid in the query.

One important thing to remember about the rowid object is that the rowid value cannot be transplanted between data sources. When the set or update method is used separately in preparedstatement and resultset, You need to specify the data source. Therefore, the rowid object should not be shared by different connection and resultset objects.

The getrowidlifetime () method in databasemetadata can be used to determine the effective survival time of the rowid object. Possible return values of this method are listed in table 1:
Rowid value description
Rowid_unsupported doesn't support rowid data type.
Rowid_valid_other lifetime of the rowid is dependent on database vendor implementation.
Rowid_valid_transaction lifetime of the rowid is within the current transaction as long as the row in the database table is not deleted.
Rowid_valid_session lifetime of the rowid is the duration of the current session as long as the row in the database table is not deleted.
Rowid_valid_forever lifetime of the rowid is unlimited as long as the row in the database table is not deleted.

Annotation-based SQL query
JDBC 4.0 further regulates and supplements annotation (added in Java SE 5). It allows developers to contact SQL queries and Java classes without having to write a large amount of code. In addition, by using the generics (JSR 014) and JSR 175 APIs, you only need to specify the input and output parameters of the query to associate the SQL query with the Java object. We can also bind the query results to the Java class to speed up the processing of the query output. Place the query object in a Java object. We do not have to write all the code as usual. Two annotations are often used to specify SQL queries in Java code:

Select Annotation
The Select annotation is used to specify a selection query in the Java class so that the get method can retrieve data from the database table. Table 2 shows the different attributes of the select annotation and their functions:
Variable type description
SQL string SQL SELECT query string.
Value string same as SQL attribute.
Tablename string name of the database table against which the SQL will be invoked.
Readonly, connected, scrollable Boolean flags used to indicate if the returned dataset is read-only or updateable, is connected to the back-end database, and is scrollable when used in connected mode respectively.
Allcolumnsmapped Boolean flag to indicate if the column names in the SQL annotation element are mapped 1-to-1 with the fields in the dataset.

Here is an example where the select clause is used to obtain all active loans from the loan database:
Interface loanappdetailsquery extends basequery {@ select ("select * From loandetais where loanstatus = 'A'") dataset <loanapplication> getallactiveloans ();}

SQL annotation also allows I/O parameters (the parameter is marked with a question mark followed by an integer data ). Here is an example of parameterized SQL query:
Interface loanappdetailsquery extends basequery {@ select (SQL = "select * From loandetails where borrowerfirstname =? 1 and borrowerlastname =? 2 ") dataset <loanapplication> getloandetailsbyborrowername (string borrfirstname, string borrlastname );}

Update Annotation
Update annotation is used to modify the query interface method to update one or more records in the database table. The update annotation must contain an SQL annotation type element. Here is an example of update annotation:
Interface loanappdetailsquery extends basequery {@ Update (SQL = "Update loandetails set loanstatus =? 1 where loanid =? 2 ") Boolean updateloanstatus (string loanstatus, int loanid );}

SQL Exception Handling enhancements
Exception Handling is an important part of Java programming, especially when connecting or querying the background relational database. Sqlexception is a class used to identify database-related errors. JDBC 4.0 has many enhancements in sqlexception processing. To provide a better development experience when processing sqlexception, JDBC 4.0 is enhanced as follows:
1. New sqlexception subclass
2. Support for causal relationships
3. Support for improved for-each Loops

New sqlexception class
The new subclass of sqlexception provides a method for Java developers to write error handling code that is easier to modify. JDBC 4.0 introduces two new sqlexception types:
• SQL non-transient exception
• SQL transient exception

Non-transient exception: unless the code that causes the sqlexception exception is corrected, the exception is thrown after the same JDBC operation fails again. Table 3 shows the sqlnontransientexception exception subclass added to JDBC 4.0 (the sqlstate class value is defined in the SQL 2003 Specification ):

Exception class sqlstate Value
Sqlfeaturenotsupportedexception 0a
Sqlnontransientconnectionexception 08
Sqldataexception 22
Sqlintegrityconstraintviolationexception 23
Sqlinvalidauthorizationexception 28
Sqlsyntaxerrorexception 42

Transient exception: a jdbc operation that fails before execution may succeed without any application-level function interference. This exception is thrown. New exceptions inherited from sqltransientexception are listed in table 4:

Exception class sqlstate Value
Sqltransientconnectionexception 08
Sqltransactionrollbackexception 40
Sqltimeoutexception none

Causal relationship
The sqlexception class currently supports Java SE chained exception mechanism (also known as a causal tool), which enables us to handle multiple sqlexception exceptions in a JDBC operation (if the background database supports multiple exception features ). This occurs when you execute a statement that may throw multiple sqlexception exceptions.

We can call the getnextexception () method in sqlexception to iterate in the exception chain. Here is some sample code for processing the causal relationship of getnextexception:
Catch (sqlexception ex) {While (ex! = NULL) {log. error ("SQL state:" + ex. getsqlstate (); log. error ("error code:" + ex. geterrorcode (); log. error ("message:" + ex. getmessage (); throwable T = ex. getcause (); While (T! = NULL) {log. Error ("cause:" + T); t = T. getcause () ;}ex = ex. getnextexception ();}}

Enhanced for-each ring
In Java SE 5, the sqlexception class adds the features supported by the for-each loop by implementing the iterable interface. The trajectory of this loop will include sqlexception and Its Causes of exceptions. The code snippet here shows the enhanced for-each ring feature added in sqlexception.
Catch (sqlexception ex) {for (throwable E: Ex) {log. Error ("error occurred:" + E );}}

Support for international character set Conversion
The following are new features of JDBC class processing international character sets:
1. JDBC data types: nchar, nvarchar, longnvarchar, and nclob data types are added.
2. preparedstatement: added the setnstring, setncharacterstream, and setnclob methods.
3. callablestatement: added the getnclob, getnstring, and getncharacterstream methods.
4. resultset: The updatenclob, updatenstring, and updatencharacterstream methods are added to the resultset interface.

Improvement on support for large objects (blobs and clobs)
The following are new features of lobs processing in JDBC 4.0:
1. Connection: add methods (createblob (), createclob (), and createnclob () to create a new instance of blob, clob, and nclob objects.
2. preparedstatement: add methods setblob (), setclob (), and setnclob () to insert blob objects using inputstream, and insert clob and nclob objects using reader objects.
3. lobs: Add a method (free () in the blob, clob, and nclob interfaces to release the resources held by these objects.
Now, let's take a look at the new classes of Java. SQL and javax. JDBC packages, as well as the services they provide.

JDBC 4.0 API: New Class
Rowid (Java. SQL)
As described earlier, this interface displays the SQL rowid value in the database. Rowid is a built-in SQL data type used to identify a specific row of data in the database. Rowid is often used to return query results from a table, and these result rows often lack a unique ID field.

Getrowid, setrowid, and other callablestatement, preparedstatement, and resultset interfaces allow programmers to access the SQL rowid value. The rowid interface also provides a method (getbytes () To return the rowid value as a byte array. The databasemetadata interface has a new method named getrowidlifetime to determine the survival time of a rowid object. The rowid survival time range can be of the following three types:
1. Database Transaction duration for creating rowid
2. session duration for creating rowid
3. The duration of the records corresponding to the database that have not been deleted

Dataset (Java. SQL)
The dataset interface provides a security check for the data types returned after SQL query is executed. Dataset can also run in connection or non-connection mode. In connection mode, dataset is similar to the resultset function. In non-connection mode, dataset is similar to the cachedrowset function. Because dataset inherits from the list interface, we can iterate the records returned by the query.

For existing classes, JDBC 4.0 also adds many methods. For example, the createsqlxml and createsqlxml methods are added for connection, and the getrowid method is added for resultset.

Example Application
The example application shown in this article is a loan processing application, which has a loan search page. You can submit a query table by entering the loan ID to obtain the loan details. The loan search page calls a controller object, and the object calls a DaO object to access the background database to retrieve the loan details. These details include the lender, loan amount, loan deadline, and other information, and are displayed on the loan Details screen. In the background database, we have a table named loanapplicationdetails to store the details of the loan software.

The use case of this example is to obtain the loan details by specifying the loan ID. After the loan has been registered and the collateral is linked to interest, the loan details can be obtained. Table 5 shows the details of the loan processing application project.
NAME value
Project name jdbcapp
Project directory c:/dev/projects/jdbcapp
DB directory c:/dev/dbservers/Apache/Derby
JDK directory c:/dev/Java/jdk_1.6.0
IDE directory c:/dev/tools/eclipse
Database Apache Derby 10.1.2.1
JDK 6.0 (Beta 2 release)
IDE eclipse 3.1.
Unit testing JUnit 4
Build ant 1.6.5

The following table lists the JDBC parameters required for connecting to the loan details Apache Derby database. These parameters are stored in a text file named Derby. properties and placed in the ETC/jdbc directory of the project (see table 6 ).

NAME value
JDBC driver file loanapp/META-INF/services/Java. SQL. Driver
Driver org. Apache. Derby. clientdriver
Url jdbc: Derby: derbydb
User ID user1
Password user1

Note: The Apache Derby database provides two JDBC drivers: the embedded Driver (Org. apache. derby. JDBC. embeddeddriver) and Client/Server Driver (Org. apache. derby. JDBC. clientdriver ). I use the Client/Server Driver in the sample application.
The following commands use the IJ tool to start the Derby database server and create a new database.

To start the Derby network server, open a command line window and run the following command (rewrite the derby_install and java_home environment variables according to your local environment ).
Set derby_install = C:/dev/dbservers/db-derby-10.1.2.1-binset java_home = C:/dev/Java/jdk1.6.0set derby_install = C:/dev/dbservers/db-derby-10.1.3.1-binset classpath = % classpath %; % derby_install %/lib/derby. jar; % derby_install %/lib/derbytools. jar; % derby_install %/lib/derbynet. jar; cd % derby_install %/frameworks/networkserver/binstartnetworkserver. bat

To connect to the database server and create a test database, open another command line window and run the following command. Make sure that the derby_install and java_home environment variables match your local environment.
Set java_home = C:/dev/Java/jdk1.6.0set derby_install = C:/dev/dbservers/db-derby-10.1.3.1-binset classpath = % derby_install %/lib/derbyclient. jar; % derby_install %/lib/derbytools. jar ;. % java_home %/bin/Java Org. apache. derby. tools. ijconnect 'JDBC: Derby: // localhost: 1527/loandb; Create = true ';

Test
To compile the Java source code, classpath must include the Derby. jar and junit4.jar files, which are in the lib directory of the project. Classpath also includes the etc, ETC/JDBC, and ETC/log4j directories so that the application can access the JDBC attribute file and log4j configuration file. I created an ant build script (under the jdbcapp/build directory) to automatically compile and package the Java source code.

The test class used to test the loan details database access object is loanappdetailsdaotest. You can obtain the loan details by passing in the loan ID and lender parameters.
The following section shows sample code for automatically loading the JDBC driver and annotation-based SQL query features in JDBC 4.0.

Automatic JDBC driver Loading
The basedao abstract class has a method named getconnection to obtain a database connection. The following code snippet shows this method (note that we do not have to register the JDBC driver ). The JDBC driver is automatically loaded as long as the java. SQL. Driver file contains the appropriate Driver Class Name (Org. Apache. Derby. JDBC. clientdriver.
Protected connection getconnection () throws daoexception {// load JDBC properties first if (jdbcurl = NULL | jdbcuser = NULL | jdbcpassword = NULL) {loadjdbcproperties ();} // get connection conn = NULL; try {conn = drivermanager. getconnection (jdbcurl, jdbcuser, jdbcpassword);} catch (sqlexception sqle) {Throw new daoexception ("error in getting a DB connection. ", sqle) ;}return conn ;}

SQL Annotation
Loanappdetailsquery interface has a labeled SQL query to obtain the list of active loans (criteria is loanstatus = "") and the loan details of a lender (when a lender has multiple loans ). We have learned about these SQL annotations above. The sample code here shows how to use annotation to call a defined SQL query.
Public dataset <loanappdetails> getallactiveloans () throws exception {// get connection conn = getconnection (); lost query = NULL; dataset <loanappdetails> loandetails = NULL; query = queryobjectfactory. createqueryobject (loanappdetailsquery. class, Conn); loandetails = query. getallactiveloans (); Return loandetails;} public dataset <loanappdetails> values (string borrfirstname, string borrlastname) throws exception {// get connection conn = getconnection (); lost query = NULL; dataset <loanappdetails> loandetails = NULL; query = queryobjectfactory. createqueryobject (loanappdetailsquery. class, Conn); loandetails = query. getloandetailsbyborrowername (borrfirstname, borrlastname); Return loandetails ;}

Conclusion
JDBC 4.0 provides developers with a better development experience in SQL. Another goal of JDBC 4.0 is to add more tools for APIs to provide enterprise-level JDBC features for managing JDBC resources. In addition, the JDBC 4.0 API also provides a JDBC driver porting method that complies with the J2EE Connector Architecture (JCA) specifications. This provides JDBC vendors with the ability to migrate data to JDBC connectors. It is important to port JDBC data sources in an enterprise-oriented architecture (SPA. In SOA, the JDBC data source can be deployed in another enterprise service bus (ESB) architecture without writing an ESB implementation code for the JDBC data source.

In this article, we understand the enhanced features of JDBC 4.0, such as rowid support, JDBC driver loading, and annotation-based SQL. In the future, JDBC 4.0 will add more features to support SQL: 2003. For more information about the JDBC 4.0 feature, see the feature documentation.