statementstest.java

在资料浩瀚的互联网中

                this.stmt.executeUpdate(
                    "CREATE PROCEDURE testCallStmt(n INT, x CHAR(16), y INT)" +
                    " WHILE n DO" + "    SET n = n - 1;" +
                    "    INSERT INTO callStmtTbl VALUES (x, y);" +
                    " END WHILE;");

                int rowsToCheck = 15;

                cStmt = this.conn.prepareCall("{call testCallStmt(?,?,?)}");
                cStmt.setInt(1, rowsToCheck);
                cStmt.setString(2, stringVal);
                cStmt.setInt(3, intVal);
                cStmt.execute();

                this.rs = this.stmt.executeQuery("SELECT x,y FROM callStmtTbl");

                int numRows = 0;

                while (this.rs.next()) {
                    assertTrue(this.rs.getString(1).equals(stringVal) &&
                        (this.rs.getInt(2) == intVal));

                    numRows++;
                }

                this.rs.close();
                this.rs = null;

                cStmt.close();
                cStmt = null;

                System.out.println(rowsToCheck + " rows returned");

                assertTrue(numRows == rowsToCheck);
            } finally {
                try {
                    this.stmt.executeUpdate("DROP PROCEDURE testCallStmt");
                } catch (SQLException sqlEx) {
                    if (sqlEx.getMessage().indexOf("does not exist") == -1) {
                        throw sqlEx;
                    }
                }

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

                if (cStmt != null) {
                    cStmt.close();
                }
            }
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @throws SQLException DOCUMENT ME!
     */
    public void testClose() throws SQLException {
        Statement closeStmt = null;
        boolean exceptionAfterClosed = false;

        try {
            closeStmt = this.conn.createStatement();
            closeStmt.close();

            try {
                closeStmt.executeQuery("SELECT 1");
            } catch (SQLException sqlEx) {
                exceptionAfterClosed = true;
            }
        } finally {
            if (closeStmt != null) {
                try {
                    closeStmt.close();
                } catch (SQLException sqlEx) {
                    /* ignore */
                }
            }

            closeStmt = null;
        }

        assertTrue("Operations not allowed on Statement after .close() is called!",
            exceptionAfterClosed);
    }

    /**
     * DOCUMENT ME!
     *
     * @throws SQLException DOCUMENT ME!
     */
    public void testInsert() throws SQLException {
        try {
            boolean autoCommit = this.conn.getAutoCommit();

            // Test running a query for an update. It should fail.
            try {
                this.conn.setAutoCommit(false);
                this.stmt.executeUpdate("SELECT * FROM statement_test");
            } catch (SQLException sqlEx) {
                assertTrue("Exception thrown for unknown reason",
                    sqlEx.getSQLState().equalsIgnoreCase("01S03"));
            } finally {
                this.conn.setAutoCommit(autoCommit);
            }

            // Test running a update for an query. It should fail.
            try {
                this.conn.setAutoCommit(false);
                this.stmt.executeQuery(
                    "UPDATE statement_test SET strdata1='blah' WHERE 1=0");
            } catch (SQLException sqlEx) {
                assertTrue("Exception thrown for unknown reason",
                    sqlEx.getSQLState().equalsIgnoreCase(SQLError.SQL_STATE_ILLEGAL_ARGUMENT));
            } finally {
                this.conn.setAutoCommit(autoCommit);
            }

            for (int i = 0; i < 10; i++) {
                int updateCount = this.stmt.executeUpdate(
                        "INSERT INTO statement_test (strdata1,strdata2) values ('abcdefg', 'poi')");
                assertTrue("Update count must be '1', was '" + updateCount +
                    "'", (updateCount == 1));
            }

            this.stmt.executeUpdate(
                "INSERT INTO statement_test (strdata1, strdata2) values ('a', 'a'), ('b', 'b'), ('c', 'c')");
            this.rs = this.stmt.getGeneratedKeys();

            if (this.rs.next()) {
                this.rs.getInt(1);
            }

            this.rs.close();
            this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID()");

            int updateCountFromServer = 0;

            if (this.rs.next()) {
                updateCountFromServer = this.rs.getInt(1);
            }

            System.out.println("Update count from server: " +
                updateCountFromServer);
        } finally {
            if (this.rs != null) {
                try {
                    this.rs.close();
                } catch (Exception ex) { /* ignore */
                    ;
                }
            }

            this.rs = null;
        }
    }

    /**
     * Tests multiple statement support
     *
     * @throws Exception DOCUMENT ME!
     */
    public void testMultiStatements() throws Exception {
        if (versionMeetsMinimum(4, 1)) {
            Connection multiStmtConn = null;
            Statement multiStmt = null;

            try {
                Properties props = new Properties();
                props.setProperty("allowMultiQueries", "true");

                multiStmtConn = getConnectionWithProps(props);

                multiStmt = multiStmtConn.createStatement();

                multiStmt.executeUpdate(
                    "DROP TABLE IF EXISTS testMultiStatements");
                multiStmt.executeUpdate(
                    "CREATE TABLE testMultiStatements (field1 VARCHAR(255), field2 INT, field3 DOUBLE)");
                multiStmt.executeUpdate(
                    "INSERT INTO testMultiStatements VALUES ('abcd', 1, 2)");

                multiStmt.execute(
                    "SELECT field1 FROM testMultiStatements WHERE field1='abcd';" +
                    "UPDATE testMultiStatements SET field3=3;" +
                    "SELECT field3 FROM testMultiStatements WHERE field3=3");

                this.rs = multiStmt.getResultSet();

                assertTrue(this.rs.next());

                assertTrue("abcd".equals(this.rs.getString(1)));

                // Next should be an update count...
                assertTrue(!multiStmt.getMoreResults());

                assertTrue("Update count was " + multiStmt.getUpdateCount() +
                    ", expected 1", multiStmt.getUpdateCount() == 1);

                assertTrue(multiStmt.getMoreResults());

                this.rs = multiStmt.getResultSet();

                assertTrue(this.rs.next());

                assertTrue(this.rs.getDouble(1) == 3);

                // End of multi results
                assertTrue(!multiStmt.getMoreResults());
                assertTrue(multiStmt.getUpdateCount() == -1);
            } finally {
                if (multiStmt != null) {
                    multiStmt.executeUpdate(
                        "DROP TABLE IF EXISTS testMultiStatements");

                    multiStmt.close();
                }

                if (multiStmtConn != null) {
                    multiStmtConn.close();
                }
            }
        }
    }

    /**
     * Tests that NULLs and '' work correctly.
     *
     * @throws SQLException if an error occurs
     */
    public void testNulls() throws SQLException {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS nullTest");
            this.stmt.executeUpdate(
                "CREATE TABLE IF NOT EXISTS nullTest (field_1 CHAR(20), rowOrder INT)");
            this.stmt.executeUpdate("INSERT INTO nullTest VALUES (null, 1), ('', 2)");

            this.rs = this.stmt.executeQuery(
                    "SELECT field_1 FROM nullTest ORDER BY rowOrder");

            this.rs.next();

            assertTrue("NULL field not returned as NULL",
                (this.rs.getString("field_1") == null) && this.rs.wasNull());

            this.rs.next();

            assertTrue("Empty field not returned as \"\"",
                this.rs.getString("field_1").equals("") && !this.rs.wasNull());

            this.rs.close();
        } finally {
            if (this.rs != null) {
                try {
                    this.rs.close();
                } catch (Exception ex) {
                    // ignore
                }
            }

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

    /**
     * DOCUMENT ME!
     *
     * @throws SQLException DOCUMENT ME!
     */
    public void testPreparedStatement() throws SQLException {
        this.stmt.executeUpdate(
            "INSERT INTO statement_test (id, strdata1,strdata2) values (999,'abcdefg', 'poi')");
        this.pstmt = this.conn.prepareStatement(
                "UPDATE statement_test SET strdata1=?, strdata2=? where id=999");
        this.pstmt.setString(1, "iop");
        this.pstmt.setString(2, "higjklmn");

        //pstmt.setInt(3, 999);
        int updateCount = this.pstmt.executeUpdate();
        assertTrue("Update count must be '1', was '" + updateCount + "'",
            (updateCount == 1));

        this.pstmt.clearParameters();

        this.pstmt.close();

        this.rs = this.stmt.executeQuery(
                "SELECT id, strdata1, strdata2 FROM statement_test");

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 999);
        assertTrue("Expected 'iop', received '" + this.rs.getString(2) + "'",
            "iop".equals(this.rs.getString(2)));
        assertTrue("Expected 'higjklmn', received '" + this.rs.getString(3) +
            "'", "higjklmn".equals(this.rs.getString(3)));
    }

    /**
     * DOCUMENT ME!
     *
     * @throws SQLException DOCUMENT ME!
     */
    public void testPreparedStatementBatch() throws SQLException {
    	this.pstmt = this.conn.prepareStatement("INSERT INTO " +
                "statement_batch_test (strdata1, strdata2) VALUES (?,?)");

        for (int i = 0; i < 1000; i++) {
        	this.pstmt.setString(1, "batch_" + i);
        	this.pstmt.setString(2, "batch_" + i);
        	this.pstmt.addBatch();
        }

        int[] updateCounts = this.pstmt.executeBatch();

        for (int i = 0; i < updateCounts.length; i++) {
            assertTrue("Update count must be '1', was '" + updateCounts[i] +
                "'", (updateCounts[i] == 1));
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @throws SQLException DOCUMENT ME!
     */
    public void testSelectColumns() throws SQLException {
        for (int i = 6; i < MAX_COLUMNS_TO_TEST; i += STEP) {
            long start = System.currentTimeMillis();
            this.rs = this.stmt.executeQuery("SELECT * from statement_col_test_" + i);

            if (this.rs.next()) {
                ;
            }