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没有自带的”根据某标识“切割字符串的函数,所以我们要自己建立。

/**
用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

oracle入门(8)——实战:支持可变参数、多种条件、多个参数排序、分页的存储过程查询组件

上一篇:SpringBoot+log4j2+MDC+AOP记录requestId


下一篇:枚举(一)