Programming for various database

Question

I'm wondering for those enterprise programs, how do they link to various type of database just by stating the connection string?

Issues like different syntax, variable type will definitely be there.
Apart from stored procedures for each type of database, how else do they handle in terms of their programming?

1 way that came to my mind is just if else checking of database in order to populate different query.

Asking as I'm curious while using a engine which is built in C++ and jsp, but could support SQL Server, Access, MySQL, Oracle

Answer 1

ORMs tackle this problem by introducing a level of abstraction between the database and the domain model. For example with Hibernate you change the connection string and the dialect and HQL queries and Criteria APIs are automatically translated into the proper SQL for the target database.

Of course this assumes you never write a single line of SQL in your application or anything which is specific to the database.