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:com.concursive.connect.web.modules.profile.utils.ProjectUtils.java

public static int retrieveWebcastIdFromProjectId(Connection db, int projectId) throws SQLException {
    int webcastId = -1;
    PreparedStatement pst = db
            .prepareStatement("SELECT webcast_id " + "FROM project_webcast " + "WHERE project_id = ? ");
    pst.setInt(1, projectId);//from  w ww  .  j  a v a  2s. c om
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        webcastId = rs.getInt("webcast_id");
    }
    rs.close();
    pst.close();

    return webcastId;
}

From source file:com.tethrnet.manage.db.SystemDB.java

/**
 * method to do order by based on the sorted set object for systems for user
 *
 * @param sortedSet sorted set object/*from   w  w  w.j  a v a2s  .  c o  m*/
 * @param userId    user id
 * @return sortedSet with list of host systems
 */
public static SortedSet getUserSystemSet(SortedSet sortedSet, Long userId) {
    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from system where id in (select distinct system_id from  system_map m, user_map um where m.profile_id=um.profile_id and um.user_id=? ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)) ? " and um.profile_id=? "
            : "";
    sql += ") " + orderBy;

    //get user for auth token
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, userId);
        //filter by profile id if exists
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(2, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString("display_nm"));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString("authorized_keys"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(hostSystemList);
    return sortedSet;

}

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

/**
 * method to do order by based on the sorted set object for systems for user
 *
 * @param sortedSet sorted set object//from   ww  w . j ava 2 s  .  c  o m
 * @param userId    user id
 * @return sortedSet with list of host systems
 */
public static SortedSet getUserSystemSet(SortedSet sortedSet, Long userId) {
    List<HostSystem> hostSystemList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from system where id in (select distinct system_id from  system_map m, user_map um where m.profile_id=um.profile_id and um.user_id=? ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)) ? " and um.profile_id=? "
            : "";
    sql += ") " + orderBy;

    //get user for auth token
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, userId);
        //filter by profile id if exists
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(2, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString(DISPLAY_NM));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString(AUTHORIZED_KEYS));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(hostSystemList);
    return sortedSet;

}

From source file:com.wso2telco.dep.reportingservice.dao.OperatorDAO.java

/**
 * Gets the applications by operator.//from   ww w  .j ava  2  s .  co  m
 *
 * @param operatorName the operator name
 * @return the applications by operator
 * @throws APIMgtUsageQueryServiceClientException the API mgt usage query service client exception
 * @throws SQLException the SQL exception
 */
public static List<Integer> getApplicationsByOperator(String operatorName)
        throws APIMgtUsageQueryServiceClientException, SQLException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT opcoApp.applicationid FROM " + ReportingTable.OPERATORAPPS + " opcoApp INNER JOIN "
            + ReportingTable.OPERATORS
            + " opco ON opcoApp.operatorid = opco.id WHERE opco.operatorname =? AND opcoApp.isactive = 1";
    List<Integer> applicationIds = new ArrayList<Integer>();
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        ps.setString(1, operatorName);
        log.debug("getApplicationsByOperator");
        results = ps.executeQuery();
        while (results.next()) {
            int temp = results.getInt("applicationid");
            applicationIds.add(temp);
        }
    } catch (Exception e) {
        log.error("Error occured while getting application ids from the database" + e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, results);
    }
    return applicationIds;
}

From source file:com.wso2telco.dep.reportingservice.dao.OperatorDAO.java

/**
 * Gets the operator names by application.
 *
 * @param applicationId the application id
 * @return the operator names by application
 * @throws APIMgtUsageQueryServiceClientException the API mgt usage query service client exception
 * @throws SQLException the SQL exception
 *///from   www. j a v  a  2  s. c o  m
public static List<String> getOperatorNamesByApplication(int applicationId)
        throws APIMgtUsageQueryServiceClientException, SQLException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT opco.operatorname FROM " + ReportingTable.OPERATORAPPS + " opcoApp INNER JOIN "
            + ReportingTable.OPERATORS
            + " opco ON opcoApp.operatorid = opco.id  WHERE opcoApp.applicationid =? AND opcoApp.isactive = 1";
    List<String> operatorNames = new ArrayList<String>();
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        ps.setInt(1, applicationId);
        log.debug("getOperatorNamesByApplication");
        results = ps.executeQuery();
        while (results.next()) {
            String temp = results.getString("operatorname");
            operatorNames.add(temp);
        }
    } catch (Exception e) {
        log.error("Error occured while getting operator names from the database" + e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, results);
    }

    return operatorNames;
}

From source file:com.wso2telco.dep.subscriptionvalidator.util.ValidatorDBUtils.java

/**
 * Gets the validator class for subscription.
 *
 * @param applicationId the application id
 * @param apiId the api id/*from   w  ww  .j  av a  2  s.  com*/
 * @return the validator class for subscription
 * @throws ValidatorException the validator exception
 */
public static String getValidatorClassForSubscription(int applicationId, int apiId) throws ValidatorException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT class FROM validator, subscription_validator "
            + "WHERE subscription_validator.application_id=? AND subscription_validator.api_id=? AND "
            + "validator.id=subscription_validator.validator_id";
    String validatorClass = null;
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        log.debug("getValidatorClassForSubscription for applicationId---> " + applicationId + " apiId--> "
                + apiId);
        ps.setInt(1, applicationId);
        ps.setInt(2, apiId);
        results = ps.executeQuery();
        while (results.next()) {
            validatorClass = results.getString("class");
        }
    } catch (Exception e) {
        handleException("Error occured while getting Validator Class for App: " + applicationId + " API: "
                + apiId + " from the database", e);
    } finally {
        closeAllConnections(ps, conn, results);
    }
    return validatorClass;
}

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

/**
 * Select count SQL query executor.//from  w ww.ja  v a  2 s. c o  m
 * @param con
 * @param selectCountQuery
 * @param params
 * @throws SQLException
 */
public static <O> long selectCount(Connection con, String selectCountQuery, Collection<O> params)
        throws SQLException {
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
        prepStmt = con.prepareStatement(selectCountQuery);
        DBUtil.setParameters(prepStmt, params);
        rs = prepStmt.executeQuery();
        rs.next();
        long count = rs.getLong(1);
        if (rs.next()) {
            throw new IllegalArgumentException("select count execution error");
        }
        return count;
    } finally {
        DBUtil.close(rs, prepStmt);
    }
}

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

/**
 * Get Operators' Properties.//  w  w w  .j  a  va2  s.  c o  m
 *
 * @return operators properties map.
 * @throws SQLException    on errors.
 * @throws NamingException on errors.
 */
public static Map<String, Operator> getOperatorProperties() throws SQLException, NamingException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    Map<String, Operator> operatorProperties = new HashMap<String, Operator>();
    String queryToGetOperatorProperties = "SELECT ID, operatorName, requiredIPValidation, ipHeader FROM operators";
    try {
        connection = getConnectDBConnection();
        preparedStatement = connection.prepareStatement(queryToGetOperatorProperties);
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            Operator operator = new Operator();
            int operatorId = resultSet.getInt(AuthProxyConstants.ID);
            String operatorName = resultSet.getString(AuthProxyConstants.OPERATOR_NAME);
            boolean requiredIPValidation = resultSet.getBoolean(AuthProxyConstants.REQUIRED_IP_VALIDATION);
            String ipHeader = resultSet.getString(AuthProxyConstants.IP_HEADER);
            operator.setOperatorId(operatorId);
            operator.setOperatorName(operatorName);
            operator.setRequiredIpValidation(requiredIPValidation);
            operator.setIpHeader(ipHeader);
            operatorProperties.put(operatorName, operator);
        }
    } catch (SQLException e) {
        throw new SQLException("Error occurred while retrieving operator properties.", e);
    } catch (NamingException e) {
        throw new ConfigurationException("DataSource could not be found in mobile-connect.xml");
    } finally {
        closeAllConnections(preparedStatement, connection, resultSet);
    }
    return operatorProperties;
}

From source file:com.concursive.connect.web.modules.profile.utils.ProjectUtils.java

private static String generateUniqueId(String title, int projectId, Connection db) throws SQLException {
    // Title can look like...
    // Some Project Name
    // some-project-name
    // some-project-name-2

    // Format to allowed characters to get extension (some will be treated later)
    String allowed = "abcdefghijklmnopqrstuvwxyz1234567890-/& ";
    String nameToSearch = StringUtils.toAllowedOnly(allowed, title.trim().toLowerCase());
    if (!StringUtils.hasText(nameToSearch)) {
        nameToSearch = "listing";
    }/*from  w  w w  .j  a va2  s.  co  m*/

    // Break out any numbered extension: ex. name-5
    String originalExtension = null;
    int dotIndex = nameToSearch.lastIndexOf("-");
    if (dotIndex > -1 && dotIndex + 1 < nameToSearch.length()) {
        if (StringUtils.isNumber(nameToSearch.substring(dotIndex + 1))) {
            originalExtension = nameToSearch.substring(dotIndex);
            nameToSearch = nameToSearch.substring(0, dotIndex);
        }
    }

    // Convert spaces to - for url compliance and search engine readability
    nameToSearch = StringUtils.replace(nameToSearch, " ", "-");
    nameToSearch = StringUtils.replace(nameToSearch, "&", "and");
    nameToSearch = StringUtils.replace(nameToSearch, "/", "-");

    // See if there is a dupe in the database, and retrieve the latest value
    boolean originalExtensionExists = false;
    PreparedStatement pst = db.prepareStatement(
            "SELECT project_id, projecttextid " + "FROM projects " + "WHERE projecttextid LIKE ? ");
    pst.setString(1, nameToSearch + "%");
    ResultSet rs = pst.executeQuery();
    long value = 0;
    while (rs.next()) {
        long thisProjectId = rs.getLong("project_id");
        String thisTextId = rs.getString("projecttextid");
        // If it already owns this id, then keep it
        if (projectId > -1 && projectId == thisProjectId && nameToSearch.equals(thisTextId)) {
            return nameToSearch;
        }
        if (originalExtension != null) {
            if (thisTextId.equals(nameToSearch + originalExtension)) {
                originalExtensionExists = true;
            }
        }
        // Only compare to this name exactly, or this named iteration
        if (thisTextId.equals(nameToSearch)) {
            if (1 > value) {
                value = 1;
            }
        }

        if (thisTextId.startsWith(nameToSearch + "-")) {
            String foundExtensionValue = thisTextId.substring(thisTextId.lastIndexOf("-") + 1);
            if (StringUtils.isNumber(foundExtensionValue)) {
                try {
                    long thisValue = Long.parseLong(foundExtensionValue);
                    if (thisValue > value) {
                        value = thisValue;
                    }
                } catch (Exception e) {
                    // The extension is big... so add another extension
                    rs.close();
                    pst.close();
                    return generateUniqueId(nameToSearch + "-2", projectId, db);
                }
            }
        }
    }
    if (originalExtension != null && !originalExtensionExists) {
        return (nameToSearch + originalExtension);
    }
    // Set this one accordingly
    if (value == 0) {
        return nameToSearch;
    } else {
        ++value;
        return (nameToSearch + "-" + value);
    }
}

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

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

    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;

}