:::: MENU ::::
Browsing posts in: jdbc

DriverManager – Connection URL

As we learned earlier, the traditional way to create a connection object is to use the DriverManager class with a connection URL in the following format:

jdbc:<subprotocol>:<subname>

<subprotocol> in the URL is used to identify the JDBC driver class which will create a connection object based on information provided in <subname>. For example, “odbc” in the connection URL “jdbc:odbc:HY_FLAT” identifies the JDBC-ODBC Bridge driver. “sqlserver” in “jdbc:sqlserver://localhost:1269” identifies the Microsoft JDBC Driver.

<subname> in the URL is used to provide additional information to help the JDBC driver to identify the database server. If the database server is on remote host on the Internet, <subname> should have the following format:

jdbc:<subprotocol>://<hostname>:port<subname>

For example, “HY_FLAT” in the connection URL “jdbc:odbc:HY_FLAT” provides the data source name to help JDBC-ODBC Bridge driver to create a connection object. “//localhost:1269” in “jdbc:sqlserver://localhost:1269” provides the host name and the port number to help Microsoft JDBC Driver to create a connection object.

The DriverManager class offers 3 methods for you to create a connection object using the specified connection URL:

Connection con = DriverManager.getConnection(String url);

Connection con = DriverManager.getConnection(String url, 
  Properties info) 

Connection con = DriverManager.getConnection(String url, 
  String user, String password)

Tutorials of using connection URLs are included in other sections


DriverManager – Loading JDBC Driver

This section describes how to load a JDBC driver and register it with DriverManager

If you want to use DriverManager class to create a connection to a database server, you need to load a JDBC driver that knows how to create a connection to that database server. The loaded JDBC driver class will be automatically registered to DriverManager.

There are two ways to load a JDBC driver:

  • Using the Class.forName() method – Loading the specified driver class when you need it.
  • Using the java.lang.System property jdbc.drivers setting – Loading the specified driver classes when the first call to a DriverManager method is made.

I wrote the following program to test both ways of loading JDBC drivers. To test this program, you need to download Microsoft JDBC Driver 1.0.

 
/**
 * LoadJdbcDriver.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.sql.*;
import java.util.*;
public class LoadJdbcDriver {
  public static void main(String [] args) {
    Connection con = null;
    try {
      System.out.println("Before loading SQLServerDriver:");
      listDrivers();

// Load Microsoft JDBC Driver 1.0
      Class.forName(
        "com.microsoft.sqlserver.jdbc.SQLServerDriver");

      System.out.println("After loading SQLServerDriver:");
      listDrivers();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
  private static void listDrivers() {
    Enumeration driverList = DriverManager.getDrivers();
    while (driverList.hasMoreElements()) {
      Driver driverClass = (Driver) driverList.nextElement();
      System.out.println("   "+driverClass.getClass().getName());
    }
  }
}

Test 1: Load Microsoft JDBC Driver 1.0 with Class.forName() by follow the commands below:

C:\>javac LoadJdbcDriver.java

C:\>java -cp .;\local\lib\sqljdbc.jar LoadJdbcDriver
Before loading SQLServerDriver:
   sun.jdbc.odbc.JdbcOdbcDriver
After loading SQLServerDriver:
   sun.jdbc.odbc.JdbcOdbcDriver
   com.microsoft.sqlserver.jdbc.SQLServerDriver

Test 2: Load Microsoft JDBC Driver 1.0 with jdbc.drivers property by follow the commands below:

C:\>javac LoadJdbcDriver.java

C:\>java -cp .;\local\lib\sqljdbc.jar
   -Djdbc.drivers="com.microsoft.sqlserver.jdbc.SQLServerDriver"
   LoadJdbcDriver

Before loading SQLServerDriver:
   sun.jdbc.odbc.JdbcOdbcDriver
   com.microsoft.sqlserver.jdbc.SQLServerDriver
After loading SQLServerDriver:
   sun.jdbc.odbc.JdbcOdbcDriver
   com.microsoft.sqlserver.jdbc.SQLServerDriver

What I learned from the outputs of the two tests:

  • sun.jdbc.odbc.JdbcOdbcDriver, the JDBC-ODBC Bridge driver is loaded automatically by the JVM, because it showed up in the driver list automatically.
  • The loaded driver class is automatically registered to the DriverManager class, because I didn’t call any DriverManager method to register any class.
  • The “-Djdbc.drivers=*” option worked correctly to bring driver classes to the DriverManager class, because the Microsoft class specified in the option showed in the driver list before the call of the forName() method.

Establishing Connections from JDBC to Databases

JDBC 4.0 API offers two different ways to establish a connection to the database server:

1. Using DrirverManager Class: DriverManager.getConnection(connection_url) – The driver manager passes the connection URL to all loaded JDBC drivers, hoping that one of them will recognize the URL and creates a connection. See sample code below:

   // Loading a JDBC driver
   Class.forName("acme.db.Driver");

   // Creating a connection
   String url = "jdbc:odbc:fred";
   Connection con = DriverManager.getConnection(url,"user","pass");

2. Using DataSource Object: ds.getConnection() – A DataSource object should be configured and registered with a JNDI (Java Naming and Directory Interface) directory service only once. When a connection is needed, the registered DataSource object can be retrieved back from JNDI. A connection can be then created from the retrieved DataSource object.

   // Registering a DataSource
   VendorDataSource vds = new VendorDataSource();
   vds.setServerName("my_database_server");
   vds.setDatabaseName("my_database");
   vds.setDescription("the data source for inventory and personnel");
   Context ctx = new InitialContext();
   ctx.bind("jdbc/AcmeDB", vds);

   // Creating a connection
   Context ctx = new InitialContext();
   DataSource ds = (DataSource)ctx.lookup("jdbc/AcmeDB");
   Connection con = ds.getConnection("genius", "abracadabra");

JDK documentation suggests to use Database object to create connection objects whenever possible.


JBCD Driver Types

JDBC drivers can be implemented in 4 ways. So JDBC drivers are divided into 4 types:

JDBC Type 1: JDBC-ODBC Bridge plus ODBC Driver. See the left side of the first picture shown below. This combination provides JDBC access via ODBC drivers. ODBC binary code, and in many cases, database client code, must be loaded on each client machine that uses a JDBC-ODBC Bridge. Sun provides a JDBC-ODBC Bridge driver, which is appropriate for experimental use and for situations in which no other driver is available.

JDBC Type 2: A native API partly Java technology-enabled driver. See the right side of the first picture shown below. This type of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

JDBC Type 3: Pure Java Driver for Database Middleware. See the right side of the second picture shown below. This style of driver translates JDBC calls into the middleware vendor’s protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases.

JDBC Type 4: Direct-to-Database Pure Java Driver. See the left side of the second picture shown below. This style of driver converts JDBC calls into the network protocol used directly by DBMSs, allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access. For example, Microsoft JDBC Driver 1.0 is a Type 4 JDBC driver.

JDBC Drivers: Type 1 and 2

JDBC Drivers: Type 3 and 4


JDBC Version and History

Main new features introduced in JDBC 4.0 API are:

  • Automatic loading of java.sql.Driver
  • ROWID data type support
  • National Character Set Conversion Support
  • SQL/XML and XML Support

Note that JDBC 4.0 API has been implemented in Java SE 6. But JDBC drivers of specific database servers for JDBC 4.0 API may still not available. Since JDBC 4.0 API is backward compatible, there is no problem with using Java SE 6 with JDBC 3.0 drivers, as long as you do not use the new methods or classes there were introduced in JDBC 4.0 API


Pages:12