largetabletests.java

来自「weblogic应用全实例」· Java 代码 · 共 338 行

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

/**
 *
 * 这个实例创建一个大表, 获取它的内容然后删除这个表。
 */

public class LargeTableTests
{
  // 声明数据库连接URL. 
  static String url = "jdbc:weblogic:mssqlserver4";             
  
  // Microsoft SQL Server驱动程序名字
  static String driverName = "weblogic.jdbc.mssqlserver4.Driver";
        
  // 插入表的行数
  static int kNumberOfRows = 300;
  
  public static void main(String [] args)
  {
      // 如果有必要改变用户名，密码，主机名和端口
      Properties props = new Properties();
      props.put("user", "sa");
      props.put("password", "secret");
      props.put("server", "myHost:1433");
	//驱动
      Driver myDriver                   = null;
      //连接
      java.sql.Connection conn          = null;
      //声明，SQL对象
      java.sql.Statement stmt           = null;
      //存储过程
      java.sql.CallableStatement cstmt  = null;
      java.sql.PreparedStatement pstmt = null;
      //结果集对象
      java.sql.ResultSet rs             = null;
      
    try
      {
        System.out.println("\n\nLarge table tests...\n");
        
        // 实例JDBC驱动,创建数据库连接
        myDriver = (Driver) Class.forName(driverName).newInstance();
        conn = myDriver.connect(url, props);   
        
        System.out.println("Connected to " + url + " as " + conn + "\n");

        // 创建右两个字段的表,一个整型和一个字符串

        try
          {
          //定义SQL语句
            String sql = "create table largeTable(number int,string varchar(255))";
            System.out.println("Creating largeTable table:\n\n" + sql + "\n");
		//创建SQL对象
           stmt = conn.createStatement();                     

            // 执行SQL语句
            stmt.execute(sql);

            System.out.println("Created table.\n");

            // 关闭声明，释放资源
            stmt.close();
          } catch (SQLException sqle) {
              System.out.println("The table could not be created because " + sqle); 
          }

        // 在表中插入若干行

        int index = 1;
        try
          {
            System.out.println("Inserting rows in the table using a Statement object.");

            // 创建一个计时器
            Timer aTimer = new Timer("Inserted in ");                       
            //创建SQL语句对象
            stmt = conn.createStatement();                     
            for ( ; index <= (kNumberOfRows / 2) ; index++)
              {
                String sql = "insert into largeTable values(" + index + 
                  ",'" + String.valueOf(index) + "')";

                // 执行INSERT语句
                // 应该返回1 (更新的数量).
                int count = stmt.executeUpdate(sql);

                if (count != 1) 
                  throw new SQLException("One row should have been added. Count was " + count + " instead of 1.");
                if (index % 20 == 0) 
                  System.out.println("  added " + index + " rows...");
              }
             //关闭语句对象
            stmt.close();
            
            // 打印时间结果
            System.out.println("\n" + aTimer + "\n");
          } catch (SQLException sqle) {
          //异常处理
              System.out.println("Failed while inserting rows because " + sqle); 
          }

        // 使用PreparedStatement和SQL INSERT命令插入一行


        try
          {
            System.out.println("Inserting entries in the table using a PreparedStatement.\n");
            //声明计时器
            Timer aTimer = new Timer("Inserted in ");
            //创建PreparedStatement对象
            pstmt = conn.prepareStatement("insert into largeTable values(?,?)"); 
            for ( ; index <= kNumberOfRows ; index++)
              {
                // 用一个整数和字串替换参数
                pstmt.setInt(1,index);
                pstmt.setString(2,String.valueOf(index));

                // 执行语句，返回更新次数. 
                int count = pstmt.executeUpdate();

                if (count != 1) 
                //抛出异常
                  throw new SQLException("One row should have been added. Count was " + count + " instead of 1.");
                if (index % 20 == 0) 
                  System.out.println("  added " + index + " rows.");
              }
            System.out.println("\n" + aTimer + "\n");
            //关闭
            pstmt.close();
          } catch (SQLException se) {
          	//异常处理
              System.out.println("Failed while inserting rows because " + se); 
          }

        // 用SELECT语句得到整个表
        try
          {
            System.out.println("Selecting all rows from table using a Statement.\n");
            //声明计时器对象
            Timer aTimer = new Timer("Selected in ");                       
            // 这个相对来说快一些，因为只执行一次
             stmt = conn.createStatement(); 
             // 执行SQL语句
             rs = stmt.executeQuery("select number,string from largeTable order by number");
            for (index = 1 ; rs.next() && index <= kNumberOfRows ; index++) 
              {
                // 从当前行中获取两列
                int number = rs.getInt(1);
                String string = rs.getString(2).trim();

                if (number != index || Integer.parseInt(string) != index)
                  {
                    throw new SQLException("Expecting " + index + ", but got " + 
                                           number + " " + string);
                  }
                if (index % 20 == 0 || index == kNumberOfRows) 
                  System.out.println("  selected " + index + " rows.");
              }
            
            // 检查读取得所有行
            if (index < (kNumberOfRows + 1))
              {
                System.out.println("Only " + index + " rows where selected. Expected " + kNumberOfRows + ".");
              }

            stmt.close();
            System.out.println("\n" + aTimer + "\n");
          } catch (SQLException sqle) {
              System.out.println("Failed while selecting rows because " + sqle); 
          }

        // 给出一个字符串，创建一个存储过程返回一个整型列，每次调用使用存储过程获取多行

        try
          {
          //创建SQL语句对象
            stmt = conn.createStatement();
            //执行创建存储过程语句
            stmt.execute("create procedure " + 
                      "largeTableProcedure(@parm1 integer output, " +
                      "@parm2 varchar(255)) as " + 
                      "select @parm1 = number from largeTable where string = @parm2");
            //关闭
            stmt.close();
            System.out.println("Created stored procedure.\n");

            // 现在使用过程获取表的每行
            // 时间会长一些，因为每行是独自查询的
            System.out.println("Selecting rows from table using CallableStatement.\n");
            //声明计时器对象
            Timer aTimer = new Timer("Selected in ");                       
            //调用存储过程
            cstmt = conn.prepareCall("{call largeTableProcedure(?,?)}");

            // 注册第一个整型参数
            cstmt.registerOutParameter(1, Types.INTEGER);
            for (index = 1 ; index <= kNumberOfRows ; index++)
              {
                // 设置输入参数
                cstmt.setString(2,String.valueOf(index));
                cstmt.execute();
                // 结果
                dumpResults(cstmt);

                // 从过程获取输出参数
                int number = cstmt.getInt(1);

                // 输出参数应该和行号一致
                if (number != index)
                  {
                  	//抛出异常
                    throw new SQLException("Expecting " + index + " got " + number + " instead.");
                  }

                if (index % 20 == 0 || index == kNumberOfRows) 
                  System.out.println("  selected " + index + " rows.");
              }

            System.out.println("\n" + aTimer + "\n");
		//关闭
            cstmt.close();
          } catch(SQLException se) {
          //异常处理
              System.out.println("Failed while selecting rows because " + se); 
          }

        // 从数据库中删除表
        try
          {
          //创建SQL语句对象
            stmt = conn.createStatement();
            //执行SQL语句
            stmt.execute("drop table largeTable");
            stmt.execute("drop procedure largeTableProcedure");
            //关闭
            stmt.close();

            System.out.println("largeTable was dropped.\n");
          } catch(SQLException sqle) {
          //异常处理
              System.out.println("SQL exception raised: " + sqle);
          }
        // 结束测试
      } catch (Exception e) {
      //异常处理
          System.out.println("An exception was raised " + e); 
      } catch (UnknownError e) {
      //异常处理	
        System.out.println("An exception was raised " + e + "\n"); 
        e.printStackTrace(); 
      } finally {
          try {
          	//关闭连接
            if (conn != null)
              conn.close();
          } catch (SQLException sqle) {
          	//异常处理
              System.out.println("SQLException during conn.close(): " + sqle.getMessage());
          }
      }
      
    System.out.println("\n\n(Press Enter to quit)");

    // 等待用户输入
    try 
      { 
        System.in.read(); 
      }

    catch (IOException e) 
      { 
        return; 
      }
  }

  /** 
   * 清空结果集
   *
   * @参数 statement, 语句对象，已经执行过了的
   * @异常 SQLException
   */

  static void dumpResults(Statement statement) throws SQLException
  {
    for (boolean hasMore = true ; hasMore ; )
      {
        ResultSet resultSet = statement.getResultSet();
        if (statement.getMoreResults() == false)
          {
            hasMore = (statement.getUpdateCount() != -1);
          }
      }
  }
}

/**
 * 计时器对象跟踪用去的时间。这个类在LargeTableTests中用到。
 **/

class Timer
{
  /*
   * 创建一个计时器对象记住创建时的时间
   * @参数 iLabel, 描述这个对象的字符串
   **/
  public Timer(String iLabel)
  {
    label = iLabel; 
    time = System.currentTimeMillis();
  }

  String label; 
  long time;

  /** 
   * 返回计时器创建以来的毫秒数
   **/
  long getTime()
  {
    return System.currentTimeMillis() - time;
  }

  /**
   * 返回计时器标签
   **/
  public String toString()
  {
    return label + getTime() + " ms";
  }
}