metadatatest.java

mysql的jdbc驱动

						+ "ON DELETE RESTRICT ON UPDATE CASCADE"
						+ ") type=InnoDB");

		this.stmt
				.executeUpdate("create table fktable1 (TYPE_ID int not null, TYPE_DESC varchar(32), primary key(TYPE_ID)) TYPE=InnoDB");
		this.stmt
				.executeUpdate("create table fktable2 (KEY_ID int not null, COF_NAME varchar(32), PRICE float, TYPE_ID int, primary key(KEY_ID), "
						+ "index(TYPE_ID), foreign key(TYPE_ID) references fktable1(TYPE_ID)) TYPE=InnoDB");
	}

	/**
	 * Tests the implementation of metadata for views.
	 * 
	 * This test automatically detects whether or not the server it is running
	 * against supports the creation of views.
	 * 
	 * @throws SQLException
	 *             if the test fails.
	 */
	public void testViewMetaData() throws SQLException {
		try {
			this.rs = this.conn.getMetaData().getTableTypes();

			while (this.rs.next()) {
				if ("VIEW".equalsIgnoreCase(this.rs.getString(1))) {

					this.stmt
							.executeUpdate("DROP VIEW IF EXISTS vTestViewMetaData");
					this.stmt
							.executeUpdate("DROP TABLE IF EXISTS testViewMetaData");
					this.stmt
							.executeUpdate("CREATE TABLE testViewMetaData (field1 INT)");
					this.stmt
							.executeUpdate("CREATE VIEW vTestViewMetaData AS SELECT field1 FROM testViewMetaData");

					ResultSet tablesRs = null;

					try {
						tablesRs = this.conn.getMetaData().getTables(
								this.conn.getCatalog(), null, "%ViewMetaData",
								new String[] { "TABLE", "VIEW" });
						assertTrue(tablesRs.next());
						assertTrue("testViewMetaData".equalsIgnoreCase(tablesRs
								.getString(3)));
						assertTrue(tablesRs.next());
						assertTrue("vTestViewMetaData"
								.equalsIgnoreCase(tablesRs.getString(3)));

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

					try {
						tablesRs = this.conn.getMetaData().getTables(
								this.conn.getCatalog(), null, "%ViewMetaData",
								new String[] { "TABLE" });
						assertTrue(tablesRs.next());
						assertTrue("testViewMetaData".equalsIgnoreCase(tablesRs
								.getString(3)));
						assertTrue(!tablesRs.next());
					} finally {
						if (tablesRs != null) {
							tablesRs.close();
						}
					}
					break;
				}
			}

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

	/**
	 * Tests detection of read-only fields when the server is 4.1.0 or newer.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testRSMDIsReadOnly() throws Exception {
		try {
			this.rs = this.stmt.executeQuery("SELECT 1");

			ResultSetMetaData rsmd = this.rs.getMetaData();

			if (versionMeetsMinimum(4, 1)) {
				assertTrue(rsmd.isReadOnly(1));

				try {
					this.stmt
							.executeUpdate("DROP TABLE IF EXISTS testRSMDIsReadOnly");
					this.stmt
							.executeUpdate("CREATE TABLE testRSMDIsReadOnly (field1 INT)");
					this.stmt
							.executeUpdate("INSERT INTO testRSMDIsReadOnly VALUES (1)");

					this.rs = this.stmt
							.executeQuery("SELECT 1, field1 + 1, field1 FROM testRSMDIsReadOnly");
					rsmd = this.rs.getMetaData();

					assertTrue(rsmd.isReadOnly(1));
					assertTrue(rsmd.isReadOnly(2));
					assertTrue(!rsmd.isReadOnly(3));
				} finally {
					this.stmt
							.executeUpdate("DROP TABLE IF EXISTS testRSMDIsReadOnly");
				}
			} else {
				assertTrue(rsmd.isReadOnly(1) == false);
			}
		} finally {
			if (this.rs != null) {
				this.rs.close();
			}
		}
	}

	public void testBitType() throws Exception {
		if (versionMeetsMinimum(5, 0, 3)) {
			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBitType");
				this.stmt
						.executeUpdate("CREATE TABLE testBitType (field1 BIT, field2 BIT, field3 BIT)");
				this.stmt
						.executeUpdate("INSERT INTO testBitType VALUES (1, 0, NULL)");
				this.rs = this.stmt
						.executeQuery("SELECT field1, field2, field3 FROM testBitType");
				this.rs.next();

				assertTrue(((Boolean) this.rs.getObject(1)).booleanValue());
				assertTrue(!((Boolean) this.rs.getObject(2)).booleanValue());
				assertEquals(this.rs.getObject(3), null);

				System.out.println(this.rs.getObject(1) + ", "
						+ this.rs.getObject(2) + ", " + this.rs.getObject(3));

				this.rs = this.conn.prepareStatement(
						"SELECT field1, field2, field3 FROM testBitType")
						.executeQuery();
				this.rs.next();

				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));
	}
}