complexproc.java

weblogic应用全实例

//声明本类包含在包examples.jdbc.mssqlserver4中
package examples.jdbc.mssqlserver4;
//声明本类要引入的其他包和类
import java.io.*;
import java.util.*;
import java.math.*;
import java.lang.*;
import java.net.*;
import java.sql.*;

/**
 * 这个实例演示从复杂存储过程重获取行、更新计数、过程状态和输出参数。
 */

public class complexproc
{
  public static void main(String argv[])
  {//声明驱动变量、连接、声明和CallableStatement声明
    java.sql.Connection conn         = null; 
    java.sql.Statement stmt          = null;
    java.sql.CallableStatement cstmt = null;
    java.sql.ResultSet rs            = null;

    try {
    	// 设置用户名、密码和服务器名
      java.util.Properties props = new java.util.Properties();
      props.put("user",     "sa");
      props.put("password", "secret");
      props.put("server",   "myHOST:1433");
     // 加载驱动程序
      Driver myDriver = (Driver)    
          Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
     //获取连接
      conn = myDriver.connect("jdbc:weblogic:mssqlserver4", props);
     //声明过程语句
      String setup_proc =
        " if exists (select * from sysobjects where id = "
        + " object_id('dbo.sp_myname') and sysstat & 0xf = 4) "
        + " drop procedure dbo.sp_myname";
    
      String big_proc =
        " create procedure sp_myname @aname varchar(30) output as "
        + " begin                                                   "
        + "    declare @string varchar(30)                          "
        + "    select @aname = 'no name'                            "
        + "    declare @tmp_return_code INT                         "
        + "    declare str_cursor cursor for                        "
        + "       select name from master.dbo.sysdatabases          "
        + "       for read only                                     "
        + "    IF @@ERROR != 0                                      "
        + "    begin                                                "
        + "        RETURN @@ERROR                                   "
        + "    end                                                  "
        + "    open str_cursor                                      "
        + "    IF @@ERROR != 0                                      "
        + "    begin                                                "
        + "      RETURN @@ERROR                                     "
        + "    end                                                  "
        + "                                                         "
        + "    fetch str_cursor into @string                        "
        + "                                                         "
        + "    SELECT @tmp_return_code = @@ERROR                    "
        + "    IF @tmp_return_code != 0                             "
        + "    begin                                                "
        + "        GOTO WAY_OUT                                     "
        + "    end                                                  "
        + "                                                         "
        + "    WHILE (@@fetch_status = 0)                           "
        + "      BEGIN                                              "
        + "         FETCH str_cursor INTO @string                   "
        + "                                                         "
        + "          IF @@fetch_status = -1                         "
        + "          begin                                          "
        + "            select @tmp_return_code = 88                 "
        + "            GOTO WAY_OUT                                 "
        + "          end                                            "
        + "                                                         "
        + "          IF @@fetch_status = -2                         "
        + "          begin                                          "
        + "            select @tmp_return_code = 77                 "
        + "            GOTO WAY_OUT                                 "
        + "          end                                            "
        + "                                                         "
        + "          select @aname = @string                        "
        + "          SELECT @tmp_return_code = @@ERROR              "
        + "          IF @tmp_return_code != 0                       "
        + "          begin                                          "
        + "              RETURN @tmp_return_code                    "
        + "          end                                            "
        + "                                                         "
        + "       END                                               "
        + "                                                         "
        + " WAY_OUT:                                                "
        + "   CLOSE str_cursor                                      "
        + "   DEALLOCATE  str_cursor                                "
        + "   select @aname                                         "
        + "   return @tmp_return_code                               "
        + "                                                         "
        + " end                                                     ";
    
    	//创建语句对象
      stmt = conn.createStatement();
      //执行过程setup_proc
      stmt.executeUpdate ( setup_proc );
      System.out.println("setup_proc executed\n");
      //执行过程big_proc
      stmt.executeUpdate ( big_proc );
      cstmt = conn.prepareCall("{ ? = call sp_myname(?)}");
      //注册输出参数
      cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
      cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
      //执行
      boolean hasResultSet = cstmt.execute();
    
      while (true)
      {
      	//获取结果集
        rs    = cstmt.getResultSet();
        //获取更新次数
	int updateCount = cstmt.getUpdateCount();
	
        // 如果没有结果和更新次数，则调出循环
        if (rs == null && updateCount == -1)
          break; 
      
        // 检查是否有结果集
        if (rs != null) {
          while (rs.next()) {
            System.out.println("Get first col by id:" + rs.getString(1));
          }
          rs.close();
        } // Otherwise, there will be an update count
        else {
          System.out.println("Update count = " + cstmt.getUpdateCount());
        }
        cstmt.getMoreResults();
      }
    
      System.out.println( "Output status: " + cstmt.getInt(1));
      System.out.println( "Output param:  " + cstmt.getString(2));

	} catch (Exception e) {
	//异常处理
        System.out.println("Exception was trown: " + e.getMessage());
    } finally {
    	//关闭所有结果集，语句声明对象，连接
        try {
          if (rs != null)
            rs.close();
          if (cstmt != null)
            cstmt.close();
          if (stmt != null)
            stmt.close();
          if (conn != null)
            conn.close();
       } catch (SQLException sqle) {
       	//异常处理
           System.out.println("SQLException during close(): " + sqle.getMessage());
       }
    }
  }
}