What is a JDBC driver?

[Paul Crane]

I understand that JDBC is an API that provides some functionality to get access to a database. However, I have heard that there are 4 types of the drivers, what are its advantages and differences?

[Stephan van Hulst]

Where have you heard that there are four types of drivers, Please QuoteYourSources.

There are many different database vendors who provide even more different database management systems. Each DBMS might operate slightly differently, so for each DBMS that is slightly different you need a different implementation of the JDBC API to translate API calls to actual DBMS operations.

We call a library that implements the JDBC API for a specific set of DBMSs a "JDBC driver". I'm not aware that there are specific "types" of drivers.

[Paul Crane]

Look: https://www.tutorialspoint.com/jdbc/jdbc-driver-types.htm

[Tim Moores]

Yes, that was the situation when JDBC was new, some 20+ years ago. But the situation has changed a lot since. So let's review it:

If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.

Type 4 is almost always to be preferred, whether your app accesses one type of DB, or multiple.

If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.

This statement makes no sense to me. There is no difficulty using several drivers for different DBs within one app. I've never used a type 3 driver, but it sounds needlessly complicated for almost all circumstances.

Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not available yet for your database.

The "not yet" part makes it sound as if a type 3 driver is to be preferred, but -see above- that is not so. There used to be drivers out there that had native components (and a some still do), but for all major DBs type 4 drivers are available. At this point the JVM as well as those drivers have been optimized to the point where native drivers offer no performance advantage.

The type 1 driver is not considered a deployment-level driver, and is typically used for development and testing purposes only.

The JDBC/ODBC bridge has been removed from the JRE, so it's no longer an option.

TL;DR - use a type 4 driver, and never think about these types again. Also keep in mind that some stuff you read online may be outdated, or flat out wrong. Tutorialspoint.com is not generally a bad resource, though.

[Stephan van Hulst]

Note that most applications that require access to a relational database are usually web applications that run inside a web application container.

In such cases, it's the container's responsibility to provide a DataSource to your application. It's the job of the system administrator to configure the correct database settings and database drivers in the application container. Your application absolutely must not provide its own database drivers, nor should it even know or care about the type of database it's interacting with, as long as that database can be connected to through a DataSource that is provided by the application container.

If your application is a desktop application that connects to a database, then you might have to bundle a driver with your application.

[Paul Crane]

Okay. You wrote that the JDBC bridge has been removed, so there’s no need to use that? I heard about DataSource, but I want to understand how the old way works. I don’t even know how the Class.forName("driver name") works, so all I heard is that it creates an object in the memory or something like that, so why I don’t have to write the import then?

[Tim Holloway]

Class.forName is a method that searches the classpath for a named class and loads it. Because JDBC Drivers are plug-replaceable modules and not hard-linked to the application, it used to be that you needed to force-load them. That's no longer true, as there's a special entry in the MANIFEST of the JDBC driver JAR that tells the JVM to make the connection, so Class.forName is not longer needed (it's relatively harmless to keep it, just shows someone's out of date).

There are 2 ways of obtaining a Connection for database access. One is the brute-force DriverManager logic, the other is to use a DataSource. At heart, all Connections are obtained from a DriverManager, but for multi-user systems, a DataSource is the preferred way to go, since it's backed by a Database Connection Pool (DBCP). A Database Connection Pool keeps a set of pre-allocated Connections. When you request a Connection from the pool, a façade Connection fronts the actual Connection. It mostly passes method calls through to the actual connection, but its close() method doesn't close the Connection, but instead returns it to the pool.

The advantage of the DBCP is that Connections require a lot of overhead to create, so if you can keep re-using Connections that were already created, you can operate more efficiently. Note that when using pool Connections, you should obtain, use, and release (close) as quickly as possible to facilitate efficient sharing. And, of course, absolutely, positively NEVER pass a Connection between web service requests. Always close your Connection(s)  before returning from the current request process.

It's not true that databases are only used by webapps. There are also offline utilities run as webapps, for example to do heavy-duty end-of-day tidying up. And there are non-web containers such as the OSGi-based Apache Karafe system and ETL (extract/transform/load) utilities such as Talend, Pentaho, and the like.

As a general rule ALL webapps should be using a Connection Pool, and in fact JEE servers can (and should) be creating and managing that pool, not the application itself. For offline services, the need for a pool depends on whether the database operations are limited to a single thread or if there are possibly many competing threads. There's a standard DBCP architecture designed to allow plugin replacement of different pool managers for systems such as Tomcat. One such pool manager is Apache DBCP. You can use that in your own application code, as it's a fairly simple library.

[Tim Moores]

Paul Crane wrote:You wrote that the JDBC bridge has been removed, so there’s no need to use that?

That was used for accessing ODBC data sources. If you need to use ODBC (but why would you?), you would have a need for something like it. But most DBs have their own JDBC drivers these days, so the bridge no longer serves much of a purpose. The notable exception is MS Access, for which a commercial JDBC driver is available, and the free Jackcess library (which uses a different API than JDBC).

[Paul Crane]

Thank you for all the information. So, JDBC is a platform that has all the functionality for working with databases. As I got it, all the classes and interfaces will always be the same, even if I use another database. JDBC is a module, so I have to download the jars with all the classes, or use Maven to automatically download them while runtime. So, all the functionality is available, and I can use it. I’m currently using MySQL (Workbench). I have noticed that when I’m trying to get access to a database through the DriverManager, I have to write a full URL. This URL consists of "jdbc:mysql://localhost…", so what is that? Is my database located on the local computer? It also has a port(I can set it myself), so the URL above looks so awkward.

[Paul Clapham]

Yes, the database server is a program which runs on a computer, where the files are located which contain the database tables and supporting information. It listens for requests over the network and handles them in the usual way that network apps do. It will accept a connection request and subsequently deal with messages sent via that connection.

Now, all of that connecting and messaging on your end is taken care of by the JDBC driver, so you don't have to. But you at least have to tell the driver where to find the server -- what computer is it running on and what port is it listening on? Hence the JDBC URL. Host name and port number are the only required information, but most drivers support a variety of other information.

[Stephan van Hulst]

Paul Crane wrote:so I have to download the jars with all the classes, or use Maven to automatically download them while runtime

Again, only if your application is not running in a container that provides a preconfigured connection pool. In that case your application doesn't have to provide any drivers, and it doesn't have to configure any connection URLs or even a user name or password. All of that is done by the administrator of the application container. Your application just requests a DataSource from the application container.

In other cases, yes. You can use Maven to download a JDBC driver that is compatible with the DMBS you want to use. Some of those libraries even provide a way to easily initialize a DataSource implementation, but if not you can always fall back on DriverManager.

[Paul Crane]

However, in my first JDBC application, I didn't use the Class.forName("driver-path-name"). Is that okay? Didn't I use a driver?! I simply did like that:

try {
        String url = "jdbc:mysql://localhost:3306/mydbtest";
        Connection connection = DriverManager.getConnection(url, "root", "123123");

        if (!connection.isClosed()) {
            System.out.println("Connection is done");
         }

         connection.close();
        } catch (SQLException ex) {
            throw new Error("Error", ex);
        }

[Stephan van Hulst]

As Tim H. pointed out, using Class.forName() is a relic of the past. Modern drivers are discovered through Java's service loading mechanism. The driver's JAR contains a text file that tells the JVM that it contains a class that implements java.sql.Driver, and DriverManager uses Java's ServiceLoader to obtain an instance of the driver without your application needing to load the driver class explicitly.

Having the driver library on the class path or module path is enough for it to be loaded when the application requires it.

[Stephan van Hulst]

Another thing, you really should be using try-with-resources when you're using connections:

try (
  var connection = ...
) {
  // do something with connection
}

This automatically closes the connection when you're done with it, even if an exception occurs while you're using the connection.

[Tim Holloway]

Yes, JDBC makes for plug-replacement of database drivers, whether to a more effective version of a specific database access or to switch to a completely different vendor's database (for example, when Amazon ditched Oracle for PostgreSQL).

The JDBC URL includes a host (and optionally a port number) because JDBC was designed to work with network-connected database servers. In a large shop it's not uncommon to have one or more machines whose sole purpose is to host one or more database instances. In fact, I have most of my own databases in a single VM.

The JDBC-ODBC bridge was never anything but a kludge. ODBC never really caught on except for Windows, anyway so there was never much call for direct ODBC access from Java apps. But the bridge was a low-performance interface and some of the databases it talked to such as FoxPro and MS Access were not designed for multi-threaded database access (for example from webapps). Or for that matter for remote access.

[Paul Crane]

Well, as I have learnt, the driver is downloaded jars which provide all the needed functionality for particular database. Is it always the same? As far as I know, JDBC is a specific standard, so all the companies providing a specific database have to implement these interfaces. So logically these jars differ from each other. Right?

[Tim Holloway]

Yes, but no. Database providers are not "required" to supply JDBC driver JARs and in fact, Microsoft SQL Server was notably lacking in that regard for quite a while.

Conversely, you don't have to be a database vendor to create a JDBC driver JAR. Before Microsoft themselves provided an SQL Server driver there were several independently-produced SQL Server JDBC drivers in use.

Nor is it always the case that a vendor only produces one Driver JAR or even just one Driver within a JAR. Sometimes they will produce more than one driver to allow for different performance needs.

And in case you're wondering how the system knows which driver to use, it's based on the JDBC URL that's used. Both to select a specific driver from a multi-driver JAR and to select a specific driver if you happen to have multiple JDBC driver JARs in your classpath. For example, if you're copying data from a MySQL database to an Oracle database.

[Paul Crane]

Okay. I fully don’t understand the Driver/DriverManager usage. It has to be a part of JDBC. So they’re contained within the jars?

[Tim Holloway]

Paul Crane wrote:Okay. I fully don’t understand the Driver/DriverManager usage. It has to be a part of JDBC. So they’re contained within the jars?

DriverManager is part of the basic JRE class set (built into Java). A JDBC Driver is a class that implements the java.sql.Driver Interface. So JDBC Driver JARs contain one or more Drivers. But there is only one DriverManager and it has static (class) methods for constructing Connections using a JDBC URL to find the appropriate Driver.

As mentioned earlier, DriverManager is used to obtain a Connection by brute force. If you are running in an environment that will have multiple open Connections, then a Datasource is preferable to using a DriverManager.

[Paul Crane]

DriverManager is used for connecting to a database, but it has to "find" the right driver? Wow. I do just write the getConnection() method, and then write the URL inside. Something must be under the hood?!

[Tim Holloway]

Paul Crane wrote:DriverManager is used for connecting to a database, but it has to "find" the right driver? Wow. I do just write the getConnection() method, and then write the URL inside. Something must be under the hood?!

Indeed there is. All of the JDBC Drivers in the classpath are registered with the DriverManager. For details, look at Drivermanager's JavaDoc: https://docs.oracle.com/javase/8/docs/api/java/sql/DriverManager.html

The Driver interface defines a method named "acceptsURL". So what the DriverManager can do is enumerate its collection of registered drivers until it finds one where the acceptsURL method returns true for the JDBC URL you've passed to getConnection. Then the DriverManager can invoke that Driver's connect method which will either create the Connection instance that will be returned to the app OR it will return null, indicating that this Driver cannot handle that request and the DriverManager will need to keep searching.

JDBC URLs have a common format. The first part (protocol identifier) is always "jdbc", to distinguish from things like "http", "frp", "file" or other non-database URLs. Following that is usually a vendor identifier followed by //host/port/database name. although at that point the Driver decides what the proper format should be.

So, for example, the URL "jdbc:mysql://server:3306/dname" gets handled by the MySQL JDBC driver, "jdbc:postgresql://server:5432/dname", URL "jdbc:oracle:thin:scott@tiger://server/dname" is dealt with by the Oracle Thin database driver. The URL "jdbc:sqlite::memory:" will create a JDBC Connection to an SQLite in-memory database, and the URL "jdbc:sqlite:C:/work/product.db" will connect to a database on the local filesystem (instead of using a network connection).

For proper operation, you shouldn't have more than one mysql driver JAR in your classpath, but the URL ensures that it will be a MySQL (or MariaDB) driver that will construct Connections for talking to MySQL/MariaDB databases.

[Paul Crane]

So I don’t have to install something more because it’s already been in the jars. So drivers are classes implementing the JDBC interfaces and contained inside the API. During the getConnection(), the DriverManager looks on the URL and takes the particular downloaded driver?

[Stephan van Hulst]

Paul Crane wrote:So I don’t have to install something more because it’s already been in the jars.

Well, if your application is not running in a container, you will have to deliver the drivers with your application and they have to be on the class path or module path when your application is running.

So drivers are classes implementing the JDBC interfaces

Correct.

and contained inside the API

Incorrect. JDBC drivers are separate modules that are packaged as JAR files and are NOT included in the standard API.

During the getConnection(), the DriverManager looks on the URL and takes the particular downloaded driver?

I don't think that DriverManager inspects the URLs itself. It just iterates over all available drivers and asks them whether they can handle the URL, until it finds one that can. It looks roughly like this:

public final class ExampleDriverManager {

  private static ServiceLoader<ExampleDriver> driverLoader;

  private ExampleDriverManager() {}

  public static ExampleConnection getConnection(String url, Properties info) throws SQLException {
    if (driverLoader == null) {
      driverLoader = ServiceLoader.load(ExampleDriver.class);
    }

    for (var driver: driverLoader) {
      if (driver.acceptsURL(url)) {
        return driver.connect(url, info);
      }
    }

    return null;
  }
}

For this example, I renamed DriverManager, Driver and Connection to distinguish them from the actual types in JDBC, because I feel a more complete example application coming up in the future.

[Paul Crane]

That's no longer true, as there's a special entry in the MANIFEST of the JDBC driver JAR that tells the JVM to make the connection, so Class.forName is not longer needed (it's relatively harmless to keep it, just shows someone's out of date)

But can I find the entry in the MANIFEST to finally understand it? I see lots of examples where people still use Class.forName()

[edit by CR] Replace missing ] character in quote tags.

[Campbell Ritchie]

Paul Crane wrote:. . . I see lots of examples where people still use Class.forName()

There are lots of examples of out of date code out there. I still see the use of

BufferedReader reader = ...
...
reader.close();

...which wasn't even properly correct code before try with resources back in 2009.

You were missing a ] in your quote tags, but I corrected that.

[Tim Holloway]

Campbell Ritchie wrote:

Paul Crane wrote:. . . I see lots of examples where people still use Class.forName()

There are lots of examples of out of date code out there. I still see the use of.

There are lots of examples of ignorant people repeating ignorant things. Not all of them software-related, alas.

And yes, the file in question is the /META-INF/MANIFEST.MF file in the JDBC JAR. There is documentation available on the details of that entry, should you ever want to write your own JDBC driver.

[Paul Crane]

The Driver interface defines a method named "acceptsURL". So what the DriverManager can do is enumerate its collection of registered drivers until it finds one where the acceptsURL method returns true for the JDBC URL you've passed to getConnection. Then the DriverManager can invoke that Driver's connect method which will either create the Connection instance that will be returned to the app OR it will return null, indicating that this Driver cannot handle that request and the DriverManager will need to keep searching.

Well, I've gone through a large hierarchy of classes, and did not understand it at all. There're lots of classes linked with each other and they call the absolutely different methods. I don't know whether I should understand it fully or not, but just want to. This makes me struggled.    

[Tim Holloway]

Consider both the Driver and the DriverManager to be Black Boxes. You don't have to know what goes on inside them, you just have to know how to talk to them. In the case of the DriverManager, that's basically just to get a JDBC Connection. The driver itself you don't even talk to directly, just need it to be in the classpath. The Connection is what does the talking to the Driver.

Database vendors provide instructions and examples for their JDBC connection URLs and that's all you need at the application level.

[Paul Clapham]

Paul Crane wrote:I don't know whether I should understand it fully or not, but just want to.

There seems to be this idea going around which claims that understanding how something works internally makes you a better person somehow.

I have a lock on my front door. I put a key in it and turn it, then I can open the door. I don't know how the lock works internally. In my kitchen is a refrigerator. I know it has a motor which pushes hot air out and leaves cold air in, but I don't know how it does that. My world is full of things which have been provided to me by people who know what they are doing, and I don't feel the need to second-guess their work. Life is too short for that.

[Tim Holloway]

Paul Clapham wrote:
There seems to be this idea going around which claims that understanding how something works internally makes you a better person somehow.

Well it does. For example, know that in order for a refrigerator motor to push out hot air, it has to pump refrigerant past an expansion nozzle that employs the gas laws of physics to suck heat out of the cooling coils so that it can blow it out. But that doesn't help me unjam the icemaker, thankyouverymuchSamsung. I know how an internal combustion engine engine can compact vaporized gasoline to then deliver a precisely-timed spark, but that definitely won't help me repair a Tesla.

It means that I have something of a head start, should Civilization collapse and I end up needing to do these things myself, but in the mean time, I do not find it profitable to obtain the tools or the detailed skills.

So it is with the internals of complex systems like JDBC. Other people got paid to think about it, to design it, to realize it and to document it so that I wouldn't have to. And I really appreciate that because back in my mainframe days, about the only software I didn't have to develop from scratch was the OS itself. And compared to modern PC OS's, OS/MVS wasn't even that complex and DOS/VS-360 was hardly better than CP/M. Indeed, at least in CP/M you didn't have to keep a notebook listing what disk files were on what tracks of each disk drive so that you could allocate new files.

Rejoice, then! You can explore such things at leisure rather than as an essential job task. Better yet, you can easily obtain the source code for the entire OpenJDK system, and that certainly beats hand-dissassembling hex codes from a memory core dump. Which is how I learned the internals of a lot of stuff when I was much younger!

[Stephan van Hulst]

Modern-ish libraries will contain a folder META-INF/services that contains a file named java.sql.Driver, that contains the names of the classes that implement the Driver class.

Modern libraries contain a module descriptor module-info.java that contains a provides declaration.

[Tim Holloway]

Stephan van Hulst wrote:Modern-ish libraries will contain a folder META-INF/services that contains a file named java.sql.Driver, that contains the names of the classes that implement the Driver class.

Modern libraries contain a module descriptor module-info.java that contains a provides declaration.

Stephan read the documentation!

The module-info is new for Java after version 9.0 (??), but the java.sql.Driver is what tells the DriverManager what drivers are available since Class.forName() became obsolete.

[Paul Crane]

Oh thank you all guys for it. I face this situation every time when it comes to learn new things. Just «How it works? What is it? How do these classes create tcp/ip connections while I don’t know it’s happening, etc». I know it’s kinda weird, but it’s something I can’t control every time. There’s something I don’t know. And I begin to learn everything again and again realising it. Well, I hope I’ll cope with this and become a great programmer

[Tim Holloway]

I think the hard thing about Java is that when you encounter a new system, you're never sure of how deep to go. Something that I think that Java Modules may make easier, since it obscures the purely internal stuff from the stuff you actually need to interact with.

Just remember. There Is No Magic. JDBC is just a standard database protocol. I'm sure if you needed to you could set up a system that would do the same thing as the ConnectionManager does as far as finding (and invoking) drivers. It's relatively simple logic.

As far as Drivers go, they're just ordinary Java code (at least for Type 4 Drivers!) and ordinary code has no problem in generall with opening a network or file connection and using it. The only tricky part there is in what the database server wants to send and receive via that connection. And they[/define that - all you need is the specs. The real magic comes from using Java Interfaces for the callind mechanisms so that you can use plug-replaceable components.