Beispiel #1
0
 /**
  * Test batched callable statements where the call includes literal parameters which prevent the
  * use of RPC calls.
  */
 public void testCallStmtBatch2() throws Exception {
   dropProcedure("jTDS_PROC");
   try {
     Statement stmt = con.createStatement();
     stmt.execute("create table #testbatch (id int, data varchar(255))");
     stmt.execute(
         "create proc jTDS_PROC @p1 varchar(10), @p2 varchar(255) as "
             + "INSERT INTO #testbatch VALUES (convert(int, @p1), @p2)");
     CallableStatement cstmt = con.prepareCall("{call jTDS_PROC (?, 'literal parameter')}");
     for (int i = 0; i < 5; i++) {
       if (i == 2) {
         cstmt.setString(1, "XXX");
       } else {
         cstmt.setString(1, Integer.toString(i));
       }
       cstmt.addBatch();
     }
     int x[];
     try {
       x = cstmt.executeBatch();
     } catch (BatchUpdateException e) {
       x = e.getUpdateCounts();
     }
     if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
       assertEquals(5, x.length);
       assertEquals(1, x[0]);
       assertEquals(1, x[1]);
       assertEquals(EXECUTE_FAILED, x[2]);
       assertEquals(EXECUTE_FAILED, x[3]);
       assertEquals(EXECUTE_FAILED, x[4]);
     } else {
       assertEquals(5, x.length);
       assertEquals(1, x[0]);
       assertEquals(1, x[1]);
       assertEquals(EXECUTE_FAILED, x[2]);
       assertEquals(1, x[3]);
       assertEquals(1, x[4]);
     }
     // Now without errors
     stmt.execute("TRUNCATE TABLE #testbatch");
     for (int i = 0; i < 5; i++) {
       cstmt.setString(1, Integer.toString(i));
       cstmt.addBatch();
     }
     try {
       x = cstmt.executeBatch();
     } catch (BatchUpdateException e) {
       x = e.getUpdateCounts();
     }
     assertEquals(5, x.length);
     assertEquals(1, x[0]);
     assertEquals(1, x[1]);
     assertEquals(1, x[2]);
     assertEquals(1, x[3]);
     assertEquals(1, x[4]);
   } finally {
     dropProcedure("jTDS_PROC");
   }
 }
Beispiel #2
0
 /** Test batched callable statements where the call has no parameters. */
 public void testCallStmtNoParams() throws Exception {
   dropProcedure("jTDS_PROC");
   try {
     Statement stmt = con.createStatement();
     stmt.execute("create table #testbatch (id numeric(10) identity, data varchar(255))");
     stmt.execute(
         "create proc jTDS_PROC  as " + "INSERT INTO #testbatch (data) VALUES ('same each time')");
     CallableStatement cstmt = con.prepareCall("{call jTDS_PROC}");
     for (int i = 0; i < 5; i++) {
       cstmt.addBatch();
     }
     int x[];
     try {
       x = cstmt.executeBatch();
     } catch (BatchUpdateException e) {
       x = e.getUpdateCounts();
     }
     assertEquals(5, x.length);
     assertEquals(1, x[0]);
     assertEquals(1, x[1]);
     assertEquals(1, x[2]);
     assertEquals(1, x[3]);
     assertEquals(1, x[4]);
   } finally {
     dropProcedure("jTDS_PROC");
   }
 }
Beispiel #3
0
 private void testExecuteCall() throws SQLException {
   deleteDb("batchUpdates");
   conn = getConnection("batchUpdates");
   stat = conn.createStatement();
   stat.execute("CREATE ALIAS updatePrices FOR \"" + getClass().getName() + ".updatePrices\"");
   CallableStatement call = conn.prepareCall("{call updatePrices(?, ?)}");
   call.setString(1, "Hello");
   call.setFloat(2, 1.4f);
   call.addBatch();
   call.setString(1, "World");
   call.setFloat(2, 3.2f);
   call.addBatch();
   int[] updateCounts = call.executeBatch();
   int total = 0;
   for (int t : updateCounts) {
     total += t;
   }
   assertEquals(4, total);
   conn.close();
 }
  public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    try {
      Driver drv = new com.mysql.jdbc.Driver();
      DriverManager.registerDriver(drv);
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3308/tests", "root", "123456");
      conn.setAutoCommit(false); // 设置事务非自动提�?

      pstmt = conn.prepareStatement("select * from student");
      rs = pstmt.executeQuery();
      conn.commit(); // 主动提交事务

      System.out.println("callablestatement批量添加前:");
      System.out.println("id\tname\tage");
      while (rs.next()) {
        System.out.print(rs.getInt(1) + "\t");
        System.out.print(rs.getString(2) + "\t");
        System.out.print(rs.getInt(3) + "\n");
      }

      // 批量更新
      cstmt = conn.prepareCall("{call proc_insert_test(?,?)}");
      cstmt.setString(1, "xiaoming1");
      cstmt.setInt(2, 33);
      cstmt.addBatch();

      cstmt.setString(1, "xiaoming2");
      cstmt.setInt(2, 13);
      cstmt.addBatch();

      int count[] = cstmt.executeBatch();
      conn.commit(); // 主动提交事务

      System.out.println("callablestatement批量更新返回�?");
      for (int i = 0; i < count.length; i++) {
        System.out.print(count[i] + ",\t");
      }
      System.out.println();

      pstmt = conn.prepareStatement("select * from student");
      rs = pstmt.executeQuery();
      conn.commit(); // 主动提交事务

      System.out.println("callablestatement批量添加�?");
      System.out.println("id\tname\tage");
      while (rs.next()) {
        System.out.print(rs.getInt(1) + "\t");
        System.out.print(rs.getString(2) + "\t");
        System.out.print(rs.getInt(3) + "\n");
      }

    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (pstmt != null) {
          pstmt.close();
        }
        if (cstmt != null) {
          cstmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
 /** @see java.sql.Statement#executeBatch() */
 public int[] executeBatch() throws SQLException {
   return original.executeBatch();
 }