connectiontest.java

用于JAVA数据库连接.解压就可用,方便得很

			Connection newConn = getAdminConnectionWithProps(props);
			newConn.createStatement().executeUpdate(
					"DROP DATABASE testcreatedatabaseifnotexists");
		}
	}
    
    /**
     * Tests if gatherPerfMetrics works.
     * 
     * @throws Exception if the test fails
     */
    public void testGatherPerfMetrics() throws Exception {
        if(versionMeetsMinimum(4, 1)) {
            try {
                Properties props = new Properties();
                props.put("autoReconnect", "true");
                props.put("relaxAutoCommit", "true");
                props.put("logSlowQueries", "true");
                props.put("slowQueryThresholdMillis", "2000");
                // these properties were reported as the cause of NullPointerException
                props.put("gatherPerfMetrics", "true"); 
                props.put("reportMetricsIntervalMillis", "3000"); 
                
                Connection conn1 = getConnectionWithProps(props);
                Statement stmt1 = conn1.createStatement();
                ResultSet rs1 = stmt1.executeQuery("SELECT 1");
                rs1.next();
                conn1.close();
            } catch (NullPointerException e) {
                e.printStackTrace();
                fail();
            }
        }
    }

    /**
     * Tests if useCompress works.
     * 
     * @throws Exception if the test fails
     */
    public void testUseCompress() throws Exception {
        Properties props = new Properties();
        props.put("useCompression", "true");
        props.put("traceProtocol", "true");
        Connection conn1 = getConnectionWithProps(props);
        Statement stmt1 = conn1.createStatement();
        ResultSet rs1 = stmt1.executeQuery("SELECT VERSION()");
        rs1.next();
        rs1.getString(1);
        stmt1.close();
        conn1.close();
    }
    
    /**
     * Tests feature of "localSocketAddress", by enumerating local IF's and
     * trying each one in turn. This test might take a long time to run, since
     * we can't set timeouts if we're using localSocketAddress. We try and keep
     * the time down on the testcase by spawning the checking of each interface
     * off into separate threads.
     * 
     * @throws Exception if the test can't use at least one of the local machine's
     *                   interfaces to make an outgoing connection to the server.
     */
    public void testLocalSocketAddress() throws Exception {
    	if (isRunningOnJdk131()) { 
    		return;
    	}
    	
    	Enumeration allInterfaces = NetworkInterface.getNetworkInterfaces();
    	
    	
    	SpawnedWorkerCounter counter = new SpawnedWorkerCounter();
    	
    	List allChecks = new ArrayList();
    	
    	while (allInterfaces.hasMoreElements()) {
    		NetworkInterface intf = (NetworkInterface)allInterfaces.nextElement();
    		
    		Enumeration allAddresses = intf.getInetAddresses();

    		allChecks.add(new LocalSocketAddressCheckThread(allAddresses, counter));
    	}
    	
    	counter.setWorkerCount(allChecks.size());
    	
    	for (Iterator it = allChecks.iterator(); it.hasNext();) {
    		LocalSocketAddressCheckThread t = (LocalSocketAddressCheckThread)it.next();
    		t.start();
    	}
    	
    	// Wait for tests to complete....
    	synchronized (counter) {
    	
    		while (counter.workerCount > 0 /* safety valve */) {
    		
    			counter.wait();

    			if (counter.workerCount == 0) {
    				System.out.println("Done!");
    				break;
    			}
    		}
    	}
    	
    	boolean didOneWork = false;
    	boolean didOneFail = false;
    	
    	for (Iterator it = allChecks.iterator(); it.hasNext();) {
    		LocalSocketAddressCheckThread t = (LocalSocketAddressCheckThread)it.next();

    		if (t.atLeastOneWorked) {
    			didOneWork = true;
    			
    			break;
    		} else {
    			if (!didOneFail) {
    				didOneFail = true;
    			}
    		}
    	}
    	
    	assertTrue("At least one connection was made with the localSocketAddress set", didOneWork);
    	
    	NonRegisteringDriver d = new NonRegisteringDriver();
    	
    	String hostname = d.host(d.parseURL(dbUrl, null));
    	
    	if (!hostname.startsWith(":") && !hostname.startsWith("localhost")) {
    		
    		int indexOfColon = hostname.indexOf(":");
    		
    		if (indexOfColon != -1) {
    			hostname = hostname.substring(0, indexOfColon);
    		}
    		
    		boolean isLocalIf = false;
    		
    		isLocalIf = (null != NetworkInterface.getByName(hostname));
    		
    		if (!isLocalIf) {
    			try {
    				isLocalIf = (null != NetworkInterface.getByInetAddress(InetAddress.getByName(hostname)));
    			} catch (Throwable t) {
    				isLocalIf = false;
    			}
    		}
    		
    		if (!isLocalIf) {
    			assertTrue("At least one connection didn't fail with localSocketAddress set", didOneFail);
    		}
    	}
    }
    
    class SpawnedWorkerCounter {
    	private int workerCount = 0;
    	
    	synchronized void setWorkerCount(int i) {
    		workerCount = i;
    	}
    	
    	synchronized void decrementWorkerCount() {
    		workerCount--;
    		notify();
    	}
    }
    
    class LocalSocketAddressCheckThread extends Thread {
    	boolean atLeastOneWorked = false;
    	Enumeration allAddresses = null;
    	SpawnedWorkerCounter counter = null;
    	
    	LocalSocketAddressCheckThread(Enumeration e, SpawnedWorkerCounter c) {
    		allAddresses = e;
    		counter = c;
    	}
    	
    	public void run() {
    		
    		while (allAddresses.hasMoreElements()) {
    			InetAddress addr = (InetAddress)allAddresses.nextElement();
    			
    			try {
    				Properties props = new Properties();
    				props.setProperty("localSocketAddress", addr.getHostAddress());
    				props.setProperty("connectTimeout", "2000");
    				getConnectionWithProps(props).close();
    				
    				atLeastOneWorked = true;
    				
    				break;
    			} catch (SQLException sqlEx) {
    				// ignore, we're only seeing if one of these tests succeeds
    			}
    		}
    		
    		counter.decrementWorkerCount();
    	}
    }
    
    public void testUsageAdvisorTooLargeResultSet() throws Exception {
    	Connection uaConn = null;
    	
    	PrintStream stderr = System.err;
    	
    	StringBuffer logBuf = new StringBuffer();
    	
    	StandardLogger.bufferedLog = logBuf;
    	
    	try {
    		Properties props = new Properties();
    		props.setProperty("useUsageAdvisor", "true");
    		props.setProperty("resultSetSizeThreshold", "4");
    		props.setProperty("logger", "StandardLogger");
    		
    		uaConn = getConnectionWithProps(props);
    		
    		assertTrue("Result set threshold message not present", 
    				logBuf.toString().indexOf("larger than \"resultSetSizeThreshold\" of 4 rows") != -1);
    	} finally {
    		System.setErr(stderr);
    		
    		closeMemberJDBCResources();
    		
    		if (uaConn != null) {
    			uaConn.close();
    		}
    	}
    }
    
    public void testUseLocalSessionStateRollback() throws Exception {
    	if (!versionMeetsMinimum(5, 0, 0)) {
    		return;
    	}
    	
    	Properties props = new Properties();
    	props.setProperty("useLocalSessionState", "true");
    	props.setProperty("profileSQL", "true");
    	
    	StringBuffer buf = new StringBuffer();
    	StandardLogger.bufferedLog = buf;
    	
    	createTable("testUseLocalSessionState", "(field1 varchar(32)) ENGINE=InnoDB");
    	
    	Connection localStateConn = null;
    	Statement localStateStmt = null;
    	
    	try {
    		localStateConn = getConnectionWithProps(props);
        	localStateStmt = localStateConn.createStatement();
        	
	    	localStateConn.setAutoCommit(false);
	    	localStateStmt.executeUpdate("INSERT INTO testUseLocalSessionState VALUES ('abc')");
	    	localStateConn.rollback();
	    	localStateConn.rollback();
	    	localStateStmt.executeUpdate("INSERT INTO testUseLocalSessionState VALUES ('abc')");
	    	localStateConn.commit();
	    	localStateConn.commit();
	    	localStateStmt.close();
    	} finally {
    		StandardLogger.bufferedLog = null;
    		 
    		if (localStateStmt != null) {
    			localStateStmt.close();
    		}
    		
    		if (localStateConn != null) {
    			localStateConn.close();
    		}
    	}
    	
    	int rollbackCount = 0;
    	int rollbackPos = 0;
    	
    	String searchIn = buf.toString();
    	
    	while (rollbackPos != -1) {
    		rollbackPos = searchIn.indexOf("rollback", rollbackPos);
    		
    		if (rollbackPos != -1) {
    			rollbackPos += "rollback".length();
    			rollbackCount++;
    		}
    	}
    	
    	assertEquals(1, rollbackCount);
    	
    	int commitCount = 0;
    	int commitPos = 0;
    	
    	// space is important here, we don't want to count "autocommit"
    	while (commitPos != -1) {
    		commitPos = searchIn.indexOf(" commit", commitPos);
    		
    		if (commitPos != -1) {
    			commitPos += " commit".length();
    			commitCount++;
    		}
    	}
    	
    	assertEquals(1, commitCount);
    }
    
    /**
     * Checks if setting useCursorFetch to "true" automatically
     * enables server-side prepared statements.
     */
     
    public void testCouplingOfCursorFetch() throws Exception {
    	if (!versionMeetsMinimum(5, 0)) {
    		return;
    	}
    	
    	Connection fetchConn = null;
    	
    	try {
    		Properties props = new Properties();
    		props.setProperty("useServerPrepStmts", "false"); // force the issue
    		props.setProperty("useCursorFetch", "true");
    		fetchConn = getConnectionWithProps(props);
    		assertEquals("com.mysql.jdbc.ServerPreparedStatement",
    				fetchConn.prepareStatement("SELECT 1").getClass().getName());
    	} finally {
    		if (fetchConn != null) {
    			fetchConn.close();
    		}
    	}
    }
    
    public void testInterfaceImplementation() throws Exception {
    	testInterfaceImplementation(getConnectionWithProps((Properties)null));
    	MysqlConnectionPoolDataSource cpds = new MysqlConnectionPoolDataSource();
    	cpds.setUrl(dbUrl);
    	testInterfaceImplementation(cpds.getPooledConnection().getConnection());
    }
    
    private void testInterfaceImplementation(Connection connToCheck) throws Exception {
    	Method[] dbmdMethods = java.sql.DatabaseMetaData.class.getMethods();
    	
    	// can't do this statically, as we return different
    	// implementations depending on JDBC version
    	DatabaseMetaData dbmd = connToCheck.getMetaData();
    	
    	checkInterfaceImplemented(dbmdMethods, dbmd.getClass(), dbmd);
    	
    	Statement stmtToCheck = connToCheck.createStatement();
    	
    	checkInterfaceImplemented(java.sql.Statement.class.getMethods(), stmtToCheck.getClass(), stmtToCheck);
    	
    	PreparedStatement pStmtToCheck = connToCheck.prepareStatement("SELECT 1");
    	ParameterMetaData paramMd = pStmtToCheck.getParameterMetaData();
    	
    	checkInterfaceImplemented(java.sql.PreparedStatement.class.getMethods(), pStmtToCheck.getClass(), pStmtToCheck);
    	checkInterfaceImplemented(java.sql.ParameterMetaData.class.getMethods(), paramMd.getClass(), paramMd);
    	
    	pStmtToCheck = ((com.mysql.jdbc.Connection) connToCheck).serverPrepareStatement("SELECT 1");
    	
    	checkInterfaceImplemented(java.sql.PreparedStatement.class.getMethods(), pStmtToCheck.getClass(), pStmtToCheck);
    	ResultSet toCheckRs = connToCheck.createStatement().executeQuery("SELECT 1");
    	checkInterfaceImplemented(java.sql.ResultSet.class.getMethods(), toCheckRs.getClass(), toCheckRs);
    	toCheckRs = connToCheck.createStatement().executeQuery("SELECT 1");
    	checkInterfaceImplemented(java.sql.ResultSetMetaData.class.getMethods(), toCheckRs.getMetaData().getClass(), toCheckRs.getMetaData());
    	
    	if (versionMeetsMinimum(5, 0, 0)) {
    		createProcedure("interfaceImpl", "(IN p1 INT)\nBEGIN\nSELECT 1;\nEND");
    		
    		CallableStatement cstmt = connToCheck.prepareCall("{CALL interfaceImpl(?)}");
    		
    		checkInterfaceImplemented(java.sql.CallableStatement.class.getMethods(), cstmt.getClass(), cstmt);
    	}
    	checkInterfaceImplemented(java.sql.Connection.class.getMethods(), connToCheck.getClass(), connToCheck);
    }

	private void checkInterfaceImplemented(Method[] interfaceMethods,
			Class implementingClass, Object invokeOn) throws NoSuchMethodException {
		for (int i = 0; i < interfaceMethods.length; i++) {
    		Method toFind = interfaceMethods[i];
    		Method toMatch = implementingClass.getMethod(toFind.getName(), toFind.getParameterTypes());
    		assertNotNull(toFind.toString(), toMatch);

    		Object[] args = new Object[toFind.getParameterTypes().length];
    		
    		try {
				toMatch.invoke(invokeOn, args);
			} catch (IllegalArgumentException e) {
				
			} catch (IllegalAccessException e) {
				
			} catch (InvocationTargetException e) {
				
			} catch (java.lang.AbstractMethodError e) {
				throw e;
			}
    	}
	}
}