Example usage for java.sql PreparedStatement 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:dao.DirSearchQuery.java

/**
 * This method lists all the results for the search text from directories
 * @param conn the connection//from  w  w  w .j  a  v a  2 s .  c  om
 * @param collabrumId the collabrumid
 * @return HashSet the set that has the list of moderators for these collabrums.
 * @throws BaseDaoException - when error occurs
 **/
public HashSet run(Connection conn, String stext) throws BaseDaoException {

    if ((RegexStrUtil.isNull(stext) || conn == null)) {
        return null;
    }
    ResultSet rs = null;

    StringBuffer sb = new StringBuffer(
            "select distinct directoryid, dirname, LEFT(dirdesc, 160) as info, hits, creationdate from directory where ");

    ArrayList columns = new ArrayList();
    columns.add("dirdesc");
    columns.add("dirname");
    columns.add("keywords");
    sb.append(sqlSearch.getConstraint(columns, stext));
    sb.append(" order by hits DESC");
    logger.info("search = " + sb.toString());

    try {
        PreparedStatement stmt = conn.prepareStatement(sb.toString());
        rs = stmt.executeQuery();

        Vector columnNames = null;
        Directory directory = null;
        HashSet pendingSet = new HashSet();

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }

        while (rs.next()) {
            directory = (Directory) eop.newObject(DbConstants.DIRECTORY);
            for (int j = 0; j < columnNames.size(); j++) {
                if (((String) (columnNames.elementAt(j))).equalsIgnoreCase("creationdate")) {
                    try {
                        directory.setValue("creationdate",
                                GlobalConst.dncalendar.getDisplayDate(rs.getTimestamp("creationdate")));
                    } catch (ParseException e) {
                        throw new BaseDaoException(
                                "could not parse the date for creationdate in DirSearchQuery()"
                                        + rs.getTimestamp("creationdate"),
                                e);
                    }
                } else {
                    directory.setValue((String) columnNames.elementAt(j),
                            (String) rs.getString((String) columnNames.elementAt(j)));
                }
            }
            pendingSet.add(directory);
        }
        return pendingSet;
    } catch (Exception e) {
        throw new BaseDaoException("Error occured while executing search in directory run query ", e);
    }
}

---

From source file:dao.PblogSearchBizAwareQuery.java

/**
 * This method lists all the results for the search text from pblogs
 * @param conn the connection/* ww  w. j a  v a2  s  . c om*/
 * @param sString - search text
 * @param bid - bid
 * @return HashSet the set that has the list of search result
 * @throws BaseDaoException - when error occurs
 **/
public HashSet run(Connection conn, String sString, String bid) throws BaseDaoException {

    if (RegexStrUtil.isNull(sString) || RegexStrUtil.isNull(bid) || conn == null) {
        return null;
    }

    ResultSet rs = null;
    StringBuffer sb = new StringBuffer(
            "select hdlogin.loginid, hdlogin.login, hdlogin.fname, lname, LEFT(message, 160) as info, entrydate, tid, hits, hdlogin.bid, business.bsearch from business, hdlogin left join pblogtopics on hdlogin.loginid=pblogtopics.pblogid left join pblog on hdlogin.loginid=pblog.loginid where business.bid=hdlogin.bid and (");
    ArrayList columns = new ArrayList();
    columns.add("topic");
    columns.add("message");
    sb.append(sqlSearch.getConstraint(columns, sString));
    sb.append(") group by login order by hits DESC");

    logger.info("search query string" + sb.toString());

    try {
        PreparedStatement stmt = conn.prepareStatement(sb.toString());
        rs = stmt.executeQuery();

        Vector columnNames = null;
        Blog pblog = null;
        HashSet pendingSet = new HashSet();

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }

        while (rs.next()) {
            pblog = (Blog) eop.newObject(DbConstants.BLOG);
            for (int j = 0; j < columnNames.size(); j++) {
                if (((String) (columnNames.elementAt(j))).equalsIgnoreCase("entrydate")) {
                    try {
                        pblog.setValue("entrydate",
                                GlobalConst.dncalendar.getDisplayDate(rs.getTimestamp("entrydate")));
                    } catch (ParseException e) {
                        throw new BaseDaoException(
                                "could not parse the date for entrydate in PblogSearchBizAwareQuery()"
                                        + rs.getTimestamp("entrydate"),
                                e);
                    }
                } else {
                    pblog.setValue((String) columnNames.elementAt(j),
                            (String) rs.getString((String) columnNames.elementAt(j)));
                }
            }
            pendingSet.add(pblog);
        }
        return pendingSet;
    } catch (Exception e) {
        throw new BaseDaoException("Error occured while executing search in pblog run query " + sb.toString(),
                e);
    }
}

---

From source file:com.alibaba.druid.benckmark.pool.Oracle_Case0.java

private void p0(DataSource dataSource, String name) throws SQLException {
    long startMillis = System.currentTimeMillis();
    long startYGC = TestUtil.getYoungGC();
    long startFullGC = TestUtil.getFullGC();

    final int COUNT = 1000 * 1;
    for (int i = 0; i < COUNT; ++i) {
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn.prepareStatement("SELECT 1 FROM DUAL");
        ResultSet rs = stmt.executeQuery();
        rs.next();/*  ww  w . j a  v  a2s.co m*/
        rs.close();
        stmt.close();
        conn.close();
    }
    long millis = System.currentTimeMillis() - startMillis;
    long ygc = TestUtil.getYoungGC() - startYGC;
    long fullGC = TestUtil.getFullGC() - startFullGC;

    System.out.println(name + " millis : " + NumberFormat.getInstance().format(millis) + ", YGC " + ygc
            + " FGC " + fullGC);
}

---

From source file:com.jk.db.datasource.JKPoolingDataSource.java

/**
 * Test connection./*from  w  ww .j  a v a  2 s  .c o  m*/
 *
 * @param queryConnection
 *            the query connection
 * @throws SQLException
 *             the SQL exception
 */
private void testConnection(final Connection queryConnection) throws SQLException {
    PreparedStatement ps = null;
    try {
        ps = queryConnection.prepareStatement(getTestQuery());
        ps.executeQuery();
    } finally {
        close(ps);
    }
}

---

From source file:com.l2jfree.gameserver.datatables.SkillSpellbookTable.java

private SkillSpellbookTable() {
    _skillSpellbooks = new FastMap<Integer, Integer>().setShared(true);

    if (!Config.ALT_SP_BOOK_NEEDED)
        return;//from   w ww. ja  va  2s  .  co  m

    Connection con = null;

    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement("SELECT skill_id, item_id FROM skill_spellbooks");
        ResultSet spbooks = statement.executeQuery();

        while (spbooks.next())
            _skillSpellbooks.put(spbooks.getInt("skill_id"), spbooks.getInt("item_id"));

        spbooks.close();
        statement.close();

        _log.info("SkillSpellbookTable: Loaded " + _skillSpellbooks.size() + " Spellbooks.");
    } catch (Exception e) {
        _log.warn("Error while loading spellbook data: ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

---

From source file:com.spend.spendService.SearchText.java

private void Search() {
    try {//ww w  .j av a  2  s  . c  o m
        TimerTask timertask = new TimerTask() {
            public void run() {
                try {
                    String[] seList = getSearchEngineNamesArray();
                    /* get search queries from keywords table */
                    PreparedStatement psmt = con.prepareStatement("SELECT searchKeyword FROM keywords");
                    ResultSet rs = psmt.executeQuery();
                    while (rs.next()) {
                        searchQuery = rs.getString("searchKeyword");
                        /* insert search queries into searchqueue table */
                        for (Object se : seList) {
                            PreparedStatement psmt1 = con.prepareStatement(
                                    "INSERT INTO searchqueue(searchText,disabled,searchEngineName) VALUES(?,0,?);");
                            psmt1.setString(1, searchQuery);
                            psmt1.setString(2, se.toString());
                            psmt1.executeUpdate();
                            psmt1.close();
                        }
                    }
                } catch (Exception ex) {
                    System.out.println("SearchText.java timertask run function SQL ERROR " + ex.getMessage());
                }
            }
        };
        Timer timer = new Timer();
        DateFormat dateformat = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
        Date date = dateformat.parse("20-07-2017 00:00:00"); // set time and date
        timer.schedule(timertask, date, 1000 * 60 * 60 * 24); // for 24 hour 1000*60*60*24
    } catch (Exception ex) {
        System.out.println("SearchText.java Search function ERROR " + ex.getMessage());
    }
}

---

From source file:CreateGameServlet.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    JSONObject json = new JSONObject();

    Enumeration paramNames = request.getParameterNames();
    String params[] = new String[1];
    int i = 0;/* w w  w. jav  a  2  s . co  m*/
    while (paramNames.hasMoreElements()) {
        String paramName = (String) paramNames.nextElement();
        String[] paramValues = request.getParameterValues(paramName);
        params[i] = paramValues[0];
        i++;
    }
    //System.out.println("param0->"+params[0]);

    DatabaseHandler db = new DatabaseHandler();

    conn = db.makeConnection();

    try {
        stmt = conn.createStatement();

        String query = "INSERT INTO game (hoster,nameplayer1) VALUES (\"" + params[0] + "\",\"" + params[0]
                + "\")";
        //System.out.println(query);

        int updatedRows = stmt.executeUpdate(query);
        if (updatedRows == 1) {
            json.put("reply", "done");
        } else {
            json.put("reply", "undone");
        }

        query = "SELECT id FROM game WHERE hoster=\"" + params[0] + "\"";
        PreparedStatement prstmt = conn.prepareStatement(query);
        ResultSet rs = prstmt.executeQuery();
        String id = "0";
        while (rs.next()) {
            id = rs.getString("id");
        }
        //System.out.println("id-> "+id);
        if (!id.equals("0")) {
            json.put("gameid", id);
        }

    } catch (SQLException ex) {
        Logger.getLogger(CreateGameServlet.class.getName()).log(Level.SEVERE, null, ex);
    }
    response.setContentType("application/json");
    response.setCharacterEncoding("UTF-8");
    response.getWriter().write(json.toString());

    db.closeAllConnections(conn, stmt);

}

---

From source file:dao.PblogSearchQuery.java

/**
 * This method lists all the results for the search text from pblogs
 * @param conn the connection/*from  w ww .j a  v a2  s .com*/
 * @param sString
 * @return HashSet the set that has the list of moderators for these collabrums.
 * @throws BaseDaoException - when error occurs
 **/
public HashSet run(Connection conn, String sString) throws BaseDaoException {

    if ((RegexStrUtil.isNull(sString) || conn == null)) {
        return null;
    }
    ResultSet rs = null;
    StringBuffer sb = new StringBuffer(
            "select hdlogin.loginid, hdlogin.login, hdlogin.fname, lname, LEFT(message, 160) as info, entrydate, usertags, pblogtopics.tid as ptid, hits from hdlogin left join pblogtopics on hdlogin.loginid=pblogtopics.pblogid left join pblogtags on pblogtopics.tid=pblogtags.tid left join pblog on hdlogin.loginid=pblog.loginid where ");

    // StringBuffer sb = new StringBuffer("select login, fname, lname, hits, membersince, LEFT(description, 160) as info from hdlogin left join usertab on hdlogin.loginid=usertab.loginid left join yourkeywords on hdlogin.loginid=yourkeywords.loginid left join mykeywords on hdlogin.loginid=mykeywords.loginid where ");

    ArrayList columns = new ArrayList();
    columns.add("topic");
    columns.add("message");
    columns.add("usertags");
    sb.append(sqlSearch.getConstraint(columns, sString));
    sb.append(" group by login order by hits DESC");

    logger.info("search query string" + sb.toString());

    try {
        PreparedStatement stmt = conn.prepareStatement(sb.toString());
        rs = stmt.executeQuery();

        Vector columnNames = null;
        Blog pblog = null;
        HashSet pendingSet = new HashSet();

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }

        while (rs.next()) {
            pblog = (Blog) eop.newObject(DbConstants.BLOG);
            for (int j = 0; j < columnNames.size(); j++) {
                if (((String) (columnNames.elementAt(j))).equalsIgnoreCase("entrydate")) {
                    try {
                        pblog.setValue("entrydate",
                                GlobalConst.dncalendar.getDisplayDate(rs.getTimestamp("entrydate")));
                        logger.info("entrydate" + rs.getTimestamp("entrydate"));
                    } catch (ParseException e) {
                        throw new BaseDaoException(
                                "could not parse the date for entrydate in PblogSearchQuery()"
                                        + rs.getTimestamp("entrydate"),
                                e);
                    }
                } else {
                    pblog.setValue((String) columnNames.elementAt(j),
                            (String) rs.getString((String) columnNames.elementAt(j)));
                }
            }
            pendingSet.add(pblog);
        }
        return pendingSet;
    } catch (Exception e) {
        throw new BaseDaoException("Error occured while executing search in pblog run query ", e);
    }
}

---

From source file:mx.com.pixup.portal.dao.DiscoPaisGenerateDaoJdbc.java

public void generateXML() {

    //querys//  w ww . ja  va  2  s.  co  m
    String sql = "SELECT id_idioma, id_disquera, pais.id as id_pais, pais.nombre as pais,\n"
            + "disco.titulo FROM disco\n" + "INNER JOIN pais ON pais.id = disco.id_pais";

    try {
        //seccion de preparacion de la query
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        //seccion de nodo raiz
        Element discos = new Element("discos");
        this.xmlLogico.setRootElement(discos);

        while (resultSet.next()) {
            //Elementos de Segundo Orden
            Element disco = new Element("disco");

            Attribute idioma = new Attribute("idioma", Integer.toString(resultSet.getInt("id_idioma")));
            disco.setAttribute(idioma);

            Attribute disquera = new Attribute("disquera", Integer.toString(resultSet.getInt("id_disquera")));
            disco.setAttribute(disquera);

            //Elementos de Tercer Orden
            Element pais = new Element("pais");

            Attribute id = new Attribute("id", Integer.toString(resultSet.getInt("id_pais")));
            pais.setAttribute(id);

            pais.setText(resultSet.getString("pais"));
            disco.addContent(pais);

            Element titulo = new Element("titulo");
            titulo.setText(resultSet.getString("titulo"));

            disco.addContent(titulo);

            discos.addContent(disco);
            //aqu se hace la magia para el XML
        }

        //se genera el xml fsico
        this.xmlFisico.setFormat(Format.getPrettyFormat());
        this.xmlFisico.output(this.xmlLogico, this.archivoFisico);
    } catch (Exception e) {
        //*** se quit el return porque el mtodo es void
        System.out.println(e.getMessage());
    }

}

---

From source file:net.mms_projects.copy_it.api.http.pages.v1.ClipboardUpdate.java

public void dispatchNotification(Database database, int user_id) throws SQLException {
    PreparedStatement statement = database.getConnection().prepareStatement(SELECT_GCM_TOKENS);
    statement.setInt(1, user_id);/*w w  w  .j a  va2s.c o  m*/
    ResultSet resultSet = statement.executeQuery();
    if (resultSet.first()) {
        GCMRunnable gcm = new GCMRunnable();
        do {
            gcm.addRegistrationId(resultSet.getString(GCM_TOKEN));
        } while (resultSet.next());
        gcm.setData("action", "content-updated");
        postProcess(gcm);
    }
    resultSet.close();
}

---

• «
• 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
• »