ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Java 调用Oracle存储过程，动态执行参数化Sql语句

Java 调用Oracle存储过程，动态执行参数化Sql语句

原创 Oracle 作者：jdk159 时间：2015-07-06 13:11:32 0 删除 编辑
Java 调用Oracle存储过程，动态执行参数化Sql语句。
说明：Java 调用存储过程，传递一个任意参数化的Sql语句，和相应的参数列表，执行返回结果集。
思考：存储过程返回结果集一般用游标（cursor），执行语句一般未：open cur for Sql using parameter1，parameter1，....，parameterN；using后跟参数列表。但是这样有个局限，using 后的参数列表个数是确定的，只能手动填写，不能用循环。此时需要用过程中的另外一个包，dbms_sql。
具体实现步骤如下：
需要Jar 包：ojdbc6.jar、orai18n.jar 、druid-1.0.9.jar
1. 创建数组类型。
create or replace type varchar2_array as varray(50000) of varchar2(5000);

2. 创建存储过程
create or replace procedure list_sql(p_query in varchar2,sqlv_para in varchar2_array,rs out varchar2_array) is
  l_columnValue varchar2(4000); --列值
  l_status      integer; --状态
  l_colCnt      number default 0; --列的个数
  l_cnt         number default 0;--列索引
  l_line        long; --行值
  l_descTbl     dbms_sql.desc_tab; --table对象
  l_theCursor integer default dbms_sql.open_cursor; --游标编号
  rec_tab dbms_sql.desc_tab;
begin
  dbms_sql.parse(l_theCursor, p_query, dbms_sql.native); --解析
  for i in 1..sqlv_para.count loop   --绑定参数
       dbms_sql.bind_variable(l_theCursor, ':'||i, sqlv_para(i));
  end loop;   
  dbms_sql.describe_columns(l_theCursor, l_colCnt, l_descTbl); 
  rs:=varchar2_array(); --初始化数组
  for i in 1 .. l_colCnt loop
    dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000); --定义列
  end loop;
  l_status := dbms_sql.execute(l_theCursor);
  dbms_sql.describe_columns(l_theCursor, l_colCnt, rec_tab);
  while (dbms_sql.fetch_rows(l_theCursor) > 0) loop --遍历光标，取得数据
    rs.extend;
    l_cnt  := l_cnt + 1; --第N行
    l_line := '{'; --拼接数据
    for i in 1 .. l_colCnt loop
      dbms_sql.column_value(l_theCursor, i, l_columnValue);
      l_line:=l_line||rec_tab(i).col_name||':'||l_columnValue;
      if i<>l_colCnt then
        l_line:=l_line||',';
      end if;
    end loop;
    l_line:=l_line||'}';
    rs(l_cnt):=l_line; --将数据放入数组中
   -- dbms_output.put_line(l_line);
  end loop;
  dbms_sql.close_cursor(l_theCursor);
exception
  when others then
    dbms_sql.close_cursor(l_theCursor);
    raise;
end list_sql;

2. 创建Sql参数化组件（类）
    
    package com.util;


import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;


public class SqlUtil {


public static void main(String[] args) {
String sql = "select count(EC001.Area) total from dy2 d join EC001  EC001 on d.代码2=EC001.area " +
"where 1=1  and (EC001.currentprovertystatus='1' or EC001.currentprovertystatus is null) and  1=1 ";
getSqlObject(sql);
}


public static List getSqlObjectByproc(String sql) {
sql = sql + " ";
Character ch = null, chn;
boolean flag, flagn;
int begin, end, r = 1;
LinkedHashMap map = new LinkedHashMap(); // 用于存储参数
LinkedHashMap map_v = new LinkedHashMap(); // 用于存储参数
a: for (int i = 0; i < sql.length(); i++) {
begin = -2;
end = -1;
ch = sql.charAt(i);
flag = isrelationChar(ch);


if (flag) {// 得到关系符
chn = sql.charAt(i + 1); // 关系符的后一位
flagn = isrelationChar(chn); // 关系符的后一位也是关系符，此时就有可能出现
// <>、>=、<=等情况
if (flagn) {
i++;
}
// 此时的 i 就是关系符所在的索引 substring是前开后闭区间
// System.out.println("关系符索引: "+i+" "+sql.charAt(i+1));
a1: for (int j = i + 1; j < sql.length(); j++) {
Character ch_j = sql.charAt(j);
if (!ch_j.toString().trim().isEmpty()) {
begin = j;
break a1;
}
}


Character begin_ch = sql.charAt(begin), end_ch = null;
if (Character.isDigit(begin_ch)) { // 值是已数字开头
a2: for (int j = begin + 1; j < sql.length(); j++) {
Character f_ch = sql.charAt(j);
if (!Character.isDigit(f_ch)) { // 找到非数字字符
end = j - 1;
end_ch = sql.charAt(j - 1);
break a2;
}
}
} else if (begin_ch.toString().equals("'")) {
end = sql.indexOf("'", begin + 1);
end_ch = sql.charAt(end);
i = end + 1;
} else {
continue a;
}
String to_replace = sql.substring(begin, end + 1);
map.put(r, to_replace.replaceAll("'", ""));
map_v.put(r, begin + "," + end);


r++;
} else if (ch.toString().equals("'")) {
begin = i;
end = sql.indexOf("'", begin + 1);
i = end + 1;
String to_replace = sql.substring(begin, end + 1);
map.put(r, to_replace.replaceAll("'", ""));
map_v.put(r, begin + "," + end);
r++;
}
}
int k = 0,j=1;



for (Object o : map.keySet()) {
begin = Integer.parseInt(map_v.get(o).split(",")[0]) - k;
end = Integer.parseInt(map_v.get(o).split(",")[1]) - k;


String sql_left = sql.substring(0, begin);
String sql_right = sql.substring(end + 1);


sql = sql_left + ":"+j + sql_right;
if(j<10){
k = k + (end - begin)-1;
}else if(j<100){
k = k + (end - begin)-2;
}
j++;
}

List list = new ArrayList();
list.add(sql);
list.add(map);
return list;
}

public static List getSqlObject(String sql) {
sql = sql + " ";
Character ch = null, chn;
boolean flag, flagn;
int begin, end, r = 1;
LinkedHashMap map = new LinkedHashMap(); // 用于存储参数
LinkedHashMap map_v = new LinkedHashMap(); // 用于存储参数
a: for (int i = 0; i < sql.length(); i++) {
begin = -2;
end = -1;
ch = sql.charAt(i);
flag = isrelationChar(ch);


if (flag) {// 得到关系符
chn = sql.charAt(i + 1); // 关系符的后一位
flagn = isrelationChar(chn); // 关系符的后一位也是关系符，此时就有可能出现
// <>、>=、<=等情况
if (flagn) {
i++;
}
// 此时的 i 就是关系符所在的索引 substring是前开后闭区间
// System.out.println("关系符索引: "+i+" "+sql.charAt(i+1));
a1: for (int j = i + 1; j < sql.length(); j++) {
Character ch_j = sql.charAt(j);
if (!ch_j.toString().trim().isEmpty()) {
begin = j;
break a1;
}
}


Character begin_ch = sql.charAt(begin), end_ch = null;
if (Character.isDigit(begin_ch)) { // 值是已数字开头
a2: for (int j = begin + 1; j < sql.length(); j++) {
Character f_ch = sql.charAt(j);
if (!Character.isDigit(f_ch)) { // 找到非数字字符
end = j - 1;
end_ch = sql.charAt(j - 1);
break a2;
}
}
} else if (begin_ch.toString().equals("'")) {
end = sql.indexOf("'", begin + 1);
end_ch = sql.charAt(end);
i = end + 1;
} else {
continue a;
}
String to_replace = sql.substring(begin, end + 1);
map.put(r, to_replace.replaceAll("'", ""));
map_v.put(r, begin + "," + end);


r++;
} else if (ch.toString().equals("'")) {
begin = i;
end = sql.indexOf("'", begin + 1);
i = end + 1;
String to_replace = sql.substring(begin, end + 1);
map.put(r, to_replace.replaceAll("'", ""));
map_v.put(r, begin + "," + end);
r++;
}
}
int k = 0;



for (Object o : map.keySet()) {
begin = Integer.parseInt(map_v.get(o).split(",")[0]) - k;
end = Integer.parseInt(map_v.get(o).split(",")[1]) - k;


String sql_left = sql.substring(0, begin);
String sql_right = sql.substring(end + 1);


sql = sql_left + "?" + sql_right;
k = k + (end - begin);
}

List list = new ArrayList();
list.add(sql);
list.add(map);
return list;
}


public static boolean isrelationChar(char c) {
char[] relationchar = { '=', '>', '<' };
for (char d : relationchar) {
if (c == d) {
return true;
}
}
return false;
}





/**
* 过滤字符串里的的特殊字符
* 
* @param str
*            要过滤的字符串
* @return 过滤后的字符串
*/
public static String sqlSaft(String str) {
String reg = "(?:')|(?:\\[)|(?:--)|(?:\\+)|(/\\*(?:.|[\\n\\r])*?\\*/)|(\\b(select|update|and|or|delete|insert|trancate|char|into|substr|ascii|declare|exec|count|master|into|drop|execute)\\b)";  


return str.replaceAll(reg, "");
}


}


3.object字符串转化未Map集合
package com.util;


import java.util.HashMap;
import java.util.Map;


public class WchUtil {
public static Map StringToMap(String str){
str=str.replaceAll("\\{|\\}", "");
String[] r_datas=str.split(",");
Map map=new HashMap();

for (String  r_data: r_datas) {
String[] c_datas=r_data.split(":|=");
if(c_datas.length==1){
map.put(c_datas[0].toLowerCase(),null);
}else{
map.put(c_datas[0].toLowerCase(), c_datas[1]);
}
}
return map;
}
}


4.  Java 调用存储过程
public List> queryListAll(String sql_v) {
 ResultSet rs = null;
 CallableStatement st = null;
 Connection con = null;

 HashMap map = null;
 List> list = new ArrayList>();
 try {
con = Connections.getConnection();
List sql_o_l = SqlUtil.getSqlObjectByproc(sql_v);
String sql = sql_o_l.get(0).toString();
Map sql_v_m = (Map) sql_o_l.get(1);
String[] parameValue_array = new String[sql_v_m.size()];
int sqlvm_index = 0;
for (Object o : sql_v_m.keySet()) {
parameValue_array[sqlvm_index] = sql_v_m.get(o);
sqlvm_index++;
}
ArrayDescriptor des = ArrayDescriptor.createDescriptor("varchar2_array".toUpperCase(), con.getMetaData().getConnection());
ARRAY array_to_pass = new ARRAY(des, con.getMetaData().getConnection(), parameValue_array);
st = con.prepareCall("call list_sql(?,?,?)");
st.setString(1, sql); 
st.setArray(2, array_to_pass);
st.registerOutParameter(3, OracleTypes.ARRAY,"varchar2_array".toUpperCase());
st.execute();
ARRAY ary = (ARRAY) st.getArray(3);
Datum[] dt = ary.getOracleArray();

for (int i = 0; i < dt.length; i++) {
map = (HashMap) WchUtil.StringToMap(dt[i]
.toString());
list.add((HashMap) map);
}
} catch (SQLException e) {
     e.printStackTrace();
 } finally {
     ConnDB.cleanUP(rs, st, con);
 }
 return list;
}

注意：Java 中除了要引入ojdbc6.jar 以外，还需要引入：orai18n.jar，否则得到的数据乱码。
代码：Demo.rar 只有代码，没有Jar包

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29166805/viewspace-1724364/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： Hibernate 5 环境搭建注意点
请登录后发表评论 登录
全部评论
jdk159

注册时间：2015-07-06

  • 博文量
    3
  • 访问量
    17139

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593