Example usage for java.sql ResultSet CLOSE_CURSORS_AT

Introduction

In this page you can find the example usage for java.sql ResultSet CLOSE_CURSORS_AT_COMMIT.

Prototype

int CLOSE_CURSORS_AT_COMMIT

To view the source code for java.sql ResultSet CLOSE_CURSORS_AT_COMMIT.

Click Source Link

Document

The constant indicating that open ResultSet objects with this holdability will be closed when the current transaction is committed.

Usage

From source file:org.kawanfw.sql.jdbc.ConnectionHttp.java

/**
 * Creates a <code>PreparedStatement</code> object for sending parameterized
 * SQL statements to the database./*from   w  ww.ja  v a  2 s .c o  m*/
 * <P>
 * A SQL statement with or without IN parameters can be pre-compiled and
 * stored in a <code>PreparedStatement</code> object. This object can then
 * be used to efficiently execute this statement multiple times.
 * 
 * <P>
 * <B>Note:</B> This method is optimized for handling parametric SQL
 * statements that benefit from precompilation. If the driver supports
 * precompilation, the method <code>prepareStatement</code> will send the
 * statement to the database for precompilation. Some drivers may not
 * support precompilation. In this case, the statement may not be sent to
 * the database until the <code>PreparedStatement</code> object is executed.
 * This has no direct effect on users; however, it does affect which methods
 * throw certain <code>SQLException</code> objects.
 * <P>
 * Result sets created using the returned <code>PreparedStatement</code>
 * object will by default be type <code>TYPE_FORWARD_ONLY</code> and have a
 * concurrency level of <code>CONCUR_READ_ONLY</code>.
 * 
 * @param sql
 *            an SQL statement that may contain one or more '?' IN parameter
 *            placeholders
 * @return a new default <code>PreparedStatement</code> object containing
 *         the pre-compiled SQL statement
 * @exception SQLException
 *                if a database access error occurs
 */
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
    testIfClosed();

    return new PreparedStatementHttp(this, sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
            ResultSet.CLOSE_CURSORS_AT_COMMIT);
}

From source file:org.kawanfw.sql.jdbc.ConnectionHttp.java

/**
 * Changes the default holdability of <code>ResultSet</code> objects created
 * using this <code>Connection</code> object to the given holdability. The
 * default holdability of <code>ResultSet</code> objects can be be
 * determined by invoking {@link DatabaseMetaData#getResultSetHoldability}.
 * /*w w w  .  j a v  a 2 s  . co  m*/
 * @param holdability
 *            a <code>ResultSet</code> holdability constant; one of
 *            <code>ResultSet.HOLD_CURSORS_OVER_COMMIT</code> or
 *            <code>ResultSet.CLOSE_CURSORS_AT_COMMIT</code>
 * @throws SQLException
 *             if a database access occurs, this method is called on a
 *             closed connection, or the given parameter is not a
 *             <code>ResultSet</code> constant indicating holdability
 * @exception SQLFeatureNotSupportedException
 *                if the given holdability is not supported
 * @see #getHoldability
 * @see DatabaseMetaData#getResultSetHoldability
 * @see ResultSet
 * @since 1.4
 */
@Override
public void setHoldability(int holdability) throws SQLException {
    testIfClosed();

    if (holdability != ResultSet.HOLD_CURSORS_OVER_COMMIT && holdability != ResultSet.CLOSE_CURSORS_AT_COMMIT) {
        throw new SQLException("Illegal holdability: " + holdability);
    }

    if (!statelessMode) {
        JdbcHttpTransactionTransfer jdbcHttpTransactionTransfer = new JdbcHttpTransactionTransfer(this,
                authenticationToken);
        jdbcHttpTransactionTransfer.setHoldability(holdability);
    } else {
        if (holdability == ResultSet.HOLD_CURSORS_OVER_COMMIT) {
            throw new SQLFeatureNotSupportedException(
                    "holdability ResultSet.HOLD_CURSORS_OVER_COMMIT is not supported");
        }
    }

    this.holdability = holdability;
}

From source file:org.kawanfw.sql.jdbc.PreparedStatementHttp.java

/**
 * Constructor for auto-generated keys/*from ww  w .jav a  2  s.  c o  m*/
 * 
 * @param connectionHttp
 *            The Http Connection
 * @param sql
 *            the sql statement to use
 * @param autoGeneratedKeys
 *            a flag indicating whether auto-generated keys should be
 *            returned; one of <code>Statement.RETURN_GENERATED_KEYS</code>
 *            or <code>Statement.NO_GENERATED_KEYS</code>
 * 
 * @throws SQLException
 */
public PreparedStatementHttp(ConnectionHttp connectionHttp, String sql, int autoGeneratedKeys)
        throws SQLException {

    super(connectionHttp, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
            ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (sql == null) {
        throw new SQLException("sql order is null!");
    }

    this.sql = sql.trim();

    statementHolder = new StatementHolder(sql, autoGeneratedKeys);
    statementHolder.setPreparedStatement(true);
    statementHolder.setHtmlEncodingOn(connectionHttp.isHtmlEncodingOn());

    localFilesStatement = new Vector<File>();
    remoteFilesStatement = new Vector<String>();
    // localInputStreams = new Vector<InputStream>();
    // localInputStreamLengths = new Vector<Long>();

}

From source file:org.kawanfw.sql.jdbc.PreparedStatementHttp.java

/**
 * Constructor for auto-generated keys//from  w  w w.  j a  va 2s. c  o m
 * 
 * @param connectionHttp
 *            The Http Connection
 * @param sql
 *            the sql statement to use
 * @param columnIndexes
 *            an array of column indexes indicating the columns that should
 *            be returned from the inserted row or rows
 * 
 * @throws SQLException
 */

public PreparedStatementHttp(ConnectionHttp connectionHttp, String sql, int[] columnIndexes)
        throws SQLException {
    super(connectionHttp, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
            ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (sql == null) {
        throw new SQLException("sql order is null!");
    }

    this.sql = sql.trim();

    statementHolder = new StatementHolder(sql, columnIndexes);
    statementHolder.setPreparedStatement(true);
    statementHolder.setHtmlEncodingOn(connectionHttp.isHtmlEncodingOn());

    localFilesStatement = new Vector<File>();
    remoteFilesStatement = new Vector<String>();
}

From source file:org.kawanfw.sql.jdbc.PreparedStatementHttp.java

/**
 * Constructor for auto-generated keys// w  w  w .j  a v  a2s.  c o  m
 * 
 * @param connectionHttp
 *            The Http Connection
 * @param sql
 *            the sql statement to use
 * @param columnNames
 *            an array of column names indicating the columns that should be
 *            returned from the inserted row or rows
 * 
 * @throws SQLException
 */

public PreparedStatementHttp(ConnectionHttp connectionHttp, String sql, String[] columnNames)
        throws SQLException {
    super(connectionHttp, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
            ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (sql == null) {
        throw new SQLException("sql order is null!");
    }

    this.sql = sql.trim();

    statementHolder = new StatementHolder(sql, columnNames);
    statementHolder.setPreparedStatement(true);
    statementHolder.setHtmlEncodingOn(connectionHttp.isHtmlEncodingOn());

    localFilesStatement = new Vector<File>();
    remoteFilesStatement = new Vector<String>();
}

From source file:org.kawanfw.sql.json.StatementHolder.java

/**
 * Constructor to use for autogenerated keys
 * /*ww  w  .java  2 s  .  c o m*/
 * @param sqlOrder
 *            the sql order
 * @param autoGeneratedKeys
 *            a flag indicating whether auto-generated keys should be
 *            returned; one of <code>Statement.RETURN_GENERATED_KEYS</code>
 *            or <code>Statement.NO_GENERATED_KEYS</code>
 */
public StatementHolder(String sqlOrder, int autoGeneratedKeys) throws SQLException {
    if (sqlOrder == null) {
        throw new IllegalArgumentException("sqlOrder is null!");
    }

    sqlOrder = sqlOrder.trim();

    sqlOrder = HtmlConverter.toHtml(sqlOrder);

    this.sql = sqlOrder;
    this.autoGeneratedKeys = autoGeneratedKeys;

    stP[StaParms.RESULT_SET_TYPE] = ResultSet.TYPE_FORWARD_ONLY;
    stP[StaParms.RESULT_SET_CONCURRENCY] = ResultSet.CONCUR_READ_ONLY;
    stP[StaParms.RESULT_SET_HOLDABILITY] = ResultSet.CLOSE_CURSORS_AT_COMMIT;
    stP[StaParms.IS_ESCAPE_PROCESSING] = -1;
}

From source file:org.kawanfw.sql.json.StatementHolder.java

/**
 * Main Constructor//from  w  ww. j  a  v a2s. co  m
 * 
 * @param sqlOrder
 *            the sql order
 * @param autoGeneratedKeys
 *            a flag indicating whether auto-generated keys should be
 *            returned; one of <code>Statement.RETURN_GENERATED_KEYS</code>
 *            or <code>Statement.NO_GENERATED_KEYS</code> the result set
 *            holdability
 */
public StatementHolder(String sqlOrder, int columnIndexes[]) throws SQLException {
    if (sqlOrder == null) {
        throw new IllegalArgumentException("sqlOrder is null!");
    }

    if (columnIndexes == null) {
        throw new IllegalArgumentException("columnIndexes is null!");
    }

    sqlOrder = sqlOrder.trim();

    sqlOrder = HtmlConverter.toHtml(sqlOrder);

    this.sql = sqlOrder;

    for (int i = 0; i < columnIndexes.length; i++) {
        columnIndexesAutogenerateKeys.add(columnIndexes[i]);
    }

    stP[StaParms.RESULT_SET_TYPE] = ResultSet.TYPE_FORWARD_ONLY;
    stP[StaParms.RESULT_SET_CONCURRENCY] = ResultSet.CONCUR_READ_ONLY;
    stP[StaParms.RESULT_SET_HOLDABILITY] = ResultSet.CLOSE_CURSORS_AT_COMMIT;
    stP[StaParms.IS_ESCAPE_PROCESSING] = -1;
}

From source file:org.kawanfw.sql.json.StatementHolder.java

/**
 * Main Constructor//from w w w.jav a 2  s  . c  o m
 * 
 * @param sqlOrder
 *            the sql order
 * @param autoGeneratedKeys
 *            a flag indicating whether auto-generated keys should be
 *            returned; one of <code>Statement.RETURN_GENERATED_KEYS</code>
 *            or <code>Statement.NO_GENERATED_KEYS</code> the result set
 *            holdability
 */
public StatementHolder(String sqlOrder, String columnNames[]) throws SQLException {
    if (sqlOrder == null) {
        throw new IllegalArgumentException("sqlOrder is null!");
    }

    if (columnNames == null) {
        throw new IllegalArgumentException("columnNames is null!");
    }

    sqlOrder = sqlOrder.trim();

    sqlOrder = HtmlConverter.toHtml(sqlOrder);

    this.sql = sqlOrder;

    for (int i = 0; i < columnNames.length; i++) {
        columnNamesAutogenerateKeys.add(columnNames[i]);
    }

    stP[StaParms.RESULT_SET_TYPE] = ResultSet.TYPE_FORWARD_ONLY;
    stP[StaParms.RESULT_SET_CONCURRENCY] = ResultSet.CONCUR_READ_ONLY;
    stP[StaParms.RESULT_SET_HOLDABILITY] = ResultSet.CLOSE_CURSORS_AT_COMMIT;
    stP[StaParms.IS_ESCAPE_PROCESSING] = -1;
}

From source file:org.opendatakit.common.persistence.engine.mysql.TaskLockImpl.java

private TaskLockTable doTransaction(TaskLockTable entity, long l)
        throws ODKEntityNotFoundException, ODKTaskLockException {
    boolean first;

    final List<String> stmts = new ArrayList<String>();

    final String uri = entity.getUri();
    StringBuilder b = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;/*from w ww.  j  ava 2s. co  m*/
    // String tableName= TaskLockTable.TABLE_NAME;

    b.append("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("START TRANSACTION WITH CONSISTENT SNAPSHOT");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("SET @present := NOW(),");
    b.append(" @uriUser := '");
    b.append(user.getUriUser().replaceAll("'", "''"));
    b.append("',");
    b.append(" @uriLock := '");
    b.append(uri.replaceAll("'", "''"));
    b.append("',");
    b.append(" @formId := '");
    b.append(entity.getFormId().replaceAll("'", "''"));
    b.append("',");
    b.append(" @taskType := '");
    b.append(entity.getTaskType().replaceAll("'", "''"));
    b.append("',");
    b.append(" @lifetimeMicroseconds := ");
    b.append(1000L * l);
    stmts.add(b.toString());
    b.setLength(0);
    b.append("LOCK TABLES ");
    b.append(tableName);
    b.append(" WRITE ");
    stmts.add(b.toString());
    b.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record
        b.append("REPLACE INTO ");
        b.append(tableName);
        b.append(" (");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (!first) {
                b.append(",");
            }
            first = false;
            b.append(K_BQ);
            b.append(f.getName());
            b.append(K_BQ);
        }
        first = true;
        b.append(") VALUES ( ");
        for (DataField f : entity.getFieldList()) {
            if (!first) {
                b.append(",");
            }
            first = false;
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                b.append("@present");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                b.append("@uriUser");
            } else if (f.equals(entity.formId)) {
                b.append("@formId");
            } else if (f.equals(entity.taskType)) {
                b.append("@taskType");
            } else if (f.equals(entity.primaryKey)) {
                b.append("@uriLock");
            } else if (f.equals(entity.expirationDateTime)) {
                b.append(" DATE_ADD(CAST(@present AS DATETIME), INTERVAL @lifetimeMicroseconds MICROSECOND)");
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        b.append(")");
        stmts.add(b.toString());
        b.setLength(0);
    } else {
        // update existing record
        b.append("UPDATE ");
        b.append(tableName);
        b.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                b.append(",");
            }
            first = false;
            b.append(K_BQ);
            b.append(f.getName());
            b.append(K_BQ);
            b.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                b.append("@present");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                b.append("@uriUser");
            } else if (f.equals(entity.formId)) {
                b.append("@formId");
            } else if (f.equals(entity.taskType)) {
                b.append("@taskType");
            } else if (f.equals(entity.primaryKey)) {
                b.append("@uriLock");
            } else if (f.equals(entity.expirationDateTime)) {
                b.append(" DATE_ADD(CAST(@present AS DATETIME), INTERVAL @lifetimeMicroseconds MICROSECOND)");
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        b.append(" WHERE ");
        b.append(K_BQ);
        b.append(entity.primaryKey.getName());
        b.append(K_BQ);
        b.append(" = ");
        b.append("@uriLock");
        stmts.add(b.toString());
        b.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    b.append("DELETE FROM ");
    b.append(tableName);
    b.append(" WHERE ");
    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" <= CAST(@present AS DATETIME)");
    stmts.add(b.toString());
    b.setLength(0);
    // determine the time of the oldest lock for this resource and task type...
    // do this by querying for the minimum expiration time
    // BUT, first, set the minimum time to the present time in case there are no locks.
    b.append("SET @minExpiration = @present");
    stmts.add(b.toString());
    b.setLength(0);
    dam.recordQueryUsage(TaskLockTable.TABLE_NAME, 1);
    b.append("SELECT @minExpiration := MIN(");
    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(") FROM ");
    b.append(tableName);
    b.append(" WHERE ");
    b.append(K_BQ);
    b.append(entity.formId.getName());
    b.append(K_BQ);
    b.append(" = @formId AND ");
    b.append(K_BQ);
    b.append(entity.taskType.getName());
    b.append(K_BQ);
    b.append(" = @taskType");
    stmts.add(b.toString());
    b.setLength(0);
    // determine if there are two or more records matching
    // the minimum expiration time.  If there are, we must
    // release the locks and retry, as we cannot determine
    // which one is first.
    b.append("SET @uriCount = 0");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("SELECT @uriCount := COUNT(");
    b.append(K_BQ);
    b.append(entity.primaryKey.getName());
    b.append(K_BQ);
    b.append(") FROM ");
    b.append(tableName);
    b.append(" WHERE ");

    b.append(K_BQ);
    b.append(entity.formId.getName());
    b.append(K_BQ);
    b.append(" = @formId AND ");

    b.append(K_BQ);
    b.append(entity.taskType.getName());
    b.append(K_BQ);
    b.append(" = @taskType AND ");

    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" = CAST(@minExpiration AS DATETIME)");
    stmts.add(b.toString());
    b.setLength(0);
    // delete all locks except the oldest one for this resource and task type...
    // or, if we have two or more old locks, release ours
    // whatever lock exists identifies the owner of the resource.
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    b.append("DELETE FROM ");
    b.append(tableName);
    b.append(" WHERE ");

    b.append(K_BQ);
    b.append(entity.formId.getName());
    b.append(K_BQ);
    b.append(" = @formId AND ");

    b.append(K_BQ);
    b.append(entity.taskType.getName());
    b.append(K_BQ);
    b.append(" = @taskType AND ( ");

    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" > CAST(@minExpiration AS DATETIME)");
    b.append(" OR ( @uriCount > 1 AND ");
    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" = CAST(@minExpiration AS DATETIME)");
    b.append(" AND ");
    b.append(K_BQ);
    b.append(entity.primaryKey.getName());
    b.append(K_BQ);
    b.append(" = @uriLock ) )");
    stmts.add(b.toString());
    b.setLength(0);
    // and within the transaction, see if the primary key of the winning lock is ours...
    b.append("SELECT ");
    b.append(K_BQ);
    b.append(entity.primaryKey.getName());
    b.append(K_BQ);
    b.append(" FROM ");
    b.append(tableName);
    b.append(" WHERE ");
    b.append(K_BQ);
    b.append(entity.formId.getName());
    b.append(K_BQ);
    b.append(" = @formId AND ");

    b.append(K_BQ);
    b.append(entity.taskType.getName());
    b.append(K_BQ);
    b.append(" = @taskType AND ");
    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" = CAST(@minExpiration AS DATETIME)");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("UNLOCK TABLES");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("COMMIT");
    stmts.add(b.toString());
    b.setLength(0);

    TaskLockTable relation;
    try {
        relation = TaskLockTable.assertRelation(datastore, user);
    } catch (ODKDatastoreException e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }

    boolean success = false;
    try {

        JdbcTemplate jdbc = datastore.getJdbcConnection();
        Object o = jdbc.execute(new ConnectionCallback<Object>() {

            @Override
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                boolean success = false;
                boolean oldValue = conn.getAutoCommit();
                try {
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    String lastResult = null;
                    for (String s : stmts) {
                        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
                        if (s.startsWith("SELECT")) {
                            ResultSet rs = stmt.executeQuery(s);
                            if (rs.first()) {
                                lastResult = rs.getString(1);
                            }
                            rs.close();
                        } else {
                            stmt.executeUpdate(s);
                        }
                        stmt.close();
                    }
                    conn.commit();
                    success = uri.equals(lastResult);
                } catch (Exception e) {
                    e.printStackTrace();
                    conn.rollback();
                } finally {
                    if (!success) {
                        Statement stmt = conn.createStatement();
                        LogFactory.getLog(TaskLockImpl.class).info("UNLOCK TABLES");
                        stmt.execute("UNLOCK TABLES");
                        conn.commit();
                    }
                }
                conn.setAutoCommit(oldValue);
                return success ? uri : null;
            }

        });

        success = o != null && uri.equals((String) o);

    } catch (Exception e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }
    if (success) {
        return (TaskLockTable) datastore.getEntity(relation, uri, user);
    } else {
        throw new ODKEntityNotFoundException();
    }
}

From source file:org.xenei.jdbc4sparql.J4SDatabaseMetaData.java

@Override
public boolean supportsResultSetType(final int arg0) throws SQLException {
    switch (arg0) {
    case ResultSet.TYPE_FORWARD_ONLY:
    case ResultSet.CONCUR_READ_ONLY:
    case ResultSet.TYPE_SCROLL_INSENSITIVE:
    case ResultSet.HOLD_CURSORS_OVER_COMMIT:
        return true;

    case ResultSet.CLOSE_CURSORS_AT_COMMIT:
    case ResultSet.CONCUR_UPDATABLE:
    case ResultSet.TYPE_SCROLL_SENSITIVE:
    default://from   w  ww .j  ava 2  s.  c  o  m
        return false;
    }
}

Example usage for java.sql ResultSet CLOSE_CURSORS_AT_COMMIT

Introduction

Prototype

Document

Usage