SpringJDBC 调用oracle 通用存储过程分页

我博客前面有写道SpringJDBC调用通用的Oracle存储过程,今天来讲一下通用的Java存储过程带分页的功能,其中里面还有动态查询的SQL拼接,好的,先上代码

1.Java代码

    @Autowired
    private JdbcTemplate jdbcTemplate;


    /**分页查询
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/findPageList", method = {RequestMethod.GET, RequestMethod.POST}, produces = "application/json;charset=utf-8")
    public IResult findPageList(@RequestBody String param){
        HashMap hashMap = JSON.parseObject(param, HashMap.class);
        String name=(String) hashMap.get("name");
        List<String> reqList=null;
        if( hashMap.get("reqList") != null){
            reqList=(List<String>) hashMap.get("reqList");
        }
        List<String> resList=(List<String>) hashMap.get("resList");

       Map<String,Object> map=this.callPageProcedure(name,reqList,resList);
       List rlist= (List)map.get("data");
       int rtotal= (Integer)map.get("total");
       return new PageResultBean<Collection<Map>>(rlist,rtotal);

    }

    /***
     *    分页的存储过程
     * *  获取数据库内的存储过程--返回一个Map,封装有list集合和总记录数
     * @param procedureName 存储过程名
     * @param inParameter   输入的参数page,limit 至少有2个
     * @param outParamter   输出的参数
     * @return
     */
    private Map<String,Object> callPageProcedure(final String procedureName,final List inParameter,final List outParamter){
        if(procedureName==null || procedureName.length() == 0 ){
            return null;
        }
        //输入参数的校验
        if(inParameter ==null ){
            throw new BizException("输出参数为空!");
        }
        //输入参数的校验
        if(inParameter.size()<2 ){
            throw new BizException("输出参数的个数错误!");
        }

        //输出参数的校验,没有返回参数则抛出异常
        if(outParamter == null || outParamter.size() == 0){
          throw new BizException("输出参数为空!");
        }

        Map<String,Object> r = (Map<String,Object>) jdbcTemplate.execute(
                new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        int inSize = inParameter==null?0:inParameter.size();
                        //int outSize = outParamter==null?0:outParamter.size();
                        StringBuffer sbsql = new StringBuffer();
                        sbsql.append("{call "+procedureName).append("(");
                        //注意这里面返回的是游标,加1即可
                        for(int i=0;i<(inSize+1);i++){
                            if(i == 0){//必须有一个参数
                                sbsql.append("?");
                            }else{
                                sbsql.append(",?");
                            }
                        }
                        sbsql.append(")}");
                        CallableStatement cs = con.prepareCall(sbsql.toString());
                        // 设置输入参数的值
                        if(inSize > 0 ){
                            String typeName =  null;
                            for(int i=0;i<inSize;i++){
                                typeName = inParameter.get(i).getClass().getName().toString();
                                cs.setObject(i+1, inParameter.get(i));
                            }
                        }
                        // 注册输出参数的类型
                        System.out.println("inSize的大小 "+inSize );
                        cs.registerOutParameter(inSize, OracleTypes.INTEGER);//取条数
                        cs.registerOutParameter(inSize+1, OracleTypes.CURSOR);//取list
                        return cs;
                    }
                }, new CallableStatementCallback<Map<String,Object> >() {
                    public Map<String,Object> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                        int inSize = inParameter==null?0:inParameter.size();
                        Map<String,Object> resMap=new HashMap<>();
                        List<Map> list = new ArrayList();
                        cs.execute();
                        ResultSet rs = (ResultSet) cs.getObject(inSize+1);// 获取游标一行的值

                        int count=(int)cs.getObject(inSize);//取条数

                        while (rs.next()) {// 转换每行的返回值到Map中
                            Map rowMap = new HashMap();
                            for(int i=0;i<outParamter.size();i++){
                                String outP = outParamter.get(i).toString();
                                rowMap.put(outP.toLowerCase(),rs.getObject(outP.toUpperCase()) );
                            }
                            list.add(rowMap);
                        }
                        rs.close();

                        resMap.put("data",list);
                        resMap.put("total",count);
                        return resMap;
                    }

                });
        return r;

    }

2.Oracle数据库代码

--程序包头
create or replace package P_findAreaPage_pak
is
type outData is ref cursor;
procedure P_findAreaPage(p_pagesize int,
                        p_startsize int,
                        jgdm varchar2,
                        ywlx varchar2,
                        jgmc varchar2,
                        xzqhname varchar2,
                        sDate varchar2,
                        eDate varchar2,
                        out_pagecount out int,--输出参数,调用的时候随便填写一个整数值,详情---请看postman传参
                        out_datacollection out outData);
end P_findAreaPage_pak;

--程序包体
create or replace package body P_findAreaPage_pak
is
procedure P_findAreaPage(p_pagesize int,
                          p_startsize int,
                          jgdm varchar2,
                          ywlx varchar2,
                          jgmc varchar2,
                          xzqhname varchar2,
                          sDate varchar2,
                          eDate varchar2,
                          out_pagecount out int,--输出参数,调用的时候随便填写一个整数值
--请看postman传参
                          out_datacollection out outData) 
as
v_sql varchar2(3000);
v_count int;
v_upCount int;
v_lowCount int;
v_where varchar2(3000):='where 1=1';
begin
    --拼接查询条件
       IF jgdm IS NOT NULL or jgdm <> ' ' THEN
       v_where := v_where||'  and  t0.jgdm= '''||jgdm||'''';
          END IF;
       IF ywlx IS NOT NULL or ywlx <> ' ' THEN
       v_where := v_where||'  and t0.ywlx = '''||ywlx||'''';
          END IF;
       IF jgmc IS NOT NULL or jgmc <> ' ' THEN
       v_where := v_where||'  and t0.jgmc like  ''%'||jgmc||'%''';
          END IF;
       IF xzqhname IS NOT NULL or xzqhname <> ' ' THEN
       v_where := v_where||'  and t0.xzqhname like ''%'||xzqhname||'%''';
          END IF;
       IF sDate IS NOT NULL or sDate <> ' ' THEN
       v_where := v_where|| ' and t0.bzrq >= to_date('''||sDate||''',''yyyy-mm-dd'')';
          END IF;
       IF eDate IS NOT NULL or eDate <> ' ' THEN
       v_where := v_where|| ' and  t0.bzrq <= to_date('''||eDate||''',''yyyy-mm-dd'')';
      DBMS_OUTPUT.put_line(v_where);
        END IF;
     --获取页面总的记录数
    execute immediate ' select count(1)
		from  AA t0  inner JOIN BB t1 on T0.BZJGDM =T1.BZJGCODE ' || v_where into v_count;
    out_pagecount:=v_count;
    --获取数据的上下限
    v_upCount:=p_startsize*p_pagesize;   --上限
    v_lowCount:=v_upCount-p_pagesize+1; --下限
    v_sql:='select * from (select a.*,rownum r from (select nvl(substr(T1.BZJGNAME,4),''||其它地方||'') as name,count(1) as sum
					  from  AA t0  inner JOIN BB t1 on T0.BZJGDM =T1.BZJGCODE ' || v_where ||' GROUP BY T1.BZJGNAME ORDER BY count(1) desc ) a where rownum <='||to_char(v_upCount)||') B
               where r>='||to_char(v_lowCount);
    DBMS_OUTPUT.put_line(v_sql);
    open out_datacollection for v_sql;
end p_findAreaPage;
end P_findAreaPage_pak;

注意,我这里用到了两张表,你们可以自行新建两张表,将我的替换即可,附上建表语句

3.建表语句,数据你们自行插入

-- Create table
create table AA
(
  jjhydmold VARCHAR2(10),
  orgid     NUMBER(20),
  jgdm      VARCHAR2(10),
  jgmc      VARCHAR2(200),
  xzqhname  VARCHAR2(100),
  bzrq      DATE,
  bzjgdm    VARCHAR2(9),
  fddbr     VARCHAR2(200),
  zjlx      VARCHAR2(100),
  zjhm      VARCHAR2(25),
  jgdz      VARCHAR2(200),
  yzbm      VARCHAR2(10),
  dhhm      VARCHAR2(42),
  zczj      NUMBER(20,8),
  zch       VARCHAR2(65),
  pzjgmc    VARCHAR2(160),
  jyfw      VARCHAR2(2000),
  pigetime  DATE,
  ywlx      VARCHAR2(50),
  zgrs      NUMBER(20),
  rn        NUMBER
)

-- Create table
create table BB
(
  bzjgid      NUMBER(10) not null,
  bzjgname    VARCHAR2(100),
  bzjgcode    VARCHAR2(100),
  bzjgjcname  VARCHAR2(50),
  centerid    NUMBER(10),
  centername  VARCHAR2(50),
  centercode  VARCHAR2(50),
  address     VARCHAR2(150),
  tellphone   VARCHAR2(50),
  leader      VARCHAR2(50),
  mobilephone VARCHAR2(50),
  remark      VARCHAR2(500),
  coltime     DATE
)

4.Postman请求
 

//请求url(Get或Post请求)
localhost:9090/produce/findPageList

//参数JSON
{"name":"P_findAreaPage_pak.P_findAreaPage",
"reqList":[ "3", "1","","换证","武汉","","","2009-02-02",1222],
"resList":["name","sum"]
    
}

//返回JSON
{
    "code": "0000",
    "msg": "success",
    "data": [
        {
            "name": "办证大厅",
            "sum": 158
        },
        {
            "name": "青山区",
            "sum": 7
        },
        {
            "name": "洪山区",
            "sum": 7
        }
    ],
    "count": 202
}

注意reqList最后一个参数是一个输入输出参数,我们这里随便填写一个整数值即可,我这里填写的是:1222

最后,附上的Java工具类

//总共4个类

//返回结果
@Data
public class PageResultBean<T> extends AbstractResult  implements Serializable{

    private static final long serialVersionUID = 1L;

    /**
     * 分页数据,同layui接受的参数名一样
     * */
    private T data;

    /**
     * 记录总数,同layui接受的参数名一样
     * */
    private Integer count;

    public PageResultBean() {
        super();
    }

    /**
     * 构造函数
     * */
    public PageResultBean(T data,Integer count) {
        super();
        this.data = data;
        this.count = count;
    }

    /**
     * 构造函数
     * */
    public PageResultBean(IErrCode e) {
        super();
        this.msg = e.getDesc();
        this.code = e.getCode();
    }

    public PageResultBean(Throwable e) {
        super();
        this.msg = e.toString();
        this.code = SYSTEM_FAIL;
    }

    /**
     * 构造函数
     * */
    public PageResultBean(String code,String msg) {
        super();
        this.msg = msg;
        this.code = code;
    }
}

//返回结果
@Data
public abstract class AbstractResult implements IResult{
    /**成功Code*/
    public static final String SUCCESS = "0000";
    /**系统失败Code*/
    public static final String SYSTEM_FAIL = "9999";
    /**检查失败Code*/
    public static final String CHECK_FAIL = "9100";
    /**业务失败Code*/
    public static final String BIZ_FAIL = "9200";
    
    public static final String HTTP_FILE="5000";

    /**错误代码*/
    protected String code = SUCCESS;
    /**错误信息*/
    protected String msg = "success";
}

//返回结果
public interface IResult {

}


/**
 * 业务异常封装
 * @author leiYao 2017-11-10
 * */
@Data
public class BizException extends RuntimeException {

	private static final long serialVersionUID = 1L;

	private String errorCode;

	public BizException() {
	}

	/**
	 * 构造函数
	 * */
	public BizException(String code,String message) {
		super(message);
		this.errorCode = code;
	}

	/**
	 * 构造函数
	 * */
	public BizException(IErrCode e) {
		super(e.getDesc());
		this.errorCode = e.getCode();
	}

	public BizException(Throwable cause) {
		super(cause);
	}

	public BizException(String message, Throwable cause) {
		super(message, cause);
	}

	public BizException(String message) {
		super(message);
	}

}