EasySQLMAIL技术文档 -EasySQLMAIL新特性：在SQL语句中使用JDBC变量

EasySQLMAIL新特性：在SQL语句中使用JDBC变量

使用快速信息分发工具分发Excel表格记录

前言

1、SQL语句中JDBC变量的写法

2、在SQL语句中使用JDBC变量需要注意的问题

3、普通SQL变量与JDBC变量的用法比较

前言

为了提高SQL语句的运行效率和安全性，从EasySQLMAIL1.0.1212开始提供了JDBC类型的变量。

在语句中使用JDBC类型变量有以下优点：

（1）获得更快的执行速度。使用普通变量时，系统在执行命令时会把SQL语句中的变量文字替换为变量值，每次执行的SQL语句的文本内容可能会不同。使用JDBC变量时，系统把语句中的变量文字替换为“?”(问号，即JDBC变量占位符)，变量值由数据库的JDBC驱动进行处理，可以获得更快的处理速度，同时还可以合理利用数据库的SQL语句缓存，降低数据库的资源消耗。

（2）能够更有效地保证SQL语句的安全性。例如：使用普通变量时，如果变量值的文字中包含了单引号或双引号，系统在进行参数检查时会出现错误，导致任务执行失败。使用JDBC变量可以避免出现此问题。

本文向您介绍如何在EasySQLMAIL使用JDBC变量进行查询。

1、SQL语句中JDBC变量的写法

在SQL语句中的双引号和单引号之外的变量名两端加“[“和“]”，系统会将这个变量作为JDBC变量来处理。例如：

SELECT *
FROM t_stat
WHERE area = #[area]#

系统在运行这个SQL语句时，会将变量文字“#[area]#”替换为“?”,并把变量“area”的值以JDBC变量的形式传递给数据库。替换后的SQL语句如下：

SELECT *
FROM t_stat
WHERE area = ?

实际执行语句时，EasySQLMAIL通过JDBC的setString方法将变量area的值传递给相应数据库的JDBC参数。

2、在SQL语句中使用JDBC变量需要注意的问题

（1） JDBC变量的变量文字(上例中的#[area]#)不能位于双引号和单引号中，例如下面的语句运行时将会出现“找不到变量[area]”的错误：

SELECT *
FROM t_stat
WHERE area = ‘#[area]#’

（2） JDBC变量仅能出现在语句的查询条件中或是为字符赋值的语句中。例如

SELECT *
FROM table
WHERE column1 = #[变量1]# AND column2 = #[变量2]#

或

UPDATE table
SET column1 = #[变量1]#
WHERE column1 = #[变量2]#

JDBC变量不能出现在要查询的表名、字段名或其它对象名称中，例如下的面的写法将会运行出错：

SELECT * FROM t_stat_#[月份]#

上述语句中表名部份的“月份”变量应该使用普通变量来实现，例如：

SELECT * FROM t_stat_#月份#

（3） EasySQLMAIL中的JDBC变量的数据类型统一为VARCHAR类型。在实际使用时，可能需要根据操作的表的数据类型在SQL语句中对JDBC参数的数据类型进行转换，例如：

--说明：本例中PostgreSQL数据库的表t_stat中，month是int类型的字段，传入的jdbc参数”月份”的数据类型是varchar,如果直接查询将会报错“数据类型不一致”，因此需要将”月份”参数转换为int类型的数据
SELECT *
FROM t_stat
WHERE month = #[月份]#::int4

3、普通SQL变量与JDBC变量的用法比较

	普通SQL变量	JDBC变量
传值方式	SQL文本替换为变量值例如 select * from t_stat where area = ‘#area#’ 替换为 select * from t_stat where area = ‘成都’	SQL文本替换为“?”，JDBC驱动传值。例如 select * from t_stat where area = #[area]# 替换为 select * from t_stat where area = ? 并将area变量的值通过setString(1,value)传递给JDBC驱动
一个变量一次包含参数个数	可以包含多个值。例如变量area=1,2,3 语句select * from t_stat where area in (#area#) 执行时被替换为 select * from t_stat where area in (1,2,3)	一次只能包含一个值。语句select * from t_stat where area in (#[area]#) 执行时被替换为 select * from t_stat where area in (?) ，且area必须是一个单一的数字，例如“5”。执行的SQL语句等同于 select * from t_stat where area in (5)
作为对象名的一部份	普通参数可以作为对象名的一部份。例如month=202110时，语句select * from t_stat_#month# 被替换为 select * from t_stat_202110	不可作为对象名的一部份。语句select * from t_stat_#[month]#执行时将会报错。
NULL值的处理	普通参数类型中，如果@@sqldata(字段名)获得的上级语句的字段值是NULL，系统在处理时会将参数值转换为空字符进行处理。例如，上级语句调用SQL命令时传入的“用户ID” 字段的值是NULL，下面的语句： INSERT INTO USER (user_id) VALUES ('#@@sqldata(用户ID)#'); 将被替换为 INSERT INTO USER (user_id) VALUES (''); --#@@sqldata(用户ID)#被替换为空字符串	JDBC参数中，如果@@sqldata(字段名) 获得的上级语句的字段值是NULL，系统会将对应的SQL语句参数的值设置为NULL。例如，上级语句调用SQL命令时传入的“用户ID” 字段的值是NULL，下面的语句： INSERT INTO USER (user_id) VALUES (#[@@sqldata(用户ID)]#); 将被替换为 INSERT INTO USER (user_id) VALUES (?); 调用时，JDBC参数1的值是NULL。
执行效率	较低，在并发数量大时效率问题尤为明显。	高。可利用数据库的SQL缓存。
安全性	较低。系统在执行前会对变量安全性进行检查，变量值中出现非法符号时会中止任务运行。	高。安全性由数据库驱动程序处理。