mybatis多数据库兼容改造

个人博客地址：mybatis多数据库兼容改造
微信公众号：Code技术资讯，每日为你带来各种开发/运维干货。

基于mybatis的多数据库兼容功能，实现同时支持Mysql和Oracle数据库。

一、前言

花了老大功夫博客终于搭起来了_，由于之前一直在使用为知笔记，所以现在为知上笔记越来越多，也越来越杂，最近会将其中重要的部分抽取出来，陆续发表到博客上。
恩，本博主第一篇博客，先拿奔驰祭天！咳咳，其实是三个月前，公司拿下了奔驰这个大客户，由于奔驰方所用数据库为Oracle，而我司产品只支持MySQL！因此花（lei）了（si）很（lei）久（huo）对项目持久层做了改造，基于mybatis的多数据库支持，实现了同时对Oracle和MySQL数据库的支持，在此记录实现的过程和踩过的坑。

二、开发设计

准备工作
  • 内网准备oracle环境，客户端建议使用PL developer。
  • 本地tomcat中添加oracle jar包（因为版权问题，不宜放到仓库）
数据库变化
  • boolean类型转换为字符串，oracle中不支持boolean
  • text，longtext类型转换为clob，varchar转换为varchar2，int/bitint/tinyint转换为number类型
  • 数据库主键自增：（可通过新增用户操作不报错来验证） 最终讨论结果选择 方案一
    • 方案一：使用序列+触发器实现。
    • 方案二：使用mybatis selectKey标签实现
  • 创建时间和更新时间设置方式变化，sql中使用sysdate或代码中操作。
  • oracle保留字检查，如resource，user，为Oracle中关键字，若表名或表字段中出现，需做相应修改。
持久层
  • 所有sql语句检查，涉及到函数等特殊的地方均需进行兼容性替换
    例如：mysql中使用函数concat(str1, str2, str3, ....)，oracle中concat一次只能连接两个字符串。这个可以替换为concat(concat(str1, str2), str3)的形式。
  • 分页逻辑改动，添加OracleDialect方言，替换MySQL方言，实现Oracle分页逻辑
数据迁移

使用工具将数据迁移到oracle，我使用的工具是Navicat Premium的数据传输功能。

三、实现过程

实现的过程基本是按照上述开发设计对框架和数据库进行改造，此处主要记录实现过程中的关键点和遇到的问题。

添加Mybatis的多数据库支持

在spring-mybatis.xml中添加mybatis的多数据库支持配置，然后在mapper.xml中可使用标签中的databaseId来设置sql语句生效的数据库，实现如下：
spring-mybatis.xml

<!-- 多数据库处理,设定vendor属性 -->
   <bean id="vendorProperties"
         class="org.springframework.beans.factory.config.PropertiesFactoryBean">
       <property name="properties">
           <props>
               <prop key="Oracle">oracle</prop> <!-- 配置数据库关键字，可在mapper的xml中使用 -->
               <prop key="MySQL">mysql</prop>
               <prop key="SQL Server">sqlserver</prop>
               <prop key="DB2">db2</prop>
           </props>
       </property>
   </bean>
   <bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
       <property name="properties" ref="vendorProperties" />
   </bean>

<!-- Mybatis SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
   <property name="dataSource" ref="dataSource" />
   <property name="configLocation" value="classpath:mybatis-config.xml"></property>
   <property name="mapperLocations" value="classpath:com/cloudcat/dev/dao/impl/*.xml" />
   <!-- 自动配置别名 -->
   <property name="typeAliasesPackage" value="com.cloudcat.dev.entity"/>
   <!-- 将databseIdProvider绑定到sessionFactory中 -->
   <property name="databaseIdProvider" ref="databaseIdProvider"/> 
</bean>

Mapper.xml Example：

<!-- 当数据库为mysql，下列sql起作用 -->
<insert id="save" databaseId="mysql" useGeneratedKeys="true" keyProperty="e.id">
   INSERT INTO XXX ...
</insert>
<!-- 当数据库为oracle，下列sql起作用 -->
<insert id="save" databaseId="oracle" useGeneratedKeys="true" keyProperty="e.id">
   INSERT INTO XXX ...
</insert>

mybatis的多数据库支持实际上是在服务器启动时读取DataSource中的厂商标识，然后根据我们配置的vendorProperties得到databaseId供Mapper使用，具体实现可以参考org.apache.ibatis.mapping.VendorDatabaseIdProvider中的源码。

SQL中数据库函数的兼容

为了解决mybatis SQL中使用的函数在多个数据库下不兼容的问题，建立通用DBUtils类，专门处理数据库兼容的问题，在mybatis的xml文件中，涉及到SQL函数的地方替换为调用DBUtils中的方法，示例如下：
在spring-mybatis.xml中加入DBUtils配置：

<!-- 数据库兼容处理类 -->
<bean class="com.cloudcat.dev.util.DBUtils" >
    <constructor-arg ref="databaseIdProvider" name="databaseIdProvider" />
    <constructor-arg ref="dataSource" name="dataSource" />
</bean>

DBUtils.java：

import com.cloudcat.dev.entity.type.DBType;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.mapping.VendorDatabaseIdProvider;

import javax.sql.DataSource;
import java.text.ParseException;

/**
 * 数据库操作工具类
 * 此工具类目的是为了兼容多个数据库，目前兼容的数据库是mysql和oracle
 *
 * @Author: zhenzhong.wang
 * @Time: 2018/1/2 13:56
 */
public class DBUtils {

  private VendorDatabaseIdProvider databaseIdProvider;
  private DataSource dataSource;

  public static DBType DB_TYPE;

  public DBUtils() {
  }

  public DBUtils(VendorDatabaseIdProvider databaseIdProvider, DataSource dataSource) {
    // 初始化
    this.databaseIdProvider = databaseIdProvider;
    this.dataSource = dataSource;
    String databaseId = databaseIdProvider.getDatabaseId(dataSource);
    DB_TYPE = DBType.valueOf(StringUtils.upperCase(databaseId));
  }

  /**
   * 获得当前时间
   *
   * @return
   */
  public static String getCurrentTime() throws ParseException {
    String result = "";
    if (DB_TYPE.equals(DBType.MYSQL)) {
      result = "now()";
    } else if (DB_TYPE.equals(DBType.ORACLE)) {
      result = "sysdate";
    } else {

    }
    return result;
  }
}

DBUtils为数据库操作工具类，供Mapper.xml调用处理sql函数的兼容。
Mapper.xml中调用示例：

<insert id="save" useGeneratedKeys="true" keyProperty="e.id">
        INSERT INTO app(name, status, create_time)
        VALUES(#{e.name}, #{e.status},${@com.cloudcat.dev.util.DBUtils@getCurrentTime()})
</insert>

当执行save操作时，会调用DBUtils中的getCurrentTime()方法，然后根据当前所使用数据库返回对应的函数。

插入操作的处理

插入操作设计到数据库的主键自增，Mysql的主键自增是通过数据库本身所支持的主键自增实现，而Oracle本身不支持主键自增，所以只有通过序列+触发器的方式实现，Oracle数据库层面的主键自增实现此处不在累赘，主要描述下mybatis下的主键自增处理。

单条记录插入

oracle：

<insert id="save" databaseId="oracle">
    <selectKey keyProperty="e.id" order="BEFORE" resultType="long">
        SELECT seq_queuelog_id.nextval from dual
    </selectKey>
    INSERT INTO queue_logs(id, uuid, authId, ...)
    VALUES(#{e.id}, #{e.uuid}, #{e.authId}, ...);
</insert>

此处使用selectKey，目的是为了得到插入后id回传的值，若不需回传，可类似mysql的操作直接执行插入。
oracle中selectKey中的order属性必须是before，类似mysql这种支持主键自增的如果使用SelectKey则需把order属性设置为after。

mysql：

<insert id="save" databaseId="mysql" useGeneratedKeys="true" keyProperty="e.id">
   INSERT INTO queue_logs(uuid, authId, ...)
   VALUES(#{e.uuid}, #{e.authId}, ...);
</insert>

批量插入

oracle：

<insert id="saveAll" databaseId="oracle">
    BEGIN
    <foreach collection="list" item="e">
        INSERT INTO queue_logs(id, uuid, authId, ...)
        VALUES (seq_queuelogs_id.nextval, #{e.uuid}, #{e.authId}, ...);
    </foreach>
    END ;
</insert>

oracle的批量插入需要使用begin--end关键字，因为mybatis生成的sql语句会全部挤到一行~~这是个大坑！此时在oracle中是会报错，加入了begin--end可以解决这个问题。此处批量插入同时不能够使用selectKey，因为selectKey只能够生成一次id。

mysql：

<insert id="saveAll" databaseId="mysql" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO queue_logs(uuid, authId, ...)
    <foreach collection="list" item="e" separator=",">
        VALUES (#{e.uuid}, #{e.authId}, ...)
    </foreach>
</insert>
NULL空值处理

mybatis-config.xml中加入如下配置：

<settings>   
    <setting name="lazyLoadingEnabled" value="false" />  
    <setting name="aggressiveLazyLoading" value="false" />   
    <setting name="jdbcTypeForNull" value="NULL" />  <!-- 空值类型处理为NULL -->
</settings>

oracle中不允许插入空字符串，加了上述配置可以让mybatis统一的处理空值类型。

分页处理

针对不同的数据库，设计不同的分页方言（类似hibernate中的方言），在进行分页操作时调用对应的方言。
定义Dialect接口：

public interface Dialect {

    /**
     * 数据库本身是否支持分页当前的分页查询方式
     * 如果数据库不支持的话，则不进行数据库分页
     *
     * @return true：支持当前的分页查询方式
     */
    public boolean supportsLimit();

    /**
     * 将sql转换为分页SQL，分别调用分页sql
     *
     * @return 分页查询的sql
     */
    public String getLimitString(String sql, PageInfo page);

}

实现数据库方言的逻辑可根据实际情况自行编写，因为代码版权的原因，本文只给出解决思路和部分代码示例。
OracleDialect.java：

/**
 * PageInfo中包含有分页所需要的如页码，页面大小，总数量等属性。
 * 通过此方法最终生成分页的代码,如：
 * select * from (select A.*,rownum rn from (select ...) A where rownum <= ) where rn >= min
 */
@Override
public String getLimitString(String sql, PageInfo page) {
  StringBuilder sb = new StringBuilder(sql);
  String order = page.getOrderColumn();
  String type = page.getOrderType();
  if (StringUtils.isNotBlank(order)) {
    // filter
    order = sqlfilter(order);
    boolean isTwoColumn = order.indexOf(",") > 0;
    String orderType = "desc".equalsIgnoreCase(type) ? "desc" : "asc";
    // table name
    if (isTwoColumn) {
      String[] orders = order.split(",");
      order = orders[0] + " " + orderType + "," + orders[1] + " " + orderType;
    } else if (order.indexOf(".") < 0 && page.isNeedTableName()) {
      String tableName = getTableName(sql);
      if (tableName != null) {
        order = tableName + "." + order;
      }
    }
    // dict order
    if (!isTwoColumn && page.isDict()) {
      order = "NLSSORT(" + order + ",'NLS_SORT = SCHINESE_PINYIN_M')";
    }
    sb.append(" order by ").append(order);
    // order type
    if (!isTwoColumn) {
      sb.append(" ");
      sb.append(orderType);
    }
  }
  if (!page.isExport()) {
    sb.insert(0, "select * from (select A.*,rownum rn from (");
    sb.append(") A where rownum <= ");
    sb.append(page.getBegin() + page.getPagesize());
    sb.append(")  where rn >= ");
    sb.append(page.getBegin() + 1);
  }
  return sb.toString();
}

public boolean supportsLimit() {
  return true;
}

mybatis-config.xml中配置方言：

<plugins>
    <plugin interceptor="com.cloudcat.web.core.page.interceptor.PaginationInterceptor">
      <property name="dialectClass"
                value="${jdbc.database.dialect}"/>
      <property name="sqlPattern" value=".*(Page|get|getByCondition|search).*"/>
    </plugin>
 </plugins>

然后在Mybatis的PaginationInteceptor中调用方言的getLimitString方法。

Oracle表名长度问题

oracle11g和之前的版本表名的长度为32个字符，oracle12c以后修改为128个字符，所以在使用oracle11g和其以前的版本时需要注意表名长度问题。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 201,924评论 5赞 474
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,781评论 2赞 378
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 148,813评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,264评论 1赞 272
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,273评论 5赞 363
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,383评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,800评论 3赞 393
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,482评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,673评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,497评论 2赞 318
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,545评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,240评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,802评论 3赞 304
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,866评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,101评论 1赞 258
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,673评论 2赞 348
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,245评论 2赞 341