statementregressiontest.java

在资料浩瀚的互联网中

     * @deprecated uses deprecated methods of Date class
     */
    public void testBug3103() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug3103 (field1 DATETIME)");

            PreparedStatement pStmt = this.conn.prepareStatement(
                    "INSERT INTO testBug3103 VALUES (?)");

            java.util.Date utilDate = new java.util.Date();

            pStmt.setObject(1, utilDate);
            pStmt.executeUpdate();

            this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug3103");
            this.rs.next();

            java.util.Date retrUtilDate = new java.util.Date(this.rs.getTimestamp(
                        1).getTime());

            // We can only compare on the day/month/year hour/minute/second 
            // interval, because the timestamp has added milliseconds to the
            // internal date...
            assertTrue("Dates not equal",
                (utilDate.getMonth() == retrUtilDate.getMonth()) &&
                (utilDate.getDate() == retrUtilDate.getDate()) &&
                (utilDate.getYear() == retrUtilDate.getYear()) &&
                (utilDate.getHours() == retrUtilDate.getHours()) &&
                (utilDate.getMinutes() == retrUtilDate.getMinutes()) &&
                (utilDate.getSeconds() == retrUtilDate.getSeconds()));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
        }
    }

    /**
     * Tests fix for BUG#3520
     *
     * @throws Exception ...
     */
    public void testBug3520() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS t");
            this.stmt.executeUpdate("CREATE TABLE t (s1 int,primary key (s1))");
            this.stmt.executeUpdate("INSERT INTO t VALUES (1)");
            this.stmt.executeUpdate("INSERT INTO t VALUES (1)");
        } catch (SQLException sqlEx) {
            System.out.println(sqlEx.getSQLState());
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS t");
        }
    }

    /**
     * Test fix for BUG#3557 -- UpdatableResultSet not picking up default
     * values
     *
     * @throws Exception if test fails.
     */
    public void testBug3557() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3557");

            this.stmt.executeUpdate("CREATE TABLE testBug3557 ( " +
                "`a` varchar(255) NOT NULL default 'XYZ', " +
                "`b` varchar(255) default '123', " + "PRIMARY KEY  (`a`))");

            Statement updStmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            this.rs = updStmt.executeQuery("SELECT * FROM testBug3557");

            assertTrue(this.rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE);

            this.rs.moveToInsertRow();

            assertTrue("XYZ".equals(this.rs.getObject(1)));
            assertTrue("123".equals(this.rs.getObject(2)));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3557");
        }
    }

    /**
     * Tests fix for BUG#3620 -- Timezone not respected correctly.
     *
     * @throws SQLException if the test fails.
     */
    public void testBug3620() throws SQLException {
        long epsillon = 3000; // 3 seconds time difference

        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug3620 (field1 TIMESTAMP)");

            PreparedStatement tsPstmt = this.conn.prepareStatement(
                    "INSERT INTO testBug3620 VALUES (?)");

            Calendar pointInTime = Calendar.getInstance();
            pointInTime.set(2004, 02, 29, 10, 0, 0);

            long pointInTimeOffset = pointInTime.getTimeZone().getRawOffset();

            java.sql.Timestamp ts = new java.sql.Timestamp(pointInTime.getTime()
                                                                      .getTime());

            tsPstmt.setTimestamp(1, ts);
            tsPstmt.executeUpdate();

            String tsValueAsString = getSingleValue("testBug3620", "field1",
                    null).toString();

            System.out.println("Timestamp as string with no calendar: " +
                tsValueAsString.toString());

            Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

            this.stmt.executeUpdate("DELETE FROM testBug3620");

            Properties props = new Properties();
            props.put("useTimezone", "true");
            //props.put("serverTimezone", "UTC");

            Connection tzConn = getConnectionWithProps(props);

            Statement tsStmt = tzConn.createStatement();

            tsPstmt = tzConn.prepareStatement(
                    "INSERT INTO testBug3620 VALUES (?)");

            tsPstmt.setTimestamp(1, ts, cal);
            tsPstmt.executeUpdate();

            tsValueAsString = getSingleValue("testBug3620", "field1", null)
                                  .toString();

            Timestamp tsValueAsTimestamp = (Timestamp) getSingleValue("testBug3620",
                    "field1", null);

            System.out.println("Timestamp as string with UTC calendar: " +
                tsValueAsString.toString());
            System.out.println("Timestamp as Timestamp with UTC calendar: " +
                tsValueAsTimestamp);

            this.rs = tsStmt.executeQuery("SELECT field1 FROM testBug3620");
            this.rs.next();

            Timestamp tsValueUTC = this.rs.getTimestamp(1, cal);

            //
            // We use this testcase with other vendors, JDBC spec
            // requires result set fields can only be read once,
            // although MySQL doesn't require this ;)
            //
            this.rs = tsStmt.executeQuery("SELECT field1 FROM testBug3620");
            this.rs.next();

            Timestamp tsValueStmtNoCal = this.rs.getTimestamp(1);

            System.out.println(
                "Timestamp specifying UTC calendar from normal statement: " +
                tsValueUTC.toString());

            PreparedStatement tsPstmtRetr = tzConn.prepareStatement(
                    "SELECT field1 FROM testBug3620");

            this.rs = tsPstmtRetr.executeQuery();
            this.rs.next();

            Timestamp tsValuePstmtUTC = this.rs.getTimestamp(1, cal);

            System.out.println(
                "Timestamp specifying UTC calendar from prepared statement: " +
                tsValuePstmtUTC.toString());

            //
            // We use this testcase with other vendors, JDBC spec
            // requires result set fields can only be read once,
            // although MySQL doesn't require this ;)
            //
            this.rs = tsPstmtRetr.executeQuery();
            this.rs.next();

            Timestamp tsValuePstmtNoCal = this.rs.getTimestamp(1);

            System.out.println(
                "Timestamp specifying no calendar from prepared statement: " +
                tsValuePstmtNoCal.toString());

            long stmtDeltaTWithCal = (ts.getTime() -
                tsValueStmtNoCal.getTime());

            long deltaOrig = Math.abs(stmtDeltaTWithCal - pointInTimeOffset);
            
            assertTrue(
                "Difference between original timestamp and timestamp retrieved using java.sql.Statement " +
                "set in database using UTC calendar is not ~= " + epsillon +
                ", it is actually " + deltaOrig,
                (deltaOrig < epsillon));

            long pStmtDeltaTWithCal = (ts.getTime() -
                tsValuePstmtNoCal.getTime());

            System.out.println(Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) +
                " < " + epsillon +
                (Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) < epsillon));
            assertTrue(
                "Difference between original timestamp and timestamp retrieved using java.sql.PreparedStatement " +
                "set in database using UTC calendar is not ~= " + epsillon +
                ", it is actually " + pStmtDeltaTWithCal,
                (Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) < epsillon));

            System.out.println(
                "Difference between original ts and ts with no calendar: " +
                (ts.getTime() - tsValuePstmtNoCal.getTime()) +
                ", offset should be " + pointInTimeOffset);
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
        }
    }

    /**
     * Tests that DataTruncation is thrown when data is truncated.
     *
     * @throws Exception if the test fails.
     */
    public void testBug3697() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug3697 (field1 VARCHAR(255))");

            StringBuffer updateBuf = new StringBuffer(
                    "INSERT INTO testBug3697 VALUES ('");

            for (int i = 0; i < 512; i++) {
                updateBuf.append("A");
            }

            updateBuf.append("')");

            try {
                this.stmt.executeUpdate(updateBuf.toString());
            } catch (DataTruncation dtEx) {
                // This is an expected exception....
            }

            SQLWarning warningChain = this.stmt.getWarnings();

            System.out.println(warningChain);
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
        }
    }

    /**
     * Tests fix for BUG#3804, data truncation on server should throw
     * DataTruncation exception.
     *
     * @throws Exception if the test fails
     */
    public void testBug3804() throws Exception {
        if (versionMeetsMinimum(4, 1)) {
            try {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
                this.stmt.executeUpdate(
                    "CREATE TABLE testBug3804 (field1 VARCHAR(5))");

                boolean caughtTruncation = false;

                try {
                    this.stmt.executeUpdate(
                        "INSERT INTO testBug3804 VALUES ('1234567')");
                } catch (DataTruncation truncationEx) {
                    caughtTruncation = true;
                    System.out.println(truncationEx);
                }

                assertTrue("Data truncation exception should've been thrown",
                    caughtTruncation);
            } finally {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
            }
        }
    }

    /**
     * Tests BUG#3873 - PreparedStatement.executeBatch() not returning all
     * generated keys (even though that's not JDBC compliant).
     *
     * @throws Exception if the test fails
     */
    public void testBug3873() throws Exception {
        PreparedStatement batchStmt = null;

        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug3873 (keyField INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dataField VARCHAR(32))");
            batchStmt = this.conn.prepareStatement("INSERT INTO testBug3873 (dataField) VALUES (?)",
                    Statement.RETURN_GENERATED_KEYS);
            batchStmt.setString(1, "abc");
            batchStmt.addBatch();
            batchStmt.setString(1, "def");
            batchStmt.addBatch();
            batchStmt.setString(1, "ghi");
            batchStmt.addBatch();

            int[] updateCounts = batchStmt.executeBatch();

            this.rs = batchStmt.getGeneratedKeys();

            while (this.rs.next()) {
                System.out.println(this.rs.getInt(1));
            }

            this.rs = batchStmt.getGeneratedKeys();
            assertTrue(this.rs.next());
            assertTrue(1 == this.rs.getInt(1));
            assertTrue(this.rs.next());
            assertTrue(2 == this.rs.getInt(1));
            assertTrue(this.rs.next());
            assertTrue(3 == this.rs.getInt(1));
            assertTrue(!this.rs.next());
        } finally {
            if (batchStmt != null) {
                batchStmt.close();
            }

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

    /**
     * Tests that you can close a statement twice without an NPE.
     *
     * @throws Exception if an error occurs.
     */
    public void testCloseTwice() throws Exception {
        Statement closeMe = this.conn.createStatement();
        closeMe.close();
        closeMe.close();
    }

    /**
     * Tests all forms of statements influencing getGeneratedKeys().
     *
     * @throws Exception if the test fails.
     */
    public void testGetGeneratedKeysAllCases() throws Exception {
        System.out.println("Using Statement.executeUpdate()\n");

        try {
            createGGKTables();

            //Do the tests
            for (int i = 0; i < tests.length; i++) {
                doGGKTestStatement(tests[i], true);
            }
        } finally {
            dropGGKTables();
        }

        nextID = 1;
        count = 0;

        System.out.println("Using Statement.execute()\n");

        try {
            createGGKTables();

            //Do the tests
            for (int i = 0; i < tests.length; i++) {
                doGGKTestStatement(tests[i], false);