CallableStatement调用存储过程

用于执行 SQL 存储过程的接口

1) 注册驱动

           Class.forName("");

2) 建立连接

           Connection conn = DriverManager.getConnection("","","");

准备SQL

String sql1 = "select * from student";

String sql2 = "insert into student(sno,sname,ssex,sage,sdept) values(?,?,?,?,?)";

String sql3 = "{call proc_split(?,?,?,?)}";

3)      获取Statement对象

           如果没有参数获取Statement(不能处理参数)
            如果sql语句中有参数获取PreparedStatement(只扩展了处理输入参数的方法 ps.setString(1,no))

           如果sql语句是调用存储过程则获取CallableStatement(扩展了对输出参数的操作方法)

4)      执行SQL获取结果

5)      处理结果

6)      关闭连接

分页存储过程

create proc proc_split
@pageSize int,
@pageNum int,
@recordCount int output,
@pageCount int output
as
   select @recordCount=count(*) from student;
   set @pageCount=(@recordCount-1)/@pageSize+1;
   if(@pageNum<1)begin
      set @pageNum=1;
   end else if(@pageNum>@pageCount)begin
      set @pageNum=@pageCount;
   end
   select top (@pageSize) * from student where sno not in
        (select top ((@pageNum-1)*@pageSize) sno from student);
go

// 调用存储过程

public List<StudentDTO> splitPage(int pageSize, int pageNum) { ArrayList<StudentDTO> stus = new ArrayList<StudentDTO>();
   try {
      conn = DBManager.getConnection();
      String sql = "{call proc_split(?,?,?,?)}";
      cs = conn.prepareCall(sql);
      cs.setInt(1, pageSize);
      cs.setInt(2, pageNum);
      // 调用CS中所特有用来处理存储过程中输入输出参数的方法:注册输出参数
      cs.registerOutParameter(3, Types.INTEGER);
      cs.registerOutParameter(4, Types.INTEGER);
      rs = cs.executeQuery();
      while (rs.next()) {
         StudentDTO stu = new StudentDTO();
        stu.setStuNo(rs.getString("sno"));
        stu.setStuName(rs.getString("sname"));
        stu.setStuSex(rs.getString("ssex"));
        stu.setStuAge(rs.getInt("sage"));
        stu.setStuDept(rs.getString("sdept"));
        stus.add(stu);
      }
      // 获取两个输出参数的值
      int recordCount = cs.getInt(3);
      int pageCount = cs.getInt(4);
      System.out.println("总记录数:" + recordCount);
      System.out.println("第" + pageNum + "页");
      System.out.println("如果每页显示" + pageSize + "条,可分为" + pageCount + "页");
   } catch (SQLException e) {
      e.printStackTrace();
   }
   return stus;
}