connectiontest.java

mysql的jdbc驱动

				 * VALUES (_koi8r'�','CYR SMALL CHE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR SMALL SHA')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR SMALL SCHA')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR SMALL HARD SIGN')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR SMALL YERU')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR SMALL SOFT SIGN')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR SMALL E')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR SMALL YU')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR SMALL YA')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT A')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT BE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT VE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT GE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT DE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT IE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT IO')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT ZHE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT ZE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT I')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT KA')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT EL')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT EM')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT EN')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT O')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT PE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT ER')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT ES')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT TE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT U')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT EF')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT HA')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT TSE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT CHE')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT SHA')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT SCHA')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT HARD SIGN')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT YERU')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT SOFT SIGN')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT E')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT YU')");
				 * this.stmt.executeUpdate("INSERT INTO t1 (koi8_ru_f,comment)
				 * VALUES (_koi8r'�','CYR CAPIT YA')");
				 */

				this.stmt
						.executeUpdate("ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL");
				this.stmt
						.executeUpdate("UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8)");
				this.stmt.executeUpdate("SET CHARACTER SET koi8r");
				// this.stmt.executeUpdate("SET CHARACTER SET UTF8");
				this.rs = this.stmt.executeQuery("SELECT * FROM t1");

				ResultSetMetaData rsmd = this.rs.getMetaData();

				int numColumns = rsmd.getColumnCount();

				for (int i = 0; i < numColumns; i++) {
					System.out.print(rsmd.getColumnName(i + 1));
					System.out.print("\t\t");
				}

				System.out.println();

				while (this.rs.next()) {
					System.out.println(this.rs.getString(1) + "\t\t"
							+ this.rs.getString(2) + "\t\t"
							+ this.rs.getString(3));

					if (this.rs.getString(1).equals("CYR SMALL A")) {
						this.rs.getString(2);
					}
				}

				System.out.println();

				this.stmt.executeUpdate("SET NAMES utf8");
				this.rs = this.stmt.executeQuery("SELECT _koi8r 0xC1;");

				rsmd = this.rs.getMetaData();

				numColumns = rsmd.getColumnCount();

				for (int i = 0; i < numColumns; i++) {
					System.out.print(rsmd.getColumnName(i + 1));
					System.out.print("\t\t");
				}

				System.out.println();

				while (this.rs.next()) {
					System.out.println(this.rs.getString(1).equals("\u0430")
							+ "\t\t");
					System.out
							.println(new String(this.rs.getBytes(1), "KOI8_R"));

				}

				char[] c = new char[] { 0xd0b0 };

				System.out.println(new String(c));
				System.out.println("\u0430");
			} finally {
				// this.stmt.executeUpdate("DROP TABLE IF EXISTS t1");
			}
		}
	}

	/**
	 * Tests isolation level functionality
	 * 
	 * @throws Exception
	 *             if an error occurs
	 */
	public void testIsolationLevel() throws Exception {
		if (versionMeetsMinimum(4, 0)) {
			String[] isoLevelNames = new String[] {
					"Connection.TRANSACTION_NONE",
					"Connection.TRANSACTION_READ_COMMITTED",
					"Connection.TRANSACTION_READ_UNCOMMITTED",
					"Connection.TRANSACTION_REPEATABLE_READ",
					"Connection.TRANSACTION_SERIALIZABLE" };

			int[] isolationLevels = new int[] { Connection.TRANSACTION_NONE,
					Connection.TRANSACTION_READ_COMMITTED,
					Connection.TRANSACTION_READ_UNCOMMITTED,
					Connection.TRANSACTION_REPEATABLE_READ,
					Connection.TRANSACTION_SERIALIZABLE };

			DatabaseMetaData dbmd = this.conn.getMetaData();

			for (int i = 0; i < isolationLevels.length; i++) {
				if (dbmd.supportsTransactionIsolationLevel(isolationLevels[i])) {
					this.conn.setTransactionIsolation(isolationLevels[i]);

					assertTrue(
							"Transaction isolation level that was set ("
									+ isoLevelNames[i]
									+ ") was not returned, nor was a more restrictive isolation level used by the server",
							this.conn.getTransactionIsolation() == isolationLevels[i]
									|| this.conn.getTransactionIsolation() > isolationLevels[i]);
				}
			}
		}
	}

	/**
	 * Tests the savepoint functionality in MySQL.
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testSavepoint() throws Exception {
		DatabaseMetaData dbmd = this.conn.getMetaData();

		if (dbmd.supportsSavepoints()) {
			System.out.println("Testing SAVEPOINTs");

			try {
				this.conn.setAutoCommit(true);

				this.stmt.executeUpdate("DROP TABLE IF EXISTS testSavepoints");
				this.stmt
						.executeUpdate("CREATE TABLE testSavepoints (field1 int) TYPE=InnoDB");

				// Try with named save points
				this.conn.setAutoCommit(false);
				this.stmt
						.executeUpdate("INSERT INTO testSavepoints VALUES (1)");

				Savepoint afterInsert = this.conn.setSavepoint("afterInsert");
				this.stmt.executeUpdate("UPDATE testSavepoints SET field1=2");

				Savepoint afterUpdate = this.conn.setSavepoint("afterUpdate");
				this.stmt.executeUpdate("DELETE FROM testSavepoints");

				assertTrue("Row count should be 0",
						getRowCount("testSavepoints") == 0);
				this.conn.rollback(afterUpdate);
				assertTrue("Row count should be 1",
						getRowCount("testSavepoints") == 1);
				assertTrue("Value should be 2", "2".equals(getSingleValue(
						"testSavepoints", "field1", null).toString()));
				this.conn.rollback(afterInsert);
				assertTrue("Value should be 1", "1".equals(getSingleValue(
						"testSavepoints", "field1", null).toString()));
				this.conn.rollback();
				assertTrue("Row count should be 0",
						getRowCount("testSavepoints") == 0);

				// Try with 'anonymous' save points
				this.conn.rollback();

				this.stmt
						.executeUpdate("INSERT INTO testSavepoints VALUES (1)");
				afterInsert = this.conn.setSavepoint();
				this.stmt.executeUpdate("UPDATE testSavepoints SET field1=2");
				afterUpdate = this.conn.setSavepoint();
				this.stmt.executeUpdate("DELETE FROM testSavepoints");

				assertTrue("Row count should be 0",
						getRowCount("testSavepoints") == 0);
				this.conn.rollback(afterUpdate);
				assertTrue("Row count should be 1",
						getRowCount("testSavepoints") == 1);
				assertTrue("Value should be 2", "2".equals(getSingleValue(
						"testSavepoints", "field1", null).toString()));
				this.conn.rollback(afterInsert);
				assertTrue("Value should be 1", "1".equals(getSingleValue(
						"testSavepoints", "field1", null).toString()));
				this.conn.rollback();

				this.conn.releaseSavepoint(this.conn.setSavepoint());
			} finally {
				this.conn.setAutoCommit(true);
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testSavepoints");
			}
		} else {
			System.out.println("MySQL version does not support SAVEPOINTs");
		}
	}

	/**
	 * Tests the ability to set the connection collation via properties.
	 * 
	 * @throws Exception
	 *             if an error occurs or the test fails
	 */
	public void testNonStandardConnectionCollation() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			String collationToSet = "utf8_bin";
			String characterSet = "utf8";

			Properties props = new Properties();
			props.setProperty("connectionCollation", collationToSet);
			props.setProperty("characterEncoding", characterSet);

			Connection collConn = null;
			Statement collStmt = null;
			ResultSet collRs = null;

			try {
				collConn = getConnectionWithProps(props);

				collStmt = collConn.createStatement();

				collRs = collStmt
						.executeQuery("SHOW VARIABLES LIKE 'collation_connection'");

				assertTrue(collRs.next());
				assertTrue(collationToSet.equalsIgnoreCase(collRs.getString(2)));
			} finally {
				if (collConn != null) {
					collConn.close();
				}
			}
		}
	}

	public void testDumpQueriesOnException() throws Exception {
		Properties props = new Properties();
		props.setProperty("dumpQueriesOnException", "true");
		String bogusSQL = "SELECT 1 TO BAZ";
		Connection dumpConn = getConnectionWithProps(props);

		try {
			dumpConn.createStatement().executeQuery(bogusSQL);
		} catch (SQLException sqlEx) {
			assertTrue(sqlEx.getMessage().indexOf(bogusSQL) != -1);
		}

		try {
			((com.mysql.jdbc.Connection) dumpConn).clientPrepareStatement(
					bogusSQL).executeQuery();
		} catch (SQLException sqlEx) {
			assertTrue(sqlEx.getMessage().indexOf(bogusSQL) != -1);
		}

		try {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testDumpQueriesOnException");
			this.stmt
					.executeUpdate("CREATE TABLE testDumpQueriesOnException (field1 int UNIQUE)");
			this.stmt
					.executeUpdate("INSERT INTO testDumpQueriesOnException VALUES (1)");

			PreparedStatement pStmt = dumpConn
					.prepareStatement("INSERT INTO testDumpQueriesOnException VALUES (?)");
			pStmt.setInt(1, 1);
			pStmt.executeUpdate();
		} catch (SQLException sqlEx) {
			assertTrue(sqlEx.getMessage().indexOf(
					"INSERT INTO testDumpQueriesOnException") != -1);
		} finally {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testDumpQueriesOnException");
		}

		try {
			dumpConn.prepareStatement(bogusSQL);
		} catch (SQLException sqlEx) {
			assertTrue(sqlEx.getMessage().indexOf(bogusSQL) != -1);
		}
	}

	/**
	 * Tests functionality of the ConnectionPropertiesTransform interface.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testConnectionPropertiesTransform() throws Exception {
		String transformClassName = SimpleTransformer.class.getName();

		Properties props = new Properties();

		props.setProperty(NonRegisteringDriver.PROPERTIES_TRANSFORM_KEY,
				transformClassName);

		NonRegisteringDriver driver = new NonRegisteringDriver();

		Properties transformedProps = driver
				.parseURL(BaseTestCase.dbUrl, props);

		assertTrue("albequerque".equals(transformedProps
				.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY)));
	}

	/**
	 * Tests functionality of using URLs in 'LOAD DATA LOCAL INFILE' statements.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testLocalInfileWithUrl() throws Exception {
		File infile = File.createTempFile("foo", "txt");
		infile.deleteOnExit();
		String url = infile.toURL().toExternalForm();
		FileWriter output = new FileWriter(infile);
		output.write("Test");
		output.flush();
		output.close();

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