statementstest.java

开发MySql数据库的最新JDBC驱动。

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

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testInsert() throws SQLException {
		try {
			boolean autoCommit = this.conn.getAutoCommit();

			// Test running a query for an update. It should fail.
			try {
				this.conn.setAutoCommit(false);
				this.stmt.executeUpdate("SELECT * FROM statement_test");
			} catch (SQLException sqlEx) {
				assertTrue("Exception thrown for unknown reason", sqlEx
						.getSQLState().equalsIgnoreCase("01S03"));
			} finally {
				this.conn.setAutoCommit(autoCommit);
			}

			// Test running a update for an query. It should fail.
			try {
				this.conn.setAutoCommit(false);
				this.stmt
						.executeQuery("UPDATE statement_test SET strdata1='blah' WHERE 1=0");
			} catch (SQLException sqlEx) {
				assertTrue("Exception thrown for unknown reason", sqlEx
						.getSQLState().equalsIgnoreCase(
								SQLError.SQL_STATE_ILLEGAL_ARGUMENT));
			} finally {
				this.conn.setAutoCommit(autoCommit);
			}

			for (int i = 0; i < 10; i++) {
				int updateCount = this.stmt
						.executeUpdate("INSERT INTO statement_test (strdata1,strdata2) values ('abcdefg', 'poi')");
				assertTrue("Update count must be '1', was '" + updateCount
						+ "'", (updateCount == 1));
			}

			if (!isRunningOnJdk131()) {
				int insertIdFromGeneratedKeys = Integer.MIN_VALUE;

				this.stmt
						.executeUpdate("INSERT INTO statement_test (strdata1, strdata2) values ('a', 'a'), ('b', 'b'), ('c', 'c')");
				this.rs = this.stmt.getGeneratedKeys();

				if (this.rs.next()) {
					insertIdFromGeneratedKeys = this.rs.getInt(1);
				}

				this.rs.close();
				this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID()");

				int insertIdFromServer = Integer.MIN_VALUE;

				if (this.rs.next()) {
					insertIdFromServer = this.rs.getInt(1);
				}

				assertEquals(insertIdFromGeneratedKeys, insertIdFromServer);
			}
		} finally {
			if (this.rs != null) {
				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)");