oracle入门（8）——实战：支持可变长参数、多种条件、多个参数排序、分页的存储过程查询组件
【本文介绍】
学了好几天，由于项目需要，忙活了两天，写出了个小组件，不过现在还只能支持单表操作。也没考虑算法上的优化，查询速度要比hibernate只快了一点点，可能是不涉及多表查询的缘故吧，多表的情况下才更快。
经非专业的测试，在有分页的情况下，在300万条数据里面查询的时间保持在0.1秒内。相同查询条件+分页的情况下，hibernate 用时0.3秒内。
不分页的条件下，查出来的数据越多，时间越长，时间长的话，跟hibernate相相比就没什么优势了。
【思路】
我的思路是从java传来”字段名，值，排序字段，升降序，分页“等 几个参数，都是字符串。然后在存储过程中 根据 标识符 切割字符串，最后拼接成一个SQL语句。
但也有不少值得改进的地方：
（1）PL/SQL语法的字符串最多只能传4000个字符，所以多于4000个字符的字符串可能会导致查询失败。
（2）日期的排序只能靠的是字符串的排序，所以数据库的日期 要 varchar类型。这样会引起不通用的问题。
（3）比较的符号要约定好，比如查询条件为包含什么什么，即contains，就要发送instr到数据库去拼接SQL语句，因为PL/SQL语言的instr 就相当于contians。这个问题有待改成常量的形式。具体约定如下：
大于：>
小于：<
大于等于：>=
小于等于：<=
不等于：!=
包含：instr
以什么开始：startWith
以什么结尾：endWith
是否为空：isNull
是否不为空：isNotNull
【第一步：在数据库中建立分割函数】
oracle没有自带的”根据某标识“切割字符串的函数，所以我们要自己建立。
1 /** 2 用pipe函数实现字符串分割 3 **/ 4 CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000); 5 / 6 CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) 7 RETURN ty_str_split PIPELINED 8 IS 9 j INT := 0; 10 i INT := 1; 11 len INT := 0; 12 len1 INT := 0; 13 str VARCHAR2 (4000); 14 BEGIN 15 len := LENGTH (p_str); 16 len1 := LENGTH (p_delimiter); 17 18 WHILE j < len LOOP 19 j := INSTR (p_str, p_delimiter, i); 20 21 IF j = 0 THEN 22 j := len; 23 str := SUBSTR (p_str, i); 24 PIPE ROW (str); 25 IF i >= len THEN 26 EXIT; 27 END IF; 28 ELSE 29 str := SUBSTR (p_str, i, j - i); 30 i := j + len1; 31 PIPE ROW (str); 32 END IF; 33 END LOOP; 34 35 RETURN; 36 END fn_split; 37 /
【第二步：建立游标】
1 create or replace package testpackage as 2 type Test_CURSOR is ref cursor; 3 end testpackage;
【第三步：建立存储过程】
1 CREATE OR REPLACE 2 procedure testc 3 ( 4 p_cursor out testpackage.Test_CURSOR, --游标，返回列表 5 paraReturn out VARCHAR2, --返回的结果 6 paraTableName in VARCHAR2, --数据库名称 7 paraKey in VARCHAR2, --key，字段名 8 paraCondition in VARCHAR2, --condition，条件 9 paraValue in VARCHAR2, --value，值 10 paraAndOr in VARCHAR2, --where连接附，and 还是or 11 paraOrderKey in VARCHAR2, --排序的key 12 paraOrderSort in VARCHAR2, --排序的顺序 13 paraPagesize in NUMBER, --页数 14 paraPageNow in NUMBER --第几页 15 ) 16 is 17 sqlStr VARCHAR2(1000) := 'test'; --拼接的sql语句 18 paraFiledCount NUMBER := 0; --记录字段数 19 paraCount NUMBER := 1; --循环计数 20 paraOrderCount NUMBER := 0; --排序字段计数 21 paraKeySplit ty_str_split; --切割后的 key 22 paraConditionSplit ty_str_split; --切割后的 condition 23 paraValueSplit ty_str_split; --切割后的value 24 pareAndOrSplit ty_str_split; --切割后的连接符 25 paraOrderKeySplit ty_str_split; --切割后的排序KEY 26 paraOrderSortSplit ty_str_split; --切割后的排序顺序 27 paraBegin NUMBER:= (paraPageNow-1)*paraPagesize; 28 paraEnd NUMBER:= paraPageNow*paraPagesize; 29 30 31 begin 32 -- 查询的基本结构 33 --sqlStr := 'select * from (select tt.*,ROWNUM rowno from (select t.* from ' || paraTableName || ' t '; 34 --sqlStr := 'select * from (select t1.*, ROWNUM rn from (select * from ' || paraTableName ; 35 sqlStr := 'select * from ' || paraTableName ; 36 37 -- 分割 38 select fn_split (paraKey,'^') into paraKeySplit from dual; 39 select fn_split (paraCondition,'^') into paraConditionSplit from dual; 40 select fn_split (paraValue,'^') into paraValueSplit from dual; 41 select fn_split (paraAndOr,'^') into pareAndOrSplit from dual; 42 select fn_split (paraOrderKey,'^') into paraOrderKeySplit from dual; 43 select fn_split (paraOrderSort,'^') into paraOrderSortSplit from dual; 44 45 IF paraKey != 'null' THEN 46 sqlStr := sqlStr || ' where '; 47 --key 长度 48 for I in paraKeySplit.first()..paraKeySplit.last() loop 49 paraFiledCount := paraFiledCount + 1; 50 end loop; 51 -- 循环 52 LOOP 53 -- 退出循环的条件 54 EXIT WHEN paraCount > paraFiledCount; 55 56 -- 循环拼接 57 -- 拼接 = ，< ，> , >= , <= ,!= 58 if 59 paraConditionSplit(paraCount) = '=' OR 60 paraConditionSplit(paraCount) = '<' OR 61 paraConditionSplit(paraCount) = '>' OR 62 paraConditionSplit(paraCount) = '>=' OR 63 paraConditionSplit(paraCount) = '<=' OR 64 paraConditionSplit(paraCount) = '!=' 65 THEN 66 sqlStr := sqlStr || paraTableName || '."' || paraKeySplit(paraCount) || '"' || paraConditionSplit(paraCount) || CHR(39) || paraValueSplit(paraCount) || CHR(39); 67 end if; 68 -- 拼接contians 69 if 70 paraConditionSplit(paraCount) = 'instr' THEN 71 sqlStr := sqlStr || 'instr(' || paraTableName || '."' || paraKeySplit(paraCount) || '",' || CHR(39) || paraValueSplit(paraCount) || CHR(39) || ')>0'; 72 end if; 73 -- 拼接 startWith 74 if 75 paraConditionSplit(paraCount) = 'startWith' THEN 76 sqlStr := sqlStr || 'REGEXP_LIKE(' || paraTableName || '."' || paraKeySplit(paraCount) || '",' || CHR(39) || '^' || paraValueSplit(paraCount) || CHR(39) || ')'; 77 end if; 78 -- 拼接 endWith 79 if 80 paraConditionSplit(paraCount) = 'endWith' THEN 81 sqlStr := sqlStr || 'REGEXP_LIKE(' || paraTableName || '."' || paraKeySplit(paraCount) || '",' || CHR(39) || paraValueSplit(paraCount) || '$' || CHR(39) || ')'; 82 end if; 83 -- 拼接 is null 84 if 85 paraConditionSplit(paraCount) = 'isNull' THEN 86 sqlStr := sqlStr || paraTableName || '."' || paraKeySplit(paraCount) || '"' || ' is null'; 87 end if; 88 -- 拼接is not NULL 89 if 90 paraConditionSplit(paraCount) = 'isNotNull' THEN 91 sqlStr := sqlStr || paraTableName || '."' || paraKeySplit(paraCount) || '"' || ' is not null'; 92 end if; 93 -- 拼接and 或者 or 94 IF paraCount != paraFiledCount THEN 95 sqlStr := sqlStr || ' ' || pareAndOrSplit(paraCount+1) || ' '; 96 end IF; 97 -- 计数增长 98 paraCount := paraCount + 1; 99 100 end LOOP; 101 end if; 102 103 104 105 --排序 106 IF paraOrderKey != 'null' THEN 107 -- 排序字段 长度 108 for I in paraOrderKeySplit.first()..paraOrderKeySplit.last() loop 109 paraOrderCount := paraOrderCount + 1; 110 end loop; 111 paraCount := 1; 112 sqlStr := sqlStr || ' order by '; 113 --循环 114 LOOP 115 -- 退出循环的条件 116 EXIT WHEN paraCount > paraOrderCount; 117 sqlStr := sqlStr || ' ' || paraOrderKeySplit(paraCount) || ' ' || paraOrderSortSplit(paraCount); 118 IF paraCount != paraOrderCount THEN 119 sqlStr := sqlStr || ' , '; 120 END IF; 121 paraCount := paraCount + 1; 122 END LOOP; 123 END IF; 124 125 -- 分页 126 --sqlStr := sqlStr || ')t1 where ROWNUM <=' || paraEnd || ') table_alias where table_alias.rowno >=' || paraBegin; 127 --sqlStr := sqlStr || ')t1 where ROWNUM <=' || paraEnd || ') where rn >=' || paraBegin; 128 sqlStr := 'SELECT * FROM (SELECT a.*, ROWNUM rn FROM ('||sqlStr||') a WHERE ROWNUM <= ' || paraEnd || ') WHERE rn >= ' || paraBegin; 129 130 -- 记录下sql语句，返回去，以便调试 131 paraReturn := sqlStr; 132 133 134 -- 查询 135 open p_cursor for sqlStr; 136 137 -- 异常 138 EXCEPTION 139 WHEN no_data_found THEN 140 DBMS_OUTPUT.PUT_LINE('找不到数据'); 141 paraReturn := '找不到数据'; 142 end testc;
【java通用类的封装】
1 package com.topview.util; 2 3 import java.lang.reflect.Method; 4 import java.sql.CallableStatement; 5 import java.sql.Connection; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.sql.Statement; 9 import java.sql.Types; 10 import java.util.ArrayList; 11 import java.util.List; 12 13 public class FindByProcedure { 14 15 private static Connection conn = null; 16 private static Statement stmt = null; 17 private static ResultSet rs = null; 18 private static CallableStatement proc = null; 19 20 private static int pre;// 查询起始时间 21 private static int post;// 查询结束时间 22 23 private static String sql; // 查询的sql语句 24 25 public static String getSql() { 26 return sql; 27 } 28 29 public static void setSql(String sql) { 30 FindByProcedure.sql = sql; 31 } 32 33 public static Connection getConn() { 34 return conn; 35 } 36 37 /** 38 * 连接由调用者提供。 39 * @param conn 40 */ 41 public static void setConn(Connection conn) { 42 FindByProcedure.conn = conn; 43 } 44 45 public void before() { 46 try { 47 stmt = conn.createStatement(); 48 } catch (Exception e) { 49 e.printStackTrace(); 50 try { 51 throw new MyException("没有传conn进来。"); 52 } catch (Exception e2) { 53 e2.printStackTrace(); 54 } 55 } 56 } 57 58 public void after() { 59 try { 60 if (conn != null) { 61 conn.close(); 62 } 63 } catch (Exception e) { 64 e.printStackTrace(); 65 } 66 try { 67 if (stmt != null) { 68 stmt.close(); 69 } 70 } catch (Exception e) { 71 e.printStackTrace(); 72 } 73 try { 74 if (rs != null) { 75 rs.close(); 76 } 77 } catch (Exception e) { 78 e.printStackTrace(); 79 } 80 try { 81 if(proc != null) { 82 proc.close(); 83 } 84 } catch (Exception e) { 85 e.printStackTrace(); 86 } 87 } 88 89 /** 90 * 91 * @param tableName 要查询的表名，假如数据库有一张myUser表，则 tableName = user 92 * @param keyList 要查询的字段集合，如["name","address"] 93 * @param conditionList 要查询的逻辑集合，如[">",">="] 94 * @param valueList 要查询的值集合，如["小铭","广工"] 95 * @param andOrList 两个查询中间的连接符，如["and","or"] 96 * @param orderList 排序的字段集合，如["age","name"] 97 * @param orderSortList 排序的顺序集合，如["asc","desc"] 98 * @param pageSize 每页显示的数量，如 10 99 * @param pageNumber 第几页， 如1 100 * *@param clazz 实体类的Class 101 * @return 该实体类的list 102 */ 103 @SuppressWarnings("unchecked") 104 public <T> List<T> findByPropertList(String tableName,List<String> keyList,List<String> conditionList,List<String> valueList,List<String> andOrList,List<String> orderList,List<String> orderSortList,Integer pageSize,Integer pageNumber,Class<T> clazz) { 105 106 // 表名为空时抛异常。 107 if(tableName == null || "".equals(tableName)) { 108 try { 109 throw new MyException("传进来的tableName为空！"); 110 } catch (Exception e) { 111 e.printStackTrace(); 112 } 113 } 114 // 类型为空时抛异常 115 if(tableName == null || "".equals(tableName)) { 116 try { 117 throw new MyException("传进来的tableName为空！"); 118 } catch (Exception e) { 119 e.printStackTrace(); 120 } 121 } 122 123 before(); 124 125 pre = (int) System.currentTimeMillis(); 126 127 StringBuilder keyListBuilder = new StringBuilder(); 128 StringBuilder conditionListBuilder = new StringBuilder(); 129 StringBuilder valueListBuilder = new StringBuilder(); 130 StringBuilder andOrListBuilder = new StringBuilder(); 131 StringBuilder orderListBuilder = new StringBuilder(); 132 StringBuilder orderSortListBuilder = new StringBuilder(); 133 134 String keyListStr = ""; 135 String conditionListStr = ""; 136 String valueListStr = ""; 137 String andOrListStr = ""; 138 String orderSortListStr = ""; 139 String orderSortSortListStr = ""; 140 141 List<T> ObjectList = new ArrayList<T>(); 142 143 // 如果不排序 144 if(orderList == null || "".equals(orderList) || orderList.isEmpty()) { 145 146 if(orderList == null) { 147 orderList = new ArrayList<String>(); 148 } 149 if(orderSortList == null){ 150 orderSortList = new ArrayList<String>(); 151 } 152 orderList.add("null"); 153 orderSortList.add("null"); 154 } 155 else { 156 for(int i = 0 ; i < orderList.size(); i++) { 157 orderListBuilder.append(orderList.get(i)).append("^"); 158 orderSortListBuilder.append(orderSortList.get(i)).append("^"); 159 } 160 orderSortListStr = orderListBuilder.substring(0, orderListBuilder.length()-1); 161 orderSortSortListStr = orderSortListBuilder.substring(0, orderSortListBuilder.length()-1); 162 } 163 // 如果不分页 164 if(pageSize == null){ 165 pageSize = new Integer(10); 166 } 167 // 如果没key 168 if(keyList == null || "".equals(keyList) || keyList.isEmpty()) { 169 keyList.add("null"); 170 conditionList.add("null"); 171 valueList.add("null"); 172 andOrList.add("null"); 173 } 174 175 else { 176 for(int i = 0 ; i < keyList.size() ; i ++) { 177 keyListBuilder.append(keyList.get(i)).append("^"); 178 conditionListBuilder.append(conditionList.get(i)).append("^"); 179 valueListBuilder.append(valueList.get(i)).append("^"); 180 andOrListBuilder.append(andOrList.get(i)).append("^"); 181 182 } 183 keyListStr = keyListBuilder.substring(0, keyListBuilder.length()-1); 184 conditionListStr = conditionListBuilder.substring(0, conditionListBuilder.length()-1); 185 valueListStr = valueListBuilder.substring(0, valueListBuilder.length()-1); 186 andOrListStr = andOrListBuilder.substring(0, andOrListBuilder.length()-1); 187 } 188 189 // 和数据库连接 190 try { 191 proc = conn.prepareCall("{ call testc(?,?,?,?,?,?,?,?,?,?,?) }"); 192 193 proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); 194 proc.registerOutParameter(2, Types.VARCHAR); 195 proc.setString(3,tableName); 196 proc.setString(4, keyListStr); 197 proc.setString(5,conditionListStr); 198 proc.setString(6,valueListStr); 199 proc.setString(7,andOrListStr); 200 proc.setString(8,orderSortListStr); 201 proc.setString(9,orderSortSortListStr); 202 proc.setInt(10, pageSize); 203 proc.setInt(11, pageNumber); 204 205 proc.execute(); 206 String para1 = (String) proc.getString(2); 207 sql = para1; 208 ResultSet rs = (ResultSet) proc.getObject(1); 209 210 // 反射 211 Method[] methods = clazz.getMethods(); 212 List<String> fieldNameList = new ArrayList<String>(); 213 List<Method> setMethodList = new ArrayList<Method>(); 214 215 for(Method m : methods) { 216 if(m.getName().toString().contains("set")) { 217 fieldNameList.add((m.getName().toString().substring(3,m.getName().toString().length())).toLowerCase()); 218 setMethodList.add(m); 219 } 220 } 221 222 // 取返回值 223 while (rs.next()) { 224 try { 225 Object o = clazz.newInstance(); 226 for(int i = 0 ; i < setMethodList.size() ; i ++) { 227 // 通过反射创建对象 228 setMethodList.get(i).invoke(o, rs.getObject(fieldNameList.get(i))); 229 } 230 ObjectList.add((T) o); 231 } catch (Exception e) { 232 e.printStackTrace(); 233 } 234 } 235 proc.close(); 236 237 238 } catch (SQLException e) { 239 e.printStackTrace(); 240 try { 241 throw new MyException("连接存储过程错误。"); 242 } catch (MyException e1) { 243 e1.printStackTrace(); 244 } 245 }finally { 246 after(); 247 } 248 249 post = (int) System.currentTimeMillis(); 250 251 return ObjectList; 252 } 253 254 /** 255 * 得到查询用时 256 * @return 查询所用时间 257 */ 258 public Float getUseTime(){ 259 return (((float)(post - pre))/1000) ; 260 } 261 262 /** 263 * 异常类 264 * @author xbw 265 * 266 */ 267 public class MyException extends Exception { 268 269 private static final long serialVersionUID = 1L; 270 271 //定义无参构造方法 272 public MyException(){ 273 super(); 274 } 275 276 //定义有参数的构造方法 277 public MyException(String msg){ 278 super("MyExcepyion_By_Zjm:"+msg); 279 } 280 } 281 }
【一个调用的test】
1 public static void main(String[] args) { 2 FindByProcedure f = new FindByProcedure(); 3 4 5 String tableName = ""; 6 List<String> keyList = new ArrayList<String>(); 7 List<String> conditionList =new ArrayList<String>();; 8 List<String> valueList =new ArrayList<String>(); 9 List<String> andOrList =new ArrayList<String>(); 10 List<String> orderList =new ArrayList<String>(); 11 List<String> orderSortList =new ArrayList<String>(); 12 tableName = "T_AP_ZA_LYT_GNLK"; 13 14 // key 15 keyList.add("ZA_LYT_LKBH"); 16 keyList.add("ZA_LYT_TH"); 17 keyList.add("ZA_LYT_XM"); 18 keyList.add("ZA_LYT_MZ"); 19 keyList.add("ZA_LYT_CSRQ"); 20 keyList.add("ZA_LYT_RKSJ"); 21 keyList.add("ZA_LYT_RKSJ"); 22 23 // 比较符号 24 conditionList.add("<"); 25 conditionList.add(">="); 26 conditionList.add("instr"); 27 conditionList.add("<="); 28 conditionList.add("startWith"); 29 conditionList.add(">="); 30 conditionList.add("<="); 31 32 // value 33 valueList.add("4500000000000000500049"); 34 valueList.add("4600000000000000203771"); 35 valueList.add("VA"); 36 valueList.add("10"); 37 valueList.add("F"); 38 valueList.add("2014-12-24-08-29-38"); 39 valueList.add("2014-12-24-21-37-22"); 40 41 // 连接符 42 andOrList.add("and"); 43 andOrList.add("and"); 44 andOrList.add("and"); 45 andOrList.add("and"); 46 andOrList.add("and"); 47 andOrList.add("and"); 48 andOrList.add("and"); 49 50 // 排序字段 51 orderList.add("ZA_LYT_XM"); 52 orderList.add("ZA_LYT_XMPY"); 53 54 // 排序顺序 55 orderSortList.add("ASC"); 56 orderSortList.add("DESC"); 57 58 List<T_AP_ZA_LYT_GNLK> list = new ArrayList<T_AP_ZA_LYT_GNLK>(); 59 60 // 连接conn要从外部传进去 61 f.setConn(DBManager.getConnection()); 62 63 // 开始调用 64 list = f.findByPropertList(tableName, keyList, conditionList, valueList, andOrList,orderList,orderSortList,5,1,T_AP_ZA_LYT_GNLK.class); 65 66 for(T_AP_ZA_LYT_GNLK o : list) { 67 System.out.println(o.getZa_lyt_xm()); 68 } 69 70 System.out.println("总共拿出数据量："+list.size()); 71 System.out.println("sql语句："+f.getSql()); 72 System.out.println("查询用时："+f.getUseTime().toString()+"s"); 73 }
