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

1. 支持的数据库类型

• Oracle ：OracleProvider
• MySQL：MySqlProvider
• OceanBase ：MySqlProvider OceanBase 数据库使用指南
• PostgreSQL ：PostgreSQLProvider PostgreSQL 参考手册
• Dameng国产达梦数据库：DamengProvider，达梦数据库兼容oracle，和oracle的用法和语法基本一致

2. Oracle 与 MySQL 语法差异对比

2.1 分页查询

• Oracle：

  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：

  SELECT * FROM your_table
  WHERE condition
  LIMIT start_num, row_count; -- row_count = end_num - start_num + 1

2.2 字符串连接

• Oracle：

  SELECT CONCAT(column1, ' ', column2) FROM your_table;

• MySQL：

  SELECT CONCAT(column1, ' ', column2) FROM your_table;

  （此处两者语法相同，但请注意在较早版本的 MySQL 中可能使用 CONCAT_WS 或 CONCATENATE 函数）

2.3 自增列（序列）

• Oracle： 创建序列：

  CREATE SEQUENCE seq_name;

  使用序列：

  INSERT INTO your_table (id, other_columns)
  VALUES (seq_name.NEXTVAL, 'value');

• MySQL： 在表创建时声明自增列：

  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： 获取当前日期时间：

  SELECT SYSDATE FROM dual;

• MySQL： 获取当前日期时间：

  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则在所有系统上都默认不区分大小写。

3. SqlProvider的使用

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

• SqlProvider的使用：

      RelationDBAccessor da = rs.getMeta().getRelationDBAccessor();
      //获取SqlProvider
      SqlProvider sqlProvider = da.getSqlProvider();
      //对关键字添加分隔符：示例： oracle:"identify", sqlserver:[identify], mysql: `identify`
      String joinTable = sqlProvider.quote((String)property.getAttr(MetaConstant.TABLE_NAME));
      String table1 = sqlProvider.quote((String)property.getAttr(MetaConstant.RELATE_TABLE));
      String id1 = sqlProvider.quote((String)property.getAttr(MetaConstant.INVERSE_JOIN_COLUMN));
      String id2 = sqlProvider.quote((String)property.getAttr(MetaConstant.COLUMN_NAME));
      String column1 = sqlProvider.quote((String)displayProperty.getColumnName());
  
      String sql = "SELECT " + joinTable + "." + id1 + ", " + joinTable + "." + id2 + ", " + table1 + "." + column1 + " FROM " + table1 + " JOIN " + joinTable
          + " ON " + joinTable + "." + id2 + "=" + table1 + ".id" + " AND " + joinTable + "." + id1 + " IN %s ";
  
      if (!useDisplayForModel) {
          sql = "SELECT " + joinTable + "." + id1 + ", " + joinTable + "." + id2 + " FROM " + joinTable + " WHERE " + 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
      }

4. SqlProvider接口使用介绍

4.1 SqlProvider接口提供的方法

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。