resultsettest.java

来自「jtds的源码 是你学习java的好东西」· Java 代码 · 共 1,736 行 · 第 1/5 页

                "create table #insertRowNotVisible (val int primary key)");
        ResultSet rs = stmt.executeQuery("select * from #insertRowNotVisible");

        for (int i = 1; i <= rows; i++) {
            rs.moveToInsertRow();
            rs.updateInt(1, i);
            rs.insertRow();
            rs.moveToCurrentRow();
            rs.last();
            assertEquals(i, rs.getRow());
        }

        rs.close();
        stmt.close();
    }

    /**
     * Test that updated rows are marked as deleted and the new values inserted
     * at the end of the <code>ResultSet</code> if the primary key is updated.
     */
    public void testUpdateRowDuplicatesRow() throws Exception
    {
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        stmt.executeUpdate(
                "create table #updateRowDuplicatesRow (val int primary key)");
        stmt.executeUpdate(
                "insert into #updateRowDuplicatesRow (val) values (1)");
        stmt.executeUpdate(
                "insert into #updateRowDuplicatesRow (val) values (2)");
        stmt.executeUpdate(
                "insert into #updateRowDuplicatesRow (val) values (3)");

        ResultSet rs = stmt.executeQuery(
                "select val from #updateRowDuplicatesRow order by val");

        for (int i = 0; i < 3; i++) {
            assertTrue(rs.next());
            assertFalse(rs.rowUpdated());
            assertFalse(rs.rowInserted());
            assertFalse(rs.rowDeleted());
            rs.updateInt(1, rs.getInt(1) + 10);
            rs.updateRow();
            assertFalse(rs.rowUpdated());
            assertFalse(rs.rowInserted());
            assertTrue(rs.rowDeleted());
        }

        for (int i = 11; i <= 13; i++) {
            assertTrue(rs.next());
            assertFalse(rs.rowUpdated());
            assertFalse(rs.rowInserted());
            assertFalse(rs.rowDeleted());
            assertEquals(i, rs.getInt(1));
        }

        rs.close();
        stmt.close();
    }

    /**
     * Test that updated rows are modified in place if the primary key is not
     * updated.
     */
    public void testUpdateRowUpdatesRow() throws Exception
    {
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        stmt.executeUpdate(
                "create table #updateRowUpdatesRow (id int primary key, val int)");
        stmt.executeUpdate(
                "insert into #updateRowUpdatesRow (id, val) values (1, 1)");
        stmt.executeUpdate(
                "insert into #updateRowUpdatesRow (id, val) values (2, 2)");
        stmt.executeUpdate(
                "insert into #updateRowUpdatesRow (id, val) values (3, 3)");

        ResultSet rs = stmt.executeQuery(
                "select id, val from #updateRowUpdatesRow order by id");

        for (int i = 0; i < 3; i++) {
            assertTrue(rs.next());
            assertFalse(rs.rowUpdated());
            assertFalse(rs.rowInserted());
            assertFalse(rs.rowDeleted());
            rs.updateInt(2, rs.getInt(2) + 10);
            rs.updateRow();
            assertFalse(rs.rowUpdated());
            assertFalse(rs.rowInserted());
            assertFalse(rs.rowDeleted());
            assertEquals(rs.getInt(1) + 10, rs.getInt(2));
        }

        assertFalse(rs.next());

        rs.close();
        stmt.close();
    }

    /**
     * Test that deleted rows are not removed but rather marked as deleted.
     */
    public void testDeleteRowMarksDeleted() throws Exception
    {
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        stmt.executeUpdate(
                "create table #deleteRowMarksDeleted (val int primary key)");
        stmt.executeUpdate(
                "insert into #deleteRowMarksDeleted (val) values (1)");
        stmt.executeUpdate(
                "insert into #deleteRowMarksDeleted (val) values (2)");
        stmt.executeUpdate(
                "insert into #deleteRowMarksDeleted (val) values (3)");

        ResultSet rs = stmt.executeQuery(
                "select val from #deleteRowMarksDeleted order by val");

        for (int i = 0; i < 3; i++) {
            assertTrue(rs.next());
            assertFalse(rs.rowUpdated());
            assertFalse(rs.rowInserted());
            assertFalse(rs.rowDeleted());
            rs.deleteRow();
            assertFalse(rs.rowUpdated());
            assertFalse(rs.rowInserted());
            assertTrue(rs.rowDeleted());
        }

        assertFalse(rs.next());
        rs.close();
        stmt.close();
    }

    /**
     * Test for bug [1170777] resultSet.updateRow() fails if no row has been
     * changed.
     */
    public void testUpdateRowNoChanges() throws Exception {
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        stmt.executeUpdate(
                "create table #deleteRowMarksDeleted (val int primary key)");
        stmt.executeUpdate(
                "insert into #deleteRowMarksDeleted (val) values (1)");

        ResultSet rs = stmt.executeQuery(
                "select val from #deleteRowMarksDeleted order by val");
        assertTrue(rs.next());
        // This should not crash; it should be a no-op
        rs.updateRow();
        rs.refreshRow();
        assertEquals(1, rs.getInt(1));
        assertFalse(rs.next());

        rs.close();
        stmt.close();
    }

    /**
     * Test the behavior of <code>sp_cursorfetch</code> with fetch sizes
     * greater than 1.
     * <p>
     * <b>Assertions tested:</b>
     * <ul>
     *   <li>The <i>current row</i> is always the first row returned by the
     *     last fetch, regardless of what fetch type was used.
     *   <li>Row number parameter is ignored by fetch types other than absolute
     *     and relative.
     *   <li>Refresh fetch type simply reruns the previous request (it ignores
     *     both row number and number of rows) and will not affect the
     *     <i>current row</i>.
     *   <li>Fetch next returns the packet of rows right after the last row
     *     returned by the last fetch (regardless of what type of fetch that
     *     was).
     *   <li>Fetch previous returns the packet of rows right before the first
     *     row returned by the last fetch (regardless of what type of fetch
     *     that was).
     *   <li>If a fetch previous tries to read before the start of the
     *     <code>ResultSet</code> the requested number of rows is returned,
     *     starting with row 1 and the error code returned is non-zero (2).
     * </ul>
     */
    public void testCursorFetch() throws Exception
    {
        int rows = 10;
        Statement stmt = con.createStatement();
        stmt.executeUpdate(
                "create table #testCursorFetch (id int primary key, val int)");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement(
                "insert into #testCursorFetch (id, val) values (?, ?)");
        for (int i = 1; i <= rows; i++) {
            pstmt.setInt(1, i);
            pstmt.setInt(2, i);
            pstmt.executeUpdate();
        }
        pstmt.close();

        //
        // Open cursor
        //
        CallableStatement cstmt = con.prepareCall(
                "{?=call sp_cursoropen(?, ?, ?, ?, ?)}");
        // Return value (OUT)
        cstmt.registerOutParameter(1, Types.INTEGER);
        // Cursor handle (OUT)
        cstmt.registerOutParameter(2, Types.INTEGER);
        // Statement (IN)
        cstmt.setString(3, "select * from #testCursorFetch order by id");
        // Scroll options (INOUT)
        cstmt.setInt(4, 1); // Keyset driven
        cstmt.registerOutParameter(4, Types.INTEGER);
        // Concurrency options (INOUT)
        cstmt.setInt(5, 2); // Scroll locks
        cstmt.registerOutParameter(5, Types.INTEGER);
        // Row count (OUT)
        cstmt.registerOutParameter(6, Types.INTEGER);

        ResultSet rs = cstmt.executeQuery();
        assertEquals(2, rs.getMetaData().getColumnCount());
        assertFalse(rs.next());

        assertEquals(0, cstmt.getInt(1));
        int cursor = cstmt.getInt(2);
        assertEquals(1, cstmt.getInt(4));
        assertEquals(2, cstmt.getInt(5));
        assertEquals(rows, cstmt.getInt(6));

        cstmt.close();

        //
        // Play around with fetch
        //
        cstmt = con.prepareCall("{?=call sp_cursorfetch(?, ?, ?, ?)}");
        // Return value (OUT)
        cstmt.registerOutParameter(1, Types.INTEGER);
        // Cursor handle (IN)
        cstmt.setInt(2, cursor);
        // Fetch type (IN)
        cstmt.setInt(3, 2); // Next row
        // Row number (INOUT)
        cstmt.setInt(4, 1); // Only matters for absolute and relative fetching
        // Number of rows (INOUT)
        cstmt.setInt(5, 2); // Read 2 rows

        // Fetch rows 1-2 (current row is 1)
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertTrue(rs.next());
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch rows 3-4 (current row is 3)
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertTrue(rs.next());
        assertEquals(4, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Refresh rows 3-4 (current row is 3)
        cstmt.setInt(3, 0x80); // Refresh
        cstmt.setInt(4, 2);    // Try to refresh only 2nd row (will be ignored)
        cstmt.setInt(5, 1);    // Try to refresh only 1 row (will be ignored)
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertTrue(rs.next());
        assertEquals(4, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch rows 5-6 (current row is 5)
        cstmt.setInt(3, 2); // Next
        cstmt.setInt(4, 1); // Row number 1
        cstmt.setInt(5, 2); // Get 2 rows
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertTrue(rs.next());
        assertEquals(6, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch previous rows (3-4) (current row is 3)
        cstmt.setInt(3, 4); // Previous
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(4, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Refresh rows 3-4 (current row is 3)
        cstmt.setInt(3, 0x80); // Refresh
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(4, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch previous rows (1-2) (current row is 1)
        cstmt.setInt(3, 4); // Previous
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch next rows (3-4) (current row is 3)
        cstmt.setInt(3, 2); // Next
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(4, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch first rows (1-2) (current row is 1)
        cstmt.setInt(3, 1); // First
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch last rows (9-10) (current row is 9)