Oracle和Mysql数据库转换

前言

我们的项目一直使用的是SSH框架+mysql+tomcat服务器,但是最近一个新项目对数据库的使用和web服务器提出了硬性要求,按照他们的要求,需要使用Oracle数据库,并采用Weblogic进行服务部署。本文针对本次数据库转换(Mysql转换Oracle)过程中遇到的一些问题做记录,对于部署服务器的更换(tomcat更换为Weblogic)另一文章将继续分享。

问题分析

首先,我们系统的架构情况如下:

开发环境:

  • MAVEN:3.5
  • JDK:1.8
  • Spring: 4.3.9.RELEASE
  • hibernate: 4.3.11.Final
  • Shiro:1.4.0

开发工具:

  • MySql: 5.7.x
  • ORACLE: 11.2.0.1.0
  • Tomcat: 9.0
  • EZDML:2.06(表结构设计器)

分析了一下部分代码,发现Mysql转换至Oracle,整体框架无需大的改动,由于使用了hibernate框架做持久层,业务操作基本都是HQL,所以无需做单独处理,需要变动的主要涉及到数据库结构的转换和一些公共自定义sql的兼容性处理,明确了解决问题的要点,现在开始着手。

数据库转换

数据库结构

首先,使用EZDML表结构设计器进行数据库的整体转换

1、打开EZDML,点击模型,选择导入数据库:

2、点击确定,选择需要转换的表名,确定之后就可以看到对应的表结构模型;
3、在左边单击表明,就可以看到详细的表结构设计,选择生成,就可以看到不同类型数据库对应的建表DDL。

然后,对表结构进行微调

Mysql和Oracle之间的数据类型转换

MySQL Data TypeOracle Data Type
BIGINTNUMBER(19, 0)
BITRAW
BLOBBLOB, RAW
CHARCHAR
DATEDATE
DATETIMEDATE
DECIMALFLOAT (24)
DOUBLEFLOAT (24)
DOUBLE PRECISIONFLOAT (24)
ENUMVARCHAR2
FLOATFLOAT
INTNUMBER(10, 0)
INTEGERNUMBER(10, 0)
LONGBLOBBLOB, RAW
LONGTEXTCLOB, RAW
MEDIUMBLOBBLOB, RAW
MEDIUMINTNUMBER(7, 0)
MEDIUMTEXTCLOB, RAW
NUMERICNUMBER
REALFLOAT (24)
SETVARCHAR2
SMALLINTNUMBER(5, 0)
TEXTVARCHAR2, CLOB
TIMEDATE
TIMESTAMPDATE
TINYBLOBRAW
TINYINTNUMBER(3, 0)
TINYTEXTVARCHAR2
VARCHARVARCHAR2, CLOB
YEARNUMBER

自增序列处理

MySQL Data TypeOracle Data Type
有自动增长的数据类型,插入记录时不用操作此字段,会自动获得数据值ORACLE没有自动增长的数据类型,需要建立一个自动增长的序列号,插入记录时要把序列号的下一个值赋于此字段

Mysql自动增长的数据类型

1
2
3
4
5
create table table_name
(
ID int auto_increment -- 自增长
primary key
);

Oracle自动增长的数据类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create sequence table_name_pk
minvalue 1
maxvalue 9999999999999999999999999999
start with 447
increment by 1
cache 20;

create table table_name
(
ID int generated as identity
constraint table_name_pk
primary key
)
/

长字符串处理

在ORACLE中,进行INSERT和UPDATE时,最大可操作的字符串长度必须小于等于4000个单字节。如果超出这个长度,要插入更长的字符串数据,可以考虑使用CLOB类型。在本项目转换过程中,使用到了指纹数据的16进制字符串的存取,长度超过4000,在Mysql中使用BLOB类型存储没问题,但是ORACLE下不可以。BLOB全称为二进制大型对象(Binary Large Object),它用于存储数据库中的大型二进制对象,原来在ORACLE下,对二进制对象有严格要求,所以采用CLOB类型存储。

日期字段处理

在MYSQL中,日期字段可以分DATE(包含年月日)和DATETIME(包含年月日时分秒)两种类型,而ORACLE日期字段只有DATE(包含年月日时分秒)一种类型。所以在实际业务使用过程中需要进行格式转换。

比较项MySQL Data TypeOracle Data Type
数据类型DATE(包含年月日)、
DATETIME(包含年月日时分秒)
DATE(包含年月日时分秒)
日期格式%Y:代表4位的年份
%y:代表2为的年份
%m:代表月, 格式为(01……12)
%c:代表月, 格式为(1……12)
%d:代表月份中的天数,格式为(00……31)
%e:代表月份中的天数, 格式为(0……31)
%H:代表小时,格式为(00……23)
%k:代表 小时,格式为(0……23)
%h: 代表小时,格式为(01……12)
%I: 代表小时,格式为(01……12)
%l :代表小时,格式为(1……12)
%i: 代表分钟, 格式为(00……59)
%r:代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T:代表 时间,格式为24 小时(hh:mm:ss)
%S:代表 秒,格式为(00……59)
%s:代表 秒,格式为(00……59)
YYYY、YYY、YY 分别代表4位、3位、2位的数字年
MM 数字月
DD 数字日
AM 表示上午或者下午
HH24、HH12 代表24小时制或12小时制
MI 分钟
SS 秒钟
当前日期sysdate()、current_date、current_timesysdate、current_date、current_timestamp
日期和字符互转DATE_FORMAT
例如:DATE_FORMAT(sysdate(),‘%Y-%m-%d %H:%i:%s’)
STR_TO_DATE
例如:STR_TO_DATE(‘2019-12-30 19:25:34’,‘%Y-%m-%d %H:%i:%s’)
TO_CHAR
例如:TO_CHAR(sysdate,‘YYYY-MM-DD HH24:MI:SS’)
TO_DATE
例如:TO_DATE(‘2019-12-30 19:25:34’,‘YYYY-MM-DD HH24:MI:SS’)
日期/时间增减增减一小时:
date_sub(createDate, interval -1 hour)
date_sub(createDate, interval 1 hour)
增减一天:
date_sub(createDate, interval -1 day)
date_sub(createDate, interval 1 day)
增减一月:
date_sub(createDate, interval -1 month)
date_sub(createDate, interval 1 month)
增减一季度:
date_sub(createDate, interval -3 month)
date_sub(createDate, interval 3 month)
增减一年:
date_sub(createDate, interval -1 year)
date_sub(createDate, interval 1 year)
增减一小时:
createDate+1/24
createDate-1/24
增减一天:
createDate+1
createDate-1
增减一月:
add_months(createDate, 1)
add_months(createDate, -1)
增减一季度:
add_months(createDate, 3)
add_months(createDate, -3)
增减一年:
add_months(createDate, 12)
add_months(createDate, -12)
日期/时间比较①直接比较
②转成unix时间戳比较
③转换为日期类型比较
①直接比较
②转换为日期类型比较

日期/时间比较

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Mysql
-- 直接比较(此种方式不走索引,一定程度上会降低性能)
select sysdate() from dual where '2019-12-30 19:39:05' > '2019-12-30 17:39:05';
-- 用unix_timestamp函数,将字符型的时间,转成unix时间戳
select sysdate() from dual where unix_timestamp('2019-12-30 19:39:05') > unix_timestamp('2019-12-30 17:39:05');
-- 将字符串转换为相同格式相同进制的日期类型
select sysdate() from dual where str_to_date('2019-12-30 19:39:05','%Y-%m-%d %H:%i:%s') > str_to_date('2019-12-30 17:39:05','%Y-%m-%d %H:%i:%s');

-- Oracle
-- 直接比较
select sysdate from dual where '2019-12-30 19:39:05' > '2019-12-30 17:39:05';
-- 将字符串转换为相同格式相同进制的日期类型
select sysdate from dual where to_date('2019-12-30 19:39:05','yyyy-mm-dd hh24:mi:ss') > to_date('2019-12-30 17:39:05','yyyy-mm-dd hh24:mi:ss');

返回Map列名大小写问题

转换过程发现,使用Hibernate返回实体类型的数据我们可以不用关注列名大小写问题,但是返回Map类型的数据集合时,对Mysql和Oracle返回的Map的key大小写是不一致的,也就是返回列名大小写不统一,对于两数据库不同的系统环境列名大小写情况如下:

系统MySQL Data TypeOracle Data Type
Windows默认都不区分大小写,
可通过修改配置来区分大小写:
lower_case_table_names = 0(0:区分大小写,1:不区分大小写)
1、在Oracle中,如果字段名称被双引号(“”)包裹,Oracle会区分大小写;
2、如果字段名称没有被双引号(“”)包裹,则全部转换成大写来执行。
3、如果表结构设计时,字段名称使用了数据库的保留字,SQL中的字段名称必须用双引号(“”)包裹,以避免SQL语句执行出错。
Linux1、数据库名与表名是严格区分大小写的;
2、表的别名是严格区分大小写的;
3、列名与列的别名在所有的情况下均是忽略大小写的;
4、变量名也是严格区分大小写的;
同Windows

根据以上,可以看出,我们要解决大小写问题,需要对返回列表的字段名加引号(“”),这种做法工作量过大,于是查看了一下返回Map集合的接口实现,如下:

1
2
3
4
5
public List<Map> findMapResultBySql(String sql) {
SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return query.list();
}

很容易可以看出,处理结果集返回的关键是Transformers.ALIAS_TO_ENTITY_MAP,查看源码

AliasToEntityMapResultTransformer.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
public class AliasToEntityMapResultTransformer extends AliasedTupleSubsetResultTransformer {

public static final AliasToEntityMapResultTransformer INSTANCE = new AliasToEntityMapResultTransformer();

/**
* Disallow instantiation of AliasToEntityMapResultTransformer.
*/
private AliasToEntityMapResultTransformer() {
}

@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
Map result = new HashMap(tuple.length);
for ( int i=0; i<tuple.length; i++ ) {
String alias = aliases[i];
if ( alias!=null ) {
result.put( alias, tuple[i] );
}
}
return result;
}

@Override
public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
return false;
}

/**
* Serialization hook for ensuring singleton uniqueing.
*
* @return The singleton instance : {@link #INSTANCE}
*/
private Object readResolve() {
return INSTANCE;
}
}

可以看出,AliasToEntityMapResultTransformer类的transformTuple方法即是解决问题的关键,于是自定义自己的返回结果集处理工具,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/**
* 返回map统一小写
* @author sunys
*/
public class CustomResultTransformer extends AliasedTupleSubsetResultTransformer {
public static final CustomResultTransformer INSTANCE = new CustomResultTransformer();
private CustomResultTransformer() {

@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
Map result = new HashMap(tuple.length);
for ( int i=0; i<tuple.length; i++ ) {
String alias = aliases[i];
if ( alias!=null ) {
//将Map的key转为小写返回
result.put( alias.toLowerCase(), tuple[i] );
}
}
return result;
}
@Override
public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
return false;
}
private Object readResolve() {
return INSTANCE;
}
}

然后设置SQLQuery的ResultTransformer,如下:

1
2
3
4
5
6
7
8
9

public static final CustomResultTransformer ALIAS_TO_ENTITY_LOWERCASE_MAP =
CustomResultTransformer.INSTANCE;

public List<Map> findMapResultBySql(String sql) {
SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
query.setResultTransformer(ALIAS_TO_ENTITY_LOWERCASE_MAP);
return query.list();
}

至此,Oracle和Mysql数据库返回Map列名统一大小写问题得以解决。