resultsettest.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

        // Create a cursor ResultSet
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        // Set maxRows to 5
        stmt.setMaxRows(5);

        // Select all (should only return 5 rows)
        ResultSet rs = stmt.executeQuery("select * from #cursorMaxRows");
        rs.last();
        assertEquals(5, rs.getRow());
        rs.beforeFirst();

        int cnt = 0;
        while (rs.next()) {
            cnt++;
        }
        assertEquals(5, cnt);

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

    /**
     * Test for bug [1075977] <code>setObject()</code> causes SQLException.
     * <p>
     * Conversion of <code>float</code> values to <code>String</code> adds
     * grouping to the value, which cannot then be parsed.
     */
    public void testSetObjectScale() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("create table #testsetobj (i int)");
        PreparedStatement pstmt =
                con.prepareStatement("insert into #testsetobj values(?)");
        // next line causes sqlexception
        pstmt.setObject(1, new Float(1234.5667), Types.INTEGER, 0);
        assertEquals(1, pstmt.executeUpdate());
        ResultSet rs = stmt.executeQuery("select * from #testsetobj");
        assertTrue(rs.next());
        assertEquals("1234", rs.getString(1));
    }

    /**
     * Test that <code>ResultSet.previous()</code> works correctly on cursor
     * <code>ResultSet</code>s.
     */
    public void testCursorPrevious() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #cursorPrevious (val int)");
        stmt.close();

        // Insert 10 rows
        PreparedStatement pstmt = con.prepareStatement(
                "insert into #cursorPrevious (val) values (?)");
        for (int i = 0; i < 10; i++) {
            pstmt.setInt(1, i);
            assertEquals(1, pstmt.executeUpdate());
        }
        pstmt.close();

        // Create a cursor ResultSet
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        // Set fetch size to 2
        stmt.setFetchSize(2);

        // Select all
        ResultSet rs = stmt.executeQuery("select * from #cursorPrevious");
        rs.last();
        int i = 10;
        do {
            assertEquals(i, rs.getRow());
            assertEquals(--i, rs.getInt(1));
        } while (rs.previous());
        assertTrue(rs.isBeforeFirst());
        assertEquals(0, i);

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

    /**
     * Test the behavior of the ResultSet/Statement/Connection when the JVM
     * runs out of memory (hopefully) in the middle of a packet.
     * <p/>
     * Previously jTDS was not able to close a ResultSet/Statement/Connection
     * after an OutOfMemoryError because the input stream pointer usually
     * remained inside a packet and further attempts to dump the rest of the
     * response failed because of "protocol confusions".
     */
    public void testOutOfMemory() throws SQLException {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #testOutOfMemory (val binary(8000))");

        // Insert a 8KB value
        byte[] val = new byte[8000];
        PreparedStatement pstmt = con.prepareStatement(
                "insert into #testOutOfMemory (val) values (?)");
        pstmt.setBytes(1, val);
        assertEquals(1, pstmt.executeUpdate());
        pstmt.close();

        // Create a list and keep adding rows to it until we run out of memory
        // Most probably this will happen in the middle of a row packet, when
        // jTDS tries to allocate the array, after reading the data length
        ArrayList results = new ArrayList();
        ResultSet rs = null;
        try {
            while (true) {
                rs = stmt.executeQuery("select val from #testOutOfMemory");
                assertTrue(rs.next());
                results.add(rs.getBytes(1));
                assertFalse(rs.next());
                rs.close();
                rs = null;
            }
        } catch (OutOfMemoryError err) {
            results = null;
            if (rs != null) {
                // This used to fail, because the parser got confused
                rs.close();
            }
        }

        // Make sure the Statement still works
        rs = stmt.executeQuery("select 1");
        assertTrue(rs.next());
        assertFalse(rs.next());
        rs.close();
        stmt.close();
    }

    /**
     * Test for bug [1182066] regression bug resultset: relative() not working
     * as expected.
     */
    public void testRelative() throws Exception {
        final int ROW_COUNT = 99;

        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #test2 (i int primary key, v varchar(100))");
        for (int i = 1; i <= ROW_COUNT; i++) {
            stmt.executeUpdate("insert into #test2 (i, v) values (" + i + ", 'This is a test')");
        }
        stmt.close();

        String sql = "select * from #test2";

        PreparedStatement pstmt = con.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        pstmt.setFetchSize(10);

        ResultSet rs = pstmt.executeQuery();

        int resCnt = 0;

        if (rs.next()) {
            do {
                assertEquals(++resCnt, rs.getInt(1));
            } while (rs.relative(1));
        }
        assertEquals(ROW_COUNT, resCnt);

        if (rs.previous()) {
            do {
                assertEquals(resCnt--, rs.getInt(1));
            } while (rs.relative(-1));
        }

        pstmt.close();
        assertEquals(0, resCnt);
    }

    /**
     * Test that after updateRow() the cursor is positioned correctly.
     */
    public void testUpdateRowPosition() throws Exception {
        final int ROW_COUNT = 99;
        final int TEST_ROW = 33;

        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #testPos (i int primary key, v varchar(100))");
        for (int i = 1; i <= ROW_COUNT; i++) {
            stmt.executeUpdate("insert into #testPos (i, v) values (" + i + ", 'This is a test')");
        }
        stmt.close();

        String sql = "select * from #testPos order by i";

        PreparedStatement pstmt = con.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        pstmt.setFetchSize(10);

        ResultSet rs = pstmt.executeQuery();

        for (int i = 1; i <= TEST_ROW; i++) {
            assertTrue(rs.next());
            assertEquals(i, rs.getInt(1));
        }

        // We're on TEST_ROW now
        assertEquals(TEST_ROW, rs.getRow());
        rs.updateString(2, "This is another test");
        rs.updateRow();
        assertEquals(TEST_ROW, rs.getRow());
        assertEquals(TEST_ROW, rs.getInt(1));
        rs.refreshRow();
        assertEquals(TEST_ROW, rs.getRow());
        assertEquals(TEST_ROW, rs.getInt(1));

        for (int i = TEST_ROW + 1; i <= ROW_COUNT; i++) {
            assertTrue(rs.next());
            assertEquals(i, rs.getInt(1));
        }

        pstmt.close();
    }

    /**
     * Test for bug [1197603] Cursor downgrade error in CachedResultSet --
     * updateable result sets were incorrectly downgraded to read only forward
     * only ones when client side cursors were used.
     */
    public void testUpdateableClientCursor() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #testUpdateableClientCursor "
                + "(i int primary key, v varchar(100))");
        stmt.executeUpdate("insert into #testUpdateableClientCursor "
                + "(i, v) values (1, 'This is a test')");
        stmt.close();

        // Use a statement that the server won't be able to create a cursor on
        String sql = "select * from #testUpdateableClientCursor where i = ?";

        PreparedStatement pstmt = con.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        pstmt.setInt(1, 1);
        ResultSet rs = pstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));

        assertNull(pstmt.getWarnings());
        rs.updateString(2, "This is another test");
        rs.updateRow();
        rs.close();
        pstmt.close();

        stmt = con.createStatement();
        rs = stmt.executeQuery(
                "select * from #testUpdateableClientCursor");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals("This is another test", rs.getString(2));
        assertFalse(rs.next());
        rs.close();
        stmt.close();
    }

    /**
     * Test bug with Sybase where readonly scrollable result set based on a
     * SELECT DISTINCT returns duplicate rows.
     */
    public void testDistinctBug() throws Exception {
        Statement stmt = con.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        stmt.execute( "CREATE TABLE #testdistinct (id int primary key, c varchar(255))");
        stmt.addBatch("INSERT INTO #testdistinct VALUES(1, 'AAAA')");
        stmt.addBatch("INSERT INTO #testdistinct VALUES(2, 'AAAA')");
        stmt.addBatch("INSERT INTO #testdistinct VALUES(3, 'BBBB')");
        stmt.addBatch("INSERT INTO #testdistinct VALUES(4, 'BBBB')");
        stmt.addBatch("INSERT INTO #testdistinct VALUES(5, 'CCCC')");
        int counts[] = stmt.executeBatch();
        assertEquals(5, counts.length);

        ResultSet rs = stmt.executeQuery(
                "SELECT DISTINCT c FROM #testdistinct");
        assertNotNull(rs);
        int rowCount = 0;
        while (rs.next()) {
            rowCount++;
        }
        assertEquals(3, rowCount);
        stmt.close();
    }

    /**
     * Test pessimistic concurrency for SQL Server (for Sybase optimistic
     * concurrency will always be used).
     */
    public void testPessimisticConcurrency() throws Exception {
        dropTable("pessimisticConcurrency");
        Connection con2 = getConnection();
        try {
            Statement stmt = con.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE + 1);
            stmt.execute("CREATE TABLE pessimisticConcurrency (id int primary key, data varchar(255))");
            for (int i = 0; i < 4; i++) {
                stmt.executeUpdate("INSERT INTO pessimisticConcurrency VALUES("+i+", 'Table A line "+i+"')");
            }

            // Open cursor
            ResultSet rs = stmt.executeQuery("SELECT id, data FROM pessimisticConcurrency");
            assertNull(rs.getWarnings());
            assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, rs.getType());
            assertEquals(ResultSet.CONCUR_UPDATABLE + 1, rs.getConcurrency());
            // If not a MSCursorResultSet, give up as no locking will happen
            if (rs.getClass().getName().indexOf("MSCursorResultSet") == -1) {
                rs.close();
                stmt.close();
                return;
            }

            final Statement stmt2 = con2.createStatement();
            new Thread() {
                public void run() {
                    try {
                        sleep(1000);
                        stmt2.cancel();
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            }.start();

            while (rs.next()) {
                if (rs.getInt(1) == 1) {
                    try {
                        stmt2.executeUpdate(
                                "UPDATE pessimisticConcurrency SET data = 'NEW VALUE' WHERE id = 1");
                        fail("Expected locking to occur");
                    } catch (SQLException e) {
                        // Expected cancel exception
                        assertEquals("S1008", e.getSQLState());
                    }
                }
            }
            rs.close();
            stmt.close();
        } finally {
            dropTable("pessimisticConcurrency");
            if (con2 != null) {
                con2.close();
            }
        }
    }

    /**
     * Test if dynamic cursors (<code>ResultSet.TYPE_SCROLL_SENSITIVE+1</code>)
     * see others' updates. SQL Server only.
     */
    public void testDynamicCursors() throws Exception {
        final int ROWS = 4;
        dropTable("dynamicCursors");
        Connection con2 = getConnection();
        try {
            Statement stmt = con.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE + 1,
                    ResultSet.CONCUR_READ_ONLY);
            stmt.execute("CREATE TABLE dynamicCursors (id int primary key, data varchar(255))");
            for (int i = 0; i < ROWS; i++) {
                stmt.executeUpdate("INSERT INTO dynamicCursors VALUES(" + i + ", 'Table A line " + i + "')");
            }

            // Open cursor
            ResultSet rs = stmt.executeQuery("SELECT id, data FROM dynamicCursors");
            // If not a MSCursorResultSet, give up as it will not see inserts
            if (rs.getClass().getName().indexOf("MSCursorResultSet") == -1) {
                rs.close();
                stmt.close();
                return;
            }

            // Insert new row from other connection
            Statement stmt2 = con2.createStatement();
            assertEquals(1, stmt2.executeUpdate(
                    "INSERT INTO dynamicCursors VALUES(" + ROWS + ", 'Table A line " + ROWS + "')"));
            stmt2.close();

            // Count rows and make sure the newly inserted row is visible
            int cnt;
            for (cnt = 0; rs.next(); cnt++);
            assertEquals(ROWS + 1, cnt);

            rs.close();
            stmt.close();
        } finally {
            dropTable("dynamicCursors");
            if (con2 != null) {
                con2.close();
            }
        }
    }

    public static void main(String[] args) {
        junit.textui.TestRunner.run(ResultSetTest.class);
    }
}