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
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.
          In this article, we will learn commonly used methods of Connection interface with examples.
         In this article, we will learn commonly used methods of Statement interface with examples.
          In this article, we will learn commonly used methods of PreparedStatement interface.
          In this article, we will learn commonly used methods of CallableStatement interface.
          In this article, we will learn commonly used methods of ResultSet interface with examples.
          In this article, we will learn commonly used methods of ResultSetMetaData interface.
          In this article, we will learn commonly used methods of DatabaseMetadata interface.
          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

Comments