attila66

JDBC CallableStatement Procedure ResultSet

May 5th, 2020
138
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 0.90 KB | None | 0 0
  1.     public void test(String pCity) {
  2.         String connURL = "jdbc:mysql://localhost:3306/test";
  3.         String callQuery = "{CALL MyProcedure(?)}";
  4.         try (Connection conn = DriverManager.getConnection(connURL,"***", "***");
  5.              CallableStatement cstmt = conn.prepareCall(callQuery))
  6.         {
  7.             cstmt.setString(1,pCity);
  8.             cstmt.execute();
  9.             try(ResultSet rs = cstmt.getResultSet()) {
  10.                 while (rs.next()) {
  11.                     System.out.println(rs.getInt("id")+" "
  12.                         +rs.getString("name")+" "
  13.                         +rs.getString("city"));
  14.                 }
  15.             }
  16.         } catch (SQLException ex) {
  17.             System.out.println(ex);
  18.         }
  19.     }
  20.  
  21. // MySQL Stored Procedure:
  22. DELIMITER $$
  23. CREATE PROCEDURE MyProcedure(IN pCity CHAR(15))
  24. BEGIN
  25.   SELECT * FROM person WHERE city=pCity;
  26. END$$
  27. DELIMITER ;
Add Comment
Please, Sign In to add comment