statementregressiontest.java

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

/*
 Copyright (C) 2002-2007 MySQL AB

 This program is free software; you can redistribute it and/or modify
 it under the terms of version 2 of the GNU General Public License as 
 published by the Free Software Foundation.

 There are special exceptions to the terms and conditions of the GPL 
 as it is applied to this software. View the full text of the 
 exception in file EXCEPTIONS-CONNECTOR-J in the directory of this 
 software distribution.

 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU General Public License for more details.

 You should have received a copy of the GNU General Public License
 along with this program; if not, write to the Free Software
 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA



 */
package testsuite.regression;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.CharArrayReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintStream;
import java.io.StringReader;
import java.io.Writer;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.BatchUpdateException;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DataTruncation;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Locale;
import java.util.Properties;
import java.util.TimeZone;

import testsuite.BaseTestCase;

import com.mysql.jdbc.SQLError;
import com.mysql.jdbc.ServerPreparedStatement;
import com.mysql.jdbc.exceptions.MySQLTimeoutException;

/**
 * Regression tests for the Statement class
 * 
 * @author Mark Matthews
 */
public class StatementRegressionTest extends BaseTestCase {
	class PrepareThread extends Thread {
		Connection c;

		PrepareThread(Connection cn) {
			this.c = cn;
		}

		public void run() {
			for (int i = 0; i < 20; i++) // force this to end eventually
			{
				try {
					this.c.prepareStatement("SELECT 1");
					StatementRegressionTest.this.testServerPrepStmtDeadlockCounter++;
					Thread.sleep(400);
				} catch (SQLException sqlEx) {
					throw new RuntimeException(sqlEx);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}
		}
	}

	static int count = 0;

	static int nextID = 1; // The next ID we expected to generate

	/*
	 * Each row in this table is to be converted into a single REPLACE
	 * statement. If the value is zero, a new record is to be created using then
	 * autoincrement feature. If the value is non-zero, the existing row of that
	 * value is to be replace with, obviously, the same key. I expect one
	 * Generated Key for each zero value - but I would accept one key for each
	 * value, with non-zero values coming back as themselves.
	 */
	static final int[][] tests = { { 0 }, // generate 1
			{ 1, 0, 0 }, // update 1, generate 2, 3
			{ 2, 0, 0, }, // update 2, generate 3, 4
	};

	/**
	 * Runs all test cases in this test suite
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		junit.textui.TestRunner.run(StatementRegressionTest.class);
	}

	private int testServerPrepStmtDeadlockCounter = 0;

	/**
	 * Constructor for StatementRegressionTest.
	 * 
	 * @param name
	 *            the name of the test to run
	 */
	public StatementRegressionTest(String name) {
		super(name);
	}

	private void addBatchItems(Statement statement, PreparedStatement pStmt,
			String tableName, int i) throws SQLException {
		pStmt.setString(1, "ps_batch_" + i);
		pStmt.setString(2, "ps_batch_" + i);
		pStmt.addBatch();

		statement.addBatch("INSERT INTO " + tableName
				+ " (strdata1, strdata2) VALUES " + "(\"s_batch_" + i
				+ "\",\"s_batch_" + i + "\")");
	}

	private void createGGKTables() throws Exception {
		// Delete and recreate table
		dropGGKTables();

		this.stmt.executeUpdate("CREATE TABLE testggk ("
				+ "id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,"
				+ "val INT NOT NULL" + ")");
	}

	private void doGGKTestPreparedStatement(int[] values, boolean useUpdate)
			throws Exception {
		// Generate the the multiple replace command
		StringBuffer cmd = new StringBuffer("REPLACE INTO testggk VALUES ");
		int newKeys = 0;

		for (int i = 0; i < values.length; i++) {
			cmd.append("(");

			if (values[i] == 0) {
				cmd.append("NULL");
				newKeys += 1;
			} else {
				cmd.append(values[i]);
			}

			cmd.append(", ");
			cmd.append(count++);
			cmd.append("), ");
		}

		cmd.setLength(cmd.length() - 2); // trim the final ", "

		// execute and print it
		System.out.println(cmd.toString());

		PreparedStatement pStmt = this.conn.prepareStatement(cmd.toString(),
				Statement.RETURN_GENERATED_KEYS);

		if (useUpdate) {
			pStmt.executeUpdate();
		} else {
			pStmt.execute();
		}

		// print out what actually happened
		System.out.println("Expect " + newKeys
				+ " generated keys, starting from " + nextID);

		this.rs = pStmt.getGeneratedKeys();
		StringBuffer res = new StringBuffer("Got keys");

		int[] generatedKeys = new int[newKeys];
		int i = 0;

		while (this.rs.next()) {
			if (i < generatedKeys.length) {
				generatedKeys[i] = this.rs.getInt(1);
			}

			i++;

			res.append(" " + this.rs.getInt(1));
		}

		int numberOfGeneratedKeys = i;

		assertTrue(
				"Didn't retrieve expected number of generated keys, expected "
						+ newKeys + ", found " + numberOfGeneratedKeys,
				numberOfGeneratedKeys == newKeys);
		assertTrue("Keys didn't start with correct sequence: ",
				generatedKeys[0] == nextID);

		System.out.println(res.toString());

		// Read and print the new state of the table
		this.rs = this.stmt.executeQuery("SELECT id, val FROM testggk");
		System.out.println("New table contents ");

		while (this.rs.next())
			System.out.println("Id " + this.rs.getString(1) + " val "
					+ this.rs.getString(2));

		// Tidy up
		System.out.println("");
		nextID += newKeys;
	}

	private void doGGKTestStatement(int[] values, boolean useUpdate)
			throws Exception {
		// Generate the the multiple replace command
		StringBuffer cmd = new StringBuffer("REPLACE INTO testggk VALUES ");
		int newKeys = 0;

		for (int i = 0; i < values.length; i++) {
			cmd.append("(");

			if (values[i] == 0) {
				cmd.append("NULL");
				newKeys += 1;
			} else {
				cmd.append(values[i]);
			}

			cmd.append(", ");
			cmd.append(count++);
			cmd.append("), ");
		}

		cmd.setLength(cmd.length() - 2); // trim the final ", "

		// execute and print it
		System.out.println(cmd.toString());

		if (useUpdate) {
			this.stmt.executeUpdate(cmd.toString(),
					Statement.RETURN_GENERATED_KEYS);
		} else {
			this.stmt.execute(cmd.toString(), Statement.RETURN_GENERATED_KEYS);
		}

		// print out what actually happened
		System.out.println("Expect " + newKeys
				+ " generated keys, starting from " + nextID);

		this.rs = this.stmt.getGeneratedKeys();
		StringBuffer res = new StringBuffer("Got keys");

		int[] generatedKeys = new int[newKeys];
		int i = 0;

		while (this.rs.next()) {
			if (i < generatedKeys.length) {
				generatedKeys[i] = this.rs.getInt(1);
			}

			i++;

			res.append(" " + this.rs.getInt(1));
		}

		int numberOfGeneratedKeys = i;

		assertTrue(
				"Didn't retrieve expected number of generated keys, expected "
						+ newKeys + ", found " + numberOfGeneratedKeys,
				numberOfGeneratedKeys == newKeys);
		assertTrue("Keys didn't start with correct sequence: ",
				generatedKeys[0] == nextID);

		System.out.println(res.toString());

		// Read and print the new state of the table
		this.rs = this.stmt.executeQuery("SELECT id, val FROM testggk");
		System.out.println("New table contents ");

		while (this.rs.next())
			System.out.println("Id " + this.rs.getString(1) + " val "
					+ this.rs.getString(2));

		// Tidy up
		System.out.println("");
		nextID += newKeys;
	}

	private void dropGGKTables() throws Exception {
		this.stmt.executeUpdate("DROP TABLE IF EXISTS testggk");
	}

	/**
	 * @param pStmt
	 * @param catId
	 * @throws SQLException
	 */
	private void execQueryBug5191(PreparedStatement pStmt, int catId)
			throws SQLException {
		pStmt.setInt(1, catId);

		this.rs = pStmt.executeQuery();

		assertTrue(this.rs.next());
		assertTrue(this.rs.next());
		// assertTrue(rs.next());

		assertFalse(this.rs.next());
	}

	private String getByteArrayString(byte[] ba) {
		StringBuffer buffer = new StringBuffer();
		if (ba != null) {
			for (int i = 0; i < ba.length; i++) {
				buffer.append("0x" + Integer.toHexString(ba[i] & 0xff) + " ");
			}
		} else {
			buffer.append("null");
		}
		return buffer.toString();
	}

	/**
	 * @param continueBatchOnError
	 * @throws SQLException
	 */
	private void innerBug6823(boolean continueBatchOnError) throws SQLException {
		Properties continueBatchOnErrorProps = new Properties();
		continueBatchOnErrorProps.setProperty("continueBatchOnError", String
				.valueOf(continueBatchOnError));
		this.conn = getConnectionWithProps(continueBatchOnErrorProps);
		Statement statement = this.conn.createStatement();

		String tableName = "testBug6823";

		createTable(tableName, "(id int not null primary key auto_increment,"
				+ " strdata1 varchar(255) not null, strdata2 varchar(255),"
				+ " UNIQUE INDEX (strdata1))");

		PreparedStatement pStmt = this.conn.prepareStatement("INSERT INTO "
				+ tableName + " (strdata1, strdata2) VALUES (?,?)");

		int c = 0;
		addBatchItems(statement, pStmt, tableName, ++c);
		addBatchItems(statement, pStmt, tableName, ++c);
		addBatchItems(statement, pStmt, tableName, ++c);
		addBatchItems(statement, pStmt, tableName, c); // duplicate entry
		addBatchItems(statement, pStmt, tableName, ++c);
		addBatchItems(statement, pStmt, tableName, ++c);

		int expectedUpdateCounts = continueBatchOnError ? 6 : 3;

		BatchUpdateException e1 = null;
		BatchUpdateException e2 = null;

		int[] updateCountsPstmt = null;
		try {
			updateCountsPstmt = pStmt.executeBatch();
		} catch (BatchUpdateException e) {
			e1 = e;
			updateCountsPstmt = e1.getUpdateCounts();
		}

		int[] updateCountsStmt = null;
		try {
			updateCountsStmt = statement.executeBatch();
		} catch (BatchUpdateException e) {
			e2 = e;
			updateCountsStmt = e1.getUpdateCounts();
		}

		assertNotNull(e1);
		assertNotNull(e2);

		assertEquals(expectedUpdateCounts, updateCountsPstmt.length);
		assertEquals(expectedUpdateCounts, updateCountsStmt.length);

		if (continueBatchOnError) {
			assertTrue(updateCountsPstmt[3] == Statement.EXECUTE_FAILED);
			assertTrue(updateCountsStmt[3] == Statement.EXECUTE_FAILED);
		}

		int psRows = 0;
		this.rs = this.stmt.executeQuery("SELECT * from " + tableName
				+ " WHERE strdata1 like \"ps_%\"");
		while (this.rs.next()) {
			psRows++;
		}
		assertTrue(psRows > 0);

		int sRows = 0;
		this.rs = this.stmt.executeQuery("SELECT * from " + tableName
				+ " WHERE strdata1 like \"s_%\"");
		while (this.rs.next()) {
			sRows++;
		}
		assertTrue(sRows > 0);

		assertTrue(psRows + "!=" + sRows, psRows == sRows);
	}

	/**
	 * Tests fix for BUG#10155, double quotes not recognized when parsing
	 * client-side prepared statements.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug10155() throws Exception {
		this.conn.prepareStatement(
				"SELECT \"Test question mark? Test single quote'\"")
				.executeQuery().close();
	}

	/**
	 * Tests fix for BUG#10630, Statement.getWarnings() fails with NPE if
	 * statement has been closed.
	 */
	public void testBug10630() throws Exception {
		Connection conn2 = null;
		Statement stmt2 = null;

		try {
			conn2 = getConnectionWithProps(null);
			stmt2 = conn2.createStatement();

			conn2.close();
			stmt2.getWarnings();
			fail("Should've caught an exception here");
		} catch (SQLException sqlEx) {
			assertEquals("08003", sqlEx.getSQLState());
		} finally {
			if (stmt2 != null) {
				stmt2.close();
			}

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

	/**
	 * Tests fix for BUG#11115, Varbinary data corrupted when using server-side
	 * prepared statements.
	 */