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:com.krawler.esp.servlets.SuperAdminServlet.java

public static String getCompanyList(Connection conn, String start, String limit) throws ServiceException {
    String result = null;// w  w w.jav  a2 s  .  c  o  m
    PreparedStatement pstmt = null;
    KWLJsonConverter KWL = new KWLJsonConverter();
    ResultSet rs = null;
    String tp = null;
    try {
        pstmt = conn.prepareStatement(
                "SELECT company.companyid, company.image, company.companyname, company.createdon, company.address, company.city, company.modifiedon,"
                        + "company.state, company.country, company.phone, company.fax, company.zip, company.timezone, company.website, company.activated, "
                        + "count(companyusers.companyid) AS members FROM company LEFT JOIN companyusers ON company.companyid=companyusers.companyid "
                        + "GROUP BY company.companyid LIMIT ? OFFSET ?;");
        pstmt.setInt(1, Integer.parseInt(limit));
        pstmt.setInt(2, Integer.parseInt(start));
        rs = pstmt.executeQuery();
        result = KWL.GetJsonForGrid(rs);
        pstmt.close();
        pstmt = conn.prepareStatement("select count(*) as count from company;");
        rs = pstmt.executeQuery();
        rs.next();
        int count1 = rs.getInt("count");
        result = result.substring(1);
        tp = "{\"count\":" + count1 + "," + result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("SuperAdminHandler.getSUAdminData", e);
    } finally {
        DbPool.closeStatement(pstmt);
    }
    return tp;
}

---

From source file:dao.CollBlobSearchQuery.java

/**
 * This method lists all the results for the search text from directories
 * @param conn the connection/*from  w w  w  . jav  a  2 s  .  co  m*/
 * @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 sString) throws BaseDaoException {

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

    StringBuffer sb = new StringBuffer(
            "select blobtype, entrydate, collblob.collabrumid, collblob.entryid, btitle from collblob left join collblobtags on collblob.entryid=collblobtags.entryid where ");
    ArrayList columns = new ArrayList();
    columns.add("usertags");
    sb.append(sqlSearch.getConstraint(columns, sString));
    try {
        PreparedStatement stmt = conn.prepareStatement(sb.toString());
        ResultSet rs = stmt.executeQuery();
        Vector columnNames = null;
        Photo photo = null;
        HashSet pendingSet = new HashSet();

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

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

---

From source file:de.bley.word.filereader.ReaderJdbc.java

/**
 *
 * @param table Tabellenname.//from  w  w w  .  j  a va2  s . co  m
 * @return Inhalt des Angegebenen Datenbanktabels.
 */
@Override
public String readFile(String table) {
    Connection connection = JdbcConnection.getInstance().connect();
    StringBuilder buffer = new StringBuilder();

    if (connection != null) {
        try {
            //                String sql = "CREATE TABLE TextDatein" + "(inhalt VARCHAR(255)" + "PRIMARY KEY (inhalt))";
            //                Statement stmt = connection.createStatement();
            //                stmt.executeQuery(sql);

            //                PreparedStatement ps = connection.prepareStatement("select * from APP.?");
            //                ps.setString(1, table);
            PreparedStatement ps = connection.prepareStatement("Select * from App.MyTable");
            ResultSet result = ps.executeQuery();
            boolean run = true;
            while (run) {
                if (result.next()) {
                    buffer.append(result.getString(1)).append("\n");
                } else {
                    run = false;
                }
            }
            connection.close();
            return buffer.toString();
        } catch (SQLException ex) {
            log.debug("execute of Query", ex);
        }
    }
    return null;
}

---

From source file:biz.vnc.zimbra.lighthistoryzimlet.MailhistoryReader.java

public String getRecord(String msgId) {
    JSONObject storejson = new JSONObject();
    JSONArray jsonArray = new JSONArray();
    try {/*w ww  . j  a v a  2s  .  c  o  m*/
        dbConnection = LocalDB.connect(LocalConfig.get().db_name);
        String query = "SELECT * FROM mail_log_internal WHERE message_id=?" + "ORDER BY logtime ASC";
        PreparedStatement statement = dbConnection.prepareStatement(query);
        statement.setString(1, msgId.trim());
        ResultSet resultSet = statement.executeQuery();
        ZLog.info("biz_vnc_lightweight_history", "Read Message Id" + msgId);
        while (resultSet.next()) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("logtime", resultSet.getString("logtime"));
            if (resultSet.getString("from_localpart").equals("-")
                    || resultSet.getString("from_domain").equals("-")) {
                jsonObject.put("from", "-");
            } else {
                jsonObject.put("from",
                        resultSet.getString("from_localpart") + "@" + resultSet.getString("from_domain"));
            }
            if (resultSet.getString("to_localpart").equals("-")
                    || resultSet.getString("to_domain").equals("-")) {
                jsonObject.put("to", "-");
            } else {
                jsonObject.put("to",
                        resultSet.getString("to_localpart") + "@" + resultSet.getString("to_domain"));
            }
            jsonObject.put("moveto", resultSet.getString("foldername"));
            jsonObject.put("event", resultSet.getString("event"));
            jsonArray.add(jsonObject);
        }
        storejson.put("list", jsonArray);
    } catch (Exception e) {
        ZLog.err("mail-history", "getRecord: database query failed", e);
    }
    ZLog.info("biz_vnc_lightweight_history", "Recod Json :: " + storejson.toJSONString());
    return storejson.toJSONString();
}

---

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

public FullHttpResponse onGetRequest(HttpRequest request, Database database, HeaderVerifier headerVerifier)
        throws Exception {
    PreparedStatement statement = database.getConnection().prepareStatement(SELECT_USER);
    statement.setInt(1, headerVerifier.getUserId());
    ResultSet result = statement.executeQuery();
    if (result.first()) {
        final JSONObject json = new JSONObject();
        json.put(ID, result.getInt(ID));
        json.put(EMAIL, result.getString(EMAIL));
        json.put(SIGNED_UP, result.getInt(SIGNED_UP));
        result.close();/*w w  w  . ja v  a 2  s . c o  m*/
        return new DefaultFullHttpResponse(request.getProtocolVersion(), OK,
                Unpooled.copiedBuffer(json.toString(), CharsetUtil.UTF_8));
    }
    result.close();
    return null;
}

---

From source file:com.l2jfree.gameserver.instancemanager.BlockListManager.java

private BlockListManager() {
    Connection con = null;// www  .  ja  va  2 s . c  om
    try {
        con = L2DatabaseFactory.getInstance().getConnection();

        PreparedStatement statement = con.prepareStatement(SELECT_QUERY);
        ResultSet rset = statement.executeQuery();

        while (rset.next()) {
            Integer objectId = L2Integer.valueOf(rset.getInt("charId"));
            String name = rset.getString("name");

            getBlockList(objectId).add(name);
        }

        rset.close();
        statement.close();
    } catch (SQLException e) {
        _log.warn("", e);
    } finally {
        L2DatabaseFactory.close(con);
    }

    int size = 0;

    for (Set<String> set : _blocks.values())
        size += set.size();

    _log.info("BlockListManager: Loaded " + size + " character block(s).");
}

---

From source file:com.l2jfree.gameserver.instancemanager.ContestableHideoutGuardManager.java

public final void load() {
    Connection con = null;/*from w ww  . ja v  a 2s.c  o m*/
    FastList<L2Spawn> guards = new FastList<L2Spawn>(50);
    try {
        con = L2DatabaseFactory.getInstance().getConnection();
        PreparedStatement ps = con.prepareStatement(LOAD_SIEGE_GUARDS);
        ps.setInt(1, _hideout.getId());
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            L2Spawn s = new L2Spawn(NpcTable.getInstance().getTemplate(rs.getInt("npcId")));
            s.setId(rs.getInt("id"));
            s.setAmount(1);
            s.setLocx(rs.getInt("x"));
            s.setLocy(rs.getInt("y"));
            s.setLocz(rs.getInt("z"));
            s.setHeading(rs.getInt("heading"));
            s.setRespawnDelay(rs.getInt("respawnDelay"));
            s.setLocation(0);
            guards.add(s);
        }
        _guardSpawn = guards.toArray(new L2Spawn[guards.size()]);
    } catch (Exception e) {
        _log.error("Failed loading " + _hideout.getName() + "'s siege guards!", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

---

From source file:com.l2jfree.gameserver.model.entity.events.VIP.java

public static void setLoc() {
    Connection con = null;/*w  w  w  .  ja v a2s.  c  o  m*/

    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement(
                "SELECT endx,endy,endz,startx,starty,startz FROM VIPinfo WHERE teamID = " + _team);
        ResultSet rset = statement.executeQuery();
        rset.next();

        _endX = rset.getInt("endx");
        _endY = rset.getInt("endy");
        _endZ = rset.getInt("endz");
        _startX = rset.getInt("startx");
        _startY = rset.getInt("starty");
        _startZ = rset.getInt("startz");

        rset.close();
        statement.close();
    } catch (SQLException e) {
        _log.error("Could not check End & Start LOC for team" + _team + " got: ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

---

From source file:com.wso2telco.gsma.authenticators.DBUtils.java

public static int readPinAttempts(String sessionId) throws SQLException, AuthenticatorException {

    Connection connection;//from w  ww  . j  a va  2  s. c  o  m
    PreparedStatement ps;
    int noOfAttempts = 0;
    ResultSet rs;

    String sql = "select attempts from `multiplepasswords` where " + "ussdsessionid=?;";

    connection = getConnectDBConnection();

    ps = connection.prepareStatement(sql);

    ps.setString(1, sessionId);

    rs = ps.executeQuery();

    while (rs.next()) {
        noOfAttempts = rs.getInt("attempts");
    }
    if (connection != null) {
        connection.close();
    }

    return noOfAttempts;
}

---

From source file:dao.DirBlobSearchQuery.java

/**
 * This method lists all the results for the search text from directories
 * @param conn the connection/* www. j a  v a 2  s.  c o  m*/
 * @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 sString) throws BaseDaoException {

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

    /*        StringBuffer sb = new StringBuffer("select blobtype, dirblob.directoryid, entrydate, dirblob.entryid, btitle from dirblob left join dirblobtags on dirblob.entryid=dirblobtags.entryid where ");
    */
    StringBuffer sb = new StringBuffer(
            "select distinct d1.btitle, d1.mimetype, d1.entryid, d1.directoryid from dirblob d1, dirblobtags d2 where ");
    ArrayList columns = new ArrayList();
    columns.add("d1.btitle");
    columns.add("d2.usertags");
    sb.append(sqlSearch.getConstraint(columns, sString));
    sb.append(" and d1.entryid=d2.entryid and d1.directoryid=d2.directoryid");
    try {
        PreparedStatement stmt = conn.prepareStatement(sb.toString());
        ResultSet rs = stmt.executeQuery();

        Vector columnNames = null;
        Photo photo = null;
        HashSet pendingSet = new HashSet();

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

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

---

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