JDBC连接数据库中CallableStatement执行有参存储过程及注解其他
Oracle的建有参存储过程的过程
procedure pro_01(v_01 in number,v_02 out varchar2) as begin select name into v_02 from test where id = v_01; exception when no_data_found then dbms_output.put_line('no data'); when too_many_rows then dbms_output.put_line('too many rows'); end;
JDBC调用存储过程 并取得输出参数 注解代码部分为PreparedStatement对象的更新和查询
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class CallableTest { public static void main(String[] args){ try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String user = "scott"; String password = "tiger"; Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; try { conn = DriverManager.getConnection(url,user,password); // String sql = "select * from test"; // String sql ="update test set id = ? where id = ?"; // cstmt = conn.prepareStatement(sql); // rs = cstmt.executeQuery();//查询操作 返回结果集 // cstmt.setInt(1, 13); // cstmt.setInt(2, 12); // int count = cstmt.executeUpdate();//更新操作 返回受影响行数 String sql = "{call pro_01(?,?)}";//执行存储过程sql语句{call procedure_name} cstmt = conn.prepareCall(sql); cstmt.setInt(1, 13); cstmt.registerOutParameter(2, Types.VARCHAR);//注册输出参数 boolean isExecute = cstmt.execute();//只有执行结果为结果集时才会返回true String out = cstmt.getString(2);//获取输出参数,以注入位置为准提取 System.out.println(out); if(isExecute){ System.out.println("Result"); }else{ System.out.println("success"); } // if( rs != null){ //// while (rs.next()) { //// System.out.println(rs.getInt(1)); //// } // System.out.println("what is wrong"); // }else{ // System.out.println("execute over"); // } // System.out.println(count); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(rs != null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(cstmt != null){ try { cstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }