🥪MyBatis操作Oracle（实现兼容Oracle和MySQL）

MyBatis操作Oracle（实现兼容Oracle和MySQL）

前言

有这样的需求，在Oracle或者MySQL中有相同的student表。

如果在后面不确定项目最后用到哪种类型的数据库的时候，可以对Oracle或者MySQL语法的xml，单独编写

因为在语法上面两种关系型数据库有些许的区别。

环境

以Oracle11g来演示，只需要创建序列，不需要创建触发器，通过ORM框架操作来生成主键

MySQL版本是8.x

代码地址：https://gitee.com/zhang-zhixi/springboot-mp-oracle-auto.git

1、分别创建Student表

Oracle：

CREATE TABLE "STUDENT" (
  "ID" NUMBER NOT NULL,
  "NAME" VARCHAR2(255 BYTE),
  "AGE" NUMBER(2,0),
  "SEX" NVARCHAR2(2),
  "TEL" NUMBER,
  "EMAIL" VARCHAR2(255 BYTE),
  "BIRTHDAY" DATE,
  "CREATE_TIME" DATE,
  "UPDATE_TIME" DATE,
  "IS_DELETED" NUMBER,
  CONSTRAINT "PK_STUDENT" PRIMARY KEY ("ID")
);

COMMENT ON COLUMN "STUDENT"."ID" IS '主键';
COMMENT ON COLUMN "STUDENT"."NAME" IS '姓名';
COMMENT ON COLUMN "STUDENT"."AGE" IS '年龄';
COMMENT ON COLUMN "STUDENT"."SEX" IS '性别';
COMMENT ON COLUMN "STUDENT"."TEL" IS '电话号码';
COMMENT ON COLUMN "STUDENT"."EMAIL" IS '电子邮箱';
COMMENT ON COLUMN "STUDENT"."BIRTHDAY" IS '生日';
COMMENT ON COLUMN "STUDENT"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "STUDENT"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "STUDENT"."IS_DELETED" IS '是否删除(0-未删除,1已删除)';

MySQL：

CREATE TABLE `student`  (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `AGE` int NULL DEFAULT NULL COMMENT '年龄',
  `SEX` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `TEL` int NULL DEFAULT NULL COMMENT '电话号码',
  `EMAIL` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电子邮箱',
  `BIRTHDAY` date NULL DEFAULT NULL COMMENT '生日',
  `CREATE_TIME` date NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_TIME` date NULL DEFAULT NULL COMMENT '更新时间',
  `IS_DELETED` int NULL DEFAULT NULL COMMENT '是否删除(0：未删除，1：已删除)',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1000 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

2、为Oracle创建序列

create sequence SEQ_STUDENT_ID
    minvalue 1 			--最小值
    nomaxvalue 			--不设置最大值(由机器决定)，或 根据表字段的值范围设置 maxvalue
    start with 1 		--从1开始计数，数值可变
    increment by 1 	--每次加1，数值可变
    nocycle 				--nocycle：一直累加，不循环；cycle：达到最大值后，将从头开始累加
		-- 		nocache：在内存中不缓存序列的值；cache在内存中缓存序列的值
    nocache; 

3、application.properties数据库配置

# 数据库配置：Oracle
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@(description=(address=(protocol=tcp)(port=1521)(host=127.0.0.1)(PORT = 1521))(connect_data=(SERVER = DEDICATED)(service_name=orcl)))
spring.datasource.username=xxx
spring.datasource.password=xxx

# 数据库配置：MySQL
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.datasource.url=jdbc:mysql://localhost:3306/demo?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
#spring.datasource.username=xxx
#spring.datasource.password=xxx

4、业务代码

实体：

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName(value = "STUDENT")
public class Student implements Serializable {
    /**
     * 主键
     */
    @TableId(value = "ID", type = IdType.AUTO)
    private Long id;

    /**
     * 姓名
     */
    @TableField(value = "NAME")
    private String name;

    /**
     * 年龄
     */
    @TableField(value = "AGE")
    private Integer age;

    /**
     * 性别
     */
    @TableField(value = "SEX")
    private String sex;

    /**
     * 电话号码
     */
    @TableField(value = "TEL")
    private Long tel;

    /**
     * 电子邮箱
     */
    @TableField(value = "EMAIL")
    private String email;

    /**
     * 生日
     */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @TableField(value = "BIRTHDAY")
    private Date birthday;

    /**
     * 创建时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @TableField(value = "CREATE_TIME")
    private Date createTime;

    /**
     * 更新时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @TableField(value = "UPDATE_TIME")
    private Date updateTime;

    /**
     * 是否删除(0-未删除,1已删除)
     */
    @TableField(value = "IS_DELETED")
    private Long isDeleted;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

Mybati配置(***)：

@Configuration
public class MybatisConfig {
    /**
     * DatabaseIdProvider元素主要是为了支持不同的数据库
     * @return 数据库标识
     */
    @Bean
    public DatabaseIdProvider getDatabaseIdProvider() {
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.setProperty("Oracle", "oracle");
        properties.setProperty("MySQL", "mysql");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }
}

Mapper.xml

第一种方法，实现Oracle和MySQL插入数据：可以维护两套插入语句，需要指定标签参数databaseId

Oracle不支持主键自增，可以通过selectKey标签来访问序列来生成ID

<insert id="insertUserToMybatis" parameterType="com.zhixi.pojo.Student" databaseId="oracle">
        <selectKey order="BEFORE" resultType="java.lang.Long" keyProperty="id">
            SELECT SEQ_STUDENT_ID.NEXTVAL FROM DUAL
        </selectKey>
        insert into STUDENT
        <trim prefix="(" suffix=")" suffixOverrides=",">
            ID,
            <if test="name != null and name != '' ">NAME,</if>
            <if test="age != null and age != '' ">AGE,</if>
            <if test="sex != null and sex != '' ">SEX,</if>
            <if test="tel != null and tel != '' ">TEL,</if>
            <if test="email != null and email != '' ">EMAIL,</if>
            <if test="updateTime != null">UPDATE_TIME,</if>
            IS_DELETED,CREATE_TIME
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{id},
            <if test="name != null and name != '' ">#{name},</if>
            <if test="age != null and age != '' ">#{age},</if>
            <if test="sex != null and sex != '' ">#{sex},</if>
            <if test="tel != null and tel != '' ">#{tel},</if>
            <if test="email != null and email != '' ">#{email},</if>
            <if test="updateTime != null">#{updateTime},</if>
            0,
            sysdate
        </trim>
    </insert>

    <insert id="insertUserToMybatis" parameterType="com.zhixi.pojo.Student" databaseId="mysql">
        insert into STUDENT
        <trim prefix="(" suffix=")" suffixOverrides=",">
            ID,
            <if test="name != null and name != '' ">NAME,</if>
            <if test="age != null and age != '' ">AGE,</if>
            <if test="sex != null and sex != '' ">SEX,</if>
            <if test="tel != null and tel != '' ">TEL,</if>
            <if test="email != null and email != '' ">EMAIL,</if>
            <if test="updateTime != null">UPDATE_TIME,</if>
            IS_DELETED,CREATE_TIME
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{id},
            <if test="name != null and name != '' ">#{name},</if>
            <if test="age != null and age != '' ">#{age},</if>
            <if test="sex != null and sex != '' ">#{sex},</if>
            <if test="tel != null and tel != '' ">#{tel},</if>
            <if test="email != null and email != '' ">#{email},</if>
            <if test="updateTime != null">#{updateTime},</if>
            0,
            now()
        </trim>
    </insert>

第二种方法：通过_databaseId来判断是否通过序列生成ID

此处判断的是ID和创建时间，来作为区分MySQL和Oracle依据，因为Oracle需要通过序列创建ID，并且MySQL和Oracle中时间函数也不一样，如下：

<insert id="insertUserToMybatis" parameterType="com.zhixi.pojo.Student">
    insert into STUDENT
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="_databaseId == 'oracle' ">ID,</if>
        <if test="name != null and name != '' ">NAME,</if>
        <if test="age != null and age != '' ">AGE,</if>
        <if test="sex != null and sex != '' ">SEX,</if>
        <if test="tel != null and tel != '' ">TEL,</if>
        <if test="email != null and email != '' ">EMAIL,</if>
        <if test="updateTime != null">UPDATE_TIME,</if>
        IS_DELETED,CREATE_TIME
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
        /*判断是否是Oracle，是Oracle就通过序列生成ID*/
        <if test="_databaseId == 'oracle' ">SEQ_STUDENT_ID.NEXTVAL,</if>
        <if test="name != null and name != '' ">#{name},</if>
        <if test="age != null and age != '' ">#{age},</if>
        <if test="sex != null and sex != '' ">#{sex},</if>
        <if test="tel != null and tel != '' ">#{tel},</if>
        <if test="email != null and email != '' ">#{email},</if>
        <if test="updateTime != null">#{updateTime},</if>
        0,
        <choose>
            <when test="_databaseId == 'mysql' ">
                now()
            </when>
            <when test="_databaseId == 'oracle' ">
                sysdate
            </when>
        </choose>
    </trim>
</insert>

5、测试：

自行测试即可；

@Test
void insertStudentToMybatis() throws ParseException {
    Student student = new Student();
    student.setName("张三");
    student.setAge(23);
    student.setSex("男");
    student.setTel(1888888888L);
    student.setEmail("1820712542@qq.com");
    student.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("2000-03-05"));
    student.setUpdateTime(new Date());
    studentService.insertUserToMybatis(student);
}