ORCLE与MySQL的相互转化

Marydon / 2024-10-12 / 原文

1.情景展示

在实际开发中，不同的地方可能所需使用的数据库是不同的。

这就要求，我们开发的程序需要兼容不同的数据库，放到程序里面就是：

需要有不同类型的sqlMap文件。
以既要兼容MySQL，也要兼容Oracle进行举例说明。

2.准备工作

第一步

根据已经写好的一套sql进行复制，然后，在原有sql的基础上修改成对应数据库所支持的SQL。

第二步

修改mapper.xml的扫描路径

以及分页插件的数据库类型

第三步

修改数据库连接配置

MYSQL

spring:
  datasource:
  url: jdbc:mysql://192.168.0.1:3306/test?useUnicode=true&characterEncoding=utf8
    username: 123
    password: 123
    driverClassName: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 200

ORACLE

spring:
  datasource:
    url: jdbc:oracle:thin:@192.168.1.1:1521:orcl
    username: TEST
    password: TEST
    driverClassName: oracle.jdbc.driver.OracleDriver
    hikari:
      maximum-pool-size: 200

3.具体转换

字符串拼接

MYSQL使用concat()

<if test="code != null and code != ''">
	and code like CONCAT('%',#{code},'%')
</if>

ORACLE使用管道符||

<if test="code != null and code != ''">
	and code like '%' || #{code} || '%'
</if>

虽然ORACLE也可以使用CONCAT()连接字符串，但是，它只能将两个字符串拼接到一起。

如果需要拼接2个以上的字符串，只能嵌套N次，形如：

CONCAT(CONCAT('%',#{code}),'%')

字符串转日期

日期转字符串

mysql使用DATE_FORMAT()

<if test="endTime != null">
	,DATE_FORMAT(#{endTime},'%Y-%m-%d 23:59:59') as endTime
</if>

DATE_FORMAT(base_eva_detail.create_time,'%Y-%m-%d %H:%i:%s') as create_time,

在mysql中，%c表示月份，个位数月份前面不会自动补零；

而%m表示月份，个位数月份前面将会自动补零。

ORACLE使用TO_CHAR()

<if test="endTime != null">
	,TO_CHAR(#{endTime},'YYYY-MM-DD 23:59:59') as endTime
</if>

TO_CHAR(base_eva_detail.create_time, 'YYYY-MM-DD HH24:MI:SS') as create_time,

系统时间

mysql使用now()

date_format(now(),'%Y-%c-%d')

oracle使用SYSDATE

TO_CHAR(SYSDATE, 'YYYY-MM-DD')

IF()函数

MYSQL

<if test="orgId != null and orgId != ''">
	and if(boi.ORGSEQ is not null, boi.ORGSEQ like CONCAT('%',#{orgId},'.%'), boi.ORGID = #{orgId})
</if>

ORALCE

ORALCE没有IF函数

<if test="orgId != null and orgId != ''">
	AND (
		(boi.ORGSEQ IS NOT NULL AND boi.ORGSEQ LIKE '%' || #{orgId} || '%'
		OR
		boi.ORGID = #{orgId})
	)
</if>

ifnull()/NVL()

mysql使用ifnull()

ifnull(sum(base_eva_question_detail.eva_value),0) as allScore,

ifnull(trim(at.city_name), '') as city_name,

ORALCE使用NVL()

NVL(sum(base_eva_question_detail.eva_value),0) as allScore,

NVL(trim(at.city_name),'') as city_name,

将多行数据合并成一行

mysql使用group_concat()

<foreach collection="headers" item="item" index="index">
	<if test="item.evaQuestionType == 1">
		,GROUP_CONCAT( CASE eva_question_num WHEN '${item.evaQuestionNum}' THEN base_eva_question_detail.eva_answer END ) '${item.dataKey}'
	</if>
	<if test="item.evaQuestionType == 6 || item.evaQuestionType == 5">
		,GROUP_CONCAT( CASE eva_question_num WHEN '${item.evaQuestionNum}' THEN base_eva_question_detail.eva_answer  END )'${item.dataKey}'
	</if>
</foreach>

ORACLE使用LISTAGG()

<foreach collection="headers" item="item" index="index">
	<if test="item.evaQuestionType == 1">
		,LISTAGG(CASE eva_question_num WHEN '${item.evaQuestionNum}' THEN base_eva_question_detail.eva_answer END) '${item.dataKey}'
	</if>
	<if test="item.evaQuestionType == 6 || item.evaQuestionType == 5">
		,LISTAGG(CASE eva_question_num WHEN '${item.evaQuestionNum}' THEN base_eva_question_detail.eva_answer END)'${item.dataKey}'
	</if>
</foreach>

分页查询

mysql使用limit

SELECT
	*
FROM
	sso_access_token
WHERE
	`status` = 1
  AND user_id = #{userId}
  AND user_name = #{userName}
ORDER BY
	create_time DESC
LIMIT 0,1

oracle使用ROWNUM

<![CDATA[
	SELECT *
	FROM (
		SELECT *
		FROM sso_access_token
		WHERE status = 1
		AND user_id = #{userId}
		AND user_name = #{userName}
		ORDER BY create_time DESC
	)
	WHERE ROWNUM <= 1
]]>

字段名称（设置查询的别名）

mysql的字段名称是不区分大小写的。

换句话说就是：别名的大小写是啥，最终展示的就是啥。

<select id="getEvaluationObjectList" resultType="com.xyhsoft.platform.api.questionnaire.domain.EvaluationObjectDomain">
    SELECT beo.id,beo.code,beo.name,qrCode, NVL(sum(btd.score),0) as score
        FROM base_evaluation_object beo
</select>

oracle的字段名称是区分大小写的。

换句话说就是：不管你设置的别名是啥，最终展示的都将是大写。

如果要强制展示小写，需要使用双引号将其包裹起来。

<select id="getEvaluationObjectList" resultType="com.xyhsoft.platform.api.questionnaire.domain.EvaluationObjectDomain">
    SELECT beo.id AS "id", beo.code AS "code",beo.name AS "name",qrCode AS "qrCode", NVL(sum(btd.score),0) as "score"
        FROM base_evaluation_object beo
</select>

查询的别名为何非得使用驼峰或者小写？

因为查询返回的别名需要与Java实体类的属性名称保持一致，才能完成赋值。

如果不设置别名，我们也可以手动指定字段名称与属性名称的映射关系。

<resultMap id="BaseResultMap" type="com.xyhsoft.platform.api.questionnaire.domain.EvaluationObjectDomain">
	<result column="ID" jdbcType="BIGINT" property="id"/>
	<result column="TEMPLATEID" jdbcType="BIGINT" property="templateId"/>
	<result column="NAME" jdbcType="VARCHAR" property="name"/>
	<result column="CODE" jdbcType="VARCHAR" property="code"/>
	<result column="ORGCODE" jdbcType="VARCHAR" property="orgCode"/>
	<result column="QRCODE" jdbcType="VARCHAR" property="qrcode"/>
	<result column="ISDEL" jdbcType="BIGINT" property="isDel"/>
	<result column="CREATETIME" jdbcType="TIMESTAMP" property="createTime"/>
	<result column="UPDATETIME" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<select id="getEvaluationObjectById" resultMap="BaseResultMap">
	SELECT *
	FROM base_evaluation_object
	where id = #{id}
</select>

主键设置

mysql的表主键一般情况下设置成自增即可。

<insert id="addEvaluationObject" useGeneratedKeys="true" keyProperty="id" parameterType="com.xyhsoft.platform.api.questionnaire.domain.EvaluationObjectDomain">
	insert into base_evaluation_object(id,templateId,name,code,orgCode,qrCode)
		value (#{id},#{templateId},#{name},#{code},#{orgCode},#{qrCode})
</insert>

主键可以为null，当往表中插入数据时，会自动填充主键。

oracle则需要使用序列。

如果是mysql转oracle，已经将mysql的数据导入到了oracle中，需要遵循以下步骤：

第一步：确认表的主键，以及当前主键的最大值。

查询表的主键字段

查询表主键当前的最大值

第二步：

创建序列，并将序列的最小值设置为：当前主键值+1；

序列名称，一般定义为：SEQ_表名。

第三步：修改sql。

<insert id="addEvaluationObject" parameterType="com.xyhsoft.platform.api.questionnaire.domain.EvaluationObjectDomain">
	insert into base_evaluation_object(id,templateId,name,code,orgCode,qrCode)
		value ((SELECT SEQ_BASE_EVALUATION_OBJECT.NEXTVAL FROM DUAL),#{templateId},#{name},#{code},#{orgCode},#{qrCode})
</insert>