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

1. 支持的数据库类型

平台ORM框架目前支持以下几种数据库，并内置对应的 Provider 实现.以下 Provider 已实现驱动加载、方言解析、分页、事务、批量操作等常用功能，应用层只需切换 dbcp.properties 中的 driverClassName 与 url 即可在各类数据库之间平滑迁移。

数据库配置范例汇总.md

支持的数据库类型

数据库类型	Provider 类名	兼容性说明	参考文档
Oracle	OracleProvider	标准 Oracle 数据库	-
MySQL	MySqlProvider	标准 MySQL 数据库	-
OceanBase	MySqlProvider	兼容 MySQL 语法	OceanBase 数据库使用指南
PostgreSQL	PostgreSQLProvider	标准 PostgreSQL 数据库	PostgreSQL 参考手册
Dameng（达梦）	DamengProvider	兼容 Oracle 语法和用法	达梦数据库（DM8）适配
GaussDB	GaussDBProvider	兼容 MySQL	GaussDB 数据库参考手册
Vastbase G100	VastbaseProvider	兼容 PostgreSQL 和 MySQL 模式	Vastbase G100 参考文档

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。