Example usage for java.sql ResultSet first

List of usage examples for java.sql ResultSet first

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. first

Introduction

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

Prototype

boolean first() throws SQLException;

Source Link

Document

Moves the cursor to the first row in this ResultSet object.

Usage

From source file:ua.aits.Carpath.model.ArchiveArticleModel.java

public String undeleteArticle(String id) throws SQLException, ClassNotFoundException, InstantiationException,
        IllegalAccessException, IOException {
    ResultSet result = DB.getResultSet(
            "SELECT archive_articles.article_id, archive_articles.article_category, archive_articles.article_dir FROM archive_articles WHERE archive_articles.article_id = "
                    + id + ";");
    result.first();
    DB.runQuery("UPDATE `archive_articles` SET `article_is_delete`= 0 WHERE article_id = " + id + ";");
    String category = result.getString("article_category");
    DB.closeCon();//  w  ww  .  ja  v  a2s  .  c o m
    return category;
}

From source file:edu.ku.brc.specify.conversion.IdHashMapper.java

/**
 * @param tblName//from   ww w  .  j  a v  a2s.co  m
 * @return
 */
public int getMapCount(final String tblName) {
    Statement cntStmt = null;
    ResultSet rs = null;

    try {
        Integer count = 0;
        cntStmt = oldConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        rs = cntStmt.executeQuery("select count(*) from " + tblName);
        if (rs.first()) {
            count = rs.getInt(1);
            if (count == null) {
                return 0;
            }
        }

        return count;

    } catch (SQLException ex) {
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (cntStmt != null)
                cntStmt.close();
        } catch (Exception ex) {
        }
    }
    return 0;
}

From source file:org.wso2.carbon.ml.model.internal.DatabaseHandler.java

/**
 *
 * @param workflowID Machine learning workflow ID
 * @return Returns a machine learning workflow object
 * @throws DatabaseHandlerException//from  w w w .  j  a  va 2 s  . c o  m
 */
public MLWorkflow getWorkflow(String workflowID) throws DatabaseHandlerException {
    Connection connection = null;
    ResultSet result = null;
    PreparedStatement getStatement = null;
    try {
        MLWorkflow mlWorkflow = new MLWorkflow();
        mlWorkflow.setWorkflowID(workflowID);
        connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        getStatement = connection.prepareStatement(SQLQueries.GET_DATASET_LOCATION);
        getStatement.setString(1, workflowID);
        result = getStatement.executeQuery();
        if (result.first()) {
            mlWorkflow.setDatasetURL(result.getString(1));
        }
        List<MLFeature> mlFeatures = new ArrayList();
        getStatement = connection.prepareStatement(SQLQueries.GET_ML_FEATURE_SETTINGS);
        getStatement.setString(1, workflowID);
        result = getStatement.executeQuery();
        while (result.next()) {
            // check whether to include the feature or not
            if (result.getBoolean(4) == true) {
                MLFeature mlFeature = new MLFeature();
                mlFeature.setName(result.getString(1));
                mlFeature.setType(result.getString(2));
                mlFeature.setImputeOption(result.getString(3));
                mlFeature.setInclude(result.getBoolean(4));
                mlFeatures.add(mlFeature);
            }
        }
        mlWorkflow.setFeatures(mlFeatures);
        getStatement = connection.prepareStatement(SQLQueries.GET_ML_MODEL_SETTINGS);
        getStatement.setString(1, workflowID);
        result = getStatement.executeQuery();
        if (result.first()) {
            mlWorkflow.setAlgorithmClass(result.getString(1));
            mlWorkflow.setAlgorithmName(result.getString(2));
            mlWorkflow.setResponseVariable(result.getString(3));
            mlWorkflow.setTrainDataFraction(result.getDouble(4));
            mlWorkflow.setHyperParameters((Map<String, String>) result.getObject(5));
        }
        return mlWorkflow;
    } catch (SQLException e) {
        throw new DatabaseHandlerException(e.getMessage(), e);
    } finally {
        // enable auto commit
        MLDatabaseUtils.enableAutoCommit(connection);
        // Close the database resources.
        MLDatabaseUtils.closeDatabaseResources(connection, getStatement, result);
    }
}

From source file:org.integratedmodelling.sql.SQLServer.java

/**
 * Return one string corresponding to field 0 of row 0 of the result after
 * executing the passed query. Return null if no results are returned or
 * query generates errors.//from   www . j av a2s  .c om
 * 
 * @param sql
 * @return
 * @throws ThinklabStorageException 
 */
public String getResult(String sql) throws ThinklabStorageException {

    String ret = null;

    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;

    try {
        conn = getConnection();
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        rset = stmt.executeQuery(sql);

        if (rset.first()) {
            for (; !rset.isAfterLast(); rset.next()) {
                ret = rset.getString(1);
                break;
            }
        }
    } catch (SQLException e) {
        throw new ThinklabStorageException(e);
    } finally {
        try {
            rset.close();
        } catch (Exception e) {
        }
        try {
            stmt.close();
        } catch (Exception e) {
        }
        try {
            conn.close();
        } catch (Exception e) {
        }
    }
    return ret;
}

From source file:edu.ku.brc.specify.conversion.IdHashMapper.java

/**
 * Maps the first index to the second index.
 * The SQL to do the mappings.//  ww w. j  av  a2  s . co m
 */
public void mapAllIds() {
    if (sql == null) {
        throw new RuntimeException("Calling mapAllIds and the SQL statement is NULL!");
    }

    int mappingCount = getMapCount(mapTableName);
    wasEmpty = mappingCount == 0;

    if (doDelete || mappingCount == 0) {
        if (!isUsingSQL) {
            BasicSQLUtils.deleteAllRecordsFromTable(oldConn, mapTableName,
                    BasicSQLUtils.myDestinationServerType);
        }

        if (frame != null) {
            frame.setDesc("Mapping " + mapTableName);
        }

        try {
            if (frame != null) {
                frame.setProcess(0, 0);
            }

            PreparedStatement pStmt = oldConn.prepareStatement("INSERT INTO " + mapTableName + " VALUES (?,?)");
            Statement stmtOld = oldConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = stmtOld.executeQuery(sql);
            if (rs.last()) {
                if (frame != null) {
                    frame.setProcess(0, rs.getRow());
                }
            }

            if (rs.first()) {
                int count = 0;
                do {
                    pStmt.setInt(1, rs.getInt(1)); // Old Index
                    pStmt.setInt(2, rs.getInt(2)); // New Index
                    if (pStmt.executeUpdate() != 1) {
                        String msg = String.format("Error writing to Map table[%s] old: %d  new: %d",
                                mapTableName, rs.getInt(1), rs.getInt(2));
                        log.error(msg);
                        throw new RuntimeException(msg);
                    }

                    if (frame != null) {
                        if (count % 1000 == 0) {
                            frame.setProcess(count);
                        }

                    } else {
                        if (count % 2000 == 0) {
                            log.debug("Mapped " + count + " records from " + tableName);
                        }
                    }
                    count++;

                } while (rs.next());

                log.info("Mapped " + count + " records from " + tableName);
                if (frame != null) {
                    frame.setProcess(0, 0);
                }

            } else {
                log.info("No records to map in " + tableName);
            }
            rs.close();

            stmtOld.close();
            pStmt.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(IdHashMapper.class, ex);
            log.error("trying to execute:" + sql);
            log.error(ex);

            throw new RuntimeException(ex);
        }
    } else {
        log.debug("Skipping the build of mapper: " + mapTableName);
    }

    if (frame != null) {
        frame.setProcess(0, 0);
    }

}

From source file:sopho.Ofeloumenoi.EditOfeloumenoiController.java

public BufferedImage bfImage(String rand) {
    BufferedImage img = null; //Buffered image coming from database
    InputStream fis = null;/*from  ww w.  ja v  a2  s. c  o  m*/

    try {
        ResultSet myrs;

        sopho.DBClass db = new sopho.DBClass();

        Connection conn = db.ConnectDB();

        String sql = "SELECT * FROM images WHERE photoID =?";

        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, rand);

        myrs = pst.executeQuery();

        myrs.first();

        fis = myrs.getBinaryStream("image");

        img = javax.imageio.ImageIO.read(fis); //create the BufferedImaged

    } catch (SQLException | IOException e) {
        System.err.println("error " + e);
    }

    return img; //function returns a BufferedImage object
}

From source file:org.wise.portal.domain.admin.DailyAdminJob.java

/**
 * Gather the Annotation statistics. This includes the total number of Annotation
 * rows as well as how many Annotation nodes for each annotation type.
 * @param statement the object to execute queries
 * @param vleStatistics the JSONObject to store the statistics in
 *//*  w  w  w.j a  v a 2s. com*/
private void gatherAnnotationStatistics(Statement statement, JSONObject vleStatistics) {
    try {
        //get the total number of annotations
        ResultSet annotationCountQuery = statement.executeQuery("select count(*) from annotation");

        if (annotationCountQuery.first()) {
            //get the total number of annotations
            long annotationCount = annotationCountQuery.getLong(1);

            try {
                //add the total annotation count to the vle statistics
                vleStatistics.put("totalAnnotationCount", annotationCount);
            } catch (JSONException e) {
                e.printStackTrace();
            }
        }

        //this will hold all the annotation types e.g. "comment", "score", "flag", "cRater"
        Vector<String> annotationTypes = new Vector<String>();

        //get all the different types of annotations
        ResultSet annotationTypeQuery = statement.executeQuery("select distinct type from annotation");

        while (annotationTypeQuery.next()) {
            String annotationType = annotationTypeQuery.getString(1);
            annotationTypes.add(annotationType);
        }

        //the array to store the counts for each annotation type
        JSONArray annotationCounts = new JSONArray();

        //loop through all the annotation types
        for (String annotationType : annotationTypes) {
            if (annotationType != null && !annotationType.equals("") && !annotationType.equals("null")
                    && !annotationType.equals("NULL")) {
                //get the total number of annotations for the current annotation type
                ResultSet annotationTypeCountQuery = statement
                        .executeQuery("select count(*) from annotation where type='" + annotationType + "'");

                if (annotationTypeCountQuery.first()) {
                    //get the count for the current annotation type
                    long annotationTypeCount = annotationTypeCountQuery.getLong(1);

                    try {
                        //create an object to store the type and count in
                        JSONObject annotationObject = new JSONObject();
                        annotationObject.put("annotationType", annotationType);
                        annotationObject.put("count", annotationTypeCount);

                        annotationCounts.put(annotationObject);
                    } catch (JSONException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        //add the annotation statistics to the vle statistics
        vleStatistics.put("individualAnnotationCounts", annotationCounts);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (JSONException e) {
        e.printStackTrace();
    }
}

From source file:org.wise.portal.domain.admin.DailyAdminJob.java

/**
 * Get the number of times hints were viewed by a student
 * @param statement the object to execute queries
 * @param vleStatistics the JSONObject to store the statistics in
 *//*from   www . j  ava2 s.  co m*/
private void gatherHintStatistics(Statement statement, JSONObject vleStatistics) {
    try {
        //get the total number of times a hint was viewed by a student
        ResultSet hintCountQuery = statement
                .executeQuery("select count(*) from stepwork where data like '%hintStates\":[{%]%'");

        if (hintCountQuery.first()) {
            //add the count to the vle statistics
            long hintCount = hintCountQuery.getLong(1);
            vleStatistics.put("totalHintViewCount", hintCount);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (JSONException e) {
        e.printStackTrace();
    }
}

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 w  w . j  a  v  a 2  s . com
    // 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:BQJDBC.QueryResultTest.Timeouttest.java

@Test
public void QueryResultTest05() {
    final String sql = "SELECT word FROM publicdata:samples.shakespeare WHERE word=\"huzzah\"";
    final String description = "The word \"huzzah\" NOTE: It doesn't appear in any any book, so it returns with a null #WHERE";

    this.logger.info("Test number: 05");
    this.logger.info("Running query:" + sql);

    java.sql.ResultSet Result = null;
    try {/*from  w  w w.  ja  va 2  s  .c o  m*/
        Result = Timeouttest.con.createStatement().executeQuery(sql);
        this.logger.debug(Result.getMetaData().getColumnCount());
    } catch (SQLException e) {
        this.logger.error("SQLexception" + e.toString());
        Assert.fail("SQLException" + e.toString());
    }
    Assert.assertNotNull(Result);

    this.logger.debug(description);
    try {
        if (Result.getType() != ResultSet.TYPE_FORWARD_ONLY)
            Assert.assertFalse(Result.first());
    } catch (SQLException e) {
        this.logger.error("SQLexception" + e.toString());
        Assert.fail(e.toString());
    }
}