Example usage for java.sql Statement executeQuery

List of usage examples for java.sql Statement executeQuery

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

Introduction

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

Prototype

ResultSet executeQuery(String sql) throws SQLException;

Source Link

Document

Executes the given SQL statement, which returns a single ResultSet object.

Usage

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

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

    try {//from  www  .  j ava2s .  co  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:database.HashTablesTools.java

public static int countFilesInDB(String tableName, String tableFailureName) {

    Connection connection = HashTablesTools.getConnection(tableName, tableFailureName);

    int countOfFiles = 0;

    Statement stmt = null;
    ResultSet resultFindEntry = null;
    String hash = null;//from  ww w. jav a 2 s.c  om

    try {
        stmt = connection.createStatement();
        String findEntry = "SELECT * from " + tableFailureName;
        resultFindEntry = stmt.executeQuery(findEntry);

        while (resultFindEntry.next()) {
            countOfFiles += 1;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

    Set uniqueHash = new HashSet();
    try {
        stmt = connection.createStatement();
        String findEntry = "SELECT * from " + tableName;
        resultFindEntry = stmt.executeQuery(findEntry);

        while (resultFindEntry.next()) {
            hash = resultFindEntry.getString(1);
            uniqueHash.add(hash);
        }
        countOfFiles += uniqueHash.size();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    HashTablesTools.shutdown();
    return countOfFiles;
}

From source file:database.HashTablesTools.java

public static String returnSequenceInDbifFourLetterCodeAndChainfoundInDatabase(Connection connection,
        String fourLetterCode, String chainName, String sequenceTableName) {

    String sequenceInDb = null;/*from   ww w .  j a v a  2  s. c o  m*/
    try {
        Statement stmt = connection.createStatement();
        String findEntry = "SELECT * from " + sequenceTableName + " WHERE fourLettercode = '" + fourLetterCode
                + "' and chainId = '" + chainName + "'";
        ResultSet resultFindEntry = stmt.executeQuery(findEntry);
        int foundEntriesCount = 0;
        String fourLetterCodeFromDB;
        String chainIdFromDB;
        if (resultFindEntry.next()) {
            foundEntriesCount += 1;

            fourLetterCodeFromDB = resultFindEntry.getString(1);
            chainIdFromDB = resultFindEntry.getString(2);
            sequenceInDb = resultFindEntry.getString(4);
        }

        if (foundEntriesCount != 1) {
            System.out.println("problem isFourLetterCodeAndChainfoundInDatabase " + fourLetterCode + "  "
                    + chainName + "  " + foundEntriesCount);
            return null;
        }
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
        return null;
    }
    return sequenceInDb;
}

From source file:com.mirth.connect.server.controllers.tests.TestUtils.java

public static List<?> selectColumn(String query) {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;// w  ww  . ja va 2  s.com

    try {
        connection = getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(query);
        List<Object> col = new ArrayList<Object>();

        while (resultSet.next()) {
            col.add(resultSet.getObject(1));
        }

        return col;
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        DbUtils.closeQuietly(resultSet);
        DbUtils.closeQuietly(statement);
        DbUtils.closeQuietly(connection);
    }
}

From source file:ems.util.DataHandler.java

public static String getBoothName(String boothNumber) {
    String sqlQuery = "select ifnull(booth_no,'') || ' - ' || ifnull(booth_name,'') "
            + "from booth_master where booth_no=" + boothNumber;
    Connection con = getConnection();
    Statement s = null;
    ResultSet rs = null;//from  w w  w. ja v  a 2  s  .  com
    try {
        s = con.createStatement();
        rs = s.executeQuery(sqlQuery);
        while (rs.next()) {
            return rs.getString(1);
        }
    } catch (Exception e) {
        log.error("getBoothList: " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (s != null) {
                s.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            log.error("getBoothList: " + ex.getMessage());
        }
    }
    return null;
}

From source file:ems.util.DataHandler.java

public static List<MyModel> getBoothList() {
    List<MyModel> myModels = new LinkedList<>();
    String sqlQuery = "select distinct booth_no, booth_id from booth_master order by 1";
    Connection con = getConnection();
    Statement s = null;
    ResultSet rs = null;//from w ww  . ja  v a 2 s .  c  o  m
    try {
        s = con.createStatement();
        rs = s.executeQuery(sqlQuery);
        myModels.add(new MyModel("0", "Choose Booth No."));
        while (rs.next()) {
            myModels.add(new MyModel(rs.getString(1), rs.getString(2)));
        }
    } catch (Exception e) {
        log.error("getBoothList: " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (s != null) {
                s.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            log.error("getBoothList: " + ex.getMessage());
        }
    }
    return myModels;
}

From source file:ems.util.DataHandler.java

public static List<MyModel> getWardList() {
    List<MyModel> myModels = new LinkedList<>();
    String sqlQuery = "select distinct ward_no from bmc_history order by 1";
    Connection con = getConnection();
    Statement s = null;
    ResultSet rs = null;// w w w  .j a  v a2 s.c  o  m
    try {
        s = con.createStatement();
        rs = s.executeQuery(sqlQuery);
        myModels.add(new MyModel("0", "Choose Ward No."));
        while (rs.next()) {
            myModels.add(new MyModel(rs.getString(1), rs.getString(1)));
        }
    } catch (Exception e) {
        log.error("getWardList: " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (s != null) {
                s.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            log.error("getWardList: " + ex.getMessage());
        }
    }
    return myModels;
}

From source file:ems.util.DataHandler.java

public static List<MyModel> getCommunityList() {
    List<MyModel> myModels = new LinkedList<>();
    String sqlQuery = "select distinct cast_nm from e_details where cast_nm is not null and cast_nm <>'' order by 1";
    Connection con = getConnection();
    Statement s = null;
    ResultSet rs = null;/*from   w  ww . j a  v a 2  s  .  co  m*/
    try {
        s = con.createStatement();
        rs = s.executeQuery(sqlQuery);
        myModels.add(new MyModel("0", "Choose Community"));
        while (rs.next()) {
            myModels.add(new MyModel(rs.getString(1), rs.getString(1)));
        }
    } catch (Exception e) {
        log.error("getWardList: " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (s != null) {
                s.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            log.error("getWardList: " + ex.getMessage());
        }
    }
    return myModels;
}

From source file:com.mirth.connect.server.controllers.tests.TestUtils.java

public static void fixMessageIdSequence(String channelId) throws Exception {
    Connection connection = null;
    long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId);
    String database = (String) Donkey.getInstance().getConfiguration().getDonkeyProperties().get("database");
    Long maxId = null;//  w w  w.  j  a  v  a2s  . c  om

    if (database.equals("derby") || database.equals("mysql") || database.equals("sqlserver")) {
        Statement statement = null;
        ResultSet result = null;

        try {
            connection = getConnection();
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT MAX(id) FROM d_m" + localChannelId);
            result.next();
            maxId = result.getLong(1) + 1;
            close(result);
            statement.execute("DELETE FROM d_message_sequences WHERE local_channel_id = " + localChannelId);
            statement.execute(
                    "INSERT INTO d_message_sequences (local_channel_id) VALUES (" + localChannelId + ")");
            connection.commit();
        } finally {
            close(result);
            close(statement);
            close(connection);
        }
    }

    logger.debug("Message ID sequence updated to: " + maxId);
}

From source file:ems.util.DataHandler.java

public static List<MyModel> getDashboardData(String reportType) {
    List<MyModel> myModels = new LinkedList<>();
    String sqlQuery = "";
    switch (reportType) {
    case "1":
        sqlQuery = Q_S_DASHBOARD_CAST_WISE;
        break;//from w  w  w  .java2  s  .c o  m
    case "2":
        sqlQuery = Q_S_DASHBOARD_GENDER_WISE;
        break;
    case "3":
        sqlQuery = Q_S_DASHBOARD_COLOR_WISE;
        break;
    }
    Connection con = getConnection();
    Statement s = null;
    ResultSet rs = null;
    try {
        s = con.createStatement();
        rs = s.executeQuery(sqlQuery);
        while (rs.next()) {
            myModels.add(new MyModel(rs.getString(1), rs.getString(2)));
        }
    } catch (Exception e) {
        log.error("getDashboardData: " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (s != null) {
                s.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            log.error("getDashboardData: " + ex.getMessage());
        }
    }
    return myModels;
}