statementstest.java

在资料浩瀚的互联网中

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

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

    /**
     * Tests all variants of numerical types (signed/unsigned) for correct
     * operation when used as return values from a prepared statement.
     *
     * @throws Exception
     */
    public void testBinaryResultSetNumericTypes() throws Exception {
        /*
             * TINYINT          1          -128          127
             * SMALLINT         2         -32768         32767
             * MEDIUMINT         3         -8388608         8388607
             * INT         4         -2147483648         2147483647
             * BIGINT         8         -9223372036854775808         9223372036854775807
             */
    	
    	String unsignedMinimum = "0";
    	
    	String tiMinimum = "-128";
    	String tiMaximum = "127";
    	String utiMaximum = "255";
    	
    	String siMinimum = "-32768";
    	String siMaximum = "32767";
    	String usiMaximum = "65535";
    	
    	String miMinimum = "-8388608";
    	String miMaximum = "8388607";
    	String umiMaximum = "16777215";
    	
    	String iMinimum = "-2147483648";
    	String iMaximum = "2147483647";
    	String uiMaximum = "4294967295";
    	
    	String biMinimum = "-9223372036854775808";
    	String biMaximum = "9223372036854775807";
    	String ubiMaximum = "18446744073709551615";
    	
        try {
            this.stmt.executeUpdate(
                "DROP TABLE IF EXISTS testBinaryResultSetNumericTypes");
            this.stmt.executeUpdate(
                "CREATE TABLE testBinaryResultSetNumericTypes(rowOrder TINYINT, ti TINYINT," 
            		+ "uti TINYINT UNSIGNED, si SMALLINT,"
					+ "usi SMALLINT UNSIGNED, mi MEDIUMINT," 
					+ "umi MEDIUMINT UNSIGNED, i INT, ui INT UNSIGNED,"
					+ "bi BIGINT, ubi BIGINT UNSIGNED)");
            PreparedStatement inserter = this.conn.prepareStatement("INSERT INTO testBinaryResultSetNumericTypes VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            inserter.setInt(1, 0);
            inserter.setString(2, tiMinimum);
            inserter.setString(3, unsignedMinimum);
            inserter.setString(4, siMinimum);
            inserter.setString(5, unsignedMinimum);
            inserter.setString(6, miMinimum);
            inserter.setString(7, unsignedMinimum);
            inserter.setString(8, iMinimum);
            inserter.setString(9, unsignedMinimum);
            inserter.setString(10, biMinimum);
            inserter.setString(11, unsignedMinimum);
            inserter.executeUpdate();
            
            inserter.setInt(1, 1);
            inserter.setString(2, tiMaximum);
            inserter.setString(3, utiMaximum);
            inserter.setString(4, siMaximum);
            inserter.setString(5, usiMaximum);
            inserter.setString(6, miMaximum);
            inserter.setString(7, umiMaximum);
            inserter.setString(8, iMaximum);
            inserter.setString(9, uiMaximum);
            inserter.setString(10, biMaximum);
            inserter.setString(11, ubiMaximum);
            inserter.executeUpdate();
            
            PreparedStatement selector = this.conn.prepareStatement("SELECT * FROM testBinaryResultSetNumericTypes ORDER by rowOrder ASC");
            this.rs = selector.executeQuery();
            
            assertTrue(this.rs.next());
            
            assertTrue(this.rs.getString(2).equals(tiMinimum));
            assertTrue(this.rs.getString(3).equals(unsignedMinimum));
            assertTrue(this.rs.getString(4).equals(siMinimum));
            assertTrue(this.rs.getString(5).equals(unsignedMinimum));
            assertTrue(this.rs.getString(6).equals(miMinimum));
            assertTrue(this.rs.getString(7).equals(unsignedMinimum));
            assertTrue(this.rs.getString(8).equals(iMinimum));
            assertTrue(this.rs.getString(9).equals(unsignedMinimum));
            assertTrue(this.rs.getString(10).equals(biMinimum));
            assertTrue(this.rs.getString(11).equals(unsignedMinimum));
            
            assertTrue(this.rs.next());
            
            assertTrue(this.rs.getString(2) + " != " + tiMaximum, this.rs.getString(2).equals(tiMaximum));
            assertTrue(this.rs.getString(3) + " != " + utiMaximum, this.rs.getString(3).equals(utiMaximum));
            assertTrue(this.rs.getString(4) + " != " + siMaximum, this.rs.getString(4).equals(siMaximum));
            assertTrue(this.rs.getString(5) + " != " + usiMaximum, this.rs.getString(5).equals(usiMaximum));
            assertTrue(this.rs.getString(6) + " != " + miMaximum, this.rs.getString(6).equals(miMaximum));
            assertTrue(this.rs.getString(7) + " != " + umiMaximum, this.rs.getString(7).equals(umiMaximum));
            assertTrue(this.rs.getString(8) + " != " + iMaximum, this.rs.getString(8).equals(iMaximum));
            assertTrue(this.rs.getString(9) + " != " + uiMaximum, this.rs.getString(9).equals(uiMaximum));
            assertTrue(this.rs.getString(10) + " != " + biMaximum, this.rs.getString(10).equals(biMaximum));
            assertTrue(this.rs.getString(11) + " != " + ubiMaximum, this.rs.getString(11).equals(ubiMaximum));
            
            assertTrue(!this.rs.next());
        } finally {
            this.stmt.executeUpdate(
                "DROP TABLE IF EXISTS testBinaryResultSetNumericTypes");
        }
    }
    
    public void testTruncationOnRead() throws Exception {
    	this.rs = this.stmt.executeQuery("SELECT '" + Long.MAX_VALUE + "'");
    	this.rs.next();
    	
    	try {
    		this.rs.getByte(1);
    		fail("Should've thrown an out-of-range exception");
    	} catch (SQLException sqlEx) {
    		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
    	}

    	try {
    		this.rs.getShort(1);
    		fail("Should've thrown an out-of-range exception");
    	} catch (SQLException sqlEx) {
    		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
    	}
    	
    	try {
    		this.rs.getInt(1);
    		fail("Should've thrown an out-of-range exception");
    	} catch (SQLException sqlEx) {
    		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
    	}
    	
    	this.rs = this.stmt.executeQuery("SELECT '" + Double.MAX_VALUE + "'");
    	
    	this.rs.next();
    	
    	try {
    		this.rs.getByte(1);
    		fail("Should've thrown an out-of-range exception");
    	} catch (SQLException sqlEx) {
    		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
    	}

    	try {
    		this.rs.getShort(1);
    		fail("Should've thrown an out-of-range exception");
    	} catch (SQLException sqlEx) {
    		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
    	}
    	
    	try {
    		this.rs.getInt(1);
    		fail("Should've thrown an out-of-range exception");
    	} catch (SQLException sqlEx) {
    		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
    	}
    	
    	try {
    		this.rs.getLong(1);
    		fail("Should've thrown an out-of-range exception");
    	} catch (SQLException sqlEx) {
    		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
    	}
    	
    	try {
    		this.rs.getLong(1);
    		fail("Should've thrown an out-of-range exception");
    	} catch (SQLException sqlEx) {
    		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
    	}
    	
    	PreparedStatement pStmt = null;
    	
    	System.out.println("Testing prepared statements with binary result sets now");
    	
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testTruncationOnRead");
    		this.stmt.executeUpdate("CREATE TABLE testTruncationOnRead(intField INTEGER, bigintField BIGINT, doubleField DOUBLE)");
    		this.stmt.executeUpdate("INSERT INTO testTruncationOnRead VALUES (" + Integer.MAX_VALUE + ", " + Long.MAX_VALUE + ", " + Double.MAX_VALUE + ")");
    		this.stmt.executeUpdate("INSERT INTO testTruncationOnRead VALUES (" + Integer.MIN_VALUE + ", " + Long.MIN_VALUE + ", " + Double.MIN_VALUE + ")");
    		
    		pStmt = this.conn.prepareStatement("SELECT intField, bigintField, doubleField FROM testTruncationOnRead ORDER BY intField DESC");
    		this.rs = pStmt.executeQuery();
    		
    		this.rs.next();
    		
    		try {
        		this.rs.getByte(1);
        		fail("Should've thrown an out-of-range exception");
        	} catch (SQLException sqlEx) {
        		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
        	}
        	
        	try {
        		this.rs.getInt(2);
        		fail("Should've thrown an out-of-range exception");
        	} catch (SQLException sqlEx) {
        		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
        	}
        	
        	try {
        		this.rs.getLong(3);
        		fail("Should've thrown an out-of-range exception");
        	} catch (SQLException sqlEx) {
        		assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE.equals(sqlEx.getSQLState()));
        	}
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testTruncationOnRead");
    	}
    
    }
    
    public void testParsedConversionWarning() throws Exception {
    	if (versionMeetsMinimum(4, 1)) {
    		try {
    			Properties props = new Properties();
    			props.setProperty("useUsageAdvisor", "true");
    			Connection warnConn = getConnectionWithProps(props);
    			
    			this.stmt.executeUpdate("DROP TABLE IF EXISTS testParsedConversionWarning");
    			this.stmt.executeUpdate("CREATE TABLE testParsedConversionWarning(field1 VARCHAR(255))");
    			this.stmt.executeUpdate("INSERT INTO testParsedConversionWarning VALUES ('1.0')");
    			
    			PreparedStatement badStmt = warnConn.prepareStatement("SELECT field1 FROM testParsedConversionWarning");
    			
    			this.rs = badStmt.executeQuery();
    			assertTrue(this.rs.next());
    			this.rs.getFloat(1);
    		} finally {
    			this.stmt.executeUpdate("DROP TABLE IF EXISTS testParsedConversionWarning");
    		}
    	}
    }
    
    public void testHoldingResultSetsOverClose() throws Exception {
    	Properties props = new Properties();
    	props.setProperty("holdResultsOpenOverStatementClose", "true");
    	
    	Connection conn2 = getConnectionWithProps(props);
    	
    	Statement stmt2 = null;
    	PreparedStatement pstmt2 = null;
    	
    	try {
    		stmt2 = conn2.createStatement();
    		
    		this.rs = stmt2.executeQuery("SELECT 1");
    		this.rs.next();
    		this.rs.getInt(1);
    		stmt2.close();
    		this.rs.getInt(1);
    	
    		pstmt2 = conn2.prepareStatement("SELECT 1");
    		this.rs = pstmt2.executeQuery();
    		this.rs.next();
    		this.rs.getInt(1);
    		pstmt2.close();
    		this.rs.getInt(1);
    		
    		pstmt2 = conn2.prepareStatement("SELECT 1");
    		this.rs = pstmt2.executeQuery();
    		this.rs.next();
    		this.rs.getInt(1);
    		pstmt2.executeQuery();
    		this.rs.getInt(1);
    		pstmt2.execute();
    		this.rs.getInt(1);
    		
    		pstmt2 = ((com.mysql.jdbc.Connection)conn2).clientPrepareStatement("SELECT 1");
    		this.rs = pstmt2.executeQuery();
    		this.rs.next();
    		this.rs.getInt(1);
    		pstmt2.close();
    		this.rs.getInt(1);
    		
    		pstmt2 = ((com.mysql.jdbc.Connection)conn2).clientPrepareStatement("SELECT 1");
    		this.rs = pstmt2.executeQuery();
    		this.rs.next();
    		this.rs.getInt(1);
    		pstmt2.executeQuery();
    		this.rs.getInt(1);
    		pstmt2.execute();
    		this.rs.getInt(1);
    		
    		stmt2 = conn2.createStatement();
    		this.rs = stmt2.executeQuery("SELECT 1");
    		this.rs.next();
    		this.rs.getInt(1);
    		stmt2.executeQuery("SELECT 2");
    		this.rs.getInt(1);
    		this.rs = stmt2.executeQuery("SELECT 1");
    		this.rs.next();
    		this.rs.getInt(1);
    		stmt2.executeUpdate("SET @var=1");
    		this.rs.getInt(1);
    		stmt2.execute("SET @var=2");
    		this.rs.getInt(1);   		
    	} finally {
    		if (stmt2 != null) {
    			stmt2.close();
    		}
    	}
    }
    
    public void testEnableStreamingResults() throws Exception {
    	Statement streamStmt = this.conn.createStatement();
    	((com.mysql.jdbc.Statement)streamStmt).enableStreamingResults();
    	assertEquals(streamStmt.getFetchSize(), Integer.MIN_VALUE);
    	assertEquals(streamStmt.getResultSetType(), ResultSet.TYPE_FORWARD_ONLY);
    }
}