CODEWITHSUNDEEP
sql-serverdatabaseinformixjooqddlutils
Eslam Hamdy
Eslam Hamdy

Reputation: 7386

How to make my webApp switch between different DBMS smoothly(DB Independency)?

I have a Java web application that has Informix as its back end database. Many tables in my schema contain columns of type CLOB, BLOB & SERIAL8. Now I made a decision to use SQL Server instead of Informix but I found a problem in converting the above data types into the corresponding SQL Server ones, and I will face the same problem when for example I turned to use DB2 or Oracle also I found a problem in matching the relationships between tables, as each DB vendor has its own representation of relationships.

Is there a way to design my application and database schema so that it can interact with any database server, regardless of its type and without changing columns data types? I heard about DDLUtils & jOOQ but I don't know if they are suitable for me or not

Upvotes: 3

Views: 367

Answers (5)

MahdeTo
MahdeTo

Reputation: 11184

Ok, there is multiple levels of abstraction you can do here:

First, you need to concentrate all your data access code into Isolated implementations and only access those through interfaces that provides you with the data you need in a format that is only your application's and is not affected by the design of your underlying data storage.

for example, if you want to fetch parts from a certain customer order make sure the method you are using to find is one that deals with the "business id" of your object, because the actual ID could be a long in a relational database but a UUID in a NoSQL one.

By applying this you are not bound to a data storage paradigm, you can switch freely between flatfiles, in memory storage, NoSQL and your regular relational DB implementations.

Second, try to use an ORM like Hibernate. This allows you to write in a universal query language and in most cases only limit database specific changes to configuration changes.

Third, if you need to write SQL queries directly make sure that you write ANSI queries and not database specific ones and even when you do make sure these queries are not part of your code but part of a resource (i.e. properties file) so that you can tweak and change it as you wish without needing to recompile your binary.

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220762

Since you mentioned jOOQ, I can give you some insight on what is possible with that tool, and what is not - with respect to your needs. With jOOQ, you can:

  • Generate source code from a model database schema. These generated schema objects are SQL dialect independent. This may mean that you may lose some type information, if you're using vendor-specific types in your model database schema.
  • Write SQL that will run on various databases. jOOQ handles SQL-dialect-specific things from you. Many jOOQ users use jOOQ to run applications against combinations like [Oracle, Postgres], [Oracle, SQL Server, HSQLDB, Sybase], etc.

With jOOQ you cannot:

  • Handle the DDL needed in order to stay compatible across various databases. jOOQ doesn't support DDL abstractions. DdlUtils could be a good solution for that, though.
  • Use Informix. Officially, Informix is not supported (yet)

As others have pointed out, depending on how little "SQL" you really want to do, Hibernate may be a better choice. If you're ready to reduce your querying requirements to HQL or JPQL, then Hibernate also handles:

  • Informix SQL dialect support
  • DDL generation if you use a domain-model-first approach (unlike jOOQ, which supports only the database-model-first approach)

Note, you can also use a combination of Hibernate and jOOQ. See also this related question:

ORM frameworks used for insert only / query only apps

Upvotes: 1

James Allman
James Allman

Reputation: 41158

Since it's a Java application you could migrate to an ORM layer such as Hibernate that would abstract the database details.

Hibernate supports over 20 databases with existing dialects and it's not that difficult to create your own. I've had to do that for compatibility with a legacy Informix SE 7.32 database.

Upvotes: 1

Joe R.
Joe R.

Reputation: 2042

The only way you could achieve SQL-based data independence is by using ANSI SQL, but that might not provide enough functionality for your applications requirements.

Even if you were to use JDBC and keep your SQL statements external, I don't think its possible to achieve SQL-based database independence. I thought ANSI SQL was a move towards that goal, but DB vendors added their own: supersets to ANSI SQL, datatypes, variable naming syntax, etc.

Upvotes: 0

Ernest Friedman-Hill
Ernest Friedman-Hill

Reputation: 81684

You use a database-independent API (i.e., JDBC) to write your code, but keep your SQL in external text files; a key/value format is convenient. At runtime, you load in the queries that you need for a given database.

Upvotes: 0

Related Questions