statementstest.java

mysql5.0 JDBC 驱动 放在glassfish或者tomcat的lib文件夹下就可以了

				try {
					this.rs.close();
				} catch (Exception ex) { /* ignore */
					;
				}
			}

			this.rs = null;
		}
	}

	/**
	 * Tests multiple statement support
	 *
	 * @throws Exception
	 *             DOCUMENT ME!
	 */
	public void testMultiStatements() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			Connection multiStmtConn = null;
			Statement multiStmt = null;

			try {
				Properties props = new Properties();
				props.setProperty("allowMultiQueries", "true");

				multiStmtConn = getConnectionWithProps(props);

				multiStmt = multiStmtConn.createStatement();

				multiStmt
						.executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
				multiStmt
						.executeUpdate("CREATE TABLE testMultiStatements (field1 VARCHAR(255), field2 INT, field3 DOUBLE)");
				multiStmt
						.executeUpdate("INSERT INTO testMultiStatements VALUES ('abcd', 1, 2)");

				multiStmt
						.execute("SELECT field1 FROM testMultiStatements WHERE field1='abcd';"
								+ "UPDATE testMultiStatements SET field3=3;"
								+ "SELECT field3 FROM testMultiStatements WHERE field3=3");

				this.rs = multiStmt.getResultSet();

				assertTrue(this.rs.next());

				assertTrue("abcd".equals(this.rs.getString(1)));
				this.rs.close();

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

	public void testRowFetch() throws Exception {
		if (versionMeetsMinimum(5, 0, 5)) {
			createTable("testRowFetch", "(field1 int)");

			this.stmt.executeUpdate("INSERT INTO testRowFetch VALUES (1)");

			Connection fetchConn = null;

			Properties props = new Properties();
			props.setProperty("useCursorFetch", "true");


			try {
				fetchConn = getConnectionWithProps(props);

				PreparedStatement fetchStmt = fetchConn
						.prepareStatement("SELECT field1 FROM testRowFetch WHERE field1=1");
				fetchStmt.setFetchSize(10);
				this.rs = fetchStmt.executeQuery();
				assertTrue(this.rs.next());

				this.stmt.executeUpdate("INSERT INTO testRowFetch VALUES (2), (3)");

				fetchStmt = fetchConn
						.prepareStatement("SELECT field1 FROM testRowFetch ORDER BY field1");
				fetchStmt.setFetchSize(1);
				this.rs = fetchStmt.executeQuery();

				assertTrue(this.rs.next());
				assertEquals(1, this.rs.getInt(1));
				assertTrue(this.rs.next());
				assertEquals(2, this.rs.getInt(1));
				assertTrue(this.rs.next());
				assertEquals(3, this.rs.getInt(1));
				assertEquals(false, this.rs.next());

				fetchStmt.executeQuery();
			} finally {
				if (fetchConn != null) {
					fetchConn.close();
				}
			}

		}
	}

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

	/**
	 * Tests for PreparedStatement.setObject()
	 *
	 * @throws Exception
	 */
	public void testSetObject() throws Exception {
		Properties props = new Properties();
		props.put("noDatetimeStringSync", "true"); // value=true for #5
		Connection conn1 = getConnectionWithProps(props);
		Statement stmt1 = conn1.createStatement();
		stmt1.executeUpdate("DROP TABLE IF EXISTS t1");
		stmt1.executeUpdate("CREATE TABLE t1 (" + "c1 DECIMAL," // instance of
																// String
				+ "c2 VARCHAR(255)," // instance of String
				+ "c3 BLOB," // instance of byte[]
				+ "c4 DATE," // instance of java.util.Date
				+ "c5 TIMESTAMP," // instance of String
				+ "c6 TIME," // instance of String
				+ "c7 TIME)"); // instance of java.sql.Timestamp

		this.pstmt = conn1
				.prepareStatement("INSERT INTO t1 VALUES (?, ?, ?, ?, ?, ?, ?)");

		long currentTime = System.currentTimeMillis();

		this.pstmt.setObject(1, "1000", Types.DECIMAL);
		this.pstmt.setObject(2, "2000", Types.VARCHAR);
		this.pstmt.setObject(3, new byte[] { 0 }, Types.BLOB);
		this.pstmt.setObject(4, new java.util.Date(currentTime), Types.DATE);
		this.pstmt.setObject(5, "2000-01-01 23-59-59", Types.TIMESTAMP);
		this.pstmt.setObject(6, "11:22:33", Types.TIME);
		this.pstmt
				.setObject(7, new java.sql.Timestamp(currentTime), Types.TIME);
		this.pstmt.execute();
		this.rs = stmt1.executeQuery("SELECT * FROM t1");
		this.rs.next();

		assertEquals("1000", this.rs.getString(1));
		assertEquals("2000", this.rs.getString(2));
		assertEquals(1, ((byte[]) this.rs.getObject(3)).length);
		assertEquals(0, ((byte[]) this.rs.getObject(3))[0]);
		assertEquals(new java.sql.Date(currentTime).toString(), this.rs
				.getDate(4).toString());

		if (versionMeetsMinimum(4, 1)) {
			assertEquals("2000-01-01 23:59:59", this.rs.getString(5));
		} else {
			assertEquals("20000101235959", this.rs.getString(5));
		}

		assertEquals("11:22:33", this.rs.getString(6));
		assertEquals(new java.sql.Time(currentTime).toString(), this.rs
				.getString(7));
	}

	public void testStatementRewriteBatch() throws Exception {
		for (int j = 0; j < 2; j++) {
			Properties props = new Properties();

			if (j == 0) {
				props.setProperty("useServerPrepStmts", "true");
			}

			props.setProperty("rewriteBatchedStatements", "true");
			Connection multiConn = getConnectionWithProps(props);
			createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
			Statement multiStmt = multiConn.createStatement();
			multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (1)");
			multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (2)");
			multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (3)");
			multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (4)");
			multiStmt.addBatch("UPDATE testStatementRewriteBatch SET field1=5 WHERE field1=1");
			multiStmt.addBatch("UPDATE testStatementRewriteBatch SET field1=6 WHERE field1=2 OR field1=3");

			int[] counts = multiStmt.executeBatch();

			if (!isRunningOnJdk131()) {
				ResultSet genKeys = multiStmt.getGeneratedKeys();

				for (int i = 1; i < 5; i++) {
					genKeys.next();
					assertEquals(i, genKeys.getInt(1));
				}
			}

			assertEquals(counts.length, 6);
			assertEquals(counts[0], 1);
			assertEquals(counts[1], 1);
			assertEquals(counts[2], 1);
			assertEquals(counts[3], 1);
			assertEquals(counts[4], 1);
			assertEquals(counts[5], 2);

			this.rs = multiStmt.executeQuery("SELECT field1 FROM testStatementRewriteBatch ORDER BY field1");
			assertTrue(this.rs.next());
			assertEquals(this.rs.getInt(1), 4);
			assertTrue(this.rs.next());
			assertEquals(this.rs.getInt(1), 5);
			assertTrue(this.rs.next());
			assertEquals(this.rs.getInt(1), 6);
			assertTrue(this.rs.next());
			assertEquals(this.rs.getInt(1), 6);

			createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
			props.clear();
			props.setProperty("rewriteBatchedStatements", "true");
			props.setProperty("sessionVariables", "max_allowed_packet=1024");
			multiConn = getConnectionWithProps(props);
			multiStmt = multiConn.createStatement();

			for (int i = 0; i < 1000; i++) {
				multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (" + i + ")");
			}

			multiStmt.executeBatch();

			if (!isRunningOnJdk131()) {
				ResultSet genKeys = multiStmt.getGeneratedKeys();

				for (int i = 1; i < 1000; i++) {
					genKeys.next();
					assertEquals(i, genKeys.getInt(1));
				}
			}

			createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");

			props.clear();
			props.setProperty("useServerPrepStmts", j == 0 ? "true" : "false");
			props.setProperty("rewriteBatchedStatements", "true");
			multiConn = getConnectionWithProps(props);

			PreparedStatement pStmt = null;

			if (!isRunningOnJdk131()) {
				pStmt = multiConn.prepareStatement("INSERT INTO testStatementRewriteBatch(field1) VALUES (?)",
						Statement.RETURN_GENERATED_KEYS);

				for (int i = 0; i < 1000; i++) {
					pStmt.setInt(1, i);
					pStmt.addBatch();
				}

				pStmt.executeBatch();

				ResultSet genKeys = pStmt.getGeneratedKeys();

				for (int i = 1; i < 1000; i++) {
					genKeys.next();
					assertEquals(i, genKeys.getInt(1));
				}
			}

			createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
			props.setProperty("useServerPrepStmts", j == 0 ? "true" : "false");
			props.setProperty("rewriteBatchedStatements", "true");
			props.setProperty("sessionVariables", "max_allowed_packet=1024");
			multiConn = getConnectionWithProps(props);

			if (!isRunningOnJdk131()) {

				pStmt = multiConn.prepareStatement("INSERT INTO testStatementRewriteBatch(field1) VALUES (?)",
					Statement.RETURN_GENERATED_KEYS);

				for (int i = 0; i < 1000; i++) {
					pStmt.setInt(1, i);
					pStmt.addBatch();
				}

				pStmt.executeBatch();


				ResultSet genKeys = pStmt.getGeneratedKeys();

				for (int i = 1; i < 1000; i++) {
					genKeys.next();
					assertEquals(i, genKeys.getInt(1));
				}
			}

			Object[][] differentTypes = new Object[1000][14];

			createTable("rewriteBatchTypes", "(internalOrder int, f1 tinyint null, "
					+ "f2 smallint null, f3 int null, f4 bigint null, "
					+ "f5 decimal(8, 2) null, f6 float null, f7 double null, "
					+ "f8 varchar(255) null, f9 text null, f10 blob null, "
					+ "f11 blob null, f12 datetime null, f13 time null, f14 date null)");

			for (int i = 0; i < 1000; i++) {
				differentTypes[i][0] = Math.random() < .5 ? null : new Byte((byte)(Math.random() * 127));
				differentTypes[i][1] = Math.random() < .5 ? null : new Short((short)(Math.random() * Short.MAX_VALUE));
				differentTypes[i][2] = Math.random() < .5 ? null : new Integer((int)(Math.random() * Integer.MAX_VALUE));
				differentTypes[i][3] = Math.random() < .5 ? null : new Long((long)(Math.random() * Long.MAX_VALUE));
				differentTypes[i][4] = Math.random() < .5 ? null : new BigDecimal("19.95");
				differentTypes[i][5] = Math.random() < .5 ? null : new Float(3 + ((float)(Math.random())));
				differentTypes[i][6] = Math.random() < .5 ? null : new Double(3 + (Math.random()));
				differentTypes[i][7] = Math.random() < .5 ? null : randomString();
				differentTypes[i][8] = Math.random() < .5 ? null : randomString();
				differentTypes[i][9] = Math.random() < .5 ? null : randomString().getBytes();
				differentTypes[i][10] = Math.random() < .5 ? null : randomString().getBytes();
				differentTypes[i][11] = Math.random() < .5 ? null : new Timestamp(System.currentTimeMillis());
				differentTypes[i][12] = Math.random() < .5 ? null : new Time(System.currentTimeMillis());
				differentTypes[i][13] = Math.random() < .5 ? null : new Date(System.currentTimeMillis());
			}

			props.setProperty("useServerPrepStmts", j == 0 ? "true" : "false");
			props.setProperty("rewriteBatchedStatements", "true");
			props.setProperty("sessionVariables", "max_allowed_packet=1024");
			multiConn = getConnectionWithProps(props);