mybatis多数据库兼容改造

2018-09-05 本文已影响2人 Code技术资讯

原文链接（博主原创）：mybatis多数据库兼容改造

基于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和其以前的版本时需要注意表名长度问题。