statementstest.java

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

			pStmt = multiConn.prepareStatement("INSERT INTO rewriteBatchTypes(internalOrder,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

			for (int i = 0; i < 1000; i++) {
				pStmt.setInt(1, i);
				for (int k = 0; k < 14; k++) {
					if (k == 8) {
						String asString = (String)differentTypes[i][k];

						if (asString == null) {
							pStmt.setObject(k + 2, null);
						} else {
							pStmt.setCharacterStream(k + 2, new StringReader(asString), asString.length());
						}
					} else if (k == 9) {
						byte[] asBytes = (byte[])differentTypes[i][k];

						if (asBytes == null) {
							pStmt.setObject(k + 2, null);
						} else {
							pStmt.setBinaryStream(k + 2, new ByteArrayInputStream(asBytes), asBytes.length);
						}
					} else {
						pStmt.setObject(k + 2, differentTypes[i][k]);
					}
				}
				pStmt.addBatch();
			}

			pStmt.executeBatch();

			this.rs = this.stmt.executeQuery("SELECT f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14 FROM rewriteBatchTypes ORDER BY internalOrder");

			int idx = 0;

			// We need to format this ourselves, since we have to strip the nanos off of
			// TIMESTAMPs, so .equals() doesn't really work...

			SimpleDateFormat sdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US);

			while (this.rs.next()) {
				for (int k = 0; k < 14; k++) {
					if (differentTypes[idx][k] == null) {
						assertTrue("On row " + idx + " expected NULL, found " + this.rs.getObject(k + 1)
								+ " in column " + (k + 1), this.rs.getObject(k + 1) == null);
					} else {
						String className = differentTypes[idx][k].getClass().getName();

						if (className.equals("java.io.StringReader")) {
							StringReader reader = (StringReader)differentTypes[idx][k];
							StringBuffer buf = new StringBuffer();

							int c = 0;

							while ((c = reader.read()) != -1) {
								buf.append((char)c);
							}

							String asString = this.rs.getString(k + 1);

							assertEquals("On row " + idx + ", column " + (k + 1), buf.toString(), asString);

						} else if (differentTypes[idx][k] instanceof java.io.InputStream) {
							ByteArrayOutputStream bOut = new ByteArrayOutputStream();

							int bytesRead = 0;

							byte[] buf = new byte[128];
							InputStream in = (InputStream)differentTypes[idx][k];

							while ((bytesRead = in.read(buf)) != -1) {
								bOut.write(buf, 0, bytesRead);
							}

							byte[] expected = bOut.toByteArray();
							byte[] actual = this.rs.getBytes(k + 1);

							assertEquals("On row " + idx + ", column " + (k + 1), StringUtils.dumpAsHex(expected, expected.length), StringUtils.dumpAsHex(actual, actual.length));
						} else if (differentTypes[idx][k] instanceof byte[]) {
							byte[] expected = (byte[])differentTypes[idx][k];
							byte[] actual = this.rs.getBytes(k + 1);
							assertEquals("On row " + idx + ", column " + (k + 1), StringUtils.dumpAsHex(expected, expected.length), StringUtils.dumpAsHex(actual, actual.length));
						} else if (differentTypes[idx][k] instanceof Timestamp) {
							assertEquals("On row " + idx + ", column " + (k + 1), sdf.format(differentTypes[idx][k]), sdf.format(this.rs.getObject(k + 1)));
						} else if (differentTypes[idx][k] instanceof Double) {
							assertEquals("On row " + idx + ", column " + (k + 1), ((Double)differentTypes[idx][k]).doubleValue(), this.rs.getDouble(k + 1), .1);
						} else if (differentTypes[idx][k] instanceof Float) {
							assertEquals("On row " + idx + ", column " + (k + 1), ((Float)differentTypes[idx][k]).floatValue(), this.rs.getFloat(k + 1), .1);
						} else if (className.equals("java.lang.Byte")) {
							// special mapping in JDBC for ResultSet.getObject()
							assertEquals("On row " + idx + ", column " + (k + 1), new Integer(((Byte)differentTypes[idx][k]).byteValue()), this.rs.getObject(k + 1));
						} else if (className.equals("java.lang.Short")) {
							// special mapping in JDBC for ResultSet.getObject()
							assertEquals("On row " + idx + ", column " + (k + 1), new Integer(((Short)differentTypes[idx][k]).shortValue()), this.rs.getObject(k + 1));
						} else {
							assertEquals("On row " + idx + ", column " + (k + 1) + " (" + differentTypes[idx][k].getClass() + "/" + this.rs.getObject(k + 1).getClass(), differentTypes[idx][k].toString(), this.rs.getObject(k + 1).toString());
						}
					}
				}

				idx++;
			}
		}
	}
	
	public void testBatchRewriteErrors() throws Exception {
		createTable("rewriteErrors", "(field1 int not null primary key)");

		Properties props = new Properties();
		Connection multiConn = null;

		for (int j = 0; j < 2; j++) {
			props.setProperty("useServerPrepStmts", "false");
	
			if (j == 1) {
				props.setProperty("continueBatchOnError", "false");
			}
			
			props.setProperty("sessionVariables", "max_allowed_packet=1024");
			props.setProperty("rewriteBatchedStatements", "true");
			multiConn = getConnectionWithProps(props);
			this.pstmt = multiConn.prepareStatement("INSERT INTO rewriteErrors VALUES (?)");
			Statement multiStmt = multiConn.createStatement();
			
			for (int i = 0; i < 4096; i++) {
				multiStmt.addBatch("INSERT INTO rewriteErrors VALUES (" + i + ")");
				this.pstmt.setInt(1, i);
				this.pstmt.addBatch();
			}
			
			multiStmt.addBatch("INSERT INTO rewriteErrors VALUES (2048)");
			
			this.pstmt.setInt(1, 2048);
			this.pstmt.addBatch();
			
			try {
				this.pstmt.executeBatch();
			} catch (BatchUpdateException bUpE) {
				int[] counts = bUpE.getUpdateCounts();
	
				for (int i = 4059; i < counts.length; i++) {
					assertEquals(counts[i], Statement.EXECUTE_FAILED);
				}
				
				assertEquals(4096, getRowCount("rewriteErrors"));
			}
			
			this.stmt.execute("TRUNCATE TABLE rewriteErrors");
			
			try {
				multiStmt.executeBatch();
			} catch (BatchUpdateException bUpE) {
				int[] counts = bUpE.getUpdateCounts();
	
				for (int i = 4094; i < counts.length; i++) {
					assertEquals(counts[i], Statement.EXECUTE_FAILED);
				}
				
				assertEquals(4096, getRowCount("rewriteErrors"));
			}
			
			if (versionMeetsMinimum(5, 0)) {
				this.stmt.execute("TRUNCATE TABLE rewriteErrors");
				
				createProcedure("sp_rewriteErrors", "(param1 INT)\nBEGIN\nINSERT INTO rewriteErrors VALUES (param1);\nEND");
				
				CallableStatement cStmt = multiConn.prepareCall("{ CALL sp_rewriteErrors(?)}");
				
				for (int i = 0; i < 4096; i++) {
					cStmt.setInt(1, i);
					cStmt.addBatch();
				}
				
				cStmt.setInt(1, 2048);
				cStmt.addBatch();
				
				try {
					cStmt.executeBatch();
				} catch (BatchUpdateException bUpE) {
					int[] counts = bUpE.getUpdateCounts();
	
					for (int i = 4093; i < counts.length; i++) {
						assertEquals(counts[i], Statement.EXECUTE_FAILED);
					}
					
					assertEquals(4096, getRowCount("rewriteErrors"));
				}
			}
		}
	}

	public void testStreamChange() throws Exception {
		createTable("testStreamChange",
				"(field1 varchar(32), field2 int, field3 TEXT, field4 BLOB)");
		this.pstmt = this.conn
				.prepareStatement("INSERT INTO testStreamChange VALUES (?, ?, ?, ?)");

		try {
			this.pstmt.setString(1, "A");
			this.pstmt.setInt(2, 1);

			char[] cArray = { 'A', 'B', 'C' };
			Reader r = new CharArrayReader(cArray);
			this.pstmt.setCharacterStream(3, r, cArray.length);

			byte[] bArray = { 'D', 'E', 'F' };
			ByteArrayInputStream bais = new ByteArrayInputStream(bArray);
			this.pstmt.setBinaryStream(4, bais, bArray.length);

			assertEquals(1, this.pstmt.executeUpdate());

			this.rs = this.stmt
					.executeQuery("SELECT field3, field4 from testStreamChange where field1='A'");
			this.rs.next();
			assertEquals("ABC", this.rs.getString(1));
			assertEquals("DEF", this.rs.getString(2));

			char[] ucArray = { 'C', 'E', 'S', 'U' };
			this.pstmt.setString(1, "CESU");
			this.pstmt.setInt(2, 3);
			Reader ucReader = new CharArrayReader(ucArray);
			this.pstmt.setCharacterStream(3, ucReader, ucArray.length);
			this.pstmt.setBinaryStream(4, null, 0);
			assertEquals(1, this.pstmt.executeUpdate());

			this.rs = this.stmt
					.executeQuery("SELECT field3, field4 from testStreamChange where field1='CESU'");
			this.rs.next();
			assertEquals("CESU", this.rs.getString(1));
			assertEquals(null, this.rs.getString(2));
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}

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

	/**
	 * DOCUMENT ME!
	 *
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testStubbed() throws SQLException {
		if (!isRunningOnJdk131()) {
			try {
				this.stmt.getResultSetHoldability();
			} catch (NotImplemented notImplEx) {
				;
			}
		}
	}

	public void testTruncationOnRead() throws Exception {
		this.rs = this.stmt.executeQuery("SELECT '" + Long.MAX_VALUE + "'");
		this.rs.next();

		try {
			this.rs.getByte(1);
			fail("Should've thrown an out-of-range exception");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
					.equals(sqlEx.getSQLState()));
		}

		try {
			this.rs.getShort(1);
			fail("Should've thrown an out-of-range exception");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
					.equals(sqlEx.getSQLState()));
		}

		try {
			this.rs.getInt(1);
			fail("Should've thrown an out-of-range exception");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
					.equals(sqlEx.getSQLState()));
		}

		this.rs = this.stmt.executeQuery("SELECT '" + Double.MAX_VALUE + "'");

		this.rs.next();

		try {
			this.rs.getByte(1);
			fail("Should've thrown an out-of-range exception");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
					.equals(sqlEx.getSQLState()));
		}

		try {
			this.rs.getShort(1);
			fail("Should've thrown an out-of-range exception");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
					.equals(sqlEx.getSQLState()));
		}

		try {
			this.rs.getInt(1);
			fail("Should've thrown an out-of-range exception");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
					.equals(sqlEx.getSQLState()));
		}

		try {
			this.rs.getLong(1);
			fail("Should've thrown an out-of-range exception");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
					.equals(sqlEx.getSQLState()));
		}

		try {
			this.rs.getLong(1);
			fail("Should've thrown an out-of-range exception");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
					.equals(sqlEx.getSQLState()));
		}

		PreparedStatement pStmt = null;

		System.out
				.println("Testing prepared statements with binary result sets now");

		try {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testTruncationOnRead");
			this.stmt
					.executeUpdate("CREATE TABLE testTruncationOnRead(intField INTEGER, bigintField BIGINT, doubleField DOUBLE)");
			this.stmt.executeUpdate("INSERT INTO testTruncationOnRead VALUES ("
					+ Integer.MAX_VALUE + ", " + Long.MAX_VALUE + ", "
					+ Double.MAX_VALUE + ")");
			this.stmt.executeUpdate("INSERT INTO testTruncationOnRead VALUES ("
					+ Integer.MIN_VALUE + ", " + Long.MIN_VALUE + ", "
					+ Double.MIN_VALUE + ")");

			pStmt = this.conn
					.prepareStatement("SELECT intField, bigintField, doubleField FROM testTruncationOnRead ORDER BY intField DESC");
			this.rs = pStmt.executeQuery();

			this.rs.next();

			try {
				this.rs.getByte(1);
				fail("Should've thrown an out-of-range exception");
			} catch (SQLException sqlEx) {
				assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
						.equals(sqlEx.getSQLState()));
			}

			try {
				this.rs.getInt(2);
				fail("Should've thrown an out-of-range exception");
			} catch (SQLException sqlEx) {
				assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
						.equals(sqlEx.getSQLState()));
			}

			try {
				this.rs.getLong(3);
				fail("Should've thrown an out-of-range exception");
			} catch (SQLException sqlEx) {
				assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
						.equals(sqlEx.getSQLState()));
			}
		} finally {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testTruncationOnRead");
		}

	}

	public void testStatementInterceptors() throws Exception {
		Connection interceptedConn = null;

		/*
		try {
			Properties props = new Properties();
			props.setProperty("statementInterceptors", "com.mysql.jdbc.interceptors.ResultSetScannerInterceptor");
			props.setProperty("resultSetScannerRegex", ".*");
			interceptedConn = getConnectionWithProps(props);
			this.rs = interceptedConn.createStatement().executeQuery("SELECT 'abc'");
			this.rs.next();
			this.rs.getString(1);
		} finally {
			closeMemberJDBCResources();

			if (interceptedConn != null) {
				interceptedConn.close();
			}
		}
		*/

		try {
			Properties props = new Properties();
			props.setProperty("statementInterceptors", "com.mysql.jdbc.interceptors.ServerStatusDiffInterceptor");

			interceptedConn = getConnectionWithProps(props);
			this.rs = interceptedConn.createStatement().executeQuery("SELECT 'abc'");
		} finally {
			closeMemberJDBCResources();

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

	public void testParameterBindings() throws Exception {
		// Need to check character set stuff, so need a new connection
		Connection utfConn = getConnectionWithProps("characterEncoding=utf-8");

		java.util.Date now = new java.util.Date();

		Object[] valuesToTest = new Object[] {
				new Byte(Byte.MIN_VALUE),
				new Short(Short.MIN_VALUE),
				new Integer(Integer.MIN_VALUE),
				new Long(Long.MIN_VALUE),
				new Double(Double.MIN_VALUE),
				"\u4E2D\u6587",
				new BigDecimal(Math.PI),
				null, // to test isNull
				now // to test serialization
		};

		StringBuffer statementText = new StringBuffer("SELECT ?");

		for (int i = 1; i < valuesToTest.length; i++) {
			statementText.append(",?");
		}

		this.pstmt = utfConn.prepareStatement(statementText.toString());

		for (int i = 0; i < valuesToTest.length; i++) {
			this.pstmt.setObject(i + 1, valuesToTest[i]);
		}

		ParameterBindings bindings = ((com.mysql.jdbc.PreparedStatement)this.pstmt).getParameterBindings();

		for (int i = 0; i < valuesToTest.length; i++) {
			if (bindings.getObject(i + 1) instanceof Number) {
				assertEquals("For binding " + (i + 1), bindings.getObject(i + 1).toString(), valuesToTest[i].toString());
			} else {
				assertEquals("For binding " + (i + 1), bindings.getObject(i + 1), valuesToTest[i]);
			}
		}
	}

	public void testLocalInfileHooked() throws Exception {
	    createTable("localInfileHooked", "(field1 int, field2 varchar(255))");
	    String streamData = "1\tabcd\n2\tefgh\n3\tijkl";
	    InputStream stream = new ByteArrayInputStream(streamData.getBytes());
	    try {
	        ((com.mysql.jdbc.Statement) this.stmt).setLocalInfileInputStream(stream);
	        this.stmt.execute("LOAD DATA LOCAL INFILE 'bogusFileName' INTO TABLE localInfileHooked");
	        assertEquals(-1, stream.read());
	        this.rs = this.stmt.executeQuery("SELECT field2 FROM localInfileHooked ORDER BY field1 ASC");
	        this.rs.next();
	        assertEquals("abcd", this.rs.getString(1));
	        this.rs.next();
            assertEquals("efgh", this.rs.getString(1));
            this.rs.next();
            assertEquals("ijkl", this.rs.getString(1));
	    } finally {
	        ((com.mysql.jdbc.Statement) this.stmt).setLocalInfileInputStream(null);
	        closeMemberJDBCResources();
	    }
	}
}