Example usage for java.sql PreparedStatement executeQuery

List of usage examples for java.sql PreparedStatement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. 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:ch.newscron.referral.ReferralManager.java

/**
 * Calls the database to query for all rows in ShortURL table of database
 * @return a List of CustomerShortURL objects, consisting of all shortURL entries in table
 *//*from   ww  w.  j  a  va2 s.c om*/
public static List<CustomerShortURL> getAllShortURLs() {
    Connection connection = null;
    PreparedStatement query = null;
    ResultSet rs = null;
    try {
        connection = connect();
        query = connection.prepareStatement("SELECT * FROM ShortURL");
        rs = query.executeQuery();
        List<CustomerShortURL> shortURLList = parseResultSet(rs);
        return shortURLList;
    } catch (Exception ex) {
        Logger.getLogger(ReferralManager.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        disconnect(connection, query, rs);
    }
    return null;
}

From source file:com.sql.EmailOutAttachment.java

/**
 * Gathers a list of attachments for a specific email address.
 * /*from ww  w  . j  a v  a 2 s.  co m*/
 * @param emailID Integer
 * @return List (EmailOutAttachmentModel) 
 */
public static List<EmailOutAttachmentModel> getAttachmentsByEmail(int emailID) {
    List<EmailOutAttachmentModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOutAttachment WHERE emailOutID = ?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, emailID);
        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutAttachmentModel item = new EmailOutAttachmentModel();
            item.setId(rs.getInt("id"));
            item.setEmailOutID(rs.getInt("emailOutID"));
            item.setFileName(rs.getString("fileName"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

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

/**
 * returns all profile information/*  w  w  w .  j  av a 2 s. co m*/
 *
 * @return list of profiles
 */
public static List<Profile> getAllProfiles() {

    ArrayList<Profile> profileList = new ArrayList<>();
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from  profiles order by nm asc");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Profile profile = new Profile();
            profile.setId(rs.getLong("id"));
            profile.setNm(rs.getString("nm"));
            profile.setDesc(rs.getString("desc"));
            profileList.add(profile);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    } finally {
        DBUtils.closeConn(con);
    }

    return profileList;
}

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

public static boolean doesPetNameExist(String name, int petNpcId) {
    boolean result = true;
    Connection con = null;//from   ww w .  j  ava  2 s  .com

    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement(
                "SELECT name FROM pets p, items i WHERE p.item_obj_id = i.object_id AND name=? AND i.item_id=?");
        statement.setString(1, name);
        statement.setString(2, Integer.toString(PetDataTable.getItemIdByPetId(petNpcId)));
        ResultSet rset = statement.executeQuery();
        result = rset.next();
        rset.close();
        statement.close();
    } catch (SQLException e) {
        _log.warn("could not check existing petname:" + e.getMessage(), e);
    } finally {
        L2DatabaseFactory.close(con);
    }

    return result;
}

From source file:com.sql.EmailOutRelatedCase.java

public static List<EmailOutRelatedCaseModel> getRelatedCases(EmailOutModel eml) {
    List<EmailOutRelatedCaseModel> list = new ArrayList();
    Connection conn = null;//  w  ww.  jav a 2s. c o  m
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOutRelatedCase WHERE emailOutId = ? ";

        ps = conn.prepareStatement(sql);
        ps.setInt(1, eml.getId());

        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutRelatedCaseModel item = new EmailOutRelatedCaseModel();
            item.setId(rs.getInt("id"));
            item.setEmailOutId(rs.getInt("emailOutId"));
            item.setCaseYear(rs.getString("caseYear"));
            item.setCaseType(rs.getString("caseType"));
            item.setCaseMonth(rs.getString("caseMonth"));
            item.setCaseNumber(rs.getString("caseNumber"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.concursive.connect.web.modules.activity.utils.ProjectHistoryUtils.java

public static int findNextThreadPosition(Connection db, ProjectHistory parentProjectHistory)
        throws SQLException {
    int count = 0;
    PreparedStatement pst = db
            .prepareStatement("SELECT count(*) AS ccount " + "FROM project_history " + "WHERE lineage LIKE ? ");
    pst.setString(1, parentProjectHistory.getLineage() + parentProjectHistory.getId() + "/%");
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        count = rs.getInt("ccount");
    }/*from   ww w .j a va  2 s .c om*/
    rs.close();
    pst.close();
    return (parentProjectHistory.getThreadPosition() + count + 1);
}

From source file:ch.newscron.registration.UserRegistration.java

public static List<User> getAllUsers() {
    Connection connection = null;
    PreparedStatement query = null;
    ResultSet rs = null;//from w w  w.j a  va  2s  .c  o  m
    try {
        connection = connect();
        query = connection.prepareStatement("SELECT * FROM ShortURL, User WHERE User.campaignId=ShortURL.id");
        rs = query.executeQuery();
        List<User> userList = parseResultSet(rs);

        return userList;
    } catch (Exception ex) {
        Logger.getLogger(UserRegistration.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        disconnect(connection, query, rs);
    }
    return null;
}

From source file:com.concursive.connect.web.modules.activity.utils.ProjectHistoryUtils.java

public static int findNextPosition(Connection db, int projectHistoryId) throws SQLException {
    int position = 0;
    PreparedStatement pst = db.prepareStatement("SELECT max(position) AS position " + "FROM project_history "
            + "WHERE history_id = ? OR top_id = ? ");
    pst.setInt(1, projectHistoryId);//from  w  ww .  j a  va 2 s . com
    pst.setInt(2, projectHistoryId);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        position = rs.getInt("position");
    }
    rs.close();
    pst.close();
    return (position + 1);
}

From source file:edu.jhu.pha.vospace.jobs.JobsProcessor.java

/**
 * Returns the JobDescription object serialized from the database record
 * @param jobId The identifier of a job//from  w w w  . j a va2s  .  c o m
 * @return The job java object
 */
public static JobDescription getJob(final UUID jobId) {
    return DbPoolServlet.goSql("GetJob request", "select json_notation, note from jobs where id = ?",
            new SqlWorker<JobDescription>() {
                @Override
                public JobDescription go(Connection conn, PreparedStatement stmt) throws SQLException {
                    JobDescription returnJob = null;
                    stmt.setString(1, jobId.toString());
                    ResultSet rs = stmt.executeQuery();
                    if (rs.next()) {
                        byte[] jobJsonNotation = rs.getBytes(1);
                        try {
                            returnJob = (new ObjectMapper()).readValue(jobJsonNotation, 0,
                                    jobJsonNotation.length, JobDescription.class);
                            returnJob.setNote(rs.getString("note"));
                        } catch (JsonMappingException ex) { // Shouldn't happen
                            throw new InternalServerErrorException(ex.getMessage());
                        } catch (JsonParseException ex) {
                            throw new InternalServerErrorException(ex.getMessage());
                        } catch (IOException ex) {
                            throw new InternalServerErrorException(ex.getMessage());
                        }
                    }
                    return returnJob;
                }
            });
}

From source file:Main.java

public static List<Map<String, Object>> query(Connection connection, String sql, List<Object> parameters)
        throws SQLException {
    List<Map<String, Object>> results = null;
    PreparedStatement ps = null;
    ResultSet rs = null;/*from  w w w.j  av  a 2 s  .  c o m*/
    try {
        ps = connection.prepareStatement(sql);

        int i = 0;
        for (Object parameter : parameters) {
            ps.setObject(++i, parameter);
        }
        rs = ps.executeQuery();
        results = map(rs);
    } finally {
        close(rs);
        close(ps);
    }
    return results;
}