testbatchupdates.java

来自「非常棒的java数据库」· Java 代码 · 共 511 行 · 第 1/2 页

/*
 * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
 * (license2)
 * Initial Developer: H2 Group
 */
package org.h2.test.jdbc;

import java.io.ByteArrayOutputStream;
import java.io.PrintStream;
import java.sql.BatchUpdateException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.h2.test.TestBase;

/**
 * Test for batch updates.
 */
public class TestBatchUpdates extends TestBase {

    static final String COFFEE_UPDATE = "UPDATE TEST SET PRICE=PRICE*20 WHERE TYPE_ID=?";
    static final String COFFEE_SELECT = "SELECT PRICE FROM TEST WHERE KEY_ID=?";
    static final String COFFEE_QUERY = "SELECT C_NAME,PRICE FROM TEST WHERE TYPE_ID=?";
    static final String COFFEE_DELETE = "DELETE FROM TEST WHERE KEY_ID=?";
    static final String COFFEE_INSERT1 = "INSERT INTO TEST VALUES(9,'COFFEE-9',9.0,5)";
    static final String COFFEE_DELETE1 = "DELETE FROM TEST WHERE KEY_ID=9";
    static final String COFFEE_UPDATE1 = "UPDATE TEST SET PRICE=PRICE*20 WHERE TYPE_ID=1";
    static final String COFFEE_SELECT1 = "SELECT PRICE FROM TEST WHERE KEY_ID>4";
    static final String COFFEE_UPDATE_SET = "UPDATE TEST SET KEY_ID=?, C_NAME=? WHERE C_NAME=?";
    static final String COFFEE_SELECT_CONTINUED = "SELECT COUNT(*) FROM TEST WHERE C_NAME='Continue-1'";

    int coffeeSize = 10;
    int coffeeType = 11;
    Connection conn;
    Statement stat;
    PreparedStatement prep;

    public void test() throws Exception {
        testExecuteCall();
        testException();
        testCoffee();
    }

    private void testExecuteCall() throws Exception {
        deleteDb("batchUpdates");
        Connection conn = getConnection("batchUpdates");
        Statement 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 i = 0; i < updateCounts.length; i++) {
            total += updateCounts[i];
        }
        check(4, total);
        conn.close();
    }

    public static int updatePrices(String s, double f) {
        return (int) f;
    }

    private void testException() throws Exception {
        deleteDb("batchUpdates");
        Connection conn = getConnection("batchUpdates");
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int primary key)");
        PreparedStatement prep = conn.prepareStatement("insert into test values(?)");
        for (int i = 0; i < 700; i++) {
            prep.setString(1, "x");
            prep.addBatch();
        }
        try {
            prep.executeBatch();
        } catch (BatchUpdateException e) {
            PrintStream temp = System.err;
            try {
                ByteArrayOutputStream buff = new ByteArrayOutputStream();
                PrintStream p = new PrintStream(buff);
                System.setErr(p);
                e.printStackTrace();
            } finally {
                System.setErr(temp);
            }
        }
        conn.close();
    }

    private void testCoffee() throws Exception {
        deleteDb("batchUpdates");
        this.conn = getConnection("batchUpdates");
        stat = conn.createStatement();
        DatabaseMetaData meta = conn.getMetaData();
        if (!meta.supportsBatchUpdates()) {
            error("does not support BatchUpdates");
        }
        stat.executeUpdate("CREATE TABLE TEST(KEY_ID INT PRIMARY KEY,"
                + "C_NAME VARCHAR(255),PRICE DECIMAL(20,2),TYPE_ID INT)");
        String newName = null;
        float newPrice = 0;
        int newType = 0;
        prep = conn.prepareStatement("INSERT INTO TEST VALUES(?,?,?,?)");
        int newKey = 1;
        for (int i = 1; i <= coffeeType && newKey <= coffeeSize; i++) {
            for (int j = 1; j <= i && newKey <= coffeeSize; j++) {
                newName = "COFFEE-" + newKey;
                newPrice = newKey + (float) .00;
                newType = i;
                prep.setInt(1, newKey);
                prep.setString(2, newName);
                prep.setFloat(3, newPrice);
                prep.setInt(4, newType);
                prep.execute();
                newKey = newKey + 1;
            }
        }
        trace("Inserted the Rows ");
        testAddBatch01();
        testAddBatch02();
        testClearBatch01();
        testClearBatch02();
        testExecuteBatch01();
        testExecuteBatch02();
        testExecuteBatch03();
        testExecuteBatch04();
        testExecuteBatch05();
        testExecuteBatch06();
        testExecuteBatch07();
        testContinueBatch01();

        conn.close();
    }

    public void testAddBatch01() throws Exception {
        trace("testAddBatch01");
        int i = 0;
        int[] retValue = { 0, 0, 0 };
        String s = COFFEE_UPDATE;
        trace("Prepared Statement String:" + s);
        prep = conn.prepareStatement(s);
        prep.setInt(1, 2);
        prep.addBatch();
        prep.setInt(1, 3);
        prep.addBatch();
        prep.setInt(1, 4);
        prep.addBatch();
        int[] updateCount = prep.executeBatch();
        int updateCountLen = updateCount.length;

        // PreparedStatement p;
        // p = conn.prepareStatement(COFFEE_UPDATE);
        // p.setInt(1,2);
        // System.out.println("upc="+p.executeUpdate());
        // p.setInt(1,3);
        // System.out.println("upc="+p.executeUpdate());
        // p.setInt(1,4);
        // System.out.println("upc="+p.executeUpdate());

        trace("updateCount length:" + updateCountLen);
        if (updateCountLen != 3) {
            error("updateCount: " + updateCountLen);
        } else {
            trace("addBatch add the SQL statements to Batch ");
        }
        String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=2";
        String query2 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=3";
        String query3 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=4";
        ResultSet rs = stat.executeQuery(query1);
        rs.next();
        retValue[i++] = rs.getInt(1);
        rs = stat.executeQuery(query2);
        rs.next();
        retValue[i++] = rs.getInt(1);
        rs = stat.executeQuery(query3);
        rs.next();
        retValue[i++] = rs.getInt(1);
        for (int j = 0; j < updateCount.length; j++) {
            trace("UpdateCount:" + updateCount[j]);
            check(updateCount[j], retValue[j]);
        }
    }

    public void testAddBatch02() throws Exception {
        trace("testAddBatch02");
        int i = 0;
        int[] retValue = { 0, 0, 0 };
        int updCountLength = 0;
        String sUpdCoffee = COFFEE_UPDATE1;
        String sDelCoffee = COFFEE_DELETE1;
        String sInsCoffee = COFFEE_INSERT1;
        stat.addBatch(sUpdCoffee);
        stat.addBatch(sDelCoffee);
        stat.addBatch(sInsCoffee);
        int[] updateCount = stat.executeBatch();
        updCountLength = updateCount.length;
        trace("updateCount Length:" + updCountLength);
        if (updCountLength != 3) {
            error("addBatch " + updCountLength);
        } else {
            trace("addBatch add the SQL statements to Batch ");
        }
        String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
        ResultSet rs = stat.executeQuery(query1);
        rs.next();
        retValue[i++] = rs.getInt(1);
        // 1 as delete Statement will delete only one row
        retValue[i++] = 1;
        // 1 as insert Statement will insert only one row
        retValue[i++] = 1;
        trace("ReturnValue count : " + retValue.length);
        for (int j = 0; j < updateCount.length; j++) {
            trace("Update Count:" + updateCount[j]);
            trace("Returned Value : " + retValue[j]);
            if (updateCount[j] != retValue[j]) {
                error("j=" + j + " right:" + retValue[j]);
            }
        }
    }

    public void testClearBatch01() throws Exception {
        trace("testClearBatch01");
        String sPrepStmt = COFFEE_UPDATE;
        trace("Prepared Statement String:" + sPrepStmt);
        prep = conn.prepareStatement(sPrepStmt);
        prep.setInt(1, 2);
        prep.addBatch();
        prep.setInt(1, 3);
        prep.addBatch();
        prep.setInt(1, 4);
        prep.addBatch();
        prep.clearBatch();
        int[] updateCount = prep.executeBatch();
        int updCountLength = updateCount.length;
        if (updCountLength == 0) {
            trace("clearBatch Method clears the current Batch ");
        } else {
            error("clearBatch " + updCountLength);
        }
    }

    public void testClearBatch02() throws Exception {
        trace("testClearBatch02");
        int updCountLength = 0;
        String sUpdCoffee = COFFEE_UPDATE1;
        String sInsCoffee = COFFEE_INSERT1;
        String sDelCoffee = COFFEE_DELETE1;