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:com.tascape.qa.th.db.H2Handler.java

@Override
public void updateSuiteExecutionResult(String execId) throws SQLException {
    LOG.info("Update test suite execution result with execution id {}", execId);
    int total = 0, fail = 0;

    try (Connection conn = this.getConnection();) {
        final String sql1 = "SELECT " + Test_Result.EXECUTION_RESULT.name() + " FROM " + TestResult.TABLE_NAME
                + " WHERE " + Test_Result.SUITE_RESULT.name() + " = ?;";
        try (PreparedStatement stmt = conn.prepareStatement(sql1)) {
            stmt.setString(1, execId);/*w  ww.  jav a  2  s.  c  o m*/
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                total++;
                String result = rs.getString(Test_Result.EXECUTION_RESULT.name());
                if (!result.equals(ExecutionResult.PASS.name()) && !result.endsWith("/0")) {
                    fail++;
                }
            }
        }
    }

    try (Connection conn = this.getConnection();) {
        final String sql = "SELECT * FROM " + SuiteResult.TABLE_NAME + " WHERE " + SuiteResult.SUITE_RESULT_ID
                + " = ?;";
        try (PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE)) {
            stmt.setString(1, execId);
            ResultSet rs = stmt.executeQuery();
            if (rs.first()) {
                rs.updateInt(SuiteResult.NUMBER_OF_TESTS, total);
                rs.updateInt(SuiteResult.NUMBER_OF_FAILURE, fail);
                rs.updateString(SuiteResult.EXECUTION_RESULT, fail == 0 ? "PASS" : "FAIL");
                rs.updateLong(SuiteResult.STOP_TIME, System.currentTimeMillis());
                rs.updateRow();
            }
        }
    }
}

From source file:org.geowebcache.storage.jdbc.jobstore.JDBCJobWrapper.java

/**
 * Gets a count of logs for a job/* w w w  . ja va 2  s.com*/
 * @param stObj The job to get a count of logs for
 * @param level optionally only count logs of a certain level (ERROR, WARN or INFO)
 * @return count
 * @throws SQLException
 */
public long getJobLogCount(JobObject stObj, LOG_LEVEL level) throws SQLException {

    long result = 0;

    String query = "SELECT COUNT(*) FROM JOB_LOGS WHERE job_id = ?";

    if (level != null) {
        query += " AND log_level = ?";
    }

    final Connection conn = getConnection();
    PreparedStatement prep = null;
    try {
        prep = conn.prepareStatement(query);
        prep.setLong(1, stObj.getJobId());
        if (level != null) {
            prep.setString(2, level.name());
        }

        ResultSet rs = prep.executeQuery();
        try {
            if (rs.first()) {
                result = rs.getLong(1);
            }
        } finally {
            close(rs);
        }
    } finally {
        close(prep);
        close(conn);
    }

    return result;
}

From source file:Data.java

private JTable getTbleData(Statement stmt) throws SQLException, ClassNotFoundException {
    ResultSet rs;
    String sql = "SELECT pro_name, pro_description, COUNT(sto_uid) - (SUM(sto_inout) * 2) AS NbProduit FROM t_produit, t_stock WHERE t_produit.id_produit = t_stock.id_produit GROUP BY t_produit.id_produit";

    rs = stmt.executeQuery(sql);/*from   www. j a  v  a2  s. c o  m*/

    rs.last();
    Object rowData[][] = new Object[rs.getRow()][3];

    rs.beforeFirst();

    while (rs.next()) {
        rowData[rs.getRow() - 1][0] = rs.getString(1);
        rowData[rs.getRow() - 1][1] = rs.getString(2);
        rowData[rs.getRow() - 1][2] = rs.getInt(3);
    }

    sql = "SELECT tmp_temperature, tmp_humidity FROM t_temphum ORDER BY tmp_date DESC LIMIT 1";

    rs = stmt.executeQuery(sql);
    rs.first();

    if (tempUnit == "C") {
        tempRealTime.setText("Temprature : " + rs.getDouble(1) + "" + tempUnit);
    } else {
        tempRealTime.setText("Temprature : " + celsiusToFahrenheit(rs.getString(1)) + "" + tempUnit);
    }
    humRealTime.setText("Humidit : " + rs.getDouble(2) + "%");

    lastTemp = rs.getDouble(1);
    lastHum = rs.getDouble(2);

    Object columnNames[] = { "Poduit", "Description", "Quantit" };
    JTable table = new JTable(rowData, columnNames);

    return table;

}

From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java

public boolean insertContent(String folder, Object key, Object bean) throws WGBackendException {

    ResultSet resultSet = null;/*from  ww w  .  j av a2  s.  co m*/
    try {

        // Gather all columns to set as values, including those keys that are set. Find out the key to generate, if present.
        Map allColumns = new HashMap();
        Map valuesMap = (Map) bean;
        allColumns.putAll(valuesMap);
        String keyToGenerate = null;
        Map keyMap = (Map) key;
        Iterator keys = keyMap.keySet().iterator();
        while (keys.hasNext()) {
            String keyName = (String) keys.next();
            Object value = keyMap.get(keyName);
            if (value != null) {
                allColumns.put(keyName, value);
            } else {
                keyToGenerate = keyName;
            }
        }

        // Execute Statement
        PreparedStatement stmt = getInsertStatement(folder, allColumns);
        int rows = stmt.executeUpdate();
        if (rows != 1) {
            throw new WGBackendException("Insert failed. Wrong number of inserted rows returned: " + rows);
        }

        if (keyToGenerate == null) {
            return true;
        }

        // Try to retrieve generated key and store it at the bean and the key map
        if (!stmt.getConnection().getMetaData().supportsGetGeneratedKeys()) {
            throw new WGBackendException(
                    "Row was inserted but JDBC Driver does not support returning of generated keys. Usage of a table with generated key is not possible with this driver.");
        }
        ResultSet generatedKeys = stmt.getGeneratedKeys();
        generatedKeys.first();
        Object generatedKey = generatedKeys.getObject(1);
        valuesMap.put(keyToGenerate, generatedKey);
        keyMap.put(keyToGenerate, generatedKey);

        if (getConnection().getAutoCommit() == false) {
            getConnection().commit();
        }

        return true;

        /*String whereClause = getWhereClause(folder, key);
        resultSet = getTableResultSet(folder, whereClause, true);
                
        if (resultSet != null) {
        startResultSet(resultSet);
          if (!resultSet.next()) {
             resultSet.moveToInsertRow();
             pushRowData(resultSet, (Map) bean);
             resultSet.insertRow();
             if (getConnection().getAutoCommit() == false) {
           getConnection().commit();
             }
             return true;
          }
          else {
             throw new WGBackendException("The key '" + key + "' already exists in table '" + folder + "'");
          }
        }
        else {
          return false;
        }*/

    } catch (SQLException e) {
        try {
            if (getConnection().getAutoCommit() == false) {
                getConnection().rollback();
            }
        } catch (SQLException e1) {
            Logger.getLogger(LOGGER_NAME).error("Error rolling back content insertion", e);
        }
        throw new WGBackendException("Error inserting row", e);
    } finally {
        closeResultSet(resultSet);
    }

}

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  w  w  . j av a  2s.c o 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.chiorichan.database.DatabaseEngine.java

public int getRowCount(ResultSet rs) {
    try {/*  w  ww.  j  a v  a  2s  .c om*/
        // int curRow = rs.getRow();
        rs.last();
        int lastRow = rs.getRow();
        rs.first(); // TODO: Set the row???
        return lastRow;
    } catch (Exception e) {
        return 0;
    }
}

From source file:org.eclipse.ecr.core.storage.sql.jdbc.JDBCMapper.java

@Override
public PartialList<Serializable> query(String query, QueryFilter queryFilter, boolean countTotal)
        throws StorageException {
    QueryMaker queryMaker = findQueryMaker(query);
    if (queryMaker == null) {
        throw new StorageException("No QueryMaker accepts query: " + query);
    }//from w  ww .jav  a 2  s.  co  m
    QueryMaker.Query q = queryMaker.buildQuery(sqlInfo, model, pathResolver, query, queryFilter);

    if (q == null) {
        logger.log("Query cannot return anything due to conflicting clauses");
        return new PartialList<Serializable>(Collections.<Serializable>emptyList(), 0);
    }

    long limit = queryFilter.getLimit();
    long offset = queryFilter.getOffset();

    if (logger.isLogEnabled()) {
        String sql = q.selectInfo.sql;
        if (limit != 0) {
            sql += " -- LIMIT " + limit + " OFFSET " + offset;
        }
        if (countTotal) {
            sql += " -- COUNT TOTAL";
        }
        logger.logSQL(sql, q.selectParams);
    }

    String sql = q.selectInfo.sql;

    if (!countTotal && limit > 0 && sqlInfo.dialect.supportsPaging()) {
        // full result set not needed for counting
        sql += " " + sqlInfo.dialect.getPagingClause(limit, offset);
        limit = 0;
        offset = 0;
    }

    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        int i = 1;
        for (Object object : q.selectParams) {
            if (object instanceof Calendar) {
                Calendar cal = (Calendar) object;
                Timestamp ts = new Timestamp(cal.getTimeInMillis());
                ps.setTimestamp(i++, ts, cal); // cal passed for timezone
            } else if (object instanceof String[]) {
                Array array = sqlInfo.dialect.createArrayOf(Types.VARCHAR, (Object[]) object, connection);
                ps.setArray(i++, array);
            } else {
                ps.setObject(i++, object);
            }
        }
        ResultSet rs = ps.executeQuery();

        // limit/offset
        long totalSize = -1;
        boolean available;
        if ((limit == 0) || (offset == 0)) {
            available = rs.first();
            if (!available) {
                totalSize = 0;
            }
            if (limit == 0) {
                limit = -1; // infinite
            }
        } else {
            available = rs.absolute((int) offset + 1);
        }

        Column column = q.selectInfo.whatColumns.get(0);
        List<Serializable> ids = new LinkedList<Serializable>();
        int rowNum = 0;
        while (available && (limit != 0)) {
            Serializable id = column.getFromResultSet(rs, 1);
            ids.add(id);
            rowNum = rs.getRow();
            available = rs.next();
            limit--;
        }

        // total size
        if (countTotal && (totalSize == -1)) {
            if (!available && (rowNum != 0)) {
                // last row read was the actual last
                totalSize = rowNum;
            } else {
                // available if limit reached with some left
                // rowNum == 0 if skipped too far
                rs.last();
                totalSize = rs.getRow();
            }
        }

        if (logger.isLogEnabled()) {
            logger.logIds(ids, countTotal, totalSize);
        }

        return new PartialList<Serializable>(ids, totalSize);
    } catch (Exception e) {
        checkConnectionReset(e);
        throw new StorageException("Invalid query: " + query, e);
    } finally {
        if (ps != null) {
            try {
                closeStatement(ps);
            } catch (SQLException e) {
                log.error("Cannot close connection", e);
            }
        }
    }
}

From source file:br.org.indt.ndg.server.client.TemporaryOpenRosaBussinessDelegate.java

private void saveResultsToFilesForDeviceId(String deviceId, String saveDir) {
    ResultSet resultsSet = null;
    PreparedStatement listUsersStmt = null;
    Connection conn = null;//from  www  .  ja va2 s . com
    try {
        conn = getDbConnection();
        listUsersStmt = conn.prepareStatement(SELECT_ALL_RESULTS_FOR_USER_STATEMENT,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        listUsersStmt.setString(1, deviceId);
        resultsSet = listUsersStmt.executeQuery();
        try {
            boolean isValidRow = resultsSet.first();
            while (isValidRow) {
                String resultId = resultsSet.getString(RESULT_ID_COLUMN);
                String resultContent = resultsSet.getString(RESULT_CONTENT_COLUMN);
                FileOutputStream fileOutputStream = null;
                try {
                    fileOutputStream = new FileOutputStream(saveDir + resultId + ".xml");
                    fileOutputStream.write(resultContent.getBytes());
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    if (fileOutputStream != null)
                        try {
                            fileOutputStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                }
                isValidRow = resultsSet.next();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            listUsersStmt.close();
            conn.close();
        } catch (Exception e) {
        }
    }
}

From source file:org.geowebcache.storage.jdbc.jobstore.JDBCJobWrapper.java

/**
 * Checks / creates the "variables" table and verifies that the db_version variable is.
 * //from  w w w . j  av  a 2 s  .  c  o m
 * @param conn
 * @throws SQLException
 * @throws StorageException
 *             if the database is newer than the software
 */
protected int getDbVersion(Connection conn) throws SQLException, ConfigurationException {

    condCreate(conn, "VARIABLES", "KEY VARCHAR(32), VALUE VARCHAR(128)", "KEY", null);

    Statement st = null;
    ResultSet rs = null;

    try {
        st = conn.createStatement();
        rs = st.executeQuery("SELECT VALUE FROM VARIABLES WHERE KEY LIKE 'db_version'");

        if (rs.first()) {
            // Check what version of the database this is
            String db_versionStr = rs.getString("value");
            int cur_db_version = Integer.parseInt(db_versionStr);
            return cur_db_version;
        } else {
            // This is a new database, insert current value
            st.execute("INSERT INTO VARIABLES (KEY,VALUE) " + " VALUES ('db_version',"
                    + JDBCJobWrapper.DB_VERSION + ")");

            return JDBCJobWrapper.DB_VERSION;
        }
    } finally {
        close(rs);
        close(st);
    }
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#getApplication(java.lang.String)
 *///  w  w  w .  j a  v  a 2  s  .  c om
public synchronized List<Object> getApplication(final String value) throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#getApplication(final String value) throws SQLException";

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

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

    try {
        sqlConn = dataSource.getConnection();

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

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

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

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

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

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

                responseData = new ArrayList<Object>(Arrays.asList(resultSet.getString(1), // APPLICATION_GUID
                        resultSet.getString(2), // APPLICATION_NAME
                        resultSet.getDouble(3), // APPLICATION_VERSION
                        resultSet.getString(4), // INSTALLATION_PATH
                        resultSet.getString(5), // PACKAGE_LOCATION
                        resultSet.getString(6), // PACKAGE_INSTALLER
                        resultSet.getString(7), // INSTALLER_OPTIONS
                        resultSet.getString(8), // LOGS_DIRECTORY
                        resultSet.getString(9) // PLATFORM_GUID
                ));

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

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

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

    return responseData;
}