List of usage examples for java.sql Connection setTransactionIsolation
void setTransactionIsolation(int level) throws SQLException;
Connection object to the one given. From source file:com.adaptris.jdbc.connection.FailoverDatasourceTest.java
@Test public void testInfo() throws Exception { Connection conn = new MyProxy(); try {/*w ww.jav a 2s . c o m*/ try { conn.getMetaData(); } catch (SQLException e) { } try { conn.setCatalog(conn.getCatalog()); } catch (SQLException e) { } try { conn.setReadOnly(conn.isReadOnly()); } catch (SQLException e) { } try { conn.setTransactionIsolation(conn.getTransactionIsolation()); } catch (SQLException e) { } try { conn.setTransactionIsolation(conn.getTransactionIsolation()); } catch (SQLException e) { } try { conn.getWarnings(); } catch (SQLException e) { } try { conn.clearWarnings(); } catch (SQLException e) { } try { conn.setHoldability(conn.getHoldability()); } catch (SQLException e) { } try { conn.setSchema(conn.getSchema()); } catch (SQLException e) { } } finally { JdbcUtil.closeQuietly(conn); } }
From source file:org.apache.hadoop.sqoop.manager.SqlManager.java
/** * Create a connection to the database; usually used only from within * getConnection(), which enforces a singleton guarantee around the * Connection object./*from ww w .j a va 2s .co m*/ */ protected Connection makeConnection() throws SQLException { Connection connection; String driverClass = getDriverClass(); try { Class.forName(driverClass); } catch (ClassNotFoundException cnfe) { throw new RuntimeException("Could not load db driver class: " + driverClass); } String username = options.getUsername(); String password = options.getPassword(); if (null == username) { connection = DriverManager.getConnection(options.getConnectString()); } else { connection = DriverManager.getConnection(options.getConnectString(), username, password); } connection.setAutoCommit(false); connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); return connection; }
From source file:org.cloudgraph.rdb.service.RDBGraphService.java
public SnapshotMap commit(DataGraph[] dataGraphs, String username) { if (dataGraphs == null) throw new IllegalArgumentException("expected non-null 'dataGraphs' argument"); if (username == null) throw new IllegalArgumentException("expected non-null 'username' argument"); if (username.trim().length() == 0) throw new IllegalArgumentException("unexpected zero length 'username' argument"); SnapshotMap snapshotMap = new SnapshotMap(new Timestamp((new Date()).getTime())); Connection con = null; try {//from w w w. j av a 2 s . c o m if (log.isDebugEnabled()) log.debug("getting connection"); con = ProviderManager.instance().getConnection(); if (con.getAutoCommit()) { if (log.isDebugEnabled()) log.debug("turning off connection autocommit for multi graph commit"); con.setAutoCommit(false); } // TODO: make transaction isolation configurable con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); if (log.isDebugEnabled()) log.debug("using transaction isolation level " + con.getTransactionIsolation() + " for multi graph commit"); } catch (SQLException e2) { if (con != null) try { if (log.isDebugEnabled()) log.debug("closing connection"); con.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } throw new DataAccessException(e2); } DataGraphDispatcher dispatcher = new GraphDispatcher(snapshotMap, username, con); try { dispatcher.commit(dataGraphs); con.commit(); return snapshotMap; } catch (DataAccessException e) { try { con.rollback(); } catch (SQLException e1) { log.error(e1.getMessage(), e1); } throw e; } catch (Throwable t) { try { con.rollback(); } catch (SQLException e) { log.error(e.getMessage(), e); } throw new DataAccessException(t); } finally { if (con != null) try { if (log.isDebugEnabled()) log.debug("closing connection"); con.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } dispatcher.close(); } }
From source file:org.opendatakit.common.persistence.engine.mysql.TaskLockImpl.java
private TaskLockTable doTransaction(TaskLockTable entity, long l) throws ODKEntityNotFoundException, ODKTaskLockException { boolean first; final List<String> stmts = new ArrayList<String>(); final String uri = entity.getUri(); StringBuilder b = new StringBuilder(); String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME + K_BQ;/* w w w . ja v a2 s . c om*/ // String tableName= TaskLockTable.TABLE_NAME; b.append("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE"); stmts.add(b.toString()); b.setLength(0); b.append("START TRANSACTION WITH CONSISTENT SNAPSHOT"); stmts.add(b.toString()); b.setLength(0); b.append("SET @present := NOW(),"); b.append(" @uriUser := '"); b.append(user.getUriUser().replaceAll("'", "''")); b.append("',"); b.append(" @uriLock := '"); b.append(uri.replaceAll("'", "''")); b.append("',"); b.append(" @formId := '"); b.append(entity.getFormId().replaceAll("'", "''")); b.append("',"); b.append(" @taskType := '"); b.append(entity.getTaskType().replaceAll("'", "''")); b.append("',"); b.append(" @lifetimeMicroseconds := "); b.append(1000L * l); stmts.add(b.toString()); b.setLength(0); b.append("LOCK TABLES "); b.append(tableName); b.append(" WRITE "); stmts.add(b.toString()); b.setLength(0); dam.recordPutUsage(TaskLockTable.TABLE_NAME); if (!entity.isFromDatabase()) { // insert a new record b.append("REPLACE INTO "); b.append(tableName); b.append(" ("); first = true; for (DataField f : entity.getFieldList()) { if (!first) { b.append(","); } first = false; b.append(K_BQ); b.append(f.getName()); b.append(K_BQ); } first = true; b.append(") VALUES ( "); for (DataField f : entity.getFieldList()) { if (!first) { b.append(","); } first = false; if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) { b.append("@present"); } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) { b.append("@uriUser"); } else if (f.equals(entity.formId)) { b.append("@formId"); } else if (f.equals(entity.taskType)) { b.append("@taskType"); } else if (f.equals(entity.primaryKey)) { b.append("@uriLock"); } else if (f.equals(entity.expirationDateTime)) { b.append(" DATE_ADD(CAST(@present AS DATETIME), INTERVAL @lifetimeMicroseconds MICROSECOND)"); } else { throw new IllegalStateException("unexpected case " + f.getName()); } } b.append(")"); stmts.add(b.toString()); b.setLength(0); } else { // update existing record b.append("UPDATE "); b.append(tableName); b.append(" SET "); first = true; for (DataField f : entity.getFieldList()) { if (f == entity.primaryKey) continue; if (!first) { b.append(","); } first = false; b.append(K_BQ); b.append(f.getName()); b.append(K_BQ); b.append(" = "); if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) { b.append("@present"); } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) { b.append("@uriUser"); } else if (f.equals(entity.formId)) { b.append("@formId"); } else if (f.equals(entity.taskType)) { b.append("@taskType"); } else if (f.equals(entity.primaryKey)) { b.append("@uriLock"); } else if (f.equals(entity.expirationDateTime)) { b.append(" DATE_ADD(CAST(@present AS DATETIME), INTERVAL @lifetimeMicroseconds MICROSECOND)"); } else { throw new IllegalStateException("unexpected case " + f.getName()); } } b.append(" WHERE "); b.append(K_BQ); b.append(entity.primaryKey.getName()); b.append(K_BQ); b.append(" = "); b.append("@uriLock"); stmts.add(b.toString()); b.setLength(0); } // delete stale locks (don't care who's) dam.recordDeleteUsage(TaskLockTable.TABLE_NAME); b.append("DELETE FROM "); b.append(tableName); b.append(" WHERE "); b.append(K_BQ); b.append(entity.expirationDateTime.getName()); b.append(K_BQ); b.append(" <= CAST(@present AS DATETIME)"); stmts.add(b.toString()); b.setLength(0); // determine the time of the oldest lock for this resource and task type... // do this by querying for the minimum expiration time // BUT, first, set the minimum time to the present time in case there are no locks. b.append("SET @minExpiration = @present"); stmts.add(b.toString()); b.setLength(0); dam.recordQueryUsage(TaskLockTable.TABLE_NAME, 1); b.append("SELECT @minExpiration := MIN("); b.append(K_BQ); b.append(entity.expirationDateTime.getName()); b.append(K_BQ); b.append(") FROM "); b.append(tableName); b.append(" WHERE "); b.append(K_BQ); b.append(entity.formId.getName()); b.append(K_BQ); b.append(" = @formId AND "); b.append(K_BQ); b.append(entity.taskType.getName()); b.append(K_BQ); b.append(" = @taskType"); stmts.add(b.toString()); b.setLength(0); // determine if there are two or more records matching // the minimum expiration time. If there are, we must // release the locks and retry, as we cannot determine // which one is first. b.append("SET @uriCount = 0"); stmts.add(b.toString()); b.setLength(0); b.append("SELECT @uriCount := COUNT("); b.append(K_BQ); b.append(entity.primaryKey.getName()); b.append(K_BQ); b.append(") FROM "); b.append(tableName); b.append(" WHERE "); b.append(K_BQ); b.append(entity.formId.getName()); b.append(K_BQ); b.append(" = @formId AND "); b.append(K_BQ); b.append(entity.taskType.getName()); b.append(K_BQ); b.append(" = @taskType AND "); b.append(K_BQ); b.append(entity.expirationDateTime.getName()); b.append(K_BQ); b.append(" = CAST(@minExpiration AS DATETIME)"); stmts.add(b.toString()); b.setLength(0); // delete all locks except the oldest one for this resource and task type... // or, if we have two or more old locks, release ours // whatever lock exists identifies the owner of the resource. dam.recordDeleteUsage(TaskLockTable.TABLE_NAME); b.append("DELETE FROM "); b.append(tableName); b.append(" WHERE "); b.append(K_BQ); b.append(entity.formId.getName()); b.append(K_BQ); b.append(" = @formId AND "); b.append(K_BQ); b.append(entity.taskType.getName()); b.append(K_BQ); b.append(" = @taskType AND ( "); b.append(K_BQ); b.append(entity.expirationDateTime.getName()); b.append(K_BQ); b.append(" > CAST(@minExpiration AS DATETIME)"); b.append(" OR ( @uriCount > 1 AND "); b.append(K_BQ); b.append(entity.expirationDateTime.getName()); b.append(K_BQ); b.append(" = CAST(@minExpiration AS DATETIME)"); b.append(" AND "); b.append(K_BQ); b.append(entity.primaryKey.getName()); b.append(K_BQ); b.append(" = @uriLock ) )"); stmts.add(b.toString()); b.setLength(0); // and within the transaction, see if the primary key of the winning lock is ours... b.append("SELECT "); b.append(K_BQ); b.append(entity.primaryKey.getName()); b.append(K_BQ); b.append(" FROM "); b.append(tableName); b.append(" WHERE "); b.append(K_BQ); b.append(entity.formId.getName()); b.append(K_BQ); b.append(" = @formId AND "); b.append(K_BQ); b.append(entity.taskType.getName()); b.append(K_BQ); b.append(" = @taskType AND "); b.append(K_BQ); b.append(entity.expirationDateTime.getName()); b.append(K_BQ); b.append(" = CAST(@minExpiration AS DATETIME)"); stmts.add(b.toString()); b.setLength(0); b.append("UNLOCK TABLES"); stmts.add(b.toString()); b.setLength(0); b.append("COMMIT"); stmts.add(b.toString()); b.setLength(0); TaskLockTable relation; try { relation = TaskLockTable.assertRelation(datastore, user); } catch (ODKDatastoreException e) { throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e); } boolean success = false; try { JdbcTemplate jdbc = datastore.getJdbcConnection(); Object o = jdbc.execute(new ConnectionCallback<Object>() { @Override public Object doInConnection(Connection conn) throws SQLException, DataAccessException { boolean success = false; boolean oldValue = conn.getAutoCommit(); try { conn.setAutoCommit(false); conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); String lastResult = null; for (String s : stmts) { Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT); if (s.startsWith("SELECT")) { ResultSet rs = stmt.executeQuery(s); if (rs.first()) { lastResult = rs.getString(1); } rs.close(); } else { stmt.executeUpdate(s); } stmt.close(); } conn.commit(); success = uri.equals(lastResult); } catch (Exception e) { e.printStackTrace(); conn.rollback(); } finally { if (!success) { Statement stmt = conn.createStatement(); LogFactory.getLog(TaskLockImpl.class).info("UNLOCK TABLES"); stmt.execute("UNLOCK TABLES"); conn.commit(); } } conn.setAutoCommit(oldValue); return success ? uri : null; } }); success = o != null && uri.equals((String) o); } catch (Exception e) { throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e); } if (success) { return (TaskLockTable) datastore.getEntity(relation, uri, user); } else { throw new ODKEntityNotFoundException(); } }
From source file:org.apache.slide.store.impl.rdbms.JDBCStore.java
protected Connection getNewConnection() throws SQLException { Connection connection; if (useDbcpPooling) { try {/* ww w . ja v a 2s. c o m*/ connection = DriverManager.getConnection(DBCP_URL + ":" + dbcpPoolName); } catch (SQLException e) { getLogger().log("Could not create connection. Reason: " + e, LOG_CHANNEL, Logger.EMERGENCY); throw e; } } else { try { connection = DriverManager.getConnection(url, user, password); } catch (SQLException e) { getLogger().log("Could not create connection. Reason: " + e, LOG_CHANNEL, Logger.EMERGENCY); throw e; } try { if (connection.getTransactionIsolation() != isolationLevel) { connection.setTransactionIsolation(isolationLevel); } } catch (SQLException e) { getLogger().log("Could not set isolation level '" + isolationLevelToString(isolationLevel) + "'. Reason: " + e, LOG_CHANNEL, Logger.WARNING); } if (connection.getAutoCommit()) { connection.setAutoCommit(false); } } return connection; }
From source file:org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCTransactionManager.java
public void beginTransaction() throws RegistryException { if (dataAccessManager.getDatabaseTransaction().getNestedDepth() != 0) { if (log.isTraceEnabled()) { log.trace("The transaction was not started, because it is called within a " + "transaction, nested depth: " + dataAccessManager.getDatabaseTransaction().getNestedDepth() + "."); }/*from w w w . j av a2s . c o m*/ dataAccessManager.getDatabaseTransaction().incNestedDepth(); if (JDBCDatabaseTransaction.getConnection() != null) { return; } else { // If we get here, there has been some issue related to connection failure. // There is no point in using the connection that is already existing on this // thread. We need to start using a new one. while (dataAccessManager.getDatabaseTransaction().getNestedDepth() > 0) { dataAccessManager.getDatabaseTransaction().decNestedDepth(); } while (dataAccessManager.getDatabaseTransaction().getNestedDepth() < 0) { dataAccessManager.getDatabaseTransaction().incNestedDepth(); } } } Connection conn; try { if (!(dataAccessManager instanceof JDBCDataAccessManager)) { String msg = "Failed to begin transaction. Invalid data access manager."; log.error(msg); throw new RegistryException(msg); } DataSource dataSource = ((JDBCDataAccessManager) dataAccessManager).getDataSource(); conn = dataSource.getConnection(); // If a managed connection already exists, use that instead of a new connection. JDBCDatabaseTransaction.ManagedRegistryConnection temp = JDBCDatabaseTransaction .getManagedRegistryConnection(conn); if (temp != null) { conn.close(); conn = temp; } if (conn.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) { conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); } conn.setAutoCommit(false); dataAccessManager.getDatabaseTransaction().incNestedDepth(); } catch (SQLException e) { String msg = "Failed to start new registry transaction."; log.error(msg, e); throw new RegistryException(msg, e); } JDBCDatabaseTransaction.setConnection(conn); }
From source file:de.ufinke.cubaja.sql.Database.java
/** * Uses existing connection with specific configuration attributes. * @param connection/*ww w. j av a 2 s. com*/ * @param config * @throws SQLException */ public Database(Connection connection, DatabaseConfig config) throws SQLException { if (config.isLog()) { myId = getId(); logger = LogFactory.getLog(Database.class); String url = connection.getMetaData().getURL(); String user = connection.getMetaData().getUserName(); if (user == null) { logger.debug(text.get("connectedNoUser", myId, url)); } else { logger.debug(text.get("connectedUser", myId, url, user)); } } this.connection = connection; this.config = config; connection.setAutoCommit(config.isAutoCommit()); if (config.getTransactionIsolation() != null) { connection.setTransactionIsolation(config.getTransactionIsolation().getLevel()); } if (config.getExecute() != null) { execute(config.getExecute()); } }
From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java
protected void apply(Connection conn, ConnectionProperties connProps) throws SQLException { if (connProps.isAutoCommit() != null) { conn.setAutoCommit(connProps.isAutoCommit()); }//ww w . j a v a2 s . c o m if (connProps.isReadOnly() != null) { conn.setReadOnly(connProps.isReadOnly()); } if (connProps.getTransactionIsolation() != null) { conn.setTransactionIsolation(connProps.getTransactionIsolation()); } if (connProps.getCatalog() != null) { conn.setCatalog(connProps.getCatalog()); } if (connProps.getSchema() != null) { conn.setSchema(connProps.getSchema()); } }
From source file:org.wso2.carbon.repository.core.jdbc.dataaccess.JDBCTransactionManager.java
public void beginTransaction() throws RepositoryException { if (dataAccessManager.getDatabaseTransaction().getNestedDepth() != 0) { if (log.isTraceEnabled()) { log.trace(// ww w . j a v a 2 s.c o m "The transaction was not started, because it is called within a transaction, nested depth: " + dataAccessManager.getDatabaseTransaction().getNestedDepth() + "."); } dataAccessManager.getDatabaseTransaction().incNestedDepth(); if (JDBCDatabaseTransaction.getConnection() != null) { return; } else { // If we get here, there has been some issue related to connection failure. // There is no point in using the connection that is already existing on this // thread. We need to start using a new one. while (dataAccessManager.getDatabaseTransaction().getNestedDepth() > 0) { dataAccessManager.getDatabaseTransaction().decNestedDepth(); } while (dataAccessManager.getDatabaseTransaction().getNestedDepth() < 0) { dataAccessManager.getDatabaseTransaction().incNestedDepth(); } } } Connection conn; try { if (!(dataAccessManager instanceof JDBCDataAccessManager)) { String msg = "Failed to begin transaction. Invalid data access manager."; log.error(msg); throw new RepositoryDBException(msg); } DataSource dataSource = ((JDBCDataAccessManager) dataAccessManager).getDataSource(); conn = dataSource.getConnection(); // If a managed connection already exists, use that instead of a new connection. JDBCDatabaseTransaction.ManagedRegistryConnection temp = JDBCDatabaseTransaction .getManagedRegistryConnection(conn); if (temp != null) { conn.close(); conn = temp; } if (conn.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) { conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); } conn.setAutoCommit(false); dataAccessManager.getDatabaseTransaction().incNestedDepth(); } catch (SQLException e) { String msg = "Failed to start new registry transaction."; log.error(msg, e); throw new RepositoryDBException(msg, e); } JDBCDatabaseTransaction.setConnection(conn); }
From source file:org.cloudgraph.rdb.service.RDBGraphService.java
public int count(Query query) { if (query == null) throw new IllegalArgumentException("expected non-null 'query' argument"); validate(query);// w ww . ja v a2 s . c o m if (log.isDebugEnabled()) { log(query); } Connection con = null; try { if (log.isDebugEnabled()) log.debug("getting connection"); con = ProviderManager.instance().getConnection(); if (con.getAutoCommit()) { if (log.isDebugEnabled()) log.debug("turning off connection autocommit for count query"); con.setAutoCommit(false); } RDBMSVendorName vendor = PlasmaRuntime.getInstance() .getRDBMSProviderVendor(DataAccessProviderName.JDBC); switch (vendor) { case ORACLE: con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); break; case MYSQL: con.setTransactionIsolation(Connection.TRANSACTION_NONE); // Oracle // does // not // support break; default: } // TODO: make transaction isolation configurable if (log.isDebugEnabled()) log.debug( "using transaction isolation level " + con.getTransactionIsolation() + " for count query"); } catch (SQLException e2) { if (con != null) try { if (log.isDebugEnabled()) log.debug("closing connection"); con.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } throw new DataAccessException(e2); } GraphQuery dispatcher = new GraphQuery(con); try { return dispatcher.count(query); } finally { if (con != null) try { if (log.isDebugEnabled()) log.debug("closing connection"); con.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } } }