【本文介紹】

　　學了好幾天，由于項目需要，忙活了兩天，寫出了個小組件，不過現在還只能支持單表操作。也沒考慮算法上的優化，查詢速度要比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 / View Code

?

【第二步：建立游標】

?

1 create or replace package testpackage as 2 type Test_CURSOR is ref cursor; 3 end testpackage; View Code

?

?

?

【第三步：建立存儲過程】

?

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; View Code

?

?

【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 } View Code

?

?【一個調用的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 } View Code

?

轉載于:https://www.cnblogs.com/xiaoMzjm/p/3885066.html

總結

以上是生活随笔為你收集整理的oracle入门（8）——实战：支持可变长参数、多种条件、多个参数排序、分页的存储过程查询组件...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。