Write access, coding!

JDBC applications require a per project driver configuration:

Figure 765. pom.xml driver runtime scope Slide presentation Create comment in forum
...
<dependency>
  <groupId>postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.1-901-1.jdbc4</version>
  <scope>runtime</scope>
</dependency> ...

exercise No. 22

Why <scope>runtime</scope>? Create comment in forum

Q:

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

A:

According to Figure 752, “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 766. Person table Slide presentation Create comment in forum
CREATE TABLE Person (
   name CHAR(20)
  ,email CHAR(20) UNIQUE
)

Figure 767. Objective: insert person record Slide presentation Create comment in forum

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 768. JDBC™ backed data insert Slide presentation Create comment in forum
// 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 769. Result Slide presentation Create comment in forum

Execution yields:

Successfully inserted 1 dataset(s)

Note

The database server returns the number of inserted datasets.


Figure 770. Two JDBC configurations Slide presentation Create comment in forum
  1. IDE level.

  2. Project level (Maven).


exercise No. 23

Exception on inserting objects Create comment in forum

Q:

Set up Figure 768, “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 772. Why properties? Slide presentation Create comment in forum
  • Connection parameter changes require recompilation!

  • Parameters should be configurable.

Possible solution: Java properties.


Figure 773. message.properties string externalization Slide presentation Create comment in forum
message.properties string externalization

Figure 774. Properties code sketch Slide presentation Create comment in forum
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 776. Database related unit test phases Slide presentation Create comment in forum
  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 777. Implementing unit tests Slide presentation Create comment in forum
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 778. Spring is your friend Slide presentation Create comment in forum

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

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

Figure 779. Project layout Slide presentation Create comment in forum
Project layout

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

Figure 781. Employ AutoCloseable Slide presentation Create comment in forum

Using try-with-resources statement.

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

exercise No. 24

Interactive inserts, connection properties, error handling and unit tests Create comment in forum

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

Avoiding intermediate SQL file export Create comment in forum

Q:

In Generating SQL INSERT statements from XML data shows a SAX application transforming XML product catalog instances into a series of SQL statements. Implement a JDBC based application which reads the following type of data and writes it to a relational database:

<catalog>
  <item orderNo="3218">Swinging headset</item>
  <item orderNo="9921">200W Stereo Amplifier</item>
</catalog>

Error handling may be implemented by simply issuing a corresponding message before exiting the application. In order to assure data integrity transferring data shall be realized in an all or nothing fashion by grouping all INSERTs into a single transaction. You may want to read about setAutoCommit(boolean autoCommit) and commit() for this purpose.

A:

This solution requires a mvn install on dependent project saxerrorhandler:

exercise No. 26

Interfaces and classes in JDBCCreate comment in forum

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 752, “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. 27

Closing JDBC™ connections Create comment in forum

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

Driver dispatch mechanism Create comment in forum

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:mysql://someserver.com:3306/someDatabase this is just jdbc:mysql. 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.