Java 在 Jdbc 中执行绑定变量的 Sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20604736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql query with bind variables execution in Jdbc
提问by Obulesu Bukkana
I have a sql query like this.
我有一个这样的 sql 查询。
select "DEPT"."DEPTNO" as "DEPTNO1",
"DEPT"."DNAME" as "DNAME1",
"DEPT"."LOC" as "LOC1",
"EMP"."COMM" as "COMM1",
"EMP"."EMPNO" as "EMPNO1",
"EMP"."ENAME" as "ENAME1",
"EMP"."HIREDATE" as "HIREDATE1",
"EMP"."JOB" as "JOB1",
"EMP"."MGR" as "MGR1",
"EMP"."SAL" as "SAL1"
from "EMP" , "DEPT" where "DEPT"."DEPTNO" in (:DeptNo)
//This is the Jdbc code
//这是jdbc代码
Class.forName(DB_DRIVER);
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
Statement statment = dbConnection.createStatement();
result = statment.execute(query);//query is above sql query
When i run above query in Oracle sql developer works perfectly.But when i run it with above jdbc code it is throwing Not all variables bound exception. How to run above query in JDBC
当我在 Oracle sql developer 中运行上面的查询时运行完美。但是当我用上面的 jdbc 代码运行它时，它抛出并非所有变量绑定异常。如何在 JDBC 中运行上述查询
采纳答案by Obulesu Bukkana
Get the query dynamically from the report.
从报告中动态获取查询。
From this query we need to split querystring to get number of bind variables and placing those bind variables in a HashMap.HashMap is like
从这个查询中，我们需要拆分查询字符串以获取绑定变量的数量并将这些绑定变量放在 HashMap.HashMap 中，就像
{DeptName =1, Job =1, DeptNo =1}
From this hashmap,need to replace the query bind variable with ?.For this we need to do like
从这个哈希图中，需要用 替换查询绑定变量?。为此，我们需要这样做
bindkey = entry1.getKey().toString();
String bindkeyreplace =":".concat(bindkey).trim();
String bindkeyreplacestring = "?";
query = query.replace(bindkeyreplace, bindkeyreplacestring);
Then we will get dynamic query coming from the report with ?instead of :bindvariable
然后我们将获得来自报告的动态查询，?而不是:bindvariable
PreparedStatement prestmt = dbConnection.prepareStatement(query);
for (int i = 0; i < bindParamMap.size(); i++) {
prestmt.setInt(i + 1, 0);//Setting default value to check the query is running successfully or not
}
result = prestmt.execute();
If in case, we don't know how many bind variables we get then this approach is running successfully for me.
如果以防万一，我们不知道我们得到了多少绑定变量，那么这种方法对我来说是成功的。
回答by EJ Dogar
Use this syntax,EMP.DNAME as DNAME1. I mean your dot and as must be inside the double quotes.
使用此语法，EMP.DNAME as DNAME1. 我的意思是你的点和必须在双引号内。
回答by thiyaga
The variable DeptNo must be bound to a value before you execute the statement like below.
在执行如下语句之前，变量 DeptNo 必须绑定到一个值。
DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
Statement statment = dbConnection.createStatement();
//Bind deptno to a value
statment.setParameter("DeptNo",5);
result = statment.execute(query);
You must set values for all the variables in your prepared statement othwerise you cannot execute the statement. If you receive the query to execute itself as an input then you should also get the parameters and its values also as input. Something like below
您必须为准备好的语句中的所有变量设置值，否则无法执行该语句。如果您收到查询以作为输入执行自身，那么您还应该将参数及其值也作为输入获取。像下面这样
public <returnType> executeQuery(String queryStr, Map<String,Object> params) {
//Code to create connecitno and statment from queryStr.
//Bind deptno to a value
for(int i=0;i<params.size(),i++) {
//Get entry set from map
statment.setParameter(entryset.getKey(),entryset.getValue());
}
result = statment.execute(query);
//return or work on the result
}
回答by Vijay
replace :deptnoin your query with a ?.
用:deptno?替换您的查询。
and instead of instantiating statement use the following:
而不是实例化语句使用以下内容：
PreparedStatement stmt=con.prepareStatement(query);
stmt.setInt(1,deptno); //1 is for the first question mark
where deptnoholds the value for which you want to execute the query.
wheredeptno保存要为其执行查询的值。
Through PrepredStatement interface we can use parametrized query which is compiled only once and has performance advantage in comparison to the Statement interface.
通过 PrepredStatement 接口，我们可以使用只编译一次的参数化查询，并且与 Statement 接口相比具有性能优势。
回答by Maheswaran Ravisankar
You created a Query with bind variable and you never set it.
您使用绑定变量创建了一个查询，但从未设置过它。
Use OraclePreparedStatementand its method setStringAtName()
用途OraclePreparedStatement及其方法setStringAtName()
statement.setStringAtName("DeptNo","<<your Value>>");
If not OraclePreparedStatement, you can just put it as ?1in your Query string
and use,
如果没有OraclePreparedStatement，你可以把它?1放在你的查询字符串中并使用，
statement.setString(1,"<<your Value>>");
If in case, you don't know how many bind variables you get, you have capture the bind variables in a map and prepare a list and set it accordingly!
如果您不知道获得了多少绑定变量，您可以在映射中捕获绑定变量并准备一个列表并进行相应的设置！
Else your requirement is unachievable!
否则你的要求是无法实现的！
