Does different database system implementations (e.g. MySQL, and Microsoft SQL Server) affect `DataSource` and `DriverManager` differently?

Question

Here is some comparison of using DataSource and using DriverManager from https://stackoverflow.com/a/19674833/1224441

With JDBC, we can use an abstracted class, such as java.sql.DataSource, which defines a database. However, the MySQL DataSource and a Microsoft SQL DataSource are different implementations. JDBC doesn't know which one you want to use.

So you use DriverManager to specify to JDBC that you want to use the MySQL implementation, and you load that driver's class. Say that later on you switch to Microsoft SQL. Now, all you have to do is change the DriverManager to load the Microsoft SQL driver's class, and all of your code that uses the abstract java.sql classes will still work.

I found some examples of using DataSource and using DriverManager at http://zetcode.com/tutorials/jeetutorials/datasource/

But I don't see how different implementations (e.g. implementations by MySQL, and Microsoft SQL Server) would affect DataSource and DriverManager differently. Or do I misunderstand something?

Answer 1

When you acces you database with JDBC, you will handle java.sql.Connection, java.sql.Resultset... which are interfaces. You will actually use implementations that are specific to your database.

That way most of the code you wrote for one database can be used with another one and you don't have to learn a new API each time you need to access a new DB.

You need to specify first which implementations you need which are provided by the JDBC driver. You can do it with the old school DriverManager or with the now preferred DataSource.

DriverManager

The DriverManager is a concrete class. It isn't specific to any database. In summary it is just the place where you register your driver so you can get later all the specific implementations you need. It isn't specific by itself but it provides all the specific stuff you need.

Here is an excerpt from the example you mentioned with some of my comments.

final String url = "jdbc:mysql://localhost:3306/books";

//This line returns the Class of the Jdbc drive
//It will not be used but doing this will allow 
//static initializations where it will register
//the driver to the DriverManager
Class.forName("com.mysql.jdbc.Driver");

//Here you get your connection implmentation
Connection con = DriverManager.getConnection(url, "root", "");

//MySQL specific Statement
Statement stmt = con.createStatement();
//MySQL specific ResultSet
ResultSet result = stmt.executeQuery("SELECT * FROM books");

The "trap" here is that the registration of the driver (to the DriverManager) is done during the static init done when you call the the Class.forName method.

For your information, here is the piece of code in com.mysql.jdbc.Driver doing the the driver registration in the init:

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    //
    // Register ourselves with the DriverManager
    //
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

DataSource

Using DataSource is more suitable for external configuration. DataSource is an interface and the implementation depends directly on the database.

In your example, the Driver implementation is specified in the web.xml file.

First we define the driver to be able to find it with JNDI

<database>
  <jndi-name>jdbc/mysql</jndi-name>
  <driver>
    <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type>
    <url>jdbc:mysql://localhost:3306/books</url>
    <user>root</user>
    <password></password>
  </driver>
<database>

And then we pass the reference to the servlet :

<servlet>
 <servlet-name>datasource</servlet-name>
 <servlet-class>com.zetcode.DataSourceExample</servlet-class>
 <init>
    <data-source>${jndi:lookup('jdbc/mysql')}</data-source>
 </init>
</servlet>

That way, you had not to write any specific line of code. If you change your database, (in theory) you just have to change your configuration. Not the code.

Important notes

1. As Kayaman mentioned in the comments calling Class.forName is no longer needed since Java 6. All you need is having the driver in your classpath.
2. As a reminder using DriverManager is not recommended anymore. See this excerpt from the javadoc

NOTE: The DataSource interface, new in the JDBC 2.0 API, provides another way to connect to a data source. The use of a DataSource object is the preferred means of connecting to a data source.