statementstest.java

mysql的jdbc驱动

				// Next should be an update count...
				assertTrue(!multiStmt.getMoreResults());

				assertTrue("Update count was " + multiStmt.getUpdateCount()
						+ ", expected 1", multiStmt.getUpdateCount() == 1);

				assertTrue(multiStmt.getMoreResults());

				this.rs = multiStmt.getResultSet();

				assertTrue(this.rs.next());

				assertTrue(this.rs.getDouble(1) == 3);

				// End of multi results
				assertTrue(!multiStmt.getMoreResults());
				assertTrue(multiStmt.getUpdateCount() == -1);
			} finally {
				if (multiStmt != null) {
					multiStmt
							.executeUpdate("DROP TABLE IF EXISTS testMultiStatements");

					multiStmt.close();
				}

				if (multiStmtConn != null) {
					multiStmtConn.close();
				}
			}
		}
	}

	/**
	 * Tests that NULLs and '' work correctly.
	 * 
	 * @throws SQLException
	 *             if an error occurs
	 */
	public void testNulls() throws SQLException {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS nullTest");
			this.stmt
					.executeUpdate("CREATE TABLE IF NOT EXISTS nullTest (field_1 CHAR(20), rowOrder INT)");
			this.stmt
					.executeUpdate("INSERT INTO nullTest VALUES (null, 1), ('', 2)");

			this.rs = this.stmt
					.executeQuery("SELECT field_1 FROM nullTest ORDER BY rowOrder");

			this.rs.next();

			assertTrue("NULL field not returned as NULL", (this.rs
					.getString("field_1") == null)
					&& this.rs.wasNull());

			this.rs.next();

			assertTrue("Empty field not returned as \"\"", this.rs.getString(
					"field_1").equals("")
					&& !this.rs.wasNull());

			this.rs.close();
		} finally {
			if (this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception ex) {
					// ignore
				}
			}

			this.stmt.executeUpdate("DROP TABLE IF EXISTS nullTest");
		}
	}

	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");
			}
		}
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testPreparedStatement() throws SQLException {
		this.stmt
				.executeUpdate("INSERT INTO statement_test (id, strdata1,strdata2) values (999,'abcdefg', 'poi')");
		this.pstmt = this.conn
				.prepareStatement("UPDATE statement_test SET strdata1=?, strdata2=? where id=999");
		this.pstmt.setString(1, "iop");
		this.pstmt.setString(2, "higjklmn");

		// pstmt.setInt(3, 999);
		int updateCount = this.pstmt.executeUpdate();
		assertTrue("Update count must be '1', was '" + updateCount + "'",
				(updateCount == 1));

		this.pstmt.clearParameters();

		this.pstmt.close();

		this.rs = this.stmt
				.executeQuery("SELECT id, strdata1, strdata2 FROM statement_test");

		assertTrue(this.rs.next());
		assertTrue(this.rs.getInt(1) == 999);
		assertTrue("Expected 'iop', received '" + this.rs.getString(2) + "'",
				"iop".equals(this.rs.getString(2)));
		assertTrue("Expected 'higjklmn', received '" + this.rs.getString(3)
				+ "'", "higjklmn".equals(this.rs.getString(3)));
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testPreparedStatementBatch() throws SQLException {
		this.pstmt = this.conn.prepareStatement("INSERT INTO "
				+ "statement_batch_test (strdata1, strdata2) VALUES (?,?)");

		for (int i = 0; i < 1000; i++) {
			this.pstmt.setString(1, "batch_" + i);
			this.pstmt.setString(2, "batch_" + i);
			this.pstmt.addBatch();
		}

		int[] updateCounts = this.pstmt.executeBatch();

		for (int i = 0; i < updateCounts.length; i++) {
			assertTrue("Update count must be '1', was '" + updateCounts[i]
					+ "'", (updateCounts[i] == 1));
		}
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testSelectColumns() throws SQLException {
		for (int i = 6; i < MAX_COLUMNS_TO_TEST; i += STEP) {
			long start = System.currentTimeMillis();
			this.rs = this.stmt
					.executeQuery("SELECT * from statement_col_test_" + i);

			if (this.rs.next()) {
				;
			}

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

	public void testStreamChange() throws Exception {
		createTable("testStreamChange",
				"(field1 varchar(32), field2 int, field3 TEXT, field4 BLOB)");
		this.pstmt = this.conn
				.prepareStatement("INSERT INTO testStreamChange VALUES (?, ?, ?, ?)");

		try {
			this.pstmt.setString(1, "A");
			this.pstmt.setInt(2, 1);

			char[] cArray = { 'A', 'B', 'C' };
			Reader r = new CharArrayReader(cArray);
			this.pstmt.setCharacterStream(3, r, cArray.length);

			byte[] bArray = { 'D', 'E', 'F' };
			ByteArrayInputStream bais = new ByteArrayInputStream(bArray);
			this.pstmt.setBinaryStream(4, bais, bArray.length);

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

			this.rs = this.stmt
					.executeQuery("SELECT field3, field4 from testStreamChange where field1='A'");
			this.rs.next();
			assertEquals("ABC", this.rs.getString(1));
			assertEquals("DEF", this.rs.getString(2));

			char[] ucArray = { 'C', 'E', 'S', 'U' };
			this.pstmt.setString(1, "CESU");
			this.pstmt.setInt(2, 3);
			Reader ucReader = new CharArrayReader(ucArray);
			this.pstmt.setCharacterStream(3, ucReader, ucArray.length);
			this.pstmt.setBinaryStream(4, null, 0);
			assertEquals(1, this.pstmt.executeUpdate());

			this.rs = this.stmt
					.executeQuery("SELECT field3, field4 from testStreamChange where field1='CESU'");
			this.rs.next();
			assertEquals("CESU", this.rs.getString(1));
			assertEquals(null, this.rs.getString(2));
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}

			if (this.pstmt != null) {
				this.pstmt.close();
				this.pstmt = null;
			}
		}
	}

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

	// Server-side prepared statements can only reset streamed data
	// in-toto, not piecemiel.

	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");
		}

	}
}