The java.sql package contains mostly interfaces. When and how are these interfaces implemented while connecting to database?

The implementation of these interfaces is all part of the driver. A JDBC driver is not just one class – it is a complete set of database-specific implementations for the interfaces defined by the JDBC. These driver classes come into being through a bootstrap process. This is best shown by stepping through the process of using JDBC to connect to a database, using Oracle's type 4 JDBC driver as an example:

* First, the main driver class must be loaded into the VM:

Class.forName("oracle.jdbc.driver.OracleDriver");

The specified driver must implement the Driver interface. A class initializer (static code block) within the OracleDriver class registers the driver with the DriverManager.

* Next, we need to obtain a connection to the database:

String jdbcURL = "jdbc:oracle:thin:@www.jguru.com:1521:ORCL";

Connection connection = DriverManager.getConnection(jdbcURL);

DriverManager determines which registered driver to use by invoking the acceptsURL(String url) method of each driver, passing each the JDBC URL. The first driver to return "true" in response will be used for this connection. In this example, OracleDriver will return "true", so DriverManager then invokes the connect() method of OracleDriver to obtain an instance of OracleConnection. It is this database-specific connection instance implementing the Connection interface that is passed back from the DriverManager.getConnection() call.

* The bootstrap process continues when you create a statement:

Statement statement = connection.createStatement();

The connection reference points to an instance of OracleConnection. This database-specific implementation of Connection returns a database-specific implementation of Statement, namely OracleStatement

* Invoking the execute() method of this statement object will execute the database-specific code necessary to issue an SQL statement and retrieve the results:

ResultSet result = statement.executeQuery("SELECT * FROM TABLE");

Again, what is actually returned is an instance of OracleResultSet, which is an Oracle-specific implementation of the ResultSet interface. So the purpose of a JDBC driver is to provide these implementations that hide all the database-specific details behind standard Java interfaces.