metadataregressiontest.java

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

						this.conn.getCatalog(), null, tableName, "%");
				assertTrue(this.rs.next());
				assertEquals(Types.BINARY, this.rs.getInt("DATA_TYPE"));
				assertEquals(32, this.rs.getInt("COLUMN_SIZE"));
				assertTrue(this.rs.next());
				assertEquals(Types.VARBINARY, this.rs.getInt("DATA_TYPE"));
				assertEquals(64, this.rs.getInt("COLUMN_SIZE"));
				this.rs.close();

				this.rs = this.stmt
						.executeQuery("SELECT binary_field, varbinary_field FROM "
								+ tableName);
				ResultSetMetaData rsmd = this.rs.getMetaData();
				assertEquals(Types.BINARY, rsmd.getColumnType(1));
				assertEquals(32, rsmd.getPrecision(1));
				assertEquals(Types.VARBINARY, rsmd.getColumnType(2));
				assertEquals(64, rsmd.getPrecision(2));
				this.rs.close();
			} finally {
				if (this.rs != null) {
					this.rs.close();
				}
			}
		}
	}

	/**
	 * Tests fix for BUG#12975 - OpenOffice expects DBMD.supportsIEF() to return
	 * "true" if foreign keys are supported by the datasource, even though this
	 * method also covers support for check constraints, which MySQL _doesn't_
	 * have.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug12975() throws Exception {
		assertEquals(false, this.conn.getMetaData()
				.supportsIntegrityEnhancementFacility());

		Connection overrideConn = null;

		try {
			Properties props = new Properties();

			props.setProperty("overrideSupportsIntegrityEnhancementFacility",
					"true");

			overrideConn = getConnectionWithProps(props);
			assertEquals(true, overrideConn.getMetaData()
					.supportsIntegrityEnhancementFacility());
		} finally {
			if (overrideConn != null) {
				overrideConn.close();
			}
		}
	}

	/**
	 * Tests fix for BUG#13277 - RSMD for generated keys has NPEs when a
	 * connection is referenced.
	 * 
	 * @throws Exception
	 */
	public void testBug13277() throws Exception {
		if (isRunningOnJdk131()) {
			return; // test not valid on JDK-1.3.1
		}

		createTable("testBug13277",
				"(field1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT, field2 VARCHAR(32))");

		try {
			this.stmt.executeUpdate(
					"INSERT INTO testBug13277 (field2) VALUES ('abcdefg')",
					Statement.RETURN_GENERATED_KEYS);

			this.rs = this.stmt.getGeneratedKeys();

			ResultSetMetaData rsmd = this.rs.getMetaData();
			checkRsmdForBug13277(rsmd);
			this.rs.close();

			for (int i = 0; i < 5; i++) {
				this.stmt
						.addBatch("INSERT INTO testBug13277 (field2) VALUES ('abcdefg')");
			}

			this.stmt.executeBatch();

			this.rs = this.stmt.getGeneratedKeys();

			rsmd = this.rs.getMetaData();
			checkRsmdForBug13277(rsmd);
			this.rs.close();

			this.pstmt = this.conn.prepareStatement(
					"INSERT INTO testBug13277 (field2) VALUES ('abcdefg')",
					Statement.RETURN_GENERATED_KEYS);
			this.pstmt.executeUpdate();

			this.rs = this.pstmt.getGeneratedKeys();

			rsmd = this.rs.getMetaData();
			checkRsmdForBug13277(rsmd);
			this.rs.close();

			this.pstmt.addBatch();
			this.pstmt.addBatch();

			this.pstmt.executeUpdate();

			this.rs = this.pstmt.getGeneratedKeys();

			rsmd = this.rs.getMetaData();
			checkRsmdForBug13277(rsmd);
			this.rs.close();

		} finally {
			if (this.pstmt != null) {
				this.pstmt.close();
				this.pstmt = null;
			}

			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
		}
	}

	/**
	 * Tests BUG13601 (which doesn't seem to be present in 3.1.11, but we'll
	 * leave it in here for regression's-sake).
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug13601() throws Exception {

		if (versionMeetsMinimum(5, 0)) {
			createTable("testBug13601",
					"(field1 BIGINT NOT NULL, field2 BIT default 0 NOT NULL) ENGINE=MyISAM");

			this.rs = this.stmt
					.executeQuery("SELECT field1, field2 FROM testBug13601 WHERE 1=-1");
			ResultSetMetaData rsmd = this.rs.getMetaData();
			assertEquals(Types.BIT, rsmd.getColumnType(2));
			assertEquals(Boolean.class.getName(), rsmd.getColumnClassName(2));

			this.rs = this.conn.prepareStatement(
					"SELECT field1, field2 FROM testBug13601 WHERE 1=-1")
					.executeQuery();
			rsmd = this.rs.getMetaData();
			assertEquals(Types.BIT, rsmd.getColumnType(2));
			assertEquals(Boolean.class.getName(), rsmd.getColumnClassName(2));

		}
	}

	/**
	 * Tests fix for BUG#14815 - DBMD.getColumns() doesn't return TABLE_NAME
	 * correctly.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug14815() throws Exception {
		try {
			createTable("testBug14815_1", "(field_1_1 int)");
			createTable("testBug14815_2", "(field_2_1 int)");

			boolean lcTableNames = this.conn.getMetaData()
					.storesLowerCaseIdentifiers();

			String tableName1 = lcTableNames ? "testbug14815_1"
					: "testBug14815_1";
			String tableName2 = lcTableNames ? "testbug14815_2"
					: "testBug14815_2";

			this.rs = this.conn.getMetaData().getColumns(
					this.conn.getCatalog(), null, "testBug14815%", "%");

			assertTrue(this.rs.next());
			assertEquals(tableName1, this.rs.getString("TABLE_NAME"));
			assertEquals("field_1_1", this.rs.getString("COLUMN_NAME"));

			assertTrue(this.rs.next());
			assertEquals(tableName2, this.rs.getString("TABLE_NAME"));
			assertEquals("field_2_1", this.rs.getString("COLUMN_NAME"));

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

	/**
	 * Tests fix for BUG#15854 - DBMD.getColumns() returns wrong type for BIT.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug15854() throws Exception {
		if (versionMeetsMinimum(5, 0)) {
			createTable("testBug15854", "(field1 BIT)");
			try {
				this.rs = this.conn.getMetaData().getColumns(
						this.conn.getCatalog(), null, "testBug15854", "field1");
				assertTrue(this.rs.next());
				assertEquals(Types.BIT, this.rs.getInt("DATA_TYPE"));
			} finally {
				if (this.rs != null) {
					ResultSet toClose = this.rs;
					this.rs = null;
					toClose.close();
				}
			}

		}
	}

	/**
	 * Tests fix for BUG#16277 - Invalid classname returned for
	 * RSMD.getColumnClassName() for BIGINT type.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug16277() throws Exception {
		createTable("testBug16277", "(field1 BIGINT, field2 BIGINT UNSIGNED)");
		ResultSetMetaData rsmd = this.stmt.executeQuery(
				"SELECT field1, field2 FROM testBug16277").getMetaData();
		assertEquals("java.lang.Long", rsmd.getColumnClassName(1));
		assertEquals("java.math.BigInteger", rsmd.getColumnClassName(2));
	}

	/**
	 * Tests fix for BUG#18554 - Aliased column names where length of name > 251
	 * are corrupted.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug18554() throws Exception {
		testBug18554(249);
		testBug18554(250);
		testBug18554(251);
		testBug18554(252);
		testBug18554(253);
		testBug18554(254);
		testBug18554(255);
	}

	private void testBug18554(int columnNameLength) throws Exception {
		StringBuffer buf = new StringBuffer(columnNameLength + 2);

		for (int i = 0; i < columnNameLength; i++) {
			buf.append((char) ((Math.random() * 26) + 65));
		}

		String colName = buf.toString();
		this.rs = this.stmt.executeQuery("select curtime() as `" + colName
				+ "`");
		ResultSetMetaData meta = this.rs.getMetaData();

		assertEquals(colName, meta.getColumnLabel(1));

	}

	private void checkRsmdForBug13277(ResultSetMetaData rsmd)
			throws SQLException {
		assertEquals(17, rsmd.getColumnDisplaySize(1));

		if (versionMeetsMinimum(4, 1)) {
			assertEquals(false, rsmd.isDefinitelyWritable(1));
			assertEquals(true, rsmd.isReadOnly(1));
			assertEquals(false, rsmd.isWritable(1));
		}
	}

	public void testSupportsCorrelatedSubqueries() throws Exception {
		DatabaseMetaData dbmd = this.conn.getMetaData();

		assertEquals(versionMeetsMinimum(4, 1), dbmd
				.supportsCorrelatedSubqueries());
	}

	public void testSupportesGroupByUnrelated() throws Exception {
		DatabaseMetaData dbmd = this.conn.getMetaData();

		assertEquals(true, dbmd.supportsGroupByUnrelated());
	}

	/**
	 * Tests fix for BUG#21267, ParameterMetaData throws NullPointerException
	 * when prepared SQL actually has a syntax error
	 * 
	 * @throws Exception
	 */
	public void testBug21267() throws Exception {
		if (isRunningOnJdk131()) {
			return; // no parameter metadata on JDK-1.3.1
		}
		
		createTable(
				"bug21267",
				"(`Col1` int(11) NOT NULL,`Col2` varchar(45) default NULL,`Col3` varchar(45) default NULL,PRIMARY KEY  (`Col1`))");

		try {
			this.pstmt = this.conn
					.prepareStatement("SELECT Col1, Col2,Col4 FROM bug21267 WHERE Col1=?");
			this.pstmt.setInt(1, 1);

			java.sql.ParameterMetaData psMeta = this.pstmt
					.getParameterMetaData();

			try {
				assertEquals(0, psMeta.getParameterType(1));
			} catch (SQLException sqlEx) {
				assertEquals(SQLError.SQL_STATE_DRIVER_NOT_CAPABLE, sqlEx.getSQLState());
			}
			
			this.pstmt.close();
			
			Properties props = new Properties();
			props.setProperty("generateSimpleParameterMetadata", "true");
			
			this.pstmt = getConnectionWithProps(props).prepareStatement("SELECT Col1, Col2,Col4 FROM bug21267 WHERE Col1=?");
			
			psMeta = this.pstmt.getParameterMetaData();
			
			assertEquals(Types.VARCHAR, psMeta.getParameterType(1));
		} finally {
			closeMemberJDBCResources();
		}
	}

	/**
	 * Tests fix for BUG#21544 - When using information_schema for metadata, 
	 * COLUMN_SIZE for getColumns() is not clamped to range of 
	 * java.lang.Integer as is the case when not using 
	 * information_schema, thus leading to a truncation exception that 
	 * isn't present when not using information_schema.
	 * 
	 * @throws Exception if the test fails
	 */
	public void testBug21544() throws Exception {
		if (!versionMeetsMinimum(5, 0)) {
			return;
		}
		
		createTable("testBug21544",
	            "(foo_id INT NOT NULL, stuff LONGTEXT"
	            + ", PRIMARY KEY (foo_id)) TYPE=INNODB");
		
		Connection infoSchemConn = null;
		
		Properties props = new Properties();
		props.setProperty("useInformationSchema", "true");
		props.setProperty("jdbcCompliantTruncation", "false");
		
		infoSchemConn = getConnectionWithProps(props);
		
		try {
	        this.rs = infoSchemConn.getMetaData().getColumns(null, null, 
	        		"testBug21544",
	                null);
	        
	        while (rs.next()) {
	        	rs.getInt("COLUMN_SIZE");   
	        }
	    } finally {
	        if (infoSchemConn != null) {
	        	infoSchemConn.close();
	        }
	        
	        closeMemberJDBCResources();
	    }
	}

	/** 
	 * Tests fix for BUG#22613 - DBMD.getColumns() does not return expected
	 * COLUMN_SIZE for the SET type (fixed to be consistent with the ODBC driver)
	 * 
	 * @throws Exception if the test fails
	 */
	public void testBug22613() throws Exception {
		
		createTable("bug22613", "( s set('a','bc','def','ghij') default NULL, t enum('a', 'ab', 'cdef'), s2 SET('1','2','3','4','1585','ONE','TWO','Y','N','THREE'))");

		try {
			checkMetadataForBug22613(this.conn);
			
			if (versionMeetsMinimum(5, 0)) {
				Connection infoSchemConn = null;
			
				try {
					Properties props = new Properties();
					props.setProperty("useInformationSchema", "true");
					
					infoSchemConn = getConnectionWithProps(props);
					
					checkMetadataForBug22613(infoSchemConn);
				} finally {
					if (infoSchemConn != null) {
						infoSchemConn.close();
					}
				}
			}
		} finally {
			closeMemberJDBCResources();
		}
	}
	
	private void checkMetadataForBug22613(Connection c) throws Exception {