ORCLE与MySQL的相互转化
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: 200ORACLE
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关于Oracle URL的说明：
低版本的Oracle数据库使用
jdbc:oracle:thin:@IP:1521:数据库实例高版本的Oracle数据库使用
jdbc:oracle:thin:@IP:1521/数据库实例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中，可以直接将日期格式的字符串插入或更新到日期类型的字段中，无需调用str_to_date()。
STR_TO_DATE('2024-10-17 12:15:15', '%Y-%m-%d %H:%i:%s')但是，在Oracle中是无法将日期格式的字符串插入或更新到日期类型的字段中，只能使用TO_DATE()，否则会报错：ORA-01861: 文字与格式字符串不匹配。
TO_DATE('2024-10-17 12:15:15', 'YYYY-MM-DD HH24:MI:SS')
<insert id="insertBaseAcUser" parameterType="com.XXX.platform.api.user.domain.User">
insert into base_ac_user (USERID, USERCODE, USERNAME, USERPASSWORD, STATUS, MACCODE, IPADDRESS, CREATEDATE, HEADIMAGE, ORGID, USERTEL, USEREMAIL, USERZIP, REMARK, ENDDATE, LASTLOGIN, THEME, HOME, ADMINORGIDS, SECRETKEY, STATUSDESC, SIMBAID, ORGCODE, AREACODE, SEX, USERMOBILE, ADDRESS, YSID, FID, USERIDCARD, ROLENAMES, FRCODE, FSITES, OPENID, OVERDUEDATE, ISDEL)
values(#{userId},#{userCode}, #{userName}, #{userPassWord}, #{status}, #{macCode, jdbcType=VARCHAR}, #{ipAddress, jdbcType=VARCHAR}, TO_DATE(#{createDate, jdbcType=VARCHAR}, 'YYYY-MM-DD HH24:MI:SS'), #{headImage, jdbcType=VARCHAR}, #{orgId}, #{userTel, jdbcType=VARCHAR}, #{usereMail, jdbcType=VARCHAR}, #{userZip, jdbcType=VARCHAR}, #{remark, jdbcType=VARCHAR},#{endDate, jdbcType=TIMESTAMP}, TO_DATE(#{lastLogin, jdbcType=VARCHAR}, 'YYYY-MM-DD HH24:MI:SS'), #{theme, jdbcType=VARCHAR}, #{home, jdbcType=VARCHAR}, #{adminOrgIds, jdbcType=VARCHAR}, #{secretKey, jdbcType=VARCHAR}, #{statusDesc, jdbcType=VARCHAR}, #{simbaId, jdbcType=VARCHAR}, #{orgCode, jdbcType=VARCHAR}, #{areaCode, jdbcType=VARCHAR}, #{sex}, #{userMobile, jdbcType=VARCHAR}, #{address, jdbcType=VARCHAR}, #{ysid, jdbcType=VARCHAR}, #{fid, jdbcType=VARCHAR}, #{userIdCard, jdbcType=VARCHAR}, #{roleNames, jdbcType=VARCHAR}, #{frCode, jdbcType=VARCHAR}, #{fsItes, jdbcType=BIGINT}, #{openId, jdbcType=VARCHAR}, TO_DATE(#{overdueDate, jdbcType=VARCHAR}, 'YYYY-MM-DD HH24:MI:SS'), '0')
</insert>日期转字符串
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>示例2
mysql
<select id="selectNotReadCount" parameterType="com.xxx.platform.api.message.domain.MessageDomain" resultType="java.lang.Integer">
SELECT
COUNT(1)
FROM
base_message_info bmi
WHERE
bmi.available = 1
AND bmi.status = 0
AND bmi.receiver = #{receiver}
<![CDATA[ AND IF(bmi.send_time IS NOT NULL,send_time, now()) <= now() ]]>
</select>oracle
<select id="selectNotReadCount" parameterType="com.xxx.platform.api.message.domain.MessageDomain" resultType="java.lang.Integer">
SELECT
COUNT(1)
FROM
base_message_info bmi
WHERE
bmi.available = 1
AND bmi.status = 0
AND bmi.receiver = #{receiver}
<![CDATA[
AND (CASE
WHEN bmi.send_time IS NOT NULL THEN bmi.send_time
ELSE SYSDATE
END) <= SYSDATE
]]>
</select>示例3
mysql
IF(bmi.`status`='0','未读','已读') as statusNameoracle
DECODE(bmi.status,0,'未读','已读') AS "statusName"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) AS '${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) AS '${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,1oracle使用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的字段名称是区分大小写的。
换句话说就是：不管你设置的别名是啥，最终展示的都将是大写。
第1种实现方式
如果要强制展示小写，需要使用双引号将其包裹起来。
<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实体类的属性名称保持一致，才能完成赋值。
第2种实现方式
如果不设置别名，我们也可以手动指定字段名称与属性名称的映射关系。
<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>第3种实现方式
第1种和第2种的结合体。
将响应字段与实体类属性进行一一对应（设置映射关系）。
<resultMap id="BaseResultMap" type="com.xxx.platform.api.widget.domain.WidgetV2Domain">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="role_id" jdbcType="BIGINT" property="roleId"/>
<result column="user_id" jdbcType="BIGINT" property="userId"/>
<result column="type" jdbcType="BIGINT" property="type"/>
<result column="json_str" jdbcType="VARCHAR" property="jsonStr"/>
<result column="extra_str" jdbcType="VARCHAR" property="extraStr"/>
<result column="index_desc" jdbcType="VARCHAR" property="indexDesc"/>
<result column="w" jdbcType="BIGINT" property="w"/>
<result column="h" jdbcType="BIGINT" property="h"/>
<result column="x" jdbcType="BIGINT" property="x"/>
<result column="y" jdbcType="BIGINT" property="y"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>将查询字段剥离出来
<sql id="Column">
id,role_id,user_id,type,json_str,extra_str,index_desc,create_time,update_time,w,h,x,y
</sql>调用
<select id="findWidgetByUserId" resultMap="BaseResultMap">
SELECT <include refid="Column"></include>
FROM base_ac_index i
WHERE i.user_id = #{userId}
OR i.role_id IN (SELECT r.ROLEID
FROM base_ac_user_role r
WHERE r.USERID = #{userId})
</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则需要使用序列。
ORCLE插入表数据，共有3种实现方式。
如果是mysql转oracle，已经将mysql的数据导入到了oracle中，需要遵循以下步骤：
第一步： 确认表的主键，以及当前主键的最大值。
查询表的主键字段
查询表主键当前的最大值
第二步：
创建序列，并将序列的最小值设置为：当前主键值+1；
序列名称，一般定义为：SEQ_表名。
第三步：修改sql。
方式1
<insert id="addEvaluationObject" parameterType="com.xyhsoft.platform.api.questionnaire.domain.EvaluationObjectDomain">
insert into base_evaluation_object(id,templateId,name,code,orgCode,qrCode)
value (SEQ_BASE_EVALUATION_OBJECT.NEXTVAL, #{templateId},#{name},#{code},#{orgCode},#{qrCode})
</insert>方式2
插入时返回主键
插入之前获取主键
<insert id="insertMeeting" parameterType="com.xyhsoft.platform.api.meeting.domain.Meeting">
<!-- 使用 selectKey 获取自增主键值 -->
<selectKey keyProperty="id" resultType="java.lang.Long" order="BEFORE">
SELECT SEQ_BASE_MEETING_INFO.NEXTVAL FROM DUAL
</selectKey>
insert into base_meeting_info(ID,
<trim suffixOverrides=",">
<if test="meetingName != null and meetingName != '' ">
meetingName,
</if>
<if test="address != null and address != '' ">
address,
</if>
<if test="meetingRoom != null and meetingRoom != '' ">
meetingRoom,
</if>
<if test="startTime != null ">
startTime,
</if>
<if test="endTime != null ">
endTime,
</if>
<if test="attendeeId != null and attendeeId != '' ">
attendeeId,
</if>
<if test="attendeeName != null and attendeeName != '' ">
attendeeName,
</if>
<if test="descr != null and descr != '' ">
descr,
</if>
<if test="hostId != null">
hostId,
</trim>
)
values (#{id},
<trim suffixOverrides=",">
<if test="meetingName != null and meetingName != '' ">
#{meetingName},
</if>
<if test="address != null and address != '' ">
#{address},
</if>
<if test="meetingRoom != null and meetingRoom != '' ">
#{meetingRoom},
</if>
<if test="startTime != null">
#{startTime},
</if>
<if test="endTime != null">
#{endTime},
</if>
<if test="attendeeId != null and attendeeId != '' ">
#{attendeeId},
</if>
<if test="attendeeName != null and attendeeName != '' ">
#{attendeeName},
</if>
<if test="descr != null and descr != '' ">
#{descr},
</if>
<if test="hostId != null">
#{hostId},
</if>
</trim>
)
</insert>插入操作完成后，主键会被塞入到实体类Meeting的id属性中。
方式3
oracle如果不创建序列，还有一种实现方式：
在插入的时候，查询当前主键的最大值+1。
<insert id="addEvaluationObject" parameterType="com.xyhsoft.platform.api.questionnaire.domain.EvaluationObjectDomain">
insert into base_evaluation_object(id,templateId,name,code,orgCode,qrCode)
value ((SELECT MAX(ID) + 1 FROM base_evaluation_object),#{templateId},#{name},#{code},#{orgCode},#{qrCode})
</insert>2024-10-14 10:42:03
不等于
mysql不等于使用"!="
<update id="updateSta" parameterType="com.xxx.platform.api.questionnaire.domain.ArrangeTemplateVo">
update base_arrange_template_eval set status = 0 where eva_id != #{id}
</update>oracle不等于推荐使用"<>"，但也可以使用使用"!="。
<update id="updateSta" parameterType="com.xxx.platform.api.questionnaire.domain.ArrangeTemplateVo">
<![CDATA[
update base_arrange_template_eval set status = 0 where eva_id <> #{id}
]]>>
</update><update id="updateSta" parameterType="com.xxx.platform.api.questionnaire.domain.ArrangeTemplateVo">
update base_arrange_template_eval set status = 0 where eva_id != #{id}
</update>2024-10-16 17:38:21
mybatis关于字段类型的问题
当数据库是mysql时，插入或更新的时候，参数值可以不指定JDBC类型。
当数据库是oraclel时，插入或更新的时候，参数值如果不指定JDBC类型，则将变成非空参数，如果对应的参数值为null会报错。
如果insert或update使用的是动态拼接参数的话，就不需要指定JDBC类型了。
动态insert
<insert id="insertExtend">
INSERT INTO base_ac_user_extend
<trim prefix="(" suffix=")" suffixOverrides=",">
ID,
<if test="userId != null and userId != ''">userId,</if>
<if test="userCode != null and userCode != ''">userCode,</if>
<if test="status != null and status != ''">status,</if>
<if test="userKey != null and userKey != ''">userKey,</if>
<if test="isDel != null and isDel != ''">isDel,</if>
<if test="userType != null and userType != ''">userType</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
SEQ_BASE_AC_USER_EXTEND.NEXTVAL,
<if test="userId != null and userId != ''">#{userId},</if>
<if test="userCode != null and userCode != ''">#{userCode},</if>
<if test="status != null and status != ''">#{status},</if>
<if test="userKey != null and userKey != ''">#{userKey},</if>
<if test="isDel != null and isDel != ''">#{isDel},</if>
<if test="userType != null and userType != ''">#{userType}</if>
</trim>
</insert>动态update
<update id="updateExtend">
UPDATE base_ac_user_extend
<trim prefix="set" suffixOverrides=",">
<if test="userCode != null and userCode != ''">userCode = #{userCode},</if>
<if test="status != null and status != ''">status= #{status},</if>
<if test="userKey != null and userKey != ''">userKey = #{userKey},</if>
<if test="userType != null and userType != ''">userType = #{userType},</if>
<if test="isDel != null and isDel != ''">isDel = #{isDel}</if>
</trim>
WHERE
userId = #{userId}
</update>mybatis非空判断
对于字符串的非空判断
<if test="department != null and department != ''">
</if>对于对象的非空判断
<if test="expireTime != null">
</if>对于对象的非空判断不能使用!=''，否则会报错，它是用来判断字符串是否为空的。
关于>、<和&
<![CDATA[ ... ]]> 是一种在XML和XHTML中使用的特殊标记，用于告诉解析器包含在其中的内容应该被当作纯文本处理，而不是XML标记。这通常用于包含那些可能会被解析器误认为是XML标记的文本数据，比如包含<、>、&等特殊字符的文本。
<if test="null != validDate and validDate != '' ">
<![CDATA[
and SYSDATE <= end_date
and SYSDATE >= start_date
]]>
</if>错误用法，将<转义
<where>
1=1
<choose>
<when test="orgId != null and orgId ==1">
and bau.ORGID <= 2
</when>
<otherwise>
and bau.ORGID = #{orgLevel}
</otherwise>
</choose>
</where>
CDATA段确保了SQL表达式中的特殊字符不会被XML解析器解析，从而保证了SQL查询的正确性。
查询当天数据
mysql
<![CDATA[
and base_eva_detail.create_time >= CURDATE()
and base_eva_detail.create_time < CURDATE() + INTERVAL 1 DAY
]]> oracle
<![CDATA[
and base_eva_detail.create_time >= TRUNC(SYSDATE)
and base_eva_detail.create_time <= TRUNC(SYSDATE) + INTERVAL '1' DAY
]]>2024-10-24 14:39:16
单行表
mysql查询的时候，不需要加"from dual"，当然，加上也是可以的。
select replace(uuid(),'-','') from dual
select replace(uuid(),'-','')
oracle
在Oracle数据库中，DUAL 是一个特殊的单行表，通常用于返回常量或进行一些不需要实际表数据的操作。
DUAL 表是Oracle系统预定义的一个表，它只有一行一列，且该列的值为空。
SELECT REPLACE(SYS_GUID(),'-','') FROM DUAL
如果不加from dual会报错
guid与uuid
mysql有uuid，没有guid。
oracle有guid，没有uuid。
案例见上面。
2024-10-25 10:42:59
根据出生日期计算年龄
前提：只计算岁数。
严格来说，不满一岁需要计算月数，不满月数要计算天数，如果是这种需求的话，下面的sql显然不符合要求。
一般来说，下面只计算岁数的语法就够用了。
mysql
TIMESTAMPDIFF(YEAR,DATE(出生日期字段),CURDATE()) AS AGE示例
select
NOW(),
STR_TO_DATE('2023-10-25 11:10:10',
'%Y-%m-%d %H:%i:%s') birth1,
TIMESTAMPDIFF(year,
DATE(STR_TO_DATE('2023-10-25 11:10:10',
'%Y-%m-%d %H:%i:%s')),
CURDATE()) as AGE1,
STR_TO_DATE('2023-10-26 11:10:10',
'%Y-%m-%d %H:%i:%s') birth2,
TIMESTAMPDIFF(year,
DATE(STR_TO_DATE('2023-10-26 11:10:10',
'%Y-%m-%d %H:%i:%s')),
CURDATE()) as AGE2
说明：只比较日期，不比较时分秒。
也就是说：假设今天是2024年10月25日，出生日期为2023年10月25日（<=25日），年龄为1岁；出生日期在2023年10月26日及以后（>=26日），年龄为0岁。
oracle
TRUNC(MONTHS_BETWEEN(SYSDATE, 出生日期字段) / 12) AS AGE示例
SELECT SYSDATE,
TO_DATE('2023-10-25 11:10:10', 'YYYY-MM-DD HH24:MI:SS') BIRTH1,
TRUNC(MONTHS_BETWEEN(SYSDATE,
TO_DATE('2023-10-25 11:10:10',
'YYYY-MM-DD HH24:MI:SS')) / 12) AS AGE1,
TO_DATE('2023-10-26 11:10:10', 'YYYY-MM-DD HH24:MI:SS') BIRTH2,
TRUNC(MONTHS_BETWEEN(SYSDATE,
TO_DATE('2023-10-26 11:10:10',
'YYYY-MM-DD HH24:MI:SS')) / 12) AS AGE2
FROM DUAL
说明：只比较日期，不比较时分秒。
比较两个日期的间隔月份数，然后除以12，得到的商就是年龄。
2024-10-17 11:38:47
4.报错信息
未明确定义的列
当两表有相同的列名，但在查询时，却没有指明查的是哪张表的列时，会报此错误。
当要查询的表字段名称重复，且进行分页时，会报此错误。
写在最后
哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！
相关推荐：
本文来自博客园，作者：Marydon，转载请注明原文链接：https://www.cnblogs.com/Marydon20170307/p/18460263
