statementstest.java

MySql Java Connector

            stmt.executeUpdate(
                "INSERT INTO statement_test (strdata1) values ('blah')");

            int autoIncKeyFromApi = -1;
            rs = stmt.getGeneratedKeys();

            if (rs.next()) {
                autoIncKeyFromApi = rs.getInt(1);
            } else {
                fail(
                    "Failed to retrieve AUTO_INCREMENT using Statement.getGeneratedKeys()");
            }

            rs.close();

            int autoIncKeyFromFunc = -1;
            rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");

            if (rs.next()) {
                autoIncKeyFromFunc = rs.getInt(1);
            } else {
                fail("Failed to retrieve AUTO_INCREMENT using LAST_INSERT_ID()");
            }

            if ((autoIncKeyFromApi != -1) && (autoIncKeyFromFunc != -1)) {
                assertTrue("Key retrieved from API (" + autoIncKeyFromApi
                    + ") does not match key retrieved from LAST_INSERT_ID() "
                    + autoIncKeyFromFunc + ") function",
                    autoIncKeyFromApi == autoIncKeyFromFunc);
            } else {
                fail("AutoIncrement keys were '0'");
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) { /* ignore */
                    ;
                }
            }

            rs = null;
        }
    }

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

        try {
            closeStmt = 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 = conn.getAutoCommit();

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

            // Test running a update for an query. It should fail.
            try {
                conn.setAutoCommit(false);
                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 {
                conn.setAutoCommit(autoCommit);
            }

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

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

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

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

            int updateCountFromServer = 0;

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

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

            rs = null;
        }
    }

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

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

            rs.next();

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

            rs.next();

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

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

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

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

        int updateCount = pstmt.executeUpdate();
        assertTrue("Update count must be '1', was '" + updateCount + "'",
            (updateCount == 1));
    }

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

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

        int[] updateCounts = 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();
            rs = stmt.executeQuery("SELECT * from statement_col_test_" + i);

            if (rs.next()) {
                ;
            }

            long end = System.currentTimeMillis();
            System.out.println(i + " columns = " + (end - start));
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @throws SQLException DOCUMENT ME!
     */
    public void testStubbed() throws SQLException {
        try {
            stmt.getResultSetHoldability();
        } /* ignore */catch (NotImplemented notImplEx) {
            ;
        }
    }
}