statementstest.java

mysql的jdbc驱动

			inserter.setString(8, iMinimum);
			inserter.setString(9, unsignedMinimum);
			inserter.setString(10, biMinimum);
			inserter.setString(11, unsignedMinimum);
			inserter.executeUpdate();

			inserter.setInt(1, 1);
			inserter.setString(2, tiMaximum);
			inserter.setString(3, utiMaximum);
			inserter.setString(4, siMaximum);
			inserter.setString(5, usiMaximum);
			inserter.setString(6, miMaximum);
			inserter.setString(7, umiMaximum);
			inserter.setString(8, iMaximum);
			inserter.setString(9, uiMaximum);
			inserter.setString(10, biMaximum);
			inserter.setString(11, ubiMaximum);
			inserter.executeUpdate();

			PreparedStatement selector = this.conn
					.prepareStatement("SELECT * FROM testBinaryResultSetNumericTypes ORDER by rowOrder ASC");
			this.rs = selector.executeQuery();

			assertTrue(this.rs.next());

			assertTrue(this.rs.getString(2).equals(tiMinimum));
			assertTrue(this.rs.getString(3).equals(unsignedMinimum));
			assertTrue(this.rs.getString(4).equals(siMinimum));
			assertTrue(this.rs.getString(5).equals(unsignedMinimum));
			assertTrue(this.rs.getString(6).equals(miMinimum));
			assertTrue(this.rs.getString(7).equals(unsignedMinimum));
			assertTrue(this.rs.getString(8).equals(iMinimum));
			assertTrue(this.rs.getString(9).equals(unsignedMinimum));
			assertTrue(this.rs.getString(10).equals(biMinimum));
			assertTrue(this.rs.getString(11).equals(unsignedMinimum));

			assertTrue(this.rs.next());

			assertTrue(this.rs.getString(2) + " != " + tiMaximum, this.rs
					.getString(2).equals(tiMaximum));
			assertTrue(this.rs.getString(3) + " != " + utiMaximum, this.rs
					.getString(3).equals(utiMaximum));
			assertTrue(this.rs.getString(4) + " != " + siMaximum, this.rs
					.getString(4).equals(siMaximum));
			assertTrue(this.rs.getString(5) + " != " + usiMaximum, this.rs
					.getString(5).equals(usiMaximum));
			assertTrue(this.rs.getString(6) + " != " + miMaximum, this.rs
					.getString(6).equals(miMaximum));
			assertTrue(this.rs.getString(7) + " != " + umiMaximum, this.rs
					.getString(7).equals(umiMaximum));
			assertTrue(this.rs.getString(8) + " != " + iMaximum, this.rs
					.getString(8).equals(iMaximum));
			assertTrue(this.rs.getString(9) + " != " + uiMaximum, this.rs
					.getString(9).equals(uiMaximum));
			assertTrue(this.rs.getString(10) + " != " + biMaximum, this.rs
					.getString(10).equals(biMaximum));
			assertTrue(this.rs.getString(11) + " != " + ubiMaximum, this.rs
					.getString(11).equals(ubiMaximum));

			assertTrue(!this.rs.next());
		} finally {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testBinaryResultSetNumericTypes");
		}
	}

	/**
	 * Tests stored procedure functionality
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testCallableStatement() throws Exception {
		if (versionMeetsMinimum(5, 0)) {
			CallableStatement cStmt = null;
			String stringVal = "abcdefg";
			int intVal = 42;

			try {
				try {
					this.stmt.executeUpdate("DROP PROCEDURE testCallStmt");
				} catch (SQLException sqlEx) {
					if (sqlEx.getMessage().indexOf("does not exist") == -1) {
						throw sqlEx;
					}
				}

				this.stmt.executeUpdate("DROP TABLE IF EXISTS callStmtTbl");
				this.stmt
						.executeUpdate("CREATE TABLE callStmtTbl (x CHAR(16), y INT)");

				this.stmt
						.executeUpdate("CREATE PROCEDURE testCallStmt(n INT, x CHAR(16), y INT)"
								+ " WHILE n DO"
								+ "    SET n = n - 1;"
								+ "    INSERT INTO callStmtTbl VALUES (x, y);"
								+ " END WHILE;");

				int rowsToCheck = 15;

				cStmt = this.conn.prepareCall("{call testCallStmt(?,?,?)}");
				cStmt.setInt(1, rowsToCheck);
				cStmt.setString(2, stringVal);
				cStmt.setInt(3, intVal);
				cStmt.execute();

				this.rs = this.stmt.executeQuery("SELECT x,y FROM callStmtTbl");

				int numRows = 0;

				while (this.rs.next()) {
					assertTrue(this.rs.getString(1).equals(stringVal)
							&& (this.rs.getInt(2) == intVal));

					numRows++;
				}

				this.rs.close();
				this.rs = null;

				cStmt.close();
				cStmt = null;

				System.out.println(rowsToCheck + " rows returned");

				assertTrue(numRows == rowsToCheck);
			} finally {
				try {
					this.stmt.executeUpdate("DROP PROCEDURE testCallStmt");
				} catch (SQLException sqlEx) {
					if (sqlEx.getMessage().indexOf("does not exist") == -1) {
						throw sqlEx;
					}
				}

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

				if (cStmt != null) {
					cStmt.close();
				}
			}
		}
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testClose() throws SQLException {
		Statement closeStmt = null;
		boolean exceptionAfterClosed = false;

		try {
			closeStmt = this.conn.createStatement();
			closeStmt.close();

			try {
				closeStmt.executeQuery("SELECT 1");
			} catch (SQLException sqlEx) {
				exceptionAfterClosed = true;
			}
		} finally {
			if (closeStmt != null) {
				try {
					closeStmt.close();
				} catch (SQLException sqlEx) {
					/* ignore */
				}
			}

			closeStmt = null;
		}

		assertTrue(
				"Operations not allowed on Statement after .close() is called!",
				exceptionAfterClosed);
	}

	public void testEnableStreamingResults() throws Exception {
		Statement streamStmt = this.conn.createStatement();
		((com.mysql.jdbc.Statement) streamStmt).enableStreamingResults();
		assertEquals(streamStmt.getFetchSize(), Integer.MIN_VALUE);
		assertEquals(streamStmt.getResultSetType(), ResultSet.TYPE_FORWARD_ONLY);
	}

	public void testHoldingResultSetsOverClose() throws Exception {
		Properties props = new Properties();
		props.setProperty("holdResultsOpenOverStatementClose", "true");

		Connection conn2 = getConnectionWithProps(props);

		Statement stmt2 = null;
		PreparedStatement pstmt2 = null;

		try {
			stmt2 = conn2.createStatement();

			this.rs = stmt2.executeQuery("SELECT 1");
			this.rs.next();
			this.rs.getInt(1);
			stmt2.close();
			this.rs.getInt(1);

			stmt2 = conn2.createStatement();
			stmt2.execute("SELECT 1");
			this.rs = stmt2.getResultSet();
			this.rs.next();
			this.rs.getInt(1);
			stmt2.execute("SELECT 2");
			this.rs.getInt(1);

			pstmt2 = conn2.prepareStatement("SELECT 1");
			this.rs = pstmt2.executeQuery();
			this.rs.next();
			this.rs.getInt(1);
			pstmt2.close();
			this.rs.getInt(1);

			pstmt2 = conn2.prepareStatement("SELECT 1");
			this.rs = pstmt2.executeQuery();
			this.rs.next();
			this.rs.getInt(1);
			pstmt2.executeQuery();
			this.rs.getInt(1);
			pstmt2.execute();
			this.rs.getInt(1);

			pstmt2 = ((com.mysql.jdbc.Connection) conn2)
					.clientPrepareStatement("SELECT 1");
			this.rs = pstmt2.executeQuery();
			this.rs.next();
			this.rs.getInt(1);
			pstmt2.close();
			this.rs.getInt(1);

			pstmt2 = ((com.mysql.jdbc.Connection) conn2)
					.clientPrepareStatement("SELECT 1");
			this.rs = pstmt2.executeQuery();
			this.rs.next();
			this.rs.getInt(1);
			pstmt2.executeQuery();
			this.rs.getInt(1);
			pstmt2.execute();
			this.rs.getInt(1);

			stmt2 = conn2.createStatement();
			this.rs = stmt2.executeQuery("SELECT 1");
			this.rs.next();
			this.rs.getInt(1);
			stmt2.executeQuery("SELECT 2");
			this.rs.getInt(1);
			this.rs = stmt2.executeQuery("SELECT 1");
			this.rs.next();
			this.rs.getInt(1);
			stmt2.executeUpdate("SET @var=1");
			this.rs.getInt(1);
			stmt2.execute("SET @var=2");
			this.rs.getInt(1);
		} finally {
			if (stmt2 != null) {
				stmt2.close();
			}
		}
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testInsert() throws SQLException {
		try {
			boolean autoCommit = this.conn.getAutoCommit();

			// Test running a query for an update. It should fail.
			try {
				this.conn.setAutoCommit(false);
				this.stmt.executeUpdate("SELECT * FROM statement_test");
			} catch (SQLException sqlEx) {
				assertTrue("Exception thrown for unknown reason", sqlEx
						.getSQLState().equalsIgnoreCase("01S03"));
			} finally {
				this.conn.setAutoCommit(autoCommit);
			}

			// Test running a update for an query. It should fail.
			try {
				this.conn.setAutoCommit(false);
				this.stmt
						.executeQuery("UPDATE statement_test SET strdata1='blah' WHERE 1=0");
			} catch (SQLException sqlEx) {
				assertTrue("Exception thrown for unknown reason", sqlEx
						.getSQLState().equalsIgnoreCase(
								SQLError.SQL_STATE_ILLEGAL_ARGUMENT));
			} finally {
				this.conn.setAutoCommit(autoCommit);
			}

			for (int i = 0; i < 10; i++) {
				int updateCount = this.stmt
						.executeUpdate("INSERT INTO statement_test (strdata1,strdata2) values ('abcdefg', 'poi')");
				assertTrue("Update count must be '1', was '" + updateCount
						+ "'", (updateCount == 1));
			}

			this.stmt
					.executeUpdate("INSERT INTO statement_test (strdata1, strdata2) values ('a', 'a'), ('b', 'b'), ('c', 'c')");
			this.rs = this.stmt.getGeneratedKeys();

			if (this.rs.next()) {
				this.rs.getInt(1);
			}

			this.rs.close();
			this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID()");

			int updateCountFromServer = 0;

			if (this.rs.next()) {
				updateCountFromServer = this.rs.getInt(1);
			}

			System.out.println("Update count from server: "
					+ updateCountFromServer);
		} finally {
			if (this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception ex) { /* ignore */
					;
				}
			}

			this.rs = null;
		}
	}

	/**
	 * Tests multiple statement support
	 * 
	 * @throws Exception
	 *             DOCUMENT ME!
	 */
	public void testMultiStatements() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			Connection multiStmtConn = null;
			Statement multiStmt = null;

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

				multiStmtConn = getConnectionWithProps(props);

				multiStmt = multiStmtConn.createStatement();

				multiStmt
						.executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
				multiStmt
						.executeUpdate("CREATE TABLE testMultiStatements (field1 VARCHAR(255), field2 INT, field3 DOUBLE)");
				multiStmt
						.executeUpdate("INSERT INTO testMultiStatements VALUES ('abcd', 1, 2)");

				multiStmt
						.execute("SELECT field1 FROM testMultiStatements WHERE field1='abcd';"
								+ "UPDATE testMultiStatements SET field3=3;"
								+ "SELECT field3 FROM testMultiStatements WHERE field3=3");

				this.rs = multiStmt.getResultSet();

				assertTrue(this.rs.next());

				assertTrue("abcd".equals(this.rs.getString(1)));
				this.rs.close();