j2ee规范-jdbc-存储过程调用(oracle为例）

oracle procedure

/* 创建存储过程 */--插入数据create or replace procedure insertUser(   u_id  OUT integer,   uname varchar2,   upass varchar2,   rname VARCHAR2,   usex number,   uage number,   uphone varchar2,   uaddress varchar2,   uauthority number) asbegin    insert into sm_User values(SEQ_USERID.NEXTVAL,uname,upass,rname,usex,uage,uphone,uaddress,uauthority);    SELECT SEQ_USERID.CURRVAL INTO  u_id FROM dual;end;--根据ID查询数据create or replace procedure selectUserById(u_id integer, o_cur out sys_refcursor) asbegin  open o_cur for select * from sm_User where userId = u_id;end;--declare  --xx number := 2;--begin  --selectUserById(xx);  --dbms_output.put_line(xx);--end;--修改数据create or replace procedure updateUser(   u_id integer,   uname varchar2,   upass varchar2,   rname VARCHAR2,   usex number,   uage number,   uphone varchar2,   uaddress varchar2,   uauthority number,   ret_rows out integer) as begin  update sm_User set      lgName = uname,     lgPass = upass,     userName = rname,     sex = usex,     age = uage,     userPhone = uphone,     userAddr = uaddress,     quanxian = uauthority  where userId = u_id;  ret_rows := sql%rowcount;end;--declare  --xx integer := 5;--begin  --updateUser(13,'火爆','123556','钱二',0,19,'23333223','fas',1,xx);  --dbms_output.put_line(xx);  --commit;--end;--删除数据create or replace procedure deleteUser(   u_id in out integer) isbegin  delete from sm_User where userid = u_id;  u_id := sql%rowcount;end;

java

    public int deleteUserInfo(int id) throws ClassNotFoundException, SQLException {        // 根据Id删除用户        Connection conn = null;        CallableStatement cs = null;        String sql = "{call deleteUser(?)}";        try{            conn = super.getConn();            cs = conn.prepareCall(sql);            cs.setInt(1, id);            cs.registerOutParameter(1, oracle.jdbc.OracleTypes.INTEGER);            cs.execute();            return cs.getInt(1);        }finally{            super.closeAll(conn, cs, null);        }    }    public void insertUserInfo(UserInfo info) throws SQLException, NamingException, ClassNotFoundException {        // 插入用户数据        Connection conn = null;        CallableStatement cs = null;        String sql = "{call insertUser(?,?,?,?,?,?,?,?)}";        try{            conn = super.getConn();            cs = conn.prepareCall(sql);            cs.setInt(1, info.getU_id());            cs.setString(2, info.getU_name());            cs.setString(3, info.getPass());            cs.setBoolean(4, info.isSex());            cs.setInt(5, info.getAge());            cs.setString(6, info.getPhone());            cs.setString(7, info.getAddress());            cs.setInt(8, info.getAuthority());            cs.execute();        }finally{            super.closeAll(conn, cs, null);        }    }    public UserInfo selectUserInfoById(int id) throws ClassNotFoundException, SQLException, NamingException {        //根据Id查询        Connection conn = null;        CallableStatement cs = null;        String sql = "call selectUserById(?,?)";        try{            conn = super.getConn();            cs = conn.prepareCall(sql);            cs.setInt(1, id);            cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); //输出参数必须注册            cs.execute();            ResultSet rs = (ResultSet)cs.getObject(2);            UserInfo uf=null;            if(rs.next()){                uf=new UserInfo();                uf.setAddress(rs.getString("address"));                uf.setAge(rs.getInt("age"));                uf.setAuthority(rs.getInt("authority"));                uf.setPass(rs.getString("pass"));                uf.setPhone(rs.getString("phone"));                uf.setSex(rs.getBoolean("sex"));                uf.setU_id(rs.getInt("u_Id"));                uf.setU_name(rs.getString("u_Name"));            }            return uf;        }finally{            super.closeAll(conn, cs, null);        }    }    public int updateUserInfo(UserInfo info) throws SQLException, ClassNotFoundException, NamingException {        // 更新用户数据        Connection conn = null;        CallableStatement cs = null;        String sql = "call updateUser(?,?,?,?,?,?,?,?,?)";        try{            conn = super.getConn();            cs = conn.prepareCall(sql);            cs.setInt(1, info.getU_id());            cs.setString(2, info.getU_name());            cs.setString(3, info.getPass());            cs.setBoolean(4, info.isSex());            cs.setInt(5, info.getAge());            cs.setString(6, info.getPhone());            cs.setString(7, info.getAddress());            cs.setInt(8, info.getAuthority());            cs.registerOutParameter(9, oracle.jdbc.OracleTypes.INTEGER);            cs.execute();            return cs.getInt(9);        }finally{            super.closeAll(conn, cs, null);        }    }