Java SQL Package Tutorial

In this tutorial, we will learn a few important interfaces and classes of a java.sql package with lots of examples.

java.sql Package

The java.sql package provides the 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. Although the JDBC API is mainly geared to passing SQL statements to a database, it provides for reading and writing data from any data source with a tabular format. The reader/writer facility, available through the javax.sql.RowSet group of interfaces can be customized to use and update data from a spreadsheet, flat file, or any other tabular data source.

What the java.sql Package Contains

The java.sql package contains API for the following:
1. Making a connection with a database via the DriverManager facility

• DriverManager class -- makes a connection with a driver
• SQLPermission class -- provides permission when code running within a Security Manager, such as an applet, attempts to set up a logging stream through the DriverManager
• Driver interface -- provides the API for registering and connecting drivers based on JDBC technology ("JDBC drivers"); generally used only by the DriverManager class
• DriverPropertyInfo class -- provides properties for a JDBC driver; not used by the general user

2. Sending SQL statements to a database

• Statement -- used to send basic SQL statements
• PreparedStatement -- used to send prepared statements or basic SQL statements (derived from Statement)
• CallableStatement -- used to call database stored procedures (derived from PreparedStatement)
• Connection interface -- provides methods for creating statements and managing connections and their properties
• Savepoint -- provides savepoints in a transaction

3. Retrieving and updating the results of a query

• ResultSet interface

4. Standard mappings for SQL types to classes and interfaces in the Java programming language

• Array interface -- mapping for SQL ARRAY
• Blob interface -- mapping for SQL BLOB
• Clob interface -- mapping for SQL CLOB
• Date class -- mapping for SQL DATE
• NClob interface -- mapping for SQL NCLOB
• Ref interface -- mapping for SQL REF
• RowId interface -- mapping for SQL ROWID
• Struct interface -- mapping for SQL STRUCT
• SQLXML interface -- mapping for SQL XML
• Time class -- mapping for SQL TIME
• Timestamp class -- mapping for SQL TIMESTAMP
• Types class -- provides constants for SQL types

5. Custom mapping an SQL user-defined type (UDT) to a class in the Java programming language

1. SQLData interface -- specifies the mapping of a UDT to an instance of this class
2. SQLInput interface -- provides methods for reading UDT attributes from a stream
3. SQLOutput interface -- provides methods for writing UDT attributes back to a stream

6. Metadata

• DatabaseMetaData interface -- provides information about the database
• ResultSetMetaData interface -- provides information about the columns of a ResultSet object
• ParameterMetaData interface -- provides information about the parameters to PreparedStatement commands

7. Exceptions

• SQLException -- thrown by most methods when there is a problem accessing data and by some methods for other reasons
• SQLWarning -- thrown to indicate a warning
• DataTruncation -- thrown to indicate that data may have been truncated
• BatchUpdateException -- thrown to indicate that not all commands in a batch update executed successfully

I have written articles on below important interfaces and classes of a java.sql package with examples.

• JDBC Connection Interface

          In this article, we will learn commonly used methods of Connection interface with examples.

• JDBC Statement Interface

         In this article, we will learn commonly used methods of Statement interface with examples.

• JDBC PreparedStatement Interface

          In this article, we will learn commonly used methods of PreparedStatement interface.

• JDBC CallableStatement Interface

          In this article, we will learn commonly used methods of CallableStatement interface.

• JDBC ResultSet Interface with Examples

          In this article, we will learn commonly used methods of ResultSet interface with examples.

• JDBC ResultSetMetaData Interface

          In this article, we will learn commonly used methods of ResultSetMetaData interface.

• JDBC DatabaseMetaData Interface

          In this article, we will learn commonly used methods of DatabaseMetadata interface.

• JDBC DriverManager Class

          In this article, we will learn commonly used methods of DriverManager class with examples.

Let's discuss what are new JDBC features added to a java.sql package as below listed.

java.sql Features Introduced in the JDBC 4.2 API

• Added JDBCType enum and SQLType interface
• Support for REF CURSORS in CallableStatement
• DatabaseMetaData methods to return maximum Logical LOB size and if Ref Cursors are supported
• Added support for large update counts

java.sql Features Introduced in the JDBC 4.1 API

• Allow Connection, ResultSet and Statement objects to be used with the try-with-resources statement
• Supported added to CallableStatement and ResultSet to specify the Java type to convert to via the getObject method
• DatabaseMetaData methods to return a PseudoColumns and if a generated key is always returned
• Added support to Connection to specify a database schema, abort and timeout a physical connection.
• Added support to close a Statement object when its dependent objects have been closed
• Support for obtaining the parent logger for a Driver, DataSource, ConnectionPoolDataSource, and XADataSource

java.sql Features Introduced in the JDBC 4.0 API

• auto java.sql.Driver discovery -- no longer need to load a java.sql.Driver class via Class.forName
• National Character Set support added
• Support added for the SQL:2003 XML data type
• SQLException enhancements -- Added support for cause chaining; New SQLExceptions added for common SQLState class value codes
• Enhanced Blob/Clob functionality -- Support provided to create and free a Blob/Clob instance as well as additional methods added to improve accessibility
• Support added for accessing a SQL ROWID
• Support added to allow a JDBC application to access an instance of a JDBC resource that has been wrapped by a vendor, usually in an application server or connection pooling environment.
• Availability to be notified when a PreparedStatement that is associated with a PooledConnection has been closed or the driver determines is invalid

Read more about a java.sql package on https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html

Learn complete latest JDBC 4.2 on JDBC 4.2 Tutorial