Introduction to JDBC™

According to Figure 849, “JDBC™ architecture ” a JDBC™ based application requires just interfaces rather than classes at compile time. The actual instances of java.sql.Connection, java.sql.Statement and friends will be created during the bootstrap process starting from java.sql.DriverManager.

We require a database table resources/schema.sql prior to execution:

CREATE TABLE Person (
   name char(80)
  ,email CHAR(20) UNIQUE
)

Building the executable jar and running de.hdm_stuttgart.sda1.insert.SimpleInsert yields:

goik@goiki Minimum> mvn package
[INFO] Scanning for projects...
...
[INFO] Replacing .../Jdbc/Insert/Minimum/target/insert_user-0.1.jar
  with .../Jdbc/Insert/Minimum/target/insert_user-0.1-shaded.jar

goik@goiki Minimum> java -jar /.../Jdbc/Insert/Minimum/target/insert_user-0.1.jar
Successfully inserted 1 dataset(s)

We may check our database:

MariaDB [hdm]> SELECT * FROM Person;
+------+-------------+
| name | email       |
+------+-------------+
| Jim  | jim@foo.org |
+------+-------------+
1 row in set (0.00 sec)

A second invocation results in a runtime error:

The exception relates to a constraint violation with respect to the UNIQUE attribute email in our schema definition file resources/schema.sql: We cannot add a second entry having the same value 'jim@foo.org'.

Our last exercise's database schema resources/schema.sql may remain untouched. Solution:

Figure Figure 849, “JDBC™ architecture ” depicts JDBC™ being a vendor independent architecture. Oracle for example may implement a class com.oracle.jdbc.OracleConnection:

package com.oracle.jdbc;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;

public class OracleConnection implements Connection {

...

Statement createStatement(int resultSetType,
                        int resultSetConcurrency)
                          throws SQLException) {
  // Implementation omitted here due to
  // limited personal hacking capabilities
  ...
}
...
}

Using JDBC™ interfaces rather than vendor specific classes allows for decoupling an application from a specific database platform. It requires a database vendor's implementation not to be exposed to our own Java™ code but to be encapsulated by a set of interfaces.

Regarding the special role of DriverManager we notice the need for a starting point: An application requires an initial entry point for accessing a given framework. In theory (BUT NOT IN PRACTICE!!!) the following (ugly) code might be possible:

package my.personal.application;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;

public someClass {

  public void someMethod(){

      Connection conn = new OracleConnection(); // bad idea!
      ...
  }
 ...
}

The problem with this approach is its explicit constructor call: Whenever we want to use another database we have two possibilities:

A JDBC™ connection ties network resources (socket connections). These may get used up if e.g. new connections are being established within a loop.

The situation is similar to memory leaks in programming languages lacking a garbage collector.

Prior to opening a Connection a JDBC™ driver registers itself at the java.sql.DriverManager. For this purpose the standard defines the static registerDriver(Driver) method. On success driver is being added to an internal dictionary:

So whenever the method getConnection() is being called the java.sql.DriverManager will scan the JDBC™ URL and isolate the protocol part. If we start with jdbc:postgresql://someserver.com:5432/someDatabase this is just jdbc:postgresql. The value is then being looked up in the above table of registered drivers to choose an appropriate instance or null otherwise. This way our hypothetic switch including the default value null is actually implemented.