Java Database Connectivity

Oracle Corporation
Company type	Public
Industry	Software
Founded	June 16, 1977; 49 years ago
Founder	Larry Ellison, Bob Miner, Ed Oates
Headquarters	Austin, Texas, United States
Key people	Safra Catz (CEO), Larry Ellison (CTO)
Products	Java Standard Edition, JDBC API
Owner	Public shareholders
Website	www.oracle.com

Java Database Connectivity (JDBC) is an application programming interface (API) designed for the Java platform that standardizes how applications interact with database systems. As a Java-centric data access framework, it forms a core component of the Java Standard Edition suite maintained by Oracle Corporation. JDBC offers functionalities for querying and modifying data, predominantly focusing on relational database management systems. Additionally, a JDBC-to-ODBC bridge facilitates connectivity to any ODBC-compliant data source within the Java virtual machine (JVM) context.

History and implementation

The JDBC API was first launched by Sun Microsystems alongside the release of Java Development Kit (JDK) 1.1 on February 19, 1997. Since its inception, it has been incorporated into the Java Platform, Standard Edition (Java SE).

The primary JDBC classes are housed within the Java packages and , both accessible via the java.sql module.

Starting with version 3.1, the evolution of JDBC has been governed by the Java Community Process. Notable specifications include JSR 54 introducing JDBC 3.0 (part of J2SE 1.4), JSR 114 which added Rowset support, and JSR 221 that defined JDBC 4.0 (included in Java SE 6). Maintenance updates under JSR 221 cover JDBC versions 4.1 (Java SE 7), 4.2 (Java SE 8), and the most recent iteration, JDBC 4.3 (Java SE 9).

JDBC versions
JDBC version	Java version	Release Type	Release date
1.1	JDK 1.1	Initial	1997-02-19
3.0	J2SE 1.4	Initial	2002-05-09
4.0	Java SE 6	Initial	2006-12-11
4.1	Java SE 7	Maintenance	2011-10-13
4.2	Java SE 8	Maintenance	2014-03-04
4.3	Java SE 9	Maintenance	2017-09-21

Functionality

Host database data types and corresponding `setXXX()` methods in Java
Oracle Datatype	`setXXX()` Methods
CHAR	`setString()`
VARCHAR2	`setString()`
NUMBER	`setBigDecimal()`
	`setBoolean()`
	`setByte()`
	`setShort()`
	`setInt()`
	`setLong()`
	`setFloat()`
	`setDouble()`
INTEGER	`setInt()`
FLOAT	`setDouble()`
CLOB	`setClob()`
BLOB	`setBlob()`
RAW	`setBytes()`
LONGRAW	`setBytes()`
DATE	`setDate()`
	`setTime()`
	`setTimestamp()`

JDBC primarily specifies interfaces and contracts rather than concrete implementations, which allows various vendors to provide their own drivers that can be loaded dynamically at runtime using the . This factory mechanism is responsible for establishing database connections.

Through these connections, SQL commands such as updates (including CREATE, INSERT, UPDATE, DELETE) and queries (SELECT) can be executed, along with calls to stored procedures. The API defines several statement classes to handle these operations:

– Executes straightforward SQL statements and returns a .
– Represents a precompiled SQL statement that can be executed repeatedly with varying parameters to enhance efficiency.
– Facilitates execution of stored procedures involving both input and output parameters.

When executing update commands, an integer value indicating the count of affected rows is returned without additional data. Query executions yield a result set that supports iterating through records and accessing columns by either their name or index position; metadata describing column types is also available.

An extended set of features is provided in the package as an enhancement to the core JDBC API.

In large-scale applications, direct interaction with JDBC drivers is uncommon; instead, connections are typically managed via connection pooling mechanisms to improve resource utilization and scalability.

Examples

When a Java program needs to establish a database connection, it calls DriverManager.getConnection() to obtain a JDBC connection. The connection URL starts with "jdbc:" followed by details specific to the database vendor.

<syntaxhighlight lang="java"> Connection conn = DriverManager.getConnection(

    "jdbc:somejdbcvendor:additional vendor-specific information",
    "myLogin",
    "myPassword");

try {

    /* interact with the connection here */

} finally {

   try { 
       conn.close();
   } catch (Throwable e) { 
       logger.warn("Failed to close JDBC Connection", e);
   }

} </syntaxhighlight>

Starting with Java SE 7, managing connections is simplified by using the try-with-resources construct:

<syntaxhighlight lang="java"> try (Connection conn = DriverManager.getConnection(

    "jdbc:somejdbcvendor:additional vendor-specific information",
    "myLogin",
    "myPassword")) {
    /* interact with the connection here */

} // Connection is automatically closed </syntaxhighlight>

After establishing the connection, a can be instantiated:

<syntaxhighlight lang="java"> try (Statement stmt = conn.createStatement()) {

   stmt.executeUpdate("INSERT INTO MyTable(name) VALUES ('my name')");

} </syntaxhighlight>

Resources such as Connections, Statements, and ResultSets consume system resources including sockets and file descriptors. When connected remotely, server-side resources like cursors are also occupied. It is essential to invoke close() on these objects promptly; relying solely on garbage collection is inadequate. The try-with-resources structure ensures these resources are released appropriately.

Data extraction involves executing queries through Statements:

<syntaxhighlight lang="java"> try (Statement stmt = conn.createStatement();

   ResultSet rs = stmt.executeQuery("SELECT * FROM MyTable")

) {

   while (rs.next()) {
       int columnCount = rs.getMetaData().getColumnCount();
       for (int i = 1; i <= columnCount; i++) {
          System.out.println("COLUMN " + i + " = " + rs.getObject(i));
       }
   }

} </syntaxhighlight>

PreparedStatements support parameterized queries, improving both security and efficiency:

<syntaxhighlight lang="java"> try (PreparedStatement ps =

   conn.prepareStatement("SELECT i.*, j.* FROM Omega i, Zappa j WHERE i.name = ? AND j.num = ?")

) {

   ps.setString(1, "Poor Yorick");
   ps.setInt(2, 8008);

   try (ResultSet rs = ps.executeQuery()) {
       while (rs.next()) {
           int columnCount = rs.getMetaData().getColumnCount();
           for (int i = 1; i <= columnCount; i++) {
               System.out.println("COLUMN " + i + " = " + rs.getObject(i));
           }
       }
   }

} </syntaxhighlight>

Failures in database operations throw . Recovery options are limited; it is recommended to log comprehensive details and convert exceptions into unchecked exceptions relevant to the application domain to initiate transaction rollback and inform users.

An example demonstrating database transaction management with commit and rollback follows:

<syntaxhighlight lang="java"> boolean originalAutoCommit = conn.getAutoCommit(); try {

   conn.setAutoCommit(false);

   /* Execute transactional commands */

   conn.commit();

} catch (Throwable e) {

   try { conn.rollback(); } catch (Throwable ex) { logger.warn("Rollback failed", ex); }
   throw e;

} finally {

   try { conn.setAutoCommit(originalAutoCommit); } catch (Throwable ex) { logger.warn("Restoring AutoCommit failed", ex); }

} </syntaxhighlight>

For invoking stored procedures, consult the CallableStatement section in the JDBC API documentation.

Example demonstrating record insertion into a MySQL database:

<syntaxhighlight lang="java"> import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement;

public class Mydb1 {

  static String URL = "jdbc:mysql://localhost/mydb";

  public static void main(String[] args) {
     try {
       Class.forName("com.mysql.jdbc.Driver");

       Connection conn = DriverManager.getConnection(URL, "root", "root");
       Statement stmt = conn.createStatement();
     
       String sql = "INSERT INTO emp1 VALUES ('pctb5361', 'kiril', 'john', 968666668)";
       stmt.executeUpdate(sql);
          
       System.out.println("Records inserted into the table...");
     } catch (Exception e) {
       e.printStackTrace();
     }
  }

} </syntaxhighlight>

JDBC drivers

JDBC drivers function as client-side interfaces that translate requests from Java applications into database management system (DBMS) protocols.

Providers

Oracle offers a variety of JDBC drivers from multiple vendors.
Simba Technologies provides an SDK designed for creating custom JDBC drivers aimed at proprietary relational data sources.
CData Software supplies Type 4 JDBC drivers compatible with numerous applications, databases, and web APIs.
RSSBus develops Type 4 JDBC drivers for integration with applications, databases, and web services.
DataDirect Technologies markets high-performance Type 4 JDBC drivers, branded as Type 5, supporting major database platforms.
IDS Software delivers a Type 3 JDBC driver featuring concurrent access, result set caching, SSL encryption, and support for custom data sources.
JDBaccess is a Java persistence framework that simplifies common database tasks over JDBC for MySQL and Oracle.
JNetDirect provides Sun J2EE-certified JDBC drivers optimized for performance.
JDBCR4 facilitates JDBC access from IBM RPG programs on IBM i systems.
HSQLDB, an RDBMS under BSD license, includes its proprietary JDBC driver.
SchemaCrawler is an open-source API that utilizes JDBC to expose database metadata as plain Java objects (POJOs).

References

External links

API Javadoc documentation
API Javadoc documentation
O/R Broker Scala-based JDBC framework
SqlTool Open source command-line generic JDBC client utility compatible with any JDBC-enabled database
Comprehensive guide to JDBC URL strings across databases

Java Database Connectivity

History and implementation

Functionality

Examples

JDBC drivers

Categories

Providers

See also

References

External links