resultsettest.java

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

     */
    public void testResultSetUpdate1() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #resultSetUpdate1 (id INT PRIMARY KEY, dsi SMALLINT NULL, di INT NULL)");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement("INSERT INTO #resultSetUpdate1 (id, dsi, di) VALUES (?, ?, ?)");

        pstmt.setInt(1, 1);
        pstmt.setShort(2, (short) 1);
        pstmt.setInt(3, 1);
        assertEquals(1, pstmt.executeUpdate());

        pstmt.close();

        stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        stmt.executeQuery("SELECT id, dsi, di FROM #resultSetUpdate1");

        ResultSet rs = stmt.getResultSet();

        assertNotNull(rs);
        assertTrue(rs.next());
        rs.updateNull("dsi");
        rs.updateNull("di");
        rs.updateRow();
        rs.moveToInsertRow();
        rs.updateInt(1, 2);
        rs.updateNull("dsi");
        rs.updateNull("di");
        rs.insertRow();

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

        stmt = con.createStatement();
        stmt.executeQuery("SELECT id, dsi, di FROM #resultSetUpdate1 ORDER BY id");

        rs = stmt.getResultSet();

        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        rs.getShort(2);
        assertTrue(rs.wasNull());
        rs.getInt(3);
        assertTrue(rs.wasNull());
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        rs.getShort(2);
        assertTrue(rs.wasNull());
        rs.getInt(3);
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

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

    /**
     * Test for bug [1009233] ResultSet getColumnName, getColumnLabel return wrong values
     */
    public void testResultSetColumnName1() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #resultSetCN1 (data INT)");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement("INSERT INTO #resultSetCN1 (data) VALUES (?)");

        pstmt.setInt(1, 1);
        assertEquals(1, pstmt.executeUpdate());

        pstmt.close();

        Statement stmt2 = con.createStatement();
        stmt2.executeQuery("SELECT data as test FROM #resultSetCN1");

        ResultSet rs = stmt2.getResultSet();

        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt("test"));
        assertFalse(rs.next());

        stmt2.close();
        rs.close();
    }

    /**
     * Test for fixed bugs in ResultSetMetaData:
     * <ol>
     * <li>isNullable() always returns columnNoNulls.
     * <li>isSigned returns true in error for TINYINT columns.
     * <li>Type names for numeric / decimal have (prec,scale) appended in error.
     * <li>Type names for auto increment columns do not have "identity" appended.
     * </ol>
     * NB: This test assumes getColumnName has been fixed to work as per the suggestion
     * in bug report [1009233].
     *
     * @throws Exception
     */
    public void testResultSetMetaData() throws Exception {
        Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        stmt.execute("CREATE TABLE #TRSMD (id INT IDENTITY NOT NULL, byte TINYINT NOT NULL, num DECIMAL(28,10) NULL)");
        ResultSetMetaData rsmd = stmt.executeQuery("SELECT id as idx, byte, num FROM #TRSMD").getMetaData();
        assertNotNull(rsmd);
        // Check id
        assertEquals("idx", rsmd.getColumnName(1)); // no longer returns base name
        assertEquals("idx", rsmd.getColumnLabel(1));
        assertTrue(rsmd.isAutoIncrement(1));
        assertTrue(rsmd.isSigned(1));
        assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(1));
        assertEquals("int identity", rsmd.getColumnTypeName(1));
        assertEquals(Types.INTEGER, rsmd.getColumnType(1));
        // Check byte
        assertFalse(rsmd.isAutoIncrement(2));
        assertFalse(rsmd.isSigned(2));
        assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
        assertEquals("tinyint", rsmd.getColumnTypeName(2));
        assertEquals(Types.TINYINT, rsmd.getColumnType(2));
        // Check num
        assertFalse(rsmd.isAutoIncrement(3));
        assertTrue(rsmd.isSigned(3));
        assertEquals(ResultSetMetaData.columnNullable, rsmd.isNullable(3));
        assertEquals("decimal", rsmd.getColumnTypeName(3));
        assertEquals(Types.DECIMAL, rsmd.getColumnType(3));
        stmt.close();
    }

    /**
     * Test for bug [1022445] Cursor downgrade warning not raised.
     */
    public void testCursorWarning() throws Exception
    {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #TESTCW (id INT PRIMARY KEY, DATA VARCHAR(255))");
        stmt.execute("CREATE PROC #SPTESTCW @P0 INT OUTPUT AS SELECT * FROM #TESTCW");
        stmt.close();
        CallableStatement cstmt = con.prepareCall("{call #SPTESTCW(?)}",
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        cstmt.registerOutParameter(1, Types.INTEGER);
        ResultSet rs = cstmt.executeQuery();
        // This should generate a ResultSet type/concurrency downgraded error.
        assertNotNull(rs.getWarnings());
        cstmt.close();
    }

    /**
     * Test whether retrieval by name returns the first occurence (that's what
     * the spec requires).
     */
    public void testGetByName() throws Exception
    {
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT 1 myCol, 2 myCol, 3 myCol");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt("myCol"));
        assertFalse(rs.next());
        stmt.close();
    }

    /**
     * Test if COL_INFO packets are processed correctly for
     * <code>ResultSet</code>s with over 255 columns.
     */
    public void testMoreThan255Columns() throws Exception
    {
        Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_UPDATABLE);

        // create the table
        int cols = 260;
        StringBuffer create = new StringBuffer("create table #manycolumns (");
        for (int i=0; i<cols; ++i) {
            create.append("col" + i + " char(10), ") ;
        }
        create.append(")");
        stmt.executeUpdate(create.toString());

        String query = "select * from #manycolumns";
        ResultSet rs = stmt.executeQuery(query);
        rs.close();
        stmt.close();
    }

    /**
     * Test that <code>insertRow()</code> works with no values set.
     */
    public void testEmptyInsertRow() throws Exception
    {
        int rows = 10;
        Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_UPDATABLE);

        stmt.executeUpdate(
                "create table #emptyInsertRow (id int identity, val int default 10)");
        ResultSet rs = stmt.executeQuery("select * from #emptyInsertRow");

        for (int i=0; i<rows; i++) {
            rs.moveToInsertRow();
            rs.insertRow();
        }
        rs.close();

        rs = stmt.executeQuery("select count(*) from #emptyInsertRow");
        assertTrue(rs.next());
        assertEquals(rows, rs.getInt(1));
        rs.close();

        rs = stmt.executeQuery("select * from #emptyInsertRow order by id");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(10, rs.getInt(2));
        rs.close();
        stmt.close();
    }

    /**
     * Test that inserted rows are visible in a scroll sensitive
     * <code>ResultSet</code> and that they show up at the end.
     */
    public void testInsertRowVisible() throws Exception
    {
        int rows = 10;
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        stmt.executeUpdate(
                "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