connectiontest.java

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

				 * 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 {
		if (!isRunningOnJdk131()) {
			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 = "utf-8";

			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");
			this.stmt
					.executeUpdate("CREATE TABLE testLocalInfileWithUrl (field1 LONGTEXT)");

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

			Connection loadConn = getConnectionWithProps(props);
			Statement loadStmt = loadConn.createStatement();

			try {
				loadStmt.executeQuery("LOAD DATA LOCAL INFILE '" + url
						+ "' INTO TABLE testLocalInfileWithUrl");
			} catch (SQLException sqlEx) {
				sqlEx.printStackTrace();

				throw sqlEx;
			}

			this.rs = this.stmt
					.executeQuery("SELECT * FROM testLocalInfileWithUrl");
			assertTrue(this.rs.next());
			assertTrue("Test".equals(this.rs.getString(1)));
			int count = this.stmt
					.executeUpdate("DELETE FROM testLocalInfileWithUrl");
			assertTrue(count == 1);

			StringBuffer escapedPath = new StringBuffer();
			String path = infile.getCanonicalPath();

			for (int i = 0; i < path.length(); i++) {
				char c = path.charAt(i);

				if (c == '\\') {
					escapedPath.append('\\');
				}

				escapedPath.append(c);
			}

			loadStmt.executeQuery("LOAD DATA LOCAL INFILE '"
					+ escapedPath.toString()
					+ "' INTO TABLE testLocalInfileWithUrl");
			this.rs = this.stmt
					.executeQuery("SELECT * FROM testLocalInfileWithUrl");
			assertTrue(this.rs.next());
			assertTrue("Test".equals(this.rs.getString(1)));

			try {
				loadStmt
						.executeQuery("LOAD DATA LOCAL INFILE 'foo:///' INTO TABLE testLocalInfileWithUrl");
			} catch (SQLException sqlEx) {
				assertTrue(sqlEx.getMessage() != null);
				assertTrue(sqlEx.getMessage().indexOf("FileNotFoundException") != -1);
			}

		} finally {
			this.stmt