MyBatis project to quickly convert MySQL into Oracle statements

One, foreword

Because of project requirements, xiaobian will migrate project from mysql to Oracle ~

Before have completed data migration (zhengqing.blog.csdn.net/article/det…).

You will now complete the conversion of all MySQL statements in the project to Oracle statements based on MyBatis-Plus

Approximate implementation steps:

1. Change the project to support dual database configuration (because the project based on mysql database has been completed, and do not want to directly replace, so the new Oracle library, so that the whole project can support multiple databases, here is not multi-data source oh!)
2. Create common functions in Oracle
3. Walk through the projectxxxMapper.xmlFile, find the difference between mysql and Oracle statements, and replace most of the SQL
4. Finally, manually change some special mysql statements to Oracle statements

MyBatis-Plus supports dual library configurationmysql.oracle】

1.application.ymlIn the configurationmybatis-plusthedatabase-id

# mybatis-plus: configuration: jdbc-type-for-null: 'null' # mybatis-plus: configuration: jdbc-type-for-null: 'null' Oracle # supports multiple library configurations for mysql, OracleCopy the code

2, MybatisPlus core configuration file -> Execute different SQL according to different database vendors

@Configuration @MapperScan("com.zhengqing.demo.modules.**.mapper*") public class MybatisPlusConfig { /** * The 'databaseId' in the 'xxxmapper. XML' file will automatically identify the type of database being used as corresponding to this * note: If you do not specify a ` databaseId ` then the SQL statement applies to all database oh ~ * * databaseIdProvider: support for multiple database vendors * VendorDatabaseIdProvider: GetDatabaseProductName (), myBatis can execute different SQL based on the database vendor id; * MySQL, Oracle, SQL Server,xxxx */ @Bean public DatabaseIdProvider getDatabaseIdProvider(){ DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider(); Properties properties = new Properties(); // alias properties.setProperty("MySQL"," MySQL") for different database vendors; properties.setProperty("Oracle","oracle"); databaseIdProvider.setProperties(properties); return databaseIdProvider; }}Copy the code

3,xxxMapper.xmlThrough thedatabaseIdSpecifying the database type

<select ID ="selectUserInfo" resultMap="UserVO" databaseId="mysql"> select * FROM table name LIMIT 1 </select> <select Id=" selectUserInfo" resultMap="UserVO" databaseId="oracle"> SELECT * FROM table WHERE ROWNUM <= 1 </ SELECT >Copy the code

Create common functions in Oracle

Here according to the personal project situation to the actual application can ~

1.ORACLE_TO_UNIX

The Oracle Date type is converted to a Unix timestamp, equivalent to UNIX_TIMESTAMP in mysql

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;Copy the code

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; STR varchar2(500); Piv_str varchar2(500) := piv_str2; -- assign piv_str2 to piv_str res number:=0; LoopIndex number:=0; BEGIN -- If there is no separator in piv_str, determine whether piv_str1 and PIv_str are equal. IF instr(piv_str, p_sep, 1) = 0 THEN IF piv_str = piv_str1 THEN res:= 1; END IF; Elsepiv_str LOOP l_idx := instr(piv_str,p_sep); loopIndex:=loopIndex+1; STR := substr(piv_str,1,l_idx-1); IF STR = piv_str1 THEN res:= loopIndex; EXIT; END IF; piv_str := substr(piv_str,l_idx+length(p_sep)); IF piv_str = PIv_str1 THEN res:= loopIndex; IF piv_str = piv_str1 THEN res:= loopIndex; END IF; -- EXIT the loop regardless of whether it is equal at the end; END IF; END LOOP; -- END IF; -- RETURN res RETURN res; END FIND_IN_SET;Copy the code

MySQL > convert MySQL to Oracle

Replacement steps:

1. inxxxMapper.xmlAdd to all SQL statementsdatabaseId="mysql"
2. Copy a copy of mysql SQL (i.e. the oracle statement to be replaced)
3. Join on the replicated SQLdatabaseId="oracle"
4. Find out the difference between mysql and Oracle statements and replace SQL statements

Warm tips: this tool class is for reference only, the actual operation according to their own project to modify oh, before operation, it is recommended to back up their own project, in case of improper operation lost code oh!

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);
            }
        }
    }

}Copy the code

Five, the summary

Here is a brief description of the difference between MySQL and Oracle SQL, as well as MySQL statement conversion Oracle statement example

1, paging

1. mysql: LIMIT 0, 1
2. oracle: ROWNUM <= 1

Situation ① : Mysql does not containORDER BY

-- mysql SELECT * FROM table_name LIMIT 1 -- oracle SELECT * FROM table_name WHERE ROWNUM <= 1Copy the code

Scenario 2: Mysql containsORDER BY

-- mysql SELECT * FROM table_name ORDER BY table_name 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;Copy the code

Sweet tips:SQLSELECT statement execution order

1. FROMClause assembles data from different data sources
2. WHEREClause filters records based on specified criteria
3. GROUP BYClauses divide data into groups
4. Aggregation functionTo calculate
5. HAVINGClause filter grouping
6. To calculateallexpression
7. ORDER BYSort the results

2, Oracle field names cannot include the symbol ‘ ‘

Mysql SELECT * FROM mysql; mysql SELECT * FROM mysqlCopy the code

3. String concatenation

Note: CONCAT function in Oracle only two parameters, thus to | | splicing

1. mysql: CONCAT('%', 'xxx' , '%')
2. oracle: '%' || 'xxx' || '%'

Mysql SELECT * FROM table_name WHERE table_name LIKE CONCAT('%','helloworld','%')  || 'helloworld' || '%')Copy the code

4. Convert the date type to a 10-bit timestamp

1. mysql: UNIX_TIMESTAMP
2. oracle: ORACLE_TO_UNIX(Note: this function was created manually in Step 3 and is not native to Oracle.)

5,The field nameforOracle keywordsNeed to addDouble quotation marks

Tip: The field name must be uppercase. If the corresponding field of the Java entity class is lowercase, the alias of the mapping must be lowercase and correspond to ~

Such as:

1. level -> "LEVEL"
2. user -> "USER"
3. number -> "NUMBER"
4. desc -> "DESC"
5. date -> DATE

NULL: if x is NULL, return value, otherwise return x itself

1. mysql: IFNULL(x, value)
2. oracle: NVL(x, value)

7. Date and time swap

Mysql before oracle

1. String type to time type:STR_TO_DATE -> TO_DATE
2. Time type to specified string type:DATE_FORMAT -> TO_CHAR
3. Obtain the current system time:NOW() -> SELECT SYSDATE FROM DUAL

Turn - time type specified string type SELECT DATE_FORMAT (NOW (), '% % Y - m - H: % d % % I: % s'); -- mysql SELECT TO_CHAR( SYSDATE,'yyyy-MM-dd HH24:mi:ss') FROM DUAL; -- oracle -- string type to time type SELECT STR_TO_DATE(NOW(), '%Y-%m-%d %H'); -- mysql SELECT TO_DATE( '2020-01-09', 'yyyy-MM-dd') FROM DUAL; -- SELECT NOW(); -- SELECT NOW(); -- mysql SELECT SYSDATE + 8/24 FROM DUAL; -- mysql SELECT YEAR(NOW()); -- mysql SELECT YEAR(NOW()); SELECT QUARTER(NOW()); SELECT MONTH(NOW()); SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL; Quarter - oCopy the code

In addition, here is the format of the time identifier used by xiaobian

Mysql > select * from '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"Copy the code

8. When judgingOn the left right The field typeMust beThe same

Note here is a must, may be different in the case of oracle version, the old version of different types will also be queried, but it is recommended to change the same type association, to avoid future database version upgrade problems!!

You are advised to convert a small number to a large number, for example, to a string. And use CONCAT to change the type, because both mysql and Oracle support this function and there are no problems with special types

SELECT a.*,b.* FROM table 1 a LEFT JOIN table 2 b on a String type field = CONCAT(b. Numeric type field, "")Copy the code

9. Batch insert

-- mysql <insert id="insertBatch" databaseId="mysql"> `) VALUES <foreach collection="list" item="item" separator="," open="(" close=")"> #{item. Field 1},#{item. Field 2}, every field name in #{item... } </foreach> </insert> -- oracle <insert ID ="insertBatch" databaseId="oracle"> SELECT A.* FROM( <foreach collection="list" item="item" index="index" separator="UNION ALL" > SELECT #{item. Field 1},#{item. Field 2}, every field name in #{item... } FROM DUAL </foreach> ) A </insert>Copy the code

10, groupingGROUP BY

SQL > select * from GROUP BY; select * from GROUP BY;

Solution:

1. Query fields are changed to aggregate functions
2. Use the followingOVER (Partition BY...) And the windowing function

Mysql > SELECT * from mysqld; -- Oracle SELECT * FROM (SELECT TB.*, ROW_NUMBER () OVER (PARTITION BY TB). ORDER BY TB. DESC) AS result FROM (SELECT name, XXX... Select 'GROUP BY' FROM 'select * FROM' select * FROM 'select * FROM' select * FROM '  ) tb ) WHERE result = 1Copy the code

SQL > alter table alias not availableAS, column aliases can be usedAS

why ? : to prevent conflicts with the Oracle stored procedure keyword AS

Note that Oracle is very strict about data types.

The difference between MySQL and Oracle is far more than the above mentioned points, more also need to be compared according to the actual project, and then to modify oh ~

MySQL to Oracle statement tool source code

Tips: If xiaobian is free later, the test replacement tool class provided above will be modified, and it will be stored in the following Github repository…

Github.com/zhengqingya…