JDBC 4.0 enhancements in Java SE 6

Source: Internet
Author: User
Tags driver manager
Java platform, Standard Edition (Java SE) version 6 (code name Mustang) is now available in the second beta version and is planned to be delivered in May. Java SE 6 includes several Java database connectivity (JDBC) APIs
. These enhancements will be released to JDBC 4.0. The main purpose of the new JDBC function is to provide a simpler design method and a better developer experience. This document describes JDBC
4.0 enhancements and their benefits to Enterprise Java developers. We will use an Apache
Derby is a loan processing example application built as a back-end database to explore new JDBC functions.

Java SE 6.0

Java SE 6.0 aims to provide compatibility, stability, and high quality. This version has several interesting enhancements, especially in monitoring and management (JMX), Web Service, scripting language support (using the rhino Script Engine JSR 223 to integrate javascript technology with Java source code), database connectivity, annotation support, and security. Several new features are added to the jdbc api, from the support of the new rowid to more sqlexception subclasses.

JDBC 4.0 features

Using Java SE contained in MustangService ProviderMechanism, Java developers no longer need to use the image
The JDBC driver can be registered if Code such as class. forname () explicitly loads the JDBC driver. By calling
The drivermanager. getconnection () method automatically locates the appropriate driver. The drivermanager class can do this. This function is intended
Later compatible, so you do not need to modify the existing JDBC code.

In Java applications that access relational databases, by minimizing the "template" code we need to write, JDBC 4.0 also improves the experience of developers. It also provides Utility Classes to improve the registration and uninstallation mechanisms of JDBC drivers and to manage data sources and connection objects.

With JDBC 4.0, Java developers can now use annotations to specify SQL queries, so that Java SE 5.0 (TIGER)
Metadata support provided in the version. Annotation-based SQL query allows you to use the annotation keyword in Java code to specify an SQL query string. In this way, we do not have
Check the JDBC code and the database queries called in the code. For example, if a method called getactiveloans () is used to obtain the current
Loan, you can use @ query (SQL = "select * From loanapplicationdetails where
Loanstatus = 'A' ") Comment to modify it.

In addition, the final version of the Java SE 6 Development Kit (JDK 6)-opposite to the Runtime Environment (JRE 6)-will have a database based on Apache Derby bound with it. This will help developers understand the new JDBC feature without having to download, install, and configure database products separately.

The following functions are added to JDBC 4.0:

  • Automatically load the JDBC Driver Class.
  • Enhanced connection management.
  • Supports the rowid SQL type.
  • Use the dataset SQL Implementation of annotations.
  • Handle enhanced SQL exceptions.
  • SQL XML is supported.

Other functions are available, such as improved support for large objects (BLOB/clob) and National Character Set support. The following content will analyze these functions in detail.

Automatically load the JDBC driver

4.0, when calling the getconnection method, you no longer need to use class. forname () to explicitly load the JDBC driver, because
Drivermanager will try to find the appropriate driver from the JDBC driver loaded at initialization and explicitly loaded with the same classloader as the current application.

The drivermanager Methods getconnection and getdrivers have been enhanced to support Java SE Service
Provider mechanism (SPM ). According to SPM, a service is defined as a group of well-known interfaces and abstract classes, while a service provider is a specific implementation of the service. It also specifies-
The INF/Services Directory stores the service provider configuration file. JDBC
4.0 the driver must contain the file META-INF/services/Java. SQL. Driver. This file contains the JDBC driver's
Java. SQL. Driver implementation name. For example, to load the JDBC driver to connect to Apache
The Derby database, META-INF/services/Java. SQL. Driver file must contain the following:

Org. Apache. Derby. JDBC. embeddeddriver

Let's learn how to use this new function to load the JDBC driver manager as soon as possible. The following list shows the sample code normally used to load the JDBC driver. We assume that you need to connect to an Apache Derby database, because this database will be used in the sample application we mentioned later in this article:

Connection conn

=DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);

However, in JDBC 4.0, class. forname () is not required. We only need to call getconnection () to obtain the database connection.

Note that this only applies to obtaining database connections in standalone mode. If you use a database connection pool to manage connections, the code will be different.

Connection Management

Before JDBC 4.0, we rely on the jdbc url to define the data source connection. Now with JDBC
4.0. We only need to provide a set of parameters (such as the host name and port number) to the standard connection factory mechanism to obtain the connection to any data source. Connection and statement
A new method is added to the interface to support connection status tracking improvement and greater flexibility when managing statement objects in the pool environment. Metadata tool (JSR-175)
Used to manage active connections. We can also obtain metadata information, such as the status of the active connection. We can also specify the connection as a standard (connection for standalone applications) and pooled
(Pooledconnection) or even a distributed connection for XA transactions ). Note that we do not directly use
Xaconnection. It is used by the transaction manager inside the Java EE application server, such as WebLogic, websphere, or JBoss.

Rowid support

The rowid interface is added to JDBC.
4.0 supports the rowid data type, which is also supported by databases such as Oracle and DB2. When multiple records do not have a unique identifier column
Rowid is useful when the query output is saved in Collection (such as hashtable. 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 rowid objects is to use the set or update Methods in preparedstatement and resultset respectively.
The value of the rowid object cannot be transplanted between data sources. It can be considered to be specific to the data source. Therefore, sharing between different connection and resultset objects is prohibited.

The getrowidlifetime () method in databasemetadata can be used to determine the validity period of the rowid object. Table 1 lists possible values of returned values or row IDs.

Rowid Value Description
Rowid_unsupported The rowid data type is not supported.
Rowid_valid_other The lifetime of rowid depends on the database vendor.
Rowid_valid_transaction As long as the row in the database table is not deleted, the rowid lifetime is in the current transaction.
Rowid_valid_session As long as the row in the database table is not deleted, the rowid lifetime is within the duration of the current session.
Rowid_valid_forever As long as the row in the database table is not deleted, the rowid lifetime is unlimited.
Annotation-based SQL query

The JDBC 4.0 specification uses annotations (added in Java SE 5) to allow developers to associate SQL queries with Java classes without writing a large amount of code. In addition, by using the generics (JSR 014) and metadata (JSR 175) APIs,
We can associate SQL queries with Java objects to specify Input and Output Parameters for queries. We can also bind the query result to the Java class to accelerate the processing of the query output. We do not need
It is usually used to fill the query result with all the code in the Java object. When specifying an SQL query in Java code, there are two main Annotations: select and update.

Select comment

The Select comment is used to specify the selection query in the Java class so that the get method can be used to obtain data from the database table. Table 2 shows the various attributes of the select comment and their usage.

Name Type Description
SQL String SQL SELECT query string.
Value String Same as SQL attributes.
Tablename String Name of the database table on which SQL is called.
Readonly, connected, scrollable Boolean Indicates whether the returned dataset is read-only or updatable, whether it is connected to the backend database, and whether it can be rolled during use in connected mode.
Allcolumnsmapped Boolean Indicates whether the column names in the SQL comment element are mapped to fields in the dataset one to one.

The following is an example of the select comment for obtaining all current loans from the loan database:

interface LoanAppDetailsQuery extends BaseQuery {
@Select("SELECT * FROM LoanDetais where LoanStatus = 'A'")
DataSet<LoanApplication> getAllActiveLoans();

SQL annotations also support I/O parameters (parameter tags are represented by an integer after a question mark ). The following 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 comment

Update annotation is used to modify the query interface method and update one or more records in the database table. Each update comment must contain an element of the SQL comment type. The following is an example of the update annotation:

interface LoanAppDetailsQuery extends BaseQuery {
@Update(sql="update LoanDetails set LoanStatus = 1
where loanId = 2")
boolean updateLoanStatus(String loanStatus, int loanId);
Handle enhanced SQL exceptions

Exception Handling is an important part of Java programming, especially when you connect to a backend relational database or run a query on a backend relational database. We have been using the sqlexception class to indicate database-related errors. JDBC 4.0 has several enhancements in sqlexception processing. The following are some enhancements in JDBC 4.0, which provide a better experience for developers when processing sqlexceptions:

  • New sqlexception subclass
  • Supporting causal relationships
  • Support for enhanced for-each Loops
New sqlexception class

In JDBC 4.0, a new subclass of sqlexception is created to provide Java programmers with a way to write more portable error handling code. Two new sqlexception classes are introduced in JDBC 4.0:

  • Non-instantaneous SQL exception
  • SQL transient exception

  Non-instantaneous exception:This exception is thrown when the same operation fails again until the cause of sqlexception is corrected. Table 3 shows the new exception classes added to JDBC 4.0, all of which are subclasses of sqlnontransientexception (sqlstate class values are defined in the SQL 2003 Specification .) :

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

  Transient exception:If the operation is retried without any application-level interference and the previous failed JDBC operation succeeds, this exception is thrown. Table 4 lists new sqltransientexception extension exceptions.

Exception Sqlstate Value
Sqltransientconnectionexception 08
Sqltransactionrollbackexception 40
Sqltimeoutexception None
Causal relationship

Currently, sqlexception supports Java SE with an exception mechanism (also known as the cause tool, this mechanism allows us to handle multiple exceptions thrown in JDBC operations (if the backend database supports multiple exception functions ). This scenario occurs when you execute a statement that may throw multiple sqlexception statements.

We can use the getnextexception () method in sqlexception to iterate through the exception chain. The following is a sample code for processing the sqlexception causal relationship:

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 loop

Sqlexception class implements the iterable interface, for Java SE
5. Loop navigation traverses sqlexception and its causes. The following is a code snippet for sqlexception.
The added for-each loop is described.

catch(SQLException ex) {
for(Throwable e : ex ) {
LOG.error("Error occurred: " + e);
Support for National Character Set Conversion

The enhancements in the JDBC class when processing the national character set are listed below:

  • JDBC data type: New JDBC data types are added, such as nchar, nvarchar, longnvarchar, and nclob.
  • Preparedstatement: added the new methods setnstring, setncharacterstream, and setnclob.
  • Callablestatement: added the new methods getnclob, getnstring, and getncharacterstream.
  • Resultset: New Methods updatenclob, updatenstring, and updatencharacterstream are added to the interface.
Enhanced support for large objects (blob and clob)

The enhancements to lob processing in JDBC 4.0 are listed below:

  • Connection: adds a new method (createblob (), createclob (), and createnclob () to create a new instance of blob, clob, and nclob objects.
  • Preparedstatement: The New Methods setblob (), setclob (), and setnclob () are added to insert blob objects using inputstream, and insert clob and nclob objects using reader objects.
  • Lob: New Methods (free () are added to blob, clob, and nclob interfaces to release the resources occupied by these objects.

Now, let's take a look at some new classes added to the java. SQL and javax. JDBC packages and what services they provide.

JDBC 4.0 API: New Class rowid (Java. SQL)

As mentioned above, this interface represents an SQL rowid value in the database. Rowid is a built-in SQL data type used to identify specific data rows in a database table. Rowid is usually used in such a query: the query returns rows from a table with no unique ID column in the output row.

Methods In the callablestatement, preparedstatement, and resultset interfaces, such as getrowid and setrowid,
Allow programmers to access SQL
Rowid value. The interface also provides a method (getbytes () To return the rowid value as a byte array. The databasemetadata interface is called
The new getrowidlifetime method is used to determine the lifetime of the rowid object. The rowid scope can be one of the following three types:

  • The duration of the database transaction in which the rowid is created.
  • The duration of the session in which the rowid is created.
  • Identify row in the database table as long as it has not been deleted.
Dataset (Java. SQL)

Dataset connection
Provides a type-Safe View of the data returned from the SQL query. Dataset can be operated in connected or unconnected mode. When used in connected mode, its function is similar
Resultset. In the unconnected mode, dataset functions similar to cachedrowset. Because dataset extends the list interface, we can
Traverses the rows returned by the query.

Several new methods are added to the existing class, such as connection (createsqlxml, isvalid) and resultset (getrowid ).

Sample Application

The example application used in this article is a loan processing application that contains a loan search page on which users can enter a loan ID to obtain details about the loan,
Then, submit the form. The loan search page calls a controller object, which calls the DaO object to access the backend database to obtain detailed information about the loan. The detailed information includes the borrower
The name, loan amount, and loan expiration time are displayed on a loan details page. In the backend database, we use a loanapplicationdetails
To save detailed information about the loan application.

The Use Case of the sample application is to obtain loan details for a specific loan ID. After registering a loan and locking it in the mortgage product and interest rate, you can obtain the loan details. Project details of the loan processing application are shown in table 5.

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
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 inDerby. PropertiesIs located inETC/jdbcDirectory (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 types of JDBC drivers: embedded
Driver (Org. Apache. Derby. JDBC. embeddeddriver) and Client/Server
Driver (Org. Apache. Derby. JDBC. clientdriver ). In the example application, I use Client/Server.
Driver version.

The following command is used to start the Derby database server and create a new database using the IJ tool.

To start Derby network server, open a command prompt and run the following commands (modify the derby_install and java_home environment variables to affect the local environment ).

set DERBY_INSTALL=C:/dev/dbservers/db-derby-
set JAVA_HOME=C:/dev/java/jdk1.6.0
set DERBY_INSTALL=C:/dev/dbservers/db-derby-

cd %DERBY_INSTALL%/frameworks/ NetworkServer/bin

To connect to the database server and create a test database, open another command prompt and run the following command. Make sure that the derby_install and java_home environment variables are modified to adapt to the environment.

set JAVA_HOME=C:/dev/java/jdk1.6.0
set DERBY_INSTALL=C:/dev/dbservers/db-derby-
set CLASSPATH=%DERBY_INSTALL%/lib/derbyclient.jar;

%JAVA_HOME%/bin/java org.apache.derby.tools.ij
connect 'jdbc:derby://localhost:1527/LoanDB;create=true';

Classpath settings used to compile Java source code should be included in the main directory of the projectLibDirectoryDerby. JarAndJunit4.jarFile. We also need to includeEtc,ETC/jdbcAndETC/log4jDirectory so that the application can access the JDBC attributes and log4j configuration files. I created an ant compilation script (located inJdbcapp/buildDirectory) to automatically compile and package Java code tasks.

The test class used to test loan details data access objects is called loanappdetailsdaotest. Parameters (such as loan ID and borrower name) are input to obtain loan details.

The following content provides some sample code about the SQL query function that the JDBC 4.0 specification uses to automatically load and annotate the JDBC driver.

Automatically load the JDBC driver

The basedao abstract class has a method called getconnection to obtain a database connection. The following code snippet shows this method (note that we do not have to register the JDBC driver ). As longJava. SQL. DriverFile contains the correct driver name (Org. Apache. Derby. JDBC. clientdriver), you can automatically load the JDBC driver.

protected Connection getConnection() throws DAOException {
// Load JDBC properties first
if (jdbcUrl == null || jdbcUser == null ||
jdbcPassword == null) {
// Get Connection
Connection conn = null;
try {
conn = DriverManager.getConnection(jdbcUrl, jdbcUser,
} catch (SQLException sqle) {
throw new DAOException("Error in getting a DB connection.",
return conn;
SQL comment

The loanappdetailsquery interface has an annotated SQL query for obtaining the list of current loans (condition: loanstatus = "A") and loan-based
Loan details of the payer's name (when a lender borrows more than one loan ). We have seen these SQL comments before this article. The sample code below demonstrates how to call and use
The SQL query defined by annotation.

public DataSet<LoanAppDetails> getAllActiveLoans() throws Exception {
// Get Connection
Connection conn = getConnection();
LoanAppDetailsQuery query = null;
DataSet<LoanAppDetails> loanDetails = null;
query = QueryObjectFactory.createQueryObject(
LoanAppDetailsQuery.class, conn);
loanDetails = query.getAllActiveLoans();
return loanDetails;
public DataSet<LoanAppDetails> getLoanDetailsByBorrowerName(
String borrFirstName, String borrLastName) throws Exception {
// Get Connection
Connection conn = getConnection();
LoanAppDetailsQuery query = null;
DataSet<LoanAppDetails> loanDetails = null;
query = QueryObjectFactory.createQueryObject(
LoanAppDetailsQuery.class, conn);
loanDetails = query.getLoanDetailsByBorrowerName(
return loanDetails;


When using SQL, JDBC 4.0 can provide development simplicity and improve the developer experience. JDBC
Another goal of 4.0 is to provide enterprise-level JDBC functions and publish APIs to a wider set of tools to better manage JDBC resources. In addition, JDBC 4.0
The API provides a migration path for the JDBC driver
The Connector Architecture (JCA) is compatible. This allows JDBC vendors to continue to implement JDBC technology Connector
API. When the enterprise-level service-oriented architecture (service oriented)
This is important when JDBC data sources are used in an enterprise-level SOA application.
Another adapter in the Enterprise Service Bus (ESB) architecture, without having to write ESB-specific implementation code for the JDBC data source.

In this article, we discuss enhancements in JDBC 4.0, such as rowid support, JDBC driver loading, and annotations-based SQL. Other functions will be added to JDBC 4.0 to support the SQL 2003 specification in the future. For more information about the JDBC 4.0 specification, see the specification documentation.

  • Sample application code
  • Java SE 6 Homepage
  • Java SE 6 reference implementation (Mustang) web site
  • Java SE 6 Download Page
  • API specifications
  • Apache Derby Homepage
  • Tomcat 5.5 Homepage
Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.