metadataregressiontest.java

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

					.executeUpdate("CREATE TABLE testGetUnsignedCols (field1 BIGINT, field2 BIGINT UNSIGNED)");

			DatabaseMetaData dbmd = this.conn.getMetaData();

			this.rs = dbmd.getColumns(this.conn.getCatalog(), null,
					"testGetUnsignedCols", "%");

			assertTrue(this.rs.next());
			// This row doesn't have 'unsigned' attribute
			assertTrue(this.rs.next());
			assertTrue(this.rs.getString(6).toLowerCase().indexOf("unsigned") != -1);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetUnsignedCols");
		}
	}

	/**
	 * Tests whether bogus parameters break Driver.getPropertyInfo().
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testGetPropertyInfo() throws Exception {
		new Driver().getPropertyInfo("", null);
	}

	/**
	 * Tests whether ResultSetMetaData returns correct info for CHAR/VARCHAR
	 * columns.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testIsCaseSensitive() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitive");
			this.stmt
					.executeUpdate("CREATE TABLE testIsCaseSensitive (bin_char CHAR(1) BINARY, bin_varchar VARCHAR(64) BINARY, ci_char CHAR(1), ci_varchar VARCHAR(64))");
			this.rs = this.stmt
					.executeQuery("SELECT bin_char, bin_varchar, ci_char, ci_varchar FROM testIsCaseSensitive");

			ResultSetMetaData rsmd = this.rs.getMetaData();
			assertTrue(rsmd.isCaseSensitive(1));
			assertTrue(rsmd.isCaseSensitive(2));
			assertTrue(!rsmd.isCaseSensitive(3));
			assertTrue(!rsmd.isCaseSensitive(4));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitive");
		}

		if (versionMeetsMinimum(4, 1)) {
			try {
				this.stmt
						.executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitiveCs");
				this.stmt
						.executeUpdate("CREATE TABLE testIsCaseSensitiveCs ("
								+ "bin_char CHAR(1) CHARACTER SET latin1 COLLATE latin1_general_cs,"
								+ "bin_varchar VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_general_cs,"
								+ "ci_char CHAR(1) CHARACTER SET latin1 COLLATE latin1_general_ci,"
								+ "ci_varchar VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_general_ci, "
								+ "bin_tinytext TINYTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
								+ "bin_text TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
								+ "bin_med_text MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
								+ "bin_long_text LONGTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
								+ "ci_tinytext TINYTEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
								+ "ci_text TEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
								+ "ci_med_text MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
								+ "ci_long_text LONGTEXT CHARACTER SET latin1 COLLATE latin1_general_ci)");

				this.rs = this.stmt
						.executeQuery("SELECT bin_char, bin_varchar, ci_char, ci_varchar, bin_tinytext, bin_text, bin_med_text, bin_long_text, ci_tinytext, ci_text, ci_med_text, ci_long_text FROM testIsCaseSensitiveCs");

				ResultSetMetaData rsmd = this.rs.getMetaData();
				assertTrue(rsmd.isCaseSensitive(1));
				assertTrue(rsmd.isCaseSensitive(2));
				assertTrue(!rsmd.isCaseSensitive(3));
				assertTrue(!rsmd.isCaseSensitive(4));

				assertTrue(rsmd.isCaseSensitive(5));
				assertTrue(rsmd.isCaseSensitive(6));
				assertTrue(rsmd.isCaseSensitive(7));
				assertTrue(rsmd.isCaseSensitive(8));

				assertTrue(!rsmd.isCaseSensitive(9));
				assertTrue(!rsmd.isCaseSensitive(10));
				assertTrue(!rsmd.isCaseSensitive(11));
				assertTrue(!rsmd.isCaseSensitive(12));
			} finally {
				this.stmt
						.executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitiveCs");
			}
		}
	}

	/**
	 * Tests whether or not DatabaseMetaData.getColumns() returns the correct
	 * java.sql.Types info.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testLongText() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testLongText");
			this.stmt
					.executeUpdate("CREATE TABLE testLongText (field1 LONGTEXT)");

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

			assertTrue(this.rs.next());

			assertTrue(this.rs.getInt("DATA_TYPE") == java.sql.Types.LONGVARCHAR);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testLongText");
		}
	}

	/**
	 * Tests for types being returned correctly
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testTypes() throws Exception {
		try {
			this.stmt.execute("DROP TABLE IF EXISTS typesRegressTest");
			this.stmt.execute("CREATE TABLE typesRegressTest ("
					+ "varcharField VARCHAR(32)," + "charField CHAR(2),"
					+ "enumField ENUM('1','2'),"
					+ "setField  SET('1','2','3')," + "tinyblobField TINYBLOB,"
					+ "mediumBlobField MEDIUMBLOB," + "longblobField LONGBLOB,"
					+ "blobField BLOB)");

			this.rs = this.stmt.executeQuery("SELECT * from typesRegressTest");

			ResultSetMetaData rsmd = this.rs.getMetaData();

			int numCols = rsmd.getColumnCount();

			for (int i = 0; i < numCols; i++) {
				String columnName = rsmd.getColumnName(i + 1);
				String columnTypeName = rsmd.getColumnTypeName(i + 1);
				System.out.println(columnName + " -> " + columnTypeName);
			}
		} finally {
			this.stmt.execute("DROP TABLE IF EXISTS typesRegressTest");
		}
	}

	/**
	 * Tests fix for BUG#4742, 'DOUBLE' mapped twice in getTypeInfo().
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug4742() throws Exception {
		HashMap clashMap = new HashMap();

		this.rs = this.conn.getMetaData().getTypeInfo();

		while (this.rs.next()) {
			String name = this.rs.getString(1);
			assertTrue("Type represented twice in type info, '" + name + "'.",
					!clashMap.containsKey(name));
			clashMap.put(name, name);
		}
	}

	/**
	 * Tests fix for BUG#4138, getColumns() returns incorrect JDBC type for
	 * unsigned columns.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug4138() throws Exception {
		try {
			String[] typesToTest = new String[] { "TINYINT", "SMALLINT",
					"MEDIUMINT", "INT", "BIGINT", "FLOAT", "DOUBLE",
					"DECIMAL" };

			short[] jdbcMapping = new short[] { Types.TINYINT, Types.SMALLINT,
					Types.INTEGER, Types.INTEGER, Types.BIGINT, Types.REAL,
					Types.DOUBLE, Types.DECIMAL };

			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4138");

			StringBuffer createBuf = new StringBuffer();

			createBuf.append("CREATE TABLE testBug4138 (");

			boolean firstColumn = true;

			for (int i = 0; i < typesToTest.length; i++) {
				if (!firstColumn) {
					createBuf.append(", ");
				} else {
					firstColumn = false;
				}

				createBuf.append("field");
				createBuf.append((i + 1));
				createBuf.append(" ");
				createBuf.append(typesToTest[i]);
				createBuf.append(" UNSIGNED");
			}
			createBuf.append(")");
			this.stmt.executeUpdate(createBuf.toString());

			DatabaseMetaData dbmd = this.conn.getMetaData();
			this.rs = dbmd.getColumns(this.conn.getCatalog(), null,
					"testBug4138", "field%");

			assertTrue(this.rs.next());

			for (int i = 0; i < typesToTest.length; i++) {
				assertTrue(
						"JDBC Data Type of "
								+ this.rs.getShort("DATA_TYPE")
								+ " for MySQL type '"
								+ this.rs.getString("TYPE_NAME")
								+ "' from 'DATA_TYPE' column does not match expected value of "
								+ jdbcMapping[i] + ".",
						jdbcMapping[i] == this.rs.getShort("DATA_TYPE"));
				this.rs.next();
			}

			this.rs.close();

			StringBuffer queryBuf = new StringBuffer("SELECT ");
			firstColumn = true;

			for (int i = 0; i < typesToTest.length; i++) {
				if (!firstColumn) {
					queryBuf.append(", ");
				} else {
					firstColumn = false;
				}

				queryBuf.append("field");
				queryBuf.append((i + 1));
			}

			queryBuf.append(" FROM testBug4138");

			this.rs = this.stmt.executeQuery(queryBuf.toString());

			ResultSetMetaData rsmd = this.rs.getMetaData();

			for (int i = 0; i < typesToTest.length; i++) {

				assertTrue(jdbcMapping[i] == rsmd.getColumnType(i + 1));
				String desiredTypeName = typesToTest[i] + " unsigned";

				assertTrue(rsmd.getColumnTypeName((i + 1)) + " != "
						+ desiredTypeName, desiredTypeName
						.equalsIgnoreCase(rsmd.getColumnTypeName(i + 1)));
			}
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4138");
		}
	}

	/**
	 * Here for housekeeping only, the test is actually in testBug4138().
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug4860() throws Exception {
		testBug4138();
	}

	/**
	 * Tests fix for BUG#4880 - RSMD.getPrecision() returns '0' for non-numeric
	 * types.
	 * 
	 * Why-oh-why is this not in the spec, nor the api-docs, but in some
	 * 'optional' book, _and_ it is a variance from both ODBC and the ANSI SQL
	 * standard :p
	 * 
	 * (from the CTS testsuite)....
	 * 
	 * The getPrecision(int colindex) method returns an integer value
	 * representing the number of decimal digits for number types,maximum length
	 * in characters for character types,maximum length in bytes for JDBC binary
	 * datatypes.
	 * 
	 * (See Section 27.3 of JDBC 2.0 API Reference & Tutorial 2nd edition)
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */

	public void testBug4880() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4880");
			this.stmt
					.executeUpdate("CREATE TABLE testBug4880 (field1 VARCHAR(80), field2 TINYBLOB, field3 BLOB, field4 MEDIUMBLOB, field5 LONGBLOB)");
			this.rs = this.stmt
					.executeQuery("SELECT field1, field2, field3, field4, field5 FROM testBug4880");
			ResultSetMetaData rsmd = this.rs.getMetaData();

			assertEquals(80, rsmd.getPrecision(1));
			assertEquals(Types.VARCHAR, rsmd.getColumnType(1));
			assertEquals(80, rsmd.getColumnDisplaySize(1));

			assertEquals(255, rsmd.getPrecision(2));
			assertEquals(Types.VARBINARY, rsmd.getColumnType(2));
			assertTrue("TINYBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(2)));
			assertEquals(255, rsmd.getColumnDisplaySize(2));

			assertEquals(65535, rsmd.getPrecision(3));
			assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(3));
			assertTrue("BLOB".equalsIgnoreCase(rsmd.getColumnTypeName(3)));
			assertEquals(65535, rsmd.getColumnDisplaySize(3));

			assertEquals(16777215, rsmd.getPrecision(4));
			assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(4));
			assertTrue("MEDIUMBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(4)));
			assertEquals(16777215, rsmd.getColumnDisplaySize(4));

			if (versionMeetsMinimum(4, 1)) {
				// Server doesn't send us enough information to detect LONGBLOB
				// type
				assertEquals(Integer.MAX_VALUE, rsmd.getPrecision(5));
				assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(5));
				assertTrue("LONGBLOB".equalsIgnoreCase(rsmd
						.getColumnTypeName(5)));
				assertEquals(Integer.MAX_VALUE, rsmd.getColumnDisplaySize(5));
			}
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4880");
		}
	}

	/**
	 * Tests fix for BUG#6399, ResultSetMetaData.getDisplaySize() is wrong for
	 * multi-byte charsets.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug6399() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6399");
				this.stmt
						.executeUpdate("CREATE TABLE testBug6399 (field1 CHAR(3) CHARACTER SET UTF8, field2 CHAR(3) CHARACTER SET LATIN1, field3 CHAR(3) CHARACTER SET SJIS)");
				this.stmt
						.executeUpdate("INSERT INTO testBug6399 VALUES ('a', 'a', 'a')");

				this.rs = this.stmt
						.executeQuery("SELECT field1, field2, field3 FROM testBug6399");
				ResultSetMetaData rsmd = this.rs.getMetaData();

				assertTrue(3 == rsmd.getColumnDisplaySize(1));
				assertTrue(3 == rsmd.getColumnDisplaySize(2));
				assertTrue(3 == rsmd.getColumnDisplaySize(3));
			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6399");
			}
		}
	}

	/**
	 * Tests fix for BUG#7081, DatabaseMetaData.getIndexInfo() ignoring 'unique'
	 * parameters.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug7081() throws Exception {
		String tableName = "testBug7081";

		try {
			createTable(tableName, "(field1 INT, INDEX(field1))");

			DatabaseMetaData dbmd = this.conn.getMetaData();
			this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null,
					tableName, true, false);
			assertTrue(!this.rs.next()); // there should be no rows that meet
			// this requirement

			this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null,
					tableName, false, false);
			assertTrue(this.rs.next()); // there should be one row that meets
			// this requirement
			assertTrue(!this.rs.next());

		} finally {
			dropTable(tableName);
		}
	}

	/**
	 * Tests fix for BUG#7033 - PreparedStatements don't encode Big5 (and other
	 * multibyte) character sets correctly in static SQL strings.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug7033() throws Exception {
		if (false) { // disabled for now
			Connection big5Conn = null;
			Statement big5Stmt = null;
			PreparedStatement big5PrepStmt = null;

			String testString = "\u5957 \u9910";

			try {
				Properties props = new Properties();
				props.setProperty("useUnicode", "true");
				props.setProperty("characterEncoding", "Big5");

				big5Conn = getConnectionWithProps(props);