java 调用存储过程

mybatise 调存储过程

存储过程

create or replace procedure pag_add(p1 varchar2,p2 varchar2,p3 out varchar2)
   as
    begin
          p3:=p1+p2;
      end;

<!-- mapper.xml -->
    <select id="callProcedure" parameterType="map" statementType="CALLABLE" >
      {
         call prg_add(
               #{p1,mode=IN,jdbcType=NUMERIC},   <!--参数一-->
               #{p2,mode=IN,jdbcType=NUMERIC},   <!--参数二-->

               #{p3,mode=OUT,jdbcType=NUMERIC}   <!--输出参数-->

         )
      }
  </select>

service： 

//调用存储过程
    public static void main(String[] args) throws IOException {
        SqlSession session=getSession();
        CallMapper cm=session.getMapper(CallMapper.class);
        Map map=new HashMap();
        map.put("p1", 100);
        map.put("p2", 200);

      map.put("p3", null);

cm.callProcedure(map); System. out.println(map. get( " p3 " )); }

jdbc 调用存储过程

1. public void testProcedure(){
3. String sql = "{call selectUserById(?,?,?,?)}";
5. Connection conn = null;
6. CallableStatement call = null;
7. try {
8. //得到一个数据库连接
9. conn = JDBCUtils.getConnection();
10. //通过连接创建出statement
11. call = conn.prepareCall(sql);
12. //对于in参数，赋值
13. call.setInt( 1, 2); // (第几个问号,要赋的值)
14. //对out参数，声明
15. call.registerOutParameter( 2, OracleTypes.VARCHAR); //(第几个问号，声明的类型)
16. call.registerOutParameter( 3, OracleTypes.NUMBER);
17. call.registerOutParameter( 4, OracleTypes.CHAR);
19. //执行调用
20. call.execute();
22. //取出结果
23. String userName = call.getString( 2);
24. int userAge = call.getInt( 3);
25. String userSex = call.getString( 4);
27. System.out.println( "用户姓名："+userName+ "\n\t年龄:"+userAge+ "\n\t性别："+userSex);
29. } catch (Exception e) {
30. e.printStackTrace();
31. } finally{
32. //关闭连接，释放资源
33.   JDBCUtils.release(conn, call, null);
34. }
36. }

返回结果集

1. 1. import java.sql.CallableStatement;
   2. import java.sql.Connection;
   3. import java.sql.ResultSet;
   5. import org.junit.Test;
   7. import oracle.jdbc.internal.OracleCallableStatement;
   8. import oracle.jdbc.internal.OracleTypes;

   。。。class。。。。。。
2. public void testCursor(){
3. String sql = "{call Mypackage.queryUserList(?,?) }";
5. Connection conn = null;
6. CallableStatement call = null;
7. ResultSet rs = null;
8. try {
9. //得到数据库连接
10. conn = JDBCUtils.getConnection();
11. //通过数据库连接创建statement
12. call = conn.prepareCall(sql);
14. //对于输入参数，赋值
15. call.setInt( 1, 1);
16. //对于输出参数，声明
17. call.registerOutParameter( 2, OracleTypes.CURSOR);
18. //执行调用
19. call.execute();
20. //将CallableStatement 强转成 OracleCallableStatement 用来获取光标类型Cursor，并得到结果ResultSet
21. rs = ((OracleCallableStatement)call).getCursor( 2);
22. //遍历 ResultSet
23. while (rs.next()) {
24. //根据类型和列名取值
25. int id = rs.getInt( "user_id"); //括号内为 列名
26. String user_name = rs.getString( "user_name");
27. int age = rs.getInt( "user_age");
28. String sex = rs.getString( "user_sex");
29. System.out.println( "查询到的用户信息：\n\tid:"+id+ "\n\t姓名："+user_name
30. + "\n\t年龄："+age+ "\n\t性别："+sex);
31. }
34. } catch (Exception e) {
35. e.printStackTrace();
36. } finally{
37. JDBCUtils.release(conn, call, rs);
38. }
39. }

1. CallableStatement c=conn.prepareCall("{call delePro(?)}");   //删除的存储过程
2.  
3. c.setInt(1,1);  
4.  
5. c.execute();  
6.  
7. c=conn.prepareCall("{call selePro}");   //查询的存储过程
8. ResultSet rs=c.executeQuery();  
9.  
10. while(rs.next())  
11. {  
12. String Stu=rs.getString("StuID");  
13. String name=rs.getString("StuName");  
14. String add=rs.getString("StuAddress");  
15.  
16. System.out.println ("学号:"+" "+"姓名:"+" "+"地址");  
17. System.out.println (Stu+" "+name+" "+add);  
18. }  
19. c.close();  
20. }  
21. }