Example usage for java.sql Connection setAutoCommit

List of usage examples for java.sql Connection setAutoCommit

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

Introduction

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

Prototype

void setAutoCommit(boolean autoCommit) throws SQLException;

Source Link

Document

Sets this connection's auto-commit mode to the given state.

Usage

From source file:com.cws.esolutions.core.dao.impl.ApplicationDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#getApplicationsByAttribute(java.lang.String, int)
 *///from  ww w. j av  a  2 s  . c o  m
public synchronized List<Object[]> getApplicationsByAttribute(final String value, final int startRow)
        throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#getApplicationsByAttribute(final String value, final int startRow) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", value);
        DEBUGGER.debug("Value: {}", startRow);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        StringBuilder sBuilder = new StringBuilder();

        if (StringUtils.split(value, " ").length >= 2) {
            for (String str : StringUtils.split(value, " ")) {
                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", str);
                }

                sBuilder.append("+" + str);
                sBuilder.append(" ");
            }

            if (DEBUG) {
                DEBUGGER.debug("StringBuilder: {}", sBuilder);
            }
        } else {
            sBuilder.append("+" + value);
        }

        stmt = sqlConn.prepareCall("{CALL getApplicationByAttribute(?, ?)}");
        stmt.setString(1, sBuilder.toString().trim());
        stmt.setInt(2, startRow);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("resultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<Object[]>();

                while (resultSet.next()) {
                    Object[] data = new Object[] { resultSet.getString(1), // GUID
                            resultSet.getString(2), // NAME
                            resultSet.getInt(3) / 0 * 100 // score
                    };

                    if (DEBUG) {
                        DEBUGGER.debug("Value: {}", data);
                    }

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return responseData;
}

From source file:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#obtainSecurityData(java.lang.String, java.lang.String)
 *///from w  w w.j a  va2 s .  co m
public synchronized List<String> obtainSecurityData(final String userName, final String userGuid)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#obtainSecurityData(final String userName, final String userGuid) throws AuthenticatorException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userName);
        DEBUGGER.debug("Value: {}", userGuid);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> userSecurity = null;

    try {
        sqlConn = SQLAuthenticator.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL getUserByAttribute(?, ?)}");
        stmt.setString(1, userName); // guid
        stmt.setInt(2, 0); // count

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();

                while (resultSet.next()) {
                    if (StringUtils.equals(resultSet.getString(2), userName)) {
                        String cn = resultSet.getString(1);
                        String username = resultSet.getString(2);

                        if (DEBUG) {
                            DEBUGGER.debug("String: {}", cn);
                            DEBUGGER.debug("String: {}", username);
                        }

                        resultSet.close();
                        stmt.close();

                        // found the user we want
                        stmt = sqlConn.prepareCall("{ CALL getSecurityQuestions(?, ?) }");
                        stmt.setString(1, username); // common name
                        stmt.setString(2, cn);

                        if (DEBUG) {
                            DEBUGGER.debug("CallableStatement: {}", stmt);
                        }

                        if (stmt.execute()) {
                            resultSet = stmt.getResultSet();

                            if (DEBUG) {
                                DEBUGGER.debug("ResultSet: {}", resultSet);
                            }

                            if (resultSet.next()) {
                                userSecurity = new ArrayList<String>(
                                        Arrays.asList(resultSet.getString(1), resultSet.getString(2)));

                                if (DEBUG) {
                                    DEBUGGER.debug("userSecurity: {}", userSecurity);
                                }
                            }
                        }
                    }
                }

            }
        }
    } catch (SQLException sqx) {
        throw new AuthenticatorException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new AuthenticatorException(sqx.getMessage(), sqx);
        }
    }

    return userSecurity;
}

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;/*from   w  w  w.ja 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:dk.netarkivet.harvester.datamodel.extendedfield.ExtendedFieldDBDAO.java

@Override
public synchronized void create(ExtendedField aExtendedField) {
    ArgumentNotValid.checkNotNull(aExtendedField, "aExtendedField");

    Connection connection = HarvestDBConnection.get();
    if (aExtendedField.getExtendedFieldID() != null) {
        log.warn("The extendedFieldID for this extended Field is "
                + "already set. This should probably never happen.");
    } else {/* w  w  w.  j a v  a 2s.  co m*/
        aExtendedField.setExtendedFieldID(generateNextID(connection));
    }

    log.debug("Creating " + aExtendedField.toString());

    PreparedStatement statement = null;
    try {
        connection.setAutoCommit(false);
        statement = connection
                .prepareStatement("" + "INSERT INTO extendedfield " + "            (extendedfield_id, "
                        + "             extendedfieldtype_id, " + "             name, "
                        + "             format, " + "             defaultvalue, " + "             options, "
                        + "             datatype, " + "             mandatory, " + "             sequencenr, "
                        + "             maxlen) " + "VALUES      (?, " + "             ?, " + "             ?, "
                        + "             ?, " + "             ?, " + "             ?, " + "             ?, "
                        + "             ?, " + "             ?, " + "             ?) ");

        statement.setLong(1, aExtendedField.getExtendedFieldID());
        statement.setLong(2, aExtendedField.getExtendedFieldTypeID());
        statement.setString(3, aExtendedField.getName());
        statement.setString(4, aExtendedField.getFormattingPattern());
        statement.setString(5, aExtendedField.getDefaultValue());
        statement.setString(6, aExtendedField.getOptions());
        statement.setInt(7, aExtendedField.getDatatype());
        // the following conversion from boolean to int is necessary, 
        // because the database column 'mandatory' is a integer field 
        // and not a boolean (NAS-2127)
        statement.setInt(8, aExtendedField.isMandatory() ? 1 : 0);
        statement.setInt(9, aExtendedField.getSequencenr());
        statement.setInt(10, aExtendedField.getMaxlen());
        //TODO
        log.debug(statement.toString());

        statement.executeUpdate();
        connection.commit();
    } catch (SQLException e) {
        String message = "SQL error creating extended field " + aExtendedField + " in database" + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } finally {
        DBUtils.closeStatementIfOpen(statement);
        DBUtils.rollbackIfNeeded(connection, "create extended field", aExtendedField);
        HarvestDBConnection.release(connection);
    }
}

From source file:com.photon.phresco.plugin.commons.PluginUtils.java

public void executeSql(SettingsInfo info, File basedir, String filePath, String fileName)
        throws PhrescoException {
    initDriverMap();/*w  ww  . j a va 2s  .  com*/
    String host = info.getPropertyInfo(Constants.DB_HOST).getValue();
    String port = info.getPropertyInfo(Constants.DB_PORT).getValue();
    String userName = info.getPropertyInfo(Constants.DB_USERNAME).getValue();
    String password = info.getPropertyInfo(Constants.DB_PASSWORD).getValue();
    String databaseName = info.getPropertyInfo(Constants.DB_NAME).getValue();
    String databaseType = info.getPropertyInfo(Constants.DB_TYPE).getValue();
    String version = info.getPropertyInfo(Constants.DB_VERSION).getValue();
    String connectionProtocol = findConnectionProtocol(databaseType, host, port, databaseName);
    Connection con = null;
    FileInputStream file = null;
    Statement st = null;
    try {
        Class.forName(getDbDriver(databaseType)).newInstance();
        file = new FileInputStream(basedir.getPath() + filePath + databaseType.toLowerCase() + File.separator
                + version + fileName);
        Scanner s = new Scanner(file);
        s.useDelimiter("(;(\r)?\n)|(--\n)");
        con = DriverManager.getConnection(connectionProtocol, userName, password);
        con.setAutoCommit(false);
        st = con.createStatement();
        while (s.hasNext()) {
            String line = s.next().trim();
            if (databaseType.equals("oracle")) {
                if (line.startsWith("--")) {
                    String comment = line.substring(line.indexOf("--"), line.lastIndexOf("--"));
                    line = line.replace(comment, "");
                    line = line.replace("--", "");
                }
                if (line.startsWith(Constants.REM_DELIMETER)) {
                    String comment = line.substring(0, line.lastIndexOf("\n"));
                    line = line.replace(comment, "");
                }
            }
            if (line.startsWith("/*!") && line.endsWith("*/")) {
                line = line.substring(line.indexOf("/*"), line.indexOf("*/") + 2);
            }
            if (line.trim().length() > 0) {
                st.execute(line);
            }
        }
    } catch (SQLException e) {
        throw new PhrescoException(e);
    } catch (FileNotFoundException e) {
        throw new PhrescoException(e);
    } catch (Exception e) {
        throw new PhrescoException(e);
    } finally {
        try {
            if (con != null) {
                con.commit();
                con.close();
            }
            if (file != null) {
                file.close();
            }
        } catch (Exception e) {
            throw new PhrescoException(e);
        }
    }
}

From source file:com.wso2telco.workflow.dao.WorkflowDbService.java

/**
 * Application entry.//from   w w w. j a v a  2  s.  co  m
 *
 * @param applicationid the applicationid
 * @param operators     the operators
 * @return the integer
 * @throws Exception the exception
 */

public void applicationEntry(int applicationid, Integer[] operators) throws SQLException, BusinessException {

    Connection con = null;
    Statement st = null;

    try {
        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);

        if (con == null) {
            throw new Exception("Connection not found");
        }
        con.setAutoCommit(false);
        st = con.createStatement();
        for (Integer d : operators) {
            if (!operatorAppsIsExist(applicationid, d)) {
                StringBuilder query = new StringBuilder();
                query.append("INSERT INTO operatorapps (applicationid,operatorid) ");
                query.append("VALUES (" + applicationid + "," + d + ")");
                st.addBatch(query.toString());
            }
        }
        st.executeBatch();
        con.commit();

    } catch (SQLException e) {
        throw new SQLException();
    } catch (Exception e) {
        throw new BusinessException(GenaralError.UNDEFINED);
    } finally {
        DbUtils.closeAllConnections(st, con, null);
    }

}

From source file:hoot.services.controllers.osm.ChangesetResource.java

/**
 * Service method endpoint for uploading OSM changeset diff data
 * /*  www  .  ja v a  2  s  .  c om*/
 * @param changeset OSM changeset diff data
 * @param changesetId ID of the changeset being uploaded; changeset with the ID must already exist
 * @return response acknowledging the result of the update operation with updated entity ID 
 * information
 * @throws Exception
 * @see http://wiki.openstreetmap.org/wiki/API_0.6 and 
 * http://wiki.openstreetmap.org/wiki/OsmChange
 * @todo why can't I pass in changesetDiff as an XML doc instead of a string?
 */
@POST
@Path("/{changesetId}/upload")
@Consumes(MediaType.TEXT_XML)
@Produces(MediaType.TEXT_XML)
public Response upload(final String changeset, @PathParam("changesetId") final long changesetId,
        @QueryParam("mapId") final String mapId) throws Exception {
    Connection conn = DbUtils.createConnection();
    Document changesetUploadResponse = null;
    try {
        log.debug("Intializing database connection...");

        log.debug("Intializing changeset upload transaction...");
        TransactionStatus transactionStatus = transactionManager
                .getTransaction(new DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRED));
        conn.setAutoCommit(false);

        try {
            if (mapId == null) {
                throw new Exception("Invalid map id.");
            }
            long mapid = Long.parseLong(mapId);
            changesetUploadResponse = (new ChangesetDbWriter(conn)).write(mapid, changesetId, changeset);
        } catch (Exception e) {
            log.error("Rolling back transaction for changeset upload...");
            transactionManager.rollback(transactionStatus);
            conn.rollback();
            handleError(e, changesetId, StringUtils.abbreviate(changeset, 100));
        }

        log.debug("Committing changeset upload transaction...");
        transactionManager.commit(transactionStatus);
        conn.commit();
    } finally {
        conn.setAutoCommit(true);
        DbUtils.closeConnection(conn);
    }

    log.debug("Returning changeset upload response: "
            + StringUtils.abbreviate(XmlDocumentBuilder.toString(changesetUploadResponse), 100) + " ...");
    return Response.ok(new DOMSource(changesetUploadResponse), MediaType.TEXT_XML)
            .header("Content-type", MediaType.TEXT_XML).build();
}

From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#listServicesForGroup(java.lang.String)
 *//* w  w w.  j  a  va  2s.co m*/
public synchronized List<String> listServicesForGroup(final String groupName) throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME
            + "#listServicesForGroup(final String groupName) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", groupName);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> serviceList = null;

    try {
        sqlConn = dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL listServicesForGroup(?)}");
        stmt.setString(1, groupName);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.first();
                serviceList = new ArrayList<String>();

                for (String service : StringUtils.split(resultSet.getString(1), ",")) // single row response
                {
                    if (DEBUG) {
                        DEBUGGER.debug("Service: {}", service);
                    }

                    serviceList.add(service);
                }

                if (DEBUG) {
                    DEBUGGER.debug("List<String>: {}", serviceList);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return serviceList;
}

From source file:com.yahoo.ycsb.db.JdbcDBClient.java

@Override
public void init() throws DBException {
    if (initialized) {
        System.err.println("Client connection already initialized.");
        return;//from ww  w  .j a v a  2  s  .  co  m
    }
    props = getProperties();
    String urls = props.getProperty(CONNECTION_URL, DEFAULT_PROP);
    String user = props.getProperty(CONNECTION_USER, DEFAULT_PROP);
    String passwd = props.getProperty(CONNECTION_PASSWD, DEFAULT_PROP);
    String driver = props.getProperty(DRIVER_CLASS);

    String jdbcFetchSizeStr = props.getProperty(JDBC_FETCH_SIZE);
    if (jdbcFetchSizeStr != null) {
        try {
            this.jdbcFetchSize = Integer.parseInt(jdbcFetchSizeStr);
        } catch (NumberFormatException nfe) {
            System.err.println("Invalid JDBC fetch size specified: " + jdbcFetchSizeStr);
            throw new DBException(nfe);
        }
    }

    String autoCommitStr = props.getProperty(JDBC_AUTO_COMMIT, Boolean.TRUE.toString());
    Boolean autoCommit = Boolean.parseBoolean(autoCommitStr);

    String isCitusStr = props.getProperty(CITUS_ENABLED, Boolean.FALSE.toString());
    isCitus = Boolean.parseBoolean(isCitusStr);

    try {
        if (driver != null) {
            Class.forName(driver);
        }
        int shardCount = 0;
        conns = new ArrayList<Connection>(3);
        for (String url : urls.split(",")) {
            System.out.println("Adding shard node URL: " + url);
            Connection conn = DriverManager.getConnection(url, user, passwd);

            // Since there is no explicit commit method in the DB interface, all
            // operations should auto commit, except when explicitly told not to
            // (this is necessary in cases such as for PostgreSQL when running a
            // scan workload with fetchSize)
            conn.setAutoCommit(autoCommit);

            if (isCitus) {
                Statement stmt = conn.createStatement();
                stmt.execute("SET citusdb.task_executor_type TO 'router'");
            }

            shardCount++;
            conns.add(conn);
        }

        System.out.println("Using " + shardCount + " shards");

    } catch (ClassNotFoundException e) {
        System.err.println("Error in initializing the JDBS driver: " + e);
        throw new DBException(e);
    } catch (SQLException e) {
        System.err.println("Error in database operation: " + e);
        throw new DBException(e);
    } catch (NumberFormatException e) {
        System.err.println("Invalid value for fieldcount property. " + e);
        throw new DBException(e);
    }
    initialized = true;
}

From source file:com.uber.stream.kafka.chaperone.collector.reporter.DbAuditReporter.java

public int removeOldRecord() {
    Connection conn = null;
    PreparedStatement stmt = null;
    int affected = 0;
    long ts = System.currentTimeMillis() - auditDbRetentionMs;
    try {//from   w w w  .  j  a  v  a2  s  .  co m
        logger.info("Start to remove old records per timestamp={} with retentionMs={}", ts, auditDbRetentionMs);

        conn = getConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(String.format(DELETE_METRICS_SQL, dataTableName));

        Timestamp dbTs = new Timestamp(ts);
        stmt.setTimestamp(1, dbTs);
        affected = stmt.executeUpdate();
        conn.commit();

        REMOVED_RECORDS_COUNTER.mark(affected);
        logger.info("Removed count={} old records per timestamp={} with retentionMs={}", affected, ts,
                auditDbRetentionMs);
    } catch (Exception e) {
        logger.warn("Got exception to remove old records", e);
        FAILED_TO_REMOVE_COUNTER.mark();
        rollback(conn);
    } finally {
        closeDbResource(null, stmt, conn);
    }

    return affected;
}