• 动态查询的实现


    动态查询,就是在我们的应用中,需要查询某个表。只要丢一个SQL语句,就能查询到所查询表的记录。
    其中查询的结果包括两个集合:有多少个字段的集合,有多少表记录的集合。

    当前使用ibatis实现

    SQLMap配置:

    View Code
    <!-- 动态查询 ex-hulaizhi001 2013-1-8 -->    
        <parameterMap id="para-dynamicQueryList" class="java.util.Map">
          <parameter property="p_sql" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
            <parameter property="p_page" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
            <parameter property="p_rows" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
            <parameter property="p_limits" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
            <parameter property="p_cnt" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />    
            <parameter property="p_select_column" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
            <parameter property="p_error_msg" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
            <parameter property="p_col_cnt" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
            <parameter property="results" jdbcType="ORACLECURSOR" javaType="java.util.List" typeHandler="XXXXXXXXXXXXXXX.util.OracleCursorHandler" mode="OUT" />
        </parameterMap>
        
            
        <procedure id="dynamicQueryList"
            parameterMap="para-dynamicQueryList">
            {call SCMS_COMMON_PACKGE.prc_dynamic_query(?,?,?,?,?,?,?,?,?)}
        </procedure>

    存储过程:

    View Code
    function fun_dynamic_query_get_col(p_table     varchar2,
                                         p_separator varchar2) return varchar2 is
      /**************************************************************************
       * 程序说明:
       *           用于获取指定表的字段,以指定分隔符进行分隔
       * 输入参数:
       *           p_table:指定的表
       *           p_separator:分隔符,如','等
       * 返回参数:
       *           varchar2类型,返回指定表的列按照分隔符组成的字符串
       *************************************************************************/
        cursor cur is
          select s.COLUMN_NAME,s.DATA_TYPE
            from user_tab_columns s
           where s.TABLE_NAME = upper(p_table)
           order by s.COLUMN_ID;
        v_return         varchar2(4000);
        --v_tochar_column  varchar2(256);
        v_mid            varchar2(256);
      begin
        for c in cur loop
          if c.data_type = 'TIMESTAMP(6)' then
            /*v_tochar_column := 'to_char('|| c.column_name ||',''yyyy-mm-dd hh24:mi:ss'') as '||c.column_name;
            v_mid    := v_tochar_column || p_separator;*/
          --  v_mid := 'to_char('|| c.column_name ||',''yyyy-mm-dd hh24:mi:ss'') as '||c.column_name || p_separator;
          v_mid := ' ';
          else
            v_mid    := c.column_name || p_separator;
          end if;
          v_return := v_return || v_mid;
        end loop;
        if v_return is not null then
          return substr(v_return, 1, length(v_return) - 1);
        end if;
    
      exception
        when others then
          v_sqlerrm := substr(sqlerrm, 1, 1024);
          --prc_log_check('prc_splitpage_new',  不插入日志
                    --    'fun_splitpage_new_get_col',
                     --   v_sqlerrm,
                      --  3);
          --raise;
      end fun_dynamic_query_get_col;
    
    procedure prc_dynamic_query(p_sql           varchar2,
                                  p_page          number,
                                  p_rows          number,
                                  p_limits        number,
                                  p_cnt           out number,
                                  p_select_column out varchar2,
                                  p_error_msg     out varchar2,
                                  p_col_cnt       out number,
                                  results         out sys_refcursor) as
      /**************************************************************************
       * 程序说明:
       *           根据用户输入的SQL进行分页,多表分页
       * 输入参数:
       *           p_sql:用户输入的SQL,多表关联,如union,join等
       *           p_pages:动态分页显示第几页
       *           p_rows:动态分页每页显示的行数
       *           p_limits:动态分页对记录数的限制,输入的SQL记录数不能超过该值
       * 输出参数:
       *           p_cnt:SQL返回的总记录数
       *           p_select_column:SQL返回的查询列组成的列表,以逗号分隔
       *           p_error_msg:异常,0表无异常,否则返回异常信息
       *           p_col_cnt:SQL返回的查询列组成的列表中列的个数
       *           results:游标返回动态分页的结果
       *注意事项:
       *           首次执行时,需要显示给当前用户授予以下权限:
       *           grant create any table to spas;
       *************************************************************************/
        p_sql_mid varchar2(32000);
        v_table   varchar2(30);
        --v_last_table varchar2(30);
        v_select  varchar2(32000);
        v_columus varchar2(32000);
        v_mid     varchar2(32000);
      begin
        --剥离提供的SQL的分号
        --p_sql_mid := ltrim(rtrim(p_sql,chr(10)),chr(10));
        if (instr(p_sql, ';') > 0) then
          --p_sql_mid := substr(trim(p_sql_mid), 1, length(trim(p_sql_mid)) - 1);
          p_sql_mid := replace(p_sql,';');
        else
          p_sql_mid := trim(p_sql);
        end if;
    
        --创建临时表
        v_table := upper('splitpage_' || to_char(sysdate, 'yyyymmddhh24miss'));
        --dbms_output.put_line(v_table);
        v_sql := 'create table ' || v_table || ' /*+ APPEND */ as select rownum as "序号", tmp.* from (' ||
                 p_sql_mid || ') tmp';
       --    insert into spas_audit_sqls_log    --不插入日志 EX-HULAIZHI001
         --   select 9998,sysdate,'pkg_insurance_audit.prc_splitpage_new',9998,'splitpage on ' || v_table,v_sql from dual;
            commit;
        --dbms_output.put_line(v_sql);
        execute immediate v_sql;
    
        v_sql := 'select count(*) from user_tab_columns s where s.table_name = ''' || v_table || '''';
        execute immediate v_sql into p_col_cnt;
    
        --查询的记录数
        v_sql := 'select count(*) from ' || v_table;
        --dbms_output.put_line(v_sql);
        execute immediate v_sql
          into p_cnt;
    
        --如果查询的记录数小于查询记录数限额,进行分页
        if nvl(p_cnt, 0) <= p_limits then
          v_select        := fun_dynamic_query_get_col(v_table, ',');
          p_select_column := v_select;
          for c in (select s.COLUMN_NAME
                       from user_tab_columns s
                      where s.TABLE_NAME = upper(v_table)
                      order by s.COLUMN_ID) loop
            v_mid     := c.column_name || ',';
            v_columus := v_columus || v_mid;
          end loop;
         /* if v_columus is not null then
            v_columus := substr(v_columus, 1, length(v_columus) - 1);
          end if;*/
    
          --dbms_output.put_line('----' || v_select || '----');
          --FIRST_ROWS(N) tells the optimizer, "Hey, I'm interested in getting the first rows,
          --                                    and I'll get N of them as fast as possible."
          v_sql := 'select *' ||
                   ' from (select /*+ FIRST_ROWS(' || p_rows || ') */ '|| v_select ||', rownum as rn from ' || v_table ||
                   ' t where rownum <= ' || p_page || ' * ' || p_rows ||
                   ') where rn > (' || p_page || ' - 1) * ' || p_rows || '';
          --dbms_output.put_line(v_sql);
         -- insert into spas_audit_sqls_log   --不插入日志
          --  select 9999,sysdate,'pkg_insurance_audit.prc_splitpage_new',9999,'splitpage on ' || v_table,v_sql from dual;
            commit;
          open results for v_sql;
    
          --如果成功,返回0
          p_error_msg := '0';
          --prc_log_check('prc_splitpage_new', v_table, 'split page success', 2);
        else
          p_error_msg := '您好,您查询出来的总记录数超过最大值!';
        end if;
    
      exception
        when others then
         v_sqlerrm   := substr(sqlerrm, 1, 1024);
          p_error_msg := v_sqlerrm;
          --4 警告级错误   不插入日志
         -- prc_log_check('prc_splitpage_new',
                --        nvl(v_table, 'prc_splitpage_new'),
                 --       v_sqlerrm || ' -- ' || v_sql,
                   --     4);
        --raise;
      end prc_dynamic_query;
    View Code
    function fun_dynamic_query_get_col(p_table     varchar2,
                                         p_separator varchar2) return varchar2 is
      /**************************************************************************
       * 程序说明:
       *           用于获取指定表的字段,以指定分隔符进行分隔
       * 输入参数:
       *           p_table:指定的表
       *           p_separator:分隔符,如','等
       * 返回参数:
       *           varchar2类型,返回指定表的列按照分隔符组成的字符串
       *************************************************************************/
        cursor cur is
          select s.COLUMN_NAME,s.DATA_TYPE
            from user_tab_columns s
           where s.TABLE_NAME = upper(p_table)
           order by s.COLUMN_ID;
        v_return         varchar2(4000);
        --v_tochar_column  varchar2(256);
        v_mid            varchar2(256);
      begin
        for c in cur loop
          if c.data_type = 'TIMESTAMP(6)' then
            /*v_tochar_column := 'to_char('|| c.column_name ||',''yyyy-mm-dd hh24:mi:ss'') as '||c.column_name;
            v_mid    := v_tochar_column || p_separator;*/
          --  v_mid := 'to_char('|| c.column_name ||',''yyyy-mm-dd hh24:mi:ss'') as '||c.column_name || p_separator;
          v_mid := ' ';
          else
            v_mid    := c.column_name || p_separator;
          end if;
          v_return := v_return || v_mid;
        end loop;
        if v_return is not null then
          return substr(v_return, 1, length(v_return) - 1);
        end if;
    
      exception
        when others then
          v_sqlerrm := substr(sqlerrm, 1, 1024);
          --prc_log_check('prc_splitpage_new',  不插入日志
                    --    'fun_splitpage_new_get_col',
                     --   v_sqlerrm,
                      --  3);
          --raise;
      end fun_dynamic_query_get_col;
    
    procedure prc_dynamic_query(p_sql           varchar2,
                                  p_page          number,
                                  p_rows          number,
                                  p_limits        number,
                                  p_cnt           out number,
                                  p_select_column out varchar2,
                                  p_error_msg     out varchar2,
                                  p_col_cnt       out number,
                                  results         out sys_refcursor) as
      /**************************************************************************
       * 程序说明:
       *           根据用户输入的SQL进行分页,多表分页
       * 输入参数:
       *           p_sql:用户输入的SQL,多表关联,如union,join等
       *           p_pages:动态分页显示第几页
       *           p_rows:动态分页每页显示的行数
       *           p_limits:动态分页对记录数的限制,输入的SQL记录数不能超过该值
       * 输出参数:
       *           p_cnt:SQL返回的总记录数
       *           p_select_column:SQL返回的查询列组成的列表,以逗号分隔
       *           p_error_msg:异常,0表无异常,否则返回异常信息
       *           p_col_cnt:SQL返回的查询列组成的列表中列的个数
       *           results:游标返回动态分页的结果
       *注意事项:
       *           首次执行时,需要显示给当前用户授予以下权限:
       *           grant create any table to spas;
       *************************************************************************/
        p_sql_mid varchar2(32000);
        v_table   varchar2(30);
        --v_last_table varchar2(30);
        v_select  varchar2(32000);
        v_columus varchar2(32000);
        v_mid     varchar2(32000);
      begin
        --剥离提供的SQL的分号
        --p_sql_mid := ltrim(rtrim(p_sql,chr(10)),chr(10));
        if (instr(p_sql, ';') > 0) then
          --p_sql_mid := substr(trim(p_sql_mid), 1, length(trim(p_sql_mid)) - 1);
          p_sql_mid := replace(p_sql,';');
        else
          p_sql_mid := trim(p_sql);
        end if;
    
        --创建临时表
        v_table := upper('splitpage_' || to_char(sysdate, 'yyyymmddhh24miss'));
        --dbms_output.put_line(v_table);
        v_sql := 'create table ' || v_table || ' /*+ APPEND */ as select rownum as "序号", tmp.* from (' ||
                 p_sql_mid || ') tmp';
       --    insert into spas_audit_sqls_log    --不插入日志 EX-HULAIZHI001
         --   select 9998,sysdate,'pkg_insurance_audit.prc_splitpage_new',9998,'splitpage on ' || v_table,v_sql from dual;
            commit;
        --dbms_output.put_line(v_sql);
        execute immediate v_sql;
    
        v_sql := 'select count(*) from user_tab_columns s where s.table_name = ''' || v_table || '''';
        execute immediate v_sql into p_col_cnt;
    
        --查询的记录数
        v_sql := 'select count(*) from ' || v_table;
        --dbms_output.put_line(v_sql);
        execute immediate v_sql
          into p_cnt;
    
        --如果查询的记录数小于查询记录数限额,进行分页
        if nvl(p_cnt, 0) <= p_limits then
          v_select        := fun_dynamic_query_get_col(v_table, ',');
          p_select_column := v_select;
          for c in (select s.COLUMN_NAME
                       from user_tab_columns s
                      where s.TABLE_NAME = upper(v_table)
                      order by s.COLUMN_ID) loop
            v_mid     := c.column_name || ',';
            v_columus := v_columus || v_mid;
          end loop;
         /* if v_columus is not null then
            v_columus := substr(v_columus, 1, length(v_columus) - 1);
          end if;*/
    
          --dbms_output.put_line('----' || v_select || '----');
          --FIRST_ROWS(N) tells the optimizer, "Hey, I'm interested in getting the first rows,
          --                                    and I'll get N of them as fast as possible."
          v_sql := 'select *' ||
                   ' from (select /*+ FIRST_ROWS(' || p_rows || ') */ '|| v_select ||', rownum as rn from ' || v_table ||
                   ' t where rownum <= ' || p_page || ' * ' || p_rows ||
                   ') where rn > (' || p_page || ' - 1) * ' || p_rows || '';
          --dbms_output.put_line(v_sql);
         -- insert into spas_audit_sqls_log   --不插入日志
          --  select 9999,sysdate,'pkg_insurance_audit.prc_splitpage_new',9999,'splitpage on ' || v_table,v_sql from dual;
            commit;
          open results for v_sql;
    
          --如果成功,返回0
          p_error_msg := '0';
          --prc_log_check('prc_splitpage_new', v_table, 'split page success', 2);
        else
          p_error_msg := '您好,您查询出来的总记录数超过最大值!';
        end if;
    
      exception
        when others then
         v_sqlerrm   := substr(sqlerrm, 1, 1024);
          p_error_msg := v_sqlerrm;
          --4 警告级错误   不插入日志
         -- prc_log_check('prc_splitpage_new',
                --        nvl(v_table, 'prc_splitpage_new'),
                 --       v_sqlerrm || ' -- ' || v_sql,
                   --     4);
        --raise;
      end prc_dynamic_query;

    action:

    View Code
    commonService.procedure("dynamicQueryList", params,"00");
            String msg = (String) params.get("p_error_msg");
    
            Map model = new HashMap();
            model.put("msg", msg);
    
            if (!"0".equals(msg)) {
    
                response.setModel(model);
                return response;
            }
    
            List templateList = (List) params.get("results");
            String cnt = (String) params.get("p_cnt");
            String coms = (String) params.get("p_select_column");
            String p_col_cnt = (String) params.get("p_col_cnt");
            Integer count = Integer.valueOf(cnt);
            coms=coms.replace("序号,", ""); //去掉序号一列
            // 总记录数
            String[] columnList = coms.split(",");
            model.put("count", count);
            model.put("testList", templateList);// 结果集
            model.put("columnList", columnList);// 字段集
            model.put("columnCount", p_col_cnt);

    网页jsp:

    View Code
    <table width="98%" border="1"cellpadding="3" cellspacing="0" width="98%" bordercolor="#999999" style="border-collapse:collapse;">
            <c:if test="${msg != '0'}">
              <tr height=30 align="center" bgcolor="#cccccc">
                 <td><font style="color:red"><c:out value="${msg}" /></font></td>
              </tr>
           </c:if> 
          <c:if test="${msg == '0'}">  
                  <tr height=30 align="center" bgcolor="#cccccc">
                   
                    <c:forEach items="${columnList}" var="column" varStatus="rowstatus">
                           <th><c:out value="${column}" /></th>
                   </c:forEach>
                 </tr>
         </c:if> 
         <c:if test="${  empty  testList && dispatch=='query' }">
             <tr align="center"><td colspan='<c:out value="${columnCount}" />' >没有查到记录</td></tr>
          </c:if>
                <c:forEach items="${testList}" var="dto" varStatus="rowstatus">
                  <tr height="25">
                  
                     <c:forEach items="${columnList}" var="column" varStatus="rowstatus">
                               <th><c:out value="${dto[column]}" /></th>
                     </c:forEach>
                  </tr>
                  </c:forEach>
             
          </table>
  • 相关阅读:
    Node Introduce
    鼠标拖动物体
    给模型自动赋予贴图代码
    JS读取XML
    动态控件01
    背包代码
    输出文本信息在U3D读取切换SHADER的SCRIPT测试
    材质球一闪一闪
    适配器模式1
    简单工厂,工厂方法的区别总结
  • 原文地址:https://www.cnblogs.com/a393060727/p/2989008.html
Copyright © 2020-2023  润新知