resultsetregressiontest.java

mysql的jdbc驱动

	 * depending on platform. Fixed earlier, but in here to catch if it ever
	 * regresses.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug9437() throws Exception {
		String tableName = "testBug9437";

		if (versionMeetsMinimum(4, 1, 0)) {
			try {
				createTable(
						tableName,
						"("
								+ "languageCode char(2) NOT NULL default '',"
								+ "countryCode char(2) NOT NULL default '',"
								+ "supported enum('no','yes') NOT NULL default 'no',"
								+ "ordering int(11) default NULL,"
								+ "createDate datetime NOT NULL default '1000-01-01 00:00:03',"
								+ "modifyDate timestamp NOT NULL default CURRENT_TIMESTAMP on update"
								+ " CURRENT_TIMESTAMP,"
								+ "PRIMARY KEY  (languageCode,countryCode),"
								+ "KEY languageCode (languageCode),"
								+ "KEY countryCode (countryCode),"
								+ "KEY ordering (ordering),"
								+ "KEY modifyDate (modifyDate)"
								+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8");

				this.stmt.executeUpdate("INSERT INTO " + tableName
						+ " (languageCode) VALUES ('en')");

				String alias = "someLocale";
				String sql = "select if ( languageCode = ?, ?, ? ) as " + alias
						+ " from " + tableName;
				this.pstmt = this.conn.prepareStatement(sql);

				int count = 1;
				this.pstmt.setObject(count++, "en");
				this.pstmt.setObject(count++, "en_US");
				this.pstmt.setObject(count++, "en_GB");

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

				assertTrue(this.rs.next());

				Object object = this.rs.getObject(alias);

				if (object != null) {
					assertEquals("java.lang.String", object.getClass()
							.getName());
					assertEquals("en_US", object.toString());
				}

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

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

	public void testBug9684() throws Exception {
		if (versionMeetsMinimum(4, 1, 9)) {
			String tableName = "testBug9684";

			try {
				createTable(tableName,
						"(sourceText text character set utf8 collate utf8_bin)");
				this.stmt.executeUpdate("INSERT INTO " + tableName
						+ " VALUES ('abc')");
				this.rs = this.stmt.executeQuery("SELECT sourceText FROM "
						+ tableName);
				assertTrue(this.rs.next());
				assertEquals("java.lang.String", this.rs.getString(1)
						.getClass().getName());
				assertEquals("abc", this.rs.getString(1));
			} finally {
				if (this.rs != null) {
					this.rs.close();
					this.rs = null;
				}
			}
		}
	}

	/**
	 * Tests fix for BUG#10156 - Unsigned SMALLINT treated as signed
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug10156() throws Exception {
		String tableName = "testBug10156";
		try {
			createTable(tableName, "(field1 smallint(5) unsigned, "
					+ "field2 tinyint unsigned," + "field3 int unsigned)");
			this.stmt.executeUpdate("INSERT INTO " + tableName
					+ " VALUES (32768, 255, 4294967295)");
			this.rs = this.conn.prepareStatement(
					"SELECT field1, field2, field3 FROM " + tableName)
					.executeQuery();
			assertTrue(this.rs.next());
			assertEquals(32768, this.rs.getInt(1));
			assertEquals(255, this.rs.getInt(2));
			assertEquals(4294967295L, this.rs.getLong(3));

			assertEquals(String.valueOf(this.rs.getObject(1)), String
					.valueOf(this.rs.getInt(1)));
			assertEquals(String.valueOf(this.rs.getObject(2)), String
					.valueOf(this.rs.getInt(2)));
			assertEquals(String.valueOf(this.rs.getObject(3)), String
					.valueOf(this.rs.getLong(3)));

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

	public void testBug10212() throws Exception {
		String tableName = "testBug10212";

		try {
			createTable(tableName, "(field1 YEAR(4))");
			this.stmt.executeUpdate("INSERT INTO " + tableName
					+ " VALUES (1974)");
			this.rs = this.conn.prepareStatement(
					"SELECT field1 FROM " + tableName).executeQuery();

			ResultSetMetaData rsmd = this.rs.getMetaData();
			assertTrue(this.rs.next());
			assertEquals("java.sql.Date", rsmd.getColumnClassName(1));
			assertEquals("java.sql.Date", this.rs.getObject(1).getClass()
					.getName());

			this.rs = this.stmt.executeQuery("SELECT field1 FROM " + tableName);

			rsmd = this.rs.getMetaData();
			assertTrue(this.rs.next());
			assertEquals("java.sql.Date", rsmd.getColumnClassName(1));
			assertEquals("java.sql.Date", this.rs.getObject(1).getClass()
					.getName());
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
		}
	}

	/**
	 * Tests fix for BUG#11190 - ResultSet.moveToCurrentRow() fails to
	 * work when preceeded with .moveToInsertRow().
	 * 
	 * @throws Exception if the test fails.
	 */
	public void testBug11190() throws Exception {
		
		createTable("testBug11190","(a CHAR(4) PRIMARY KEY, b VARCHAR(20))");
		this.stmt.executeUpdate("INSERT INTO testBug11190 VALUES('3000','L'),('3001','H'),('1050','B')");
		
		Statement updStmt = null;
		
		try {
			updStmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			
			this.rs = updStmt.executeQuery("select * from testBug11190");     
			assertTrue("must return a row", this.rs.next());
			String savedValue = this.rs.getString(1);
			this.rs.moveToInsertRow();
			this.rs.updateString(1,"4000");
			this.rs.updateString(2,"C");
			this.rs.insertRow();
			
			this.rs.moveToCurrentRow();
			assertEquals(savedValue, this.rs.getString(1));
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
			
			if (updStmt != null) {
				updStmt.close();
			}
		}
	}
	
	/**
	 * Tests fix for BUG#12104 - Geometry types not handled with
	 * server-side prepared statements.
	 * 
	 * @throws Exception if the test fails
	 */
	public void testBug12104() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			createTable("testBug12104", "(field1 GEOMETRY)");
			
			try {
				this.stmt.executeUpdate("INSERT INTO testBug12104 VALUES (GeomFromText('POINT(1 1)'))");
				this.pstmt = this.conn.prepareStatement("SELECT field1 FROM testBug12104");
				this.rs = this.pstmt.executeQuery();
				assertTrue(this.rs.next());
				System.out.println(this.rs.getObject(1));
			} finally {
				
			}
		}
	}

	/**
	 * Tests fix for BUG#13043 - when 'gatherPerfMetrics'
	 * is enabled for servers < 4.1.0, a NPE is thrown
	 * from the constructor of ResultSet if the query
	 * doesn't use any tables.
	 * 
	 * @throws Exception if the test fails
	 */
	public void testBug13043() throws Exception {
        if(!versionMeetsMinimum(4, 1)) {
        	Connection perfConn = null;
    
            try {
                Properties props = new Properties();
                props.put("gatherPerfMetrics", "true"); // this property is reported as the cause of NullPointerException
                props.put("reportMetricsIntervalMillis", "30000"); // this property is reported as the cause of NullPointerException
                perfConn = getConnectionWithProps(props);
                perfConn.createStatement().executeQuery("SELECT 1");
            } finally {
            	if (perfConn != null) {
            		perfConn.close();
            	}
            }
        }
	}
	
	/**
	 * Tests fix for BUG#13374 - ResultSet.getStatement() 
	 * on closed result set returns NULL (as per JDBC 4.0 spec,
	 * but not backwards-compatible).
	 * 
	 * @throws Exception if the test fails
	 */
	
	public void testBug13374() throws Exception {
		Statement retainStmt = null;
		Connection retainConn = null;
		
		try {
			Properties props = new Properties();
			
			props.setProperty("retainStatementAfterResultSetClose",
					"true");
			
			retainConn = getConnectionWithProps(props);
			
			retainStmt = retainConn.createStatement();
			
			this.rs = retainStmt.executeQuery("SELECT 1");
			this.rs.close();
			assertNotNull(this.rs.getStatement());
			
			this.rs = this.stmt.executeQuery("SELECT 1");
			this.rs.close();
			
			try {
				this.rs.getStatement();
			} catch (SQLException sqlEx) {
				assertEquals(sqlEx.getSQLState(), 
						SQLError.SQL_STATE_GENERAL_ERROR);
			}
			
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
			
			if (retainStmt != null) {
				retainStmt.close();
			}
			
			if (retainConn != null) {
				retainConn.close();
			}
		}
	}
	
	public void testNPEWithUsageAdvisor() throws Exception {
		Connection advisorConn = null;

		try {
			Properties props = new Properties();
			props.setProperty("useUsageAdvisor", "true");

			advisorConn = getConnectionWithProps(props);
			this.pstmt = advisorConn.prepareStatement("SELECT 1");
			this.rs = this.pstmt.executeQuery();
			this.rs.close();
			this.rs = this.pstmt.executeQuery();

		} finally {
		}
	}

	public void testAllTypesForNull() throws Exception {
		Properties props = new Properties();
		props.setProperty("jdbcCompliantTruncation", "false");
		props.setProperty("zeroDateTimeBehavior", "round");
		Connection conn2 = getConnectionWithProps(props);
		Statement stmt2 = conn2.createStatement();

		DatabaseMetaData dbmd = conn.getMetaData();

		this.rs = dbmd.getTypeInfo();

		boolean firstColumn = true;
		int numCols = 1;
		StringBuffer createStatement = new StringBuffer(
				"CREATE TABLE testAllTypes (");
		List wasDatetimeTypeList = new ArrayList();

		while (this.rs.next()) {
			String dataType = this.rs.getString("TYPE_NAME").toUpperCase();

			boolean wasDateTime = false;

			if (dataType.indexOf("DATE") != -1
					|| dataType.indexOf("TIME") != -1) {
				wasDateTime = true;
			}

			if (!"BOOL".equalsIgnoreCase(dataType)
					&& !"LONG VARCHAR".equalsIgnoreCase(dataType)
					&& !"LONG VARBINARY".equalsIgnoreCase(dataType)
					&& !"ENUM".equalsIgnoreCase(dataType)
					&& !"SET".equalsIgnoreCase(dataType)) {
				wasDatetimeTypeList.add(new Boolean(wasDateTime));
				createStatement.append("\n\t");
				if (!firstColumn) {
					createStatement.append(",");
				} else {
					firstColumn = false;
				}

				createStatement.append("field_");
				createStatement.append(numCols++);
				createStatement.append(" ");

				createStatement.append(dataType);

				if (dataType.indexOf("CHAR") != -1
						|| dataType.indexOf("BINARY") != -1
						&& dataType.indexOf("BLOB") == -1
						&& dataType.indexOf("TEXT") == -1) {
					createStatement.append("(");
					createStatement.append(this.rs.getString("PRECISION"));
					createStatement.append(")");
				}

				createStatement.append(" NULL DEFAULT NULL");
			}
		}

		createStatement.append("\n)");

		stmt2.executeUpdate("DROP TABLE IF EXISTS testAllTypes");

		stmt2.executeUpdate(createStatement.toString());
		StringBuffer insertStatement = new StringBuffer(
				"INSERT INTO testAllTypes VALUES (NULL");
		for (int i = 1; i < numCols - 1; i++) {
			insertStatement.append(", NULL");
		}
		insertStatement.append(")");
		stmt2.executeUpdate(insertStatement.toString());

		this.rs = stmt2.executeQuery("SELECT * FROM testAllTypes");

		testAllFieldsForNull(this.rs);
		this.rs.close();

		this.rs = this.conn.prepareStatement("SELECT * FROM testAllTypes")
				.executeQuery();
		testAllFieldsForNull(this.rs);

		stmt2.executeUpdate("DELETE FROM testAllTypes");

		insertStatement = new StringBuffer("INSERT INTO testAllTypes VALUES (");

		boolean needsNow = ((Boolean) wasDatetimeTypeList.get(0))
				.booleanValue();

		if (needsNow) {
			insertStatement.append("NOW()");
		} else {
			insertStatement.append("'0'");
		}

		for (int i = 1; i < numCols - 1; i++) {
			needsNow = ((Boolean) wasDatetimeTypeList.get(i)).booleanValue();
			insertStatement.append(",");
			if (needsNow) {
				insertStatement.append("NOW()");
			} else {
				insertStatement.append("'0'");
			}
		}

		insertStatement.append(")");

		stmt2.executeUpdate(insertStatement.toString());

		this.rs = stmt2.executeQuery("SELECT * FROM testAllTypes");

		testAllFieldsForNotNull(this.rs, wasDatetimeTypeList);
		this.rs.close();

		this.rs = conn2.prepareStatement("SELECT * FROM testAllTypes")
				.executeQuery();
		testAllFieldsForNotNull(this.rs, wasDatetimeTypeList);
	}

	private void testAllFieldsForNull(ResultSet rsToTest) throws Exception {
		ResultSetMetaData rsmd = rs.getMetaData();
		int numCols = rsmd.getColumnCount();

		while (rsToTest.next()) {
			for (int i = 0; i < numCols - 1; i++) {