JDBC demystified.

JDBC (Java DabaBase Connectivity) is an API interface for relational databases connections, such as Oracle RDBMS, SQL Server, MySQL, and Microsoft SQL DB.

In Java 7 includes JDBC 4.1 reduces the amounts of code required to work with databases. It is most commonly used as in web-based applications hosted in J2EE servers, including JBOSS, Tomcat, WebSphere.

Android has its own API SQLite to work with local database. Calls can be made from Android application to access larger databases through web services hosted by middleware servers.

The Spring application framework includes something called JDBC Template. It simplifies the amount of code using JDBC to talk to the database. Hibernate is the most popular data mapping APIs using an object-relational mapping mechanism. It represents the database structure with Java classes and objects. In the background it's still using JDBC to communicate with the database.

Applications that use the JDBC API require drivers. A JDBC driver is a software library that encapsulates the logic required to communicate between the application and the database management system. JDBC driver rules are defined in Java Standard Edition.

A driver library package will contain specific implementations of these Java interfaces.

• Connection - which lets you connect to the database, 
• ResultSet - which encapsulates data returned from the database, 
• Statement - requests to the database
• PreparedStatement - represent requests to the database
• CallableStatement - represent requests to the database

Typically, a driver package can be downloaded from the database vendors themselves, a MySQL driver from MySQL an Oracle driver for Oracle, et cetera. Most of JDBC drivers will support these five interfaces.

There are four distinct types of drivers, distinguished by their architecture.

Type 1 JDBC Driver

• JDBC-ODBC bridge driver + ODBC driver
• it is the oldest type. 
• Installed on the client system.
• Started in the mid to late '90s when JDBC got started, ODBC or the Open Database Connectivity protocol was the dominant model for communicating with the database. 
• At runtime, requests go from the application through the JDBC API to the Bridge driver from there to the ODBC driver and then to the database. 
• Not fast, but it is dependable, 
• Can work with any database for which an ODBC driver existed (pretty much every RDBMS) 
• Cons: 
• the ODBC Bridge driver is not 100% Java and therefore not portable between operating systems. 
• working with two drivers and both have to be on the same computer as the application, so you have increased maintenance.
• the ODBC driver has to match the database version, and so if database on the server, is updated, all the client applications have to be updated as well. 

Type 2 JDBC Driver

• Native protocol API driver + Java driver
• Both are installed on the client system just like the Bridge driver and ODBC driver.
• Fast - because primarily working with native APIs, you get the best performance.
• 
  
• Not 100% Java so it's not portable between operating systems.
• The native API driver has to be installed on the application client and maintained, and once again, if the database is updated, the client software has to be updated as well.

Type 3 JDBC Driver

• Net protocol + Java driver
• installed in multiple locations
• 100% Java driver that's installed in the client along with the application
• a middleware server which hosts its own application
• requests go at runtime from the application to the Type 3 driver that's installed on the client, to the network to the middleware server and then to the database.
• The middleware driver can be native, and so the communication between the middleware and the database can be very fast.
• but at the cost of the maintenance challenges with more than one driver to maintain. 

Type 4 JDBC Driver

• All 100% Java thin driver + 100% Java driver
• the most common.
• Only one driver package with Java application itself.
• Can be on a client computer, in a web environment on J2EE server.
• Requests go from the application to the driver that's on the client and then through JDBC through the thin driver to the database server if it's out on the web, or to the database file if it's on the local hard disk. 
• With the Java thin driver, you're communicating directly from the application to the database. No additional layers to install or maintain so maintenance is greatly simplified. 
• Cons: a different driver package is needed for each database to work with.

Working with Multiple Database Types in Single Application

• Most applications will only use a single database type, but if you're working with more than one database management system, you'll need to provide multiple drivers. 
• For example, you may have one MySQL database server hosted in the Cloud that is accessible over the web and an Apache Derby SQL Database that is initialized with local files and runs in the same Java process of the application.
• Use Type 4 pure Java drivers to make the code as portable as possible. That's the idea of encapsulated applications.

Simple Type 4 Java driver example

private Connection getConnection (String dbName) {
    Connection connection = null;
    try {
        String dbDirectory = "./Resources";
        System.setProperty("derby.system.home", dbDirectory);
        String dbUrl = "jdbc:derby:" + dbName + ";create=true";

        connection = DriverManager.getConnection (dbUrl);
        return connection;
    }
    catch (SQLException e) {
        for (Throwable t : e) t.printStackTrace();
        System.err.println(e);
        return null;
    }
}

private void getResultSet (Connection cn) {
    String sql  = "SELECT * FROM Runners ";
        try ( PreparedStatement ps = cn.prepareStatement(sql);
              ResultSet rs = ps.executeQuery();
        ) {
              readRS(rs);
              disconnect();
        }
        catch (SQLException e) {
              System.err.println(e);
        }
}

private void readRS (ResultSet rs) {
    try {
        while (rs.next()) {
            ThreadRunner t = new ThreadRunner 
                (rs.getString(1), rs.getInt(2), rs.getInt(3));
            t.setName(rs.getString(1));
            runners.add( t );
         }
     }
     catch (SQLException e) {
         System.err.println(e);
     }
}

private boolean disconnect () {
    try {
        String shutdownURL = "jdbc:derby:;shutdown=true";
        DriverManager.getConnection(shutdownURL);
    }
    catch (SQLException e) {
        if (e.getMessage().equals(
             "Derby system shutdown."))
        return true;
    }
    return false;
}