How the Hibernate-framework-based development platform can be truly compatible with Oracle and SQL Server databases

It is said that the use of Hibernate framework can easily enable your research and development platform to support a variety of different types of databases, but the practice shows that the "easy" here is relative.

It's not a simple thing to have a research platform that supports multiple databases, but it can be said that it's not possible to do so with the hibernate framework.

The following is a record of the process and some sentiment I have done this thing.

When I received the task, I took a rough look at the idea:

To complete the migration, there are 2 large pieces of work to be done on the whole: database-level migrations and the transformation of the platform's underlying code

First, the migration process at the database level:

1. Import data from Oracle to SQL Server database via SQL Server Studio2008 tool

This feature is supported from SSMS2008, with steps (right-click Database-Select Import-Point next-select Oracle Provider for OLE DB data Source-click Properties-Fill in data source, format IP: Port/Instance name), Follow the steps to follow the wizard step-by-step process. Note that in the steps to select the source table and Source view:

(1), to remove the default prefix in the "target" column so that the imported table will be associated to the DBO by default, otherwise you will have to bring the schema prefix every time you query the table, resulting in the SQL in your previous application can not be executed, because the SQL you wrote earlier will definitely not take this prefix.

(2), tick the source you want to import, and then double-click each row of records, in the popup dialog to check whether all types are properly bound, I encountered in the examination of Oracle is the VARCHAR2 type, in the dialog box displayed in the table structure becomes 130, You can only manually change all 130 to a varchar type (SQL Server does not have a varchar2 type). And the original is CLOB type, now become varchar, to manually change to the text type (because CLOB type of field is relatively small, so you can do in Oracle "SELECT * from User_tab_columns C where c.data_ Type= ' CLOB '; " To see which tables use the CLOB type of fields).

2, add To_date, To_char, To_number, concat and other commonly used functions

Description: When I was writing the to_date function, I provided only one format, "Yyyy-mm-dd HH:mi:ss", because there is no similar function in SQL Server to the To_date function, only with the CONVERT function. However, the CONVERT function does not support the passing of the formatted string, only the integer number corresponding to the format character, and 120 corresponds to the previously mentioned "Yyyy-mm-dd HH:mi:ss" format; this time it was migrated to Sqlserver2005, This version is not embedded in the CONCAT function, according to the official document, it is starting from sqlServer2012 concat function, so here I would like to write a concat function.

------------------------------------------------------------------concat function Use [Skyplatform]GO/** * * * object:userdefinedfunction [dbo].    [Concat] Script date:03/10/2015 17:11:31 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE FUNCTION [dbo].[concat](@param1 varchar( -),@param2 varchar( -))returns varchar( +) asbeginDECLARE @returntext varchar( +)    if(@param1  is NULL)    SELECT @returntext= @param2; Else if(@param2  is NULL)      SELECT @returntext= @param1; Else     SELECT @returntext= @param1 + @param2; return @returntext;End--------------------------------------------------------------------To_char function Use [Skyplatform]GO/** * * * object:userdefinedfunction [dbo].    [To_char] Script date:03/10/2015 17:12:09 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE FUNCTION [dbo].[To_char](@param1 datetime,@param2 varchar( -))returns varchar( -) asbeginreturn Convert(varchar( -),@param1, -)    End--------------------------------------------------------------------to_date function Use [Skyplatform]GO/** * * * object:userdefinedfunction [dbo].    [To_date] Script date:03/10/2015 17:12:58 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE FUNCTION [dbo].[to_date](@param1 varchar( -),@param2 varchar( -))returns datetime asbeginreturn Convert(datetime,@param1, -)--means that yyyy-mm-dd hh:mi:ss (24h)    End--------------------------------------------------------------------to_number function Use [Skyplatform]GO/** * * * object:userdefinedfunction [dbo].    [To_number] Script date:03/10/2015 17:13:09 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE FUNCTION [dbo].[To_number](@param1 varchar)returnsNumeric asbeginreturn Convert(Numeric,@param1)    End

Second, the reform of the platform's underlying code

1. Introduction of SQL Server JAR package: Sqljdbc4-4.0.jar

< groupId >com.microsoft.sqlserver</groupId><Artifactid  >sqljdbc4</artifactid><version >4.0</version>

2. Modify the configuration of database connection information in Db.properties

Jdbc.dialect=org.hibernate.dialect.sqlserverdialectjdbc.driver= Com.microsoft.sqlserver.jdbc.sqlserverdriverjdbc.url=jdbc:sqlserver://xx.xx.xx.xx:1433;databasename= Xxxjdbc.default_schema=dbojdbc.username=xxxjdbc.password=xxx

3. Modify some non-SQL standard syntax used in the platform

When using delete Insert to update these DML statements, remember not to use aliases, because in Oracle and SQL Server, these DML statements have different syntax for using aliases.

4, the transformation of the entity class primary key strategy

It is best to use a string-type primary key, but since the sequence master key policy used in the previous code is bound to be a large amount of work for string types, it is decided to use the table policy to be compatible with various databases.

5, the DAO layer of SQL processing

Because a custom scalar-valued function is called in SQL Server, You must precede the function name with the dbo prefix, but this is bound to lead to the incompatibility of other relational databases, so only from the DAO implementation layer, the SQL is processed uniformly, the rule is: if the current database is SQL Server, and there are concat, to_ Functions such as date, To_char, To_number, and so on, are prefixed with the dbo for these function names.

The above, the basic can let the platform on the SQL Server database run, but also can change the configuration file to the Oracle database.

The above approach may not be the best way, if there is a better solution, I hope you can give advice.

How the Hibernate-framework-based development platform can be truly compatible with Oracle and SQL Server databases