如何写出能适应不同数据库的 SQL 查询

无论是应用开发还是数据库管理,在不同类型数据库,实现其同样功能的 SQL 查询,都会遇到 SQL 写法或多或少不一样的问题。这与每一个数据库都有自己所特有的一些函数和功能有关
不同类型的数据库,虽然 SQL 语法大体相似,但是它们各自的函数或符号却有些不同,有的甚至也不通用。比如将字符串 "2020-02-05" 转换成日期,标准 SQL 为 select DATE('2020-02-05') from USER, 不同数据库有不同的写法:
ORACLE:select TO_DATE('2020-02-05', 'YYYY-MM-DD') from USER;

SQLServer:select CONVERT(varchar(100), '2020-02-05', 23) from USER;
MYSQL:select DATE_FORMAT('2020-02-05','%Y-%m-%d') from USER;
 

如果希望应用在不同的数据库之间切换,就需要改写 SQL 语句,很麻烦。
使用集算器的 sqltranslate 函数,可以自动将 SQL 语句中的函数变换成各数据库方言的写法,这样就方便得多了。

我们以计算员工出生日期位于当年的第几周为例描述实现步骤:

1.    运行集算器,配置数据库连接并命名。填写的参数和 JDBC 连接时一样。

a100png

2.    按集算器支持的标准函数写出这句 SQL:
select EID, NAME, BIRTHDAY, WEEKOFYEAR(BIRTHDAY) WEEKS from EMP

3.    在集算器中编写脚本sqltrans.dfx

      脚本将上面那句 SQL 当作参数。


A

B

1

=sql.sqltranslate("MYSQL")

/将标准 SQL 转换成符合 MYSQL 语法的 SQL

2

=connect("db")

/连接数据库

3

=A2.query@x(A1)

/查询表数据

4.    调试执行一下,可以看到A1的格值为
      select EID, NAME, BIRTHDAY, WEEK(BIRTHDAY) WEEKS from EMP
      已经转换使用 MYSQL 函数的 SQL 语句。

A3格是返回结果:

A3 EID NAME BIRTHDAY WEEKS

1 Rebecca 1974-11-20 46
2 Ashley 1980-07-19 28


如果要换一种数据库,只要在 sqltranslate(dbtype) 中修改参数为数据库名称即可。

集算器支持如下这些数据库类型:

数据库
ORACLE
SQLSVR
DB2
MYSQL
HSQL
TERADATA 
POSTGRES

绝大多数常见函数都已经被定义过了,如果需要,还可以自定义函数,详细信息可查询官网上集算器文档中的sqltranslate 函数帮助。

集算器提供了 JDBC 接口,这段代码很容易集成到 Java 中: public static void testSqltranslate(){>
    Connection con = null;
    java.sql.PreparedStatement st;
    //SQL 语句作为参数
    String sql="select EID, NAME, BIRTHDAY, WEEKOFYEAR(BIRTHDAY) WEEKS from EMP";
    try{
        Class.forName("com.esproc.jdbc.InternalDriver");
        con= DriverManager.getConnection("jdbc:esproc:local://"); 
        // 调用 sqltrans.dfx, 其中参数为 sql
        st =con.prepareCall("sqltrans ?");
        st.setObject(1, sql);
        st.execute();
 
        // 获取结果集
        ResultSet rst = st.getResultSet();
        System.out.println(rst);
    }catch(Exception e){
        System.out.println(e);
    }finally{
        // 关闭连接
        if (con!=null) {
            try {
                con.close();
            }catch(Exception e) {
                System.out.println(e);
            }
        }  
    }
}

集算器与 JAVA 集成的进一步信息可参考:《Java如何调用 SPL脚本》。