sprocs.java

weblogic应用全实例

//声明本类包含在包examples.jdbc.informix4中
package examples.jdbc.informix4;
//声明本类要引入的其他包和类
import java.sql.*;
import java.util.Properties;

/**
 *
 * 这个实例演示怎样使用INFORMIX创建和执行存储过程
 */
public class sprocs {

  /**
   * 从命令行运行这个实例
   */
  public static void main(String argv[]) 
  {
  //声明连接、Statement和PreparedStatement变量	
    java.sql.Connection conn         = null;
    java.sql.Statement stmt          = null;
    java.sql.CallableStatement cstmt = null;
    try {
      // 设置属性
      Properties props = new Properties();
      props.put("user",     "informix");
      props.put("password", "secret");
      props.put("server",   "myDBHost"); // Informix服务器运行的主机名
      props.put("port",     "1493");       // Informix服务器的监听端口
      props.put("db",       "myDB");  // Informix服务器数据库名
    //加载驱动程序
      Driver myDriver = (Driver) Class.forName("weblogic.jdbc.informix4.Driver").newInstance();
       //建立连接
      conn = myDriver.connect("jdbc:weblogic:informix4", props);

      // 创建Statement
      stmt = conn.createStatement();

      try {
        // 删除表
        stmt.execute("drop table demo");
        System.out.println("Table demo dropped.");
      }
      catch (SQLException e) {
      	//表不存在
        System.out.println("Table demo doesn't need to be dropped.");
      }

      try {
        // 删除过程sp_demo
        stmt.execute("drop procedure sp_demo");
        System.out.println("procedure sp_demo dropped.");
      }
      catch (SQLException e) {
      	//过程sp_demo不存在
        System.out.println("procedure sp_demo doesn't need to be dropped.");
      }

      // 创建表
      stmt.execute("create table demo " +
        " ( int_col INT, v_col VARCHAR(5) )");
      get_line("Table demo created."+ " (press ENTER to continue...)");

      // 这个查询结果不是一个ResultSet,而是插入新表的行数
      int numrows = stmt.executeUpdate("insert into demo " +
        " values ( 1, '11111' )");
      get_line("Number of rows inserted = " + numrows + " (press ENTER to continue...)");

      numrows = stmt.executeUpdate("insert into demo " +
        " values ( 2, '22222' )");
      get_line("Number of rows inserted = " + numrows + " (press ENTER to continue...)");

      // 创建存储过程
      numrows = stmt.executeUpdate("create procedure sp_demo(col1 INT, col2 VARCHAR(5)) " +
        "RETURNING INT; " +
        "DEFINE temp_int int; " +
        "insert into demo values(col1, col2); " +
        "let temp_int = col1; " +
        "return temp_int; " +
        "END PROCEDURE;");

      // 准备Sproc调用
      cstmt = conn.prepareCall("{ call sp_demo('6', '66666')}");

      // 执行sproc
      boolean retbool = cstmt.execute();

      if (retbool == true) {
        get_line("sproc returned result set" + " (press ENTER to continue...)");
        ResultSet rrs = cstmt.getResultSet();
        if (rrs.next()) {
          String rrs_col1 = rrs.getString(1);
          get_line("retrieved result =" + rrs_col1 + " (press ENTER to continue...)");
        } else {
          get_line("expected to get result from sproc !");
        }
      } else {
        get_line("sproc did not return result set" + " (press ENTER to continue...)");
      }
    } catch (Exception e) {
    	//异常处理
        System.out.println("Exception was thrown: " + e.getMessage());
    } finally {
        // 关闭连结
        try {
          if (stmt != null)
            stmt.close();
          if (cstmt != null)
            cstmt.close();
          if (conn != null)
            conn.close();
        } catch (SQLException sqle) {
            System.out.println("SQLException was thrown: " + sqle.getMessage());
        }
    }
  }
    //从命令行窗口读入一行
    private static void get_line(String s)
  {
    int key;
    //输入提示
    System.out.print(s);
    System.out.flush();
    try
      {
        while ((key=System.in.read()) != 10){}
      }
    catch (Exception ex){}
  }

}