内容简介:因项目需求,小编要将项目从mysql迁移到oracle中 ~之前已经完成现在将完成 基于
一、前言
因项目需求,小编要将项目从 mysql 迁移到oracle中 ~
之前已经完成 数据迁移
( https://zhengqing.blog.csdn.net/article/details/103694901
)
现在将完成 基于 MyBatis-Plus
将项目中的 MySQL
语句全部 转换
成 Oracle
语句
大概实现步骤:
xxxMapper.xml
二、MyBatis-Plus 支持双库配置 【 mysql
, oracle
】
1、 application.yml
中配置 mybatis-plus
的 database-id
# mybatis-plus配置 mybatis-plus: configuration: jdbc-type-for-null: 'null' # 解决oracle更新数据为null时无法转换报错 database-id: oracle # 支持多库配置 mysql,oracle
2、MybatisPlus核心配置文件 -> 根据不同的数据库厂商执行不同的SQL
@Configuration @MapperScan("com.zhengqing.demo.modules.**.mapper*") public class MybatisPlusConfig { /** * `xxxMapper.xml`文件中的`databaseId`会自动识别使用的数据库类型与这里相对应 * 注: 如果没有指定`databaseId`则该 SQL 语句适用于所有数据库哦~ * * databaseIdProvider:支持多数据库厂商 * VendorDatabaseIdProvider: 得到数据库厂商的标识(驱动getDatabaseProductName()),mybatis就能根据数据库厂商标识来执行不同的sql; * MySQL,Oracle,SQL Server,xxxx */ @Bean public DatabaseIdProvider getDatabaseIdProvider(){ DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider(); Properties properties = new Properties(); // 为不同的数据库厂商起别名 properties.setProperty("MySQL","mysql"); properties.setProperty("Oracle","oracle"); databaseIdProvider.setProperties(properties); return databaseIdProvider; } }
3、 xxxMapper.xml
中通过 databaseId
指定数据库类型
<select id="selectUserInfo" resultMap="UserVO" databaseId="mysql"> SELECT * FROM 表名 LIMIT 1 </select> <select id="selectUserInfo" resultMap="UserVO" databaseId="oracle"> SELECT * FROM 表名 WHERE ROWNUM <= 1 </select>
三、Oracle中创建常用函数
这里根据个人项目情况去实际应用即可~
1、 ORACLE_TO_UNIX
Oracle时间 Date类型转换为Unix时间戳,等同于mysql中的 UNIX_TIMESTAMP
create or replace function ORACLE_TO_UNIX(in_date IN DATE) return number is begin return( ROUND( (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600, 0) ); end ORACLE_TO_UNIX;
2、 FIND_IN_SET
CREATE OR REPLACE FUNCTION FIND_IN_SET(piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',') RETURN NUMBER IS l_idx number:=0; -- 用于计算piv_str2中分隔符的位置 str varchar2(500); -- 根据分隔符截取的子字符串 piv_str varchar2(500) := piv_str2; -- 将piv_str2赋值给piv_str res number:=0; -- 返回结果 loopIndex number:=0; BEGIN -- 如果piv_str中没有分割符,直接判断piv_str1和piv_str是否相等,相等 res=1 IF instr(piv_str, p_sep, 1) = 0 THEN IF piv_str = piv_str1 THEN res:= 1; END IF; ELSE -- 循环按分隔符截取piv_str LOOP l_idx := instr(piv_str,p_sep); loopIndex:=loopIndex+1; -- 当piv_str中还有分隔符时 IF l_idx > 0 THEN -- 截取第一个分隔符前的字段str str:= substr(piv_str,1,l_idx-1); -- 判断 str 和piv_str1 是否相等,相等 res=1 并结束循环判断 IF str = piv_str1 THEN res:= loopIndex; EXIT; END IF; piv_str := substr(piv_str,l_idx+length(p_sep)); ELSE -- 当截取后的piv_str 中不存在分割符时,判断piv_str和piv_str1是否相等,相等 res=1 IF piv_str = piv_str1 THEN res:= loopIndex; END IF; -- 无论最后是否相等,都跳出循环 EXIT; END IF; END LOOP; -- 结束循环 END IF; -- 返回res RETURN res; END FIND_IN_SET;
四、 工具 类(MySQL语句转换Oracle语句)
替换步骤:
-
在
xxxMapper.xml
中将所有sql语句上加入databaseId="mysql"
- 复制一份mysql的sql(即 将替换的oracle语句)
-
在复制的sql上加入
databaseId="oracle"
- 找出mysql与oracle语句区别,然后替换sql
温馨小提示: 这里工具类只供参考,实际操作根据自己的项目做修改哦,操作前建议先备份自己的项目,以防操作不当丢失代码哦!
import org.apache.commons.lang3.StringUtils; import org.junit.Test; import java.io.*; import java.util.*; /** * <p> mysql迁移oracle 测试工具类 </p> * * @description : * @author : zhengqing * @date : 2020/1/08 10:10 */ public class MySQLToOracleTest { private final static String ORACLE_SQL = " <!-- ====================================== ↓↓↓↓↓↓ oracle ↓↓↓↓↓↓ ====================================== -->"; @Test // 替换项目中的sql语句 public void testSQL() throws Exception { String path = System.getProperty("user.dir") + "\\src\\main\\java\\com\\zhengqing\\xxx"; // TODO 这里替换为自己的项目路径 File file = new File(path); HashMap<Object, Object> fileMap = new HashMap<>(); getAllFileByRecursion(fileMap, file); fileMap.forEach((key, value) -> { String fileXmlName = (String) key; File fileXml = (File) value; String filePath = fileXml.getPath(); if (fileXmlName.equals("Test.xml")) { System.out.println(filePath); try { // 1、加入 databaseId="mysql" addMysql(filePath); // 2、复制一份oracle的sql if (!checkHasOracle(filePath)) { copyMysqlToOracle(filePath); } // 3、加入 databaseId="oracle" addOracle(filePath); // 4、替换mybatis `xxxMapper.xml` 中的sql语句 repalceSQL(filePath); } catch (IOException e) { e.printStackTrace(); } } }); System.out.println(fileMap); } /** * 替换mybatis `xxxMapper.xml` 中的sql语句 */ private static void repalceSQL(String path) throws IOException { File file = new File(path); FileReader in = new FileReader(file); BufferedReader bufIn = new BufferedReader(in); // 内存流, 作为临时流 CharArrayWriter tempStream = new CharArrayWriter(); // 替换 String line = null; int row = 0; int rowOracle = 0; while ((line = bufIn.readLine()) != null) { row++; if (line.contains(ORACLE_SQL)) { rowOracle = row; } if (rowOracle != 0 && row > rowOracle) { // ① 替换 `LIMIT` -> `AND ROWNUM <= 1` TODO 【注: 部分包含`ORDER BY` 关键字,需单独处理】 if (line.contains("limit") || line.contains("LIMIT")) { System.out.println(); System.out.println(" ==============================↓↓↓↓↓↓ copy分页所需 (" + row + ") ↓↓↓↓↓↓===================================== "); System.out.println("SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ("); System.out.println(); System.out.println(") TMP WHERE ROWNUM <=1) WHERE ROW_ID > 0"); System.out.println(); } line = StringUtils.replace(line, "limit 1", "AND ROWNUM <= 1"); line = StringUtils.replace(line, "LIMIT 1", "AND ROWNUM <= 1"); line = StringUtils.replace(line, "limit 0,1", "AND ROWNUM <= 1"); line = StringUtils.replace(line, "LIMIT 0,1", "AND ROWNUM <= 1"); // ② oracle中不能使用“ ` ”符号 line = StringUtils.replace(line, "`", ""); // ③ CONCAT('%', #{name}, '%') -> '%'||#{name}||'%' (Oracle中concat函数只能放两个参数) if (line.contains("concat")) { // String newLine = line.substring(line.indexOf("concat(") + 7, line.lastIndexOf("'%')") + 3); line = line.replaceAll(",", " || "); line = line.replaceAll("concat", ""); } if (line.contains("CONCAT")) { // String newLine = line.substring(line.indexOf("CONCAT(") + 7, line.lastIndexOf("'%')") + 3); line = line.replaceAll(",", " || "); line = line.replaceAll("CONCAT", ""); } // ④ `UNIX_TIMESTAMP` -> `ORACLE_TO_UNIX` date类型时间转10位时间戳 line = line.replaceAll("UNIX_TIMESTAMP", "ORACLE_TO_UNIX"); // ⑤ 部分关键字需加上双引号 TODO 【注: 字段名大写,映射的别名需保存原本小写!】 `level -> "LEVEL"` `user -> "USER"` `number -> "NUMBER"` `desc -> "DESC"` List<String> keywordList = new ArrayList<>(Arrays.asList("level", "user", "number")); if (!line.contains("test=")) { for (String e : keywordList) { // StringUtils.swapCase(e) : 大小写互换 line = line.replaceAll(" " + e + " ", " \"" + StringUtils.swapCase(e) + "\" "); line = line.replaceAll("." + e + " ", "\\.\"" + StringUtils.swapCase(e) + "\" "); if (line.endsWith(e) || line.endsWith(e + ",")) { line = line.replaceAll(e, "\"" + StringUtils.swapCase(e) + "\""); } } } if (line.endsWith(" date") || line.endsWith(" date,") || line.endsWith(" 'date'") || line.endsWith(" 'DATE'") || line.endsWith("DATE")) { line = line.replaceAll(" date", " \"date\""); line = line.replaceAll(" date,", " \"date,\""); line = line.replaceAll(" 'date'", " \"date\""); line = line.replaceAll(" 'DATE'", " \"date\""); line = line.replaceAll(" DATE", " \"date\""); } line = line.replaceAll(" date ", " \"date\" "); line = line.replaceAll(" DATE ", " \"date\" "); // ⑥ `IFNULL` -> `NVL` line = line.replaceAll("IFNULL", "NVL"); line = line.replaceAll("ifnull", "NVL"); // ⑦ 时间 `str_to_date` -> `to_date` `date_format` -> `to_char` // `%Y-%m-%d` -> `yyyy-MM-dd` `%Y-%m` -> `yyyy-MM` line = line.replaceAll("str_to_date", "TO_DATE"); line = line.replaceAll("STR_TO_DATE", "TO_DATE"); line = line.replaceAll("date_format", "TO_CHAR"); line = line.replaceAll("DATE_FORMAT", "TO_CHAR"); // 这里注意替换顺序问题,最长的应该放最前面!!! line = line.replaceAll("%Y-%m-%d %H:%i:%S", "yyyy-MM-dd HH24:mi:ss"); line = line.replaceAll("%Y-%m-%d %H:%i:%s", "yyyy-MM-dd HH24:mi:ss"); line = line.replaceAll("%Y-%m-%d %H:%i", "yyyy-MM-dd HH24:mi"); line = line.replaceAll("%Y-%m-%d %H", "yyyy-MM-dd HH24"); line = line.replaceAll("%Y-%m-%d %h", "yyyy-MM-dd HH"); line = line.replaceAll("%Y-%m-%d", "yyyy-MM-dd"); line = line.replaceAll("%Y-%m", "yyyy-MM"); line = line.replaceAll("%Y", "yyyy"); line = line.replaceAll("%H", "HH24"); line = line.replaceAll("%k", "HH24"); line = line.replaceAll("now\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)"); line = line.replaceAll("NOW\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)"); // ⑧ ... // 需手动处理的SQL 【 group by | 批量插入 | ... 】 } // 将该行写入内存 tempStream.write(line); // 添加换行符 tempStream.append(System.getProperty("line.separator")); } // 关闭 输入流 bufIn.close(); // 将内存中的流 写入 文件 FileWriter out = new FileWriter(file); tempStream.writeTo(out); out.close(); } /** * 加入 databaseId="mysql" */ private static void addMysql(String path) throws IOException { File file = new File(path); FileReader in = new FileReader(file); BufferedReader bufIn = new BufferedReader(in); // 内存流, 作为临时流 CharArrayWriter tempStream = new CharArrayWriter(); // 替换 String line = null; while ((line = bufIn.readLine()) != null) { if ((line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) && !line.contains("databaseId")) { if (line.endsWith(">")) { line = line.replaceAll(">", " databaseId=\"mysql\">"); } else { line = line + " databaseId=\"mysql\""; } } // 将该行写入内存 tempStream.write(line); // 添加换行符 tempStream.append(System.getProperty("line.separator")); } // 关闭 输入流 bufIn.close(); // 将内存中的流 写入 文件 FileWriter out = new FileWriter(file); tempStream.writeTo(out); out.close(); } /** * 加入 databaseId="oracle" */ private static void addOracle(String path) throws IOException { File file = new File(path); FileReader in = new FileReader(file); BufferedReader bufIn = new BufferedReader(in); // 内存流, 作为临时流 CharArrayWriter tempStream = new CharArrayWriter(); HashSet<String> lineSet = new HashSet<>(); // 替换 String line = null; while ((line = bufIn.readLine()) != null) { if (line.contains("databaseId=\"mysql\"")) { if (lineSet.contains(line)) { line = line.replaceAll("databaseId=\"mysql\"", "databaseId=\"oracle\""); } lineSet.add(line); } // 将该行写入内存 tempStream.write(line); // 添加换行符 tempStream.append(System.getProperty("line.separator")); } // 关闭 输入流 bufIn.close(); // 将内存中的流 写入 文件 FileWriter out = new FileWriter(file); tempStream.writeTo(out); out.close(); } /** * 复制一份oracle的sql */ private static void copyMysqlToOracle(String path) throws IOException { File file = new File(path); FileReader in = new FileReader(file); BufferedReader bufIn = new BufferedReader(in); // 内存流, 作为临时流 CharArrayWriter tempStream = new CharArrayWriter(); // 替换 String line = null; // 需要替换的行 List<String> lineList = new LinkedList<>(); int row = 0; int firstRow = 0; while ((line = bufIn.readLine()) != null) { row++; if (line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) { firstRow = row; } // 添加替换内容 if (firstRow != 0 && row >= firstRow && !line.contains("</mapper>")) { lineList.add(line); } // 查询结束位置 if (line.contains("</mapper>")) { tempStream.append(System.getProperty("line.separator")); tempStream.write(ORACLE_SQL); tempStream.append(System.getProperty("line.separator")); tempStream.append(System.getProperty("line.separator")); lineList.forEach(lineValue -> { // copy mysql 语句 转为oracle try { tempStream.write(lineValue); tempStream.append(System.getProperty("line.separator")); } catch (IOException e) { e.printStackTrace(); } }); tempStream.append(System.getProperty("line.separator")); } // 将该行写入内存 tempStream.write(line); // 添加换行符 tempStream.append(System.getProperty("line.separator")); } // 关闭 输入流 bufIn.close(); // 将内存中的流 写入 文件 FileWriter out = new FileWriter(file); tempStream.writeTo(out); out.close(); } /** * 检查是否已经复制SQL */ private static boolean checkHasOracle(String path) throws IOException { File file = new File(path); FileReader in = new FileReader(file); BufferedReader bufIn = new BufferedReader(in); // 内存流, 作为临时流 CharArrayWriter tempStream = new CharArrayWriter(); // 替换 String line = null; boolean result = false; while ((line = bufIn.readLine()) != null) { if (line.contains(ORACLE_SQL)) { result = true; } // 将该行写入内存 tempStream.write(line); // 添加换行符 tempStream.append(System.getProperty("line.separator")); } // 关闭 输入流 bufIn.close(); // 将内存中的流 写入 文件 FileWriter out = new FileWriter(file); tempStream.writeTo(out); out.close(); return result; } /** * 递归文件夹 -> 找到所有xml文件 */ private static void getAllFileByRecursion(HashMap<Object, Object> fileMap, File file) { File[] fs = file.listFiles(); for (File f : fs) { String fileName = f.getName(); if (f.isDirectory()) { // 若是目录则递归,否则打印该目录下的文件 getAllFileByRecursion(fileMap, f); } if (f.isFile() && fileName.endsWith(".xml")) { fileMap.put(fileName, f); } } } }
五、总结
这里简单说下MySQL和Oracle中的SQL区别,以及mysql语句转换oracle语句示例
1、分页
LIMIT 0,1 ROWNUM <= 1
情景①:mysql中不含 ORDER BY
-- mysql SELECT * FROM 表名 LIMIT 1 -- oracle SELECT * FROM 表名 WHERE ROWNUM <= 1
情景②:mysql中含 ORDER BY
-- mysql SELECT * FROM 表名 ORDER BY 字段名 DESC LIMIT 1 -- oracle SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT * FROM 表名 ORDER BY 字段名 DESC ) TMP WHERE ROWNUM <= 1 ) WHERE ROW_ID > 0;
温馨小知识: SQL
SELECT语句执行顺序
-
FROM
子句组装来自不同数据源的数据 -
WHERE
子句基于指定的条件对记录进行筛选 -
GROUP BY
子句将数据划分为多个分组 -
聚集函数
进行计算 -
HAVING
子句筛选分组 -
计算
所有表达式
-
ORDER BY
对结果进行排序
2、oracle中字段名不能使用符号 " ` " 包括
-- mysql SELECT `字段名` FROM 表名 -- oracle SELECT 字段名 FROM 表名
3、字符串拼接
注: Oracle中 CONCAT
函数只能放两个参数,因此改为 ||
拼接
CONCAT('%', 'xxx' , '%') '%' || 'xxx' || '%'
-- mysql SELECT 字段名 FROM 表名 WHERE 字段名 LIKE CONCAT('%','helloworld','%') -- oracle SELECT 字段名 FROM 表名 WHERE 字段名 LIKE ('%' || 'helloworld' || '%')
4、date类型时间转10位时间戳
UNIX_TIMESTAMP ORACLE_TO_UNIX
5、 字段名
为 Oracle关键字
需加上 双引号
温馨小提示: 字段名需大写,如果 Java 实体类对应字段为小写,映射的别名注意需保持原本小写与之对应 ~
例如:
-
level -> "LEVEL"
-
user -> "USER"
-
number -> "NUMBER"
-
desc -> "DESC"
-
date
->DATE
6、判断是否为 NULL: 如果x为NULL,则返回value,否则返回x值本身
IFNULL(x, value) NVL(x, value)
7、日期时间互换
前mysql,后oracle
-
字符串类型转时间类型:
STR_TO_DATE
->TO_DATE
-
时间类型转指定字符串类型:
DATE_FORMAT
->TO_CHAR
-
获取系统当前时间:
NOW()
->SELECT SYSDATE FROM DUAL
-- 时间类型转指定字符串类型 SELECT DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s'); -- mysql SELECT TO_CHAR( SYSDATE,'yyyy-MM-dd HH24:mi:ss') FROM DUAL; -- oracle -- 字符串类型转时间类型 SELECT STR_TO_DATE( NOW(), '%Y-%m-%d %H'); -- mysql SELECT TO_DATE( '2020-01-09', 'yyyy-MM-dd') FROM DUAL; -- oracle 【 注:oracle中前者字符串时间的格式需与后者转换格式相同哦~ 】 -- 获取系统当前时间 SELECT NOW(); -- mysql SELECT SYSDATE + 8/24 FROM DUAL; -- oralce 【注:如果服务器时间没有相差8小时则无需加上`8/24`】 -- mysql SELECT YEAR( NOW() ); -- 求年份 SELECT QUARTER( NOW() ); -- 求季度 SELECT MONTH( NOW() ); -- 求月份 -- oracle SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL; -- 求季度
另外这里给出小编所用到的时间标识符格式
-- 前:mysql 后:oracle "%Y-%m-%d %H:%i:%S" "yyyy-MM-dd HH24:mi:ss" "%Y-%m-%d %H:%i:%s" "yyyy-MM-dd HH24:mi:ss" "%Y-%m-%d %H:%i" "yyyy-MM-dd HH24:mi" "%Y-%m-%d %H" "yyyy-MM-dd HH24" "%Y-%m-%d %h" "yyyy-MM-dd HH" "%Y-%m-%d" "yyyy-MM-dd" "%Y-%m" "yyyy-MM" "%Y" "yyyy" "%H" "HH24" "%k" "HH24"
8、判断时 左
右
字段类型
必须 相同
这里注意是必须,可能在oracle版本不同的情况下,老版本不同类型也会查询出来,但建议还是改为相同类型关联,避免以后数据库版本升级出现问题!!!
建议小转大,比如:数字转字符串;并使用 CONCAT
去修改类型,因为mysql和oracle都支持此函数,并且不会在特殊类型上出现问题 ~
-- ex: `JOIN` 关联表时 两张表的关联`字段类型`必须`相同` SELECT a.*,b.* FROM 表1 a LEFT JOIN 表2 b on a.字符串类型字段 = CONCAT(b.数字类型字段, '')
9、批量插入
-- mysql <insert id="insertBatch" databaseId="mysql"> INSERT INTO 表名( `字段名1`, `字段名2`, `字段...`) VALUES <foreach collection="list" item="item" separator="," open="(" close=")"> #{item.字段1},#{item.字段2},#{item中的每一个字段名...} </foreach> </insert> -- oracle <insert id="insertBatch" databaseId="oracle"> INSERT INTO 表名(字段名1,字段名2,xxx...) SELECT A.* FROM( <foreach collection="list" item="item" index="index" separator="UNION ALL" > SELECT #{item.字段1},#{item.字段2},#{item中的每一个字段名...} FROM DUAL </foreach> ) A </insert>
10、分组 GROUP BY
oracle中 GROUP BY
分组后,查询出来的所有字段(除分组字段)必须为聚合函数的字段,否则会报错!
解决:
分析函数 OVER (Partition BY ...) 及开窗函数
-- mysql SELECT 字段名,xxx... FROM 表名 GROUP BY 分组字段 -- oracle SELECT * FROM ( SELECT tb.*, ROW_NUMBER ( ) OVER ( PARTITION BY tb.分组字段 ORDER BY tb.排序字段 DESC ) AS result FROM ( SELECT 字段名,xxx... FROM 表名 -- 此处为查询sql,去掉`GROUP BY`分组条件,将分组字段加到上面 【 注:此sql的查询字段中要么全是聚合函数字段,要么都不是! 】 ) tb ) WHERE result = 1
11、Oracle中表的别名不能用 AS
, 列的别名可以用 AS
why ?:为了防止和Oracle存储过程中的关键字AS冲突的问题
12、注意Oracle对数据类型要求很严!!!
MySQL与Oracle不同之处远不止小编提及的如上几点,更多的还需要大家根据在实际项目中做对比,然后去修改哦 ~
六、本文MySQL转Oracle语句工具源码
温馨小提示:如果后期小编空闲会将上文中提供的测试替换工具类再加以修改,到时候会存放在如下github仓库中...
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- MySQL实战 -- 一条SQL更新语句是如何执行的?
- MySQL实战 -- 一条SQL查询语句是如何执行的?
- MySQL 建表语句转 PostgreSQL 建表语句全纪录
- Go语言开发-过程式编程-通信和并发语句-Select语句
- SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化
- Python 条件语句
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
代码里的世界观——通往架构师之路
余叶 / 人民邮电出版社 / 2018-11 / 59.00元
本书分为两大部分,第一部分讲述程序员在编写程序和组织代码时遇到的很多通用概念和共同问题,比如程序里的基本元素,如何面向对象,如何面向抽象编程,什么是耦合,如何进行单元测试等。第二部分讲述程序员在编写代码时都会遇到的思考和选择,比如程序员的两种工作模式,如何坚持技术成长,程序员的组织生产方法,程序员的职业生涯规划等。一起来看看 《代码里的世界观——通往架构师之路》 这本书的介绍吧!