callabletest.java

JAVA编程百例书中各章节的所有例子的源代码,包括套接字编程

package ch03.section06;

import java.sql.*;

class CallableTest {

  public static void main(String argv[]) {

    try {

      // register the JDBC classes you might use
      // are not using.

      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); // JDBC-ODBC bridge

      String url = "jdbc:odbc:test";

      // the user might have passed in a user name or password,

      String user, pwd;
      user = "sa";
      pwd = "sa";

      // make a connection to the specified URL

      Connection con = DriverManager.getConnection(url, user, pwd);
      if (con == null) {
        System.out.println("数据库连接不成功");
      }
      else {
        System.out.println("数据库连接成功");
      }
      // get a Statement object from the Connection

      Statement stmt = con.createStatement();

      // ignore any exception for DROP PROCEDURE;
      // it will most assuredly throw one if
      // the table does not exist

      try {
        stmt.executeUpdate("DROP PROCEDURE test_sp");
      }
      catch (Exception e) {
        System.out.println("程序开始删除存储过程不成功");
      }

      // create a stored proc

      stmt.executeUpdate("CREATE PROCEDURE test_sp " +
                         "  @id1 INT, " +
                         "  @id2 INT " +
                         "AS " +
                         "BEGIN " +
                         "  SELECT id,name FROM test WHERE id = @id1  or id =@id2 " +
                         "END");

      // set up a call to that stored proc

      String sql = "{call test_sp(?, ?)}";
      CallableStatement cstmt = con.prepareCall(sql);
      // get the name for id1 = 1
      cstmt.setInt(1, 1);
      // get the name for id2 =2
      cstmt.setInt(2, 2);

      // execute the stored procedure

      cstmt.executeQuery();

      ResultSet rs = cstmt.getResultSet();
      ResultSetMetaData rsmd = rs.getMetaData();
      for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        System.out.print(rsmd.getColumnName(i) + "\t");
      }
      System.out.println("\n----------------------------------------");
      while (rs.next()) {
        System.out.print(rs.getString(1) + "\t");
        System.out.print(rs.getString(2) + "\t" + "\n");
      }
      cstmt.close();
      stmt.close();
      con.close();

      System.out.println("成功取得数据");
      try {
        stmt.executeUpdate("DROP PROCEDURE test_sp");
      }
      catch (Exception e) {
        System.out.println(e.toString());
        System.out.println("程序结束删除存储过程不成功,这是因为 close()");
      }
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
      e.printStackTrace();
    }
  }

}