下面列出了java.sql.Connection#commit ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
@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));
}
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();
}
}
@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());
}
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());
}
/**
* 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;
}
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();
}
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);
}
}
@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();
}
}
}
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);
}
}
public static void commitAndRelease() throws SQLException {
Connection con = getCurrentConnection();
if (con != null) {
con.commit(); // 事务提交
con.close();// 关闭资源
tl.remove();// 从线程绑定中移除
}
}
@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();
}
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();
}
}
/**
* 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();
}
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));
}
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();
}
@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());
}
/**
* 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");
}
}
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;
}
@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();
}
}
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.");
}