Example usage for java.sql ResultSet TYPE_SCROLL

Introduction

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

Prototype

int TYPE_SCROLL_INSENSITIVE

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

Click Source Link

Document

The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.

Usage

From source file:org.riversun.d6.core.D6Crud.java

/**
 * Execute the SQL for number search<br>
 * //w ww . j a  v a 2s. c om
 * @param preparedSql
 * @param searchKeys
 * @return number of result
 */
public int execSelectCount(String preparedSql, Object[] searchKeys) {
    log("#execSelectCount preparedSql=" + preparedSql + " searchKeys=" + searchKeys);
    int retVal = 0;

    PreparedStatement preparedStmt = null;
    ResultSet rs = null;

    final Connection conn = createConnection();

    try {

        preparedStmt = conn.prepareStatement(preparedSql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        final StringBuilder logSb = new StringBuilder();

        if (searchKeys != null) {
            logSb.append("/ ");
            for (int i = 0; i < searchKeys.length; i++) {

                setObject((i + 1), preparedStmt, searchKeys[i]);

                logSb.append("key(" + (i + 1) + ")=" + searchKeys[i]);
                logSb.append(" ");
            }
        }

        log("#execSelectCount SQL=" + preparedSql + " " + logSb.toString());

        // execute SQL
        rs = preparedStmt.executeQuery();

        while (rs.next()) {
            retVal = rs.getInt(1);
        }

    } catch (Exception e) {
        loge("#execSelectCount", e);

    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (preparedStmt != null) {
                preparedStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {

            loge("#execSelectCount", e);
        }
    }
    return retVal;

}

From source file:com.netspective.axiom.sql.StoredProcedure.java

/**
 * Executes the stored procedure and records different statistics such as database connection times,
 * parameetr binding times, and procedure execution times.
 *
 * @param overrideIndexes parameter indexes to override
 * @param overrideValues  parameter override values
 */// w  w  w .  j  a va2s  .com
protected QueryResultSet executeAndRecordStatistics(ConnectionContext cc, int[] overrideIndexes,
        Object[] overrideValues, boolean scrollable) throws NamingException, SQLException {
    if (log.isTraceEnabled())
        trace(cc, overrideIndexes, overrideValues);
    QueryExecutionLogEntry logEntry = execLog.createNewEntry(cc, this.getQualifiedName());
    Connection conn = null;
    CallableStatement stmt = null;
    boolean closeConnection = true;
    try {
        logEntry.registerGetConnectionBegin();
        conn = cc.getConnection();
        logEntry.registerGetConnectionEnd(conn);
        String sql = StringUtils.strip(getSqlText(cc));
        if (scrollable)
            stmt = conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        else
            stmt = conn.prepareCall(sql);

        logEntry.registerBindParamsBegin();

        if (parameters != null) {
            parameters.apply(cc, stmt, overrideIndexes, overrideValues);
            logEntry.registerBindParamsEnd();

            logEntry.registerExecSqlBegin();
            stmt.execute();
            logEntry.registerExecSqlEndSuccess();
            parameters.extract(cc, stmt);
            StoredProcedureParameter rsParameter = parameters.getResultSetParameter();
            if (rsParameter != null) {
                closeConnection = false;
                Value val = rsParameter.getValue().getValue(cc.getDatabaseValueContext());
                return (QueryResultSet) val.getValue();
            } else
                return null;
        } else {
            logEntry.registerExecSqlBegin();
            stmt.execute();
            logEntry.registerExecSqlEndSuccess();
            return null;
        }
    } catch (SQLException e) {
        logEntry.registerExecSqlEndFailed();
        log.error(createExceptionMessage(cc, overrideIndexes, overrideValues), e);
        throw e;
    }
}

From source file:ro.nextreports.engine.queryexec.QueryExecutor.java

private PreparedStatement createStatement(String queryString) throws QueryException {
    // create the prepared statement
    PreparedStatement pstmt;//  w  w w.  java  2  s .  c  o m
    try {

        boolean hasScrollType = false;
        try {
            hasScrollType = DialectUtil.isSupportedResultSetType(conn, ResultSet.TYPE_SCROLL_INSENSITIVE);
        } catch (Exception ex) {
            ex.printStackTrace();
            LOG.error(ex.getMessage(), ex);
        }
        int resultSetType = hasScrollType ? ResultSet.TYPE_SCROLL_INSENSITIVE : ResultSet.TYPE_FORWARD_ONLY;

        if (QueryUtil.isProcedureCall(queryString)) {
            pstmt = conn.prepareCall("{" + queryString + "}", resultSetType, ResultSet.CONCUR_READ_ONLY);
        } else {
            if (isCsv) {
                pstmt = conn.prepareStatement(queryString);
            } else {
                boolean keepCursorsOverCommit = false;
                try {
                    Dialect dialect = DialectUtil.getDialect(conn);
                    keepCursorsOverCommit = dialect.needsHoldCursorsForPreparedStatement();
                } catch (DialectException e) {
                    e.printStackTrace();
                    LOG.error(e.getMessage(), e);
                }
                if (keepCursorsOverCommit) {
                    pstmt = conn.prepareStatement(queryString, resultSetType, ResultSet.CONCUR_READ_ONLY,
                            ResultSet.HOLD_CURSORS_OVER_COMMIT);
                } else {
                    pstmt = conn.prepareStatement(queryString, resultSetType, ResultSet.CONCUR_READ_ONLY);
                }
            }
        }
        // ignore queryTimeout and maxRows (some drivers - derby - not implement
        // these feature yet)
        try {
            // set timeout
            pstmt.setQueryTimeout(timeout);

            // set max rows
            pstmt.setMaxRows(maxRows);
        } catch (SQLException e) {
            LOG.warn(e);
        }
    } catch (SQLException e) {
        throw new QueryException(e);
    }

    return pstmt;
}

From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java

@Override
public Document saveDocument(final String user, final String password, final String db, final String table,
        final String name, final Document document) throws BackendException {
    Statement st = null;/*from   w w  w . java  2s .com*/
    Connection conn = null;
    ResultSet rs = null;
    try {
        conn = this.connectToDB(user, password, db);
        String nameField = this.getNameField(conn, table, db);
        st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        rs = st.executeQuery(String.format("select count(*) from \"%s\".\"%s\" where \"%s\"='%s'",
                this.getSchemaName(table, db), this.getPlainTableName(table), nameField, name));
        rs.next();
        int numEntries = rs.getInt(1);
        if (numEntries > 1) {
            throw new BackendException(String.format(
                    "There are more than two entries with the name '%s' in the table '%s.%s', something is wrong with the database design.",
                    name, this.getSchemaName(table, db), this.getPlainTableName(table)));
        }
        boolean isNewDocument = numEntries == 0 ? true : false;

        String insertOrUpdate = isNewDocument ? this.getInsertStatement(document, name, table, nameField, db)
                : this.getUpdateStatement(document, name, table, nameField, db);
        rs.close();
        if (!isNewDocument && document.getAllFields().containsKey("version")) {
            conn.setAutoCommit(false);
            st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String queryString = String.format("select version from \"%s\".\"%s\" where \"%s\"='%s'",
                    this.getSchemaName(table, db), this.getPlainTableName(table), nameField, name);
            this.logString(queryString, user);
            rs = st.executeQuery(queryString);
            rs.next();
            if (rs.getInt(1) != (Integer) document.getAllFields().get("version").getValue()) {
                conn.rollback();
                throw new BackendException(String.format(
                        "There is a new version of the record with name '%s' in table '%s.%s', please repeat editing.",
                        name, this.getSchemaName(table, db), this.getPlainTableName(table)));
            }
            this.logString(insertOrUpdate, user);
            st.executeUpdate(insertOrUpdate);
            conn.commit();
            conn.setAutoCommit(true);
        } else {
            this.logString(insertOrUpdate, user);
            st.executeUpdate(insertOrUpdate);
        }
    } catch (SQLException e) {
        throw new BackendException(e);
    } catch (ClassNotFoundException e) {
        throw new BackendException(e);
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(st);
        DbUtils.closeQuietly(conn);
    }
    return document;
}

From source file:fr.bird.bloom.model.GeographicTreatment.java

/**
 * Create temporary table "temp" with only correct iso2 code in DarwinCoreInput table.
 * Iso2 code (countryCode_) is correct if it's contained in IsoCode table (iso2_).
 * //w  w w  .ja v  a 2s. c  om
 * @return void
 */
public void deleteWrongIso2() {
    Statement statement = null;
    try {
        statement = ConnectionDatabase.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    DatabaseTreatment newConnectionTemp = new DatabaseTreatment(statement);
    List<String> messages = new ArrayList<>();
    String choiceStatement = "executeUpdate";
    messages.add("\n--- Create temporary table with correct ISO2 ---");
    String sqlCreateTemp = "CREATE TABLE Workflow.temp_" + this.getUuid()
            + " AS SELECT DarwinCoreInput.* FROM Workflow.DarwinCoreInput,Workflow.IsoCode WHERE countryCode_=IsoCode.iso2_ AND UUID_=\""
            + this.getUuid() + "\";";
    System.out.println(sqlCreateTemp);
    messages.addAll(newConnectionTemp.executeSQLcommand(choiceStatement, sqlCreateTemp));

    for (int i = 0; i < messages.size(); i++) {
        System.out.println(messages.get(i));
    }
}

From source file:no.polaric.aprsdb.MyDBSession.java

public DbList<JsObject> getJsObjects(String user, String tag) throws java.sql.SQLException {
    PreparedStatement stmt = getCon().prepareStatement(
            " SELECT id,data FROM \"JsObject\"" + " WHERE userid=? AND tag=? ORDER BY data ASC",
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    stmt.setString(1, user);//w  ww .j av  a 2 s.  c  om
    stmt.setString(2, tag);
    return new DbList(stmt.executeQuery(), rs -> {
        return new JsObject(rs.getLong("id"), rs.getString("data"));
    });
}

From source file:fr.bird.bloom.model.GeographicTreatment.java

/**
 * From temp table, create a Clean table with correct geospatial coordinates :
 * -90 >= latitude > 0/* w  ww  .ja  v a2 s.  co m*/
 *  0 < latitude <= 90
 *  
 *  -180 >= longitude > 0
 *   0 < longitude <= 180
 *   locationID_
 *   tag "hasGeospatialIssues" = false
 *   
 *  @return void
 */
public void createTableClean() {
    Statement statement = null;
    try {
        statement = ConnectionDatabase.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    DatabaseTreatment newConnectionClean = new DatabaseTreatment(statement);
    List<String> messages = new ArrayList<>();
    String choiceStatement = "executeUpdate";
    messages.add("\n--- Create Table Clean from temporary table ---");
    String sqlCreateClean = "CREATE TABLE Workflow.Clean_" + this.getUuid() + " AS SELECT * FROM Workflow.temp_"
            + this.getUuid() + " WHERE "
            + "(decimalLatitude_!=0 AND decimalLatitude_<90 AND decimalLatitude_>-90 AND decimalLongitude_!=0 "
            + "AND decimalLongitude_>-180 AND decimalLongitude_<180) AND ((hasGeospatialIssues_!=\"true\") OR (hasGeospatialIssues_ IS NULL));";
    messages.addAll(newConnectionClean.executeSQLcommand(choiceStatement, sqlCreateClean));

    for (int i = 0; i < messages.size(); i++) {
        System.out.println(messages.get(i));
    }
}

From source file:mysql5.MySQL5PlayerDAO.java

/**
 * {@inheritDoc}/*w w  w  .  java 2s .  co m*/
 */
@Override
public int[] getUsedIDs() {
    PreparedStatement statement = DB.prepareStatement("SELECT id FROM players",
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

    try {
        ResultSet rs = statement.executeQuery();
        rs.last();
        int count = rs.getRow();
        rs.beforeFirst();
        int[] ids = new int[count];
        for (int i = 0; i < count; i++) {
            rs.next();
            ids[i] = rs.getInt("id");
        }
        return ids;
    } catch (SQLException e) {
        log.error("Can't get list of id's from players table", e);
    } finally {
        DB.close(statement);
    }

    return new int[0];
}

From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java

@Override
public List<Row> executeQuery(final String user, final String password, final String db, final String query)
        throws BackendException {
    Statement st = null;/*www . ja  va 2s .c  o  m*/
    Connection conn = null;
    ResultSet rs = null;
    List<Row> result = new LinkedList<Row>();
    try {
        conn = this.connectToDB(user, password, db);
        st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.logString(query.trim(), user);
        rs = st.executeQuery(query.trim());
        while (rs.next()) {
            Row row = new PostgresRow();
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                row.getFields().add(rs.getObject(i));
                row.getFieldsByName().put(metaData.getColumnName(i), rs.getObject(i));
            }
            result.add(row);
        }
    } catch (SQLException e) {
        throw new BackendException(e);
    } catch (ClassNotFoundException e) {
        throw new BackendException(e);
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(st);
        DbUtils.closeQuietly(conn);
    }
    return result;
}

From source file:fr.bird.bloom.model.GeographicTreatment.java

/**
 * Select wrong coordinates and write in a file:
 * latitude = 0 ; <-90 ; >90//from w  w w  . j a v  a2  s.c om
 * longitude = 0 ; <-180 ; >180
 * 
 * @return File wrong coordinates
 */
public List<String> deleteWrongCoordinates() {
    Statement statement = null;
    try {
        statement = ConnectionDatabase.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    DatabaseTreatment newConnection = new DatabaseTreatment(statement);

    List<String> messages = new ArrayList<>();
    messages.add("\n--- Select wrong coordinates ---");

    String sqlRetrieveWrongCoord = "SELECT abstract_,acceptedNameUsage_,acceptedNameUsageID_,accessRights_,accrualMethod_,accrualPeriodicity_,accrualPolicy_,alternative_,associatedMedia_,associatedOccurrences_,associatedOrganisms_,associatedReferences_,associatedSequences_,associatedTaxa_,audience_,available_,basisOfRecord_,bed_,behavior_,bibliographicCitation_,catalogNumber_,class_,classKey_,collectionCode_,collectionID_,conformsTo_,continent_,contributor_,coordinateAccuracy_,coordinatePrecision_,coordinateUncertaintyInMeters_,country_,countryCode_,county_,coverage_,created_,creator_,dataGeneralizations_,datasetID_,datasetKey_,datasetName_,date_,dateAccepted_,dateCopyrighted_,dateIdentified_,dateSubmitted_,day_,decimalLatitude_,decimalLongitude_,depth_,depthAccuracy_,description_,disposition_,distanceAboveSurface_,distanceAboveSurfaceAccuracy_,dynamicProperties_,earliestAgeOrLowestStage_,earliestEonOrLowestEonothem_,earliestEpochOrLowestSeries_,earliestEraOrLowestErathem_,earliestPeriodOrLowestSystem_,educationLevel_,elevation_,elevationAccuracy_,endDayOfYear_,establishmentMeans_,event_,eventDate_,eventID_,eventRemarks_,eventTime_,extent_,family_,familyKey_,fieldNotes_,fieldNumber_,footprintSpatialFit_,footprintSRS_,footprintWKT_,format_,formation_,gbifID_,genericName_,genus_,genusKey_,geodeticDatum_,geologicalContext_,geologicalContextID_,georeferencedBy_,georeferencedDate_,georeferenceProtocol_,georeferenceRemarks_,georeferenceSources_,georeferenceVerificationStatus_,group_,habitat_,hasCoordinate_,hasFormat_,hasGeospatialIssues_,hasPart_,hasVersion_,higherClassification_,higherGeography_,higherGeographyID_,highestBiostratigraphicZone_,identification_,identificationID_,identificationQualifier_,identificationReferences_,identificationRemarks_,identificationVerificationStatus_,identifiedBy_,identifier_,idFile_,individualCount_,individualID_,informationWithheld_,infraspecificEpithet_,institutionCode_,institutionID_,instructionalMethod_,isFormatOf_,island_,islandGroup_,isPartOf_,isReferencedBy_,isReplacedBy_,isRequiredBy_,issue_,issued_,isVersionOf_,kingdom_,kingdomKey_,language_,lastCrawled_,lastInterpreted_,lastParsed_,latestAgeOrHighestStage_,latestEonOrHighestEonothem_,latestEpochOrHighestSeries_,latestEraOrHighestErathem_,latestPeriodOrHighestSystem_,license_,lifeStage_,lithostratigraphicTerms_,livingSpecimen_,locality_,locationAccordingTo_,locationID_,locationRemarks_,lowestBiostratigraphicZone_,machineObservation_,materialSample_,materialSampleID_,maximumDepthinMeters_,maximumDistanceAboveSurfaceInMeters_,maximumElevationInMeters_,measurementAccuracy_,measurementDeterminedBy_,measurementDeterminedDate_,measurementID_,measurementMethod_,measurementOrFact_,measurementRemarks_,measurementType_,measurementUnit_,mediator_,mediaType_,medium_,member_,minimumDepthinMeters_,minimumDistanceAboveSurfaceInMeters_,minimumElevationInMeters_,modified_,month_,municipality_,nameAccordingTo_,nameAccordingToID_,namePublishedIn_,namePublishedInID_,namePublishedInYear_,nomenclaturalCode_,nomenclaturalStatus_,occurrence_,occurrenceDetails_,occurrenceID_,occurrenceRemarks_,occurrenceStatus_,order_,orderKey_,organism_,organismID_,organismName_,organismRemarks_,organismScope_,originalNameUsage_,originalNameUsageID_,otherCatalogNumbers_,ownerInstitutionCode_,parentNameUsage_,parentNameUsageID_,phylum_,phylumKey_,pointRadiusSpatialFit_,preparations_,preservedSpecimen_,previousIdentifications_,protocol_,provenance_,publisher_,publishingCountry_,recordedBy_,recordNumber_,references_,relatedResourceID_,relationshipAccordingTo_,relationshipEstablishedDate_,relationshipRemarks_,relation_,replaces_,reproductiveCondition_,requires_,resourceID_,resourceRelationship_,resourceRelationshipID_,rights_,rightsHolder_,samplingEffort_,samplingProtocol_,scientificName_,scientificNameAuthorship_,scientificNameID_,sex_,source_,spatial_,species_,speciesKey_,specificEpithet_,startDayOfYear_,stateProvince_,subgenus_,subgenusKey_,subject_,tableOfContents_,taxon_,taxonConceptID_,taxonID_,taxonKey_,taxonomicStatus_,taxonRank_,taxonRemarks_,temporal_,title_,type_,typeStatus_,typifiedName_,valid_,verbatimCoordinates_,verbatimCoordinateSystem_,verbatimDate_,verbatimDepth_,verbatimElevation_,verbatimEventDate_,verbatimLatitude_,verbatimLocality_,verbatimLongitude_,verbatimSRS_,verbatimTaxonRank_,vernacularName_,waterBody_,year_ FROM Workflow.DarwinCoreInput WHERE (UUID_=\""
            + this.getUuid()
            + "\") AND (decimalLatitude_=0 OR decimalLatitude_>90 OR decimalLatitude_<-90 OR decimalLongitude_=0 OR decimalLongitude_>180 OR decimalLongitude_<-180);";
    messages.addAll(newConnection.executeSQLcommand("executeQuery", sqlRetrieveWrongCoord));
    List<String> resultatSelect = newConnection.getResultatSelect();
    messages.add("nb lignes affectes :" + Integer.toString(resultatSelect.size() - 1));

    if (!new File(BloomConfig.getDirectoryPath() + "temp/" + this.getUuid() + "/wrong/").exists()) {
        BloomUtils.createDirectory(BloomConfig.getDirectoryPath() + "temp/" + this.getUuid() + "/wrong/");
    }

    for (int j = 0; j < messages.size(); j++) {
        if (messages.get(j).contains("nb lignes affectes")) {
            this.setNbWrongCoordinates(Integer.parseInt(messages.get(j).split(":")[1]));
        }
        System.out.println(messages.get(j));
    }

    this.setNbWrongCoordinates(resultatSelect.size() - 1);

    return resultatSelect;
}

Example usage for java.sql ResultSet TYPE_SCROLL_INSENSITIVE

Introduction

Prototype

Document

Usage