Java DataBase Connectivity -JDBC-



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

Database Applications with JDBC

Basic principle (embedded database)

Rule(s)

<!-- 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)

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)

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)

Example JDBC_GlassFish.Java.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)

<!-- 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)

Example ODBC.Java.zip 

<dependency>
    <groupId>net.sf.ucanaccess</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.0.1</version>
</dependency>
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();
            }
        }
    }
}