MySQL Tutorial/Procedure Function/Java

Материал из SQL эксперт
Перейти к: навигация, поиск

Calling a stored procedure in Java

   <source lang="sql">

try {

  Class.forName("com.mysql.jdbc.Driver").newInstance();
  String ConnectionString="jdbc:mysql://" + hostname + "/" + database + "?user=" +
         username + "&password=" + password;
  System.out.println(ConnectionString);
  Connection conn = DriverManager.getConnection(ConnectionString);
  Statement stmt=conn.createStatement();
  stmt.execute("call error_test_proc(1)");

} catch(SQLException SQLEx) {

  System.out.println("MySQL error: "+SQLEx.getErrorCode()+
         " SQLSTATE:" +SQLEx.getSQLState());
  System.out.println(SQLEx.getMessage());

}</source>


Call stored procedure in Java and get the result set

   <source lang="sql">

private void empsInDept(Connection myConnect, int deptId) throws SQLException {

         CallableStatement cStmt = myConnect.prepareCall("{CALL sp_emps_in_dept(?)}");
         cStmt.setInt(1, deptId);
         cStmt.execute();
         ResultSet rs1 = cStmt.getResultSet();
         while (rs1.next()) {
              System.out.println(rs1.getString("department_name") + " "
                        + rs1.getString("location"));
         }
         rs1.close();
         /* process second result set */
         if (cStmt.getMoreResults()) {
              ResultSet rs2 = cStmt.getResultSet();
              while (rs2.next()) {
                   System.out.println(rs2.getInt(1) + " " + rs2.getString(2) + " "
                             + rs2.getString(3));
              }
              rs2.close();
         }
         cStmt.close();
    }</source>