Java Code Examples for java.sql.Connection#commit()

The following examples show how to use java.sql.Connection#commit() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: phoenix   File: Array2IT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayConstructorWithMultipleRows4() throws Exception {

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String table = generateUniqueName();
    String ddl = "CREATE TABLE  " + table + "  (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
    conn.createStatement().execute(ddl);
    conn.commit();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('a', 'foo', 'abc')");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('b', 'abc', 'dfg')");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('c', 'foo', 'abc')");
    stmt.execute();
    conn.commit();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT COUNT(DISTINCT ARRAY[a,b]) from  " + table);
    assertTrue(rs.next());
    assertEquals(2, rs.getInt(1));
}
 
Example 2
Source Project: gemfirexd-oss   File: BOPurgeProc.java    License: Apache License 2.0 6 votes vote down vote up
private static void deleteBORawData(Connection pCtx) throws SQLException {
  int currentBatchSize = 0;
  ResultSet result = getExpiredBOIds(pCtx);
  PreparedStatement deleteStmt=pCtx.prepareStatement(DELETE_BO_RAW_DATA);
  while (result.next()) {
    deleteStmt.setString(1, result.getString("BO_TXN_ID"));
    deleteStmt.addBatch();
    currentBatchSize++;
    if (currentBatchSize > maxBatchSize) {
      deleteStmt.executeBatch();
      pCtx.commit();
    }
    deleteStmt.executeBatch();
    pCtx.commit();
  }
}
 
Example 3
Source Project: phoenix   File: DecodeFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void shouldPass() throws Exception {
	Connection conn = DriverManager.getConnection(getUrl());

	String testTable = generateUniqueName();
	String ddl = "CREATE TABLE " + testTable
			+ " ( some_column BINARY(12) NOT NULL CONSTRAINT PK PRIMARY KEY (some_column))";

	conn.createStatement().execute(ddl);
	PreparedStatement ps = conn.prepareStatement(
			"UPSERT INTO " + testTable + " (some_column) VALUES (?)");

	byte[] kk = Bytes.add(PUnsignedLong.INSTANCE.toBytes(2232594215l), PInteger.INSTANCE.toBytes(-8));
	ps.setBytes(1, kk);

	ps.execute();
	conn.commit();

	ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + testTable
			+ " WHERE some_column = DECODE('000000008512af277ffffff8', 'hex')");
	assertTrue(rs.next());
}
 
Example 4
Source Project: gemfirexd-oss   File: TransactionDUnit.java    License: Apache License 2.0 6 votes vote down vote up
public void testBug42067_2() throws Exception {

    // Create the controller VM as client which belongs to default server group
    startClientVMs(1, 0, null);
    startServerVMs(2, -1, "SG1");
    Connection conn = TestUtil.jdbcConn;
    conn.setTransactionIsolation(getIsolationLevel());
    conn.setAutoCommit(false);
    // create table
    clientSQLExecute(1, "Create table t1 (c1 int not null primary key, "
        + "c2 int not null, c3 int not null, c4 int not null) "
        + "redundancy 1 partition by column (c1) "+ getSuffix());
    conn.commit();
    Statement st = conn.createStatement();
    st.execute("insert into t1 values (1, 1,1,1)");
    st.execute("insert into t1 values (114, 114,114,114)");
    conn.commit();
    st.execute("delete from t1 where c1 =1 and c3 =1");
    st.execute("update t1 set c2 =2 where c1 =1 and c3 =1");
    conn.commit();
    ResultSet rs = st.executeQuery("select * from t1");
    assertTrue(rs.next());
    assertEquals(114, rs.getInt(1));
    assertFalse(rs.next());
  }
 
Example 5
Source Project: DKO   File: Context.java    License: GNU Lesser General Public License v2.1 6 votes vote down vote up
/**
 * Commits the current transaction.
 * @param ds
 * @return
 * @throws SQLException
 */
public boolean commitTransaction(final DataSource ds) throws SQLException {
	final Connection c = transactionConnections.remove(ds);
	if (c == null) return false;
	if (Constants.DB_TYPE.detect(ds)==Constants.DB_TYPE.SQLITE3) {
		Statement stmt = c.createStatement();
		try {
			String sql = "commit";
			Util.log(sql, null);
			stmt.execute(sql);
		} finally {
			stmt.close();
		}
	} else {
		Util.log("connection.commit()", null);
		c.commit();
	}
	c.close();
	return true;
}
 
Example 6
public void testJSON_TX() throws Exception {
  startVMs(1, 3, 0, null, null);
  Connection cxn = TestUtil.getConnection();
  cxn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  cxn.setAutoCommit(false);
  boolean isOffHeap = (getSuffix() != "");
  JsonTest.simpleJSONOps(cxn, true, isOffHeap);
  cxn.commit();
}
 
Example 7
Source Project: carbon-identity   File: TokenMgtDAO.java    License: Apache License 2.0 5 votes vote down vote up
public String getAuthzCodeByCodeId(String codeId) throws IdentityOAuth2Exception {

        Connection connection = IdentityDatabaseUtil.getDBConnection();

        PreparedStatement prepStmt = null;
        ResultSet resultSet = null;
        try {
            String sql = SQLQueries.RETRIEVE_AUTHZ_CODE_BY_CODE_ID;

            prepStmt = connection.prepareStatement(sql);
            prepStmt.setString(1, codeId);
            resultSet = prepStmt.executeQuery();

            if (resultSet.next()) {
                return resultSet.getString("AUTHORIZATION_CODE");
            }
            connection.commit();
            return null;

        } catch (SQLException e) {
            String errorMsg = "Error occurred while retrieving 'Authorization Code' for " +
                    "authorization code : " + codeId;
            throw new IdentityOAuth2Exception(errorMsg, e);
        } finally {
            IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
        }

    }
 
Example 8
Source Project: mybaties   File: CglibNPELazyTest.java    License: Apache License 2.0 5 votes vote down vote up
@BeforeClass
public static void initDatabase() throws Exception {
  Connection conn = null;

  try {
    Class.forName("org.hsqldb.jdbcDriver");
    conn = DriverManager.getConnection("jdbc:hsqldb:mem:cglib_lazy_error", "sa",
        "");

    Reader reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/cglib_lazy_error/CreateDB.sql");

    ScriptRunner runner = new ScriptRunner(conn);
    runner.setLogWriter(null);
    runner.setErrorLogWriter(null);
    runner.runScript(reader);
    conn.commit();
    reader.close();

    reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/cglib_lazy_error/ibatisConfigLazy.xml");
    sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    sqlSessionFactory.getConfiguration().setLazyLoadingEnabled(true);
    sqlSessionFactory.getConfiguration().setAggressiveLazyLoading(false);
    reader.close();
  } finally {
    if (conn != null) {
      conn.close();
    }
  }
}
 
Example 9
Source Project: carbon-identity   File: TokenMgtDAO.java    License: Apache License 2.0 5 votes vote down vote up
public void persistAuthorizationCode(String authzCode, String consumerKey, String callbackUrl,
                                     AuthzCodeDO authzCodeDO) throws IdentityOAuth2Exception {

    if (!enablePersist) {
        return;
    }

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    try {
        prepStmt = connection.prepareStatement(SQLQueries.STORE_AUTHORIZATION_CODE);
        prepStmt.setString(1, authzCodeDO.getAuthzCodeId());
        prepStmt.setString(2, persistenceProcessor.getProcessedAuthzCode(authzCode));
        prepStmt.setString(3, callbackUrl);
        prepStmt.setString(4, OAuth2Util.buildScopeString(authzCodeDO.getScope()));
        prepStmt.setString(5, authzCodeDO.getAuthorizedUser().getUserName());
        prepStmt.setString(6, authzCodeDO.getAuthorizedUser().getUserStoreDomain());
        int tenantId = OAuth2Util.getTenantId(authzCodeDO.getAuthorizedUser().getTenantDomain());
        prepStmt.setInt(7, tenantId);
        prepStmt.setTimestamp(8, authzCodeDO.getIssuedTime(),
                              Calendar.getInstance(TimeZone.getTimeZone(UTC)));
        prepStmt.setLong(9, authzCodeDO.getValidityPeriod());
        prepStmt.setString(10, authzCodeDO.getAuthorizedUser().getAuthenticatedSubjectIdentifier());
        prepStmt.setString(11, persistenceProcessor.getProcessedClientId(consumerKey));
        prepStmt.execute();
        connection.commit();
    } catch (SQLException e) {
        throw new IdentityOAuth2Exception("Error when storing the authorization code for consumer key : " +
                consumerKey, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
}
 
Example 10
public static void commitAndRelease() throws SQLException {
	Connection con = getCurrentConnection();
	if (con != null) {
		con.commit(); // 事务提交
		con.close();// 关闭资源
		tl.remove();// 从线程绑定中移除
	}
}
 
Example 11
@BeforeClass
public static void createTable() throws SQLException, ClassNotFoundException {
    Class.forName("org.hsqldb.jdbcDriver");

    final Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
    final Statement statement = connection.createStatement();
    statement.execute("CREATE TABLE " + TABLE + "(ID INTEGER)");
    statement.close();
    connection.commit();
    connection.close();
}
 
Example 12
Source Project: gemfirexd-oss   File: TransactionTest.java    License: Apache License 2.0 4 votes vote down vote up
public void testMultipleInsertFromThinClient_bug44242() throws Exception {
  setupConnection();
  int port = startNetserverAndReturnPort("create schema emp");
  for (int i = 0; i < 2; i++) {
    Connection netConn1 = TestUtil.getNetConnection(port, null, null);
    
    Connection netConn2 = TestUtil.getNetConnection(port, null, null);

    Statement s = netConn1.createStatement();
    String ext = "";
    if (i == 1) {
      ext = "replicate";
    }
    s.execute("create table emp.EMPLOYEE_parent(lastname varchar(30) "
        + "primary key, depId int)" + ext +getSuffix());
    s.execute("create table emp.EMPLOYEE(lastname varchar(30) primary key, "
        + "depId int, foreign key(lastname) references "
        + "emp.EMPLOYEE_parent(lastname) on delete restrict)" + ext+getSuffix());
    s.execute("insert into emp.EMPLOYEE_parent values('Jones', 10), "
        + "('Rafferty', 50), ('Robinson', 100)");

    netConn2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    netConn2.setAutoCommit(false);
    Statement s2 = netConn2.createStatement();
    s2.execute("delete from emp.EMPLOYEE_parent");
    s2.execute("select * from emp.employee_parent");
    netConn1.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    netConn1.setAutoCommit(false);
    PreparedStatement pstmnt = netConn1
        .prepareStatement("INSERT INTO emp.employee VALUES (?, ?)");

    pstmnt.setString(1, "Jones");
    pstmnt.setInt(2, 33);
    pstmnt.addBatch();

    pstmnt.setString(1, "Rafferty");
    pstmnt.setInt(2, 31);
    pstmnt.addBatch();

    pstmnt.setString(1, "Robinson");
    pstmnt.setInt(2, 34);
    pstmnt.addBatch();

    try {
      pstmnt.executeBatch();
      netConn1.commit();
      fail("commit should have failed");
    } catch (SQLException e) {
      assertEquals("X0Z02", e.getSQLState());
    }
    netConn2.commit();

    s.execute("drop table emp.employee");
    this.waitTillAllClear();
    s.execute("drop table emp.employee_parent");
    this.waitTillAllClear();
  }
}
 
Example 13
/**
 * Test grant statements before, during, and after a rolled-back
 * transaction
 */
public void testGrantRollbackAndCommit() throws SQLException {

	// NOTE: grantrevoke.java originally used S2.F2 for the function
	// below, but the signature on the function didn't match the
	// declaration, so was not used properly. Have substituted
	// function S1.F1 here to get the testcase to pass.
	
	// NOTE 2: executing the grant statements on the owner connection
	//         leads to a lock timeout when asserting any privilege?
	
	Connection oc = openUserConnection(users[0]);
	oc.setAutoCommit(false);
	
	// set up some privileges and check them
	grant(oc, "select", "s2", "t2", "public");
	oc.commit();

    assertSelectPrivilege(true, users[3], "S2", "T2", null);
    assertUpdatePrivilege(false, users[3], "S2", "T2", null);
    assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
    assertDeletePrivilege(false, users[1], "S2", "T3");
    assertTriggerPrivilege(false, users[2], "S2", "T2");
    assertFunctionPrivilege(false, users[1], "S1", "F1", false);
	
	// alter some privileges, assert that they were granted.
    grant(oc, "select(c2),delete", "s2", "t3", users[1]);
    grant(oc, "trigger", "s2", "t2", "public");
	grant(oc, "execute", "function s1", "f1", users[1]);
    // the following asserts fail due to lock timeout
	//assertSelectPrivilege(true, users[1], "s2", "t3", new String[] {"C2"});
    //assertDeletePrivilege(true, users[1], "s2", "t3");
    //assertTriggerPrivilege(true, users[2], "S2", "T2");
    //assertFunctionPrivilege(true, users[1], "S1", "F1", false);
    
    // roll it back and assert the privileges were not granted.
    oc.rollback();
    assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
    assertDeletePrivilege(false, users[1], "S2", "T3");
    assertTriggerPrivilege(false, users[2], "S2", "T2");
    assertFunctionPrivilege(false, users[1], "S1", "F1", false);
    
    // do it again... 
    grant(oc, "select(c2),delete", "s2", "t3", users[1]);
    grant(oc, "trigger", "s2", "t2", "public");
	grant(oc, "execute", "function s1", "f1", users[1]);
    // the following asserts fail due to lock timeout
	//assertSelectPrivilege(true, users[1], "S2", "T3", new String[] {"C2"});
    //assertDeletePrivilege(true, users[1], "S2", "T3");
    //assertTriggerPrivilege(true, users[2], "S2", "T2");
    //assertFunctionPrivilege(true, users[1], "S1", "F1", false);
    
    // commit and ensure the permissions are correct
    oc.commit();
    assertSelectPrivilege(true, users[1], "S2", "T3", new String[] {"C2"});
    assertDeletePrivilege(true, users[1], "S2", "T3");
    assertTriggerPrivilege( true, users[2], "S2", "T2");
    assertFunctionPrivilege( true, users[1], "S1", "F1", false);
   
	// remove any permissions we granted
	revoke(oc, "select", "s2", "t2", "public");
	revoke(oc, "select(c2),delete", "s2", "t3", users[1]);
    revoke(oc, "trigger", "s2", "t2", "public");
	revoke(oc, "execute", "function s1", "f1", users[1]);
	oc.commit();
	oc.setAutoCommit(false);
    assertSelectPrivilege(false, users[3], "S2", "T2", null);
    assertUpdatePrivilege(false, users[3], "S2", "T2", null);
    assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
    assertDeletePrivilege(false, users[1], "S2", "T3");
    assertTriggerPrivilege(false, users[2], "S2", "T2");
    assertFunctionPrivilege(false, users[1], "S1", "F1", false);
	
	oc.close();
	
}
 
Example 14
public void testBatchInsert_FkOnPkViolation() throws Exception {
  Properties props = new Properties();
  System.clearProperty(GfxdConstants.GFXD_ENABLE_BULK_FK_CHECKS);
  props.setProperty(Attribute.ENABLE_BULK_FK_CHECKS, "true");
  startVMs(1, 3, 0, null, props);
  Connection conn = TestUtil.getConnection(props);
  Statement st = conn.createStatement();
  conn.setAutoCommit(false);
  conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

  // create tables
  st.execute("create table parent (col1 int, col2 int, col3 int not null, "
      + "constraint pk1 primary key (col1)) partition by list "
      + "(col3) (VALUES (1), VALUES (2), VALUES (3), values (4))");

  st.execute("create table child (col1 int, col2 int, col3 int not null, "
      + "constraint pk2 primary key (col1), constraint fk1 foreign key "
      + "(col2) references parent (col1)) partition by list "
      + "(col3) (VALUES (1), VALUES (2), VALUES (3), values (4))");

  st.execute("insert into parent values (1, 1, 1), (2, 2, 2), "
      + "(3, 3, 3), (4, 4, 4)");
  conn.commit();
  PreparedStatement pstmt = conn.prepareStatement("insert into child "
      + "values (?, ?, ?)");

  for (int i = 1; i <= 3; i++) {
    pstmt.setInt(1, i);
    pstmt.setInt(2, i);
    pstmt.setInt(3, i);
    pstmt.addBatch();
  }
  
  // this row to cause an FK violation 
  pstmt.setInt(1, 4);
  pstmt.setInt(2, 100); // FK violation
  pstmt.setInt(3, 4);
  pstmt.addBatch();
  // one more row with no error
  pstmt.setInt(1, 5);
  pstmt.setInt(2, 3);
  pstmt.setInt(3, 4);
  pstmt.addBatch();
  
  try {
    int[] ret = pstmt.executeBatch();
    fail("This statement should have failed due to FK violation");
  } catch (java.sql.BatchUpdateException be) {
    assertEquals("23503", be.getSQLState());
  }
  
  // no rows should be inserted
  ResultSet rs = st.executeQuery("select count(*) from child");
  assertTrue(rs.next());
  assertEquals(0, rs.getInt(1));
}
 
Example 15
public void testUserFunctions() throws SQLException
{
    // use valid user/pwd to set the full accessusers.
    Connection conn1 = openDefaultConnection(
        "dan", ("dan" + PASSWORD_SUFFIX));
    setDatabaseProperty(
        "gemfirexd.authz-full-access-users", 
        "francois,jeff,ames,jerry,jamie,dan,system", conn1);
    setDatabaseProperty(
        "gemfirexd.authz-default-connection-mode","NoAccess", conn1);
    conn1.commit();

    // we should still be connected as dan
    Statement stmt = conn1.createStatement();
    assertUpdateCount(stmt, 0, 
        "create table APP.t1(c1 varchar(30) check (UPPER(c1) <> 'JAMIE'))");
    assertUpdateCount(stmt, 1, "insert into APP.t1 values USER");
  
    conn1.commit();
    stmt.close();
    conn1.close();

    useUserValue(1, "jeff", "insert into APP.t1 values CURRENT_USER");
    useUserValue(1, "ames", "insert into APP.t1 values SESSION_USER");
    useUserValue(1, "jerry", "insert into APP.t1 values {fn user()}");
    assertUserValue(new String[] {"DAN","JEFF","AMES","JERRY"},
        "dan", "select * from APP.t1");
    // attempt some usage in where clause
    useUserValue(1,
        "dan", "update APP.t1 set c1 = 'edward' where c1 = USER");
    assertUserValue(new String[] {"JEFF"},"jeff",
        "select * from APP.t1 where c1 like CURRENT_USER");
    useUserValue(1, "ames", 
        "update APP.t1 set c1 = 'sema' where SESSION_USER = c1");
    useUserValue(1, "jerry", 
        "update APP.t1 set c1 = 'yrrej' where c1 like {fn user()}");
    assertUserValue(new String[] {"edward","JEFF","sema","yrrej"},
        "dan", "select * from APP.t1");
    useUserValue(4, "francois", "update APP.T1 set c1 = USER");
    assertUserValue(
        new String[] {"FRANCOIS","FRANCOIS","FRANCOIS","FRANCOIS"},
        "dan", "select * from APP.t1");

    // check that attempt to insert 'jamie' gives a check violation
    conn1 = openDefaultConnection("jamie", ("jamie" + PASSWORD_SUFFIX));
    stmt = conn1.createStatement();
    try {
        stmt.execute("insert into APP.t1 values CURRENT_USER");
    } catch (SQLException sqle) {
        assertSQLState("23513", sqle);
    }
    stmt.close();
    conn1.rollback();
    conn1.close();

    // Note: there is not much point in attempting to write with an invalid
    // user, that's already tested in the testConnectionShutdown fixture

    // reset
    conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX));
    setDatabaseProperty(
        "gemfirexd.authz-default-connection-mode","fullAccess", conn1);
    setDatabaseProperty(
        "gemfirexd.authentication.required","false", conn1);
    stmt = conn1.createStatement();
    assertUpdateCount(stmt, 0, "drop table APP.t1");
    conn1.commit();
    stmt.close();
    conn1.close();
}
 
Example 16
Source Project: phoenix   File: AlterTableIT.java    License: Apache License 2.0 4 votes vote down vote up
@Test
public void testDropCoveredColumn() throws Exception {
    ResultSet rs;
    PreparedStatement stmt;

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);

    // make sure that the tables are empty, but reachable
    conn.createStatement().execute(
      "CREATE TABLE " + DATA_TABLE_FULL_NAME
          + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 VARCHAR)");
    String dataTableQuery = "SELECT * FROM " + DATA_TABLE_FULL_NAME;
    rs = conn.createStatement().executeQuery(dataTableQuery);
    assertFalse(rs.next());

    conn.createStatement().execute(
      "CREATE INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)");
    conn.createStatement().execute(
        "CREATE LOCAL INDEX " + LOCAL_INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)");
    rs = conn.createStatement().executeQuery(dataTableQuery);
    assertFalse(rs.next());
    String indexTableQuery = "SELECT * FROM " + INDEX_TABLE_NAME;
    rs = conn.createStatement().executeQuery(indexTableQuery);
    assertFalse(rs.next());
    String localIndexTableQuery = "SELECT * FROM " + LOCAL_INDEX_TABLE_FULL_NAME;
    rs = conn.createStatement().executeQuery(localIndexTableQuery);
    assertFalse(rs.next());

    // load some data into the table
    stmt = conn.prepareStatement("UPSERT INTO " + DATA_TABLE_FULL_NAME + " VALUES(?,?,?,?)");
    stmt.setString(1, "a");
    stmt.setString(2, "x");
    stmt.setString(3, "1");
    stmt.setString(4, "j");
    stmt.execute();
    conn.commit();

    assertIndexExists(conn,true);
    conn.createStatement().execute("ALTER TABLE " + DATA_TABLE_FULL_NAME + " DROP COLUMN v2");
    assertIndexExists(conn,true);

    // verify data table rows
    rs = conn.createStatement().executeQuery(dataTableQuery);
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    assertEquals("x",rs.getString(2));
    assertEquals("j",rs.getString(3));
    assertFalse(rs.next());
    
    // verify index table rows
    rs = conn.createStatement().executeQuery(indexTableQuery);
    assertTrue(rs.next());
    assertEquals("x",rs.getString(1));
    assertEquals("a",rs.getString(2));
    assertEquals("j",rs.getString(3));
    assertFalse(rs.next());
    
    // verify local index table rows
    rs = conn.createStatement().executeQuery(localIndexTableQuery);
    assertTrue(rs.next());
    assertEquals("x",rs.getString(1));
    assertEquals("a",rs.getString(2));
    assertEquals("j",rs.getString(3));
    assertFalse(rs.next());

    // load some data into the table
    stmt = conn.prepareStatement("UPSERT INTO " + DATA_TABLE_FULL_NAME + " VALUES(?,?,?)");
    stmt.setString(1, "a");
    stmt.setString(2, "y");
    stmt.setString(3, "k");
    stmt.execute();
    conn.commit();

    // verify data table rows
    rs = conn.createStatement().executeQuery(dataTableQuery);
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    assertEquals("y",rs.getString(2));
    assertEquals("k",rs.getString(3));
    assertFalse(rs.next());
    
    // verify index table rows
    rs = conn.createStatement().executeQuery(indexTableQuery);
    assertTrue(rs.next());
    assertEquals("y",rs.getString(1));
    assertEquals("a",rs.getString(2));
    assertEquals("k",rs.getString(3));
    assertFalse(rs.next());
    
    // verify local index table rows
    rs = conn.createStatement().executeQuery(localIndexTableQuery);
    assertTrue(rs.next());
    assertEquals("y",rs.getString(1));
    assertEquals("a",rs.getString(2));
    assertEquals("k",rs.getString(3));
    assertFalse(rs.next());
}
 
Example 17
Source Project: RipplePower   File: BlockStoreDataBase.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * Initialize the tables
 *
 * @throws BlockStoreException
 *             Unable to initialize the database tables
 */
private void initTables() throws BlockStoreException {
	Connection conn = getConnection();
	try {
		conn.setAutoCommit(false);
		//
		// Initialize the block chain with the genesis block
		//
		Block genesisBlock = new Block(BTCLoader.GENESIS_BLOCK_BYTES, 0, BTCLoader.GENESIS_BLOCK_BYTES.length,
				false);
		chainHead = genesisBlock.getHash();
		prevChainHead = Sha256Hash.ZERO_HASH;
		chainHeight = 0;
		chainWork = BigInteger.ONE;
		targetDifficulty = NetParams.MAX_TARGET_DIFFICULTY;
		blockFileNumber = 0;
		chainTime = genesisBlock.getTimeStamp();
		//
		// Initialize the Settings table
		//
		try (PreparedStatement s = conn
				.prepareStatement("INSERT INTO Settings (schema_name,schema_version) VALUES(?,?)")) {
			s.setString(1, schemaName);
			s.setInt(2, schemaVersion);
			s.executeUpdate();
		}
		//
		// Add the genesis block to the Blocks table
		//
		try (PreparedStatement s = conn
				.prepareStatement("INSERT INTO Blocks(block_hash_index,block_hash,prev_hash_index,prev_hash,"
						+ "block_height,timestamp,chain_work,on_hold,"
						+ "file_number,file_offset,header) VALUES(?,?,?,?,0,?,?,false,0,0,?)")) {
			s.setLong(1, getHashIndex(chainHead));
			s.setBytes(2, chainHead.getBytes());
			s.setLong(3, getHashIndex(prevChainHead));
			s.setBytes(4, prevChainHead.getBytes());
			s.setLong(5, chainTime);
			s.setBytes(6, chainWork.toByteArray());
			s.setBytes(7, genesisBlock.getHeaderBytes());
			s.executeUpdate();
		}
		//
		// Copy the genesis block as the initial block file
		//
		File blockFile = new File(String.format("%s%sBlocks%sblk00000.dat", dataPath, LSystem.FS, LSystem.FS));
		if (!existsBlock) {
			existsBlock = blockFile.exists();
			if (!existsBlock) {
				FileUtils.makedirs(blockFile);
			}
		}
		try (FileOutputStream outFile = new FileOutputStream(blockFile)) {
			byte[] prefixBytes = new byte[8];
			Helper.uint32ToByteArrayLE(NetParams.MAGIC_NUMBER, prefixBytes, 0);
			Helper.uint32ToByteArrayLE(BTCLoader.GENESIS_BLOCK_BYTES.length, prefixBytes, 4);
			outFile.write(prefixBytes);
			outFile.write(BTCLoader.GENESIS_BLOCK_BYTES);
		}
		//
		// All done - commit the updates
		//
		conn.commit();
		conn.setAutoCommit(true);
		BTCLoader.info(String.format("Database initialized with schema version %d.%d", schemaVersion / 100,
				schemaVersion % 100));
	} catch (IOException | SQLException | VerificationException exc) {
		BTCLoader.error("Unable to initialize the database tables", exc);
		rollback();
		throw new BlockStoreException("Unable to initialize the database tables");
	}
}
 
Example 18
private boolean insertItems(Connection con, Collection<Item> items, Integer playerId, Integer accountId, Integer legionId) {

		if (GenericValidator.isBlankOrNull(items)) {
			return true;
		}

		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(INSERT_QUERY);

			for (Item item : items) {
				stmt.setInt(1, item.getObjectId());
				stmt.setInt(2, item.getItemTemplate().getTemplateId());
				stmt.setLong(3, item.getItemCount());
				stmt.setInt(4, item.getItemColor());
				stmt.setInt(5, item.getColorExpireTime());
				stmt.setString(6, item.getItemCreator());
				stmt.setInt(7, item.getExpireTime());
				stmt.setInt(8, item.getActivationCount());
				stmt.setInt(9, getItemOwnerId(item, playerId, accountId, legionId));
				stmt.setBoolean(10, item.isEquipped());
				stmt.setInt(11, item.isSoulBound() ? 1 : 0);
				stmt.setLong(12, item.getEquipmentSlot());
				stmt.setInt(13, item.getItemLocation());
				stmt.setInt(14, item.getItemTemplate().getMaxAuthorize() > 0 ? 0 : item.getEnchantOrAuthorizeLevel());
				stmt.setInt(15, item.getItemSkinTemplate().getTemplateId());
				stmt.setInt(16, item.getFusionedItemId());
				stmt.setInt(17, item.getOptionalSocket());
				stmt.setInt(18, item.getOptionalFusionSocket());
				stmt.setInt(19, item.getChargePoints());
				stmt.setInt(20, item.getBonusNumber());
				stmt.setInt(21, item.getRandomCount());
				stmt.setInt(22, item.getPackCount());
				stmt.setInt(23, item.getItemTemplate().getMaxAuthorize() > 0 ? item.getEnchantOrAuthorizeLevel() : 0);
				stmt.setBoolean(24, item.isPacked());
				stmt.setBoolean(25, item.isAmplified());
				stmt.setInt(26, item.getAmplificationSkill());
				stmt.setInt(27, item.getReductionLevel());
				stmt.setBoolean(28, item.isLunaReskin());
				stmt.setBoolean(29, item.isEnhance());
				stmt.setInt(30, item.getEnhanceSkillId());
				stmt.setInt(31, item.getEnhanceEnchantLevel());
				stmt.setInt(32, item.getUnSeal());
				stmt.setInt(33, item.getItemSkinSkill());
				stmt.addBatch();
			}

			stmt.executeBatch();
			con.commit();
		}
		catch (Exception e) {
			log.error("Failed to execute insert batch", e);
			return false;
		}
		finally {
			DatabaseFactory.close(stmt);
		}
		return true;
	}
 
Example 19
Source Project: phoenix   File: SkipScanQueryIT.java    License: Apache License 2.0 4 votes vote down vote up
@Test
public void testSkipScanFilterWhenTableHasMultipleColumnFamilies() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);
    try {
        createMultiCFTestTable(TestUtil.DEFAULT_DATA_TABLE_FULL_NAME);
        populateMultiCFTestTable(TestUtil.DEFAULT_DATA_TABLE_FULL_NAME);
        String upsert = "UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME
                + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = conn.prepareStatement(upsert);
        stmt.setString(1, "varchar4");
        stmt.setString(2, "char1");
        stmt.setInt(3, 1);
        stmt.setLong(4, 1L);
        stmt.setBigDecimal(5, new BigDecimal("1.1"));
        stmt.setString(6, "varchar_a");
        stmt.setString(7, "chara");
        stmt.setInt(8, 2);
        stmt.setLong(9, 2L);
        stmt.setBigDecimal(10, new BigDecimal("2.1"));
        stmt.setString(11, "varchar_b");
        stmt.setString(12, "charb");
        stmt.setInt(13, 3);
        stmt.setLong(14, 3L);
        stmt.setBigDecimal(15, new BigDecimal("3.1"));
        stmt.setDate(16, null);
        stmt.executeUpdate();
        
        stmt.setString(1, "varchar5");
        stmt.setString(2, "char2");
        stmt.setInt(3, 2);
        stmt.setLong(4, 2L);
        stmt.setBigDecimal(5, new BigDecimal("2.2"));
        stmt.setString(6, "varchar_a");
        stmt.setString(7, "chara");
        stmt.setInt(8, 3);
        stmt.setLong(9, 3L);
        stmt.setBigDecimal(10, new BigDecimal("3.2"));
        stmt.setString(11, "varchar_b");
        stmt.setString(12, "charb");
        stmt.setInt(13, 4);
        stmt.setLong(14, 4L);
        stmt.setBigDecimal(15, new BigDecimal("4.2"));
        stmt.setDate(16, null);
        stmt.executeUpdate();
        
        stmt.setString(1, "varchar6");
        stmt.setString(2, "char3");
        stmt.setInt(3, 3);
        stmt.setLong(4, 3L);
        stmt.setBigDecimal(5, new BigDecimal("3.3"));
        stmt.setString(6, "varchar_a");
        stmt.setString(7, "chara");
        stmt.setInt(8, 4);
        stmt.setLong(9, 4L);
        stmt.setBigDecimal(10, new BigDecimal("4.3"));
        stmt.setString(11, "varchar_b");
        stmt.setString(12, "charb");
        stmt.setInt(13, 5);
        stmt.setLong(14, 5L);
        stmt.setBigDecimal(15, new BigDecimal("5.3"));
        stmt.setDate(16, null);
        stmt.executeUpdate();
        conn.commit();
        String query = "SELECT char_col1, int_col1, long_col2 from " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " where varchar_pk in ('varchar3','varchar6')";
        ResultSet rs = conn.createStatement().executeQuery(query);
        assertTrue(rs.next());
        assertEquals("chara", rs.getString(1));
        assertEquals(4, rs.getInt(2));
        assertEquals(5L, rs.getLong(3));
        assertTrue(rs.next());
        assertEquals("chara", rs.getString(1));
        assertEquals(4, rs.getInt(2));
        assertEquals(5L, rs.getLong(3));
        assertFalse(rs.next());
        
    } finally {
        conn.close();
    }
}
 
Example 20
public void testConcConnectionsTwo() throws Exception {
  System.out.println("Conc Connections Test Two starting");

  //Open 1st  connection
  Connection conn1 = getConnection();
  Connection conn4 = getConnection();
  Connection conn5 = getConnection();
  Connection conn6 = getConnection();
  Connection conn7 = getConnection();
  if (conn1 == null
      || conn4 == null
      || conn5 == null
      || conn6 == null
      || conn7 == null
  ) {
    fail("Concurrent connections test two - Failed to open a connection");
  }
  System.out.println("Conc Connections Test Two opened 5 connections");
  Connection conn8 = getConnection();
  Connection conn9 = getConnection();
  Connection conn10 = getConnection();
  metadataCalls(conn1);

  Connection conn2= getConnection();

  metadataCalls(conn2);

  Connection conn3 = getConnection();
  metadataCalls(conn3);
  if (conn2 == null
      || conn3 == null
      || conn8 == null
      || conn9 == null
      || conn10 == null
  ) {
    fail("Concurrent connections test two - Failed to open a connection");
  }
  System.out.println("Conc Connections Test Two opened 10 connections");

  conn1.commit();
  conn2.commit();
  conn3.commit();
  conn4.commit();
  conn5.commit();
  conn6.commit();
  conn7.commit();
  conn8.commit();
  conn9.commit();
  conn10.commit();
  System.out.println("Conc Connections Test Two committed  10 connections");

  System.out.println("Conc Connections Test Two finishes.");
}