Java JDBC API Overview

Java Database Connectivity or JDBC API provides industry-standard and database-independent connectivity between the Java applications and relational database servers(relational databases, spreadsheets, and flat files).
Using the JDBC API, we can access virtually any data source, from relational databases to spreadsheets and flat files. JDBC technology also provides a common base on which tools and alternate interfaces can be built.
JDBC helps you to write Java applications that manage these three programming activities:
  1. Connect to a data source, like a database
  2. Send queries and update statements to the database
  3. Retrieve and process the results received from the database in answer to your query
The JDBC API is comprised of two packages:
We automatically get both packages when you download the Java Platform Standard Edition (Java SE) 8.
JDBC API consists of two parts – the first part is the JDBC API to be used by the application programmers. The second part is the low-level API to connect to a database server(JDBC Driver).
  1. The first part of JDBC API is part of standard java packages in java.sql package. We use java.sql package API for accessing and processing data stored in a data source (usually a relational database) using the Java programming language.
  2. For the second part is the JDBC driver(there are four different types of JDBC drivers) A JDBC driver is a set of Java classes that implement the JDBC interfaces, targeting a specific database. The JDBC interfaces come with standard Java, but the implementation of these interfaces is specific to the database you need to connect to. Such an implementation is called a JDBC driver.

JDBC Driver Types

To use the JDBC API with a particular database management system(MySQL, Oracle etc), we need a JDBC technology-based driver to mediate between JDBC technology and the database (oracle.jdbc.driver.OracleDriver). Depending on various factors, a driver might be written purely in the Java programming language or in a mixture of the Java programming language and Java Native Interface (JNI) native methods.
Install a JDBC driver from the vendor of your database. A JDBC driver is a set of Java classes that implement the JDBC interfaces, targeting a specific database. The JDBC interfaces come with standard Java, but the implementation of these interfaces is specific to the database you need to connect to. Such an implementation is called a JDBC driver.
There are 4 different types of JDBC drivers:
  1. Type 1: JDBC-ODBC bridge driver
  2. Type 2: Java + Native code driver
  3. Type 3: All Java + Middleware translation driver
  4. Type 4: All Java driver.
If you are using Java DB ( Apache Derby database), it already comes with a JDBC driver. If you are using MySQL, install the latest version of Connector/J.

Type 1: JDBC-ODBC bridge driver

Drivers that implement the JDBC API as a mapping to another data access API, such as ODBC (Open Database Connectivity). Drivers of this type are generally dependent on a native library, which limits their portability.
Note: The JDBC-ODBC Bridge should be considered a transitional solution. It is not supported by Oracle. Consider using this only if your DBMS does not offer a Java-only JDBC driver.

Type 2: Java + Native code driver

Drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited. Oracle's OCI (Oracle Call Interface) client-side driver is an example of a Type 2 driver.

Type 3: All Java + Middleware translation driver

Type 3: Drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client's requests to the data source.

Type 4: All Java driver

Type 4: Drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.
MySQL Connector/J is a Type 4 driver.

How to install a JDBC driver?

Installing a JDBC driver generally consists of copying the driver to your computer, then adding the location of it to your classpath. In addition, many JDBC drivers other than Type 4 drivers require you to install a client-side API. No other special configuration is usually needed.

Java DB or Apache Derby database

Java DB is Oracle's supported distribution of the open source Apache Derby database. Its ease of use, standards compliance, full feature set, and small footprint make it the ideal database for Java developers. Java DB is written in the Java programming language, providing "write once, run anywhere" portability. It can be embedded in Java applications, requiring zero administration by the developer or user. It can also be used in client-server mode. Java DB is fully transactional and provides a standard SQL interface as well as a JDBC 4.1 compliant driver. Java DB is included in the JDK
In this tutorial, we have used JDBC 4, JDBC 4.1 and JDBC 4.2 API so let's discuss few JDBC 4,4.1 and 4.2 API features added to java.sql and javax.sql packages.

java.sql and javax.sql Features Introduced in the JDBC 4.2 API

  • Added JDBCType enum and SQLType interface
  • The JDBC-ODBC Bridge has been removed.
  • 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 and javax.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 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 and javax.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.

Learn JDBC 4.2 API on JDBC 4.2 Tutorial

Comments