resultsetregressiontest.java

在资料浩瀚的互联网中

    /**
     * Tests fix for BUG#2006, where 2 columns with same name in a result set
     * are returned via findColumn() in the wrong order...The JDBC spec
     * states,  that the _first_ matching column should be returned.
     *
     * @throws Exception if the test fails
     */
    public void testFixForBug2006() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
            this.stmt.executeUpdate(
                "CREATE TABLE testFixForBug2006_1 (key_field INT NOT NULL)");
            this.stmt.executeUpdate(
                "CREATE TABLE testFixForBug2006_2 (key_field INT NULL)");
            this.stmt.executeUpdate(
                "INSERT INTO testFixForBug2006_1 VALUES (1)");

            this.rs = this.stmt.executeQuery(
                    "SELECT testFixForBug2006_1.key_field, testFixForBug2006_2.key_field FROM testFixForBug2006_1 LEFT JOIN testFixForBug2006_2 USING(key_field)");

            ResultSetMetaData rsmd = this.rs.getMetaData();

            assertTrue(rsmd.getColumnName(1).equals(rsmd.getColumnName(2)));
            assertTrue(rsmd.isNullable(this.rs.findColumn("key_field")) == ResultSetMetaData.columnNoNulls);
            assertTrue(rsmd.isNullable(2) == ResultSetMetaData.columnNullable);
            assertTrue(this.rs.next());
            assertTrue(this.rs.getObject(1) != null);
            assertTrue(this.rs.getObject(2) == null);
        } finally {
            if (this.rs != null) {
                try {
                    this.rs.close();
                } catch (SQLException sqlEx) {
                    // ignore
                }

                this.rs = null;
            }

            this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
        }
    }

    /**
     * Tests that ResultSet.getLong() does not truncate values.
     *
     * @throws Exception if any errors occur
     */
    public void testGetLongBug() throws Exception {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
        this.stmt.executeUpdate(
            "CREATE TABLE IF NOT EXISTS getLongBug (int_col int, bigint_col bigint)");

        int intVal = 123456;
        long longVal1 = 123456789012345678L;
        long longVal2 = -2079305757640172711L;
        this.stmt.executeUpdate("INSERT INTO getLongBug " +
            "(int_col, bigint_col) " + "VALUES (" + intVal + ", " + longVal1 +
            "), " + "(" + intVal + ", " + longVal2 + ")");

        try {
            this.rs = this.stmt.executeQuery(
                    "SELECT int_col, bigint_col FROM getLongBug ORDER BY bigint_col DESC");
            this.rs.next();
            assertTrue("Values not decoded correctly",
                ((this.rs.getInt(1) == intVal) &&
                (this.rs.getLong(2) == longVal1)));
            this.rs.next();
            assertTrue("Values not decoded correctly",
                ((this.rs.getInt(1) == intVal) &&
                (this.rs.getLong(2) == longVal2)));
        } finally {
            if (this.rs != null) {
                try {
                    this.rs.close();
                } catch (Exception ex) {
                    // ignore
                }
            }

            this.stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @throws Exception DOCUMENT ME!
     */
    public void testGetTimestampWithDate() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
            this.stmt.executeUpdate("CREATE TABLE testGetTimestamp (d date)");
            this.stmt.executeUpdate(
                "INSERT INTO testGetTimestamp values (now())");

            this.rs = this.stmt.executeQuery("SELECT * FROM testGetTimestamp");
            this.rs.next();
            System.out.println(this.rs.getTimestamp(1));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
        }
    }

    /**
     * Tests a bug where ResultSet.isBefireFirst() would return true when the
     * result set was empty (which is incorrect)
     *
     * @throws Exception if an error occurs.
     */
    public void testIsBeforeFirstOnEmpty() throws Exception {
        try {
            //Query with valid rows: isBeforeFirst() correctly returns True
            this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'version'");
            assertTrue("Non-empty search should return true", this.rs.isBeforeFirst());

            //Query with empty result: isBeforeFirst() falsely returns True
            //Sun's documentation says it should return false
            this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'garbage'");
            assertTrue("Empty search should return false ", !this.rs.isBeforeFirst());
        } finally {
            this.rs.close();
        }
    }

    /**
     * Tests a bug where ResultSet.isBefireFirst() would return true when the
     * result set was empty (which is incorrect)
     *
     * @throws Exception if an error occurs.
     */
    public void testMetaDataIsWritable() throws Exception {
        try {
            //Query with valid rows
            this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'version'");

            ResultSetMetaData rsmd = this.rs.getMetaData();

            int numColumns = rsmd.getColumnCount();

            for (int i = 1; i <= numColumns; i++) {
                assertTrue("rsmd.isWritable() should != rsmd.isReadOnly()",
                    rsmd.isWritable(i) != rsmd.isReadOnly(i));
            }
        } finally {
            this.rs.close();
        }
    }

    /**
     * Tests fix for bug # 496
     *
     * @throws Exception if an error happens.
     */
    public void testNextAndPrevious() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
            this.stmt.executeUpdate("CREATE TABLE testNextAndPrevious (field1 int)");
            this.stmt.executeUpdate("INSERT INTO testNextAndPrevious VALUES (1)");

            this.rs = this.stmt.executeQuery("SELECT * from testNextAndPrevious");

            System.out.println("Currently at row " + this.rs.getRow());
            this.rs.next();
            System.out.println("Value at row " + this.rs.getRow() + " is " +
                this.rs.getString(1));

            this.rs.previous();

            try {
                System.out.println("Value at row " + this.rs.getRow() + " is " +
                    this.rs.getString(1));
                fail(
                    "Should not be able to retrieve values with invalid cursor");
            } catch (SQLException sqlEx) {
                assertTrue(sqlEx.getMessage().startsWith("Before start"));
            }

            this.rs.next();

            this.rs.next();

            try {
                System.out.println("Value at row " + this.rs.getRow() + " is " +
                    this.rs.getString(1));
                fail(
                    "Should not be able to retrieve values with invalid cursor");
            } catch (SQLException sqlEx) {
                assertTrue(sqlEx.getMessage().startsWith("After end"));
            }
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
        }
    }

    /**
     * Tests fix for BUG#1630 (not updatable exception turning into NPE on
     * second updateFoo() method call.
     *
     * @throws Exception if an unexpected exception is thrown.
     */
    public void testNotUpdatable() throws Exception {
        this.rs = null;

        try {
            String sQuery = "SHOW VARIABLES";
            this.pstmt = this.conn.prepareStatement(sQuery,
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            this.rs = this.pstmt.executeQuery();

            if (this.rs.next()) {
                this.rs.absolute(1);

                try {
                    this.rs.updateInt(1, 1);
                } catch (SQLException sqlEx) {
                    assertTrue(sqlEx instanceof NotUpdatable);
                }

                try {
                    this.rs.updateString(1, "1");
                } catch (SQLException sqlEx) {
                    assertTrue(sqlEx instanceof NotUpdatable);
                }
            }
        } finally {
            if (this.pstmt != null) {
                try {
                	this.pstmt.close();
                } catch (Exception e) {
                    // ignore
                }
            }
        }
    }

    /**
     * Tests that streaming result sets are registered correctly.
     *
     * @throws Exception if any errors occur
     */
    public void testStreamingRegBug() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
            this.stmt.executeUpdate("CREATE TABLE StreamingRegBug ( DUMMYID " +
                " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
            this.stmt.executeUpdate(
                "INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
            this.stmt.executeUpdate(
                "INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
            this.stmt.executeUpdate(
                "INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
            this.stmt.executeUpdate(
                "INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");

            PreparedStatement streamStmt = null;

            try {
                streamStmt = this.conn.prepareStatement("SELECT DUMMYID, DUMMYNAME " +
                        "FROM StreamingRegBug ORDER BY DUMMYID",
                        java.sql.ResultSet.TYPE_FORWARD_ONLY,
                        java.sql.ResultSet.CONCUR_READ_ONLY);
                streamStmt.setFetchSize(Integer.MIN_VALUE);

                this.rs = streamStmt.executeQuery();

                while (this.rs.next()) {
                    this.rs.getString(1);
                }

                this.rs.close(); // error occurs here
            } catch (SQLException sqlEx) {
            	
            } finally {
                if (streamStmt != null) {
                	try {
                		streamStmt.close();
                	} catch (SQLException exWhileClose) {
                		exWhileClose.printStackTrace();
                	}
                }
            }
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
        }
    }

    /**
     * Tests that result sets can be updated when all parameters are correctly
     * set.
     *
     * @throws Exception if any errors occur
     */
    public void testUpdatability() throws Exception {
        this.rs = null;

        this.stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
        this.stmt.execute("CREATE TABLE IF NOT EXISTS updatabilityBug (" +
            " id int(10) unsigned NOT NULL auto_increment," +
            " field1 varchar(32) NOT NULL default ''," +
            " field2 varchar(128) NOT NULL default ''," +
            " field3 varchar(128) default NULL," +
            " field4 varchar(128) default NULL," +
            " field5 varchar(64) default NULL," +
            " field6 int(10) unsigned default NULL," +
            " field7 varchar(64) default NULL," + " PRIMARY KEY  (id)" +
            ") TYPE=InnoDB;");
        this.stmt.executeUpdate("insert into updatabilityBug (id) values (1)");

        try {
            String sQuery = " SELECT * FROM updatabilityBug WHERE id = ? ";
            this.pstmt = this.conn.prepareStatement(sQuery,
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            this.conn.setAutoCommit(false);
            this.pstmt.setInt(1, 1);
            this.rs = this.pstmt.executeQuery();

            if (this.rs.next()) {
                this.rs.absolute(1);
                this.rs.updateInt("id", 1);
                this.rs.updateString("field1", "1");
                this.rs.updateString("field2", "1");
                this.rs.updateString("field3", "1");
                this.rs.updateString("field4", "1");
                this.rs.updateString("field5", "1");
                this.rs.updateInt("field6", 1);
                this.rs.updateString("field7", "1");
                this.rs.updateRow();
            }

            this.conn.commit();
            this.conn.setAutoCommit(true);
        } finally {
            if (this.pstmt != null) {
                try {
                	this.pstmt.close();
                } catch (Exception e) {
                    // ignore
                }
            }

            this.stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
        }
    }

    /**
     * Test fixes for BUG#1071
     *
     * @throws Exception if the test fails.
     */
    public void testUpdatabilityAndEscaping() throws Exception {
        Properties props = new Properties();
        props.setProperty("useUnicode", "true");
        props.setProperty("characterEncoding", "big5");

        Connection updConn = getConnectionWithProps(props);
        Statement updStmt = updConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        try {
            updStmt.executeUpdate(
                "DROP TABLE IF EXISTS testUpdatesWithEscaping");
            updStmt.executeUpdate(
                "CREATE TABLE testUpdatesWithEscaping (field1 INT PRIMARY KEY, field2 VARCHAR(64))");
            updStmt.executeUpdate(
                "INSERT INTO testUpdatesWithEscaping VALUES (1, null)");

            String stringToUpdate = "\" \\ '";