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
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.