storedprocs.java

weblogic应用全实例

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

/**
 * 这个实例演示怎样创建、调用和清除Microsoft SQL Server.的存储过程。
 */
public class storedprocs {
  
  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;
    // 设置用户名、密码和服务器名
    Properties props = new Properties();
    props.put("user", "sa");
    props.put("password", "secret");
    props.put("server", "myHOST:1433");

    try {
    	// 实例驱动程序
      Driver myDriver = (Driver)
          Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
      //建立连接
      conn = myDriver.connect("jdbc:weblogic:mssqlserver4", props);
      //创建SQL语句对象
      stmt = conn.createStatement();
      //执行存储过程语句
      //删除过程proc_squareInt
      try {stmt.execute("drop procedure proc_squareInt");} catch (SQLException e) {;}
      //删除过程func_squareInt
      try {stmt.execute("drop procedure func_squareInt");} catch (SQLException e) {;}
      //删除过程proc_getResults
      try {stmt.execute("drop procedure proc_getResults");} catch (SQLException e) {;}
      //关闭SQL语句对象
      stmt.close();
    
      // 创建存储过程
      stmt = conn.createStatement();
      //执行创建存储过程proc_squareInt
      stmt.execute("create procedure proc_squareInt (@field1 int, @field2 int output) as " + 
                  "begin select @field2 = @field1 * @field1 end");
     //关闭SQL语句对象
      stmt.close();
    
      cstmt = conn.prepareCall("{call proc_squareInt(?, ?)}");
      //注册输出参数2
      cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
      for (int i = 0; i < 100; i++) {
        cstmt.setInt(1, i);
        cstmt.execute();
        System.out.println(i + " " + cstmt.getInt(2));
      }
      //关闭
      cstmt.close();
    
      // 创建一个过程，有一个返回值
      stmt = conn.createStatement();
      stmt.execute("create procedure func_squareInt (@field1 int) as " +
                  "begin return @field1 * @field1 end");
      stmt.close();
    
      cstmt = conn.prepareCall("{? = call func_squareInt(?)}");
    //注册输出参数1
      cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
      for (int i = 0; i < 100; i++) {
        cstmt.setInt(2, i);
        cstmt.execute();
        System.out.println(i + " " + cstmt.getInt(1));
      }
      cstmt.close();
    
    // 创建一个过程，有多个结果
      stmt = conn.createStatement();
      stmt.execute("create procedure proc_getResults as " +
                  "begin select name from sysusers \n" +
                  "select gid from sysusers end");
      stmt.close();
    
      cstmt = conn.prepareCall("{call proc_getResults()}");
    //打印结果
      boolean hasResultSet = cstmt.execute();
      while (hasResultSet) {
        rs = cstmt.getResultSet();
        while (rs.next())
          System.out.println("Value: " + rs.getString(1));
        rs.close();
        hasResultSet = cstmt.getMoreResults();
      }
    } catch (Exception e) {
    //异常处理
        System.out.println("Exception was thrown: " + e.getMessage());
    } finally {
        try {
          if (cstmt != null)
            cstmt.close();
          if (stmt != null)
            stmt.close();
          if (conn != null)
            conn.close();
         } catch (SQLException sqle) {
             System.out.println("SQLException was thrown: " + sqle.getMessage());
         }
     }
  }
}