如何将变量插入到SQL查询中

huangapple 未分类评论56阅读模式
标题翻译

how to insert variable into SQL query

问题

我有测试脚本,productId存储在文本文件中,我可以使用read方法检索它。

import static org.assertj.core.api.Assertions.*
import groovy.json.JsonOutput as JsonOutput
import groovy.json.JsonSlurper as JsonSlurper
import internal.GlobalVariable as GlobalVariable
import internal.GlobalVariable
import com.kms.katalon.core.configuration.RunConfiguration
import java.sql.*
import java.io.File

//读取productId
def productId = new File(RunConfiguration.getProjectDir() + "/Data Files/productId.txt")

//SQL语句
String dbQuery2 = "SELECT * FROM db.table1 where productId = @productId order by lastmodificationdateutc desc"

如何将“@”用作变量并将其替换为“productId.text”?

我尝试过以下代码,但不起作用,请纠正我,如果可以使用此方法?

String dbQuery2 = "SELECT * FROM db.table1 where productId = ?"
def productId = new File(RunConfiguration.getProjectDir() + "/Data Files/productId.txt")
PreparedStatement stmt = con.prepareStatement(dbQuery2)
stmt.setString(1, productId.text)
ResultSet resultSet = preparedStatement.executeQuery()

ResultSet resultSet = stmt.executeQuery()

//连接到PostgresSQL,全局变量存储在配置文件中
List resultSet = CustomKeywords.'test.database.getPostgresSQLResults'(GlobalVariable.dbConnString2, GlobalVariable.dbUsername2, GlobalVariable.dbPassword2, GlobalVariable.dbDriver2, dbQuery2)

以下是可正常工作的不使用预处理语句的代码。

脚本中的连接字符串:

//连接到PostgresSQL服务器
@Keyword
def List getPostgresSQLResults(String dbConnString2, String dbUsername2, String dbPassword2, String dbDriver2, String dbQuery2) {

    Sql sql = Sql.newInstance(dbConnString2, dbUsername2, dbPassword2, dbDriver2)

    List results = sql.rows(dbQuery2)

    return results

    sql.close()
}
英文翻译

i have test scripts, the productId is stored in text file which I can use the read method to retrieve it.

import static org.assertj.core.api.Assertions.*
import groovy.json.JsonOutput as JsonOutput
import groovy.json.JsonSlurper as JsonSlurper
import internal.GlobalVariable as GlobalVariable
import internal.GlobalVariable
import com.kms.katalon.core.configuration.RunConfiguration
import java.sql.*
import java.io.File

//Read productId
def productId = new File(RunConfiguration.getProjectDir() + "/Data Files/productId.txt")

//SQL statement
String dbQuery2 = /SELECT * FROM db.table1 where productId= @productId order by lastmodificationdateutc desc/

how do i use the @ as variable and replace it with the "productId.text" ?

i have tried below codes but it does not work
please correct me if this method can be used?

    String dbQuery2 = "SELECT * FROM db.table1 where productId = ?"
    def productId = new File(RunConfiguration.getProjectDir() + "/Data Files/productId.txt")
    PreparedStatement stmt = con.prepareStatement(dbQuery2)
    stmt.setString(1, productId.text)
    ResultSet resultSet = preparedStatement.executeQuery()

ResultSet resultSet = stmt.executeQuery()

//Connect to PostgresSQL, global variable is stored at profile
List resultSet = CustomKeywords.'test.database.getPostgresSQLResults'(GlobalVariable.dbConnString2 , GlobalVariable.dbUsername2 , GlobalVariable.dbPassword2 ,GlobalVariable.dbDriver2 ,dbQuery2 )

below is codes which is working without preparedstatement

the connection string in the script

//to connect PostgresSQL server
	@Keyword
	def List getPostgresSQLResults (String dbConnString2 , String dbUsername2 , String dbPassword2 , String dbDriver2 , String dbQuery2){

		Sql sql = Sql.newInstance(dbConnString2,dbUsername2,dbPassword2,dbDriver2)

		List results = sql.rows(dbQuery2)

		return results

		sql.close()
	}

答案1

得分: 0

我成功地使用以下语法作为变量:

def productId = new File(RunConfiguration.getProjectDir() + "/Data Files/productId.txt")
dbQuery2 = /SELECT * FROM db.table1 where productId = '$productId.text'/
英文翻译

I managed to use this syntax to act as variable

def productId = new File(RunConfiguration.getProjectDir() + "/Data Files/productId.txt")
dbQuery2 = /SELECT * FROM db.table1 where productId = '$productId.text}'/

huangapple
  • 本文由 发表于 2020年3月16日 14:22:33
  • 转载请务必保留本文链接:https://java.coder-hub.com/60701212.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定