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.tethrnet.manage.db.SystemDB.java

/**
 * returns all systems//w w w.jav  a2  s.  c  o  m
 *
 * @return system list
 */
public static List<HostSystem> getAllSystems() {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();

    Connection con = null;

    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        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);

    return hostSystemList;

}

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

/**
 * returns all systems//w  ww.  j  a va2s . c  o  m
 *
 * @return system list
 */
public static List<HostSystem> getAllSystems() {

    List<HostSystem> hostSystemList = new ArrayList<>();

    Connection con = null;

    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        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);

    return hostSystemList;

}

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

/**
 * method to do order by based on the sorted set object for systems
 *
 * @param sortedSet sorted set object//from w ww .  j  av a 2 s  . co m
 * @return sortedSet with list of host systems
 */
public static SortedSet getSystemSet(SortedSet sortedSet) {
    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 s ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
            ? ",system_map m where s.id=m.system_id and m.profile_id=?"
            : "";
    sql += orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(1, 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.clavain.alerts.Methods.java

public static void sendNotifications(ReturnServiceCheck sc) {
    Integer cid = sc.getCid();//from   w  w w. j av a 2  s .  c  o m
    try {
        Connection conn = connectToDatabase(com.clavain.muninmxcd.p);
        java.sql.Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery(
                "SELECT notifications.id as nid, contacts.* FROM `notifications` LEFT JOIN contacts ON notifications.contact_id = contacts.id WHERE check_id = "
                        + cid);
        while (rs.next()) {
            Integer contact_id = rs.getInt("id");
            String dayField = getScheduleFieldToCheck();
            logger.info("[Check Notifications " + cid + "] Found " + rs.getString("contact_name"));
            if (rs.getString(dayField).equals("disabled")) {
                logger.info("[Check Notifications " + cid + "] " + 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(sc.getDownTimeConfirmedAt(),
                            rs.getString("timezone"));
                    String title = "ALERT: " + sc.getCheckname() + " (" + sc.getChecktype() + ")";
                    String message = "Service Downtime verified @ " + failTime + ".   Details: "
                            + sc.getOutput().get(0);

                    String json = "";
                    if (rs.getInt("callback_active") == 1) {
                        logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                                + " Sending Callback");
                        sendCallback(sc, rs.getString("contact_callback"));
                        updateCheckNotificationLog(cid, contact_id,
                                "Callback executed to " + rs.getString("contact_callback"), "callback");
                    }
                    if (rs.getInt("tts_active") == 1) {
                        title = "This is a PingReports Alert: The Servicecheck: " + sc.getCheckname()
                                + " with type: " + sc.getChecktype() + " is in alert state.";
                        logger.info("[Notifications " + cid + "] " + rs.getString("contact_name")
                                + " Initiating TTS Call");
                        sendTTS(title, message, rs.getString("contact_mobile_nr"), rs.getInt("user_id"));
                        updateCheckNotificationLog(cid, contact_id,
                                "Text2Speech Call initiated to " + rs.getString("contact_mobile_nr"), "tts");
                    }
                    if (rs.getInt("email_active") == 1) {
                        logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                                + " Sending E-Mail");
                        String ENDL = System.getProperty("line.separator");
                        message = "Service Downtime verified @ " + failTime + "." + ENDL + ENDL + "Details:"
                                + ENDL + ENDL + sc.getOutput().get(0);
                        sendMail(title, message, rs.getString("contact_email"));
                        updateCheckNotificationLog(cid, contact_id,
                                "E-Mail send to " + rs.getString("contact_email"), "email");
                    }
                    if (rs.getInt("sms_active") == 1) {
                        title = sc.getCheckname() + "(" + sc.getChecktype() + ")";
                        message = sc.getOutput().get(0);
                        logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                                + " Sending SMS");
                        sendSMS(title, message, rs.getString("contact_mobile_nr"), rs.getInt("user_id"));
                        updateCheckNotificationLog(cid, contact_id,
                                "SMS send to " + rs.getString("contact_mobile_nr"), "sms");
                    }
                    if (rs.getInt("pushover_active") == 1) {
                        logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                                + " Sending Pushover Notification");
                        sendPushover(title, message, rs.getString("pushover_key"));
                        updateCheckNotificationLog(cid, contact_id,
                                "PushOver Message send to " + rs.getString("pushover_key"), "pushover");
                    }

                } else {
                    logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                            + " disabled notifications for this timerange - skipping contact");
                }
            }
        }

    } catch (Exception ex) {
        logger.error("Error in sendNotifications for CID " + cid + " : " + ex.getLocalizedMessage());
        ex.printStackTrace();
    }
}

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

/**
 * method to do order by based on the sorted set object for systems
 *
 * @param sortedSet sorted set object/*from   w w w  .j a v  a  2  s  .c o m*/
 * @return sortedSet with list of host systems
 */
public static SortedSet getSystemSet(SortedSet sortedSet) {
    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 s ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
            ? ",system_map m where s.id=m.system_id and m.profile_id=?"
            : "";
    sql += orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(1, 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.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   www .  j  a  v a 2s  .  co  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// w  w  w  . java2s. com
 * @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.chaosinmotion.securechat.server.commands.CreateAccount.java

/**
 * Process the create account request. This should receive the following
 * objects: the username, the password, the device ID and the public key
 * for the device. This adds a new entry in the account database, and
 * creates a new device.//  w  ww .j a v  a 2 s .  co m
 * 
 * If the user account cannot be created, this returns nil.
 * @param requestParams
 * @return
 */
public static UserInfo processRequest(JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    String username = requestParams.optString("username");
    String password = requestParams.optString("password");
    String deviceid = requestParams.optString("deviceid");
    String pubkey = requestParams.optString("pubkey");

    /*
     * Attempt to insert a new user into the database
     */

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = Database.get();
        ps = c.prepareStatement("INSERT INTO Users " + "    ( username, password ) " + "VALUES "
                + "    ( ?, ? ); SELECT currval('Users_userid_seq')");
        ps.setString(1, username);
        ps.setString(2, password);

        try {
            ps.execute();
        } catch (SQLException ex) {
            return null; // Can't insert; duplicate username?
        }
        int utc = ps.getUpdateCount();
        int userid = 0;
        if ((utc == 1) && ps.getMoreResults()) {
            rs = ps.getResultSet();
            if (rs.next()) {
                userid = rs.getInt(1);
            }
            rs.close();
            rs = null;
        }

        ps.close();
        ps = null;

        /*
         * We now have the user index. Insert the device. Note that it is
         * highly unlikely we will have a UUID collision, but we verify
         * we don't by deleting any rows in the device table with the
         * specified UUID. The worse case scenario is a collision which
         * knocks someone else off the air. (The alternative would be
         * to accidentally send the wrong person duplicate messages.)
         * 
         * Note that we don't actually use a device-identifying identifer,
         * choosing instead to pick a UUID, so we need to deal with
         * the possibility (however remote) of duplicate UUIDs.
         * 
         * In the off chance we did have a collision, we also delete all
         * old messages to the device; that prevents messages from being
         * accidentally delivered.
         */

        ps = c.prepareStatement("DELETE FROM Messages " + "WHERE messageid IN "
                + "    (SELECT Messages.messageid " + "     FROM Messages, Devices "
                + "     WHERE Messages.deviceid = Devices.deviceid " + "     AND Devices.deviceuuid = ?)");
        ps.setString(1, deviceid);
        ps.execute();
        ps.close();
        ps = null;

        ps = c.prepareStatement("DELETE FROM Devices WHERE deviceuuid = ?");
        ps.setString(1, deviceid);
        ps.execute();
        ps.close();
        ps = null;

        ps = c.prepareStatement("INSERT INTO Devices " + "    ( userid, deviceuuid, publickey ) " + "VALUES "
                + "    ( ?, ?, ?)");
        ps.setInt(1, userid);
        ps.setString(2, deviceid);
        ps.setString(3, pubkey);
        ps.execute();

        /*
         * Complete; return the user info record
         */

        return new Login.UserInfo(userid);
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:jp.co.golorp.emarf.sql.MetaData.java

/**
 * @param rs/*from ww w  . ja va  2 s  .c o  m*/
 *            rs
 * @param columnInfoName
 *            columnLabel
 * @return String
 */
private static Integer getInt(final ResultSet rs, final String columnInfoName) {
    try {
        LOG.trace(columnInfoName + ":" + rs.getString(columnInfoName));
        return rs.getInt(columnInfoName);
    } catch (SQLException e) {
        NOT_EXIST_COLUMN_INFO_NAMES.add(columnInfoName);
    }
    return null;
}

From source file:com.ywang.alone.handler.task.AuthTask.java

/**
 * //  w  w w  . j  a v a2s.c  o m
 * 
 * @param msg
 *            {
 *            'phoneNum':'ywang','password':'e10adc3949ba59abbe56e057f20f883
 *            e ' , 'deviceToken':'8a2597aa1d37d432a88a446d82b6561e',
 *            'lng':'117.157954','lat':'31.873432','osVersion':'8.0',
 *            'systemType':'iOS','phoneModel':'iPhone 5s','key':''}
 * 
 * @return
 */
private static JSONObject regNewUser(String msg) {
    JSONObject jsonObject = AloneUtil.newRetJsonObject();
    JSONObject user = JSON.parseObject(msg);
    DruidPooledConnection conn = null;
    PreparedStatement updatestmt = null;
    try {
        conn = DataSourceFactory.getInstance().getConn();
        conn.setAutoCommit(false);
        String uuid = UUID.randomUUID().toString();
        uuid = uuid.replaceAll("-", "");
        String token = MD5.getMD5String(uuid);

        String im_user = user.getString("phoneNum").trim();
        UserInfo userInfo = new UserInfo();
        userInfo.setRegTime(System.currentTimeMillis());
        userInfo.setOnline("1");
        userInfo.setKey(token);
        userInfo.setMessageUser(im_user);
        userInfo.setMessagePwd("alone123456");

        updatestmt = conn.prepareStatement(
                "insert into userbase (PHONE_NUM, PWD, REG_TIME, LNG, LAT, DEVICE_TOKEN, SYSTEM_TYPE, OS_VERSION,PHONE_MODEL, PKEY, MESSAGE_USER, MESSAGE_PWD) VALUES (?,?,?, ?,?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        updatestmt.setString(1, user.getString("phoneNum").trim());
        updatestmt.setString(2, user.getString("password").trim());
        updatestmt.setLong(3, userInfo.getRegTime());
        updatestmt.setString(4, user.getString("lng").trim());
        updatestmt.setString(5, user.getString("lat").trim());

        updatestmt.setString(6, user.getString("deviceToken").trim());
        updatestmt.setString(7, user.getString("systemType").trim());
        updatestmt.setString(8, user.getString("osVersion").trim());
        updatestmt.setString(9, user.getString("phoneModel").trim());
        updatestmt.setString(10, userInfo.getKey());
        updatestmt.setString(11, userInfo.getMessageUser());
        updatestmt.setString(12, "alone123456");

        int result = updatestmt.executeUpdate();

        if (result == 1) {
            ResultSet idRS = updatestmt.getGeneratedKeys();
            if (idRS.next()) {
                int userId = idRS.getInt(1);
                userInfo.setUserId(userId + "");
            }
            jsonObject.put("ret", Constant.RET.REG_SUCC);
            jsonObject.put("data", userInfo);
        } else {
            jsonObject.put("ret", Constant.RET.SYS_ERR);
            jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
            jsonObject.put("errDesc", Constant.ErrorCode.SYS_ERR);
            LoggerUtil.logServerErr("insert into userbase no result");
        }

        conn.commit();
        conn.setAutoCommit(true);
    } catch (SQLException e) {
        LoggerUtil.logServerErr(e);
        jsonObject.put("ret", Constant.RET.SYS_ERR);
        jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
        jsonObject.put("errDesc", Constant.ErrorCode.SYS_ERR);
    } finally {
        try {
            if (null != updatestmt) {
                updatestmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        } catch (SQLException e) {
            LoggerUtil.logServerErr(e.getMessage());
        }
    }

    return jsonObject;
}