statementregressiontest.java

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

					(deltaOrig < epsillon));

			long pStmtDeltaTWithCal = (ts.getTime() - tsValuePstmtNoCal
					.getTime());

			System.out
					.println(Math.abs(pStmtDeltaTWithCal - pointInTimeOffset)
							+ " < "
							+ epsillon
							+ (Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) < epsillon));
			assertTrue(
					"Difference between original timestamp and timestamp retrieved using java.sql.PreparedStatement "
							+ "set in database using UTC calendar is not ~= "
							+ epsillon
							+ ", it is actually "
							+ pStmtDeltaTWithCal, (Math.abs(pStmtDeltaTWithCal
							- pointInTimeOffset) < epsillon));

			System.out
					.println("Difference between original ts and ts with no calendar: "
							+ (ts.getTime() - tsValuePstmtNoCal.getTime())
							+ ", offset should be " + pointInTimeOffset);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
		}
	}

	/**
	 * Tests that DataTruncation is thrown when data is truncated.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug3697() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
			this.stmt
					.executeUpdate("CREATE TABLE testBug3697 (field1 VARCHAR(255))");

			StringBuffer updateBuf = new StringBuffer(
					"INSERT INTO testBug3697 VALUES ('");

			for (int i = 0; i < 512; i++) {
				updateBuf.append("A");
			}

			updateBuf.append("')");

			try {
				this.stmt.executeUpdate(updateBuf.toString());
			} catch (DataTruncation dtEx) {
				// This is an expected exception....
			}

			SQLWarning warningChain = this.stmt.getWarnings();

			System.out.println(warningChain);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
		}
	}

	/**
	 * Tests fix for BUG#3804, data truncation on server should throw
	 * DataTruncation exception.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug3804() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
				this.stmt
						.executeUpdate("CREATE TABLE testBug3804 (field1 VARCHAR(5))");

				boolean caughtTruncation = false;

				try {
					this.stmt
							.executeUpdate("INSERT INTO testBug3804 VALUES ('1234567')");
				} catch (DataTruncation truncationEx) {
					caughtTruncation = true;
					System.out.println(truncationEx);
				}

				assertTrue("Data truncation exception should've been thrown",
						caughtTruncation);
			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
			}
		}
	}

	/**
	 * Tests BUG#3873 - PreparedStatement.executeBatch() not returning all
	 * generated keys (even though that's not JDBC compliant).
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug3873() throws Exception {
		if (isRunningOnJdk131()) {
			return; // test not valid on JDK-1.3.1
		}

		PreparedStatement batchStmt = null;

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
			this.stmt
					.executeUpdate("CREATE TABLE testBug3873 (keyField INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dataField VARCHAR(32))");
			batchStmt = this.conn.prepareStatement(
					"INSERT INTO testBug3873 (dataField) VALUES (?)",
					Statement.RETURN_GENERATED_KEYS);
			batchStmt.setString(1, "abc");
			batchStmt.addBatch();
			batchStmt.setString(1, "def");
			batchStmt.addBatch();
			batchStmt.setString(1, "ghi");
			batchStmt.addBatch();

			int[] updateCounts = batchStmt.executeBatch();

			this.rs = batchStmt.getGeneratedKeys();

			while (this.rs.next()) {
				System.out.println(this.rs.getInt(1));
			}

			this.rs = batchStmt.getGeneratedKeys();
			assertTrue(this.rs.next());
			assertTrue(1 == this.rs.getInt(1));
			assertTrue(this.rs.next());
			assertTrue(2 == this.rs.getInt(1));
			assertTrue(this.rs.next());
			assertTrue(3 == this.rs.getInt(1));
			assertTrue(!this.rs.next());
		} finally {
			if (batchStmt != null) {
				batchStmt.close();
			}

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

	/**
	 * Tests fix for BUG#4119 -- misbehavior in a managed environment from
	 * MVCSoft JDO
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug4119() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
			this.stmt.executeUpdate("CREATE TABLE `testBug4119` ("
					+ "`field1` varchar(255) NOT NULL default '',"
					+ "`field2` bigint(20) default NULL,"
					+ "`field3` int(11) default NULL,"
					+ "`field4` datetime default NULL,"
					+ "`field5` varchar(75) default NULL,"
					+ "`field6` varchar(75) default NULL,"
					+ "`field7` varchar(75) default NULL,"
					+ "`field8` datetime default NULL,"
					+ " PRIMARY KEY  (`field1`)" + ")");

			PreparedStatement pStmt = this.conn
					.prepareStatement("insert into testBug4119 (field2, field3,"
							+ "field4, field5, field6, field7, field8, field1) values (?, ?,"
							+ "?, ?, ?, ?, ?, ?)");

			pStmt.setString(1, "0");
			pStmt.setString(2, "0");
			pStmt.setTimestamp(3, new java.sql.Timestamp(System
					.currentTimeMillis()));
			pStmt.setString(4, "ABC");
			pStmt.setString(5, "DEF");
			pStmt.setString(6, "AA");
			pStmt.setTimestamp(7, new java.sql.Timestamp(System
					.currentTimeMillis()));
			pStmt.setString(8, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
			pStmt.executeUpdate();
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
		}
	}

	/**
	 * Tests fix for BUG#4311 - Error in JDBC retrieval of mediumint column when
	 * using prepared statements and binary result sets.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug4311() throws Exception {
		try {
			int lowValue = -8388608;
			int highValue = 8388607;

			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
			this.stmt
					.executeUpdate("CREATE TABLE testBug4311 (low MEDIUMINT, high MEDIUMINT)");
			this.stmt.executeUpdate("INSERT INTO testBug4311 VALUES ("
					+ lowValue + ", " + highValue + ")");

			PreparedStatement pStmt = this.conn
					.prepareStatement("SELECT low, high FROM testBug4311");
			this.rs = pStmt.executeQuery();
			assertTrue(this.rs.next());
			assertTrue(this.rs.getInt(1) == lowValue);
			assertTrue(this.rs.getInt(2) == highValue);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
		}
	}

	/**
	 * Tests fix for BUG#4510 -- Statement.getGeneratedKeys() fails when key >
	 * 32767
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug4510() throws Exception {
		if (isRunningOnJdk131()) {
			return; // test not valid on JDK-1.3.1
		}

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
			this.stmt.executeUpdate("CREATE TABLE testBug4510 ("
					+ "field1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
					+ "field2 VARCHAR(100))");
			this.stmt
					.executeUpdate("INSERT INTO testBug4510 (field1, field2) VALUES (32767, 'bar')");

			PreparedStatement p = this.conn.prepareStatement(
					"insert into testBug4510 (field2) values (?)",
					Statement.RETURN_GENERATED_KEYS);

			p.setString(1, "blah");

			p.executeUpdate();

			ResultSet rs = p.getGeneratedKeys();
			rs.next();
			System.out.println("Id: " + rs.getInt(1));
			rs.close();
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
		}
	}

	/**
	 * Server doesn't accept everything as a server-side prepared statement, so
	 * by default we scan for stuff it can't handle.
	 * 
	 * @throws SQLException
	 */
	public void testBug4718() throws SQLException {
		if (versionMeetsMinimum(4, 1, 0)
				&& ((com.mysql.jdbc.Connection) this.conn)
						.getUseServerPreparedStmts()) {
			this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT ?");
			assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

			this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT 1");
			assertTrue(this.pstmt instanceof com.mysql.jdbc.ServerPreparedStatement);

			this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT 1, ?");
			assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");
				this.stmt
						.executeUpdate("CREATE TABLE testBug4718 (field1 char(32))");

				this.pstmt = this.conn
						.prepareStatement("ALTER TABLE testBug4718 ADD INDEX (field1)");
				assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

				this.pstmt = this.conn.prepareStatement("SELECT 1");
				assertTrue(this.pstmt instanceof ServerPreparedStatement);

				this.pstmt = this.conn
						.prepareStatement("UPDATE testBug4718 SET field1=1");
				assertTrue(this.pstmt instanceof ServerPreparedStatement);

				this.pstmt = this.conn
						.prepareStatement("UPDATE testBug4718 SET field1=1 LIMIT 1");
				assertTrue(this.pstmt instanceof ServerPreparedStatement);

				this.pstmt = this.conn
						.prepareStatement("UPDATE testBug4718 SET field1=1 LIMIT ?");
				assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

				this.pstmt = this.conn
						.prepareStatement("UPDATE testBug4718 SET field1='Will we ignore LIMIT ?,?'");
				assertTrue(this.pstmt instanceof ServerPreparedStatement);

			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");
			}
		}
	}

	/**
	 * Tests fix for BUG#5012 -- ServerPreparedStatements dealing with return of
	 * DECIMAL type don't work.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug5012() throws Exception {
		PreparedStatement pStmt = null;
		String valueAsString = "12345.12";

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
			this.stmt
					.executeUpdate("CREATE TABLE testBug5012(field1 DECIMAL(10,2))");
			this.stmt.executeUpdate("INSERT INTO testBug5012 VALUES ("
					+ valueAsString + ")");

			pStmt = this.conn
					.prepareStatement("SELECT field1 FROM testBug5012");
			this.rs = pStmt.executeQuery();
			assertTrue(this.rs.next());
			assertEquals(new BigDecimal(valueAsString), this.rs
					.getBigDecimal(1));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");

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

	/**
	 * Tests fix for BUG#5133 -- PreparedStatement.toString() doesn't return
	 * correct value if no parameters are present in statement.
	 * 
	 * @throws Exception
	 */
	public void testBug5133() throws Exception {
		String query = "SELECT 1";
		String output = this.conn.prepareStatement(query).toString();
		System.out.println(output);

		assertTrue(output.indexOf(query) != -1);
	}

	/**
	 * Tests for BUG#5191 -- PreparedStatement.executeQuery() gives
	 * OutOfMemoryError
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug5191() throws Exception {
		PreparedStatement pStmt = null;

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191C");

			this.stmt.executeUpdate("CREATE TABLE testBug5191Q"
					+ "(QuestionId int NOT NULL AUTO_INCREMENT, "
					+ "Text VARCHAR(200), " + "PRIMARY KEY(QuestionId))");

			this.stmt.executeUpdate("CREATE TABLE testBug5191C"
					+ "(CategoryId int, " + "QuestionId int)");

			String[] questions = new String[] { "What is your name?",
					"What is your quest?",
					"What is the airspeed velocity of an unladen swollow?",
					"How many roads must a man walk?", "Where's the tea?", };

			for (int i = 0; i < questions.length; i++) {
				this.stmt.executeUpdate("INSERT INTO testBug5191Q(Text)"
						+ " VALUES (\"" + questions[i] + "\")");
				int catagory = (i < 3) ? 0 : i;

				this.stmt.executeUpdate("INSERT INTO testBug5191C"
						+ "(CategoryId, QuestionId) VALUES (" + catagory + ", "
						+ i + ")");
				/*
				 * this.stmt.executeUpdate("INSERT INTO testBug5191C" +
				 * "(CategoryId, QuestionId) VALUES (" + catagory + ", (SELECT
				 * testBug5191Q.QuestionId" + " FROM testBug5191Q " + "WHERE
				 * testBug5191Q.Text LIKE '" + questions[i] + "'))");
				 */
			}

			pStmt = this.conn.prepareStatement("SELECT qc.QuestionId, q.Text "
					+ "FROM testBug5191Q q, testBug5191C qc "
					+ "WHERE qc.CategoryId = ? "
					+ " AND q.QuestionId = qc.QuestionId");

			int catId = 0;
			for (int i = 0; i < 100; i++) {
				execQueryBug5191(pStmt, catId);
			}

		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191C");

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

	public void testBug5235() throws Exception {
		Properties props = new Properties();
		props.setProperty("zeroDateTimeBehavior", "convertToNull");

		Connection convertToNullConn = getConnectionWithProps(props);
		Statement convertToNullStmt = convertToNullConn.createStatement();
		try {
			convertToNullStmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
			convertToNullStmt
					.executeUpdate("CREATE TABLE testBug5235(field1 DATE)");
			convertToNullStmt
					.executeUpdate("INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");

			PreparedStatement ps = convertToNullConn
					.prepareStatement("SELECT field1 FROM testBug5235");
			this.rs = ps.executeQuery();

			if (this.rs.next()) {
				Date d = (Date) this.rs.getObject("field1");
				System.out.println("date: " + d);
			}
		} finally {
			convertToNullStmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
		}
	}

	public void testBug5450() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			String table = "testBug5450";
			String column = "policyname";

			try {
				Properties props = new Properties();
				props.setProperty("characterEncoding", "utf-8");

				Connection utf8Conn = getConnectionWithProps(props);
				Statement utfStmt = utf8Conn.createStatement();

				this.stmt.executeUpdate("DROP TABLE IF EXISTS " + table);

				this.stmt.executeUpdate("CREATE TABLE " + table
						+ "(policyid int NOT NULL AUTO_INCREMENT, " + column
						+ " VARCHAR(200), "
						+ "PRIMARY KEY(policyid)) DEFAULT CHARACTER SET utf8");

				String pname0 = "inserted \uac00 - foo - \u4e00";

				utfStmt.executeUpdate("INSERT INTO " + table + "(" + column