Example usage for java.sql ResultSet getInt

List of usage examples for java.sql ResultSet getInt

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

Introduction

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

Prototype

int getInt(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

Usage

From source file:com.clavain.alerts.Methods.java

public static void sendNotifications(Alert alert) {
    Integer aid = alert.getAlert_id();

    try {/* w  w  w  .  ja  va 2 s  .  c  o  m*/
        Connection conn = connectToDatabase(com.clavain.muninmxcd.p);
        java.sql.Statement stmt = conn.createStatement();

        // SELECT alert_contacts.id as nid, contacts.* FROM `alert_contacts` LEFT JOIN contacts ON alert_contacts.contact_id = contacts.id WHERE alert_id = 1
        ResultSet rs = stmt.executeQuery(
                "SELECT alert_contacts.id as nid, contacts.*,contacts.id AS contactId FROM `alert_contacts` LEFT JOIN contacts ON alert_contacts.contact_id = contacts.id WHERE alert_id = "
                        + aid);
        while (rs.next()) {
            Integer contact_id = rs.getInt("contactId");
            String dayField = getScheduleFieldToCheck();
            logger.info("[Notifications " + aid + "] Found " + rs.getString("contact_name"));
            if (rs.getString(dayField).equals("disabled")) {
                logger.info("[Notifications " + aid + "] " + rs.getString("contact_name")
                        + " disabled notifications for today - skipping contact");
            } else {
                String splitField = rs.getString(dayField);
                // figure out if this user got notifications enabled or disabled for the current hour and time
                String[] hours = splitField.split(";");
                long a = getStampFromTimeAndZone(hours[0], rs.getString("timezone"));
                long b = getStampFromTimeAndZone(hours[1], rs.getString("timezone"));
                long cur = (System.currentTimeMillis() / 1000L);
                // if in range send notifications
                if (a < cur && b > cur) {
                    String failTime = getHumanReadableDateFromTimeStampWithTimezone(alert.getLast_alert(),
                            rs.getString("timezone"));
                    String title = "ALERT: " + alert.getHostname() + " (" + alert.getPluginName() + ")";
                    String message = "Alert Time: " + failTime + ".   Details: " + alert.getAlertMsg();

                    String json = "";
                    if (rs.getInt("callback_active") == 1) {
                        logger.info("[Notifications " + aid + "] " + rs.getString("contact_name")
                                + " Sending Callback");
                        sendCallback(alert, rs.getString("contact_callback"));
                        updateNotificationLog(aid, contact_id,
                                "Callback executed to " + rs.getString("contact_callback"), "callback");
                    }
                    if (rs.getInt("tts_active") == 1) {
                        title = "This is a MuninMX Alert: Plugin: " + alert.getPluginName() + " on host "
                                + alert.getHostname() + " is in alert state.";
                        logger.info("[Notifications " + aid + "] " + rs.getString("contact_name")
                                + " Initiating TTS Call");
                        sendTTS(title, message, rs.getString("contact_mobile_nr"), rs.getInt("user_id"));
                        updateNotificationLog(aid, contact_id,
                                "Text2Speech Call initiated to " + rs.getString("contact_mobile_nr"), "tts");
                    }
                    if (rs.getInt("email_active") == 1) {
                        logger.info("[Notifications " + aid + "] " + rs.getString("contact_name")
                                + " Sending E-Mail");
                        String ENDL = System.getProperty("line.separator");
                        message = "Alert Time: " + failTime + "." + ENDL + ENDL + "Details:" + ENDL + ENDL
                                + alert.getAlertMsg();

                        sendMail(title, message, rs.getString("contact_email"));
                        updateNotificationLog(aid, contact_id,
                                "E-Mail send to " + rs.getString("contact_email"), "email");
                    }
                    if (rs.getInt("sms_active") == 1) {
                        title = alert.getHostname() + " reports ";
                        message = alert.getAlertMsg();

                        logger.info(
                                "[Notifications " + aid + "] " + rs.getString("contact_name") + " Sending SMS");
                        sendSMS(title, message, rs.getString("contact_mobile_nr"), rs.getInt("user_id"));
                        updateNotificationLog(aid, contact_id,
                                "SMS send to " + rs.getString("contact_mobile_nr"), "sms");
                    }
                    if (rs.getInt("pushover_active") == 1) {
                        logger.info("[Notifications " + aid + "] " + rs.getString("contact_name")
                                + " Sending Pushover Notification");
                        sendPushover(title, message, rs.getString("pushover_key"));
                        updateNotificationLog(aid, contact_id,
                                "PushOver Message send to " + rs.getString("pushover_key"), "pushover");
                    }

                } else {
                    logger.info("[Notifications " + aid + "] " + rs.getString("contact_name")
                            + " disabled notifications for this timerange - skipping contact");
                }
            }
        }
        conn.close();
    } catch (Exception ex) {
        logger.error("Error in sendNotifications for CID " + aid + " : " + ex.getLocalizedMessage());
        ex.printStackTrace();
    }
}

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

/**
 * Gets the applications by operator./*from  w  w w. j  av  a2s.c o 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:ece356.UserDBAO.java

public static String getSalt(String username) throws ClassNotFoundException, SQLException {
    Connection con = null;/*from   w w  w.jav a 2 s. com*/
    PreparedStatement pstmt = null;
    UserData ret;
    try {
        con = getConnection();
        String query = "select COUNT(*)as numRecords, password_salt from user INNER JOIN userType ON user.userTypeID = userType.userTypeID where user.username = ?";
        pstmt = con.prepareStatement(query);
        pstmt.setString(1, username);
        ResultSet resultSet;
        resultSet = pstmt.executeQuery();
        resultSet.next();

        if (resultSet.getInt("numRecords") > 0) {
            return resultSet.getString("password_salt");
        } else
            return null;
    } catch (Exception e) {
        System.out.println("EXCEPTION:%% " + e);
    } finally {
        if (pstmt != null) {
            pstmt.close();
        }
        if (con != null) {
            con.close();
        }
    }
    return null;
}

From source file:edu.ku.brc.specify.dbsupport.cleanuptools.LocalityCleanup.java

/**
 * //from   w  ww  .java  2s  . c  o  m
 */
public static void fixLocality() {
    String connectStr = "jdbc:mysql://localhost/";

    String dbName = "kevin";

    DBConnection dbc = new DBConnection("root", "root", connectStr + dbName, "com.mysql.jdbc.Driver",
            "org.hibernate.dialect.MySQLDialect", dbName);
    Connection conn = dbc.createConnection();
    BasicSQLUtils.setDBConnection(conn);

    try {
        Statement stmt = conn.createStatement();
        PreparedStatement pStmt = conn
                .prepareStatement("UPDATE collectingevent SET LocalityID=? WHERE CollectingEventID = ?");
        PreparedStatement delStmt = conn.prepareStatement("DELETE FROM locality WHERE LocalityID=?");
        PreparedStatement delStmt2 = conn
                .prepareStatement("DELETE FROM localitydetail WHERE LocalityDetailID=?");
        PreparedStatement delStmt3 = conn
                .prepareStatement("DELETE FROM geocoorddetail WHERE GeocoordDetailID=?");

        int fixedCnt = 0;
        String sql = "SELECT LocalityName FROM (SELECT LocalityName, COUNT(LocalityName) as cnt FROM locality GROUP BY LocalityName) T1 WHERE cnt > 1 ORDER BY cnt desc";
        for (Object[] cols : BasicSQLUtils.query(sql)) {
            String locName = cols[0].toString();

            sql = String.format(
                    "SELECT LocalityID FROM locality WHERE LocalityName = '%s' ORDER BY LocalityID ASC",
                    locName);
            System.out.println(
                    "------------------------------------" + locName + "-----------------------------------");

            Integer firstID = null;
            int c = 0;
            ResultSet rs2 = stmt.executeQuery(sql);
            while (rs2.next()) {
                int id = rs2.getInt(1);
                if (c == 0) {
                    firstID = id;
                    c = 1;
                    continue;
                }

                System.out.println("Fixing LocalityID: " + id);
                sql = String.format("SELECT CollectingEventId FROM collectingevent WHERE LocalityID = %d", id);
                Vector<Integer> ids = BasicSQLUtils.queryForInts(conn, sql);
                for (Integer ceId : ids) {
                    pStmt.setInt(1, firstID);
                    pStmt.setInt(2, ceId);
                    if (pStmt.executeUpdate() != 1) {
                        System.out.println("Error updating CE Id: " + ceId);
                    } else {
                        System.out
                                .println("Fixed CollectingEventID: " + ceId + "  with LocalityID: " + firstID);
                        fixedCnt++;
                    }
                }
                c++;

                System.out.println("Fixing LocalityID: " + id);
                sql = String.format("SELECT LocalityDetailID FROM localitydetail WHERE LocalityID = %d", id);
                ids = BasicSQLUtils.queryForInts(conn, sql);
                for (Integer ldId : ids) {
                    delStmt2.setInt(1, ldId);
                    if (delStmt2.executeUpdate() != 1) {
                        System.out.println("Error deleting LocalityDetailID: " + id);
                    } else {
                        System.out.println("Deleted LocalityDetailID: " + id);
                    }
                }

                System.out.println("Fixing GeocoordDetail for: " + id);
                sql = String.format("SELECT GeocoordDetailID FROM geocoorddetail WHERE LocalityID = %d", id);
                ids = BasicSQLUtils.queryForInts(conn, sql);
                for (Integer ldId : ids) {
                    delStmt3.setInt(1, ldId);
                    if (delStmt3.executeUpdate() != 1) {
                        System.out.println("Error deleting GeocoordDetailID: " + id);
                    } else {
                        System.out.println("Deleted GeocoordDetailID: " + id);
                    }
                }

                sql = "SELECT COUNT(*) FROM collectingevent WHERE LocalityID = " + id;
                System.out.println(sql);
                int ceCnt = BasicSQLUtils.getCountAsInt(sql);

                if (ceCnt == 0) {
                    delStmt.setInt(1, id);
                    if (delStmt.executeUpdate() != 1) {
                        System.out.println("Error deleting LocalityID: " + id);
                    } else {
                        System.out.println("Deleted LocalityID: " + id);
                    }
                } else {
                    System.out.println("Can't Delete LocalityID: " + id);
                }
            }
            rs2.close();
        }

        stmt.close();
        pStmt.close();

        System.out.println("Fixed CE Ids: " + fixedCnt);

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

From source file:edu.ku.brc.specify.dbsupport.cleanuptools.LocalityCleanup.java

public static void fixOld() {
    String connectStr = "jdbc:mysql://localhost/";

    String dbName = "kevin";

    DBConnection dbc = new DBConnection("root", "root", connectStr + dbName, "com.mysql.jdbc.Driver",
            "org.hibernate.dialect.MySQLDialect", dbName);
    Connection conn = dbc.createConnection();
    BasicSQLUtils.setDBConnection(conn);

    try {/*from w  ww  .j  a v a2  s .co  m*/
        String sql = "SELECT LocalityName, cnt FROM (SELECT LocalityName, COUNT(LocalityName) as cnt FROM locality GROUP BY LocalityName) T1 WHERE cnt > 1 ORDER BY cnt desc ";

        Statement stmt = conn.createStatement();
        Statement stmt2 = conn.createStatement();
        PreparedStatement pStmt = conn
                .prepareStatement("UPDATE collectingevent SET LocalityID=? WHERE CollectingEventID = ?");

        int fixedCnt = 0;
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String locName = rs.getString(1);
            int cnt = rs.getInt(2);

            sql = String.format(
                    "SELECT LocalityID FROM locality WHERE LocalityName = '%s' ORDER BY LocalityID ASC",
                    locName);
            System.out.println(
                    "------------------------------------" + locName + "-----------------------------------");

            int c = 0;
            Integer firstID = null;

            ResultSet rs2 = stmt2.executeQuery(sql);
            while (rs2.next()) {
                int id = rs2.getInt(1);
                if (c == 0) {
                    firstID = id;
                    c = 1;
                    continue;
                }

                System.out.println("Fixing LocalityID: " + id);
                sql = String.format("SELECT CollectingEventId FROM collectingevent WHERE LocalityID = %d", id);
                Vector<Integer> ids = BasicSQLUtils.queryForInts(conn, sql);
                for (Integer ceId : ids) {
                    pStmt.setInt(1, firstID);
                    pStmt.setInt(2, ceId);
                    if (pStmt.executeUpdate() != 1) {
                        System.out.println("Error updating CE Id: " + ceId);
                    } else {
                        System.out
                                .println("Fixed CollectingEventID: " + ceId + "  with LocalityID: " + firstID);
                        fixedCnt++;
                    }
                }
                c++;
            }
            rs2.close();

            if (c != cnt) {
                System.out.println("Error updating all Localities for " + locName);
            }
        }
        rs.close();

        stmt.close();
        stmt2.close();
        pStmt.close();

        System.out.println("Fixed CE Ids: " + fixedCnt);

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

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

/**
 * Returns the number of fileItems that match the module and itemid
 *
 * @param db           Description of the Parameter
 * @param linkModuleId Description of the Parameter
 * @param linkItemId   Description of the Parameter
 * @return Description of the Return Value
 * @throws SQLException Description of the Exception
 *//*from  w ww.j a v a2  s. co  m*/
public static int retrieveRecordCount(Connection db, int linkModuleId, int linkItemId) throws SQLException {
    int count = 0;
    PreparedStatement pst = db.prepareStatement("SELECT COUNT(*) as filecount " + "FROM project_files pf "
            + "WHERE pf.link_module_id = ? and pf.link_item_id = ? ");
    pst.setInt(1, linkModuleId);
    pst.setInt(2, linkItemId);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        count = rs.getInt("filecount");
    }
    rs.close();
    pst.close();
    return count;
}

From source file:ca.qc.adinfo.rouge.achievement.db.AchievementDb.java

public static HashMap<String, Achievement> getAchievements(DBManager dbManager, long userId) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    HashMap<String, Achievement> returnValue = new HashMap<String, Achievement>();

    String sql = "SELECT ach.`key` as `key`, ach.`name` as name, " + "ach.point_value as point_value, "
            + "prg.progress as progress, ach.total as total "
            + "FROM rouge_achievement_progress as prg, rouge_achievements as ach "
            + "WHERE ach.key = prg.achievement_key and prg.user_id = ?; ";

    try {// w  ww .j  av a 2 s.  c o m
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);
        stmt.setLong(1, userId);

        rs = stmt.executeQuery();

        while (rs.next()) {

            String key = rs.getString("key");

            Achievement achievement = new Achievement(key, rs.getString("name"), rs.getInt("point_value"),
                    rs.getDouble("total"), rs.getDouble("progress"));

            returnValue.put(key, achievement);
        }

        return returnValue;

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return null;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

From source file:com.silverpeas.gallery.dao.MediaDAO.java

/**
 * Centralization of internal media decoration.
 * @param rs/*from  w w w .  ja  va 2s.c  o m*/
 * @param iMedia
 */
private static void decorateInternalMedia(ResultSet rs, InternalMedia iMedia) throws SQLException {
    iMedia.setFileName(rs.getString(2));
    iMedia.setFileSize(rs.getLong(3));
    iMedia.setFileMimeType(MediaMimeType.fromMimeType(rs.getString(4)));
    iMedia.setDownloadAuthorized(rs.getInt(5) == 1);
    iMedia.setDownloadPeriod(getPeriod(rs, 6, 7));
}

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

/**
 * Get Operators' Properties./* w  w  w.j ava  2s .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.stratelia.webactiv.util.DBUtil.java

public static int getMaxFromTable(Connection con, String tableName, String idName) {
    if (!StringUtil.isDefined(tableName) || !StringUtil.isDefined(idName)) {
        return 1;
    }// ww w.j a  v  a2  s .  co  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);
    }
}