SQL语句占位符在Creator中的用法

背景

在Creator的命令列表中，“软件自动化”模块下的“数据库”子模块，是对数据库自动化操作，即指在保证数据安全的前提下，直接使用用户名和密码登录数据库，并使用SQL语句对数据库进行操作。

如上图所示，“创建数据库对象”命令，已经支持MySQL、SQL Servrer、Oracle、Sqlite3、PostgreSQL共5种数据库。

如上图所示，SQL语句占位符在Creator中可使用3种(%s、？、:1)——MySQL 、SQLServer、PostgreSQL使用%s，Sqlite3使用?，Oracle使用:1。

既然支持占位符，哪有哪些好处呢？

1. 增加SQL代码可读性
2. 占位符可以预先编译，提高执行效率
3. 防止SQL注入
4. 用占位符的目的是绑定变量，这样可以减少数据SQL的硬解析，所以执行效率会提高不少

好处多多，赶快学习一下UiBot的命令手册:

然而，看着似乎还是比较难用，至少有个example也是比较好啊~

用法

首先来看看Creator中的字符串连接符“&”的用法，这也是UB语言的基础（运算符和表达式）。

UB语言支持“SQL语句占位符”，在不知道或者不明白怎么使用的情况下，通常做法如下：

/*
1.使用基本的字符串连接符“&”，拼装update类型、insert类型的SQL语句
2.以Sqlite3举例
*/

Dim dTime
dTime = Time.Now()
dTime = Time.Format(dTime,"yyyy-mm-dd hh:mm:ss")

Dim unit_price = 0
Dim job_number = ""
Dim employee_name = ""
Dim department = ""

Dim updateSQL = ""
Dim insertSQL = “”

//以Sqlite3为例
objDatabase = Database.CreateDB("Sqlite3",{"filepath":@res"db\\projectUsedTime.db"})

//执行Update
unit_price = 800
job_number = "666"
employee_name = "爱U"
department = "研发部"
updateSQL ="UPDATE employee_laiye SET unit_price="&unit_price&" , update_time= '"&dTime&"'  WHERE job_number='"&job_number&"' AND employee_name='"&employee_name&"' AND  department='"&department&"'"

Database.ExecuteSQL(objDatabase ,updateSQL, {"args":[]})

//执行Insert
unit_price = 700
job_number = "889"
employee_name = "新员工1"
department = "研发部"
insertSQL = "INSERT INTO employee_laiye(job_number,employee_name,department,unit_price,update_time)VALUES('"&job_number&"','"&employee_name&"','"&department&"',"&unit_price&",'"&dTime&"')"
Database.ExecuteSQL(objDatabase ,insertSQL, {"args":[]})

//执行Insert
unit_price = 750
job_number = "890"
employee_name = "新员工2"
department = "设计部"
insertSQL = "INSERT INTO employee_laiye(job_number,employee_name,department,unit_price,update_time)VALUES('"&job_number&"','"&employee_name&"','"&department&"',"&unit_price&",'"&dTime&"')"
Database.ExecuteSQL(objDatabase ,insertSQL, {"args":[]})

//关闭连接
Database.CloseDB(objDatabase)

以上代码最累的地方应该就是变量（“insertSQL”、“insertSQL”）的赋值了吧，单引号、双引号、字符串连接符、其他变量、SQL糅杂在一起，不仅容易手写错误，而且语句太长，更改起来一定要仔细小心，不然就是“自己搬石头砸自己脚”了。另外两次执行“Insert语句”,这部分代码重复度很高（赋值不同而已），要是还要执行“Insert语句”很多次，就得改造成“For循环”了。

继续看看命令手册吧！

//执行SQL语句
iRet = Database.ExecuteSQL(objDatabase ,sql, optionArgs)
/*
可选参数
args--SQL语句参数，SQL语句占位符：MySQL和SQLServer使用%s，Sqlite3使用?，Oracle使用:1。
*/

//批量执行SQL语句
iRet = Database.ExecuteBatchSQL(objDatabase ,sql, optionArgs)
/*
可选参数
args--SQL语句参数，遍历参数的二维数组循环执行SQL语句，SQL语句占位符：MySQL和SQLServer使用%s，Sqlite3使用?，Oracle使用:1。
*/

可选参数“optionArgs”，在这2个命令中传入的值不一样，一个是一维数组，一个二维数组。

现在就来用“SQL语句占位符”来改造上面的Demo吧。

/*
1.使用“SQL语句占位符”，拼装update类型、insert类型的SQL语句
2.以Sqlite3举例
*/

Dim dTime
dTime = Time.Now()
dTime = Time.Format(dTime,"yyyy-mm-dd hh:mm:ss")

Dim unit_price = 0
Dim job_number = ""
Dim employee_name = ""
Dim department = ""

Dim updateSQL = ""
Dim updateArgs = []
Dim insertSQL = ""
Dim insertArgs = []
//以Sqlite3为例
objDatabase = Database.CreateDB("Sqlite3",{"filepath":@res"db\\projectUsedTime.db"})

//执行Update
unit_price = 800
job_number = "666"
employee_name = "爱U"
department = "研发部"

updateArgs = [unit_price,dTime,job_number,employee_name,department]

updateSQL ="UPDATE employee_laiye SET unit_price= ? , update_time= ?  WHERE job_number= ? AND employee_name= ? AND  department= ?"

Database.ExecuteSQL(objDatabase ,updateSQL, {"args":updateArgs})

/*
//执行Insert
unit_price = 700
job_number = "889"
employee_name = "新员工1"
department = "研发部"

insertArgs = [job_number,employee_name,department,unit_price,dTime]

insertSQL = "INSERT INTO employee_laiye(job_number,employee_name,department,unit_price,update_time)VALUES(?,?,?,?,?)"
Database.ExecuteSQL(objDatabase ,insertSQL, {"args":insertArgs})

//执行Insert
unit_price = 750
job_number = "890"
employee_name = "新员工2"
department = "设计部"

insertArgs = [job_number,employee_name,department,unit_price,dTime]

insertSQL = "INSERT INTO employee_laiye(job_number,employee_name,department,unit_price,update_time)VALUES(?,?,?,?,?)"
Database.ExecuteSQL(objDatabase ,insertSQL, {"args":insertArgs})
*/

//批量执行Insert
unit_price = 700
job_number = "889"
employee_name = "新员工1"
department = "研发部"
push(insertArgs,[job_number,employee_name,department,unit_price,dTime])

unit_price = 750
job_number = "890"
employee_name = "新员工2"
department = "设计部"
push(insertArgs,[job_number,employee_name,department,unit_price,dTime])

insertSQL = "INSERT INTO employee_laiye(job_number,employee_name,department,unit_price,update_time)VALUES(?,?,?,?,?)"

Database.ExecuteBatchSQL(objDatabase ,insertSQL, {"args":insertArgs}) //传入二维数据


//关闭连接
Database.CloseDB(objDatabase)

以上以Sqlite3举例，使用了占位符“?”，其他2种占位符的使用方法，只需根据支持的数据库类型进行调整即可——MySQL 、SQLServer、PostgreSQL使用%s，Sqlite3使用?，Oracle使用:1。