This specification is not final and is subject to change. Use is subject tolicense terms

JSR 221 Maintenance Release

Package java.sql2

An API for accessing and processing data stored in a data source (usually a relational database) using the Java™ programming language. This API includes a framework whereby different drivers can be installed dynamically to access different data sources. This API is specifically geared for passing SQL statements to a database though it may be used for reading and writing data from/to any data source that has a tabular format.

This API differs from the API in java.sql in several ways.

It is worth emphasizing that this API is an alternate to the java.sql API, not a replacement. There are many programs that can much more readily be written using the java.sql API as it has many features that are not available in this API. For example this API provides almost no mechanism for getting metadata.

This API is not an extension to the java.sql API. It an independent API and is used on its own without reference to java.sql.


The core feature of this API is that it is asynchronous. No method call will wait for a network operation.

Possibly blocking actions are represented as Operations. An application using the API creates and submits one or more Operations. The driver executes these Operations asynchronously, reporting their results via CompletionStages. An application can respond to the results via the CompletionStages or via callbacks that can be configured on many of the Operations or both. Creating and submitting Operations is strictly non-blocking. Handling the results of possibly blocking Operations is done asynchronously. No application thread will ever block on a call to a method in this API.

All Operations provide a CompletionStage. The value of that CompletionStage is the value of the Operation, set when the Operation completes. Some Operations provide callbacks for processing the result of the Operation independent of the CompletionStage. Those Operations can be used for executing SQL that returns results of a specific type. For example SQL that returns a row sequence would be executed with a RowOperation. A RowOperation provides callbacks for processing each row and for collecting the results of processing the rows. Other Operations are specialized for SQL that returns a count or that returns out parameters. The choice of Operation is dependent on the result to be processed and is independent of the particular kind of SQL statement.

An OperationGroup encapsulates a group of Operations and executes them using common attributes. An OperationGroup can be unconditional or conditional, sequential or parallel, dependent or independent, or any combination of these. Dependent/independent controls error handling. If one member of a dependent OperationGroup fails the remaining not-yet-executed members are completed exceptionally. If the OperationGroup is independent, the member Operations are executed regardless of whether one or more fails.

A Connection is itself an OperationGroup and so can be conditional, parallel, or independent, but by default is unconditional, sequential, dependent. While a Connection may be created with values other than the defaults, using the defaults is by far the most common case. The API provides convenience methods that support this case. Using these convenience methods is recommended in all but the most unusual circumstances. In particular making the Connection parallel introduces some challenges that would require a full understanding of the details of the API. It would almost certainly be better to create a parallel OperationGroup within the Connection.

ISSUE: Should we disallow Connection.parallel()?

The java.sql API frequently provides many ways to do the same thing. This API makes no attempt to do this. For those capabilities this API supports, it frequently defines exactly one way to do something. Doing things another way, for example calling methods in a non-standard order, frequently results in an IllegalStateException. This approach is intended to make things simpler for both the user and the implementor. Rather than having to understand complicated interactions of many different components and methods executed in any order, the intent is that there is only one way to do things so only one path must be understood or implemented. Anything off that path is an error. While this requires a programmer to write code in one specific way it makes things easier on future maintainers of the code as the code will conform to the standard pattern. Similarly the implementation is simplified as only the standard use pattern is supported.

One way this API simplifies things in to define types as single use. Many types are created, configured, used once, and are then no longer usable. Many configuration methods can be called only once on a given instance. Once an instance is configured it cannot be reconfigured. Once an instance is used it cannot be reused. This simplifies things by eliminating the need to understand and implement arbitrary sequences of method calls that reconfigure and reuse instances. Since objects are single use there is no expectation that an application cache or share Operations.

While the user visible types are single use, it is expected that an implementation will cache and reuse data and Objects that are worth the effort. Rather than attempt to guess what an implementation should reuse and capture that in the API, this API leaves it entirely up to the implementation. Since the API specifies very little reuse, an implementation is free to reuse whatever is appropriate. Since the pattern of use is strictly enforced figuring out how to reuse objects is greatly simplified.

The java.sql API provides many tools for abstracting the database, for enabling the user to write database independent code. This API does not. It is not a goal of this API to enable users to write database independent code. That is not to say it is not possible, just that this API does not provide tools to support such. Abstraction features typically impose performance penalties on some implementations. As this API is geared for high-throughput programs it avoids such abstractions rather than reduce performance.

One such abstraction feature is the JDBC escape sequences. Implementing these features requires parsing the SQL so as to identify the escape sequences and then generating a new String with the vendor specific SQL corresponding to the escape sequence. This is an expensive operation. Further each SQL must be parsed whether it contains an escape sequence or not imposing the cost on all JDBC users, not just the ones who use escape sequences. The same is true of JDBC parameter markers. The SQL accepted by this API is entirely vendor specific, including parameter markers. There is no need for pre-processing prior to SQL execution substantially reducing the amount of work the implementation must do.

Note: It would be a reasonable future project to develop a SQL builder API that creates vendor specific SQL from some more abstract representation.

Execution Model

This section describes the function of a conforming implementation. It is not necessary for an implementation to be implemented as described only that the behavior be the same.

An Operation has an action and a CompletionStage. Some Operations have some form of result processor.

An Operation is executed by causing the action to be performed, processing the result of the action if there is a result processor, and completing the CompletionStage with the result of the result processor if there is one or with the result of the action if there is no result processor. If the action or the result processing causes an unhandled error the CompletionStage is completed exceptionally. The CompletionStage is completed asynchronously, as though it were created by calling an async method on CompletionStage.

Performing the action may require one or more interactions with the database. These interactions may be carried out in parallel with processing the result. If the database result is ordered, that result is processed in that order.

An OperationGroup has a collection of Operations and optionally a condition. For a sequential OperationGroup Operations are selected from the collection in the order they were submitted. For a parallel OperationGroup Operations are selected from the collection in any order.

The action of an OperationGroup is performed as follows:

A Connection is a distinguished OperationGroup. A Connection is executed upon being submitted.


This section describes the function of a conforming implementation. It is not necessary for an implementation to be implemented as described only that the behavior be the same.

An implementation has only limited control over transactions. SQL statements can start, commit, and rollback transactions without the implementation having any influence or even being aware. This specification only describes the behavior of those transaction actions that are visible to and controlled by the implementation, i.e. the endTransaction Operation. Transaction actions caused by SQL may interact with actions controlled by the implementation in unexpected ways.

The creation of Operations and the subsequent execution of those Operations are separated in time. It is quite reasonable to determine that a transaction should commit after the Operation that ends the transaction is created. But if the execution of the transaction does not result in the expected results it might be necessary to rollback the transaction rather than commit it. This determination depends on the execution of the Operations long after the endTransaction Operation is created. To address this mismatch, the endTransaction Operation specified by this API is conditioned by a Transaction. It commits the transaction by default, a Transaction will cause an endTransaciton Operation to commit the transaction but a Transaction can be set to rollback the transaction at any time before the endTransaction Operation that references it is executed.

An endTransaction Operation, like all Operations, is immutable once submitted. But an endTransaction Operation is created with a Transaction and that Transaction can be set to commit or rollback. A Transaction controls the endTransaction Operation created with it. Using this mechanism an error handler, result handler or other code can cause a subsequent endTransaction Operation to rollback instead of the default which is to commit.

   Transaction t = conn.getTransaction();
       .resultProcessor( count -> { 
           if (count > 1) t.setRollbackOnly(); 
           return null; 
       } )

In this example if the update SQL modifies more than one row the result processor will set the Transaction to rollback only. When the endTransaction Operation submitted by commitMaybeRollback is executed it will cause the transaction to rollback.


Does this feature carry its weight? It is a nice ease of use feature for hand written code but it is not of much valuable for framework code. It certainly is not strictly necessary. Arguably it should be removed.

This API supports setting and getting POJOs (Plain Old Java Objects) as parameter values and results. This is not a comprehensive ORM (Object Relational Mapping). It is just a convenience.

To set parameters of a SQL statement to the values of a POJO, the type of the POJO must be annotated with the @SqlParameter annotation. One or more public getter methods must be so annotated. The @SqlParameter annotation defines the parameter marker base name and SQL type. The call to the ParameterizedOperation.set method provides the POJO itself and the parameter marker prefix. The implementation binds the return value of the annotated method to the parameter identified by the parameter marker prefix prepended to the parameter marker base name.

   public class Employee {
     private String name;
     private String department;
     \@SqlParameter("name", "VARCHAR")
     public String getName() { return name; }
     public String getDepartement() { return department; }
     \@SqlColumns("name", "dept")
     public static Employee createEmployee(String name, String department) {
       Employee e = new Employee(); = name;
       e.department = department;
       return e;

   conn.countOperation("insert into emp values(:emp_name, :emp_dept)")
       .set("emp_", employee);

The above code fragment is identical to

    conn.countOperation("insert into emp values(emp_name, emp_dept)")
       .set("emp_name", employee.getName(), JdbcType.VARCHAR);
       .set("emp_dept", employee.getDepartment(), JdbcType.VARCHAR)

The prefix may be the empty string but may not be null. IllegalStateException is thrown if a parameter is set more than once either by a POJO set or a single value set. Implementations may attempt to bind a parameter for every annotated getter method. An implementation is not required to check whether or not there is a parameter with the specified name. It is implementation dependent whether an annotated getter with no corresponding parameter is an error or not and whether the implementation or the database detect it if it is an error.

To construct a POJO from the result of an Operation the type of the POJO must be annotated with @SqlColumns. The annotation may be applied to a public static factory method, a public constructor, or one or more public setter methods. If applied to setters, there must be a public zero-arg constructor or public static zero-arg factory method. The annotation provides the base names for column or out parameter marker that provides the value for the corresponding parameter or setter method. The get method call provides the prefix.

     conn.rowOperation("select name, dept from emp")
         () -> new ArrayList(),
         (l, r) -> { 
             l.add(r.get("", Employee.class)); 
         (l, r) -> l
     ) )

The above code fragment is identical to

     conn.rowOperation("select name, dept from emp")
         () -> new ArrayList(),
         (l, r) -> { 
             l.add(Employee.createEmployee(r.get("name", String.class), 
                                         r.get("dept", String.class))); 
         (l, r) -> l
     ) )

If more than one factory method, constructor, or set of setters is annotated it is implementation dependent which is used to construct a POJO. An implementation is not required to determine the best choice for any meaning of "best". An implementation my instead throw an exception if more than one alternative is annotated. If setters are annotated an implementation should call every annotated setter. It is implementation dependent whether it attempts to call a subset of the setters if all the columns named in the annotations are not present. In summary, best practice is to annotate exactly what is required and no more and to use exactly what is annotated and no more.

Skip navigation links
JSR 221 Maintenance Release

Report a bug or suggest an enhancement
For further API reference and developer documentation see the Java SE Documentation, which contains more detailed, developer-targeted descriptions with conceptual overviews, definitions of terms, workarounds, and working code examples.
Java is a trademark or registered trademark of Oracle and/or its affiliates in the US and other countries.
Copyright © 1993, 2017, Oracle and/or its affiliates, 500 Oracle Parkway, Redwood Shores, CA 94065 USA.
All rights reserved. Use is subject to license terms and the documentation redistribution policy.