statementregressiontest.java

mysql的jdbc驱动

				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 you can close a statement twice without an NPE.
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testCloseTwice() throws Exception {
		Statement closeMe = this.conn.createStatement();
		closeMe.close();
		closeMe.close();
	}

	public void testCsc4194() throws Exception {

		Connection sjisConn = null;
		Connection windows31JConn = null;

		try {
			String tableNameText = "testCsc4194Text";
			String tableNameBlob = "testCsc4194Blob";

			createTable(tableNameBlob, "(field1 BLOB)");
			String charset = "";

			if (versionMeetsMinimum(5, 0, 3) || versionMeetsMinimum(4, 1, 12)) {
				charset = " CHARACTER SET cp932";
			} else if (versionMeetsMinimum(4, 1, 0)) {
				charset = " CHARACTER SET sjis";
			}

			createTable(tableNameText, "(field1 TEXT)" + charset);

			Properties windows31JProps = new Properties();
			windows31JProps.setProperty("useUnicode", "true");
			windows31JProps.setProperty("characterEncoding", "Windows-31J");

			windows31JConn = getConnectionWithProps(windows31JProps);
			testCsc4194InsertCheckBlob(windows31JConn, tableNameBlob);

			if (versionMeetsMinimum(4, 1, 0)) {
				testCsc4194InsertCheckText(windows31JConn, tableNameText,
						"Windows-31J");
			}

			Properties sjisProps = new Properties();
			sjisProps.setProperty("useUnicode", "true");
			sjisProps.setProperty("characterEncoding", "sjis");

			sjisConn = getConnectionWithProps(sjisProps);
			testCsc4194InsertCheckBlob(sjisConn, tableNameBlob);

			if (versionMeetsMinimum(5, 0, 3)) {
				testCsc4194InsertCheckText(sjisConn, tableNameText,
						"Windows-31J");
			}

		} finally {

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

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

	private void testCsc4194InsertCheckBlob(Connection c, String tableName)
			throws Exception {
		byte[] bArray = new byte[] { (byte) 0xac, (byte) 0xed, (byte) 0x00,
				(byte) 0x05 };

		PreparedStatement testStmt = c.prepareStatement("INSERT INTO "
				+ tableName + " VALUES (?)");
		testStmt.setBytes(1, bArray);
		testStmt.executeUpdate();

		this.rs = c.createStatement().executeQuery(
				"SELECT field1 FROM " + tableName);
		assertTrue(this.rs.next());
		assertEquals(getByteArrayString(bArray), getByteArrayString(this.rs
				.getBytes(1)));
		this.rs.close();
	}

	private void testCsc4194InsertCheckText(Connection c, String tableName,
			String encoding) throws Exception {
		byte[] kabuInShiftJIS = { (byte) 0x87, // a double-byte
				// charater("kabu") in Shift JIS
				(byte) 0x8a, };

		String expected = new String(kabuInShiftJIS, encoding);
		PreparedStatement testStmt = c.prepareStatement("INSERT INTO "
				+ tableName + " VALUES (?)");
		testStmt.setString(1, expected);
		testStmt.executeUpdate();

		this.rs = c.createStatement().executeQuery(
				"SELECT field1 FROM " + tableName);
		assertTrue(this.rs.next());
		assertEquals(expected, this.rs.getString(1));
		this.rs.close();
	}

	/**
	 * Tests all forms of statements influencing getGeneratedKeys().
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testGetGeneratedKeysAllCases() throws Exception {
		System.out.println("Using Statement.executeUpdate()\n");

		try {
			createGGKTables();

			// Do the tests
			for (int i = 0; i < tests.length; i++) {
				doGGKTestStatement(tests[i], true);
			}
		} finally {
			dropGGKTables();
		}

		nextID = 1;
		count = 0;

		System.out.println("Using Statement.execute()\n");

		try {
			createGGKTables();

			// Do the tests
			for (int i = 0; i < tests.length; i++) {
				doGGKTestStatement(tests[i], false);
			}
		} finally {
			dropGGKTables();
		}

		nextID = 1;
		count = 0;

		System.out.println("Using PreparedStatement.executeUpdate()\n");

		try {
			createGGKTables();

			// Do the tests
			for (int i = 0; i < tests.length; i++) {
				doGGKTestPreparedStatement(tests[i], true);
			}
		} finally {
			dropGGKTables();
		}

		nextID = 1;
		count = 0;

		System.out.println("Using PreparedStatement.execute()\n");

		try {
			createGGKTables();

			// Do the tests
			for (int i = 0; i < tests.length; i++) {
				doGGKTestPreparedStatement(tests[i], false);
			}
		} finally {
			dropGGKTables();
		}
	}

	/**
	 * Tests that max_rows and 'limit' don't cause exceptions to be thrown.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testLimitAndMaxRows() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testMaxRowsAndLimit");
			this.stmt
					.executeUpdate("CREATE TABLE testMaxRowsAndLimit(limitField INT)");

			for (int i = 0; i < 500; i++) {
				this.stmt
						.executeUpdate("INSERT INTO testMaxRowsAndLimit VALUES ("
								+ i + ")");
			}

			this.stmt.setMaxRows(250);
			this.stmt
					.executeQuery("SELECT limitField FROM testMaxRowsAndLimit");
		} finally {
			this.stmt.setMaxRows(0);

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

	/*
	 * public void testBug9595() throws Exception { double[] vals = new double[]
	 * {52.21, 52.22, 52.23, 52.24};
	 * 
	 * createTable("testBug9595", "(field1 DECIMAL(10,2), sortField INT)");
	 * 
	 * this.pstmt = this.conn.prepareStatement("INSERT INTO testBug9595 VALUES
	 * (?, ?)"); // Try setting as doubles for (int i = 0; i < vals.length; i++) {
	 * this.pstmt.setDouble(1, vals[i]); this.pstmt.setInt(2, i);
	 * this.pstmt.executeUpdate(); }
	 * 
	 * this.pstmt = this.conn.prepareStatement("SELECT field1 FROM testBug9595
	 * ORDER BY sortField"); this.rs = this.pstmt.executeQuery();
	 * 
	 * int i = 0;
	 * 
	 * while (this.rs.next()) { double valToTest = vals[i++];
	 * 
	 * assertEquals(this.rs.getDouble(1), valToTest, 0.001);
	 * assertEquals(this.rs.getBigDecimal(1).doubleValue(), valToTest, 0.001); }
	 * 
	 * this.pstmt = this.conn.prepareStatement("INSERT INTO testBug9595 VALUES
	 * (?, ?)");
	 * 
	 * this.stmt.executeUpdate("TRUNCATE TABLE testBug9595"); // Now, as
	 * BigDecimals for (i = 0; i < vals.length; i++) { BigDecimal foo = new
	 * BigDecimal(vals[i]);
	 * 
	 * this.pstmt.setObject(1, foo, Types.DECIMAL, 2); this.pstmt.setInt(2, i);
	 * this.pstmt.executeUpdate(); }
	 * 
	 * this.pstmt = this.conn.prepareStatement("SELECT field1 FROM testBug9595
	 * ORDER BY sortField"); this.rs = this.pstmt.executeQuery();
	 * 
	 * i = 0;
	 * 
	 * while (this.rs.next()) { double valToTest = vals[i++];
	 * System.out.println(this.rs.getString(1));
	 * assertEquals(this.rs.getDouble(1), valToTest, 0.001);
	 * assertEquals(this.rs.getBigDecimal(1).doubleValue(), valToTest, 0.001); } }
	 */

	/**
	 * Tests that 'LOAD DATA LOCAL INFILE' works
	 * 
	 * @throws Exception
	 *             if any errors occur
	 */
	public void testLoadData() throws Exception {
		try {
			int maxAllowedPacket = 1048576;

			this.stmt.executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
			this.stmt
					.executeUpdate("CREATE TABLE loadDataRegress (field1 int, field2 int)");

			File tempFile = File.createTempFile("mysql", ".txt");

			// tempFile.deleteOnExit();
			System.out.println(tempFile);

			Writer out = new FileWriter(tempFile);

			int localCount = 0;
			int rowCount = 128; // maxAllowedPacket * 4;

			for (int i = 0; i < rowCount; i++) {
				out.write((localCount++) + "\t" + (localCount++) + "\n");
			}

			out.close();

			StringBuffer fileNameBuf = null;

			if (File.separatorChar == '\\') {
				fileNameBuf = new StringBuffer();

				String fileName = tempFile.getAbsolutePath();
				int fileNameLength = fileName.length();

				for (int i = 0; i < fileNameLength; i++) {
					char c = fileName.charAt(i);

					if (c == '\\') {
						fileNameBuf.append("/");
					} else {
						fileNameBuf.append(c);
					}
				}
			} else {
				fileNameBuf = new StringBuffer(tempFile.getAbsolutePath());
			}

			int updateCount = this.stmt
					.executeUpdate("LOAD DATA LOCAL INFILE '"
							+ fileNameBuf.toString()
							+ "' INTO TABLE loadDataRegress");
			assertTrue(updateCount == rowCount);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
		}
	}

	public void testNullClob() throws Exception {
		createTable("testNullClob", "(field1 TEXT NULL)");

		PreparedStatement pStmt = null;

		try {
			pStmt = this.conn
					.prepareStatement("INSERT INTO testNullClob VALUES (?)");
			pStmt.setClob(1, null);
			pStmt.executeUpdate();
		} finally {
			if (pStmt != null) {
				pStmt.close();
			}
		}
	}

	/**
	 * Tests fix for BUG#1658
	 * 
	 * @throws Exception
	 *             if the fix for parameter bounds checking doesn't work.
	 */
	public void testParameterBoundsCheck() throws Exception {
		try {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testParameterBoundsCheck");
			this.stmt
					.executeUpdate("CREATE TABLE testParameterBoundsCheck(f1 int, f2 int, f3 int, f4 int, f5 int)");

			PreparedStatement pstmt = this.conn
					.prepareStatement("UPDATE testParameterBoundsCheck SET f1=?, f2=?,f3=?,f4=? WHERE f5=?");

			pstmt.setString(1, "");
			pstmt.setString(2, "");

			try {
				pstmt.setString(25, "");
			} catch (SQLException sqlEx) {
				assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
						.getSQLState()));
			}
		} finally {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testParameterBoundsCheck");
		}
	}

	public void testPStmtTypesBug() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
			this.stmt
					.executeUpdate("CREATE TABLE testPStmtTypesBug(field1 INT)");
			this.pstmt = this.conn
					.prepareStatement("INSERT INTO testPStmtTypesBug VALUES (?)");
			this.pstmt.setObject(1, null, Types.INTEGER);
			this.pstmt.addBatch();
			this.pstmt.setInt(1, 1);
			this.pstmt.addBatch();
			this.pstmt.executeBatch();

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

	/**
	 * Tests fix for BUG#1511
	 * 
	 * @throws Exception
	 *             if the quoteid parsing fix in PreparedStatement doesn't work.
	 */
	public void testQuotedIdRecognition() throws Exception {
		if (!this.versionMeetsMinimum(4, 1)) {
			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId");
				this.stmt
						.executeUpdate("CREATE TABLE testQuotedId (col1 VARCHAR(32))");

				PreparedStatement pStmt = this.conn
						.prepareStatement("SELECT * FROM testQuotedId FROM WHERE col1='ABC`DEF' or col1=?");
				pStmt.setString(1, "foo");
				System.out.println(pStmt);
			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId");
			}
		}
	}

	/**
	 * Tests for BUG#9288, parameter index out of range if LIKE, ESCAPE '\'
	 * present in query.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	/*
	 * public void testBug9288() throws Exception { String tableName =
	 * "testBug9288"; PreparedStatement pStmt = null;
	 * 
	 * try { createTable(tableName, "(field1 VARCHAR(32), field2 INT)"); pStmt =
	 * ((com.mysql.jdbc.Connection)this.conn).clientPrepareStatement( "SELECT
	 * COUNT(1) FROM " + tableName + " WHERE " + "field1 LIKE '%' ESCAPE '\\'
	 * AND " + "field2 > ?"); pStmt.setInt(1, 0);
	 * 
	 * this.rs = pStmt.executeQuery(); } fi