Maven: How to create a correct JDBC driver dependency, if I don't know which database the client will use?

Question

I'm currently learning to use maven, I understood how to create a maven project using dependencies from maven repository - and now I have the following question:

If I have an application which uses a database access, for example via Hibernate, then I need to add a dependency representing the corresponding database driver, for example mysql-connector-java for MySql, ojdbc for Oracle and so on.

But what if I want the program to run on a different machine and I don't know what database engine it uses? What is the common way to solve this? Just import all possible drivers as dependencies? Or is there a more elegant way?

Bundling all possible drivers, aside from being quite a waste, will also cause license issues. — fvu, Dec 05 '17 at 12:15
Set the drivers as a `provided` dependency which means the runtime envrionment decided (provides) the appropriate driver...The problem is that you need to decide the type of the target language (oracle, mysql, mssql, postgresql etc.) which means you need to have different property files which configure this... — khmarbaise, Dec 05 '17 at 12:17
Building a product that must support multiple databases is a substantial effort, even with an ORM. I know from experience that even 2 systems is a PITA. How many must you support, can't you just include drivers for each of those? — MarioDS, Dec 05 '17 at 12:17
This can be achieved by loosely coupling your Database model from your application. You can create a datasource application which your core application can read and write entities over the network. — Dogukan Zengin, Dec 05 '17 at 12:18
@khmarbaise that's not necessary, unless you explicitly intend to use the implementation classes, at which point the system won't be database agnostic anymore. — Kayaman, Dec 05 '17 at 12:25

score 0 · Answer 1 · answered Dec 05 '17 at 12:20

Using a ORM (Object Relational Mapping) like Hibernate is the best solution since you write Java code that will be interpreted by Hibernate and translated into SQL queries.

At some point, you will have to decide which database are you going to use, then you will have to add the driver.

Another solution can be making configurations for different environments using maven: https://maven.apache.org/guides/mini/guide-building-for-different-environments.html

score 0 · Answer 2 · answered Dec 05 '17 at 12:23

The problem is not particularly maven bound. Whenever you move to a new database, the administrator would have to use the correct JDBC driver according to DB, and yes, it requires different jar files.

The thing is, that you don't want to bundle the database jar file with your code. It may already exist (e.g. in the application server) or you may specify a path to drop it during installation.

Assuming you are creating a webapp. If you bundle a war-file with maven, it will include all dependencies inside the war file, so you must specify that the dependency is there during compilation and testing, but not in any package. The way to do so, is by specifying it as provided

<dependency>
  <groupId>some.db</groupId>
  <artifactId>jdbc-driver<artifactId>
  <scope>provided</scope>
</dependency>

This means that the jar file will exist on the target platform, and hence, should not be packaged in a any bundle.

So the assumption I make is really, is this a web-app? Or is it standalone? Anyway, hope it helps.

That's not necessary, unless you explicitly intend to use the implementation classes, at which point the system won't be database agnostic anymore. — Kayaman, Dec 05 '17 at 12:27

score 0 · Answer 3 · answered Dec 05 '17 at 12:58

You don't need a dependency at all. What you need is a driver to be available at runtime. It's true that one way to do this is with a dependency, but if you don't know the database you can't really bundle everything in there. You could stick some most common drivers, but then as said there would be licensing issues.

If you're talking about a web application, you could just tell the user to get the appropriate driver and configure a JNDI datasource that the software uses. This is/was a standard from back in the days, but it assumes that the application is a webapp and the end user knows how to configure things (although if he doesn't, he probably shouldn't be setting up the system in the first place).

For a standalone program using a local database, you have the easy choice of using an in memory database like H2 and not allowing any other databases. Naturally this case doesn't work for everything, but I'm including it as an example. In any case it would boil down to the same as with a webapp. Have the end user get the correct driver. If they're running a database server and your app, they should be able to find the right driver too. Then you just need to make sure it's included in the runtime classpath, which might be a bit harder.

The way this is done by SquirrelSQL for example, is by explicitly selecting the drivers as shown in the below picture. This of course again means the user needs to understand what he's doing.

pirho · Answer 4 · 2017-12-05T20:52:41.847

I assume that you want everything to happen automagically and you're not too eager to instruct each user/machine admin how to configure system to have your app working. I am afraid it is not possible in the way you might have hoped.

The standalone database solution that Kayaman suggested might be the best solution in your case but hard to say without knowing further.

However here are some aspects regarding using maven and possible difficulties with some notes.

If I have an application which uses a database access, for example via Hibernate, then I need to add a dependency representing the corresponding database driver, for example mysql-connector-java for MySql, ojdbc for Oracle and so on.

Yes. And you also would need to tell hibernate about this Driver and perhaps other stuff related. It is not just adding dependency but also filtering some prop file or persistence.xml. That might be a job for maven and some of its plugins. But still it would require knowledge about all the possible db alternatives and maven profiles for each of those to handle them.

But what if I want the program to run on a different machine and I don't know what databse it uses? What is the common way to solve this? What options do I have? Just import all possible drivers as dependencies? Is there a more elegant way?

All programs have dependencies. Was it related to DB or not. In a sense as other answers suggest this is not maven specific (but quite related still! ) thing. You need to be aware of the requirements of any environment if you really want to develop on the level of JDBC drivers.

This specific question of yours is something that - I believe - is the motivation to develop things like:

ODBC
JNDI

NOTE 1 even similar naming ODBC & JDBC are totally in different level (I mean how JDBC drivers are found which actually might be the main problem...)

NOTE 2 JNDI is not restricted to DataSources

However maven can be a great help depending on what you need and finally decide to do. But not in so big role if you can use ODBC / JNDI.

Maven: How to create a correct JDBC driver dependency, if I don't know which database the client will use?

4 Answers4