statementstest.java

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

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

			if (!isRunningOnJdk131()) {
				int insertIdFromGeneratedKeys = Integer.MIN_VALUE;

				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()) {
					insertIdFromGeneratedKeys = this.rs.getInt(1);
				}

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

				int insertIdFromServer = Integer.MIN_VALUE;

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

				assertEquals(insertIdFromGeneratedKeys, insertIdFromServer);
			}
		} finally {
			if (this.rs != null) {