Example usage for java.sql ResultSet isAfterLast

List of usage examples for java.sql ResultSet isAfterLast

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

Introduction

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

Prototype

boolean isAfterLast() throws SQLException;

Source Link

Document

Retrieves whether the cursor is after the last row in this ResultSet object.

Usage

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.//  w  w w  . j av  a 2  s. 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:BQJDBC.QueryResultTest.BQForwardOnlyResultSetFunctionTest.java

@Test
public void databaseMetaDataGetTables() {
    //clouddb,ARTICLE_LOOKUP,starschema.net,[Ljava.lang.String;@9e8424
    ResultSet result = null;
    try {/*from   w  ww  .j  a  v  a 2  s .  co m*/
        //Function call getColumns 
        //catalog:null, 
        //schemaPattern: starschema_net__clouddb, 
        //tableNamePattern:OUTLET_LOOKUP, columnNamePattern: null
        //result = con.getMetaData().getTables("OUTLET_LOOKUP", null, "starschema_net__clouddb", null );
        result = con.getMetaData().getColumns(null, "starschema_net__clouddb", "OUTLET_LOOKUP", null);
        //Function call getTables(catalog: ARTICLE_COLOR_LOOKUP, schemaPattern: null, tableNamePattern: starschema_net__clouddb, types: TABLE , VIEW , SYSTEM TABLE , SYNONYM , ALIAS , )            
    } catch (SQLException e) {
        e.printStackTrace();
        Assert.fail();
    }
    try {
        Assert.assertTrue(result.first());
        while (!result.isAfterLast()) {
            String toprint = "";
            toprint += result.getString(1) + " , ";
            toprint += result.getString(2) + " , ";
            toprint += result.getString(3) + " , ";
            toprint += result.getString(4) + " , ";
            toprint += result.getString(5) + " , ";
            toprint += result.getString(6) + " , ";
            toprint += result.getString(7) + " , ";
            toprint += result.getString(8) + " , ";
            toprint += result.getString(9) + " , ";
            toprint += result.getString(10);
            System.err.println(toprint);
            result.next();
        }
    } catch (SQLException e) {
        e.printStackTrace();
        Assert.fail();
    }
}

From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java

public void testBeforeFirstAfterLast() throws SQLException {
    // stat.executeUpdate("create table test(id int)");
    stat = conn.createStatement();//w  ww . ja  va  2 s .com
    stat.execute("insert into test (column1,column2,column3) values(1,21,'binlijin2')");
    assertTrue(stat.getUpdateCount() == 1);
    // With a result
    ResultSet rs = stat.executeQuery(
            "select column1,column2,column3 from " + TABLE_NAME + " where column1=1 and column3='binlijin2'");
    assertTrue(rs.isBeforeFirst());
    assertFalse(rs.isAfterLast());
    rs.next();
    assertFalse(rs.isBeforeFirst());
    assertFalse(rs.isAfterLast());
    rs.next();
    assertFalse(rs.isBeforeFirst());
    assertTrue(rs.isAfterLast());
    rs.close();
    rs = stat.executeQuery("select column1,column2,column3 from test where column2 = -222");
    assertFalse(rs.isBeforeFirst());
    assertFalse(rs.isAfterLast());
    rs.next();
    assertFalse(rs.isBeforeFirst());
    assertFalse(rs.isAfterLast());
    rs.close();
}

From source file:org.intermine.bio.dataconversion.ModEncodeFeatureProcessor.java

private void processExpressionLevels(Connection connection) throws SQLException, ObjectStoreException {
    ResultSet res = getExpressionLevels(connection);
    Integer previousId = -1;//from   w ww. jav  a  2s  . c om
    Item level = null;
    while (res.next()) {
        Integer id = res.getInt("expression_id");
        Integer featureId = res.getInt("feature_id");
        String name = res.getString("uniquename");
        String value = res.getString("value");
        String property = res.getString("property");
        String propValue = res.getString("propvalue");
        LOG.debug("EL: " + id + "|" + previousId + "->" + featureId + ":" + property + "|" + propValue);
        if (!id.equals(previousId)) {
            // if not first store prev level
            if (previousId > 0) {
                getChadoDBConverter().store(level);
            }
            level = createExpressionLevel(featureId, name, value);
        }
        // check if dcpm is a decimal number
        if ("dcpm".equalsIgnoreCase(property) && !StringUtils.containsOnly(propValue, ".0123456789")) {
            // in some cases (waterston) the value for dcpm is
            // 'nan' or 'na' or '.na' instead of a decimal number
            previousId = id;
            continue;
        }
        if (!EL_KNOWN_ATTRIBUTES.contains(property)) {
            LOG.debug("ExpressionLevel for feature_id = " + featureId + " has unknown attribute " + property);
            previousId = id;
            continue;
        }
        level.setAttribute(getPropName(property), propValue);
        previousId = id;
    }
    if (res.isAfterLast()) {
        getChadoDBConverter().store(level);
    }
    res.close();
}

From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java

@Test
public void testExecuteSQLQuery() throws Exception {
    Statement statement = getConnection().createStatement();
    ResultSet rs = statement.executeQuery(SQL_EMPS);
    assertSame(statement, rs.getStatement());

    assertEquals(ResultSet.TYPE_FORWARD_ONLY, rs.getType());
    assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, rs.getHoldability());

    assertFalse(rs.isClosed());//from   w w w . ja v  a  2s  .  c  o m
    assertTrue(rs.isBeforeFirst());
    assertFalse(rs.isAfterLast());

    assertEquals(1, rs.findColumn("empno"));
    assertEquals(2, rs.findColumn("ename"));
    assertEquals(3, rs.findColumn("salary"));
    assertEquals(4, rs.findColumn("hiredate"));

    int count = printResultSet(rs);

    assertEquals(getEmpRowCount(), count);
    assertFalse(rs.isBeforeFirst());
    assertTrue(rs.isAfterLast());
    rs.close();
    assertTrue(rs.isClosed());

    statement.close();
    assertTrue(statement.isClosed());
}

From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java

@SuppressWarnings("deprecation")
@Test// ww w .  j  av  a  2s.  c o m
public void testResultSetWhenClosed() throws Exception {
    Statement statement = getConnection().createStatement();
    ResultSet rs = statement.executeQuery(SQL_EMPS);

    rs.close();

    try {
        rs.isBeforeFirst();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.isAfterLast();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.isFirst();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.isLast();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.beforeFirst();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.afterLast();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.first();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.last();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.next();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getRow();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getType();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getConcurrency();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.rowUpdated();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.rowDeleted();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.rowInserted();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getStatement();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.wasNull();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getString(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getString("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBoolean(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBoolean("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getByte(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getByte("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getShort(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getShort("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getInt(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getInt("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getLong(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getLong("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getFloat(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getFloat("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDouble(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDouble("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBigDecimal(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBigDecimal("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBytes(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBytes("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDate(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDate(1, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDate("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDate("col1", null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTime(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTime(1, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTime("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTime("col1", null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTimestamp(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTimestamp(1, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTimestamp("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTimestamp("col1", null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getAsciiStream(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getAsciiStream("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getUnicodeStream(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getUnicodeStream("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBinaryStream(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBinaryStream("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getCharacterStream(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getCharacterStream("col1");
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getMetaData();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.setFetchDirection(1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getFetchDirection();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.setFetchSize(100);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getFetchSize();
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getHoldability();
        fail();
    } catch (SQLException ignore) {
    }

    statement.close();
}

From source file:org.ohmage.query.impl.SurveyResponseQueries.java

@Override
public int retrieveSurveyResponses(final Campaign campaign, final String username,
        final Set<UUID> surveyResponseIds, final Collection<String> usernames, final DateTime startDate,
        final DateTime endDate, final SurveyResponse.PrivacyState privacyState,
        final Collection<String> surveyIds, final Collection<String> promptIds, final String promptType,
        final Set<String> promptResponseSearchTokens, final Collection<ColumnKey> columns,
        final List<SortParameter> sortOrder, final long surveyResponsesToSkip,
        final long surveyResponsesToProcess, final List<SurveyResponse> result) throws DataAccessException {

    if (((surveyIds != null) && (surveyIds.size() == 0)) || ((promptIds != null) && (promptIds.size() == 0))
            || ((columns != null) && (columns.size() == 0))) {

        return 0;
    }/*from   w  w  w.  jav  a  2  s .  c  om*/

    List<Object> parameters = new LinkedList<Object>();
    String sql = buildSqlAndParameters(campaign, username, surveyResponseIds, usernames, startDate, endDate,
            privacyState, surveyIds, promptIds, promptType, promptResponseSearchTokens, columns, sortOrder,
            parameters);

    // This is necessary to map tiny integers in SQL to Java's integer.
    final Map<String, Class<?>> typeMapping = new HashMap<String, Class<?>>();
    typeMapping.put("tinyint", Integer.class);

    // This is a silly, hacky way to get the total count, but it is the 
    // only real way I have found thus far.
    final Collection<Integer> totalCount = new ArrayList<Integer>(1);

    try {
        result.addAll(getJdbcTemplate().query(sql, parameters.toArray(),
                new ResultSetExtractor<List<SurveyResponse>>() {
                    /**
                     * First, it skips a set of rows based on the parameterized
                     * number of survey responses to skip. Then, it aggregates  
                     * the information from the number of desired survey 
                     * responses.
                     * 
                     * There must be some ordering on the results in order for
                     * subsequent results to skip / process the same rows. The
                     * agreed upon ordering is by time taken time stamp. 
                     * Therefore, if a user were viewing results as they were
                     * being generated and/or uploaded, it could be that
                     * subsequent calls return the same result as a previous
                     * call. This is analogous to viewing a page of feed data
                     * and going to the next page and seeing some feed items
                     * that you just saw on the previous page. It was decided
                     * that this is a common and acceptable way to view live
                     * data.
                     */
                    @Override
                    public List<SurveyResponse> extractData(ResultSet rs)
                            throws SQLException, org.springframework.dao.DataAccessException {

                        // If the result set is empty, we can simply return an
                        // empty list.
                        if (!rs.next()) {
                            totalCount.add(0);
                            return Collections.emptyList();
                        }

                        // Keep track of the number of survey responses we have
                        // skipped.
                        int surveyResponsesSkipped = 0;
                        // Continue while there are more survey responses to
                        // skip.
                        while (surveyResponsesSkipped < surveyResponsesToSkip) {
                            // Get the ID for the survey response we are 
                            // skipping.
                            String surveyResponseId = rs.getString("uuid");
                            surveyResponsesSkipped++;

                            // Continue to skip rows as long as there are rows
                            // to skip and those rows have the same survey
                            // response ID.
                            while (surveyResponseId.equals(rs.getString("uuid"))) {
                                // We were skipping the last survey response,
                                // therefore, there are no survey responses to
                                // return and we can return an empty list.
                                if (!rs.next()) {
                                    totalCount.add(surveyResponsesSkipped);
                                    return Collections.emptyList();
                                }
                            }
                        }

                        // Create a list of the results.
                        List<SurveyResponse> result = new LinkedList<SurveyResponse>();

                        // Cycle through the rows until the maximum number of
                        // rows has been processed or there are no more rows to
                        // process.
                        int surveyResponsesProcessed = 0;
                        while (surveyResponsesProcessed < surveyResponsesToProcess) {
                            // We have not yet processed this survey response,
                            // so we need to process it and then continue
                            // processing this and all of its survey responses.

                            // First, create the survey response object.
                            SurveyResponse surveyResponse;
                            try {
                                JSONObject locationJson = null;
                                String locationString = rs.getString("location");
                                if (locationString != null) {
                                    locationJson = new JSONObject(locationString);
                                }

                                surveyResponse = new SurveyResponse(
                                        campaign.getSurveys().get(rs.getString("survey_id")),
                                        UUID.fromString(rs.getString("uuid")), rs.getString("username"),
                                        rs.getString("urn"), rs.getString("client"), rs.getLong("epoch_millis"),
                                        DateTimeUtils.getDateTimeZoneFromString(rs.getString("phone_timezone")),
                                        new JSONObject(rs.getString("launch_context")),
                                        rs.getString("location_status"), locationJson,
                                        SurveyResponse.PrivacyState.getValue(rs.getString("privacy_state")));

                                if (columns != null) {
                                    surveyResponse.setCount(rs.getLong("count"));
                                }
                            } catch (IllegalArgumentException e) {
                                throw new SQLException("The TimeZone is unknown.", e);
                            } catch (JSONException e) {
                                throw new SQLException("Error creating a JSONObject.", e);
                            } catch (DomainException e) {
                                throw new SQLException("Error creating the survey response information object.",
                                        e);
                            }

                            // Add the current survey response to the result
                            // list and increase the number of survey responses
                            // processed.
                            result.add(surveyResponse);
                            surveyResponsesProcessed++;

                            // Get a string representation of the survey
                            // response's unique identifier.
                            String surveyResponseId = surveyResponse.getSurveyResponseId().toString();

                            boolean processPrompts = true;
                            try {
                                rs.getString("prompt_id");
                            } catch (SQLException e) {
                                processPrompts = false;
                            }

                            if (processPrompts) {
                                // Now, process this prompt response and all 
                                // subsequent prompt responses.
                                do {
                                    try {
                                        // Retrieve the corresponding prompt 
                                        // information from the campaign.
                                        Prompt prompt = campaign.getPrompt(surveyResponse.getSurvey().getId(),
                                                rs.getString("prompt_id"));

                                        // Generate the prompt response and add it to
                                        // the survey response.
                                        surveyResponse.addPromptResponse(prompt.createResponse(
                                                (Integer) rs.getObject("repeatable_set_iteration", typeMapping),
                                                rs.getObject("response")));
                                    } catch (DomainException e) {
                                        throw new SQLException(
                                                "The prompt response value from the database is not a valid response value for this prompt.",
                                                e);
                                    }
                                } while (
                                // Get the next prompt response unless we
                                // just read the last prompt response in
                                // the result,
                                rs.next() &&
                                // and continue as long as that prompt 
                                // response pertains to this survey 
                                // response.
                                surveyResponseId.equals(rs.getString("uuid")));
                            } else {
                                rs.next();
                            }

                            // If we exited the loop because we passed the last
                            // record, break out of the survey response 
                            // processing loop.
                            if (rs.isAfterLast()) {
                                break;
                            }
                        }

                        // Now, if we are after the last row, we need to set 
                        // the total count to be the total number skipped plus
                        // the total number processed.
                        if (rs.isAfterLast()) {
                            totalCount.add(surveyResponsesSkipped + surveyResponsesProcessed);
                        } else {
                            int otherIds = 1;
                            String id = rs.getString("uuid");

                            while (rs.next()) {
                                if (!rs.getString("uuid").equals(id)) {
                                    otherIds++;
                                    id = rs.getString("uuid");
                                }
                            }

                            totalCount.add(surveyResponsesSkipped + surveyResponsesProcessed + otherIds);
                        }

                        // Finally, return only the survey responses as a list.
                        return result;
                    }
                }));

        return totalCount.iterator().next();
    } catch (org.springframework.dao.DataAccessException e) {
        StringBuilder errorBuilder = new StringBuilder("Error executing SQL '" + sql + "' with parameters: ");

        boolean firstPass = true;
        for (Object parameter : parameters) {
            if (firstPass) {
                firstPass = false;
            } else {
                errorBuilder.append(", ");
            }
            errorBuilder.append(parameter.toString());
        }

        throw new DataAccessException(errorBuilder.toString(), e);
    }
}