JavaJDBC【四、存储过程的使用】

Mysql还没学到存储过程，不过语法比较简单
此处不深究数据库中的存储过程怎么创建，后面在mysql的学习笔记里再做整理
今天只整理java中如何调用存储过程

• 语句
  CallableStatement cs = (CallableStatement) con.prepareCall("call testProcedure()"); //使用prepareCall，用存储过程初始化
  ResultSet rs = cs.getResultSet(); //直接调用，返回resultset

cs.registerOutParameter(1, Types.INTEGER); //设置出参
cs.execute(); //或设置出参后直接调用，返回出参
cs.getInt(1); //获取返回值

1. 无参存储过程的调用

    public static List<JDBCModel> proceNoParam() throws SQLException {
    List<JDBCModel> relist = new ArrayList<JDBCModel>();
    Connection con = DBUtil.GetConnection();
    CallableStatement cs = (CallableStatement) con
            .prepareCall("call testProcedure()");
    cs.execute();
    ResultSet rs = cs.getResultSet();
    while (rs.next()) {
        JDBCModel m = new JDBCModel();
        m.setId(rs.getInt("id"));
        m.setName(rs.getString("name"));
        m.setCreatetime(rs.getDate("createtime"));
        relist.add(m);
    }
    return relist;
}

2. 含入参存储过程的调用

    public static List<JDBCModel> proceInParam(int id) throws SQLException {

    List<JDBCModel> relist = new ArrayList<JDBCModel>();
    Connection con = DBUtil.GetConnection();
    CallableStatement cs = (CallableStatement) con
            .prepareCall("call testInParam(?)");
    cs.setInt(1, id);
    cs.execute();
    ResultSet rs = cs.getResultSet();
    while (rs.next()) {
        JDBCModel m = new JDBCModel();
        m.setId(rs.getInt("id"));
        m.setName(rs.getString("name"));
        m.setCreatetime(rs.getDate("createtime"));
        relist.add(m);
    }
    return relist;
}

3. 含出参（非ResultSet）存储过程的调用

    public static int proceOutParam() throws SQLException {
    Connection con = DBUtil.GetConnection();
    CallableStatement cs = (CallableStatement) con
            .prepareCall("call testOutParam(?)");
    cs.registerOutParameter(1, Types.INTEGER);
    cs.execute();
    int c = cs.getInt(1);
    return c;
}

转载于:https://www.cnblogs.com/shanelau/p/6667159.html