sims-order-lxzy/doc/Oracle数据库环境支持.md

MySQL转换为Oracle涉及改造点:

MySQL改为Oracle数据库配置

#数据库驱动 
spring.datasource.driver-class-name: oracle.jdbc.OracleDriver
#数据库地址,重写中间件配置,业务系统自定义
spring.datasource.url: jdbc:oracle:thin:@10.1.5.189:29008/invoice_pdb
#数据库用户名,重写中间件配置,业务系统自定义
spring.datasource.username: sims
#数据库密码,重写中间件配置,业务系统自定义
spring.datasource.password: sims_123456_DXHY
#数据库线程池名称,业务系统可以自定义配置
spring.datasource.hikari.pool-name: CommonHikariCP
spring.datasource.hikari.connection-test-query: SELECT 1 FROM DUAL

MySQL改为kingbase数据库配置

#数据库驱动 
spring.datasource.driver-class-name: com.kingbase8.Driver
#数据库地址,重写中间件配置,业务系统自定义
spring.datasource.url: jdbc:kingbase8://10.1.5.151:54321/
#数据库用户名,重写中间件配置,业务系统自定义
spring.datasource.username: system
#数据库密码,重写中间件配置,业务系统自定义
spring.datasource.password: dxhy@123
#数据库线程池名称,业务系统可以自定义配置
spring.datasource.hikari.pool-name: CommonHikariCP
spring.datasource.hikari.connection-test-query: SELECT 1 FROM DUAL

• 人大金仓数据库处理时按照和Oracle一致规则进行处理,只有批量插入时,按照和mysql一样的逻辑处理

对于MySQL转Oracle后mybatis逻辑判断调整

如果有需要区分为MySQL还是Oracle,按照这种方式区分,(默认按照这种进行区分)
   <choose>
        <when test="_databaseId=='mysql'">
            and DATE_FORMAT(qi.sqjsrq,'%Y-%m-%d')&gt;=#{params.sqStartTime}
        </when>
        <when test="_databaseId=='oracle'">
            and TO_CHAR(qi.sqjsrq,'YY-MM-DD')&gt;=#{params.sqStartTime}
        </when>
    </choose>

如果需要整个sql进行区分
    <insert id="batchInsert" parameterType="java.util.List" databaseId="oracle">

日期格式转换

 mysql: date_format()
数据类型为字符串格式
    oracle: to_char(kprq, 'yyyy-mm-dd')
数据类型为日期格式
    oracle: to_date('2024-01-01' ,'yyyy-mm-dd')
    oracle: to_date(concat( '2024-04-01', ' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')

系统日期格式转换

now()  改为 SYSDATE

涉及到limit

1.需要在原有的sql基础上新增rownumber
select * from (原有sql) where 	ROWNUM <= 100
2.或者使用函数,替代limit X(此处需要修改,按照实际填写)
OFFSET 0 ROWS FETCH NEXT X(此处需要修改,按照实际填写) ROWS ONLY

表名后面不能跟as

去掉as， select * FROM dm_spxx AS spxx  改为 select * FROM dm_spxx spxx

group by 补全字段名

Oracle调整:group的字段,必须在查询条件中出现,

CONCAT_WS分组

concat_ws(',',clzldm,clzlmc,cldlmc) 改为 clzldm || ',' || clzlmc || ',' || cldlmc

模糊查询CONCAT拼接%逻辑

CONCAT('%',#{req.mbMc},'%') 改为 CONCAT(CONCAT('%',#{req.mbMc}),'%')

使用group_concat

MySQL:
GROUP_CONCAT( t3.factor ) factor,
Oracle修改:
listagg(t3.factor,',') WITHIN GROUP (ORDER BY t3.factor) AS factor,

MySQL:
GROUP_CONCAT( DISTINCT t3.factor ) factor,
Oracle修改:
regexp_replace(listagg(t3.factor,',') WITHIN GROUP (ORDER BY t3.factor), '([^,]+)(,\1)*(,|$)', '\1\3') AS factor,

如果修改为
listagg(t3.factor,',') WITHIN GROUP (ORDER BY t3.factor) AS factor,
调整为:
xmlagg(xmlparse(t3.factor||',' wellformed) order by t3.factor).getclobval()

MySQL使用反引号(`)来引用标识符，在Oracle中使用双引号(")或者不使用引号。

批量insert，改法参考：https://www.cnblogs.com/Yanjie153/p/16285095.html

mysql示例
    <insert id="insertBatch" useGeneratedKeys="false" databaseId="mysql">
        INSERT INTO LY_TEST (id, name, age )
        VALUES
        <foreach collection ="list" item="user" separator ="," close = ";">
            (#{user.id}, #{user.name}, #{user.age})
        </foreach>
    </insert>
Oracle示例:
        <insert id="insertBatch" useGeneratedKeys="false" databaseId="oracle">
            INSERT ALL
            <foreach collection="list" item="user" index="index"
                     close="SELECT * FROM dual" >
                INTO LY_TEST (id, name, age)
                VALUES (#{user.id}, #{user.name}, #{user.age})
            </foreach>
        </insert>

mysql表id自增

1.创建序列化脚本
    CREATE SEQUENCE XXL_JOB_REGISTRY_ID
         INCREMENT BY 1
         START WITH 1
         NOMAXVALUE
         NOCYCLE
         CACHE 10;
2.insert语句中新增
        <insert id="registrySave" >
            INSERT INTO xxl_job_registry( registry_group , registry_key , registry_value, update_time)
            VALUES( #{registryGroup}  , #{registryKey} , #{registryValue}, #{updateTime})
    		<selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
    			select XXL_JOB_REGISTRY_ID.NEXTVAL from dual
    		</selectKey>
        </insert>

order by排序

1.可以使用别名
2.如果不使用别名,同时存在分组情况,必须和分组的数据保持一致

批量更新，使用foreache时，增加 open="begin" close=";end;"

 <update id="batchSaveDraft">
        <foreach collection="list" item="item" separator=";" open="begin" close=";end;">
            UPDATE LY_SJS SET SFSC = '2',CZSJ = SYSDATE,
            NETID = #{item.userId},

            WHERE SFSC = '3'
            AND ID = #{item.id}
        </foreach>
    </update>

批量删除,

     <delete id="xxx" parameterType="list">
        delete from LY_TEST
        where id in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
     </delete>

字符串为null判断

在oracle中，空字符串当null处理，所以想找到空字符串，就可以用 XXX is null来获取
获取非空字段，就直接用is not null来获取
如果用 “ =''” 或者 “ != '' ” 来作为条件的话，就会找不到结果

ifnull函数处理

IFNULL(column_name, default_value)需要转换为NVL(column_name, default_value)

所有的mysql建议改为if函数部分,不要调整,为了兼容Oracle,只能使用case when

问题排查

• ORA-00933: SQL 命令未正确结束， 原因：mybatis 语句带分号，需要去掉sql语句结尾分号（;）

• java.sql.SQLException: 无效的列类型: 1111， 修改mybatis-config.xml配置，增加下面配置：

• ORA-00925: 缺失 INTO 关键字， insert语句缺少 into，需要加上

• mybatis的实体类与sql表字段名不一致

• ORA-01861: 文字与格式字符串不匹配

  • 需要调整日期格式和字符串格式