动态查询，就是在我们的应用中，需要查询某个表。只要丢一个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>