Example usage for java.sql Connection getAutoCommit

List of usage examples for java.sql Connection getAutoCommit

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. getAutoCommit

Introduction

In this page you can find the example usage for java.sql Connection getAutoCommit.

Prototype

boolean getAutoCommit() throws SQLException;

Source Link

Document

Retrieves the current auto-commit mode for this Connection object.

Usage

From source file:com.glaf.core.jdbc.connection.DBCPConnectionProvider.java

public Connection getConnection() throws SQLException {
    Connection connection = null;
    int count = 0;
    while (count < conf.getInt("jdbc.connection.retryCount", 10)) {
        try {/*from ww w  . ja v  a2  s.com*/
            connection = ds.getConnection();
            if (connection != null) {
                if (isolation != null) {
                    connection.setTransactionIsolation(isolation.intValue());
                }
                if (connection.getAutoCommit() != autocommit) {
                    connection.setAutoCommit(autocommit);
                }
                return connection;
            } else {
                count++;
                try {
                    Thread.sleep(conf.getInt("jdbc.connection.retryTimeMs", 500));
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException ex) {
            count++;
            try {
                Thread.sleep(conf.getInt("jdbc.connection.retryTimeMs", 500));
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            if (count >= conf.getInt("jdbc.connection.retryCount", 10)) {
                ex.printStackTrace();
                throw ex;
            }
        }
    }
    return connection;
}

From source file:org.cloudgraph.rdb.service.RDBGraphService.java

public SnapshotMap commit(DataGraph dataGraph, String username) {
    if (dataGraph == null)
        throw new IllegalArgumentException("expected non-null 'dataGraph' 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()));
    if (log.isDebugEnabled())
        log.debug("getting connection");
    Connection con = null;
    try {/*from   www.j a  v a  2 s  . c  om*/
        con = ProviderManager.instance().getConnection();
        if (con.getAutoCommit()) {
            if (log.isDebugEnabled())
                log.debug("turning off connection autocommit for 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() + " forgraph 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(dataGraph);
        con.commit();
        return snapshotMap;
    } catch (DataAccessException e) {
        if (log.isDebugEnabled())
            log.debug(e.getMessage(), e);
        try {
            con.rollback();
        } catch (SQLException e1) {
            log.error(e.getMessage(), e1);
        }
        throw e;
    } catch (Throwable t) {
        if (log.isDebugEnabled())
            log.debug(t.getMessage(), 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.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  w  w  . j av  a2 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;//from www  .  j  a  va 2  s.  co  m

    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);
}

From source file:com.taobao.datax.plugins.reader.postgrereader.PostgreReader.java

@Override
public int startRead(LineSender lineSender) {
    DBResultSetSender proxy = DBResultSetSender.newSender(lineSender);
    proxy.setMonitor(getMonitor());//from w  w  w . j  a va2 s . co  m
    proxy.setDateFormatMap(genDateFormatMap());

    String sql = param.getValue(ParamKey.sql);
    logger.info(String.format("PostgreReader start to query %s .", sql));
    ResultSet rs = null;
    Connection connection = null;
    try {
        connection = getCon();
        connection.setAutoCommit(false);
        Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        logger.info("set postgre statement fetch size");
        stmt.setFetchSize(fetchSize);
        logger.info("connection info autoCommit:" + connection.getAutoCommit() + " fetchSize"
                + stmt.getFetchSize());
        rs = DBUtils.query(stmt, sql);
        logger.info("get a ResultSet");
        proxy.sendToWriter(rs);
        logger.info("send to writer");
        proxy.flush();
        getMonitor().setStatus(PluginStatus.READ_OVER);
        return PluginStatus.SUCCESS.value();
    } catch (SQLException e) {
        logger.error(ExceptionTracker.trace(e));
        throw new DataExchangeException(e);
    } finally {
        if (null != rs) {
            DBUtils.closeResultSet(rs);
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception ignore) {
            }
        }
    }

}

From source file:org.sakaiproject.announcement.impl.DbAnnouncementService.java

/**
 * fill in the draft and owner db fields
 *//*  ww  w . j  a va  2s. c  o m*/
protected void convertToDraft() {
    M_log.info("convertToDraft");

    try {
        // get a connection
        final Connection connection = m_sqlService.borrowConnection();
        boolean wasCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);

        // read all message records that need conversion
        String sql = "select CHANNEL_ID, MESSAGE_ID, XML from " + m_rTableName /* + " where OWNER is null" */;
        m_sqlService.dbRead(connection, sql, null, new SqlReader() {
            private int count = 0;

            public Object readSqlResultRecord(ResultSet result) {
                try {
                    // create the Resource from the db xml
                    String channelId = result.getString(1);
                    String messageId = result.getString(2);
                    String xml = result.getString(3);

                    // read the xml
                    Document doc = Xml.readDocumentFromString(xml);

                    // verify the root element
                    Element root = doc.getDocumentElement();
                    if (!root.getTagName().equals("message")) {
                        M_log.warn("convertToDraft(): XML root element not message: " + root.getTagName());
                        return null;
                    }
                    Message m = new BaseMessageEdit(null, root);

                    // pick up the fields
                    String owner = m.getHeader().getFrom().getId();
                    boolean draft = m.getHeader().getDraft();

                    // update
                    String update = "update " + m_rTableName
                            + " set OWNER = ?, DRAFT = ? where CHANNEL_ID = ? and MESSAGE_ID = ?";
                    Object fields[] = new Object[4];
                    fields[0] = owner;
                    fields[1] = (draft ? "1" : "0");
                    fields[2] = channelId;
                    fields[3] = messageId;
                    boolean ok = m_sqlService.dbWrite(connection, update, fields);

                    if (!ok)
                        M_log.info("convertToDraft: channel: " + channelId + " message: " + messageId
                                + " owner: " + owner + " draft: " + draft + " ok: " + ok);

                    count++;
                    if (count % 100 == 0) {
                        M_log.info("convertToDraft: " + count);
                    }
                    return null;
                } catch (Throwable ignore) {
                    return null;
                }
            }
        });

        connection.commit();
        connection.setAutoCommit(wasCommit);
        m_sqlService.returnConnection(connection);
    } catch (Throwable t) {
        M_log.warn("convertToDraft: failed: " + t);
    }

    M_log.info("convertToDraft: done");
}

From source file:org.opendatakit.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 stringBuilder = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;/*  ww w.  j a  v  a  2 s  .co m*/

    stringBuilder.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'");
    String uriUserInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(uri.replaceAll("'", "''")).append("'");
    String uriLockInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'");
    String formIdInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'");
    String taskTypeInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("interval '").append(l).append(" milliseconds'");
    String lifetimeIntervalMilliseconds = stringBuilder.toString();
    stringBuilder.setLength(0);

    stringBuilder.append("LOCK TABLE ").append(tableName).append(" IN ACCESS EXCLUSIVE MODE");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record (prospective lock)
        stringBuilder.append("INSERT INTO ");
        stringBuilder.append(tableName);
        stringBuilder.append(" (");
        first = true;
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(dataField.getName());
            stringBuilder.append(K_BQ);
        }
        first = true;
        stringBuilder.append(") VALUES ( ");
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            if (dataField.equals(entity.creationDate) || dataField.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (dataField.equals(entity.creatorUriUser) || dataField.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (dataField.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (dataField.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (dataField.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (dataField.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + dataField.getName());
            }
        }
        stringBuilder.append(")");
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    } else {
        // update existing record (prospective lock)
        stringBuilder.append("UPDATE ");
        stringBuilder.append(tableName);
        stringBuilder.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(f.getName());
            stringBuilder.append(K_BQ);
            stringBuilder.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        stringBuilder.append(" WHERE ");
        stringBuilder.append(K_BQ);
        stringBuilder.append(entity.primaryKey.getName());
        stringBuilder.append(K_BQ);
        stringBuilder.append(" = ");
        stringBuilder.append(uriLockInline);
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= NOW()");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete prospective locks which are not the oldest for that resource and
    // task type
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    stringBuilder.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete our entry if it collides with another entry with exactly 
    // this time.
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ")
            .append(uriLockInline).append(" AND ");
    stringBuilder.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.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 (PSQLException e) {
                    e.printStackTrace();
                    conn.rollback();
                } 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:com.excilys.ebi.bank.jdbc.SimpleBatchResourceDatabasePopulator.java

/**
 * Execute the given SQL script./* w w  w.  ja v  a  2 s  .c om*/
 * <p>
 * The script will normally be loaded by classpath. There should be one
 * statement per line. Any {@link #setSeparator(String) statement
 * separators} will be removed.
 * <p>
 * <b>Do not use this method to execute DDL if you expect rollback.</b>
 *
 * @param connection
 *            the JDBC Connection with which to perform JDBC operations
 * @param resource
 *            the resource (potentially associated with a specific encoding)
 *            to load the SQL script from
 * @param continueOnError
 *            whether or not to continue without throwing an exception in
 *            the event of an error
 * @param ignoreFailedDrops
 *            whether of not to continue in the event of specifically an
 *            error on a <code>DROP</code>
 */
private void executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError,
        boolean ignoreFailedDrops) throws SQLException, IOException {

    if (LOGGER.isInfoEnabled()) {
        LOGGER.info("Executing SQL script from " + resource);
    }

    long startTime = System.currentTimeMillis();
    Iterator<String> statements = IOUtils.lineIterator(resource.getReader());
    int lineNumber = 0;

    boolean initialAutoCommitState = connection.getAutoCommit();

    connection.setAutoCommit(false);
    Statement stmt = connection.createStatement();
    try {
        while (statements.hasNext()) {
            String statement = statements.next();
            lineNumber++;
            try {
                stmt.addBatch(statement);

                if (lineNumber % batchSize == 0) {
                    stmt.executeBatch();
                    connection.commit();
                }
            } catch (SQLException ex) {
                boolean dropStatement = StringUtils.startsWithIgnoreCase(statement.trim(), "drop");
                if (continueOnError || (dropStatement && ignoreFailedDrops)) {
                    if (LOGGER.isDebugEnabled()) {
                        LOGGER.debug("Failed to execute SQL script statement at line " + lineNumber
                                + " of resource " + resource + ": " + statement, ex);
                    }
                } else {
                    Exception nextException = ex.getNextException();
                    throw new ScriptStatementFailedException(statement, lineNumber, resource,
                            nextException != null ? nextException : ex);
                }
            }
        }
    } finally {
        stmt.executeBatch();
        connection.commit();

        connection.setAutoCommit(initialAutoCommitState);

        try {
            stmt.close();
        } catch (Throwable ex) {
            LOGGER.debug("Could not close JDBC Statement", ex);
        }
    }
    long elapsedTime = System.currentTimeMillis() - startTime;
    if (LOGGER.isInfoEnabled()) {
        LOGGER.info("Done executing SQL script from " + resource + " in " + elapsedTime + " ms.");
    }
}

From source file:org.moneta.dao.SqlSelectExecutor.java

public SearchResult call() {
    SearchResult result = new SearchResult();
    Connection topicConnection = null;
    QueryRunner runner = new QueryRunner();

    try {//from w w w .  jav  a  2s  .  c o m
        topicConnection = MonetaEnvironment.getConfiguration().getConnection(topic.getDataSourceName());

        RecordResultSetHandler handler = new RecordResultSetHandler();
        handler.setMaxRows(this.getMaxRows());
        handler.setStartRow(this.getStartRow());
        handler.getAliasMap().putAll(topic.getAliasMap());

        result.setResultData(runner.query(topicConnection, sqlStmt.getSqlText(), handler,
                sqlStmt.getHostVariableValueList().toArray()));

        if (topicConnection.getAutoCommit()) {
            DbUtils.closeQuietly(topicConnection);
        } else {
            DbUtils.commitAndCloseQuietly(topicConnection);
        }
    } catch (Exception e) {
        result.setErrorCode(500);
        result.setErrorMessage(ExceptionUtils.getStackTrace(e));
        DbUtils.rollbackAndCloseQuietly(topicConnection);
    }

    return result;

}

From source file:org.etudes.jforum.view.install.InstallAction.java

private boolean importTablesData(Connection conn) throws Exception {
    boolean status = true;
    boolean autoCommit = conn.getAutoCommit();
    conn.setAutoCommit(false);// ww w  .  ja v a  2  s . c o  m

    String dbType = this.getFromSession("database");

    if (dbType.startsWith("mysql")) {
        dbType = "mysql";
    }

    List statements = this
            .readFromDat(SystemGlobals.getApplicationResourceDir() + "/setup-files/" + dbType + "_dump.dat");
    for (Iterator iter = statements.iterator(); iter.hasNext();) {
        String query = (String) iter.next();
        if (query == null || "".equals(query.trim())) {
            continue;
        }

        query = query.trim();

        Statement s = conn.createStatement();

        try {
            if (query.startsWith("UPDATE") || query.startsWith("INSERT") || query.startsWith("SET")) {
                s.executeUpdate(query);
            } else if (query.startsWith("SELECT")) {
                s.executeQuery(query);
            } else {
                throw new Exception("Invalid query: " + query);
            }
        } catch (SQLException ex) {
            status = false;
            conn.rollback();
            logger.error("Error importing data for " + query + ": " + ex);
            this.context.put("exceptionMessage", ex.getMessage() + "\n" + query);
            break;
        } finally {
            s.close();
        }
    }

    conn.setAutoCommit(autoCommit);
    return status;
}