【桂林银行项目迁移】

一、数据库迁移（mysql->oralce）

1、创建oracle用户及授权

-- 创建用户
CREATE USER resourcedb IDENTIFIED BY resourcedb;

-- 授权
GRANT CREATE SESSION TO resourcedb;
GRANT SELECT ANY TABLE TO resourcedb;
GRANT UPDATE ANY TABLE TO resourcedb;
GRANT INSERT ANY TABLE TO resourcedb;
GRANT DELETE ANY TABLE TO resourcedb;

grant dba to decisionenginedb;

2、数据库表迁移（用新创建的用户登录），使用navicat数据传输

3、oracle规范表和字段名大写

修改表名大写
select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);

修改表空间内所有表字段大写
select 'alter table "'||table_name||'" rename column "'||column_name||'" to '||upper(column_name)||';'  cn from user_tab_columns 
where table_name in (select table_name tn from user_tables)  and column_name<>upper(column_name)

二、项目代码调整

1、添加pom依赖

<!-- 添加oracle依赖 -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>

2、配置文件添加oracle数据源配置

#oracle数据源配置
spring.datasource.url=jdbc:oracle:thin:@//192.168.11.166:1521/malldb
spring.datasource.username=服务对应的用户名(decisionenginedb)
spring.datasource.password=服务对应的密码(decisionenginedb)
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.connection-test-query=select 1 from dual

3、接口调试，分页，函数等等调整

3.1、oracle分页

oracle分页工具类

package com.icitic.mc.resource.utils;

/**
 * @author shenzhongwu
 * @description oracle分页工具类
 * @date 2022/1/24
 */
public class OraclePageUtils {

    /**
     * 获取oracle分页查询语句
     *
     * @param src 参数
     * @return oracle分页查询语句
     */
    public static String getOraclePage(String src) {
        return " SELECT * from (SELECT ROWNUM AS rowno, a.* from ( " + src +
                " )a where ROWNUM <= :pageSize ) table_alias where table_alias.rowno > :offset";
    }
}

3.2、分页调整：跟mysql对比

mysql

//mysql分页查询
sql.append("limit :offset,:pageSize ");
params.put("offset", pageable.getOffset());
params.put("pageSize", pageable.getPageSize());
log.info("执行sql为{}", sql);

oracle

//oracle分页查询调整
String pageSql = OraclePageUtils.getOraclePage(sql.toString());
params.put("offset", pageable.getPageSize() * pageable.getPageNumber());
params.put("pageSize", pageable.getPageSize() * pageable.getPageNumber() + pageable.getPageSize());
log.info("执行sql为{}", pageSql);

3.3、函数调整

3.3.1、三目运算符

decode(字段名, null, '值1', '值2')     例子：decode(rc.id, null, '0', '1')