Example usage for java.sql Statement 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.aerothai.database.user.UserService.java

/**
 * Method to check whether uname and pwd combination are correct
 * //from  w w  w. j  a  v a 2 s .c  o  m
 * @param uname
 * @param pwd
 * @return
 * @throws Exception
 */
public static String checkLogin(String uname, String pwd) throws Exception {
    boolean isUserAvailable = false;
    Connection dbConn = null;
    JSONObject obj = new JSONObject();
    JSONObject jsonData = new JSONObject();
    try {
        dbConn = DBConnection.createConnection();

        Statement stmt = dbConn.createStatement();
        String query = "SELECT * FROM user WHERE username = '" + uname + "' AND pwd=" + "'" + pwd + "'";
        System.out.println(query);
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            //System.out.println(rs.getString(1) + rs.getString(2) + rs.getString(3));
            isUserAvailable = true;
            //JSONObject jsonData = new JSONObject();
            jsonData.put("iduser", rs.getInt("iduser"));
            jsonData.put("name", rs.getString("name"));
            jsonData.put("lastname", rs.getString("lastname"));
            jsonData.put("idposition", rs.getInt("idposition"));
            jsonData.put("iddept", rs.getInt("iddept"));
            jsonData.put("idunit", rs.getInt("idunit"));
            jsonData.put("idrole", rs.getInt("idrole"));
            jsonData.put("address", rs.getString("address"));
            jsonData.put("email", rs.getString("email"));
            jsonData.put("tel", rs.getString("tel"));
            jsonData.put("actunit", rs.getString("actunit"));
            jsonData.put("username", rs.getString("username"));
            jsonData.put("photo", rs.getString("photo"));
            jsonData.put("actcust", rs.getString("actcust"));
            //objList.add(rs.getString("pwd"));

        }
        if (isUserAvailable) {

            obj.put("tag", "login");
            obj.put("msg", "done");
            obj.put("status", true);
            obj.put("data", jsonData.toJSONString());
        } else {
            obj.put("tag", "login");
            obj.put("msg", "Incorrect Email or Password");
            obj.put("status", false);
        }
    } catch (SQLException sqle) {
        throw sqle;
    } catch (Exception e) {
        // TODO Auto-generated catch block
        if (dbConn != null) {
            dbConn.close();
        }
        throw e;
    } finally {
        if (dbConn != null) {
            dbConn.close();
        }
    }
    return obj.toJSONString();
}

---

From source file:controler.DbModules.java

public static String findName(String EmailAddress) {
    String usql = "SELECT name FROM customer WHERE email_adress=\"" + EmailAddress + "\"";
    Connection conn = DbModules.getConnection();
    String name = null;/*  w  w  w .j  ava2 s  .  com*/
    try {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(usql);
        if (rs.next()) {
            name = rs.getString("name");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return name;
}

---

From source file:controler.DbModules.java

public static int findCustomerId(String EmailAddress) {
    String usql = "SELECT cust_id FROM customer WHERE email_adress=\"" + EmailAddress + "\"";
    Connection conn = DbModules.getConnection();
    int id = 0;//from w w  w. jav a  2 s  .com
    try {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(usql);
        if (rs.next()) {
            id = rs.getInt("cust_id");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return id;
}

---

From source file:com.ubipass.middleware.ems.EMSUtil.java

/**
 * Get last time of system shutdown.//from  www .  jav a2 s  . c o  m
 * 
 * @return time stamp of last system shutdown
 */
private static long getLastShutdownTime() {

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    long result = 0;

    try {
        conn = DBConnPool.getConnection();

        // get last shutdown time from table sysconfig
        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT currentTime FROM sysconfig");
        rs.next();
        result = rs.getLong(1);
    } catch (Exception e) {
        SystemLogger.error("[EMS] getLastShutdownTime() error: " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }

            if (stmt != null) {
                stmt.close();
            }

            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {

        }
    }

    return (result == 0) ? new Date().getTime() : result;

}

---

From source file:controler.DbModules.java

public static Item validateLogin(Item user) {
    System.out.println("db modules validateLogin= email" + user.getEmail() + " pass" + user.getPassword() + "");
    String usql = "SELECT  * FROM customer WHERE email_adress = \"" + user.getEmail() + "\" and  password = \""
            + user.getPassword() + "\"";
    try {/*from   w  w  w. ja  v  a 2 s . co  m*/
        Connection conn;
        conn = DbModules.getConnection();
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(usql);
        boolean test = rs.next();
        if (!test) {
            System.out.println("Failure login");
        } else if (test) {
            System.out.println("ssuucceessffuull login");
            user.setEmail(rs.getString("email_adress"));
            user.setPassword(rs.getString("password"));
            //call the findname method from within the class and setting  the return value to name 
            user.setName(DbModules.findName(rs.getString("email_adress")));//
            user.setIsValid(true);
        }
    } catch (SQLException e) {
        System.out.println("an exception occured" + e + "");
    }
    return user;
}

---

From source file:com.baidu.qa.service.test.util.JdbcUtil.java

protected static void excuteVerifyDeleteSqls(List<String> sqlStrs, String dbname) throws Exception {

    Connection con = null;//  w w  w .  j  a v  a 2 s . c  o  m
    Statement sm = null;
    ResultSet rs = null;
    try {
        //?
        con = MysqlDatabaseManager.getCon(dbname);
        Assert.assertNotNull("connect to db error:" + dbname, con);

        //??
        sm = con.createStatement();
        for (String sqlStr : sqlStrs) {
            rs = sm.executeQuery(sqlStr);
            log.info("[sql:]" + sqlStr);

            Assert.assertFalse("[db expect error],has data like:" + sqlStr, rs.next());
            rs.close();
        }

    } catch (Exception e) {

        throw e;
    } finally {
        if (con != null) {
            con.close();
        }
        if (sm != null) {
            sm.close();
        }
        if (rs != null) {
            rs.close();
        }
    }

}

---

From source file:com.baidu.qa.service.test.util.JdbcUtil.java

protected static void excuteVerifySqls(List<String> sqlStrs, String dbname) throws Exception {

    //   ???//from  ww  w  . java  2s.  c o  m
    Connection con = null;
    Statement sm = null;
    ResultSet rs = null;
    try {
        //?
        con = MysqlDatabaseManager.getCon(dbname);
        Assert.assertNotNull("connect to db error:" + dbname, con);

        //??
        sm = con.createStatement();
        for (String sqlStr : sqlStrs) {
            rs = sm.executeQuery(sqlStr);
            log.info("[sql:]" + sqlStr);

            Assert.assertTrue("[db expect error],has no data like:" + sqlStr, rs.next());
            rs.close();
        }

    } catch (Exception e) {
        throw e;
    } finally {
        if (con != null) {
            con.close();
        }
        if (sm != null) {
            sm.close();
        }
        if (rs != null) {
            rs.close();
        }

    }

}

---

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

public static void sendNotifications(ReturnServiceCheck sc) {
    Integer cid = sc.getCid();//from   w ww . jav a2s  . com
    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:Data.java

/**
 * Creates a dataset, consisting of two series of monthly data.
 *
 * @return The dataset.// w w  w . j a  v  a 2 s.c  om
 * @throws ClassNotFoundException 
 */
private static XYDataset createDataset(Statement stmt) throws ClassNotFoundException {

    TimeSeries s1 = new TimeSeries("Humidit");
    TimeSeries s2 = new TimeSeries("Temprature");
    ResultSet rs = null;

    try {
        String sqlRequest = "SELECT * FROM `t_temphum`";
        rs = stmt.executeQuery(sqlRequest);
        Double hum;
        Double temp;
        Timestamp date;

        while (rs.next()) {
            hum = rs.getDouble("tmp_humidity");
            temp = rs.getDouble("tmp_temperature");
            date = rs.getTimestamp("tmp_date");

            if (tempUnit == "F") {
                temp = celsiusToFahrenheit(temp.toString());
            }

            if (date != null) {
                s1.add(new Second(date), hum);
                s2.add(new Second(date), temp);
            } else {
                JOptionPane.showMessageDialog(panelPrincipal, "Il manque une date dans la dase de donne",
                        "Date null", JOptionPane.WARNING_MESSAGE);
            }
        }

        rs.close();
    } catch (SQLException e) {
        String exception = e.toString();

        if (e.getErrorCode() == 0) {
            JOptionPane.showMessageDialog(panelPrincipal,
                    "Le serveur met trop de temps  rpondre ! Veuillez rssayer plus tard ou contacter un administrateur",
                    "Connection timed out", JOptionPane.ERROR_MESSAGE);
        } else {
            JOptionPane.showMessageDialog(panelPrincipal, "Voici l'exception : " + exception,
                    "Titre : exception", JOptionPane.ERROR_MESSAGE);
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (Exception e) {
        String exception = e.toString();
        JOptionPane.showMessageDialog(panelPrincipal, "Voici l'exception : " + exception, "Titre : exception",
                JOptionPane.ERROR_MESSAGE);
        e.printStackTrace();
    }

    // ******************************************************************
    //  More than 150 demo applications are included with the JFreeChart
    //  Developer Guide...for more information, see:
    //
    //  >   http://www.object-refinery.com/jfreechart/guide.html
    //
    // ******************************************************************

    TimeSeriesCollection dataset = new TimeSeriesCollection();
    dataset.addSeries(s1);
    dataset.addSeries(s2);

    return dataset;

}

---

From source file:com.vertica.hadoop.VerticaOutputFormat.java

/**
  * Optionally called at the end of a job to optimize any newly created and
  * loaded tables. Useful for new tables with more than 100k records.
  * /*from   w  w w.ja v  a 2  s.  c  om*/
  * @param conf
  * @throws Exception
  */
public static void optimize(Configuration conf) throws Exception {
    VerticaConfiguration vtconfig = new VerticaConfiguration(conf);
    Connection conn = vtconfig.getConnection(true);

    // TODO: consider more tables and skip tables with non-temp projections 
    Relation vTable = new Relation(vtconfig.getOutputTableName());
    Statement stmt = conn.createStatement();
    ResultSet rs = null;
    HashSet<String> tablesWithTemp = new HashSet<String>();

    //for now just add the single output table
    tablesWithTemp.add(vTable.getQualifiedName().toString());

    // map from table name to set of projection names
    HashMap<String, Collection<String>> tableProj = new HashMap<String, Collection<String>>();
    rs = stmt.executeQuery("select projection_schema, anchor_table_name, projection_name from projections;");
    while (rs.next()) {
        String ptable = rs.getString(1) + "." + rs.getString(2);
        if (!tableProj.containsKey(ptable)) {
            tableProj.put(ptable, new HashSet<String>());
        }

        tableProj.get(ptable).add(rs.getString(3));
    }

    for (String table : tablesWithTemp) {
        if (!tableProj.containsKey(table)) {
            throw new RuntimeException("Cannot optimize table with no data: " + table);
        }
    }

    String designName = (new Integer(conn.hashCode())).toString();
    stmt.execute("select dbd_create_workspace('" + designName + "')");
    stmt.execute("select dbd_create_design('" + designName + "', '" + designName + "')");
    stmt.execute("select dbd_add_design_tables('" + designName + "', '" + vTable.getQualifiedName().toString()
            + "')");
    stmt.execute("select dbd_populate_design('" + designName + "', '" + designName + "')");

    //Execute
    stmt.execute("select dbd_create_deployment('" + designName + "', '" + designName + "')");
    stmt.execute("select dbd_add_deployment_design('" + designName + "', '" + designName + "', '" + designName
            + "')");
    stmt.execute("select dbd_add_deployment_drop('" + designName + "', '" + designName + "')");
    stmt.execute("select dbd_execute_deployment('" + designName + "', '" + designName + "')");

    //Cleanup
    stmt.execute("select dbd_drop_deployment('" + designName + "', '" + designName + "')");
    stmt.execute("select dbd_remove_design('" + designName + "', '" + designName + "')");
    stmt.execute("select dbd_drop_design('" + designName + "', '" + designName + "')");
    stmt.execute("select dbd_drop_workspace('" + designName + "')");
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »