在Jdbc中执行具有绑定变量的SQL查询 [英] Sql query with bind variables execution in Jdbc

问题描述

我有一个这样的SQL查询.

I have a sql query like this.

 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)

//这是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

当我在Oracle sql developer中运行以上查询时，它运行完美.但是，当我在以上jdbc代码中运行它时，抛出的错误不是所有变量都绑定了异常. 如何在JDBC中运行以上查询

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

推荐答案

从报表中动态获取查询.

Get the query dynamically from the report.

从此查询中，我们需要拆分查询字符串以获取绑定变量的数量，并将这些绑定变量放置在HashMap中.HashMap类似于

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

            {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);

然后，我们将使用?而不是:bindvariable

Then we will get dynamic query coming from the report with ? instead of :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.

这篇关于在Jdbc中执行具有绑定变量的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！