callablestatementtest.java

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

								+ "SELECT field2 FROM testSpResultTbl2 WHERE field2='def';\n"
								+ "end\n");

				storedProc = this.conn.prepareCall("{call testSpResult()}");

				storedProc.execute();

				this.rs = storedProc.getResultSet();

				ResultSetMetaData rsmd = this.rs.getMetaData();

				assertTrue(rsmd.getColumnCount() == 1);
				assertTrue("field2".equals(rsmd.getColumnName(1)));
				assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

				assertTrue(this.rs.next());

				assertTrue("abc".equals(this.rs.getString(1)));

				// TODO: This does not yet work in MySQL 5.0
				// assertTrue(!storedProc.getMoreResults());
				// assertTrue(storedProc.getUpdateCount() == 2);
				assertTrue(storedProc.getMoreResults());

				ResultSet nextResultSet = storedProc.getResultSet();

				rsmd = nextResultSet.getMetaData();

				assertTrue(rsmd.getColumnCount() == 1);
				assertTrue("field2".equals(rsmd.getColumnName(1)));
				assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

				assertTrue(nextResultSet.next());

				assertTrue("def".equals(nextResultSet.getString(1)));

				nextResultSet.close();

				this.rs.close();

				storedProc.execute();

			} finally {
				this.stmt
						.executeUpdate("DROP PROCEDURE IF EXISTS testSpResult");
				this.stmt
						.executeUpdate("DROP TABLE IF EXISTS testSpResultTbl1");
				this.stmt
						.executeUpdate("DROP TABLE IF EXISTS testSpResultTbl2");
			}
		}
	}

	/**
	 * Tests parsing of stored procedures
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testSPParse() throws Exception {

		if (versionMeetsMinimum(5, 0)) {

			CallableStatement storedProc = null;

			try {

				this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
				this.stmt
						.executeUpdate("CREATE PROCEDURE testSpParse(IN FOO VARCHAR(15))\n"
								+ "BEGIN\n" + "SELECT 1;\n" + "end\n");

				storedProc = this.conn.prepareCall("{call testSpParse()}");

			} finally {
				this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
			}
		}
	}

	/**
	 * Tests parsing/execution of stored procedures with no parameters...
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testSPNoParams() throws Exception {

		if (versionMeetsMinimum(5, 0)) {

			CallableStatement storedProc = null;

			try {

				this.stmt
						.executeUpdate("DROP PROCEDURE IF EXISTS testSPNoParams");
				this.stmt.executeUpdate("CREATE PROCEDURE testSPNoParams()\n"
						+ "BEGIN\n" + "SELECT 1;\n" + "end\n");

				storedProc = this.conn.prepareCall("{call testSPNoParams()}");
				storedProc.execute();

			} finally {
				this.stmt
						.executeUpdate("DROP PROCEDURE IF EXISTS testSPNoParams");
			}
		}
	}

	/**
	 * Tests parsing of stored procedures
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testSPCache() throws Exception {
		if (isRunningOnJdk131()) {
			return; // no support for LRUCache
		}

		if (versionMeetsMinimum(5, 0)) {

			CallableStatement storedProc = null;

			try {

				this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
				this.stmt
						.executeUpdate("CREATE PROCEDURE testSpParse(IN FOO VARCHAR(15))\n"
								+ "BEGIN\n" + "SELECT 1;\n" + "end\n");

				int numIterations = 10;

				long startTime = System.currentTimeMillis();

				for (int i = 0; i < numIterations; i++) {
					storedProc = this.conn.prepareCall("{call testSpParse(?)}");
					storedProc.close();
				}

				long elapsedTime = System.currentTimeMillis() - startTime;

				System.out.println("Standard parsing/execution: " + elapsedTime
						+ " ms");

				storedProc = this.conn.prepareCall("{call testSpParse(?)}");
				storedProc.setString(1, "abc");
				this.rs = storedProc.executeQuery();

				assertTrue(this.rs.next());
				assertTrue(this.rs.getInt(1) == 1);

				Properties props = new Properties();
				props.setProperty("cacheCallableStmts", "true");

				Connection cachedSpConn = getConnectionWithProps(props);

				startTime = System.currentTimeMillis();

				for (int i = 0; i < numIterations; i++) {
					storedProc = cachedSpConn
							.prepareCall("{call testSpParse(?)}");
					storedProc.close();
				}

				elapsedTime = System.currentTimeMillis() - startTime;

				System.out
						.println("Cached parse stage: " + elapsedTime + " ms");

				storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
				storedProc.setString(1, "abc");
				this.rs = storedProc.executeQuery();

				assertTrue(this.rs.next());
				assertTrue(this.rs.getInt(1) == 1);

			} finally {
				this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
			}
		}
	}
	
	public void testOutParamsNoBodies() throws Exception {
		if (versionMeetsMinimum(5, 0)) {
			CallableStatement storedProc = null;

			Properties props = new Properties();
			props.setProperty("noAccessToProcedureBodies", "true");
			
			Connection spConn = getConnectionWithProps(props);
			
			try {
				this.stmt
						.executeUpdate("DROP PROCEDURE IF EXISTS testOutParam");
				this.stmt
						.executeUpdate("CREATE PROCEDURE testOutParam(x int, out y int)\n"
								+ "begin\n"
								+ "declare z int;\n"
								+ "set z = x+1, y = z;\n" + "end\n");

				storedProc = spConn.prepareCall("{call testOutParam(?, ?)}");

				storedProc.setInt(1, 5);
				storedProc.registerOutParameter(2, Types.INTEGER);

				storedProc.execute();

				int indexedOutParamToTest = storedProc.getInt(2);
			
				assertTrue("Output value not returned correctly",
						indexedOutParamToTest == 6);

				
				storedProc.clearParameters();
				storedProc.setInt(1, 32);
				storedProc.registerOutParameter(2, Types.INTEGER);

				storedProc.execute();

				indexedOutParamToTest = storedProc.getInt(2);
				
				assertTrue("Output value not returned correctly",
						indexedOutParamToTest == 33);
			} finally {
				this.stmt.executeUpdate("DROP PROCEDURE testOutParam");
			}
		}
	}

	/**
	 * Runs all test cases in this test suite
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		junit.textui.TestRunner.run(CallableStatementTest.class);
	}
	
	/** Tests the new parameter parser that doesn't require "BEGIN" or "\n" at end
	 * of parameter declaration
	 * @throws Exception
	 */
	public void testParameterParser() throws Exception {

		if (!versionMeetsMinimum(5, 0)) {
			return;
		}

		CallableStatement cstmt = null;

		try {

			createTable("t1",
					"(id   char(16) not null default '', data int not null)");
			createTable("t2", "(s   char(16),  i   int,  d   double)");

			createProcedure("foo42",
					"() insert into test.t1 values ('foo', 42);");
			this.conn.prepareCall("{CALL foo42()}");
			this.conn.prepareCall("{CALL foo42}");

			createProcedure("bar",
					"(x char(16), y int, z DECIMAL(10)) insert into test.t1 values (x, y);");
			cstmt = this.conn.prepareCall("{CALL bar(?, ?, ?)}");

			if (!isRunningOnJdk131()) {
				ParameterMetaData md = cstmt.getParameterMetaData();
				assertEquals(3, md.getParameterCount());
				assertEquals(Types.CHAR, md.getParameterType(1));
				assertEquals(Types.INTEGER, md.getParameterType(2));
				assertEquals(Types.DECIMAL, md.getParameterType(3));
			}

			createProcedure("p", "() label1: WHILE @a=0 DO SET @a=1; END WHILE");
			this.conn.prepareCall("{CALL p()}");

			createFunction("f", "() RETURNS INT return 1; ");
			cstmt = this.conn.prepareCall("{? = CALL f()}");

			if (!isRunningOnJdk131()) {
				ParameterMetaData md = cstmt.getParameterMetaData();
				assertEquals(Types.INTEGER, md.getParameterType(1));
			}
		} finally {
			if (cstmt != null) {
				cstmt.close();
			}
		}
	}
}