Write access, coding!

JDBC applications require a per project driver configuration:

Figure 846. pom.xml driver runtime scope Slide presentation
...
<dependency>
  <groupId>postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.1-901-1.jdbc4</version>
  <scope>runtime</scope>
</dependency> ...

exercise No. 1

Why <scope>runtime</scope>?

Q:

Why is the JDBC driver dependency not being required at compile time?

A:

According to Figure 833, “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.

So how does it actually work with respect to coding? We first prepare a database table:

Figure 847. Person table Slide presentation
CREATE TABLE Person (
   name CHAR(20)
  ,email CHAR(20) UNIQUE
)

Figure 848. Objective: insert person record Slide presentation
  • Java application executing:

    INSERT INTO Person VALUES('Jim', 'jim@foo.org')
  • No database read required (No java.sql.ResultSet).

  • Success / failure related database return parameter.


Figure 849. JDBC™ backed data insert Slide presentation
// Step 1: Open connection to database server
final Connection conn = DriverManager.getConnection (
  "jdbc:postgresql://localhost/hdm",         // Connection parameter URL
  "hdmuser",                                 // Username
  "XYZ");                                    // Password

// Step 2: Create a Statement instance
final Statement stmt = conn.createStatement();

// Step 3: Execute the desired INSERT
final int updateCount = stmt.executeUpdate(
  "INSERT INTO Person VALUES('Jim', 'jim@foo.org')");

// Step 4: Give feedback to the end user
System.out.println("Successfully inserted " + updateCount + " dataset(s)");

Figure 850. Result Slide presentation
  • Execution yields:

    Successfully inserted 1 dataset(s)
  • Note: The database server returns the number of inserted / modified / deleted datasets.


Figure 851. Two JDBC configurations Slide presentation
  1. IDE level.

  2. Project level (Maven).


exercise No. 2

Exception on inserting objects

Q:

Set up Figure 849, “JDBC™ backed data insert ” as a Maven project yourself. Create a corresponding table prior to executing your application.

Execute your application twice. What happens? Give an explanation.

A:

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'.

Missing exception handling:
public static void main(String[] args)
  throws SQLException { ...
Hard coded connection parameters:
... = DriverManager.getConnection (
  "jdbc:postgresql://localhost/hdm", //JDBC URL
  "hdmuser",                         // Username
  "XYZ")                             // Password

Figure 853. Why properties? Slide presentation
  • Connection parameter changes require recompilation!

  • Parameters should be configurable.

Possible solution: Java properties.


Figure 854. message.properties string externalization Slide presentation
message.properties string externalization

Figure 855. Properties code sketch Slide presentation
Properties key / value file resources/jdbc.properties
jdbcurl=jdbc:postgresql://localhost/hdm
username=hdmuser
password=XYZ
ResourceBundle reading properties
// resources/jdbc.properties
ResourceBundle jdbcProperties = ResourceBundle.getBundle("jdbc");
Using ResourceBundle
... Connection conn = DriverManager.getConnection(
      jdbcProperties.getString("jdbcurl"),
      jdbcProperties.getString("username"),
      jdbcProperties.getString("password"));


Figure 857. Database related unit test phases Slide presentation
  1. Set up: Test preparation.

    • Open database connection

    • Create a required schema.

    • Optional: Insert initial data.

  2. Test: Execute JDBC CRUD / SELECT operations.

  3. Tear down:

    • Drop schema

    • Close database connection.


Figure 858. Implementing unit tests Slide presentation
public class InsertTest {
  static private Connection conn;
  static private Statement stmt;

  @BeforeClass  static public void initDatabase() throws SQLException {
    conn = DriverManager.getConnection(
      SimpleInsert.jdbcProperties.getString("jdbcurl"),
      SimpleInsert.jdbcProperties.getString("username"),...);
    ScriptUtils.executeSqlScript(conn, new ClassPathResource("schema.sql"));
    stmt = conn.createStatement();}

  @Test 
  public void test_010_insertJill() throws SQLException {
    Assert.assertEquals(1, SimpleInsert.insertPerson(
         stmt, "Jill", "jill@programmer.org"));
 }
@AfterClass  static public void releaseDatabase()
   throws SQLException {conn.close();}

Set up phase.

Test execution phase.

Tear down phase.

Figure 859. Spring is your friend Slide presentation

Getting ScriptUtils.executeSqlScript(...) to work:

<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>5.3.1</version>
  <scope>test</scope>
</dependency>

Figure 860. Project layout Slide presentation
Project layout

Figure 861. Closing connections Slide presentation
final Connection conn = DriverManager.getConnection(...);
... // CRUD operations
conn.close(); // Important! Wanna use a connection pool instead?

Figure 862. Employ AutoCloseable Slide presentation

Using try-with-resources statement.

try (final Connection conn = DriverManager.getConnection(...) {
  ... // CRUD operations
} catch (SQLException e) {...}

exercise No. 3

Interactive inserts, connection properties, error handling and unit tests

Q:

Extend the previous example by adding support for interactive insert of person data. java.util.Scanner is your friend:

Ask the end user to enter name and email addresses of persons until being satisfied. In case of constraint violations issue a warning rather then terminating the application as in:

Moreover Exception on inserting objects does not yet contain any tests: SQL schema or application modifications may lead to inconsistencies. Provide the following tests:

  1. Inserting multiple Person records.

  2. Trying to insert Person records containing email duplicates.

Tip

  1. Use a connection property file for both your application and related unit tests.

  2. Consider catching java.sql.SQLIntegrityConstraintViolationException in case of email duplicates.

  3. Implement a separate method doing the actual INSERT operations. This method can be used both in your application and unit tests:

    /**
     * <p>Try inserting new Person record.</p>
     *
     * @param statement To be used for SQL INSERT attempt.
     * @param name Person's name
     * @param email Person's email
     * @return Inserted reord count: 1 on successful INSERT,
     *  0 in case of duplicate email violating UNIQUE constraint.
     *
     * @throws SQLException To be thrown in case of
     * non - {@link SQLIntegrityConstraintViolationException}
     * errors.
     */
    static public int insertPerson(
      final Statement statement, final String name, final String email)
        throws SQLException {...}

A:

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

exercise No. 4

Interfaces and classes in JDBC

Q:

The JDBC™ standard mostly defines interfaces like Connection and Statement. Why are these not being defined as classes? Moreover why is DriverManager being defined as a class rather than an interface?

You may want to supply code examples backing your argumentation.

A:

Figure Figure 833, “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:

  • Modify and recompile / redeploy our code.

  • Introduce some sort of dispatch mechanism supporting a fixed (albeit not extensible!) set of databases beforehand:

    public void someMethod(final String vendor){
    
      final Connection conn;
    
      switch(vendor) {
         case "ORACLE":
            conn = new OracleConnection();
            break;
    
         case "DB2":
            conn = new Db2Connection();
            break;
    
         default:
            conn = null;
            break;
      }
      ...
    }

    Each time adding a new database requires extending the above code.

exercise No. 5

Closing JDBC™ connections

Q:

Why is it important to call the close() method for Connection and / or Statement instances?

A:

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.

exercise No. 6

Driver dispatch mechanism

Q:

In exercise Interfaces and classes in JDBC we saw a hypothetic way to resolve the interface/class resolution problem by using a switch clause. How is this switch clause's logic actually realized in a JDBC™ based application? (behind the scenes)

Hint: Read the documentation of java.sql.DriverManager.

A:

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:

protocol driver instance
jdbc:postgresql Postgresql driver instance
jdbc:oracle Oracle driver instance
... ...

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.