Java DataBase Connectivity (JDBC)

Java SE 11 Developer certification (exam number 1Z0-819) here…

Database Applications with JDBC

Connect to and perform database SQL operations, process query results using JDBC API

Basic principle (embedded database)

Rule(s)

A JDBC driver as a packaged Java library is required depending upon the chosen Relational DataBase Management System (RDBMS), here JavaDB (a.k.a. Apache Derby). This driver may be dowloaded as a dependency; this may occur by means of Maven.

<!-- Embedded database driver (caution: ver. 10.16.1.1 is for JVM > 1.8) -->
<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.16.1.1</version>
    <scope>runtime</scope>
</dependency>

Example JDBC_JavaDB_Embedded.Java.zip

public final class JDBC_Embedded {
    private final java.sql.Connection _connection;
    public JDBC_Embedded() throws java.sql.SQLException {
// MAVEN dependency on 'derby-10.16.1.1.jar':
        _connection = java.sql.DriverManager.getConnection("jdbc:derby:memory:JDBC_test;create=true");
        java.sql.DatabaseMetaData dmd = _connection.getMetaData();
        if (dmd.getSQLStateType() == java.sql.DatabaseMetaData.sqlStateSQL99) {
            System.out.print(dmd.getDatabaseProductName() + " " + dmd.getDatabaseProductVersion() + " is SQL99-compliant\n");
        } else {
            System.out.print(dmd.getDatabaseProductName() + " " + dmd.getDatabaseProductVersion() + " isn't SQL99-compliant\n");
        }
        _connection.close();
    }
    public void close() {
        try {
            if (_connection != null) {
                _connection.close();
            }
        } catch (java.sql.SQLException sqle) {
            throw new RuntimeException(sqle);
        }
    }
    public static void main(String[] args) {
        JDBC_Embedded test = null;
        try {
            test = new JDBC_Embedded();
        } catch (java.sql.SQLException sqle) {
            sqle.printStackTrace();
            if (test != null) {
                test.close();
            }
        }
    }
}

Client/server database

<!-- Client/server database driver (caution: ver. 10.16.1.1 is for JVM > 1.8) -->
<!-- Client: -->
<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.16.1.1</version>
</dependency>
<!-- Server: -->
<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbynet</artifactId>
    <version>10.16.1.1</version>
</dependency>

Example JDBC_JavaDB_Client-Server.Java.zip

public final class JDBC_Client {
    private java.sql.Connection _client;
    private org.apache.derby.drda.NetworkServerControl _server = null;
    public JDBC_Client() throws java.sql.SQLException {
        try { // 'derbynet' dependency:
            _server = new org.apache.derby.drda.NetworkServerControl(java.net.InetAddress.getByName("localhost"), 2020);
            _server.start(null);
            System.out.println("Start JDBC server (separate thread)...");
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 'derbyclient' dependency:
        _client = java.sql.DriverManager.getConnection("jdbc:derby://localhost:2020/JDBC_test;create=true");
        java.sql.DatabaseMetaData dmd = _client.getMetaData();
        if (dmd.getSQLStateType() == java.sql.DatabaseMetaData.sqlStateSQL99) {
            System.out.print(dmd.getDatabaseProductName() + " " + dmd.getDatabaseProductVersion() + " is SQL99-compliant\n");
        } else {
            System.out.print(dmd.getDatabaseProductName() + " " + dmd.getDatabaseProductVersion() + " isn't SQL99-compliant\n");
        }
        _client.close();
        try {
            _server.shutdown();
            System.out.println("Stop JDBC server...");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void close() {
        try {
            if (_client != null) {
                _client.close();
            }
        } catch (java.sql.SQLException sqle) {
            throw new RuntimeException(sqle);
        }
    }
    public static void main(String[] args) {
        JDBC_Client test = null;
        try {
            test = new JDBC_Client();
        } catch (java.sql.SQLException sqle) {
            sqle.printStackTrace();
            if (test != null) {
                test.close();
            }
        }
    }
}

Database schema creation

Rule(s)

java.sql.Statement is a key class for setting up and next running SQL statements within Java.

Example BCMS.PauWare2Web.zip

try ( java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:derby:memory:BCMS_database;create=true")) {
    java.sql.Statement statement = connection.createStatement();
    statement.execute("CREATE TABLE Crisis(\n"
            + "crisis_id INT not null GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n"
            + "fire_truck_number integer,\n"
            + "police_vehicle_number integer,\n"
            + "constraint Crisis_key primary key(crisis_id))");
    statement.execute("CREATE TABLE Fire_truck(\n"
            + "fire_truck_name varchar(30),\n"
            + "constraint Fire_truck_key primary key(fire_truck_name))");
    statement.execute("INSERT INTO Fire_truck VALUES('Fire truck #1')");
    statement.execute("INSERT INTO Fire_truck VALUES('Fire truck #2')");
    // Etc.
} catch (java.sql.SQLException sqle1) {
    System.err.println(BCMS.class.getSimpleName() + " database probably already exists? " + sqle1.getMessage());
    try ( java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:derby:memory:BCMS_database")) {
        connection.createStatement().execute("DELETE FROM Crisis"); // 'on delete cascade'
    } catch (java.sql.SQLException sqle2) {
        System.err.println(BCMS.class.getSimpleName() + " database persistent error: " + sqle2.getMessage());
        System.exit(-1);
    }
}

Get and process data

Rule(s)

java.sql.ResultSet is a key class for getting and processing data while java.sql.Statement provides several SQL execution supports like, for example, execute, executeQuery or executeUpdate depending upon the desired action on the data.

Example BCMS.PauWare2Web.zip

try ( java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:derby:memory:BCMS_database")) {
    connection.createStatement().executeUpdate("INSERT INTO Crisis (fire_truck_number,police_vehicle_number) VALUES(0,0)");
    // Last (current) crisis:
    java.sql.ResultSet rs = connection.createStatement().executeQuery("SELECT MAX(crisis_id) FROM Crisis");
    if (rs.next()) {
        _crisis_id = rs.getInt(1);
    }
    assert (_crisis_id != -1);
} catch (java.sql.SQLException sqle) {
    // Error...
}

Example BCMS.PauWare2Web.zip

public java.util.List<String> get_fire_trucks() throws java.sql.SQLException {
        java.util.List<String> fire_trucks = new java.util.ArrayList<>();
        try ( java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:derby:memory:BCMS_database")) {
// 'TYPE_SCROLL_INSENSITIVE' is not default, cursor may move, e.g. 'rs.beforeFirst();':
// 'CONCUR_READ_ONLY' is default, no data change can be made through 'rs' ('CONCUR_UPDATABLE' as alternative may be not supported by driver):
            java.sql.ResultSet rs = connection.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY).executeQuery("SELECT * FROM Fire_truck");
            rs.beforeFirst();
            while (rs.next()) {
                fire_trucks.add(rs.getString("fire_truck_name"));
            }
        }
        return fire_trucks;
}

Transaction management

Rule(s)

As for SQL, commit and rollback facilities are necessary; they come from the Java Transaction API (JTA). This is particulary important in distributed environments like Java Enterprise Edition (Java EE) and compatible products, e.g., GlassFish. commit and rollback occur by default at predefined phases, namely closing database resources for commit and at Java exception time for rollback. Overriding default behavior (line 9) often is a requirement.
Data may be processed in batch (lines 13,16,17) by means of java.sql.PreparedStatement. Transaction boundaries may then be based on this batch (lines 18-24).

Example JDBC_GlassFish.zip

public class JDBC_GlassFish {
    javax.naming.Context _context;
    javax.sql.DataSource _data_source;
    java.sql.Connection _connection;
    public JDBC_GlassFish() throws javax.naming.NamingException, java.sql.SQLException {
        _context = new javax.naming.InitialContext();
        _data_source = (javax.sql.DataSource) _context.lookup("jdbc/New_York_City_Penitentiary");
        _connection = _data_source.getConnection();
        _connection.setAutoCommit(false);
        java.sql.PreparedStatement ps = _connection.prepareStatement("INSERT INTO Motive (motive_number, motive_label) VALUES (?,?)");
        ps.setString(1, "02");
        ps.setString(2, "assault and battery");
        ps.addBatch(); // ps.executeUpdate();
        ps.setString(1, "03");
        ps.setString(2, "fraud");
        ps.addBatch(); // ps.executeUpdate();
        ps.executeBatch();
        if (ps.getWarnings() == null && _connection.getTransactionIsolation() != java.sql.Connection.TRANSACTION_NONE) {
            _connection.commit();
        } else {
            if (_connection.getTransactionIsolation() != java.sql.Connection.TRANSACTION_NONE) {
                _connection.rollback();
            }
        }
        _connection.close();
        _context.close();
    }

    public void close() {
        try {
            if (_connection != null) {
                _connection.close();
            }
            if (_context != null) {
                _context.close();
            }
        } catch (javax.naming.NamingException ne) {
            throw new RuntimeException(ne);
        } catch (java.sql.SQLException sqle) {
            throw new RuntimeException(sqle);
        }
    }

    public static void main(String[] args) {
        JDBC_GlassFish test = null;
        try {
            test = new JDBC_GlassFish();
        } catch (Exception e) {
            e.printStackTrace();
            if (test != null) {
                test.close();
            }
        }
    }
}

Other JDBC drivers

Rule(s)

A RDBMS is in general pre-installed and operating; it is then available through a URL. Vendors provide homemade JDBC-compliant drivers as it is the case for Oracle RDBMS.

<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>21.7.0.0</version>
</dependency>

Example JDBC_Oracle.Java.zip

public final class JDBC_Oracle {
    private final String _user = "barbier";
    private final String _password = "<password>"; // Please replace...
    private final java.sql.Connection _connection;
    public JDBC_Oracle() throws java.sql.SQLException {
        java.sql.DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        _connection = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@scinfe098.univ-pau.fr:1521:etud", _user, _password);
        java.sql.Statement statement = _connection.createStatement();
        statement.execute("DROP TABLE Individual"); // Clean up prior execution...
        statement.execute("CREATE TABLE Individual(\n" // For test only...
                + "individual_id INTEGER,\n"
                + "given_name VARCHAR(30),\n"
                + "surname VARCHAR(30),\n"
                + "CONSTRAINT Individual_key PRIMARY KEY(individual_id))");
// Administrative API (metadata) specific to Oracle RDBMS:
        String query = "SELECT * FROM all_objects WHERE owner='" + _user.toUpperCase() + "'";
        java.sql.ResultSet result_set = statement.executeQuery(query);
        java.sql.ResultSetMetaData metadata = result_set.getMetaData();
        while (result_set.next()) {
            for (int i = 1; i <= metadata.getColumnCount(); i++) {
                System.out.print(result_set.getString(i) + "(Searchable: " + metadata.isSearchable(i) + ") ");
            }
            System.out.println();
        }
        // result_set.close();
        // statement.close(); // => 'result_set.close();'
        _connection.close(); // => 'statement.close();'
    }
    public void close() {
        try {
            if (_connection != null) {
                _connection.close();
            }
        } catch (java.sql.SQLException sqle) {
            throw new RuntimeException(sqle);
        }
    }

    public static void main(String[] args) {
        JDBC_Oracle test = null;
        try {
            test = new JDBC_Oracle();
        } catch (java.sql.SQLException sqle) {
            sqle.printStackTrace();
            if (test != null) {
                test.close();
            }
        }
    }
}

Rule(s)

Legacy databases based on Microsoft Access RBDMS may also be concerned provided that a JDBC-compliant exists, for example, UCanAccess.

<dependency>
    <groupId>net.sf.ucanaccess</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.0.1</version>
</dependency>

Example ODBC.Java.zip

public final class ODBC_test { // Note that UCanAccess *DOES NOT* require any ODBC configuration...
    private String _data_source_name;
    private String _user;
    private String _password;
    private java.sql.Connection _connection;
    public ODBC_test() throws java.sql.SQLException {
        System.out.println("Working Directory: " + System.getProperty("user.dir"));
        // https://ucanaccess.sourceforge.net/site.html
        _connection = java.sql.DriverManager.getConnection("jdbc:ucanaccess://" + System.getProperty("user.dir") + "/Prison_de_Nantes.mdb;memory=false");
        java.sql.DatabaseMetaData metadata = _connection.getMetaData();
        System.out.println("Product name: " + metadata.getDatabaseProductName() + ", Product version: " + metadata.getDatabaseProductVersion());
        java.sql.ResultSet tables = metadata.getTables(null, null, "Decision", null);
        // Printing the column name and type:
        while (tables.next()) {
            System.out.println("Table name: " + tables.getString("Table_NAME"));
            System.out.println("Table type: " + tables.getString("TABLE_TYPE"));
        }
        _connection.close();
    }
    public void close() {
        try {
            if (_connection != null && !_connection.isClosed()) { // Second member of 'if' is optional since closing an already closed database does not raise any problem...
                _connection.close();
            }
        } catch (java.sql.SQLException sqle) {
            throw new RuntimeException(sqle);
        }
    }
    public static void main(String[] args) {
        ODBC_test test = null;
        try {
            test = new ODBC_test();
        } catch (java.sql.SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            if (test != null) {
                test.close();
            }
        }
    }
}