【本文介绍】
学了好几天，由于项目需要，忙活了两天，写出了个小组件，不过现在还只能支持单表操作。也没考虑算法上的优化，查询速度要比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没有自带的”根据某标识“切割字符串的函数，所以我们要自己建立。
/** 用pipe函数实现字符串分割 **/ CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000); / CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split PIPELINED IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); BEGIN len := LENGTH (p_str); len1 := LENGTH (p_delimiter); WHILE j < len LOOP j := INSTR (p_str, p_delimiter, i); IF j = 0 THEN j := len; str := SUBSTR (p_str, i); PIPE ROW (str); IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; PIPE ROW (str); END IF; END LOOP; RETURN; END fn_split; /
【第二步：建立存储过程】
CREATE OR REPLACE procedure testc ( p_cursor out testpackage.Test_CURSOR, --游标，返回列表 paraReturn out VARCHAR2, --返回的结果 paraTableName in VARCHAR2, --数据库名称 paraKey in VARCHAR2, --key，字段名 paraCondition in VARCHAR2, --condition，条件 paraValue in VARCHAR2, --value，值 paraAndOr in VARCHAR2, --where连接附，and 还是or paraOrderKey in VARCHAR2, --排序的key paraOrderSort in VARCHAR2, --排序的顺序 paraPagesize in NUMBER, --页数 paraPageNow in NUMBER --第几页 ) is sqlStr VARCHAR2(1000) := ‘test‘; --拼接的sql语句 paraFiledCount NUMBER := 0; --记录字段数 paraCount NUMBER := 1; --循环计数 paraOrderCount NUMBER := 0; --排序字段计数 paraKeySplit ty_str_split; --切割后的 key paraConditionSplit ty_str_split; --切割后的 condition paraValueSplit ty_str_split; --切割后的value pareAndOrSplit ty_str_split; --切割后的连接符 paraOrderKeySplit ty_str_split; --切割后的排序KEY paraOrderSortSplit ty_str_split; --切割后的排序顺序 paraBegin NUMBER:= (paraPageNow-1)*paraPagesize; paraEnd NUMBER:= paraPageNow*paraPagesize; begin -- 查询的基本结构 --sqlStr := ‘select * from (select tt.*,ROWNUM rowno from (select t.* from ‘ || paraTableName || ‘ t ‘; --sqlStr := ‘select * from (select t1.*, ROWNUM rn from (select * from ‘ || paraTableName ; sqlStr := ‘select * from ‘ || paraTableName ; -- 分割 select fn_split (paraKey,‘^‘) into paraKeySplit from dual; select fn_split (paraCondition,‘^‘) into paraConditionSplit from dual; select fn_split (paraValue,‘^‘) into paraValueSplit from dual; select fn_split (paraAndOr,‘^‘) into pareAndOrSplit from dual; select fn_split (paraOrderKey,‘^‘) into paraOrderKeySplit from dual; select fn_split (paraOrderSort,‘^‘) into paraOrderSortSplit from dual; IF paraKey != ‘null‘ THEN sqlStr := sqlStr || ‘ where ‘; --key 长度 for I in paraKeySplit.first()..paraKeySplit.last() loop paraFiledCount := paraFiledCount + 1; end loop; -- 循环 LOOP -- 退出循环的条件 EXIT WHEN paraCount > paraFiledCount; -- 循环拼接 -- 拼接 = ，< ，> , >= , <= ,!= if paraConditionSplit(paraCount) = ‘=‘ OR paraConditionSplit(paraCount) = ‘<‘ OR paraConditionSplit(paraCount) = ‘>‘ OR paraConditionSplit(paraCount) = ‘>=‘ OR paraConditionSplit(paraCount) = ‘<=‘ OR paraConditionSplit(paraCount) = ‘!=‘ THEN sqlStr := sqlStr || paraTableName || ‘."‘ || paraKeySplit(paraCount) || ‘"‘ || paraConditionSplit(paraCount) || CHR(39) || paraValueSplit(paraCount) || CHR(39); end if; -- 拼接contians if paraConditionSplit(paraCount) = ‘instr‘ THEN sqlStr := sqlStr || ‘instr(‘ || paraTableName || ‘."‘ || paraKeySplit(paraCount) || ‘",‘ || CHR(39) || paraValueSplit(paraCount) || CHR(39) || ‘)>0‘; end if; -- 拼接 startWith if paraConditionSplit(paraCount) = ‘startWith‘ THEN sqlStr := sqlStr || ‘REGEXP_LIKE(‘ || paraTableName || ‘."‘ || paraKeySplit(paraCount) || ‘",‘ || CHR(39) || ‘^‘ || paraValueSplit(paraCount) || CHR(39) || ‘)‘; end if; -- 拼接 endWith if paraConditionSplit(paraCount) = ‘endWith‘ THEN sqlStr := sqlStr || ‘REGEXP_LIKE(‘ || paraTableName || ‘."‘ || paraKeySplit(paraCount) || ‘",‘ || CHR(39) || paraValueSplit(paraCount) || ‘$‘ || CHR(39) || ‘)‘; end if; -- 拼接 is null if paraConditionSplit(paraCount) = ‘isNull‘ THEN sqlStr := sqlStr || paraTableName || ‘."‘ || paraKeySplit(paraCount) || ‘"‘ || ‘ is null‘; end if; -- 拼接is not NULL if paraConditionSplit(paraCount) = ‘isNotNull‘ THEN sqlStr := sqlStr || paraTableName || ‘."‘ || paraKeySplit(paraCount) || ‘"‘ || ‘ is not null‘; end if; -- 拼接and 或者 or IF paraCount != paraFiledCount THEN sqlStr := sqlStr || ‘ ‘ || pareAndOrSplit(paraCount+1) || ‘ ‘; end IF; -- 计数增长 paraCount := paraCount + 1; end LOOP; end if; --排序 IF paraOrderKey != ‘null‘ THEN -- 排序字段 长度 for I in paraOrderKeySplit.first()..paraOrderKeySplit.last() loop paraOrderCount := paraOrderCount + 1; end loop; paraCount := 1; sqlStr := sqlStr || ‘ order by ‘; --循环 LOOP -- 退出循环的条件 EXIT WHEN paraCount > paraOrderCount; sqlStr := sqlStr || ‘ ‘ || paraOrderKeySplit(paraCount) || ‘ ‘ || paraOrderSortSplit(paraCount); IF paraCount != paraOrderCount THEN sqlStr := sqlStr || ‘ , ‘; END IF; paraCount := paraCount + 1; END LOOP; END IF; -- 分页 --sqlStr := sqlStr || ‘)t1 where ROWNUM <=‘ || paraEnd || ‘) table_alias where table_alias.rowno >=‘ || paraBegin; --sqlStr := sqlStr || ‘)t1 where ROWNUM <=‘ || paraEnd || ‘) where rn >=‘ || paraBegin; sqlStr := ‘SELECT * FROM (SELECT a.*, ROWNUM rn FROM (‘||sqlStr||‘) a WHERE ROWNUM <= ‘ || paraEnd || ‘) WHERE rn >= ‘ || paraBegin; -- 记录下sql语句，返回去，以便调试 paraReturn := sqlStr; -- 查询 open p_cursor for sqlStr; -- 异常 EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE(‘找不到数据‘); paraReturn := ‘找不到数据‘; end testc;
【java通用类的封装】
package com.topview.util; import java.lang.reflect.Method; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.List; public class FindByProcedure { private static Connection conn = null; private static Statement stmt = null; private static ResultSet rs = null; private static CallableStatement proc = null; private static int pre;// 查询起始时间 private static int post;// 查询结束时间 private static String sql; // 查询的sql语句 public static String getSql() { return sql; } public static void setSql(String sql) { FindByProcedure.sql = sql; } public static Connection getConn() { return conn; } /** * 连接由调用者提供。 * @param conn */ public static void setConn(Connection conn) { FindByProcedure.conn = conn; } public void before() { try { stmt = conn.createStatement(); } catch (Exception e) { e.printStackTrace(); try { throw new MyException("没有传conn进来。"); } catch (Exception e2) { e2.printStackTrace(); } } } public void after() { try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (stmt != null) { stmt.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (rs != null) { rs.close(); } } catch (Exception e) { e.printStackTrace(); } try { if(proc != null) { proc.close(); } } catch (Exception e) { e.printStackTrace(); } } /** * * @param tableName 要查询的表名，假如数据库有一张myUser表，则 tableName = user * @param keyList 要查询的字段集合，如["name","address"] * @param conditionList 要查询的逻辑集合，如[">",">="] * @param valueList 要查询的值集合，如["小铭","广工"] * @param andOrList 两个查询中间的连接符，如["and","or"] * @param orderList 排序的字段集合，如["age","name"] * @param orderSortList 排序的顺序集合，如["asc","desc"] * @param pageSize 每页显示的数量，如 10 * @param pageNumber 第几页， 如1 * *@param clazz 实体类的Class * @return 该实体类的list */ @SuppressWarnings("unchecked") 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) { // 表名为空时抛异常。 if(tableName == null || "".equals(tableName)) { try { throw new MyException("传进来的tableName为空！"); } catch (Exception e) { e.printStackTrace(); } } // 类型为空时抛异常 if(tableName == null || "".equals(tableName)) { try { throw new MyException("传进来的tableName为空！"); } catch (Exception e) { e.printStackTrace(); } } before(); pre = (int) System.currentTimeMillis(); StringBuilder keyListBuilder = new StringBuilder(); StringBuilder conditionListBuilder = new StringBuilder(); StringBuilder valueListBuilder = new StringBuilder(); StringBuilder andOrListBuilder = new StringBuilder(); StringBuilder orderListBuilder = new StringBuilder(); StringBuilder orderSortListBuilder = new StringBuilder(); String keyListStr = ""; String conditionListStr = ""; String valueListStr = ""; String andOrListStr = ""; String orderSortListStr = ""; String orderSortSortListStr = ""; List<T> ObjectList = new ArrayList<T>(); // 如果不排序 if(orderList == null || "".equals(orderList) || orderList.isEmpty()) { if(orderList == null) { orderList = new ArrayList<String>(); } if(orderSortList == null){ orderSortList = new ArrayList<String>(); } orderList.add("null"); orderSortList.add("null"); } else { for(int i = 0 ; i < orderList.size(); i++) { orderListBuilder.append(orderList.get(i)).append("^"); orderSortListBuilder.append(orderSortList.get(i)).append("^"); } orderSortListStr = orderListBuilder.substring(0, orderListBuilder.length()-1); orderSortSortListStr = orderSortListBuilder.substring(0, orderSortListBuilder.length()-1); } // 如果不分页 if(pageSize == null){ pageSize = new Integer(10); } // 如果没key if(keyList == null || "".equals(keyList) || keyList.isEmpty()) { keyList.add("null"); conditionList.add("null"); valueList.add("null"); andOrList.add("null"); } else { for(int i = 0 ; i < keyList.size() ; i ++) { keyListBuilder.append(keyList.get(i)).append("^"); conditionListBuilder.append(conditionList.get(i)).append("^"); valueListBuilder.append(valueList.get(i)).append("^"); andOrListBuilder.append(andOrList.get(i)).append("^"); } keyListStr = keyListBuilder.substring(0, keyListBuilder.length()-1); conditionListStr = conditionListBuilder.substring(0, conditionListBuilder.length()-1); valueListStr = valueListBuilder.substring(0, valueListBuilder.length()-1); andOrListStr = andOrListBuilder.substring(0, andOrListBuilder.length()-1); } // 和数据库连接 try { proc = conn.prepareCall("{ call testc(?,?,?,?,?,?,?,?,?,?,?) }"); proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); proc.registerOutParameter(2, Types.VARCHAR); proc.setString(3,tableName); proc.setString(4, keyListStr); proc.setString(5,conditionListStr); proc.setString(6,valueListStr); proc.setString(7,andOrListStr); proc.setString(8,orderSortListStr); proc.setString(9,orderSortSortListStr); proc.setInt(10, pageSize); proc.setInt(11, pageNumber); proc.execute(); String para1 = (String) proc.getString(2); sql = para1; ResultSet rs = (ResultSet) proc.getObject(1); // 反射 Method[] methods = clazz.getMethods(); List<String> fieldNameList = new ArrayList<String>(); List<Method> setMethodList = new ArrayList<Method>(); for(Method m : methods) { if(m.getName().toString().contains("set")) { fieldNameList.add((m.getName().toString().substring(3,m.getName().toString().length())).toLowerCase()); setMethodList.add(m); } } // 取返回值 while (rs.next()) { try { Object o = clazz.newInstance(); for(int i = 0 ; i < setMethodList.size() ; i ++) { // 通过反射创建对象 setMethodList.get(i).invoke(o, rs.getObject(fieldNameList.get(i))); } ObjectList.add((T) o); } catch (Exception e) { e.printStackTrace(); } } proc.close(); } catch (SQLException e) { e.printStackTrace(); try { throw new MyException("连接存储过程错误。"); } catch (MyException e1) { e1.printStackTrace(); } }finally { after(); } post = (int) System.currentTimeMillis(); return ObjectList; } /** * 得到查询用时 * @return 查询所用时间 */ public Float getUseTime(){ return (((float)(post - pre))/1000) ; } /** * 异常类 * @author xbw * */ public class MyException extends Exception { private static final long serialVersionUID = 1L; //定义无参构造方法 public MyException(){ super(); } //定义有参数的构造方法 public MyException(String msg){ super("MyExcepyion_By_Zjm:"+msg); } } }
【一个调用的test】
public static void main(String[] args) { FindByProcedure f = new FindByProcedure(); String tableName = ""; List<String> keyList = new ArrayList<String>(); List<String> conditionList =new ArrayList<String>();; List<String> valueList =new ArrayList<String>(); List<String> andOrList =new ArrayList<String>(); List<String> orderList =new ArrayList<String>(); List<String> orderSortList =new ArrayList<String>(); tableName = "T_AP_ZA_LYT_GNLK"; // key keyList.add("ZA_LYT_LKBH"); keyList.add("ZA_LYT_TH"); keyList.add("ZA_LYT_XM"); keyList.add("ZA_LYT_MZ"); keyList.add("ZA_LYT_CSRQ"); keyList.add("ZA_LYT_RKSJ"); keyList.add("ZA_LYT_RKSJ"); // 比较符号 conditionList.add("<"); conditionList.add(">="); conditionList.add("instr"); conditionList.add("<="); conditionList.add("startWith"); conditionList.add(">="); conditionList.add("<="); // value valueList.add("4500000000000000500049"); valueList.add("4600000000000000203771"); valueList.add("VA"); valueList.add("10"); valueList.add("F"); valueList.add("2014-12-24-08-29-38"); valueList.add("2014-12-24-21-37-22"); // 连接符 andOrList.add("and"); andOrList.add("and"); andOrList.add("and"); andOrList.add("and"); andOrList.add("and"); andOrList.add("and"); andOrList.add("and"); // 排序字段 orderList.add("ZA_LYT_XM"); orderList.add("ZA_LYT_XMPY"); // 排序顺序 orderSortList.add("ASC"); orderSortList.add("DESC"); List<T_AP_ZA_LYT_GNLK> list = new ArrayList<T_AP_ZA_LYT_GNLK>(); // 连接conn要从外部传进去 f.setConn(DBManager.getConnection()); // 开始调用 list = f.findByPropertList(tableName, keyList, conditionList, valueList, andOrList,orderList,orderSortList,5,1,T_AP_ZA_LYT_GNLK.class); for(T_AP_ZA_LYT_GNLK o : list) { System.out.println(o.getZa_lyt_xm()); } System.out.println("总共拿出数据量："+list.size()); System.out.println("sql语句："+f.getSql()); System.out.println("查询用时："+f.getUseTime().toString()+"s"); }
oracle入门（8）——实战：支持可变参数、多种条件、多个参数排序、分页的存储过程查询组件,布布扣,bubuko.com