statementstest.java

开发MySql数据库的最新JDBC驱动。

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

	public void testCancelStatement() throws Exception {

		if (versionMeetsMinimum(5, 0)) {
			Connection cancelConn = null;
	
			try {
				cancelConn = getConnectionWithProps(null);
				final Statement cancelStmt = cancelConn.createStatement();
			
				cancelStmt.setQueryTimeout(1);
	
				long begin = System.currentTimeMillis();
	
				try {
					cancelStmt.execute("SELECT SLEEP(30)");
				} catch (SQLException sqlEx) {
					assertTrue("Probably wasn't actually cancelled", System
							.currentTimeMillis()
							- begin < 30000);
				}
	
				for (int i = 0; i < 1000; i++) {
					try {
						cancelStmt.executeQuery("SELECT 1");
					} catch (SQLException timedOutEx) {
						break;
					}
				}
				
				// Make sure we can still use the connection...
	
				cancelStmt.setQueryTimeout(0);
				this.rs = cancelStmt.executeQuery("SELECT 1");
	
				assertTrue(this.rs.next());
				assertEquals(1, this.rs.getInt(1));
	
				cancelStmt.setQueryTimeout(0);
	
				new Thread() {
	
					public void run() {
						try {
							try {
								sleep(5000);
							} catch (InterruptedException iEx) {
								// ignore
							}
	
							cancelStmt.cancel();
						} catch (SQLException sqlEx) {
							throw new RuntimeException(sqlEx.toString());
						}
					}
	
				}.start();
	
				begin = System.currentTimeMillis();
	
				try {
					cancelStmt.execute("SELECT SLEEP(30)");
				} catch (SQLException sqlEx) {
					assertTrue("Probably wasn't actually cancelled", System
							.currentTimeMillis()
							- begin < 30000);
				}
	
				for (int i = 0; i < 1000; i++) {
					try {
						cancelStmt.executeQuery("SELECT 1");
					} catch (SQLException timedOutEx) {
						break;
					}
				}
				
				// Make sure we can still use the connection...
	
				this.rs = cancelStmt.executeQuery("SELECT 1");
	
				assertTrue(this.rs.next());
				assertEquals(1, this.rs.getInt(1));
				
				final PreparedStatement cancelPstmt = cancelConn.prepareStatement("SELECT SLEEP(30)");
				
				cancelPstmt.setQueryTimeout(1);
	
				begin = System.currentTimeMillis();
	
				try {
					cancelPstmt.execute();
				} catch (SQLException sqlEx) {
					assertTrue("Probably wasn't actually cancelled", System
							.currentTimeMillis()
							- begin < 30000);
				}
	
				for (int i = 0; i < 1000; i++) {
					try {
						cancelPstmt.executeQuery("SELECT 1");
					} catch (SQLException timedOutEx) {
						break;
					}
				}
				
				// Make sure we can still use the connection...
	
				this.rs = cancelStmt.executeQuery("SELECT 1");
	
				assertTrue(this.rs.next());
				assertEquals(1, this.rs.getInt(1));
	
				cancelPstmt.setQueryTimeout(0);
	
				new Thread() {
	
					public void run() {
						try {
							try {
								sleep(5000);
							} catch (InterruptedException iEx) {
								// ignore
							}
	
							
							cancelPstmt.cancel();
						} catch (SQLException sqlEx) {
							throw new RuntimeException(sqlEx.toString());
						}
					}
	
				}.start();
	
				begin = System.currentTimeMillis();
	
				try {
					cancelPstmt.execute();
				} catch (SQLException sqlEx) {
					assertTrue("Probably wasn't actually cancelled", System
							.currentTimeMillis()
							- begin < 30000);
				}
	
				for (int i = 0; i < 1000; i++) {
					try {
						cancelPstmt.executeQuery("SELECT 1");
					} catch (SQLException timedOutEx) {
						break;
					}
				}
				
				// Make sure we can still use the connection...
	
				this.rs = cancelStmt.executeQuery("SELECT 1");
	
				assertTrue(this.rs.next());
				assertEquals(1, this.rs.getInt(1));
				
				final PreparedStatement cancelClientPstmt = ((com.mysql.jdbc.Connection)cancelConn).clientPrepareStatement("SELECT SLEEP(30)");
				
				cancelClientPstmt.setQueryTimeout(1);
	
				begin = System.currentTimeMillis();
	
				try {
					cancelClientPstmt.execute();
				} catch (SQLException sqlEx) {
					assertTrue("Probably wasn't actually cancelled", System
							.currentTimeMillis()
							- begin < 30000);
				}
	
				for (int i = 0; i < 1000; i++) {
					try {
						cancelStmt.executeQuery("SELECT 1");
					} catch (SQLException timedOutEx) {
						break;
					}
				}
				
				// Make sure we can still use the connection...
	
				this.rs = cancelStmt.executeQuery("SELECT 1");
	
				assertTrue(this.rs.next());
				assertEquals(1, this.rs.getInt(1));
	
				cancelClientPstmt.setQueryTimeout(0);
	
				new Thread() {
	
					public void run() {
						try {
							try {
								sleep(5000);
							} catch (InterruptedException iEx) {
								// ignore
							}
	
							
							cancelClientPstmt.cancel();
						} catch (SQLException sqlEx) {
							throw new RuntimeException(sqlEx.toString());
						}
					}
	
				}.start();
	
				begin = System.currentTimeMillis();
	
				try {
					cancelClientPstmt.execute();
				} catch (SQLException sqlEx) {
					assertTrue("Probably wasn't actually cancelled", System
							.currentTimeMillis()
							- begin < 30000);
				}
	
				for (int i = 0; i < 1000; i++) {
					try {
						cancelClientPstmt.executeQuery("SELECT 1");
					} catch (SQLException timedOutEx) {
						break;
					}
				}
				
				// Make sure we can still use the connection...
	
				this.rs = cancelStmt.executeQuery("SELECT 1");
	
				assertTrue(this.rs.next());
				assertEquals(1, this.rs.getInt(1));
			} finally {
				if (this.rs != null) {
					ResultSet toClose = this.rs;
					this.rs = null;
					toClose.close();
				}
	
				if (cancelConn != null) {
					cancelConn.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");