I am searching to create a java program which has the next needs:

  1. The capability to exchange the database being used without changes towards the code.
  2. I must perform the maintanence from Java - creating the tables, sights etc.
  3. Should have the ability to support complicated queries

JDBC appears just like a good beginning point, however it appears such as the queries might be different for various DBs (for instance slight syntax variations create table claims in MySQL, and MSSQL).

The concept (clearly) would be to avoid writing exactly the same code particularly for every DB, and depend on the driver to complete the dirty work with me.

Is JDBC adequate? Exist other available choices?

JPA appears to become the perfect selection for you. It really works almost-out-of-the-box. Abstracts the direct DB handling away and may be used stand alone (with no application server).

JDBC continues to be a legitimate option however the development with it's not as DB-agnostic just like JPA (which may be seen as abstraction layer over JDBC).

Here is a fairly example.

Most widely used JPA implementations are Hibernate and Eclipselink with Eclipselink (former TopLink) being the reference implementation.

I'd take a look at Hibernate or any other ORM solution.

You should use property files to swap the db without changes towards the code within the easiest situation. You might want to take a look at profiles if you work with Maven.

You can start searching for Hibernate. This is just what you are looking for.

the thing you need is really a design pattern take a look only at that. It may help

Core J2EE Patterns - Data Access Object

If you want to possess DB independence, don't put any claims inside your code. Rather, store them within an exterior file.

You might have mysql.ddl to begin. Then in the future, should you change to oracle, you are make oracle.ddl with similar format as mysql.ddl however with different queries.

In case your database access is its very own project, this really is extensible because now other programs that speak with this database will also have the ability to leverage the present ddl file you authored.

Two libraries that will help, when Hibernate is definitely an overkill:

  1. Table maintenance - CREATE,Remove (DDL) - DDLUtils
  2. Creating the SQL queries to retrieve the data - JOOQ