动态查询的实现

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

当前使用ibatis实现

SQLMap配置：

<!-- 动态查询 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>

 

存储过程：

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;

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：

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：

<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>