下面列出了java.sql.Statement#close ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* Tests updating a Blob from a scollable resultset produced by a
* select query with projection. Updates are made using
* result set update methods.
* @exception SQLException causes test to fail with error
* @exception IOException causes test to fail with error
*/
public void testUpdateBlobFromScrollableResultSetWithProjectUsingResultSetMethods()
throws SQLException, IOException
{
final Statement stmt =
createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
final ResultSet rs =
stmt.executeQuery("SELECT data,val,length from " +
BLOBDataModelSetup.getBlobTableName());
println("Last");
rs.last();
final int newVal = rs.getInt(2) + 11;
final int newSize = rs.getInt(3) / 2;
testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
println("Verify updated blob using result set");
verifyBlob(newVal, newSize, rs.getBlob(1));
rs.close();
stmt.close();
}
/**
* 获得单元测试配置文件信息
*
* @return
* @throws Exception
*/
public static UnitTestConfig getUnitTestConfig(String name) throws Exception {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = getConnection();
stat = conn.createStatement();
String sql = String.format("select * from UnitTestConfig where name='%s'", name);
ResultSet resultSet = stat.executeQuery(sql);
while (resultSet.next()) {
UnitTestConfig result = JSON.parseObject(resultSet.getString("value"), UnitTestConfig.class);
return result;
}
} finally {
if (rs != null)
rs.close();
if (stat != null)
stat.close();
if (conn != null)
conn.close();
}
return null;
}
public static Connection getConnection()
throws SQLException
{
Connection connection = AbstractDriverIT.getConnection();
Statement statement = connection.createStatement();
statement.execute(
"alter session set " +
"TIMEZONE='America/Los_Angeles'," +
"TIMESTAMP_TYPE_MAPPING='TIMESTAMP_LTZ'," +
"TIMESTAMP_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
"TIMESTAMP_TZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
"TIMESTAMP_LTZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
"TIMESTAMP_NTZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'");
statement.close();
return connection;
}
/**
* Test that % matches tab characters (DERBY-1262).
*/
public void testTabs() throws SQLException {
Statement s = createStatement();
s.executeUpdate("insert into test values " +
"('asd\tp', 'asd\tp', 'asd\tp'), " +
"('ase\tp', 'ase\tg', 'ase\tg')");
String[][] expected = { {"asdf"}, {"asdg"}, {"asd\tp"} };
JDBC.assertUnorderedResultSet(
s.executeQuery("select c10 from test where c10 like 'asd%'"),
expected);
PreparedStatement ps =
prepareStatement("select c10 from test where c10 like ?");
ps.setString(1, "asd%");
JDBC.assertUnorderedResultSet(ps.executeQuery(), expected);
s.close();
ps.close();
}
protected void loadSingleSQL(Connection aConnection, String aResource)
throws IOException, SQLException {
String theSQL = readResourceFile(aResource);
Statement theStatement = aConnection.createStatement();
StringTokenizer theST = new StringTokenizer(theSQL, ";");
while (theST.hasMoreTokens()) {
String theSingleSQL = theST.nextToken();
if (StringUtils.isNotEmpty(theSingleSQL)) {
theStatement.execute(theSingleSQL);
}
}
theStatement.close();
}
/**
* Test that each system table has a table type of "S".
*
* @throws SQLException
*/
public void testSystemCatalogTableTypes() throws SQLException {
Statement s = createStatement();
ResultSet rs = s.executeQuery("select TABLENAME, TABLETYPE from sys.systables order by tablename");
boolean nonEmptyResultSet = false;
while(rs.next()) {
nonEmptyResultSet = true;
assertEquals("S", rs.getString(2));
}
assertTrue(nonEmptyResultSet);
rs.close();
s.close();
}
/**
* Tests shutdown with the transaction was rollbacked, and next transaction was not
* committed/rollbacked yet.
*/
public void testOnceRollbackedAndLeft() throws SQLException {
rollback();
insertIntoTestTable(1001, 999);
assertShutdownOK();
Statement st = createStatement();
JDBC.assertSingleValueResultSet(st.executeQuery("select " + "count(*) "
+ "from " + "TEST_TABLE "), "0");
st.close();
}
/**
* Tests shutdown with the only transaction was committed.
*/
public void testOnlyTransactionWasCommited() throws SQLException {
commit();
assertShutdownOK();
Statement st = createStatement();
JDBC.assertSingleValueResultSet(st.executeQuery("select " + "count(*) "
+ "from " + "TEST_TABLE "), "1000");
st.close();
}
/**
* Closes SQL Statement object without throwing Exceptions.
* Exceptions are still loged as errors.
* @param statements
*/
protected void closeSilently(Statement... statements) {
for (Statement statement : statements) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
baseDaoImplLogger.error("Error occured: " + e.getMessage(), e);
}
}
}
}
/**
* Test the firing order of triggers. Should be:
*
* Before operations
* after operations
*
* For multiple triggers within the same group (before or after)
* firing order is determined by create order.
* @throws SQLException
*
*/
public void testFiringOrder() throws SQLException
{
Statement s = createStatement();
s.executeUpdate("CREATE TABLE T(ID INT)");
int triggerCount = createRandomTriggers()[0];
List info = new ArrayList();
TRIGGER_INFO.set(info);
// Check ordering with a single row.
s.execute("INSERT INTO T VALUES 1");
commit();
int fireCount = assertFiringOrder("INSERT", 1);
info.clear();
s.execute("UPDATE T SET ID = 2");
commit();
fireCount += assertFiringOrder("UPDATE", 1);
info.clear();
s.execute("DELETE FROM T");
commit();
fireCount += assertFiringOrder("DELETE", 1);
info.clear();
assertEquals("All triggers fired?", triggerCount, fireCount);
// and now with multiple rows
s.execute("INSERT INTO T VALUES 1,2,3");
commit();
fireCount = assertFiringOrder("INSERT", 3);
info.clear();
s.execute("UPDATE T SET ID = 2");
commit();
fireCount += assertFiringOrder("UPDATE", 3);
info.clear();
s.execute("DELETE FROM T");
commit();
fireCount += assertFiringOrder("DELETE", 3);
info.clear();
// cannot assume row triggers were created so can only
// say that at least all the triggers were fired.
assertTrue("Sufficient triggers fired?", fireCount >= triggerCount);
// and then with no rows
assertTableRowCount("T", 0);
s.execute("INSERT INTO T SELECT ID FROM T");
commit();
fireCount = assertFiringOrder("INSERT", 0);
info.clear();
s.execute("UPDATE T SET ID = 2");
commit();
fireCount += assertFiringOrder("UPDATE", 0);
info.clear();
s.execute("DELETE FROM T");
commit();
fireCount += assertFiringOrder("DELETE", 0);
info.clear();
// can't assert anthing about fireCount, could be all row triggers.
s.close();
}
/**
* Test 7 - Online compress test for fetching more rows than buffer limit.
* <p>
* For smaller row size, if number of rows per page is more than max buffer
* size, then check if the remaining rows are also fetched for Compress
* Operation
* <p>
**/
private void test7(
Connection conn,
String test_name,
String table_name)
throws SQLException
{
beginTest(conn, test_name);
Statement s = conn.createStatement();
s.execute("create table " + table_name + "(keycol int)");
s.close();
PreparedStatement insert_stmt =
conn.prepareStatement("insert into " + table_name + " values(?)");
try
{
for (int i = 0; i < 1200; i++)
{
insert_stmt.setInt(1, i);
insert_stmt.execute();
}
}
catch (SQLException sqle)
{
System.out.println(
"Exception while trying to insert a row");
throw sqle;
}
conn.commit();
// delete the front rows leaving the last 200. Post commit may reclaim
// space on pages where all rows are deleted.
executeQuery(
conn, "delete from " + table_name + " where keycol < 1000", true);
conn.commit();
if (verbose)
testProgress("deleted first 1000 rows, now calling compress.");
callCompress(conn, "APP", table_name, true, true, true, true);
testProgress("delete rows case succeeded.");
executeQuery(conn, "drop table " + table_name, true);
endTest(conn, test_name);
}
/**
* Test various default store properties for the system tables.
*
* @throws SQLException
*/
//FIXME
// GemFireXD returns different values for this function, need to tweak this testcase
// but comment out for now
//public void testSystemCatalogStoreProperties() throws SQLException{
public void _testSystemCatalogStoreProperties() throws SQLException{
Statement s = createStatement();
s.execute("create function gatp(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllTableProperties' LANGUAGE JAVA PARAMETER STYLE JAVA");
s.execute("create function gaip(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllIndexProperties' LANGUAGE JAVA PARAMETER STYLE JAVA");
// get the properties for the heaps
ResultSet rs = s.executeQuery("select tablename,gatp('SYS', tablename) from sys.systables order by tablename");
boolean nonEmptyResultSet = false;
String tablename = null;
String sysdummy = "SYSDUMMY1";
while(rs.next()) {
nonEmptyResultSet = true;
tablename = rs.getString(1);
if (tablename.equals(sysdummy)) {
assertTrue(rs.getString(2).startsWith("{ }"));
} else {
String expected = "{ GEMFIRE_REGION_ATTRIBUTES=MISSING_VALUE, ddIndex=MISSING_VALUE, gemfirexd.schema-name=SYS, gemfirexd.table-name="+tablename+" }";
assertTrue(rs.getString(2).startsWith(expected));
}
}
assertTrue(nonEmptyResultSet);
rs.close();
// get the properties for the indexes
rs = s.executeQuery("select conglomeratename, gaip('SYS', conglomeratename) from sys.sysconglomerates where isindex order by conglomeratename");
nonEmptyResultSet = false;
// GemStone changes BEGIN
//String expected = "{ baseConglomerateId=MISSING_VALUE, ddIndex=MISSING_VALUE, indexComparator=MISSING_VALUE, gemfirexd.schema-name=SYS, gemfirexd.table-name=";
///String expectedRegex = "\\{ baseConglomerateId=MISSING_VALUE, ddIndex=MISSING_VALUE, gemfirexd.schema-name=SYS, gemfirexd.table-name=\\d*, indexComparator=MISSING_VALUE \\}";
String expectedRegex = "\\{ baseConglomerateId=MISSING_VALUE, ddIndex=MISSING_VALUE, indexComparator=MISSING_VALUE, gemfirexd.schema-name=SYS, gemfirexd.table-name=\\d* \\}";
// GemStone changes END
while(rs.next()) {
nonEmptyResultSet = true;
// GemStone changes BEGIN
//assertTrue(rs.getString(2).startsWith(expected));
if (!Pattern.matches(expectedRegex, rs.getString(2))) {
fail("Conglomerate row "+rs.getString(2)+" does not match " + expectedRegex);
}
// GemStone changes END
}
assertTrue(nonEmptyResultSet);
rs.close();
s.close();
}
@Test
public void testPartitioned() throws Exception {
// copy data file to test dir
File dataDir = context.getDataDir();
File dataFile = new File(dataDir, SINGLE_TYPE_DATA_FILE_NAME);
FileOutputStream to = new FileOutputStream(dataFile);
Resources.copy(Resources.getResource(SINGLE_TYPE_DATA_FILE_NAME), to);
to.close();
String viewName = "view1";
String db = "db1";
String tabName = "tab1";
policyFile
.addPermissionsToRole("view", "server=server1->db=" + db + "->table=" + viewName)
.addRolesToGroup(USERGROUP1, "view")
.setUserGroupMapping(StaticUserGroup.getStaticMapping());
policyFile.write(context.getPolicyFile());
//admin creates a view
Connection conn = context.createConnection(ADMIN1);
Statement stmt = context.createStatement(conn);
stmt.execute("DROP DATABASE IF EXISTS " + db + " CASCADE");
stmt.execute("CREATE DATABASE " + db);
stmt.execute("use " + db);
stmt.execute("create table " + tabName + " (id int) partitioned by (part string)");
stmt.execute("load data local inpath '" + dataFile + "' into table " + tabName + " PARTITION (part=\"a\")");
stmt.execute("load data local inpath '" + dataFile + "' into table " + tabName + " PARTITION (part=\"b\")");
ResultSet res = stmt.executeQuery("select count(*) from " + tabName);
Assert.assertThat(res, notNullValue());
while(res.next()) {
Assume.assumeTrue(res.getInt(1) == Integer.valueOf(1000));
}
stmt.execute("create view " + viewName + " as select * from " + tabName + " where id<100");
res = stmt.executeQuery("select count(*) from " + viewName);
Assert.assertThat(res, notNullValue());
int rowsInView = 0;
while(res.next()) {
rowsInView = res.getInt(1);
}
stmt.close();
conn.close();
Connection userConn = context.createConnection(USER1_1);
Statement userStmt = context.createStatement(userConn);
userStmt.execute("use " + db);
res = userStmt.executeQuery("select count(*) from " + viewName);
Assert.assertThat(res, notNullValue());
while(res.next()) {
Assert.assertThat(res.getInt(1), is(rowsInView));
}
userStmt.close();
userConn.close();
// user2 hasn't the privilege for the view
userConn = context.createConnection(USER2_1);
userStmt = context.createStatement(userConn);
try {
userStmt.executeQuery("select count(*) from " + viewName);
Assert.fail("Expected SQL exception");
} catch (SQLException e) {
// ignore the exception
}
userStmt.close();
userConn.close();
}
/**
* Execute and check the ODBC variant of getImported/Exported keys, which
* uses the SQLFOREIGNKEYS system procedure to provide the same information
* to ODBC clients. Note that for "correctness" we just compare the results
* to those of the equivalent JDBC calls; this fixture assumes that the
* the JDBC calls return correct results (testing of the JDBC results occurs
* elsewhere, see fixtures testGetXXportedKeys()
*/
public void testGetXXportedKeysODBC() throws SQLException, IOException
{
Statement st = createStatement();
// Create some simple tables with primary/foreign keys.
st.execute("create table pkt1 (i int not null, c char(1) not null)");
st.execute("create table pkt2 (i int not null, c char(1) not null)");
st.execute("create table pkt3 (i int not null, c char(1) not null)");
st.execute("alter table pkt1 add constraint pk1 primary key (i)");
st.execute("alter table pkt2 add constraint pk2 primary key (c)");
st.execute("alter table pkt3 add constraint pk3 primary key (i, c)");
st.execute("create table fkt1 (fi int, fc char(1), vc varchar(80))");
st.execute("create table fkt2 (fi int, fc char(1), vc varchar(80))");
st.execute("alter table fkt1 add constraint fk1 foreign key (fi) " +
"references pkt1(i)");
st.execute("alter table fkt1 add constraint fk2 foreign key (fc) " +
"references pkt2(c)");
st.execute("alter table fkt2 add constraint fk3 foreign key (fi, fc) " +
"references pkt3(i, c)");
/* Check for all arguments NULL; SQLFOREIGNKEYS allows this, though
* there is no equivalent in JDBC.
*/
checkODBCKeys(null, null, null, null, null, null);
/* Run equivalent of getImportedKeys(), getExportedKeys(),
* and getCrossReference for each of the primary/foreign
* key pairs.
*/
checkODBCKeys(null, null, null, null, null, "FKT1");
checkODBCKeys(null, null, "PKT1", null, null, null);
checkODBCKeys(null, null, "PKT1", null, null, "FKT1");
checkODBCKeys(null, null, null, null, null, "FKT2");
checkODBCKeys(null, null, "PKT2", null, null, null);
checkODBCKeys(null, null, "PKT2", null, null, "FKT2");
checkODBCKeys(null, null, null, null, null, "FKT3");
checkODBCKeys(null, null, "PKT3", null, null, null);
checkODBCKeys(null, null, "PKT3", null, null, "FKT3");
// Reverse primary and foreign tables.
checkODBCKeys(null, null, "FKT1", null, null, null);
checkODBCKeys(null, null, null, null, null, "PKT3");
checkODBCKeys(null, null, "FKT1", null, null, "PKT1");
checkODBCKeys(null, null, "FKT2", null, null, "PKT2");
checkODBCKeys(null, null, "FKT3", null, null, "PKT3");
// Mix-and-match primary key tables and foreign key tables.
checkODBCKeys(null, null, "PKT1", null, null, "FKT2");
checkODBCKeys(null, null, "PKT1", null, null, "FKT3");
checkODBCKeys(null, null, "PKT2", null, null, "FKT3");
checkODBCKeys(null, null, "FKT1", null, null, "PKT2");
checkODBCKeys(null, null, "FKT1", null, null, "PKT3");
checkODBCKeys(null, null, "FKT2", null, null, "PKT3");
// Cleanup.
st.execute("drop table fkt1");
st.execute("drop table fkt2");
st.execute("drop table pkt1");
st.execute("drop table pkt2");
st.execute("drop table pkt3");
st.close();
}
/**
* Validates a connection.
* @param validateAction the action used. One of {@link #VALIDATE_BORROW}, {@link #VALIDATE_IDLE},
* {@link #VALIDATE_INIT} or {@link #VALIDATE_RETURN}
* @param sql the SQL to be used during validation. If the {@link PoolConfiguration#setInitSQL(String)} has been called with a non null
* value and the action is {@link #VALIDATE_INIT} the init SQL will be used for validation.
*
* @return true if the connection was validated successfully. It returns true even if validation was not performed, such as when
* {@link PoolConfiguration#setValidationInterval(long)} has been called with a positive value.
* <p>
* false if the validation failed. The caller should close the connection if false is returned since a session could have been left in
* an unknown state during initialization.
*/
public boolean validate(int validateAction,String sql) {
if (this.isDiscarded()) {
return false;
}
if (!doValidate(validateAction)) {
//no validation required, no init sql and props not set
return true;
}
//Don't bother validating if already have recently enough
long now = System.currentTimeMillis();
if (validateAction!=VALIDATE_INIT &&
poolProperties.getValidationInterval() > 0 &&
(now - this.lastValidated) <
poolProperties.getValidationInterval()) {
return true;
}
if (poolProperties.getValidator() != null) {
if (poolProperties.getValidator().validate(connection, validateAction)) {
this.lastValidated = now;
return true;
} else {
if (getPoolProperties().getLogValidationErrors()) {
log.error("Custom validation through "+poolProperties.getValidator()+" failed.");
}
return false;
}
}
String query = sql;
if (validateAction == VALIDATE_INIT && poolProperties.getInitSQL() != null) {
query = poolProperties.getInitSQL();
}
if (query == null) {
query = poolProperties.getValidationQuery();
}
Statement stmt = null;
try {
stmt = connection.createStatement();
int validationQueryTimeout = poolProperties.getValidationQueryTimeout();
if (validationQueryTimeout > 0) {
stmt.setQueryTimeout(validationQueryTimeout);
}
stmt.execute(query);
stmt.close();
this.lastValidated = now;
return true;
} catch (Exception ex) {
if (getPoolProperties().getLogValidationErrors()) {
log.warn("SQL Validation error", ex);
} else if (log.isDebugEnabled()) {
log.debug("Unable to validate object:",ex);
}
if (stmt!=null)
try { stmt.close();} catch (Exception ignore2){/*NOOP*/}
}
return false;
}
public void execUpdate(String update) throws SQLException {
Statement stmt = connection.createStatement();
stmt.execute(update);
stmt.close();
}
@Test
public void testOnFailureHookLoading() throws Exception {
// Do not run this test if run with external HiveServer2
// This test checks for a static member, which will not
// be set if HiveServer2 and the test run in different JVMs
String hiveServer2Type = System.getProperty(
HiveServerFactory.HIVESERVER2_TYPE);
if (hiveServer2Type != null &&
!HiveServerFactory.isInternalServer(HiveServerFactory.HiveServer2Type
.valueOf(hiveServer2Type.trim()))) {
return;
}
context = createContext(testProperties);
File dataDir = context.getDataDir();
//copy data file to test dir
File dataFile = new File(dataDir, SINGLE_TYPE_DATA_FILE_NAME);
FileOutputStream to = new FileOutputStream(dataFile);
Resources.copy(Resources.getResource(SINGLE_TYPE_DATA_FILE_NAME), to);
to.close();
policyFile
.addRolesToGroup(USERGROUP1, "all_db1", "load_data")
.addPermissionsToRole("all_db1", "server=server1->db=DB_1")
.setUserGroupMapping(StaticUserGroup.getStaticMapping())
.write(context.getPolicyFile());
// setup db objects needed by the test
Connection connection = context.createConnection(ADMIN1);
Statement statement = context.createStatement(connection);
statement.execute("DROP DATABASE IF EXISTS DB_1 CASCADE");
statement.execute("DROP DATABASE IF EXISTS DB_2 CASCADE");
statement.execute("CREATE DATABASE DB_1");
statement.execute("CREATE DATABASE DB_2");
statement.close();
connection.close();
// test execution
connection = context.createConnection(USER1_1);
statement = context.createStatement(connection);
//negative test case: user can't drop another user's database
assertFalse(DummySentryOnFailureHook.invoked);
try {
statement.execute("DROP DATABASE DB_2 CASCADE");
Assert.fail("Expected SQL exception");
} catch (SQLException e) {
assertTrue(DummySentryOnFailureHook.invoked);
}
statement.close();
connection.close();
//test cleanup
connection = context.createConnection(ADMIN1);
statement = context.createStatement(connection);
statement.execute("DROP DATABASE DB_1 CASCADE");
statement.execute("DROP DATABASE DB_2 CASCADE");
statement.close();
connection.close();
context.close();
}
/**
* Transactions with indexes.
*/
public void testTransactionsAndIndexMaintenance() throws Exception {
Connection conn= getConnection();
Statement st = conn.createStatement();
st.execute("create schema test");
st.execute("create table test.t1 ( PkCol1 int not null, PkCol2 int not null , " +
"col3 int, col4 int, col5 varchar(10), Primary Key (PkCol1) ) "+
"Partition by column (PkCol1)"+getSuffix());
conn.commit();
st.execute("create index IndexCol4 on test.t1 (col4)");
conn.commit();
conn.setTransactionIsolation(getIsolationLevel());
final int rows = 1000;
final CheckIndexOperations checkIndex = new CheckIndexOperations("Test.IndexCol4");
try {
GemFireXDQueryObserverHolder.putInstance(checkIndex);
PreparedStatement psInsert = conn.prepareStatement("insert into test.t1 "
+ "values (?,10,10,10,'XXXX1')");
for (int i = 0; i < rows; i++) {
psInsert.setInt(1, i);
psInsert.executeUpdate();
conn.commit();
}
checkIndex.checkNumInserts(rows);
} finally {
GemFireXDQueryObserverHolder.removeObserver(checkIndex);
}
//conn.commit();
ResultSet rs = st.executeQuery("select * from test.t1 where col4 = 10");
int numRows = 0;
while (rs.next()) {
assertEquals("Should return correct result ", 10, rs.getInt("COL4"));
numRows++;
}
assertEquals("Should return 1000 rows ", rows, numRows);
rs.close();
PreparedStatement psUpdate = conn.prepareStatement("update test.t1 set " +
"col3 = 20, col4 = 20, col5 = 'changed' where PkCol1=?");
try {
GemFireXDQueryObserverHolder.putInstance(checkIndex);
for(int i = 0; i < rows ; i++) {
psUpdate.setInt(1, i);
psUpdate.executeUpdate();
conn.commit();
}
} finally {
GemFireXDQueryObserverHolder.removeObserver(checkIndex);
}
// TODO fix the observer index maintenance increment logic with the new model.
//checkIndex.checkNumInserts(2 * rows);
//checkIndex.checkNumDeletes(rows);
rs = st.executeQuery("select * from test.t1 where col4 = 20 " +
"order by PkCol1 asc");
numRows = 0;
while (rs.next()) {
assertEquals("Should return correct result ", 20, rs.getInt("COL4"));
assertEquals("Should return correct result ", 20, rs.getInt("COL3"));
assertEquals("Should return correct result ", "changed",
rs.getString("COL5").trim());
assertEquals("Should return correct result ", numRows,
rs.getInt("PKCOL1"));
numRows++;
}
assertEquals("Should return 1000 rows ", rows, numRows);
st.close();
conn.commit();
}
/**
* Test multiple commit and rollback.
*
* @throws Exception
*/
public void testCommitAndRollBack() throws Exception {
startVMs(1, 2);
// TestUtil.loadDriver();
Properties props = new Properties();
props.setProperty(Attribute.TX_SYNC_COMMITS, "true");
final Connection conn = TestUtil.getConnection(props);
conn.setAutoCommit(false);
Statement st = conn.createStatement();
st.execute("create table T1 ( PkCol1 int not null, PkCol2 int not null , "
+ "col3 int, col4 int, col5 varchar(10), Primary Key (PkCol1, PkCol2)) "
+ "Partition by column (PkCol1)"+ getSuffix());
conn.commit();
conn.setTransactionIsolation(getIsolationLevel());
PreparedStatement psInsert = conn.prepareStatement("insert into t1 "
+ "values(?, ?, ?, ?, ?)");
for (int i = 0; i < 1000; i++) {
psInsert.setInt(1, i);
psInsert.setInt(2, i);
psInsert.setInt(3, i);
psInsert.setInt(4, i);
psInsert.setString(5, "XXXX" + i);
psInsert.executeUpdate();
if ((i % 2) == 0) {
conn.commit();
}
else {
conn.rollback();
}
}
// approx. 240 commits/rollbacks gets distributed across 2 nodes. adjust these numbers to a little lower value
// if unbalanced commits/rollbacks happen.
checkTxStatistics("commit-afterInserts", 240, 240, 240, 240, 500, 501);
ResultSet rs = st.executeQuery("select * from t1");
int numRows = 0;
while (rs.next()) {
numRows++;
}
assertEquals("Table should have 500 rows", 500, numRows);
rs.close();
st.close();
conn.commit();
conn.close();
checkTxStatistics("commit-afterSelects", 240, 240, 240, 240, 500, 501);
}
/** Add an user to the DB.
* @return String */
public String addUser() {
HttpSession session = ServletActionContext.getRequest().getSession();
User us = new User();
// Save fields
us.setUsername(this.usernameForm);
us.setPassword(this.passwordForm);
us.setRepeatPassword(this.repeatPasswordForm);
us.setEmail(this.emailForm);
us.setRole(String.valueOf(this.roleForm));
us.setType(String.valueOf(this.typeForm));
us.setOrganisation(String.valueOf(this.organisationForm));
session.setAttribute("newUser", us);
Connection connection = null;
try {
connection = new DBConnectionManager().getConnection();
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM aliada.user WHERE user_name='" + getUsernameForm() + "'");
if (!rs.next()) {
statement = connection.createStatement();
if (this.passwordForm.equals(this.repeatPasswordForm)) {
// Encrypt password
StrongPasswordEncryptor passwordEncryptor = new StrongPasswordEncryptor();
String encryptedPassword = passwordEncryptor.encryptPassword(this.passwordForm);
statement.executeUpdate("INSERT INTO aliada.user VALUES ('" + this.usernameForm + "', '"
+ encryptedPassword + "', '" + this.emailForm + "', '" + this.typeForm + "', '" + this.roleForm + "', '" + this.organisationForm + "')");
addActionMessage(getText("user.save.ok"));
rs.close();
statement.close();
connection.close();
getUsersDb();
session.setAttribute("newUser", null);
} else {
rs.close();
statement.close();
connection.close();
addFieldError("passwordForm", getText("userPassword.not.equals"));
addFieldError("repeatPasswordForm", getText("userPassword.not.equals"));
return ERROR;
}
} else {
rs.close();
statement.close();
connection.close();
addFieldError("usernameForm", getText("err.duplicate.user"));
return ERROR;
}
} catch (SQLException e) {
logger.error(MessageCatalog._00011_SQL_EXCEPTION, e);
return ERROR;
}
return SUCCESS;
}