metadatatest.java

开发MySql数据库的最新JDBC驱动。

				assertTrue(((Boolean) this.rs.getObject(1)).booleanValue());
				assertTrue(!((Boolean) this.rs.getObject(2)).booleanValue());

				assertEquals(this.rs.getObject(3), null);
				byte[] asBytesTrue = this.rs.getBytes(1);
				byte[] asBytesFalse = this.rs.getBytes(2);
				byte[] asBytesNull = this.rs.getBytes(3);

				assertEquals(asBytesTrue[0], 1);
				assertEquals(asBytesFalse[0], 0);
				assertEquals(asBytesNull, null);

				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBitField");
				this.stmt
						.executeUpdate("CREATE TABLE testBitField(field1 BIT(9))");
				this.rs = this.stmt
						.executeQuery("SELECT field1 FROM testBitField");
				System.out.println(this.rs.getMetaData().getColumnClassName(1));
			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBitType");
			}
		}
	}

	public void testSupportsSelectForUpdate() throws Exception {
		boolean supportsForUpdate = this.conn.getMetaData()
				.supportsSelectForUpdate();

		if (this.versionMeetsMinimum(4, 0)) {
			assertTrue(supportsForUpdate);
		} else {
			assertTrue(!supportsForUpdate);
		}
	}

	public void testTinyint1IsBit() throws Exception {
		String tableName = "testTinyint1IsBit";
		// Can't use 'BIT' or boolean
		createTable(tableName, "(field1 TINYINT(1))");
		this.stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (1)");

		Properties props = new Properties();
		props.setProperty("tinyint1IsBit", "true");
		props.setProperty("transformedBitIsBoolean", "true");
		Connection boolConn = getConnectionWithProps(props);

		this.rs = boolConn.createStatement().executeQuery(
				"SELECT field1 FROM " + tableName);
		checkBitOrBooleanType(false);

		this.rs = boolConn.prepareStatement("SELECT field1 FROM " + tableName)
				.executeQuery();
		checkBitOrBooleanType(false);

		this.rs = boolConn.getMetaData().getColumns(boolConn.getCatalog(),
				null, tableName, "field1");
		assertTrue(this.rs.next());

		if (versionMeetsMinimum(4, 1)) {
			assertEquals(Types.BOOLEAN, this.rs.getInt("DATA_TYPE"));
		} else {
			assertEquals(Types.BIT, this.rs.getInt("DATA_TYPE"));
		}

		if (versionMeetsMinimum(4, 1)) {
			assertEquals("BOOLEAN", this.rs.getString("TYPE_NAME"));
		} else {
			assertEquals("BIT", this.rs.getString("TYPE_NAME"));
		}

		props.clear();
		props.setProperty("transformedBitIsBoolean", "false");
		props.setProperty("tinyint1IsBit", "true");

		Connection bitConn = getConnectionWithProps(props);

		this.rs = bitConn.createStatement().executeQuery(
				"SELECT field1 FROM " + tableName);
		checkBitOrBooleanType(true);

		this.rs = bitConn.prepareStatement("SELECT field1 FROM " + tableName)
				.executeQuery();
		checkBitOrBooleanType(true);

		this.rs = bitConn.getMetaData().getColumns(boolConn.getCatalog(), null,
				tableName, "field1");
		assertTrue(this.rs.next());

		assertEquals(Types.BIT, this.rs.getInt("DATA_TYPE"));

		assertEquals("BIT", this.rs.getString("TYPE_NAME"));
	}

	private void checkBitOrBooleanType(boolean usingBit) throws SQLException {

		assertTrue(this.rs.next());
		assertEquals("java.lang.Boolean", this.rs.getObject(1).getClass()
				.getName());
		if (!usingBit) {
			if (versionMeetsMinimum(4, 1)) {
				assertEquals(Types.BOOLEAN, this.rs.getMetaData()
						.getColumnType(1));
			} else {
				assertEquals(Types.BIT, this.rs.getMetaData().getColumnType(1));
			}
		} else {
			assertEquals(Types.BIT, this.rs.getMetaData().getColumnType(1));
		}

		assertEquals("java.lang.Boolean", this.rs.getMetaData()
				.getColumnClassName(1));
	}
    
    /**
     * Tests the implementation of Information Schema for primary keys.
     */
    public void testGetPrimaryKeysUsingInfoShcema() throws Exception {
        if (versionMeetsMinimum(5, 0, 7)) {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS t1");
            this.stmt.executeUpdate("CREATE TABLE t1 (c1 int(1) primary key)");
            Properties props = new Properties();
            props.put("useInformationSchema", "true");
            Connection conn1 = null;
            try {
                conn1 = getConnectionWithProps(props);
                DatabaseMetaData metaData = conn1.getMetaData();
                this.rs = metaData.getPrimaryKeys(null, null, "t1");
                this.rs.next();
                assertEquals("t1", this.rs.getString("TABLE_NAME"));
                assertEquals("c1", this.rs.getString("COLUMN_NAME"));
            } finally {
                conn1.close();
            }
        }
    }
    
    /**
     * Tests the implementation of Information Schema for index info.
     */
    public void testGetIndexInfoUsingInfoSchema() throws Exception {
        if (versionMeetsMinimum(5, 0, 7)) {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS t1");
            this.stmt.executeUpdate("CREATE TABLE t1 (c1 int(1))");
            this.stmt.executeUpdate("CREATE INDEX index1 ON t1 (c1)");
            Properties props = new Properties();
            props.put("useInformationSchema", "true");
            Connection conn1 = null;
            try {
                conn1 = getConnectionWithProps(props);
                DatabaseMetaData metaData = conn1.getMetaData();
                this.rs = metaData.getIndexInfo("test", null, "t1", false, true);
                this.rs.next();
                assertEquals("t1", this.rs.getString("TABLE_NAME"));
                assertEquals("c1", this.rs.getString("COLUMN_NAME"));
                assertEquals("1", this.rs.getString("NON_UNIQUE"));
                assertEquals("index1", this.rs.getString("INDEX_NAME"));
            } finally {
                conn1.close();
            }
        }
    }
    
    /**
     * Tests the implementation of Information Schema for columns.
     */
    public void testGetColumnsUsingInfoSchema() throws Exception {
        if (versionMeetsMinimum(5, 0, 7)) {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS t1");
            this.stmt.executeUpdate("CREATE TABLE t1 (c1 char(1))");
            Properties props = new Properties();
            props.put("useInformationSchema", "true");
            Connection conn1 = null;
            try {
            conn1 = getConnectionWithProps(props);
                DatabaseMetaData metaData = conn1.getMetaData();
                this.rs = metaData.getColumns(null, null, "t1", null);
                this.rs.next();
                assertEquals("t1", this.rs.getString("TABLE_NAME"));
                assertEquals("c1", this.rs.getString("COLUMN_NAME"));
                assertEquals("char", this.rs.getString("TYPE_NAME"));
                assertEquals("1", this.rs.getString("COLUMN_SIZE"));
            } finally {
                conn1.close();
            }
        }
    }
    
    /**
     * Tests the implementation of Information Schema for tables.
     */
    public void testGetTablesUsingInfoSchema() throws Exception {
        if (versionMeetsMinimum(5, 0, 7)) {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS `t1-1`");
            this.stmt.executeUpdate("CREATE TABLE `t1-1` (c1 char(1))");
            this.stmt.executeUpdate("DROP TABLE IF EXISTS `t1-2`");
            this.stmt.executeUpdate("CREATE TABLE `t1-2` (c1 char(1))");
            this.stmt.executeUpdate("DROP TABLE IF EXISTS `t2`");
            this.stmt.executeUpdate("CREATE TABLE `t2` (c1 char(1))");
            Set tableNames = new HashSet();
            tableNames.add("t1-1");
            tableNames.add("t1-2");
            Properties props = new Properties();
            props.put("useInformationSchema", "true");
            Connection conn1 = null;
            try {
                conn1 = getConnectionWithProps(props);
                DatabaseMetaData metaData = conn1.getMetaData();
                // pattern matching for table name
                this.rs = metaData.getTables(null, null, "t1-_", null);
                while (this.rs.next()) {
                    assertTrue(tableNames.remove(this.rs.getString("TABLE_NAME")));
                }
                assertTrue(tableNames.isEmpty());
            } finally {
                conn1.close();
            }
        }
    }
    
    /**
     * Tests the implementation of Information Schema for column privileges.
     */
    public void testGetColumnPrivilegesUsingInfoSchema() throws Exception {
    	String dontRunPropertyName = "com.mysql.jdbc.testsuite.cantGrant";
    	
    	if (!runTestIfSysPropDefined(dontRunPropertyName)) {
	        if (versionMeetsMinimum(5, 0, 7)) {
	            Properties props = new Properties();
	            
	            props.put("useInformationSchema", "true");
	            Connection conn1 = null;
	            Statement stmt1 = null;
	            String userHostQuoted = null;
	            
	            boolean grantFailed = true;
	            
	            try {
	                conn1 = getConnectionWithProps(props);
	                stmt1 = conn1.createStatement();
	                stmt1.executeUpdate("DROP TABLE IF EXISTS t1");
	                stmt1.executeUpdate("CREATE TABLE t1 (c1 int)");
	                this.rs = stmt1.executeQuery("SELECT USER()");
	                this.rs.next();
	                String user = this.rs.getString(1);
	                List userHost = StringUtils.split(user, "@", false);
	                userHostQuoted = "'" + userHost.get(0) + "'@'" + userHost.get(1) + "'";
	                
	                try {
	                	stmt1.executeUpdate("GRANT update (c1) on t1 to " + userHostQuoted);
	                	
	                	grantFailed = false;
	                	
	                } catch (SQLException sqlEx) {
	                	logDebug("This testcase needs to be run with a URL that allows the user to issue GRANTs "
	                			+ " in the current database. You can skip this test by setting the system property \""
	                			+ dontRunPropertyName + "\".");
	                	
	                	grantFailed = true;
	                }
	                
	                if (!grantFailed) {
		                DatabaseMetaData metaData = conn1.getMetaData();
		                this.rs = metaData.getColumnPrivileges(null, null, "t1", null);
		                this.rs.next();
		                assertEquals("t1", this.rs.getString("TABLE_NAME"));
		                assertEquals("c1", this.rs.getString("COLUMN_NAME"));
		                assertEquals(userHostQuoted, this.rs.getString("GRANTEE"));
		                assertEquals("UPDATE", this.rs.getString("PRIVILEGE"));
	                }
	            } finally {
		            if (stmt1 != null) {
		       
		            	stmt1.executeUpdate("DROP TABLE IF EXISTS t1");
		            	
		            	if (!grantFailed) {
		            		stmt1.executeUpdate("REVOKE UPDATE (c1) ON t1 FROM " + userHostQuoted);
		            	}
		            	
		            	stmt1.close();
	            	}
	            	
	            	if (conn1 != null) {
	            		conn1.close();
	            	}
	            }
	        }
    	}
    }
    
    /**
     * Tests the implementation of Information Schema for description
     * of stored procedures available in a catalog.
     */
    public void testGetProceduresUsingInfoSchema() throws Exception {
        if (versionMeetsMinimum(5, 0, 7)) {
            this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS sp1");
            this.stmt.executeUpdate("CREATE PROCEDURE sp1()\n BEGIN\n" + "SELECT 1;" + "end\n");
            Properties props = new Properties();
            props.put("useInformationSchema", "true");
            Connection conn1 = null;
            try {
                conn1 = getConnectionWithProps(props);
                DatabaseMetaData metaData = conn1.getMetaData();
                this.rs = metaData.getProcedures(null, null, "sp1");
                this.rs.next();
                assertEquals("sp1", this.rs.getString("PROCEDURE_NAME"));
                assertEquals("1", this.rs.getString("PROCEDURE_TYPE"));
            } finally {
                conn1.close();
                this.stmt.executeUpdate("DROP PROCEDURE sp1");
            }
        }
    }
    
    /**
     * Tests the implementation of Information Schema for foreign key.
     */
    public void testGetCrossReferenceUsingInfoSchema() throws Exception {
        if (versionMeetsMinimum(5, 0, 7)) {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
            this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
            this.stmt.executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
                + "PRIMARY KEY (id)) ENGINE=INNODB");
            this.stmt.executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
                + "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
            Properties props = new Properties();
            props.put("useInformationSchema", "true");
            Connection conn1 = null;
            try {
                conn1 = getConnectionWithProps(props);
                DatabaseMetaData metaData = conn1.getMetaData();
                this.rs = metaData.getCrossReference(null, null, "parent", null, null, "child");
                this.rs.next();
                assertEquals("parent", this.rs.getString("PKTABLE_NAME"));
                assertEquals("id", this.rs.getString("PKCOLUMN_NAME"));
                assertEquals("child", this.rs.getString("FKTABLE_NAME"));
                assertEquals("parent_id", this.rs.getString("FKCOLUMN_NAME"));
            } finally {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
                this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
                conn1.close();
            }
        }
    }
    
    /**
     * Tests the implementation of Information Schema for foreign key.
     */
    public void testGetExportedKeysUsingInfoSchema() throws Exception {
        if (versionMeetsMinimum(5, 0, 7)) {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
            this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
            this.stmt.executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
                + "PRIMARY KEY (id)) ENGINE=INNODB");
            this.stmt.executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
                + "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
            Properties props = new Properties();
            props.put("useInformationSchema", "true");
            Connection conn1 = null;
            try {
                conn1 = getConnectionWithProps(props);
                DatabaseMetaData metaData = conn1.getMetaData();
                this.rs = metaData.getExportedKeys(null, null, "parent");
                this.rs.next();
                assertEquals("parent", this.rs.getString("PKTABLE_NAME"));
                assertEquals("id", this.rs.getString("PKCOLUMN_NAME"));
                assertEquals("child", this.rs.getString("FKTABLE_NAME"));
                assertEquals("parent_id", this.rs.getString("FKCOLUMN_NAME"));
            } finally {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
                this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
                conn1.close();
            }
        }
    }
    
    /**
     * Tests the implementation of Information Schema for foreign key.
     */
    public void testGetImportedKeysUsingInfoSchema() throws Exception {
        if (versionMeetsMinimum(5, 0, 7)) {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
            this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
            this.stmt.executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
                + "PRIMARY KEY (id)) ENGINE=INNODB");
            this.stmt.executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
                + "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
            Properties props = new Properties();
            props.put("useInformationSchema", "true");
            Connection conn1 = null;
            try {
                conn1 = getConnectionWithProps(props);
                DatabaseMetaData metaData = conn1.getMetaData();
                this.rs = metaData.getImportedKeys(null, null, "child");
                this.rs.next();
                assertEquals("parent", this.rs.getString("PKTABLE_NAME"));
                assertEquals("id", this.rs.getString("PKCOLUMN_NAME"));
                assertEquals("child", this.rs.getString("FKTABLE_NAME"));
                assertEquals("parent_id", this.rs.getString("FKCOLUMN_NAME"));
            } finally {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
                this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
                conn1.close();
            }
        }
    }
}