testjdbcsavepoints.java

hsql是很有名的嵌入式数据库

/* Copyright (c) 2001-2005, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


package org.hsqldb.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Savepoint;
import java.sql.Statement;

import org.hsqldb.WebServer;

import junit.framework.TestCase;
import junit.framework.TestResult;

/**
 * Tests JDBC java.sql.Savepoint support in context of new engine SQL-savepoint
 * support and new HSQL protocol extensions for savepoint support. <p>
 *
 * @author boucher@users
 * @version 1.7.2
 * @since 1.7.2
 */
public class TestJDBCSavepoints extends TestCase {

//  You change the url and serverProps to reflect your preferred settings
    // String serverProps = "database.0=mem:test;silent=false;trace=true" // debugging
    String serverProps = "database.0=mem:test;silent=true;trace=false";

    //String     url         = "jdbc:hsqldb:hsql://localhost";
    String     url = "jdbc:hsqldb:http://localhost";
    String     user;
    String     password;
    Statement  stmt;
    Connection conn1;
    Connection conn2;

    // Server server;
    // this exercises everything:
    // the engine and JDBC savepoint support,
    // the new HSQL protocol and tunneling HSQL protocol over HTTP
    WebServer server;

    public TestJDBCSavepoints(String name) {
        super(name);
    }

    protected void setUp() {

        user     = "sa";
        password = "";
        stmt     = null;
        conn1    = null;
        conn2    = null;

        // server   = new Server();
        server = new WebServer();

        server.putPropertiesFromString(serverProps);
        server.setLogWriter(null);
        server.start();

        try {
            Class.forName("org.hsqldb.jdbcDriver");

            conn1 = DriverManager.getConnection(url, user, password);
            conn2 = DriverManager.getConnection(url, user, password);
            stmt  = conn1.createStatement();
        } catch (Exception e) {

            //e.printStackTrace();
            System.out.println(this + ".setUp() error: " + e.getMessage());
        }
    }

    protected void tearDown() {

        try {
            conn1.close();
        } catch (Exception e) {

            //e.printStackTrace();
            System.out.println(this + ".tearDown() error: " + e.getMessage());
        }

        try {
            conn2.close();
        } catch (Exception e) {

            //e.printStackTrace();
            System.out.println(this + ".tearDown() error: " + e.getMessage());
        }

        server.stop();
    }

    public void testJDBCSavepoints() throws Exception {

        String            sql;
        String            msg;
        int               i;
        PreparedStatement ps;
        ResultSet         rs;
        Savepoint         sp1;
        Savepoint         sp2;
        Savepoint         sp3;
        Savepoint         sp4;
        Savepoint         sp5;
        Savepoint         sp6;
        Savepoint         sp7;
        int               rowcount = 0;

        sql = "drop table t if exists";

        stmt.executeUpdate(sql);

        sql = "create table t(id int, fn varchar, ln varchar, zip int)";

        stmt.executeUpdate(sql);
        conn1.setAutoCommit(true);

        //-- Test 1 : The execution of an SQL savepoint statement shall
        //            raise an exception in the absence of an active
        //            enclosing transaction
        // fredt@users - there is always an active transaction when autocommit
        // is true. The transaction is committed automatically if the next
        // Statement.execute() or similar call is performed successfully.
/*
        msg = "savepoint set successfully in the abscence of an active transaction";
        try {
            conn.setSavepoint("savepoint1");
            assertTrue(msg,false);
        } catch (Exception e) {}

*/

        //-- setup for following tests
        conn1.setAutoCommit(false);

        sql = "insert into t values(?,?,?,?)";
        ps  = conn1.prepareStatement(sql);

        ps.setString(2, "Mary");
        ps.setString(3, "Peterson-Clancy");

        i = 0;

        for (; i < 10; i++) {
            ps.setInt(1, i);
            ps.setInt(4, i);
            ps.executeUpdate();
        }

        sp1 = conn1.setSavepoint("savepoint1");

        for (; i < 20; i++) {
            ps.setInt(1, i);
            ps.setInt(4, i);
            ps.executeUpdate();
        }

        sp2 = conn1.setSavepoint("savepoint2");

        for (; i < 30; i++) {
            ps.setInt(1, i);
            ps.setInt(4, i);
            ps.executeUpdate();
        }

        sp3 = conn1.setSavepoint("savepoint3");

        for (; i < 40; i++) {
            ps.setInt(1, i);
            ps.setInt(4, i);
            ps.executeUpdate();
        }

        sp4 = conn1.setSavepoint("savepoint4");

        for (; i < 50; i++) {
            ps.setInt(1, i);
            ps.setInt(4, i);
            ps.executeUpdate();
        }

        sp5 = conn1.setSavepoint("savepoint5");
        sp6 = conn1.setSavepoint("savepoint6");
        sp7 = conn1.setSavepoint("savepoint7");
        rs  = stmt.executeQuery("select count(*) from t");

        rs.next();

        rowcount = rs.getInt(1);

        rs.close();

        //-- Test 2 : count of rows matches # rows inserted (assertion req'd by
        //            following tests, but not directly related to the feature
        //            being tested)
        msg = "select count(*) from t value";

        try {
            assertEquals(msg, 50, rowcount);
        } catch (Exception e) {}

        conn2.setAutoCommit(false);
        conn2.setSavepoint("savepoint1");
        conn2.setSavepoint("savepoint2");

        //-- test 3 : A JDBC Savepoint shall be considered invalid if used to
        //            release an SQL-savepoint in an SQL-session other than that
        //            of the originating Connection object
        msg = "savepoint released succesfully on non-originating connection";

        try {
            conn2.releaseSavepoint(sp2);
            assertTrue(msg, false);
        } catch (Exception e) {}

        //-- test 4 : A JDBC Savepoint shall be invalid if used to roll back to
        //            an SQL-savepoint in an SQL-session other than that of the
        //            originating Connection object
        try {
            conn2.rollback(sp1);

            msg = "succesful rollback to savepoint on "