3

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

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Eslam Hamdy
  • 7,126
  • 27
  • 105
  • 165
  • What is the nature of the problems you faced, regarding "table relationships"? From my experience, most databases pretty much follow the standard in that field... – Lukas Eder Jul 08 '12 at 14:38

5 Answers5

2

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
MahdeTo
  • 11,034
  • 2
  • 27
  • 28
1

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.

James Allman
  • 40,573
  • 11
  • 57
  • 70
1

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

Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
0

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.

Ernest Friedman-Hill
  • 80,601
  • 10
  • 150
  • 186
0

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.

Joe R.
  • 2,032
  • 4
  • 36
  • 72
  • You can use ORM tools like [Hibernate](http://www.hibernate.org/) or [MyBatis](http://www.mybatis.org/core/index.html) and then you will have an abstraction layer that do all the _SQL-based data independence_ stuff. – maba Jul 05 '12 at 08:27
  • 1
    Unfortunately, ANSI SQL is not implemented strictly by most databases. While you *should* be right in theory, in *reality*, ANSI SQL is yet another dialect among many others... – Lukas Eder Jul 08 '12 at 14:25
  • about the only thing I could think of to achieve as close to possible SQL independence is to define all columns as type CHARACTER and only use ANSI SQL-92. – Joe R. Jul 08 '12 at 18:39
  • Even then, the interpretation of padding and equality with `CHAR` types differs from vendor to vendor, and also from driver to driver. One example: http://stackoverflow.com/questions/5332845/oracle-jdbc-and-oracle-char-data-type – Lukas Eder Jul 10 '12 at 07:50
  • @LukasEder - I chose CHAR because one could cast it to specific datatypes and also do string manipulation? Which datatype is portable across all SQL DB's? – Joe R. Jul 10 '12 at 11:09
  • 1
    Hmm, `DECIMAL` isn't too bad either, unless you're casting or converting... Hmm, I guess `CHAR` is a good defensive bet, anyway – Lukas Eder Jul 10 '12 at 12:09