connectiontest.java

mysql的jdbc驱动

			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
					.executeUpdate("DROP TABLE IF EXISTS testLocalInfileWithUrl");
		}
	}

	public void testServerConfigurationCache() throws Exception {
		Properties props = new Properties();

		props.setProperty("cacheServerConfiguration", "true");
		props.setProperty("profileSQL", "true");
		props.setProperty("logFactory", "com.mysql.jdbc.log.StandardLogger");

		Connection conn1 = getConnectionWithProps(props);

		StandardLogger.saveLogsToBuffer();

		Connection conn2 = getConnectionWithProps(props);

		assertTrue("Configuration wasn't cached", StandardLogger.bufferedLog
				.toString().indexOf("SHOW VARIABLES") == -1);

		if (versionMeetsMinimum(4, 1)) {
			assertTrue("Configuration wasn't cached",
					StandardLogger.bufferedLog.toString().indexOf(
							"SHOW COLLATION") == -1);

		}
	}

	/**
	 * Tests whether or not the configuration 'useLocalSessionState' actually
	 * prevents non-needed 'set autocommit=', 'set session transaction isolation
	 * ...' and 'show variables like tx_isolation' queries.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testUseLocalSessionState() throws Exception {
		Properties props = new Properties();

		props.setProperty("useLocalSessionState", "true");
		props.setProperty("profileSQL", "true");
		props.setProperty("logFactory", "com.mysql.jdbc.log.StandardLogger");

		Connection conn1 = getConnectionWithProps(props);
		conn1.setAutoCommit(true);
		conn1.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

		StandardLogger.saveLogsToBuffer();
		StandardLogger.bufferedLog.setLength(0);

		conn1.setAutoCommit(true);
		conn1.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
		conn1.getTransactionIsolation();

		String logAsString = StandardLogger.bufferedLog.toString();

		assertTrue(logAsString.indexOf("SET SESSION") == -1
				&& logAsString.indexOf("SHOW VARIABLES LIKE 'tx_isolation'") == -1
				&& logAsString.indexOf("SET autocommit=") == -1);

	}

	/**
	 * Tests whether re-connect with non-read-only connection can happen.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testFailoverConnection() throws Exception {

		Properties props = new Properties();
		props.setProperty("autoReconnect", "true");
		props.setProperty("failOverReadOnly", "false");

		// Re-build the connection information
		int firstIndexOfHost = BaseTestCase.dbUrl.indexOf("//") + 2;
		int lastIndexOfHost = BaseTestCase.dbUrl.indexOf("/", firstIndexOfHost);

		String hostPortPair = BaseTestCase.dbUrl.substring(firstIndexOfHost,
				lastIndexOfHost);
		System.out.println(hostPortPair);

		StringTokenizer st = new StringTokenizer(hostPortPair, ":");

		String host = null;
		String port = null;

		if (st.hasMoreTokens()) {
			String possibleHostOrPort = st.nextToken();

			if (Character.isDigit(possibleHostOrPort.charAt(0))) {
				port = possibleHostOrPort;
				host = "localhost";
			} else {
				host = possibleHostOrPort;
			}
		}

		if (host == null) {
			host = "localhost"; 
		}
		
		if (st.hasMoreTokens()) {
			port = st.nextToken();
		}

		StringBuffer newHostBuf = new StringBuffer();
		newHostBuf.append(host);
		if (port != null) {
			newHostBuf.append(":");
			newHostBuf.append(port);
		}
		newHostBuf.append(",");
		newHostBuf.append(host);
		if (port != null) {
			newHostBuf.append(":");
			newHostBuf.append(port);
		}

		props
				.put(NonRegisteringDriver.HOST_PROPERTY_KEY, newHostBuf
						.toString());

		Connection failoverConnection = null;

		try {
			failoverConnection = getConnectionWithProps(props);

			String originalConnectionId = getSingleIndexedValueWithQuery(
					failoverConnection, 1, "SELECT connection_id()").toString();
			System.out.println("Original Connection Id = "
					+ originalConnectionId);

			assertTrue("Connection should not be in READ_ONLY state",
					!failoverConnection.isReadOnly());

			// Kill the connection
			this.stmt.executeUpdate("KILL " + originalConnectionId);

			// This takes a bit to occur

			Thread.sleep(3000);

			try {
				failoverConnection.createStatement().executeQuery("SELECT 1");
				fail("We expect an exception here, because the connection should be gone until the reconnect code picks it up again");
			} catch (SQLException sqlEx) {
				; // do-nothing
			}

			// Tickle re-connect

			failoverConnection.setAutoCommit(true);

			String newConnectionId = getSingleIndexedValueWithQuery(
					failoverConnection, 1, "SELECT connection_id()").toString();
			System.out.println("new Connection Id = " + newConnectionId);

			assertTrue(
					"We should have a new connection to the server in this case",
					!newConnectionId.equals(originalConnectionId));
			assertTrue("Connection should not be read-only",
					!failoverConnection.isReadOnly());
		} finally {
			if (failoverConnection != null) {
				failoverConnection.close();
			}
		}

	}

	public void testCannedConfigs() throws Exception {
		String url = "jdbc:mysql:///?useConfigs=clusterBase";

		Properties cannedProps = new NonRegisteringDriver().parseURL(url, null);

		assertTrue("true".equals(cannedProps.getProperty("autoReconnect")));
		assertTrue("false".equals(cannedProps.getProperty("failOverReadOnly")));
		assertTrue("true".equals(cannedProps
				.getProperty("roundRobinLoadBalance")));

		// this will fail, but we test that too
		url = "jdbc:mysql:///?useConfigs=clusterBase,clusterBase2";

		try {
			cannedProps = new NonRegisteringDriver().parseURL(url, null);
			fail("should've bailed on that one!");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_INVALID_CONNECTION_ATTRIBUTE
					.equals(sqlEx.getSQLState()));
		}
	}

	public void testUseOldUTF8Behavior() throws Exception {

		Properties props = new Properties();
		props.setProperty("useOldUTF8Behavior", "true");
		props.setProperty("useUnicode", "true");
		props.setProperty("characterEncoding", "UTF-8");
		props.setProperty("logFactory", "com.mysql.jdbc.log.StandardLogger");
		props.setProperty("profileSQL", "true");
		StandardLogger.saveLogsToBuffer();
		StandardLogger.bufferedLog.setLength(0);

		try {
			getConnectionWithProps(props);

			assertTrue(StringUtils.indexOfIgnoreCase(StandardLogger.bufferedLog
					.toString(), "SET NAMES utf8") == -1);
		} finally {
			StandardLogger.bufferedLog = null;
		}
	}

	/**
	 * Checks implementation of 'dontTrackOpenResources' property.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testDontTrackOpenResources() throws Exception {
		Properties props = new Properties();

		props.setProperty("dontTrackOpenResources", "true");
		Connection noTrackConn = null;
		Statement noTrackStatement = null;
		PreparedStatement noTrackPstmt = null;
		ResultSet rs2 = null;

		try {
			noTrackConn = getConnectionWithProps(props);
			noTrackStatement = noTrackConn.createStatement();
			noTrackPstmt = noTrackConn.prepareStatement("SELECT 1");
			rs2 = noTrackPstmt.executeQuery();
			rs2.next();

			this.rs = noTrackStatement.executeQuery("SELECT 1");
			this.rs.next();

			noTrackConn.close();

			// Under 'strict' JDBC requirements, these calls should fail
			// (and _do_ if dontTrackOpenResources == false)

			this.rs.getString(1);
			rs2.getString(1);
		} finally {
			if (rs2 != null) {
				rs2.close();
			}

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

			if (noTrackConn != null & !noTrackConn.isClosed()) {
				noTrackConn.close();
			}
		}
	}

	public void testPing() throws SQLException {
		Connection conn2 = getConnectionWithProps(null);

		((com.mysql.jdbc.Connection) conn2).ping();
		conn2.close();

		try {
			((com.mysql.jdbc.Connection) conn2).ping();
			fail("Should have failed with an exception");
		} catch (SQLException sqlEx) {
			// ignore for now
		}

		//
		// This feature caused BUG#8975, so check for that too!

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

		getConnectionWithProps(props);
	}

	public void testSessionVariables() throws Exception {
		String getInitialMaxAllowedPacket = getMysqlVariable("max_allowed_packet");

		int newMaxAllowedPacket = Integer.parseInt(getInitialMaxAllowedPacket) + 1024;

		Properties props = new Properties();
		props.setProperty("sessionVariables", "max_allowed_packet="
				+ newMaxAllowedPacket);
		props.setProperty("profileSQL", "true");

		Connection varConn = getConnectionWithProps(props);

		assertTrue(!getInitialMaxAllowedPacket.equals(getMysqlVariable(varConn,
				"max_allowed_packet")));
	}

	/**
	 * Tests setting profileSql on/off in the span of one connection.
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testSetProfileSql() throws Exception {
		((com.mysql.jdbc.Connection) this.conn).setProfileSql(false);
		stmt.executeQuery("SELECT 1");
		((com.mysql.jdbc.Connection) this.conn).setProfileSql(true);
		stmt.executeQuery("SELECT 1");
	}

	public void testCreateDatabaseIfNotExist() throws Exception {
		if (isAdminConnectionConfigured()) {
			Properties props = new Properties();
			props.setProperty("createDatabaseIfNotExist", "true");
			props.setProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY,
					"testcreatedatabaseifnotexists");

			Connection newConn = getAdminConnectionWithProps(props);
			newConn.createStatement().executeUpdate(
					"DROP DATABASE testcreatedatabaseifnotexists");
		}
	}
}