mysql jdbc 绑定变量,在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)

//This is the Jdbc code

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

解决方案

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

{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

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.