JDBC存储过程调用

一、不带参数

 

      public void procedure01() throws SQLException {

            Connection connection = JDBC.getConn();

            CallableStatement callableStatement = connection.prepareCall("{call query_top10()}"); 

            ResultSet resultSet = callableStatement.executeQuery();

            while(resultSet.next()){

                  int num = resultSet.getInt("num");

                  System.out.println(num);

            }

二、带参数

 

create procedure sp_paging

(

currentpage int,

pagesize int,

tname varchar(64),

selections varchar(2000),

conditions varchar(2000),

sortcolumn varchar(64),

sortorder varchar(40),

out totalnum int,

out totalpage int

)

begin

         #临时变量，标记开始查询位置

         declare vstart int default 0;

         #设置起始位置

         set vstart = (currentpage -1)*pagesize;

         #判断是否填写查询列，若未填写则默认使用*

         if selections is null or selections='' then

                     set selections='*';

         end if;

         #判断是否填写查询条件

         if conditions is null or conditions='' then

                     set conditions = '1=1';

         end if;

         #判断是否填写排序列，若未填写则使用默认的排序列

         if sortcolumn is null or sortcolumn='' then

                     set sortcolumn = '_id';

         end if;

         #判断是否给定正确的排序方式，如未规定或者不符合要求

         if lower(sortorder) !='asc' and lower(sortorder) !='desc' then

                     SELECT'hhhhhhhhhh';

                     set sortorder='asc';

         end if;

end;

    

package com.softeem.jdbc.proc;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.softeem.jdbc1.BaseConn;

public class ProcedureDemo extends BaseConn{//BaseConn 包括连接获取的方法，省略

    public void proc01() throws SQLException{

        //根据存储过程调用命令获取预处理对象

        CallableStatement cs = getConn().prepareCall("{call query_top10()}");

        ResultSet rs = cs.executeQuery();

        while(rs.next()){

            int num = rs.getInt("num");

            String name = rs.getString("name");

            System.out.println(num+"---"+name);

        }

    }
    
public PageUtils procPaging(PageUtils pu) throws SQLException{//PageUtils包含DTO或者实体类

        CallableStatement cs = getConn().prepareCall("{call sp_paging(?,?,?,?,?,?,?,?,?)}");

        cs.setInt(1, pu.getCurrentPage());

        cs.setInt(2, pu.getPageSize());

        cs.setString(3, pu.getTableName());

        cs.setString(4, pu.getSelections());

        cs.setString(5, pu.getCondition());

        cs.setString(6, pu.getSortColumn());

        cs.setString(7, pu.getSortType());

        

        //注册输出参数

        cs.registerOutParameter(8, java.sql.Types.INTEGER);

        cs.registerOutParameter(9, java.sql.Types.INTEGER);

        //执行存储过程

        cs.execute();

        //获取制定位置的输出参数值

        int totalNum = cs.getInt(8);

        int totalPage = cs.getInt(9);

        pu.setTotalNum(totalNum);

        pu.setTotalPage(totalPage);

        

        //声明用于存储查询结果的集合

        List<Object[]> datas = new ArrayList<>();

        

        //获取查询的结果集

        ResultSet rs = cs.getResultSet();

        ResultSetMetaData rsmd = rs.getMetaData();

        int count = rsmd.getColumnCount();

        while(rs.next()){

            Object[] obj = new Object[count];

            for(int i = 0;i<count;i++){

                //获取标签名称（可能是列名称）

                String label = rsmd.getColumnLabel(i+1);

                Object c = rs.getObject(label);

                obj[i] = c;

            }

            datas.add(obj);

        }

        //将查询结果设置到PageUtils中

        pu.setDatas(datas);

        return pu;

    }

    

    public static void main(String[] args) throws SQLException {

        PageUtils pu = new PageUtils();

        pu.setCurrentPage(1);

        pu.setPageSize(2);

        pu.setTableName("department");

//        pu.setSelections("");

//        pu.setCondition("num < 10000");

        pu.setSortColumn("depno");

        pu.setSortType("desc");

        

        pu = new ProcedureDemo().procPaging(pu);

        for (Object[] obj : pu.getDatas()) {

            for (int i = 0; i < obj.length; i++) {

                System.out.print(obj[i]+"  ");

            }

            System.out.println();

        }

    }

}