Example usage for java.sql PreparedStatement executeQuery

List of usage examples for java.sql PreparedStatement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. executeQuery

Introduction

In this page you can find the example usage for java.sql PreparedStatement executeQuery.

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:eu.sisob.uma.restserver.AuthorizationManager.java

private static boolean DBAuthorizeUserIn(String user, String pass) {
    boolean success = false;
    Connection conn = null;/*from   w w w .  ja  va2  s  .  c  o  m*/
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        String query = "SELECT 1 FROM USERS WHERE user_email = ? and user_pass = ?";
        conn = SystemManager.getInstance().getSystemDbPool().getConnection();
        statement = conn.prepareStatement(query);
        statement.setString(1, user);
        statement.setString(2, pass);

        rs = statement.executeQuery();
        if (rs.next())
            success = true;
        else
            success = false;
    } catch (SQLException ex) {
        ProjectLogger.LOGGER.error("", ex);
        success = false;
    } catch (Exception ex) {
        ProjectLogger.LOGGER.error("", ex);
        success = false;
    } finally {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        if (statement != null)
            try {
                statement.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        statement = null;
        rs = null;
    }

    return success;
}

From source file:com.keybox.manage.db.SessionAuditDB.java

/**
 * returns sessions based on sort order defined
 *
 * @param sortedSet object that defines sort order
 * @return session list/*from  ww  w . j a  va 2s .  com*/
 */
public static SortedSet getSessions(SortedSet sortedSet) {
    //get db connection
    Connection con = null;
    List<SessionAudit> outputList = new LinkedList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = " order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }

    String sql = "select * from session_log, users where users.id= session_log.user_id ";
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID))
            ? " and session_log.user_id=? "
            : "";
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM_ID))
            ? " and session_log.id in ( select session_id from terminal_log where terminal_log.system_id=? ) "
            : "";
    sql += orderBy;

    try {

        con = DBUtils.getConn();
        deleteAuditHistory(con);

        PreparedStatement stmt = con.prepareStatement(sql);
        int i = 1;
        //set filters in prepared statement
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID))) {
            stmt.setLong(i++, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_USER_ID)));
        }
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM_ID))) {
            stmt.setLong(i, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM_ID)));
        }

        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            SessionAudit sessionAudit = new SessionAudit();
            sessionAudit.setId(rs.getLong("session_log.id"));
            sessionAudit.setSessionTm(rs.getTimestamp(SESSION_TM));
            sessionAudit.setUser(UserDB.getUser(con, rs.getLong(USER_ID)));
            outputList.add(sessionAudit);

        }

        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    //close db connection
    DBUtils.closeConn(con);

    sortedSet.setItemList(outputList);

    return sortedSet;

}

From source file:com.stratelia.webactiv.util.DBUtil.java

public static int getMaxFromTable(Connection con, String tableName, String idName) {
    if (!StringUtil.isDefined(tableName) || !StringUtil.isDefined(idName)) {
        return 1;
    }//  w  w w .  j  av  a2s  .  c  o m
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
        int maxFromTable = 0;
        String nextPKStatement = "SELECT MAX(" + idName + ") " + "FROM " + tableName;
        prepStmt = con.prepareStatement(nextPKStatement);
        rs = prepStmt.executeQuery();
        if (rs.next()) {
            maxFromTable = rs.getInt(1);
        }
        return maxFromTable + 1;
    } catch (SQLException ex) {
        rollback(con);
        return 1;
    } finally {
        close(rs, prepStmt);
    }
}

From source file:com.wso2telco.dep.verificationhandler.verifier.DatabaseUtils.java

/**
 * Read blacklist numbers./*from   w w  w . j a  va 2 s.  c o m*/
 *
 * @param apiId the api name
 * @return the list
 * @throws SQLException the SQL exception
 * @throws NamingException the naming exception
 * @deprecated
 */
@Deprecated
public static List<String> ReadBlacklistNumbers(String apiId) throws SQLException, NamingException {

    String sql = "select * from blacklistmsisdn where API_ID = ?";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        conn = getStatsDBConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, apiId);

        rs = ps.executeQuery();

        if (rs != null) {
            while (rs.next()) {
                String msisdnTable = rs.getString("MSISDN").replace("tel3A+", "");
                log.debug("msisdn in the table = " + msisdnTable);
                msisdn.add(msisdnTable);

            }
        }

    } catch (SQLException e) {
        log.error("Error occured while writing southbound record.", e);
        throw e;
    } catch (NamingException e) {
        log.error("Error while finding the Datasource..", e);
        throw e;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
    }

    return msisdn;

}

From source file:com.wso2telco.dep.verificationhandler.verifier.DatabaseUtils.java

/**
 * Read whitelist numbers./*from   ww  w . ja  v a  2  s  .c  o  m*/
 *
 * @param subscriptionID the subscription id
 * @return the list
 * @throws SQLException the SQL exception
 * @throws NamingException the naming exception
 */
public static List<String> ReadWhitelistNumbers(String subscriptionID) throws SQLException, NamingException {

    String sql = "select msisdn " + "from subscription_WhiteList where " + "subscriptionID=?;";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        conn = getStatsDBConnection();
        ps = conn.prepareStatement(sql);

        ps.setString(1, subscriptionID);

        rs = ps.executeQuery();
        whitelistedmsisdn.clear();
        if (rs != null) {

            while (rs.next()) {
                String msisdnTable = rs.getString("msisdn").replace("tel3A+", "");
                log.info("msisdn in the table = " + msisdnTable);
                whitelistedmsisdn.add(msisdnTable);

            }
        }

    } catch (SQLException e) {
        log.error("Error occured while writing southbound record.", e);
        throw e;
    } catch (NamingException e) {
        log.error("Error while finding the Datasource.", e);
        throw e;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
    }
    return whitelistedmsisdn;
}

From source file:com.wso2telco.proxy.util.DBUtils.java

private static List<LoginHintFormatDetails> getLoginHintFormatTypeDetails(int paramId, Connection conn)
        throws AuthenticatorException, SQLException {
    PreparedStatement ps = null;
    ResultSet results = null;/*  w w  w.  j a v a  2s .c om*/
    String sql = "SELECT * FROM `login_hint_format` WHERE `format_id` IN (SELECT `format_id` FROM "
            + "`scope_supp_login_hint_format` WHERE `param_id` = ?);";

    if (log.isDebugEnabled()) {
        log.debug("Executing the query : " + sql);
    }

    List<LoginHintFormatDetails> loginHintFormatDetails = new ArrayList<LoginHintFormatDetails>();
    try {
        ps = conn.prepareStatement(sql);
        ps.setInt(1, paramId);
        results = ps.executeQuery();

        while (results.next()) {
            LoginHintFormatDetails loginHintFormat = new LoginHintFormatDetails();
            loginHintFormat.setFormatType(
                    LoginHintFormatDetails.loginHintFormatTypes.valueOf(results.getString("type")));
            loginHintFormat.setEncrypted(results.getBoolean("is_encrypted"));
            loginHintFormat.setDecryptAlgorithm(results.getString("decrypt_algorithm"));
            loginHintFormatDetails.add(loginHintFormat);
        }
    } catch (SQLException e) {
        //using the same connection to avoid connection pool exhaust exception within the loop. SQL exception to
        // be handled in the parent function.
        log.error("Error occurred while getting login format details from the database", e);
        throw e;
    } finally {
        closeAllConnections(ps, null, results);
    }
    return loginHintFormatDetails;
}

From source file:com.concursive.connect.web.modules.documents.dao.FileFolder.java

/**
 * Description of the Method//from  w  w w  .  j  av a 2  s  . c o  m
 *
 * @param db        Description of the Parameter
 * @param hierarchy Description of the Parameter
 * @param currentId Description of the Parameter
 * @throws SQLException Description of the Exception
 */
public static void buildHierarchy(Connection db, Map hierarchy, int currentId) throws SQLException {
    PreparedStatement pst = db.prepareStatement(
            "SELECT parent_id, subject, display " + "FROM project_folders " + "WHERE folder_id = ? ");
    pst.setInt(1, currentId);
    ResultSet rs = pst.executeQuery();
    int parentId = 0;
    String subject = null;
    int display = -1;
    if (rs.next()) {
        parentId = DatabaseUtils.getInt(rs, "parent_id");
        subject = rs.getString("subject");
        display = DatabaseUtils.getInt(rs, "display");
    }
    rs.close();
    pst.close();
    hierarchy.put(new Integer(currentId), new String[] { subject, String.valueOf(display) });
    if (parentId > -1) {
        FileFolder.buildHierarchy(db, hierarchy, parentId);
    }
}

From source file:eu.sisob.uma.restserver.AuthorizationManager.java

private static boolean DBAuthorizeUserIn(String user, String pass, UserAttributes out_attributes) {
    boolean success = false;
    Connection conn = null;//  w  ww .  j a va  2  s.  co m
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        String query = "SELECT `user_tasks_allow` as n_tasks_allow, `user_type` as account_type FROM USERS WHERE user_email = ? and user_pass = ?";
        conn = SystemManager.getInstance().getSystemDbPool().getConnection();
        statement = conn.prepareStatement(query);
        statement.setString(1, user);
        statement.setString(2, pass);

        rs = statement.executeQuery();
        if (rs.next()) {

            out_attributes.setAccountType(rs.getString("account_type"));
            out_attributes.setNTasksAllow((Integer) rs.getInt("n_tasks_allow"));

            success = true;
        } else
            success = false;
    } catch (SQLException ex) {
        ProjectLogger.LOGGER.error("", ex);
        success = false;
    } catch (Exception ex) {
        ProjectLogger.LOGGER.error("", ex);
        success = false;
    } finally {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        if (statement != null)
            try {
                statement.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        statement = null;
        rs = null;
    }

    return success;
}

From source file:com.wso2telco.proxy.util.DBUtils.java

/**
 * Get operators' MSISDN header properties.
 *
 * @return operators' MSISDN header properties map.
 * @throws SQLException    on errors/*from  ww  w  . ja  v a  2 s . co  m*/
 * @throws NamingException on errors
 */
public static Map<String, List<MSISDNHeader>> getOperatorsMSISDNHeaderProperties()
        throws SQLException, NamingException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    Map<String, List<MSISDNHeader>> operatorsMSISDNHeadersList = new HashMap<String, List<MSISDNHeader>>();
    String queryToGetOperatorProperty = "SELECT DISTINCT operatorId, LOWER(operatorName) AS operatorName FROM "
            + "operators_msisdn_headers_properties prop LEFT JOIN operators op ON op.ID=prop.operatorId";
    try {
        connection = getConnectDBConnection();
        preparedStatement = connection.prepareStatement(queryToGetOperatorProperty);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int operatorId = resultSet.getInt(AuthProxyConstants.OPERATOR_ID);
            String operatorName = resultSet.getString(AuthProxyConstants.OPERATOR_NAME);
            //Get msisdn properties of the operator.
            List<MSISDNHeader> msisdnHeaderList = getMSISDNPropertiesByOperatorId(operatorId, operatorName);
            operatorsMSISDNHeadersList.put(operatorName, msisdnHeaderList);
        }
    } catch (SQLException e) {
        throw new SQLException("Error occurred while retrieving operator MSISDN properties of operators : ", e);
    } catch (NamingException e) {
        throw new ConfigurationException("DataSource could not be found in mobile-connect.xml");
    } finally {
        closeAllConnections(preparedStatement, connection, resultSet);
    }
    return operatorsMSISDNHeadersList;
}

From source file:com.stratelia.webactiv.util.DBUtil.java

private static int updateMaxFromTable(Connection connection, String tableName) throws SQLException {
    String table = tableName.toLowerCase(Locale.ROOT);
    int max = 0;//from   w  w  w.  j ava2s . com
    PreparedStatement prepStmt = null;
    int count = 0;
    try {
        prepStmt = connection.prepareStatement("UPDATE UniqueId SET maxId = maxId + 1 WHERE tableName = ?");
        prepStmt.setString(1, table);
        count = prepStmt.executeUpdate();
        connection.commit();
    } catch (SQLException sqlex) {
        rollback(connection);
        throw sqlex;
    } finally {
        close(prepStmt);
    }

    if (count == 1) {
        PreparedStatement selectStmt = null;
        ResultSet rs = null;
        try {
            // l'update c'est bien passe, on recupere la valeur
            selectStmt = connection.prepareStatement("SELECT maxId FROM UniqueId WHERE tableName = ?");
            selectStmt.setString(1, table);
            rs = selectStmt.executeQuery();
            if (!rs.next()) {
                SilverTrace.error("util", "DBUtil.getNextId", "util.MSG_NO_RECORD_FOUND");
                throw new RuntimeException("Erreur Interne DBUtil.getNextId()");
            }
            max = rs.getInt(1);
        } finally {
            close(rs, selectStmt);
        }
        return max;
    }
    throw new SQLException("Update impossible : Ligne non existante");
}