preparedstatementtest.java

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

        assertEquals(1, rs.getInt(2));
        assertFalse(rs.next());
        rs.close();
        pstmt.close();

        pstmt = con.prepareStatement("select {fn round(?, 0)}");
        pstmt.setDouble(1, 1.2);
        rs = pstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(1, rs.getDouble(1), 0);
        assertFalse(rs.next());
        rs.close();
        pstmt.close();
    }

    /**
     * Inner class used by {@link PreparedStatementTest#testMultiThread} to
     * test concurrency.
     */
    static class TestMultiThread extends Thread {
        static Connection con;
        static final int THREAD_MAX = 10;
        static final int LOOP_MAX = 10;
        static final int ROWS_MAX = 10;
        static int live;
        static Exception error;

        int threadId;

        TestMultiThread(int n) {
            threadId = n;
        }

        public void run() {
            try {
                con.rollback();
                PreparedStatement pstmt = con.prepareStatement(
                        "SELECT id, data FROM #TEST WHERE id = ?",
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

                for (int i = 1; i <= LOOP_MAX; i++) {
                    pstmt.clearParameters();
                    pstmt.setInt(1, i);
                    ResultSet rs = pstmt.executeQuery();

                    while (rs.next()) {
                        rs.getInt(1);
                        rs.getString(2);
                    }

                }

                pstmt.close();
            } catch (Exception e) {
                System.err.print("ID=" + threadId + ' ');
                e.printStackTrace();
                error = e;
            }

            synchronized (this.getClass()) {
                live--;
            }
        }

        static void startThreads(Connection con) throws Exception {
            TestMultiThread.con = con;
            con.setAutoCommit(false);

            Statement stmt = con.createStatement();
            stmt.execute("CREATE TABLE #TEST (id int identity primary key, data varchar(255))");

            for (int i = 0; i < ROWS_MAX; i++) {
                stmt.executeUpdate("INSERT INTO #TEST (data) VALUES('This is line " + i + "')");
            }

            stmt.close();
            con.commit();

            live = THREAD_MAX;
            for (int i = 0; i < THREAD_MAX; i++) {
                new TestMultiThread(i).start();
            }
            while (live > 0) {
                sleep(1);
            }

            if (error != null) {
                throw error;
            }
        }
    }

    /**
     * Test <code>Connection</code> concurrency by running
     * <code>PreparedStatement</code>s and rollbacks at the same time to see
     * whether handles are not lost in the process.
     */
    public void testMultiThread() throws Exception {
        TestMultiThread.startThreads(con);
    }

    /**
     * Test for bug [1094621] Decimal conversion error:  A prepared statement
     * with a decimal parameter that is -1E38 will fail as a result of the
     * driver generating a parameter specification of decimal(38,10) rather
     * than decimal(38,0).
     */
    public void testBigDecBadParamSpec() throws Exception
    {
        Statement stmt = con.createStatement();
        stmt.execute(
                "create table #test (id int primary key, val decimal(38,0))");
        BigDecimal bd =
                new BigDecimal("99999999999999999999999999999999999999");
        PreparedStatement pstmt =
                con.prepareStatement("insert into #test values(?,?)");
        pstmt.setInt(1, 1);
        pstmt.setBigDecimal(2, bd);
        assertEquals(1, pstmt.executeUpdate()); // Worked OK
        pstmt.setInt(1, 2);
        pstmt.setBigDecimal(2, bd.negate());
        assertEquals(1, pstmt.executeUpdate()); // Failed
    }

    /**
     * Test for bug [1111516 ] Illegal Parameters in PreparedStatement.
     */
    public void testIllegalParameters() throws Exception
    {
        Statement stmt = con.createStatement();
        stmt.execute("create table #test (id int)");
        PreparedStatement pstmt =
                con.prepareStatement("select top ? * from #test");
        pstmt.setInt(1, 10);
        try {
            pstmt.executeQuery();
            // This won't fail in unprepared mode (prepareSQL == 0)
            // fail("Expecting an exception to be thrown.");
        } catch (SQLException ex) {
            assertEquals("37000", ex.getSQLState());
        }
        pstmt.close();
    }

    /**
     * Test for bug [1180777] collation-related execption on update.
     * <p/>
     * If a statement prepare fails the statement should still be executed
     * (unprepared) and a warning should be added to the connection (the
     * prepare failed, this is a connection event even if it happened on
     * statement execute).
     */
    public void testPrepareFailWarning() throws SQLException {
        try {
            PreparedStatement pstmt = con.prepareStatement(
                    "CREATE VIEW prepFailWarning AS SELECT 1 AS value");
            pstmt.execute();
            // Check that a warning was generated on the connection.
            // Although not totally correct (the warning should be generated on
            // the statement) the warning is generated while preparing the
            // statement, so it belongs to the connection.
            assertNotNull(con.getWarnings());
            pstmt.close();

            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM prepFailWarning");
            assertTrue(rs.next());
            assertEquals(1, rs.getInt(1));
            assertFalse(rs.next());
            rs.close();
            stmt.close();
        } finally {
            Statement stmt = con.createStatement();
            stmt.execute("DROP VIEW prepFailWarning");
            stmt.close();
        }
    }

    /**
     * Test that preparedstatement logic copes with commit modes and
     * database changes.
     */
    public void testPrepareModes() throws Exception {
        //
        // To see in detail what is happening enable logging and study the prepare
        // statements that are being executed.
        // For example if maxStatements=0 then the log should show that each
        // statement is prepared and then unprepared at statement close.
        //
//        DriverManager.setLogStream(System.out);
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #TEST (id int primary key, data varchar(255))");
        //
        // Statement prepared with auto commit = true
        //
        PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO #TEST (id, data) VALUES (?,?)");
        pstmt1.setInt(1, 1);
        pstmt1.setString(2, "Line one");
        assertEquals(1, pstmt1.executeUpdate());
        //
        // Move to manual commit mode
        //
        con.setAutoCommit(false);
        //
        // Ensure a new transaction is started
        //
        ResultSet rs = stmt.executeQuery("SELECT * FROM #TEST");
        assertNotNull(rs);
        rs.close();
        //
        // With Sybase this execution should cause a new proc to be created
        // as we are now in chained mode
        //
        pstmt1.setInt(1, 2);
        pstmt1.setString(2, "Line two");
        assertEquals(1, pstmt1.executeUpdate());
        //
        // Statement prepared with auto commit = false
        //
        PreparedStatement pstmt2 = con.prepareStatement("SELECT * FROM #TEST WHERE id = ?");
        pstmt2.setInt(1, 2);
        rs = pstmt2.executeQuery();
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals("Line two", rs.getString("data"));
        //
        // Change catalog
        //
        String oldCat = con.getCatalog();
        con.setCatalog("master");
        //
        // Executiion from another database should cause SQL Server to create
        // a new handle or store proc
        //
        pstmt2.setInt(1, 1);
        rs = pstmt2.executeQuery();
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals("Line one", rs.getString("data"));
        //
        // Now change back to original database
        //
        con.setCatalog(oldCat);
        //
        // Roll back transaction which should cause SQL Server procs (but not
        // handles to be lost) causing statement to be prepared again.
        //
        pstmt2.setInt(1, 1);
        rs = pstmt2.executeQuery();
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals("Line one", rs.getString("data"));
        //
        // Now return to auto commit mode
        //
        con.setAutoCommit(true);
        //
        // With Sybase statement will be prepared again as now in chained off mode
        //
        pstmt2.setInt(1, 1);
        rs = pstmt2.executeQuery();
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals("Line one", rs.getString("data"));
        pstmt2.close();
        pstmt1.close();
        stmt.close();
    }

    /**
     * Test that statements which cannot be prepared are remembered.
     */
    public void testNoPrepare() throws Exception {
        //       DriverManager.setLogStream(System.out);
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #TEST (id int primary key, data text)");
        //
        // Statement cannot be prepared on Sybase due to text field
        //
        PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO #TEST (id, data) VALUES (?,?)");
        pstmt1.setInt(1, 1);
        pstmt1.setString(2, "Line one");
        assertEquals(1, pstmt1.executeUpdate());
        //
        // This time should not try and prepare
        //
        pstmt1.setInt(1, 2);
        pstmt1.setString(2, "Line two");
        assertEquals(1, pstmt1.executeUpdate());
        pstmt1.close();
    }

    /**
     * Tests that float (single precision - 32 bit) values are not converted to
     * double (thus loosing precision).
     */
    public void testFloatValues() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #floatTest (v real)");
        stmt.executeUpdate("insert into #floatTest (v) values (2.3)");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement(
                "select * from #floatTest where v = ?");
        pstmt.setFloat(1, 2.3f);
        ResultSet rs = pstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(2.3f, rs.getFloat(1), 0);
        assertTrue(rs.getObject(1) instanceof Float);
        assertEquals(2.3f, ((Float) rs.getObject(1)).floatValue(), 0);

        // Just make sure that conversion to double will break this
        assertFalse(2.3 - rs.getDouble(1) == 0);
        assertFalse(rs.next());
        rs.close();
        pstmt.close();
    }

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