Example usage for java.sql ResultSet beforeFirst

List of usage examples for java.sql ResultSet beforeFirst

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

Introduction

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

Prototype

void beforeFirst() throws SQLException;

Source Link

Document

Moves the cursor to the front of this ResultSet object, just before the first row.

Usage

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#listApplications(int)
 *//* www .ja  va2s  .  c o m*/
public synchronized List<String[]> listApplications(final int startRow) throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#listApplications(final int startRow) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> 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 listApplications(?, ?)}");
        stmt.setInt(1, startRow);
        stmt.registerOutParameter(2, Types.INTEGER);

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

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

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

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<String[]>();

                while (resultSet.next()) {
                    String[] data = new String[] { resultSet.getString(1), // APPLICATION_GUID
                            resultSet.getString(2), // APPLICATION_NAME
                    };

                    if (DEBUG) {
                        DEBUGGER.debug("Value: {}", (Object[]) data);
                    }

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", 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;
}

From source file:com.krminc.phr.security.PHRRealm.java

/**
 * Returns names of all the groups in this particular realm.
 *
 * @return enumeration of group names (strings)
 *
 *//*from  www.j av  a  2  s.c o  m*/
public Enumeration getGroupNames() throws BadRealmException {
    //check role cache before querying

    String query = "SELECT UNIQUE role FROM user_roles";
    ResultSet rs = null;
    Vector roles = new Vector();
    PreparedStatement st = null;
    try {
        createDS();
        conn = ds.getNonTxConnection();
        st = conn.prepareStatement(query);
        rs = st.executeQuery();
    } catch (Exception e) {
        log("Error getting roles from database");
        log(e.getMessage());
        rs = null;
    } finally {
        try {
            conn.close();
        } catch (Exception e) {
            log(e.getMessage());
        }
        conn = null;
    }
    if (rs != null) {
        try {
            rs.beforeFirst();
            while (rs.next()) {
                roles.add(rs.getString(1));
            }
        } catch (Exception e) {
            log("Error getting roles from resultset");
            log(e.getMessage());
        } finally {
            try {
                st.close();
                rs.close();
            } catch (Exception e) {
                log(e.getMessage());
            }
        }
    }
    return roles.elements();
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#listServices(int)
 *///from ww w.  jav a2  s .  co m
public synchronized List<String[]> listServices(final int startRow) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME + "#listServices(final int startRow) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> 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 listServices(?)}");
        stmt.setInt(1, startRow);

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

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

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

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<String[]>();

                while (resultSet.next()) {
                    String[] data = new String[] { resultSet.getString(1), // GUID
                            resultSet.getString(2), // SERVICE_TYPE
                            resultSet.getString(3), // NAME
                    };

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("List<String>: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), 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;
}

From source file:com.krminc.phr.security.PHRRealm.java

/**
 * Returns names of all the users in this particular realm.
 *
 * @return enumeration of user names/* w w  w  .  j a  v a  2  s  .  co  m*/
 *
 */
public Enumeration getUserNames() throws BadRealmException {
    String query = "SELECT username FROM user_users";
    ResultSet rs = null;
    Vector usernames = new Vector();
    PreparedStatement st = null;
    try {
        createDS();
        conn = ds.getNonTxConnection();
        st = conn.prepareStatement(query);
        rs = st.executeQuery();
    } catch (Exception e) {
        log("Error getting usernames from database");
        log(e.getMessage());
        rs = null;
    } finally {
        try {
            conn.close();
        } catch (Exception e) {
            log(e.getMessage());
        }
        conn = null;
    }
    if (rs != null) {
        try {
            rs.beforeFirst();
            while (rs.next()) {
                usernames.add(rs.getString(1));
            }
        } catch (Exception e) {
            log("Error getting usernames from resultset");
            log(e.getMessage());
        } finally {
            try {
                st.close();
                rs.close();
            } catch (Exception e) {
                log(e.getMessage());
            }
        }
    }
    return usernames.elements();
}

From source file:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#obtainSecurityData(java.lang.String, java.lang.String)
 *//*ww  w  .  j a v a2s . c o m*/
public synchronized List<String> obtainSecurityData(final String userName, final String userGuid)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#obtainSecurityData(final String userName, final String userGuid) throws AuthenticatorException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userName);
        DEBUGGER.debug("Value: {}", userGuid);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> userSecurity = null;

    try {
        sqlConn = SQLAuthenticator.dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL getUserByAttribute(?, ?)}");
        stmt.setString(1, userName); // guid
        stmt.setInt(2, 0); // count

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

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

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

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

                while (resultSet.next()) {
                    if (StringUtils.equals(resultSet.getString(2), userName)) {
                        String cn = resultSet.getString(1);
                        String username = resultSet.getString(2);

                        if (DEBUG) {
                            DEBUGGER.debug("String: {}", cn);
                            DEBUGGER.debug("String: {}", username);
                        }

                        resultSet.close();
                        stmt.close();

                        // found the user we want
                        stmt = sqlConn.prepareCall("{ CALL getSecurityQuestions(?, ?) }");
                        stmt.setString(1, username); // common name
                        stmt.setString(2, cn);

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

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

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

                            if (resultSet.next()) {
                                userSecurity = new ArrayList<String>(
                                        Arrays.asList(resultSet.getString(1), resultSet.getString(2)));

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

            }
        }
    } catch (SQLException sqx) {
        throw new AuthenticatorException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

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

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new AuthenticatorException(sqx.getMessage(), sqx);
        }
    }

    return userSecurity;
}

From source file:com.cws.us.pws.dao.impl.ProductReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getProductList(String) throws SQLException
 *//*from w  w w. j  a va2s. co m*/
@Override
public List<Object[]> getProductList(final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getProductList(final String lang) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> results = null;

    try {
        sqlConn = this.dataSource.getConnection();

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

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

        stmt = sqlConn.prepareCall("{ CALL getProductList(?) }");
        stmt.setString(1, lang);

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

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

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

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object[]>();

            while (resultSet.next()) {
                Object[] data = new Object[] { resultSet.getString(1), // PRODUCT_ID
                        resultSet.getString(2), // PRODUCT_NAME
                        resultSet.getString(3), // PRODUCT_SHORT_DESC
                        resultSet.getString(4), // PRODUCT_DESC
                        resultSet.getBigDecimal(5), // PRODUCT_PRICE
                        resultSet.getBoolean(6) // IS_FEATURED
                };

                results.add(data);
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), 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();
        }
    }

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

    return results;
}

From source file:com.cws.us.pws.dao.impl.ProductReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getFeaturedProducts(String) throws SQLException
 *///from  w w w .j av  a  2  s .  c om
@Override
public List<Object[]> getFeaturedProducts(final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getFeaturedProducts(final String lang) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> results = null;

    try {
        sqlConn = this.dataSource.getConnection();

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

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

        stmt = sqlConn.prepareCall("{ CALL getFeaturedProducts(?) }");
        stmt.setString(1, lang);

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

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

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

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object[]>();

            while (resultSet.next()) {
                Object[] data = new Object[] { resultSet.getString(1), // PRODUCT_ID
                        resultSet.getString(2), // PRODUCT_NAME
                        resultSet.getString(3), // PRODUCT_SHORT_DESC
                        resultSet.getString(4), // PRODUCT_DESC
                        resultSet.getBigDecimal(5), // PRODUCT_PRICE
                        resultSet.getBoolean(6) // IS_FEATURED
                };

                results.add(data);
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), 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();
        }
    }

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

    return results;
}

From source file:org.efaps.db.databases.MySQLDatabase.java

/**
     * <p>This is the MySQL specific implementation of an all deletion.
     * Following order is used to remove all eFaps specific information:
     * <ul>//from  w ww .  j  av a  2s  . c  o m
     * <li>remove all views of the user</li>
     * <li>remove all tables of the user</li>
     * <li>remove all sequences of the user</li>
     * </ul></p>
     * <p>The table are dropped with cascade, so all depending sequences etc.
     * are also dropped automatically. </p>
     * <p>Attention! If application specific tables, views or constraints are
     * defined, this database objects are also removed!</p>
     *
     * @param _con sql connection
     * @throws SQLException on error while executing sql statements
     */
@Override
@SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE")
public void deleteAll(final Connection _con) throws SQLException {

    final Statement stmtSel = _con.createStatement();
    final Statement stmtExec = _con.createStatement();

    try {
        if (MySQLDatabase.LOG.isInfoEnabled()) {
            MySQLDatabase.LOG.info("Remove all Tables");
        }

        final DatabaseMetaData metaData = _con.getMetaData();

        // delete all views
        final ResultSet rsViews = metaData.getTables(null, null, "%", new String[] { "VIEW" });
        while (rsViews.next()) {
            final String viewName = rsViews.getString("TABLE_NAME");
            if (MySQLDatabase.LOG.isDebugEnabled()) {
                MySQLDatabase.LOG.debug("  - View '" + viewName + "'");
            }
            stmtExec.execute("drop view " + viewName);
        }
        rsViews.close();

        // delete all constraints
        final ResultSet rsTables = metaData.getTables(null, null, "%", new String[] { "TABLE" });
        while (rsTables.next()) {
            final String tableName = rsTables.getString("TABLE_NAME");
            final ResultSet rsf = _con.getMetaData().getImportedKeys(null, null, tableName);
            while (rsf.next()) {
                final String fkName = rsf.getString("FK_NAME").toUpperCase();
                if (MySQLDatabase.LOG.isDebugEnabled()) {
                    MySQLDatabase.LOG.debug("  - Foreign Key '" + fkName + "'");
                }
                stmtExec.execute("alter table " + tableName + " drop foreign key " + fkName);
            }
        }

        // delete all tables
        rsTables.beforeFirst();
        while (rsTables.next()) {
            final String tableName = rsTables.getString("TABLE_NAME");
            if (MySQLDatabase.LOG.isDebugEnabled()) {
                MySQLDatabase.LOG.debug("  - Table '" + tableName + "'");
            }
            stmtExec.execute("drop table " + tableName + " cascade");
        }
        rsTables.close();

    } finally {
        stmtSel.close();
        stmtExec.close();
    }
}

From source file:com.krminc.phr.security.PHRRealm.java

/**
 * Returns enumeration of groups that a particular user belongs to.
 *
 *@exception NoSuchUserException// www .j a  va 2s. c  om
 */
public Enumeration getGroupNames(String user) throws NoSuchUserException {
    //check user cache before querying?

    String query = "SELECT DISTINCT role FROM user_roles WHERE username = ?";
    ResultSet rs = null;
    Vector roles = new Vector();
    PreparedStatement st = null;
    try {
        createDS();
        conn = ds.getNonTxConnection();
        st = conn.prepareStatement(query);
        st.setString(1, user);
        rs = st.executeQuery();
    } catch (Exception e) {
        log("Error getting roles from database");
        log(e.getMessage());
        rs = null;
    } finally {
        try {
            conn.close();
        } catch (Exception e) {
            log(e.getMessage());
        }
        conn = null;
    }
    if (rs != null) {
        try {
            rs.beforeFirst();
            while (rs.next()) {
                roles.add(rs.getString(1));
            }
        } catch (Exception e) {
            log("Error getting roles from resultset");
            log(e.getMessage());
        } finally {
            try {
                st.close();
                rs.close();
            } catch (Exception e) {
                log(e.getMessage());
            }
        }
    } else {
        throw new NoSuchUserException("User not available.");
    }
    return roles.elements();
}

From source file:com.cws.us.pws.dao.impl.ProductReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getProductData(String, String) throws SQLException
 *///from w  w w. j  a v a 2s .co m
@Override
public List<Object> getProductData(final String productId, final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getProductData(final int productId, final String lang) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", productId);
        DEBUGGER.debug("Value: {}", lang);
    }

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

    try {
        sqlConn = this.dataSource.getConnection();

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

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

        stmt = sqlConn.prepareCall("{ CALL getProductData(?, ?) }");
        stmt.setString(1, productId);
        stmt.setString(2, lang);

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

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

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

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object>();

            while (resultSet.next()) {
                results.add(resultSet.getString(1)); // PRODUCT_ID
                results.add(resultSet.getString(2)); // PRODUCT_NAME
                results.add(resultSet.getString(3)); // PRODUCT_SHORT_DESC
                results.add(resultSet.getString(4)); // PRODUCT_DESC
                results.add(resultSet.getBigDecimal(5)); // PRODUCT_PRICE
                results.add(resultSet.getBoolean(6)); // IS_FEATURED
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), 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();
        }
    }

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

    return results;
}