List of usage examples for java.sql Connection setTransactionIsolation
void setTransactionIsolation(int level) throws SQLException;
Connection object to the one given. From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCPathCache.java
/** * Method to add resource path entry to the database. * * @param path the path to add.//from w w w . j a v a 2 s . co m * @param parentPathId the parent path's id. * * @return the path's id. * @throws RegistryException if the data access manager was invalid. * @throws SQLException if an error occurs while adding the entry. */ public int addEntry(String path, int parentPathId) throws SQLException, RegistryException { ResultSet results = null; PreparedStatement ps = null; PreparedStatement ps1 = null; DataAccessManager dataAccessManager; if (CurrentSession.getUserRegistry() != null && CurrentSession.getUserRegistry().getRegistryContext() != null) { dataAccessManager = CurrentSession.getUserRegistry().getRegistryContext().getDataAccessManager(); } else { // TODO: This code block doesn't seem to get hit. Remove if unused. dataAccessManager = RegistryContext.getBaseInstance().getDataAccessManager(); } if (!(dataAccessManager instanceof JDBCDataAccessManager)) { String msg = "Failed to add path entry. Invalid data access manager."; log.error(msg); throw new RegistryException(msg); } DataSource dataSource = ((JDBCDataAccessManager) dataAccessManager).getDataSource(); Connection conn = dataSource.getConnection(); if (conn != null) { if (conn.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) { conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); } conn.setAutoCommit(false); } else { log.error("Unable to acquire connection to database."); return -1; } boolean success = false; int pathId = 0; try { String sql = "INSERT INTO REG_PATH(REG_PATH_VALUE, REG_PATH_PARENT_ID, REG_TENANT_ID) " + "VALUES (?, ?, ?)"; String sql1 = "SELECT MAX(REG_PATH_ID) FROM REG_PATH"; String dbProductName = conn.getMetaData().getDatabaseProductName(); boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName); if (returnsGeneratedKeys) { ps = conn.prepareStatement(sql, new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_PATH_ID") }); } else { ps = conn.prepareStatement(sql); } ps.setString(1, path); ps.setInt(2, parentPathId); ps.setInt(3, CurrentSession.getTenantId()); if (returnsGeneratedKeys) { ps.executeUpdate(); results = ps.getGeneratedKeys(); } else { synchronized (ADD_ENTRY_LOCK) { ps.executeUpdate(); ps1 = conn.prepareStatement(sql1); results = ps1.executeQuery(); } } if (results.next()) { pathId = results.getInt(1); if (pathId > 0) { success = true; return pathId; } } } catch (SQLException e) { // we have to be expecting an exception with the duplicate value for the path value // which can be further checked from here.. String msg = "Failed to insert resource to " + path + ". " + e.getMessage(); log.error(msg, e); throw e; } finally { if (success) { try { conn.commit(); RegistryCacheEntry e = new RegistryCacheEntry(pathId); String connectionId = null; if (conn.getMetaData() != null) { connectionId = RegistryUtils.getConnectionId(conn); } RegistryCacheKey key = RegistryUtils.buildRegistryCacheKey(connectionId, CurrentSession.getTenantId(), path); getCache().put(key, e); } catch (SQLException e) { String msg = "Failed to commit transaction. Inserting " + path + ". " + e.getMessage(); log.error(msg, e); } finally { try { try { if (results != null) { results.close(); } } finally { try { if (ps1 != null) { ps1.close(); } } finally { try { if (ps != null) { ps.close(); } } finally { conn.close(); } } } } catch (SQLException e) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR + e.getMessage(); log.error(msg, e); } } } else { try { conn.rollback(); } catch (SQLException e) { String msg = "Failed to rollback transaction. Inserting " + path + ". " + e.getMessage(); log.error(msg, e); } finally { try { try { if (results != null) { results.close(); } } finally { try { if (ps != null) { ps.close(); } } finally { if (conn != null) { conn.close(); } } } } catch (SQLException e) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR + e.getMessage(); log.error(msg, e); } } } } return -1; }
From source file:it.cnr.icar.eric.server.persistence.rdb.ConnectionPool.java
private Connection newConnection() throws SQLException { Connection conn = null; if ((user == null) || (user.length() == 0)) { conn = DriverManager.getConnection(URL); } else {//from w ww . ja va 2s . com conn = DriverManager.getConnection(URL, user, password); } // Set Transaction Isolation and AutoComit // WARNING: till present Oracle dirvers (9.2.0.5) do not accept // setTransactionIsolation being called after setAutoCommit(false) conn.setTransactionIsolation(transactionIsolation); conn.setAutoCommit(false); return conn; }
From source file:org.cfr.capsicum.datasource.DataSourceUtils.java
/** * Prepare the given Connection with the given transaction semantics. * @param con the Connection to prepare// w ww. j av a 2 s. co m * @param definition the transaction definition to apply * @return the previous isolation level, if any * @throws SQLException if thrown by JDBC methods * @see #resetConnectionAfterTransaction */ public static Integer prepareConnectionForTransaction(Connection con, TransactionDefinition definition) throws SQLException { Assert.notNull(con, "No Connection specified"); // Set read-only flag. if (definition != null && definition.isReadOnly()) { try { if (logger.isDebugEnabled()) { logger.debug("Setting JDBC Connection [" + con + "] read-only"); } con.setReadOnly(true); } catch (Throwable ex) { // SQLException or UnsupportedOperationException // -> ignore, it's just a hint anyway. logger.debug("Could not set JDBC Connection read-only", ex); } } // Apply specific isolation level, if any. Integer previousIsolationLevel = null; if (definition != null && definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) { if (logger.isDebugEnabled()) { logger.debug("Changing isolation level of JDBC Connection [" + con + "] to " + definition.getIsolationLevel()); } previousIsolationLevel = new Integer(con.getTransactionIsolation()); con.setTransactionIsolation(definition.getIsolationLevel()); } return previousIsolationLevel; }
From source file:org.wso2.carbon.repository.core.jdbc.dao.JDBCPathCache.java
/** * Method to add resource path entry to the database. * * @param path the path to add./*from ww w.j a v a 2s .c o m*/ * @param parentPathId the parent path's id. * * @return the path's id. * @throws RepositoryException if the data access manager was invalid. * @throws SQLException if an error occurs while adding the entry. */ public int addEntry(String path, int parentPathId) throws SQLException, RepositoryException { ResultSet results = null; PreparedStatement ps = null; PreparedStatement ps1 = null; DataAccessManager dataAccessManager; if (CurrentContext.getRespository() != null && InternalUtils.getRepositoryContext(CurrentContext.getRespository()) != null) { dataAccessManager = InternalUtils.getRepositoryContext(CurrentContext.getRespository()) .getDataAccessManager(); } else { dataAccessManager = RepositoryContext.getBaseInstance().getDataAccessManager(); } if (!(dataAccessManager instanceof JDBCDataAccessManager)) { String msg = "Failed to add path entry. Invalid data access manager."; log.error(msg); throw new RepositoryServerException(msg); } DataSource dataSource = ((JDBCDataAccessManager) dataAccessManager).getDataSource(); Connection conn = dataSource.getConnection(); if (conn != null) { if (conn.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) { conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); } conn.setAutoCommit(false); } else { log.error("Unable to acquire connection to database."); return -1; } boolean success = false; int pathId = 0; try { String sql = "INSERT INTO REG_PATH(REG_PATH_VALUE, REG_PATH_PARENT_ID, REG_TENANT_ID) VALUES (?, ?, ?)"; String sql1 = "SELECT MAX(REG_PATH_ID) FROM REG_PATH"; String dbProductName = conn.getMetaData().getDatabaseProductName(); boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName); if (returnsGeneratedKeys) { ps = conn.prepareStatement(sql, new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_PATH_ID") }); } else { ps = conn.prepareStatement(sql); } ps.setString(1, path); ps.setInt(2, parentPathId); ps.setInt(3, CurrentContext.getTenantId()); if (returnsGeneratedKeys) { ps.executeUpdate(); results = ps.getGeneratedKeys(); } else { synchronized (ADD_ENTRY_LOCK) { ps.executeUpdate(); ps1 = conn.prepareStatement(sql1); results = ps1.executeQuery(); } } if (results.next()) { pathId = results.getInt(1); if (pathId > 0) { success = true; return pathId; } } } catch (SQLException e) { // we have to be expecting an exception with the duplicate value for the path value // which can be further checked from here.. String msg = "Failed to insert resource to " + path + ". " + e.getMessage(); log.error(msg, e); throw e; } finally { if (success) { try { conn.commit(); RepositoryCacheEntry e = new RepositoryCacheEntry(pathId); String connectionId = null; if (conn.getMetaData() != null) { connectionId = InternalUtils.getConnectionId(conn); } RepositoryCacheKey key = InternalUtils.buildRegistryCacheKey(connectionId, CurrentContext.getTenantId(), path); getCache().put(key, e); } catch (SQLException e) { String msg = "Failed to commit transaction. Inserting " + path + ". " + e.getMessage(); log.error(msg, e); } finally { try { try { if (results != null) { results.close(); } } finally { try { if (ps1 != null) { ps1.close(); } } finally { try { if (ps != null) { ps.close(); } } finally { conn.close(); } } } } catch (SQLException e) { String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR + e.getMessage(); log.error(msg, e); } } } else { try { conn.rollback(); } catch (SQLException e) { String msg = "Failed to rollback transaction. Inserting " + path + ". " + e.getMessage(); log.error(msg, e); } finally { try { try { if (results != null) { results.close(); } } finally { try { if (ps != null) { ps.close(); } } finally { if (conn != null) { conn.close(); } } } } catch (SQLException e) { String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR + e.getMessage(); log.error(msg, e); } } } } return -1; }
From source file:org.geoserver.security.jdbc.AbstractJDBCService.java
/** * Get a new connection from the datasource, * check/set autocommit == false and isolation level * according to {@link #DEFAULT_ISOLATION_LEVEL} * /*from www.ja va 2 s .c o m*/ * @return * @throws SQLException */ protected Connection getConnection() throws SQLException { Connection con = getDataSource().getConnection(); if (con.getAutoCommit()) con.setAutoCommit(false); if (con.getTransactionIsolation() != DEFAULT_ISOLATION_LEVEL) con.setTransactionIsolation(DEFAULT_ISOLATION_LEVEL); return con; }
From source file:org.wso2.carbon.rssmanager.core.dao.util.EntityManager.java
public synchronized Connection createConnection(int txIsolationLevel, boolean wantXA) throws RSSDAOException { Connection conn; try {//from w w w. j a v a 2 s.c om conn = dataSource.getConnection(); if (wantXA && conn instanceof XAConnection && isInTransaction()) { Transaction tx = this.getRSSTransactionManager().getTransactionManager().getTransaction(); XAResource xaRes = ((XAConnection) conn).getXAResource(); if (!isXAResourceEnlisted(xaRes)) { if (txIsolationLevel >= 0) { conn.setTransactionIsolation(txIsolationLevel); } tx.enlistResource(xaRes); addToEnlistedXADataSources(xaRes); } } return conn; } catch (Exception e) { throw new RSSDAOException("Error occurred while creating datasource connection: " + e.getMessage(), e); } }
From source file:org.craftercms.cstudio.alfresco.objectstate.ObjectStateDAOServiceImpl.java
@Override public void initIndexes() { DataSource dataSource = _sqlMapClient.getDataSource(); Connection connection = null; int oldval = -1; try {//from w w w .ja va 2 s .c om connection = dataSource.getConnection(); oldval = connection.getTransactionIsolation(); if (oldval != Connection.TRANSACTION_READ_COMMITTED) { connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); } List<HashMap> checkTable = _sqlMapClient.queryForList(STATEMENT_CHECK_TABLE_EXISTS); if (checkTable == null || checkTable.size() < 1) { ScriptRunner scriptRunner = new ScriptRunner(connection, false, true); scriptRunner.runScript(Resources.getResourceAsReader(initializeScriptPath)); } else { Integer checkColumnCTEUsername = (Integer) _sqlMapClient.queryForObject(STATEMENT_CHECK_PATH_SIZE); if (checkColumnCTEUsername < 2000) { ScriptRunner scriptRunner = new ScriptRunner(connection, false, true); scriptRunner.runScript(Resources.getResourceAsReader( initializeScriptPath.replace("initialize.sql", "alter_path_column_size.sql"))); } } List<HashMap> indexCheckResult = _sqlMapClient.queryForList(STATEMENT_CHECK_OBJECT_IDX); if (indexCheckResult == null || indexCheckResult.size() < 1) { _sqlMapClient.insert(STATEMENT_ADD_OBJECT_IDX); } connection.commit(); if (oldval != -1) { connection.setTransactionIsolation(oldval); } } catch (SQLException e) { if (LOGGER.isErrorEnabled()) { LOGGER.error("Error while initializing Object State table DB indexes.", e); } } catch (IOException e) { if (LOGGER.isErrorEnabled()) { LOGGER.error("Error while initializing Sequence table DB indexes.", e); } } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { } connection = null; } } }
From source file:dao.PendingfriendDaoDb.java
/** * This method adds a pending friend as a friend. * @param guestLogin - the guest login that is added as a pending friend * @param memberId - the memberId to whom the pending friend is added. * @param memberLoginInfo - the members login information bean * @throws BaseDaoException/* ww w .java2 s. com*/ **/ public void addPendingFriend(String guestLogin, String memberId, Hdlogin memberLoginInfo) throws BaseDaoException { /** * (loginId = memberId) (sending person) * (destloginId=guestLogin) (Receiving person, receives the request as a friend) */ if (RegexStrUtil.isNull(guestLogin) || RegexStrUtil.isNull(memberId)) { throw new BaseDaoException("params are null"); } /** * Get the guest's login information from guestLogin * the person sending the request, the order is important, do not remove this */ Hdlogin hdlogin = getLoginid(guestLogin); if (hdlogin == null) { throw new BaseDaoException("hdlogin is null for guestLogin " + guestLogin); } String guestId = hdlogin.getValue(DbConstants.LOGIN_ID); if (RegexStrUtil.isNull(guestId)) { throw new BaseDaoException("guestId is null for guestLogin " + guestLogin); } String gfname = hdlogin.getValue("fname"); String glname = hdlogin.getValue("lname"); String to = hdlogin.getValue("email"); /** * if already a friend, ignore it */ List prefResult = null; Object[] myParams = { (Object) guestId, (Object) memberId }; /** * Get scalability datasource for pendingfriends - not partitioned */ String sourceName = scalabilityManager.getWriteZeroScalability(); ds = scalabilityManager.getSource(sourceName); if (ds == null) { throw new BaseDaoException("ds null, addPendingfriend() " + sourceName); } String from = webconstants.getMailfrom(); /** * check if this entry exists for this user, do read_uncommitted also */ HashSet pendingSet = null; Connection conn = null; try { conn = ds.getConnection(); if (conn != null) { conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); pendingSet = listQuery.run(conn, memberId, guestId); conn.close(); } } catch (Exception e) { try { if (conn != null) { conn.close(); } } catch (Exception e1) { throw new BaseDaoException("connection close exception ", e1); } throw new BaseDaoException("error occured in db query", e); } /** * this entry already exists in the pendinglist, return */ if (!pendingSet.isEmpty()) { Iterator it1 = pendingSet.iterator(); Integer count = new Integer(((Pendingfriend) it1.next()).getValue("count(*)")); if (count > 0) { return; } } /** * add this friend */ try { addQuery.run(guestId, memberId); } catch (Exception e) { throw new BaseDaoException("error occured while adding a PendingfriendAddQuery()" + addQuery.getSql() + ", guestId = " + guestId + ", memberId = " + memberId, e); } /** * If the Email "to" field is missing, don't send email, just return */ if (!RegexStrUtil.isNull(to)) { /** * Get scalability datasource for hdprofile - partitioned on loginId */ sourceName = scalabilityManager.getReadScalability(guestId); ds = scalabilityManager.getSource(sourceName); if (ds == null) { throw new BaseDaoException("ds null, addPendingfriend() " + sourceName); } /** * check if the guest needs to be notified */ Object[] params = { (Object) guestId }; //params[0] = guestId; prefResult = null; try { prefResult = myprofileQuery.execute(params); } catch (Exception e) { throw new BaseDaoException("error occured while executing HdprofileQuery()" + myprofileQuery.getSql() + " guestId = " + params[0], e); } if (prefResult.size() == 1) { Hdprofile hdprofile = (Hdprofile) prefResult.get(0); if (hdprofile.getValue("informfd").equalsIgnoreCase("1")) { //memberId = memberLoginInfo.getValue(DbConstants.LOGIN_ID); String fname = memberLoginInfo.getValue(DbConstants.FIRST_NAME); String lname = memberLoginInfo.getValue(DbConstants.LAST_NAME); String subject = "Invitation for friendship with " + fname + " " + lname; String msg = "Hi, " + gfname + "\n" + fname + " " + lname + " has sent you " + "\n\n" + "Members webpage: " + webconstants.getHddomain() + "/userpage?member=" + memberLoginInfo.getValue(DbConstants.LOGIN) + webconstants.getMailfooter(); hdmail.sendEmail(to, subject, msg, from); } } } Fqn fqn = cacheUtil.fqn(DbConstants.USER_PAGE); if (treeCache.exists(fqn, guestLogin)) { treeCache.remove(fqn, guestLogin); } fqn = cacheUtil.fqn(DbConstants.USER_PAGE); if (treeCache.exists(fqn, memberLoginInfo.getValue(DbConstants.LOGIN))) { treeCache.remove(fqn, memberLoginInfo.getValue(DbConstants.LOGIN)); } }
From source file:org.opendatakit.common.persistence.engine.sqlserver.TaskLockImpl.java
private TaskLockTable doTransaction(TaskLockTable entity, long l) throws ODKEntityNotFoundException, ODKTaskLockException { boolean first; final List<String> stmts = new ArrayList<String>(); String uri = entity.getUri(); StringBuilder b = new StringBuilder(); String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME + K_BQ;//from w ww . j av a 2 s. c om b.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'"); String uriUserInline = b.toString(); b.setLength(0); b.append("'").append(uri.replaceAll("'", "''")).append("'"); String uriLockInline = b.toString(); b.setLength(0); b.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'"); String formIdInline = b.toString(); b.setLength(0); b.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'"); String taskTypeInline = b.toString(); b.setLength(0); b.append("DATEADD(ms,").append(l).append(",SYSUTCDATETIME())"); String nowPlusLifetimeIntervalMilliseconds = b.toString(); b.setLength(0); b.append("SELECT COUNT(1) FROM ").append(tableName).append(" WITH (TABLOCKX, HOLDLOCK)"); stmts.add(b.toString()); b.setLength(0); dam.recordPutUsage(TaskLockTable.TABLE_NAME); if (!entity.isFromDatabase()) { // insert a new record (prospective lock) b.append("INSERT 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("SYSUTCDATETIME()"); } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) { b.append(uriUserInline); } else if (f.equals(entity.formId)) { b.append(formIdInline); } else if (f.equals(entity.taskType)) { b.append(taskTypeInline); } else if (f.equals(entity.primaryKey)) { b.append(uriLockInline); } else if (f.equals(entity.expirationDateTime)) { b.append(nowPlusLifetimeIntervalMilliseconds); } else { throw new IllegalStateException("unexpected case " + f.getName()); } } b.append(")"); stmts.add(b.toString()); b.setLength(0); } else { // update existing record (prospective lock) 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("SYSUTCDATETIME()"); } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) { b.append(uriUserInline); } else if (f.equals(entity.formId)) { b.append(formIdInline); } else if (f.equals(entity.taskType)) { b.append(taskTypeInline); } else if (f.equals(entity.primaryKey)) { b.append(uriLockInline); } else if (f.equals(entity.expirationDateTime)) { b.append(nowPlusLifetimeIntervalMilliseconds); } 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(uriLockInline); stmts.add(b.toString()); b.setLength(0); } // delete stale locks (don't care who's) dam.recordDeleteUsage(TaskLockTable.TABLE_NAME); b.append("DELETE FROM ").append(tableName).append(" WHERE "); b.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= SYSUTCDATETIME()"); stmts.add(b.toString()); b.setLength(0); // delete prospective locks which are not the oldest for that resource and // task type dam.recordDeleteUsage(TaskLockTable.TABLE_NAME); b.append("DELETE FROM ").append(tableName).append(" WHERE "); b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline) .append(" AND "); b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline) .append(" AND "); b.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ); b.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ); b.append(") FROM ").append(tableName).append(" AS t3 WHERE t3."); b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline) .append(" AND t3."); b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline) .append(")"); stmts.add(b.toString()); b.setLength(0); // delete our entry if it collides with another entry with exactly // this time. b.append("DELETE FROM ").append(tableName).append(" WHERE "); b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline) .append(" AND "); b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline) .append(" AND "); b.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ").append(uriLockInline) .append(" AND "); b.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ); b.append(") FROM ").append(tableName).append(" AS t3 WHERE t3."); b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline) .append(" AND t3."); b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline) .append(")"); stmts.add(b.toString()); b.setLength(0); // assert: only the lock that holds the resource for that task type appears // in the task lock table TaskLockTable relation; try { JdbcTemplate jdbc = datastore.getJdbcConnection(); jdbc.execute(new ConnectionCallback<Object>() { @Override public Object doInConnection(Connection conn) throws SQLException, DataAccessException { boolean oldAutoCommitValue = conn.getAutoCommit(); int oldTransactionValue = conn.getTransactionIsolation(); try { conn.setAutoCommit(false); conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); Statement stmt = conn.createStatement(); for (String s : stmts) { // for debugging: LogFactory.getLog(TaskLockImpl.class).info(s); stmt.execute(s); } conn.commit(); } catch (Exception e) { e.printStackTrace(); conn.rollback(); } conn.setTransactionIsolation(oldTransactionValue); conn.setAutoCommit(oldAutoCommitValue); return null; } }); relation = TaskLockTable.assertRelation(datastore, user); } catch (Exception e) { throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e); } return (TaskLockTable) datastore.getEntity(relation, entity.getUri(), user); }
From source file:org.opendatakit.common.persistence.engine.pgres.TaskLockImpl.java
private TaskLockTable doTransaction(TaskLockTable entity, long l) throws ODKEntityNotFoundException, ODKTaskLockException { boolean first; final List<String> stmts = new ArrayList<String>(); String uri = entity.getUri(); StringBuilder b = new StringBuilder(); String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME + K_BQ;//ww w. j a va2 s . c om b.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'"); String uriUserInline = b.toString(); b.setLength(0); b.append("'").append(uri.replaceAll("'", "''")).append("'"); String uriLockInline = b.toString(); b.setLength(0); b.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'"); String formIdInline = b.toString(); b.setLength(0); b.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'"); String taskTypeInline = b.toString(); b.setLength(0); b.append("interval '").append(l).append(" milliseconds'"); String lifetimeIntervalMilliseconds = b.toString(); b.setLength(0); b.append("LOCK TABLE ").append(tableName).append(" IN ACCESS EXCLUSIVE MODE"); stmts.add(b.toString()); b.setLength(0); dam.recordPutUsage(TaskLockTable.TABLE_NAME); if (!entity.isFromDatabase()) { // insert a new record (prospective lock) b.append("INSERT 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("NOW()"); } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) { b.append(uriUserInline); } else if (f.equals(entity.formId)) { b.append(formIdInline); } else if (f.equals(entity.taskType)) { b.append(taskTypeInline); } else if (f.equals(entity.primaryKey)) { b.append(uriLockInline); } else if (f.equals(entity.expirationDateTime)) { b.append(" NOW() + "); b.append(lifetimeIntervalMilliseconds); } else { throw new IllegalStateException("unexpected case " + f.getName()); } } b.append(")"); stmts.add(b.toString()); b.setLength(0); } else { // update existing record (prospective lock) 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("NOW()"); } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) { b.append(uriUserInline); } else if (f.equals(entity.formId)) { b.append(formIdInline); } else if (f.equals(entity.taskType)) { b.append(taskTypeInline); } else if (f.equals(entity.primaryKey)) { b.append(uriLockInline); } else if (f.equals(entity.expirationDateTime)) { b.append(" NOW() + "); b.append(lifetimeIntervalMilliseconds); } 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(uriLockInline); stmts.add(b.toString()); b.setLength(0); } // delete stale locks (don't care who's) dam.recordDeleteUsage(TaskLockTable.TABLE_NAME); b.append("DELETE FROM ").append(tableName).append(" WHERE "); b.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= NOW()"); stmts.add(b.toString()); b.setLength(0); // delete prospective locks which are not the oldest for that resource and // task type dam.recordDeleteUsage(TaskLockTable.TABLE_NAME); b.append("DELETE FROM ").append(tableName).append(" WHERE "); b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline) .append(" AND "); b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline) .append(" AND "); b.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ); b.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ); b.append(") FROM ").append(tableName).append(" AS t3 WHERE t3."); b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline) .append(" AND t3."); b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline) .append(")"); stmts.add(b.toString()); b.setLength(0); // delete our entry if it collides with another entry with exactly // this time. b.append("DELETE FROM ").append(tableName).append(" WHERE "); b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline) .append(" AND "); b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline) .append(" AND "); b.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ").append(uriLockInline) .append(" AND "); b.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ); b.append(") FROM ").append(tableName).append(" AS t3 WHERE t3."); b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline) .append(" AND t3."); b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline) .append(")"); stmts.add(b.toString()); b.setLength(0); // assert: only the lock that holds the resource for that task type appears // in the task lock table TaskLockTable relation; try { JdbcTemplate jdbc = datastore.getJdbcConnection(); jdbc.execute(new ConnectionCallback<Object>() { @Override public Object doInConnection(Connection conn) throws SQLException, DataAccessException { boolean oldAutoCommitValue = conn.getAutoCommit(); int oldTransactionValue = conn.getTransactionIsolation(); try { conn.setAutoCommit(false); conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); Statement stmt = conn.createStatement(); for (String s : stmts) { // for debugging: LogFactory.getLog(TaskLockImpl.class).info(s); stmt.execute(s); } conn.commit(); } catch (Exception e) { e.printStackTrace(); conn.rollback(); } conn.setTransactionIsolation(oldTransactionValue); conn.setAutoCommit(oldAutoCommitValue); return null; } }); relation = TaskLockTable.assertRelation(datastore, user); } catch (Exception e) { throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e); } return (TaskLockTable) datastore.getEntity(relation, entity.getUri(), user); }