下面列出了java.sql.Connection#setAutoCommit ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
/**
* Updates a page record in the database.
*
* @param page The page to update
*/
@Override
public void updatePage(IPage page) {
Connection conn = null;
try {
conn = this.getConnection();
conn.setAutoCommit(false);
String pageCode = page.getCode();
this.deleteDraftWidgets(pageCode, conn);
this.deleteDraftPageMetadata(pageCode, conn);
this.updatePageRecord(page, conn);
PageMetadata metadata = page.getMetadata();
metadata.setUpdatedAt(new Date());
this.addDraftPageMetadata(pageCode, page.getMetadata(), conn);
this.addWidgetForPage(page, WidgetConfigDest.DRAFT, conn);
conn.commit();
} catch (Throwable t) {
this.executeRollback(conn);
_logger.error("Error while updating the page", t);
throw new RuntimeException("Error while updating the page", t);
} finally {
closeConnection(conn);
}
}
/**
* Stores a "dataversion" record in the ES store. There is only a single one of these. The
* return value of the add will include the version number of the entity. This version
* number is what we use to determine whether our cache is stale.
*/
protected void updateDataVersion() {
Connection conn = null;
try {
long newVersion = System.currentTimeMillis();
conn = ds.getConnection();
conn.setAutoCommit(false);
QueryRunner run = new QueryRunner();
run.update(conn, "DELETE FROM gw_dataversion"); //$NON-NLS-1$
run.update(conn, "INSERT INTO gw_dataversion (version) VALUES (?)", //$NON-NLS-1$
newVersion);
DbUtils.commitAndClose(conn);
dataVersion = newVersion;
} catch (SQLException e) {
dataVersion = -1;
}
}
@Test
public void testSelectValueWithPointKeyQuery() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.setAutoCommit(false);
try {
initTableValues();
String query;
PreparedStatement stmt;
ResultSet rs;
query = "SELECT * FROM " + TEST_TABLE + " where key_string = 'abc'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
assertEquals("abc", rs.getString(1));
assertEquals(3, rs.getInt(2));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Override
public void updateMapping(FriendlyCodeVO vo) {
Connection conn = null;
try {
conn = this.getConnection();
conn.setAutoCommit(false);
super.executeQueryWithoutResultset(conn, DELETE_FROM_FRIENDLYCODE, vo.getFriendlyCode());
this.addRecord(vo, conn);
conn.commit();
} catch (Throwable t) {
this.executeRollback(conn);
_logger.error("Error update the mapping", t);
throw new RuntimeException("Error update the mapping", t);
} finally {
this.closeConnection(conn);
}
}
@Test(expected = SQLException.class)
public void testConnectionClosed() throws Exception {
Connection conn = createMock(Connection.class);
conn.setAutoCommit(true);
expect(conn.isClosed()).andReturn(true);
Driver driver = createMock(Driver.class);
String url = "jdbc:bar:baz";
expect(driver.acceptsURL(url)).andReturn(true);
expect(driver.connect(isA(String.class), isA(Properties.class))).andReturn(conn);
replay(driver, conn);
DriverManager.registerDriver(driver);
try {
JdbcConnectionSource sds = new JdbcConnectionSource(url, databaseType);
assertNotNull(sds.getReadOnlyConnection(null));
sds.getReadOnlyConnection(null);
sds.close();
fail("Should not get here");
} finally {
DriverManager.deregisterDriver(driver);
}
}
public void testGroupByCount(boolean localIndex) throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.setAutoCommit(false);
ensureTableCreated(getUrl(), INDEX_DATA_TABLE);
populateTestTable();
String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
+ " (int_col2)";
PreparedStatement stmt = conn.prepareStatement(ddl);
stmt.execute();
ResultSet rs;
rs = conn.createStatement().executeQuery("SELECT int_col2, COUNT(*) FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " GROUP BY int_col2");
assertTrue(rs.next());
assertEquals(1,rs.getInt(2));
}
public static Connection getDBConnection(DataSource dataSource) throws SQLException {
Connection dbConnection = dataSource.getConnection();
dbConnection.setAutoCommit(false);
if (dbConnection.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) {
dbConnection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
}
return dbConnection;
}
@After
public void tearDown() throws Exception {
final Connection c = this.dataSource.getConnection();
final Statement s = c.createStatement();
c.setAutoCommit(true);
for (int i = 0; i < 5; i++) {
final String sql = String.format("delete from casusers;");
s.execute(sql);
}
c.close();
}
@Test
public void testSelectDiff() throws Exception {
String tableName1 = generateUniqueName();
String tableName2 = generateUniqueName();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.setAutoCommit(false);
try {
String ddl = "CREATE TABLE " + tableName1 + " " +
" (a_string varchar not null, col1 integer" +
" CONSTRAINT pk PRIMARY KEY (a_string))\n";
createTestTable(getUrl(), ddl);
ddl = "CREATE TABLE " + tableName2 + " " +
" (a_string varchar not null, col1 integer" +
" CONSTRAINT pk PRIMARY KEY (a_string))\n";
createTestTable(getUrl(), ddl);
ddl = "select a_string, col1, col1 from " + tableName1 + " union all select * from " + tableName2 + " union all select a_string, col1 from " + tableName1;
conn.createStatement().executeQuery(ddl);
fail();
} catch (SQLException e) {
assertEquals(SQLExceptionCode.SELECT_COLUMN_NUM_IN_UNIONALL_DIFFS.getErrorCode(), e.getErrorCode());
} finally {
conn.close();
}
}
@Test
public void testSubqueryWithUpsert() throws Exception {
String tempTable = generateUniqueName();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.setAutoCommit(true);
String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
try {
conn.createStatement().execute("CREATE TABLE " + tempTable
+ " (item_id varchar not null primary key, "
+ " name varchar)");
conn.createStatement().execute("UPSERT INTO " + tempTable + "(item_id, name)"
+ " SELECT \"item_id\", name FROM " + tableName1
+ " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + tableName4 + ")");
String query = "SELECT name FROM " + tempTable + " ORDER BY item_id";
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue (rs.next());
assertEquals(rs.getString(1), "T4");
assertTrue (rs.next());
assertEquals(rs.getString(1), "T5");
assertTrue (rs.next());
assertEquals(rs.getString(1), "INVALID-1");
assertFalse(rs.next());
} finally {
conn.close();
}
}
@BeforeClass
public static void addDatabaseUserWithPassword() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
Connection conn = DriverManager.getConnection(DATASOURCE_URL, "sa", "");
conn.setAutoCommit(true);
Statement st = conn.createStatement();
st.executeUpdate("CREATE USER " + USER + " PASSWORD '" + PASSWORD + "';");
st.close();
conn.commit();
conn.close();
}
public static synchronized Connection getConnection(String protocol,
Properties props) throws SQLException {
loadDriverClass(getDriver());
props = doCommonSetup(props);
final Connection conn = DriverManager.getConnection(protocol, props);
boolean nonTXTestMode = Boolean.getBoolean(SanityManager.TEST_MODE_NON_TX)
|| Boolean.parseBoolean(System.getenv(SanityManager.TEST_MODE_NON_TX));
// if (nonTXTestMode) {
System.out.println("Non-tx test mode.");
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
// }
LogWriter logger = TestUtil.getLogger();
if (logger != null) {
logger.info("TestUtil.getConnection::Autocommit is " + conn.getAutoCommit());
}
// Read the flag for deleting persistent files only during boot up
if (jdbcConn == null || jdbcConn.isClosed()) {
jdbcConn = conn;
currentUserName = props.getProperty(Attribute.USERNAME_ATTR);
currentUserName = currentUserName == null ? props
.getProperty(Attribute.USERNAME_ALT_ATTR) : currentUserName;
currentUserPassword = props.getProperty(Attribute.PASSWORD_ATTR);
}
return conn;
}
public void deleteApplication(String name) {
Application app = getApplication(name);
// delete infos
Connection conn = null;
boolean autoCommit = true;
try {
conn = DBPower.getConnection(table.getId());
autoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
ApplicationUserType appUserType = new ApplicationUserType();
appUserType.setAppName(name);
applicationUserTypeDeletor.delete(conn,
appNameUserTypeTableWhereEmt, appUserType);
Application hint = new Application();
hint.setName(name);
deletor.delete(conn, appNameWhereEmt, hint);
conn.commit();
} catch (SQLException e) {
DBUtil.rollback(conn);
throw new DBLevelException(e);
} finally {
DBUtil.setCommitMode(conn, autoCommit);
DBUtil.close(conn);
}
changed = true;
// delete tables
deleteTablesForApp(app);
// notify Factory
org.ralasafe.Factory.applicationChanged(app.getName());
}
@Override
protected void doOpen() {
if (isOpen())
throw Transaction.Exceptions.transactionAlreadyOpen();
else {
try {
Connection connection = this.sqlgGraph.getConnection();
connection.setAutoCommit(false);
if (this.sqlgGraph.getSqlDialect().supportsClientInfo()) {
String applicationName = Thread.currentThread().getName();
if (applicationName.length() > 63) {
String first = applicationName.substring(0, 30);
String last = applicationName.substring(applicationName.length() - 30);
applicationName = first + "..." + last;
}
connection.setClientInfo("ApplicationName", applicationName);
}
// read default setting for laziness
boolean lazy = this.sqlgGraph.getConfiguration().getBoolean(QUERY_LAZY, true);
TransactionCache tc;
if (supportsBatchMode()) {
tc = TransactionCache.of(this.cacheVertices, connection, new BatchManager(this.sqlgGraph, ((SqlBulkDialect) this.sqlgGraph.getSqlDialect())), lazy);
} else {
tc = TransactionCache.of(this.cacheVertices, connection, lazy);
}
tc.setFetchSize(getDefaultFetchSize());
this.threadLocalTx.set(tc);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
@Override
public void run() {
Connection con = null;
try {
List<Map<String, String>> batch = getNextBatch();
while (!batch.isEmpty()) {
try {
if (con == null || con.isClosed()) {
con = conPool.getConnection();
con.setAutoCommit(false);
}
insert(con, batch);
finshiedCount.addAndGet(batch.size());
} catch (Exception e) {
e.printStackTrace();
try {
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
e1.printStackTrace();
}
failedCount.addAndGet(batch.size());
}
batch = getNextBatch();
}
} finally {
if (con != null) {
this.conPool.returnCon(con);
}
}
}
/**
* Sets the auto commit to false.
*/
protected void initializeConnection(Connection conn) throws SQLException {
conn.setAutoCommit(false);
}
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));
}
@Override
public void initializeConnection(Connection connection) throws SQLException {
// Disable auto-commit in PostgreSQL to support cursors
// @see http://jdbc.postgresql.org/documentation/83/query.html
connection.setAutoCommit(false);
}
/**
* Tests whether re-connect with non-read-only connection can happen.
*
* @throws Exception
* if the test fails.
*/
public void testFailoverConnection() throws Exception {
if (!isServerRunningOnWindows()) { // windows sockets don't work for this test
Properties props = new Properties();
props.setProperty("autoReconnect", "true");
props.setProperty("failOverReadOnly", "false");
Properties urlProps = new NonRegisteringDriver().parseURL(dbUrl, null);
String host = urlProps.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY);
String port = urlProps.getProperty(NonRegisteringDriver.PORT_PROPERTY_KEY);
props.setProperty(NonRegisteringDriver.HOST_PROPERTY_KEY + ".1", host);
props.setProperty(NonRegisteringDriver.PORT_PROPERTY_KEY + ".1", port);
props.setProperty(NonRegisteringDriver.HOST_PROPERTY_KEY + ".2", host);
props.setProperty(NonRegisteringDriver.PORT_PROPERTY_KEY + ".2", port);
props.setProperty(NonRegisteringDriver.NUM_HOSTS_PROPERTY_KEY, "2");
Connection failoverConnection = null;
try {
failoverConnection = getConnectionWithProps(props);
String originalConnectionId = getSingleIndexedValueWithQuery(failoverConnection, 1, "SELECT connection_id()").toString();
System.out.println("Original Connection Id = " + originalConnectionId);
assertTrue("Connection should not be in READ_ONLY state", !failoverConnection.isReadOnly());
// Kill the connection
this.stmt.executeUpdate("KILL " + originalConnectionId);
// This takes a bit to occur
Thread.sleep(3000);
try {
failoverConnection.createStatement().execute("SELECT 1");
fail("We expect an exception here, because the connection should be gone until the reconnect code picks it up again");
} catch (SQLException sqlEx) {
// do-nothing
}
// Tickle re-connect
failoverConnection.setAutoCommit(true);
String newConnectionId = getSingleIndexedValueWithQuery(failoverConnection, 1, "SELECT connection_id()").toString();
System.out.println("new Connection Id = " + newConnectionId);
assertTrue("We should have a new connection to the server in this case", !newConnectionId.equals(originalConnectionId));
assertTrue("Connection should not be read-only", !failoverConnection.isReadOnly());
} finally {
if (failoverConnection != null) {
failoverConnection.close();
}
}
}
}
private static void testTwiceCommited()
throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
final String dbname = "testTwiceCommitedDB";
Connection conn = null;
try{
conn = openConnectionToNewDatabase(dbname);
createTestTable(conn);
conn.setAutoCommit(false);
insertIntoTestTable(conn,
1,
1000);
conn.commit();
insertIntoTestTable(conn,
1001,
999);
conn.commit();
shutdownDatabase(dbname);
}catch(SQLException e){
verifyShutdownError(e);
}
conn = null;
try{
conn = reopenConnectionToDatabase(dbname);
countRowInTestTable(conn);
}finally{
if(conn != null){
conn.close();
conn = null;
}
}
}