statementstest.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.simple;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.CharArrayReader;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringReader;
import java.math.BigDecimal;
import java.rmi.server.UID;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Locale;
import java.util.Properties;

import testsuite.BaseTestCase;

import com.mysql.jdbc.NotImplemented;
import com.mysql.jdbc.SQLError;
import com.mysql.jdbc.StringUtils;

/**
 * DOCUMENT ME!
 * 
 * @author Mark Matthews
 * @version $Id: StatementsTest.java 4494 2005-10-31 22:30:34 -0600 (Mon, 31 Oct
 *          2005) mmatthews $
 */
public class StatementsTest extends BaseTestCase {
	private static final int MAX_COLUMN_LENGTH = 255;

	private static final int MAX_COLUMNS_TO_TEST = 40;

	private static final int MIN_COLUMN_LENGTH = 10;

	private static final int STEP = 8;

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

	/**
	 * Creates a new StatementsTest object.
	 * 
	 * @param name
	 *            DOCUMENT ME!
	 */
	public StatementsTest(String name) {
		super(name);
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws Exception
	 *             DOCUMENT ME!
	 */
	public void setUp() throws Exception {
		super.setUp();

		this.stmt.executeUpdate("DROP TABLE IF EXISTS statement_test");

		this.stmt.executeUpdate("DROP TABLE IF EXISTS statement_batch_test");

		this.stmt
				.executeUpdate("CREATE TABLE statement_test (id int not null primary key auto_increment, strdata1 varchar(255) not null, strdata2 varchar(255))");

		this.stmt.executeUpdate("CREATE TABLE statement_batch_test "
				+ "(id int not null primary key auto_increment, "
				+ "strdata1 varchar(255) not null, strdata2 varchar(255), "
				+ "UNIQUE INDEX (strdata1))");

		for (int i = 6; i < MAX_COLUMNS_TO_TEST; i += STEP) {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS statement_col_test_"
					+ i);

			StringBuffer insertBuf = new StringBuffer(
					"INSERT INTO statement_col_test_");
			StringBuffer stmtBuf = new StringBuffer(
					"CREATE TABLE IF NOT EXISTS statement_col_test_");
			stmtBuf.append(i);
			insertBuf.append(i);
			stmtBuf.append(" (");
			insertBuf.append(" VALUES (");

			boolean firstTime = true;

			for (int j = 0; j < i; j++) {
				if (!firstTime) {
					stmtBuf.append(",");
					insertBuf.append(",");
				} else {
					firstTime = false;
				}

				stmtBuf.append("col_");
				stmtBuf.append(j);
				stmtBuf.append(" VARCHAR(");
				stmtBuf.append(MAX_COLUMN_LENGTH);
				stmtBuf.append(")");
				insertBuf.append("'");

				int numChars = 16;

				for (int k = 0; k < numChars; k++) {
					insertBuf.append("A");
				}

				insertBuf.append("'");
			}

			stmtBuf.append(")");
			insertBuf.append(")");
			this.stmt.executeUpdate(stmtBuf.toString());
			this.stmt.executeUpdate(insertBuf.toString());
		}

		// explicitly set the catalog to exercise code in execute(),
		// executeQuery() and
		// executeUpdate()
		// FIXME: Only works on Windows!
		// this.conn.setCatalog(this.conn.getCatalog().toUpperCase());
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws Exception
	 *             DOCUMENT ME!
	 */
	public void tearDown() throws Exception {
		this.stmt.executeUpdate("DROP TABLE statement_test");

		for (int i = 0; i < MAX_COLUMNS_TO_TEST; i += STEP) {
			StringBuffer stmtBuf = new StringBuffer(
					"DROP TABLE IF EXISTS statement_col_test_");
			stmtBuf.append(i);
			this.stmt.executeUpdate(stmtBuf.toString());
		}

		try {
			this.stmt.executeUpdate("DROP TABLE statement_batch_test");
		} catch (SQLException sqlEx) {
			;
		}

		super.tearDown();
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testAccessorsAndMutators() throws SQLException {
		assertTrue("Connection can not be null, and must be same connection",
				this.stmt.getConnection() == this.conn);

		// Set max rows, to exercise code in execute(), executeQuery() and
		// executeUpdate()
		Statement accessorStmt = null;

		try {
			accessorStmt = this.conn.createStatement();
			accessorStmt.setMaxRows(1);
			accessorStmt.setMaxRows(0); // FIXME, test that this actually
			// affects rows returned
			accessorStmt.setMaxFieldSize(255);
			assertTrue("Max field size should match what was set", accessorStmt
					.getMaxFieldSize() == 255);

			try {
				accessorStmt.setMaxFieldSize(Integer.MAX_VALUE);
				fail("Should not be able to set max field size > max_packet_size");
			} catch (SQLException sqlEx) {
				;
			}

			accessorStmt.setCursorName("undef");
			accessorStmt.setEscapeProcessing(true);
			accessorStmt.setFetchDirection(java.sql.ResultSet.FETCH_FORWARD);

			int fetchDirection = accessorStmt.getFetchDirection();
			assertTrue("Set fetch direction != get fetch direction",
					fetchDirection == java.sql.ResultSet.FETCH_FORWARD);

			try {
				accessorStmt.setFetchDirection(Integer.MAX_VALUE);
				fail("Should not be able to set fetch direction to invalid value");
			} catch (SQLException sqlEx) {
				;
			}

			try {
				accessorStmt.setMaxRows(50000000 + 10);
				fail("Should not be able to set max rows > 50000000");
			} catch (SQLException sqlEx) {
				;
			}

			try {
				accessorStmt.setMaxRows(Integer.MIN_VALUE);
				fail("Should not be able to set max rows < 0");
			} catch (SQLException sqlEx) {
				;
			}

			int fetchSize = this.stmt.getFetchSize();

			try {
				accessorStmt.setMaxRows(4);
				accessorStmt.setFetchSize(Integer.MAX_VALUE);
				fail("Should not be able to set FetchSize > max rows");
			} catch (SQLException sqlEx) {
				;
			}

			try {
				accessorStmt.setFetchSize(-2);
				fail("Should not be able to set FetchSize < 0");
			} catch (SQLException sqlEx) {
				;
			}

			assertTrue(
					"Fetch size before invalid setFetchSize() calls should match fetch size now",
					fetchSize == this.stmt.getFetchSize());
		} finally {
			if (accessorStmt != null) {
				try {
					accessorStmt.close();
				} catch (SQLException sqlEx) {
					;
				}

				accessorStmt = null;
			}
		}
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testAutoIncrement() throws SQLException {
		if (!isRunningOnJdk131()) {
			try {
				this.stmt = this.conn.createStatement(
						java.sql.ResultSet.TYPE_FORWARD_ONLY,
						java.sql.ResultSet.CONCUR_READ_ONLY);
				this.stmt.setFetchSize(Integer.MIN_VALUE);
				this.stmt
						.executeUpdate("INSERT INTO statement_test (strdata1) values ('blah')");

				int autoIncKeyFromApi = -1;
				this.rs = this.stmt.getGeneratedKeys();

				if (this.rs.next()) {
					autoIncKeyFromApi = this.rs.getInt(1);
				} else {
					fail("Failed to retrieve AUTO_INCREMENT using Statement.getGeneratedKeys()");
				}

				this.rs.close();

				int autoIncKeyFromFunc = -1;
				this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID()");

				if (this.rs.next()) {
					autoIncKeyFromFunc = this.rs.getInt(1);
				} else {
					fail("Failed to retrieve AUTO_INCREMENT using LAST_INSERT_ID()");
				}

				if ((autoIncKeyFromApi != -1) && (autoIncKeyFromFunc != -1)) {
					assertTrue(
							"Key retrieved from API ("
									+ autoIncKeyFromApi
									+ ") does not match key retrieved from LAST_INSERT_ID() "
									+ autoIncKeyFromFunc + ") function",
							autoIncKeyFromApi == autoIncKeyFromFunc);
				} else {
					fail("AutoIncrement keys were '0'");
				}
			} finally {
				if (this.rs != null) {
					try {
						this.rs.close();
					} catch (Exception ex) { /* ignore */
						;
					}
				}

				this.rs = null;
			}
		}
	}

	/**
	 * Tests all variants of numerical types (signed/unsigned) for correct
	 * operation when used as return values from a prepared statement.
	 * 
	 * @throws Exception
	 */
	public void testBinaryResultSetNumericTypes() throws Exception {
		/*
		 * TINYINT 1 -128 127 SMALLINT 2 -32768 32767 MEDIUMINT 3 -8388608
		 * 8388607 INT 4 -2147483648 2147483647 BIGINT 8 -9223372036854775808
		 * 9223372036854775807
		 */

		String unsignedMinimum = "0";

		String tiMinimum = "-128";
		String tiMaximum = "127";
		String utiMaximum = "255";

		String siMinimum = "-32768";
		String siMaximum = "32767";
		String usiMaximum = "65535";

		String miMinimum = "-8388608";
		String miMaximum = "8388607";
		String umiMaximum = "16777215";

		String iMinimum = "-2147483648";
		String iMaximum = "2147483647";
		String uiMaximum = "4294967295";

		String biMinimum = "-9223372036854775808";
		String biMaximum = "9223372036854775807";
		String ubiMaximum = "18446744073709551615";

		try {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testBinaryResultSetNumericTypes");
			this.stmt
					.executeUpdate("CREATE TABLE testBinaryResultSetNumericTypes(rowOrder TINYINT, ti TINYINT,"
							+ "uti TINYINT UNSIGNED, si SMALLINT,"
							+ "usi SMALLINT UNSIGNED, mi MEDIUMINT,"
							+ "umi MEDIUMINT UNSIGNED, i INT, ui INT UNSIGNED,"
							+ "bi BIGINT, ubi BIGINT UNSIGNED)");
			PreparedStatement inserter = this.conn
					.prepareStatement("INSERT INTO testBinaryResultSetNumericTypes VALUES (?,?,?,?,?,?,?,?,?,?,?)");
			inserter.setInt(1, 0);
			inserter.setString(2, tiMinimum);
			inserter.setString(3, unsignedMinimum);
			inserter.setString(4, siMinimum);
			inserter.setString(5, unsignedMinimum);
			inserter.setString(6, miMinimum);
			inserter.setString(7, unsignedMinimum);
			inserter.setString(8, iMinimum);
			inserter.setString(9, unsignedMinimum);
			inserter.setString(10, biMinimum);
			inserter.setString(11, unsignedMinimum);
			inserter.executeUpdate();

			inserter.setInt(1, 1);
			inserter.setString(2, tiMaximum);
			inserter.setString(3, utiMaximum);
			inserter.setString(4, siMaximum);
			inserter.setString(5, usiMaximum);
			inserter.setString(6, miMaximum);
			inserter.setString(7, umiMaximum);
			inserter.setString(8, iMaximum);
			inserter.setString(9, uiMaximum);
			inserter.setString(10, biMaximum);
			inserter.setString(11, ubiMaximum);
			inserter.executeUpdate();

			PreparedStatement selector = this.conn
					.prepareStatement("SELECT * FROM testBinaryResultSetNumericTypes ORDER by rowOrder ASC");
			this.rs = selector.executeQuery();

			assertTrue(this.rs.next());

			assertTrue(this.rs.getString(2).equals(tiMinimum));
			assertTrue(this.rs.getString(3).equals(unsignedMinimum));
			assertTrue(this.rs.getString(4).equals(siMinimum));
			assertTrue(this.rs.getString(5).equals(unsignedMinimum));
			assertTrue(this.rs.getString(6).equals(miMinimum));
			assertTrue(this.rs.getString(7).equals(unsignedMinimum));
			assertTrue(this.rs.getString(8).equals(iMinimum));
			assertTrue(this.rs.getString(9).equals(unsignedMinimum));
			assertTrue(this.rs.getString(10).equals(biMinimum));
			assertTrue(this.rs.getString(11).equals(unsignedMinimum));

			assertTrue(this.rs.next());

			assertTrue(this.rs.getString(2) + " != " + tiMaximum, this.rs
					.getString(2).equals(tiMaximum));
			assertTrue(this.rs.getString(3) + " != " + utiMaximum, this.rs
					.getString(3).equals(utiMaximum));
			assertTrue(this.rs.getString(4) + " != " + siMaximum, this.rs
					.getString(4).equals(siMaximum));
			assertTrue(this.rs.getString(5) + " != " + usiMaximum, this.rs
					.getString(5).equals(usiMaximum));