使用SqlProvider适配不同的数据库和手写SQL最佳实践

default

markdown

#  使用SqlProvider适配不同的数据库和手写SQL最佳实践

## 1. 支持的数据库类型

* Oracle ：OracleProvider
* MySQL：MySqlProvider
* Dameng国产达梦数据库：DamengProvider，达梦数据库兼容oracle，和oracle的用法和语法基本一致
* PostgreSQL ：PostgreSQLProvider

## 2. Oracle 与 MySQL 语法差异对比

### 2.1 分页查询

- **Oracle**：
  ```sql
  SELECT * FROM (
    SELECT a.*, ROWNUM rn
    FROM your_table a
    WHERE condition
    ORDER BY some_column
  )
  WHERE rn BETWEEN start_num AND end_num;
  ```
  
- **MySQL**：
  ```sql
  SELECT * FROM your_table
  WHERE condition
  LIMIT start_num, row_count; -- row_count = end_num - start_num + 1
  ```

### 2.2 字符串连接

- **Oracle**：
  ```sql
  SELECT CONCAT(column1, ' ', column2) FROM your_table;
  ```

- **MySQL**：
  ```sql
  SELECT CONCAT(column1, ' ', column2) FROM your_table;
  ```
  （此处两者语法相同，但请注意在较早版本的 MySQL 中可能使用 `CONCAT_WS` 或 `CONCATENATE` 函数）

### 2.3 自增列（序列）

- **Oracle**：
  创建序列：
  ```sql
  CREATE SEQUENCE seq_name;
  ```
  使用序列：
  ```sql
  INSERT INTO your_table (id, other_columns)
  VALUES (seq_name.NEXTVAL, 'value');
  ```

- **MySQL**：
  在表创建时声明自增列：
  ```sql
  CREATE TABLE your_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    other_columns VARCHAR(255)
  );
  ```

### 2.4 数据类型差异

- **Oracle** 支持 `NUMBER(p,s)` 类型，对应 MySQL 的可能是 `DECIMAL(p,s)` 或者 `INT`、`BIGINT` 等。
  
- **MySQL** 提供了额外的数据类型如 `SET` 和 `ENUM`，而 Oracle 没有直接对应的类型。

- **TEXT长文本类型**：
- **Oracle** 中用于存储大文本数据的主要类型是 `CLOB` (Character Large Object) 和 `NCLOB` (National Character Large Object)。

- **MySQL** 有明确的 `TEXT` 数据类型，包括 `TINYTEXT`, `TEXT`, `MEDIUMTEXT`, 和 `LONGTEXT`，分别对应不同大小的文本数据范围，最大可存储至 2^32 字节的数据。

### 2.5 时间日期函数

- **Oracle**：
  获取当前日期时间：
  ```sql
  SELECT SYSDATE FROM dual;
  ```

- **MySQL**：
  获取当前日期时间：
  ```sql
  SELECT NOW();
  ```

### 2.6 事务处理

- **Oracle** 默认情况下不自动提交事务，需要显式执行 `COMMIT` 或 `ROLLBACK`。

- **MySQL** 默认开启了自动提交模式，可以通过 `SET autocommit=0;` 关闭并手动控制事务。

### 2.7 字符串内引用的分隔符

- **Oracle** 中，可以使用双引号 (`"`) 来定义标识符（如表名、列名），单引号 (`'`) 用于定义字符串常量。
   - **MySQL** 中，可以使用反引号 (`` ` ``)、双引号 (`"`) 或者单引号 (`'`) 来定义标识符，但推荐使用反引号以避免与SQL标准冲突；单引号同样用于定义字符串常量。

### 2.8 Oracle 和 MySQL 在大小写处理方面的差异：

**Oracle：**
- **SQL关键字：** Oracle 不区分大小写，但通常建议大写 SQL 关键字以提高可读性。
- **对象名（如表名、列名、序列名等）：** **在Linux/Unix环境下是区分大小写的,默认都是大写，返回的结果列默认都是大写******。

**MySQL：**
- **SQL关键字：** MySQL 对于SQL关键字也不区分大小写。
- **对象名（如表名、列名、数据库名等）：** MySQL 默认在所有环境下都不区分大小写。
例如：
- 在Oracle中，`SELECT * FROM MyTable` 和 `select * from mytable` 指的是不同的一个表（在Linux/Unix环境）。
- 在MySQL中，`SELECT * FROM MyTable` 和 `select * from mytable` 同样默认视为相同的表名，除非你用反引号定义为 `SELECT * FROM \`MyTable\`;` 与 `select * from \`mytable\`;` 这时它们会被认为是不同的表名。

**总结来说，对于大小写处理，Oracle区分大小写， 默认都是大写，而MySQL则在所有系统上都默认不区分大小写。**

### 2.8 Oracle 表、列等对象名字的长度限制
Oracle数据库 12.2 版本把表、列等对象名字的长度限制，从之前的 30 个字节调整到 128 个字节。以下的测试案例说明 12.1 版本和 12.2 版本的区别。

[2.2 新特性 - 12.2 之后表名和列名可以大于 30 个字符](https://support.oracle.com/knowledge/Oracle%20Database%20Products/2533900_1.html)

## 3. SqlProvider的使用

### 建议我们的代码中写的SQL都是标准的SQL，如果遇到数据库的差异的SQL，可以使用SqlProvider提供的方法来屏蔽不同数据库之间的差异。

- **SqlProvider的使用**：
  ```java
        RelationDBAccessor da = rs.getMeta().getRelationDBAccessor();
        //获取SqlProvider
        SqlProvider sqlProvider = da.getSqlProvider();
        //对关键字添加分隔符：示例： oracle:"identify", sqlserver:[identify], mysql: `identify`
        String joinTable = sqlProvider.quote("tableName");

String sql = "SELECT " + joinTable + "." + id1 + ", " + joinTable + "." + id2 + ", " + table1 + "." + column1 + " FROM " + table1 + " JOIN " + joinTable
            + " ON " + joinTable + "." + id2 + "=" + table1 + ".id" + " AND " + joinTable + "." + id1 + " IN %s ";

//分页查询
        sql = sqlProvider.getPaging(sql, 99999, 0);
        List<Object> params = new ArrayList<Object>(ids);
        //执行sql
        da.executeWithoutAuth(sql, Arrays.asList(params));

// 读取数据，如果是oracle数据库，则所有的列名都自动转成小写（因为oracle默认都是全部大小，不想转小写的话，请调用fetchMap）
        List<Map<String, Object>> results = da.fetchMapAll();
        for (Map<String, Object> rows : searchResults) {
            //TODO
        }
  ```
  
** 示例2：**
 ```java
     
        RelationDBAccessor da = meta.getRelationDBAccessor();
        String searchProperties = DataUtil.VIEW_COLUMNS.stream().map(p -> da.getSqlProvider().quote(p)).collect(Collectors.joining(","));
        da.executeWithoutAuth(String.format(
                " select %s from ui_view_seed where (is_delete is null or is_delete = '0') ",
                searchProperties));
        for (Map<String, Object> objectMap : da.fetchMapAll()) {
            keyMap.put((String) objectMap.get("key"), objectMap);
            if (objectMap.get("model") != null) {
                String viewKey = StringUtils.join(objectMap.get("model"), ".", objectMap.get("type"));
                List<Map<String, Object>> mapList = modelViews.get(viewKey);
                if (mapList == null) {
                    mapList = new ArrayList<>();
                    modelViews.put(viewKey, mapList);
                }
                mapList.add(objectMap);
            }
        }

```

** 示例3：**
 ```java
       RelationDBAccessor dataAccessor = meta.getRelationDBAccessor();
        SqlProvider provider = dataAccessor.getSqlProvider();
        String sql = "INSERT INTO meta_attachment(id, name, size, md5,content_type, bucket, " + provider.quote("key")
                + ", create_date) VALUES(%s, %s, %s, %s, %s, %s, %s, " + provider.getNowUtc() + ")";
        
        dataAccessor.executeWithoutAuth(sql);

```

## 4. SqlProvider接口使用介绍

### 4.1 SqlProvider接口提供的方法

```java
public abstract class SqlProvider {

/**
     * 获取数据库时间
     *
     * @return
     */
    public abstract String getNowUtc();
	
	
    /**
     * 为数据库对象名（如表名、列名、数据库名等）添加分隔符：示例：</br>
     * oracle:"identify", sqlserver:[identify], mysql: `identify`
     *
     * @param identify
     * @return
     */
    public abstract String quote(String identify);

/**
     * 为值添加分隔符，示例：'value'
     * @param identify
     * @return
     */
    public  String quoteValue(String identify){
        return StringUtils.join("'" , identify , "'");
    }

/**
     * 为as后的别名添加分隔符
     *
     * @param alias
     * @return
     */
    public String asQuote(String alias) {
        return quote(alias);
    }

/**
     * AS 关键字,oracle列名可以使用AS,但是表名不能添加AS
     * 建议：oracle 列名和表名都不使用as ; mysql:AS
     *
     * @return
     */
    public abstract String as();

/**
     * 生成分页sql
     *
     * @param sql
     * @param limit 总条数
     * @param offset 偏移量
     * @return
     */
    public abstract String getPaging(String sql, Integer limit, Integer offset);

/**
     * 使用日期字符串函数，插入日期字符串
     * @param value
     * @return
     */
    public String toDate(String value) {
    }

/**
     * 使用日期字符串函数，插入日期时间格式的字符串
     * @param value
     * @return
     */
    public String toDateTime(String value) {
    }

/**
     * 使用日期字符串函数，插入时间戳字符串
     * @param value
     * @return
     */
    public String toTimestamp(String value) {
        return String.format("'%s'", value);
    }
    
    /**
     * 插入boolean值,存储在数据库的字段为字符串 0和1，true:'1';false:'0'
     * @param value
     * @return
     */
    public String toBoolean(Boolean value) {
        if (value) {
            return String.format("'%s'", 1);
        } else {
            return String.format("'%s'", 0);
        }
    }
	
	
	  
    /**
     * 插入text类型：oracle varchar2最大字节数都是4000，超过4000的话就需要使用clob存储。
     * 如果属性中是text类型，插入的时候必须使用toText转换成对应数据库的类型
     * 转成text类型
     * @param value
     * @return
     */
    public abstract String toText(String value);

/**
      *Oracle toText实现
     * Oracle的sql语句单引号内部的字节长度不能大于4000
     * 把传入的body字符串，按宽度splitLength，拆分成多条
     * 并用to_clob拼接:
     * https://stackoverflow.com/questions/66974557/oracle-clob-data-type-giving-an-error-sql-error-ora-01704-string-literal-too
     */
	@Override
	public  String toText(String content){
		int maxLen = 1024;
		int count = Math.floorDiv(content.length(), maxLen) + (content.length() % maxLen == 0 ? 0 : 1);
		String toClob = IntStream.range(0, count).mapToObj(i -> {
			String subStr = content.substring(i * 1024, Math.min((i + 1) * maxLen, content.length()));
			subStr = StringUtils.replace(subStr, "'", "''");
			return "to_clob('" + subStr + "')";
		}).collect(Collectors.joining("||"));
		return toClob;
	}

/**
     * 在 Oracle 和 MySQL 中，转义字符主要用于处理字符串中的特殊字符或通配符。在 SQL 语句中，常见的转义字符是反斜杠 \。
     * @param content
     * @return
     */
    @Override
	public  String escapeText(String content){
		String replace = StringUtils.replace(content.toString(), "\\", "\\\\");
		replace = replace.replace("'","\\'");
		return replace;
	}
 
}
  ```
  
  
## 5 手写SQL最佳实践：
建议我们的代码中写的SQL都是标准的SQL，不要使用不兼容的函数，如果遇到数据库的差异的SQL，可以使用SqlProvider提供的方法来屏蔽数据库之间的差异。**
- **对一些数据库的关键字，对象名（如表名、列名、序列名等），使用`quote`包裹** 。
- **Oracle 列名和表名都不使用关键字AS,直接空着不写就行; MySql可以使用AS**。
- **对于TXET长文本类型，必须使用`toText`方法进行转换 **。
- **分页使用`getPaging`**。
- **插入boolean属性使用`toBoolean`**，存储在数据库的字段为字符串 0和1，`true:'1'; false:'0'`，因为oracle没有boolean数据类型，所以统一成字符串类型。
- **插入日期格式的字符串时候，使用`toDate`,`toDateTime`,`toTimestamp`**。
- **获取数据库的当前时间使用 `getNowUtc`**。

Uploading file...

Edit message:

Cancel

Editing 使用SqlProvider适配不同的数据库和手写SQL最佳实践

Sidebar