Hibernate for Oracle and SQLServer
I am representing the DAO layer in our application currently running on SQL Server because I need to port it to Oracle.
I would like to use Hibernate and write a factory (or use dependency injection) to select the correct DAOs according to the deployment configuration. What are the best practices in this case? Should I have two packages with different hibernate.cfg.xml and * .hbm.xml files and select them in my factory? Is there a chance my DAOs will work on both DBMSs without the hassle?
source to share
Assuming the table names and columns are the same between them, you should be able to use the same files hbm.xml
. However, you will certainly need to specify a different Hibernate ( hibernate.cfg.xml
) configuration value , as you will need to change the Hibernate dialogs from SQLServer to Oracle.
If there are minor differences between the two names, then I will create two sets of mapping files, one per database server, and package them into separate JAR files (for example, yourproject-sqlserver-mappings.jar
and yourproject-oracle-mappings.jar
) and deploy the application with one JAR or another depending on the environment.
source to share
I did this for a client a while ago - when deploying depending on the property set in the file production.properties
, I changed hibernate.dialect
in the file cfg
using Ant (you can use any xml transformer). However, this will only work if the Hibernate code is seamless across both DBs, i.e. no db specific function calls etc. HQL / JPAQL has standard function calls that help in this regard like UPPER(s)
, LENGTH(s)
etc.
If the db implementations are bound to be different, you will need to do something like what @matt suggested.
source to share
I have been working on an application that supports many databases (Oracle, Informix, SQL Server, MySQL). We have one config file and one set of mappings. We use jndi to connect to the database, so we don't need to deal with different connection urls in the application. When we initialize the SessionFactory, we have a method that infers the database type from the underlying connection. For example, manually get the connection via JNDI and then use connection.getMetaData (). GetDatabaseProductName () to find out what the database is. You can also use a container environment variable to set it explicitly. Then set the dialect using configuration.setProperty (Environment.DIALECT, deducedDialect) and initialize the SessionFactory as usual.
Some of the things you have to deal with:
- Generation of primary keys. We are using a customized version of the TableGenerator strategy, so we have one key table with columns for the table name and next key. This way each database can use the same strategy rather than a sequence in Oracle, native to SQL Server, etc.
- Database-specific functions. We avoid them whenever possible. Hibernate dialects handle the most common. Sometimes we have to add our own custom dialect classes, for example. date arithmetic is pretty non-standard, so we'll just create a function name and map it to each database way.
- Schema generation - we are using the Hibernate schema generation class - it works with dialects to generate the correct DDL for each type of database and makes the database match the mappings. You must know the keywords for each database, for example. do not try to use the USER table in Oracle (USERS will work) or the TRANSLATION table in MySQL.
source to share
There is a table showing the differences between Oracle and SQLServer: http://psoug.org/reference/sqlserver.html
In my opinion, the biggest pitfalls are: 1) Dates. The functions and mechanics are completely different. You will need to use different code for each DB. 2) Generation of keys. Oracle and SQLServer use different mechanisms, and if you try to avoid the "native" generation altogether by having your own keymap, well, you've just serialized all your inserts completely. Not suitable for the job. 3) Concurrency / blocking is slightly different. The performance sensitive parts of the code are likely to be different for each database. 4) Oracle is case sensitive, SQLServer is not. You have to be careful with this.
There is a lot more :) Writing SQL code that will run on two databases is challenging. Fast at times can seem almost impossible.
source to share