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:com.nabla.dc.server.handler.fixed_asset.UpdateAssetDisposalHandler.java

private Date getAssetAcqisitionDate(final Connection conn, int assetId) throws SQLException, ActionException {
    final PreparedStatement stmt = StatementFormat.prepare(conn,
            "SELECT acquisition_date FROM fa_asset WHERE id=?;", assetId);
    try {/*from  w ww  .ja v a  2s .com*/
        final ResultSet rs = stmt.executeQuery();
        try {
            if (!rs.next())
                throw new ActionException(CommonServerErrors.RECORD_HAS_BEEN_REMOVED);
            return rs.getDate(1);
        } finally {
            rs.close();
        }
    } finally {
        stmt.close();
    }
}

From source file:com.l2jfree.gameserver.AutoAnnouncements.java

private void restore() {
    Connection conn = null;//from  w  w w.j ava  2  s .  c  o m
    try {
        conn = L2DatabaseFactory.getInstance().getConnection();

        PreparedStatement statement = conn
                .prepareStatement("SELECT initial, delay, cycle, memo FROM auto_announcements");
        ResultSet data = statement.executeQuery();

        while (data.next()) {
            final long initial = data.getLong("initial");
            final long delay = data.getLong("delay");
            final int repeat = data.getInt("cycle");
            final String[] memo = data.getString("memo").split("\n");

            _announcers.add(new AutoAnnouncer(memo, repeat, initial, delay));
        }

        data.close();
        statement.close();
    } catch (Exception e) {
        _log.fatal("AutoAnnoucements: Failed to load announcements data.", e);
    } finally {
        L2DatabaseFactory.close(conn);
    }

    _log.info("AutoAnnoucements: Loaded " + _announcers.size() + " Auto Annoucement Data.");
}

From source file:com.product.ProductResource.java

/**
 * Retrieves representation of an instance of com.oracle.products.ProductResource
 * @return an instance of java.lang.String
 *///  w  ww  . jav  a2  s.c  o  m
@GET
@Produces(MediaType.APPLICATION_JSON)
public String getAllProducts() throws SQLException {
    if (conn == null) {
        return "not connected";
    } else {
        String query = "Select * from product";
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rs = pstmt.executeQuery();
        String result = "";
        JSONArray productArr = new JSONArray();
        while (rs.next()) {
            JsonObjectBuilder prodMap = Json.createObjectBuilder();
            prodMap.add("productID", rs.getInt("product_id"));
            prodMap.add("name", rs.getString("name"));
            prodMap.add("description", rs.getString("description"));
            prodMap.add("quantity", rs.getInt("quantity"));
            productArr.add(prodMap);
        }
        result = productArr.toString();
        return result.replace("},", "},\n");
    }

}

From source file:com.artivisi.iso8583.persistence.MapperServiceTestIT.java

private void verifyMapper(Mapper m, Connection conn, String name, String description) throws SQLException {
    String sqlSelectMapper = "select * from iso8583_mapper where id = ?";
    PreparedStatement psSelectMapper = conn.prepareStatement(sqlSelectMapper);
    psSelectMapper.setString(1, m.getId());
    ResultSet rsSelectMapper = psSelectMapper.executeQuery();
    assertTrue(rsSelectMapper.next());/*from ww w  . ja  v a  2 s.co m*/
    assertEquals(name, rsSelectMapper.getString("name"));
    assertEquals(description, rsSelectMapper.getString("description"));
}

From source file:edu.mayo.bmi.dictionary.jdbc.JdbcDictionaryImpl.java

/**
 * vng changes - use cache, use immutable collections to save memory. use
 * ImmutableList because there is no implementation of equals on the
 * MetaDataHit//from  ww  w . java  2 s  .c om
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public Collection getEntries(String str) throws DictionaryException {
    // Set metaDataHitSet = new HashSet();
    // check cache
    if (cache != null) {
        Element e = cache.get(str);
        if (e != null) {
            return (Collection) e.getObjectValue();
        }
    }
    try {
        ImmutableList.Builder<MetaDataHit> b = new ImmutableList.Builder<MetaDataHit>();
        PreparedStatement prepStmt = initMetaDataPrepStmt(str);
        ResultSet rs = prepStmt.executeQuery();

        while (rs.next()) {
            // Map nameValMap = new HashMap();
            ImmutableMap.Builder<String, String> mb = new ImmutableMap.Builder<String, String>();
            Iterator<String> metaFieldNameItr = getMetaFieldNames();
            while (metaFieldNameItr.hasNext()) {
                String metaFieldName = metaFieldNameItr.next();
                String metaFieldValue = rs.getString(metaFieldName);
                if (metaFieldValue != null) {
                    // nameValMap.put(metaFieldName, metaFieldValue);
                    mb.put(metaFieldName, metaFieldValue);
                }
            }
            // MetaDataHit mdh = new GenericMetaDataHitImpl(nameValMap);
            // metaDataHitSet.add(mdh);
            b.add(new GenericMetaDataHitImpl(mb.build()));
        }
        List<MetaDataHit> metaDataHitSet = b.build();
        // add to cache
        if (cache != null) {
            cache.put(new Element(str, metaDataHitSet));
        }
        return metaDataHitSet;
        //      } catch (SQLException e) {
        //         throw new DictionaryException(e);
    } catch (Exception e) {
        log.error("error on: " + str, e);
        throw new DictionaryException(e);
    }
}

From source file:com.artivisi.iso8583.persistence.MapperServiceTestIT.java

private void verifyCountMapper(Mapper m, Connection conn, int count) throws SQLException {
    String sqlSelectMapper = "select count(*) from iso8583_mapper where name = ?";
    PreparedStatement psSelectMapper = conn.prepareStatement(sqlSelectMapper);
    psSelectMapper.setString(1, m.getName());
    ResultSet rsSelectMapper = psSelectMapper.executeQuery();
    assertTrue(rsSelectMapper.next());/*from ww  w.  j  av  a 2 s.c o  m*/
    assertEquals(new Integer(count), new Integer(rsSelectMapper.getInt(1)));
}

From source file:org.miloss.fgsms.bueller.Bueller.java

/**
 * Alternate URLs, basically multiple hostnames, FQDN, host file entries, ip
 * addresses, etc can map to the same service. Sometimes a particular url is
 * firewalled or only listens on a specific hostname. By default bueller
 * will first attempt a connection using the modified url, i.e. the URL
 * displayed in fgsms which usually uses the hostname of the machine hosting
 * the service If another url was observed by an agent at some point in
 * time, then this will fetch all other urls for
 *
 * @param url/*from w  w  w. j a v a  2s.  c om*/
 * @param perf
 * @return
 */
protected static List<String> GetAlternateUrls(String url, java.sql.Connection perf) {
    List<String> alts = new ArrayList<String>();
    PreparedStatement com = null;
    ResultSet rs = null;
    try {
            
        //dec 10-2011 PreparedStatement com = perf.prepareStatement("select  originalurl from rawdata where uri=? group by originalurl;");
        com = perf.prepareStatement("select  alturi from alternateurls where uri=?;");
        com.setString(1, url);
        rs = com.executeQuery();
        while (rs.next()) {
            String t = rs.getString(1);
            if (!Utility.stringIsNullOrEmpty(t)) {
                t = t.trim();
                if (!Utility.stringIsNullOrEmpty(t)) {
                    if (!t.equals(url)) {
                        if (!url.equals(t)) {
                            alts.add(t);
                        }
                    }
                }
            }
            //TODO future optimization but not required, this might be a good spot to filter out localhost/127.0.0.1 records, but only if this machine is different that the machine hosting the service
        }
    } catch (Exception ex) {
        log.log(Level.ERROR, null, ex);
    } finally {
        DBUtils.safeClose(rs);
        DBUtils.safeClose(com);
    }
    return alts;
        
}

From source file:com.Assignment4.Assign.java

/**
 * Retrieves representation of an instance of com.oracle.products.ProductResource
 * @return an instance of java.lang.String
 *///w  w  w . j  a va 2 s.  co m
@GET
@Produces(MediaType.APPLICATION_JSON)
public String getAllProducts() throws SQLException {
    if (connected == null) {
        return "not connected";
    } else {
        String query = "Select * from product";
        PreparedStatement ps = connected.prepareStatement(query);
        ResultSet rs = ps.executeQuery();
        String res = "";
        JSONArray productArr = new JSONArray();
        while (rs.next()) {
            Map proMap = new LinkedHashMap();
            proMap.put("productID", rs.getInt("product_id"));
            proMap.put("name", rs.getString("name"));
            proMap.put("description", rs.getString("description"));
            proMap.put("quantity", rs.getInt("quantity"));
            productArr.add(proMap);
        }
        res = productArr.toString();
        return res.replace("},", "},\n");
    }

}

From source file:com.products.ProductResource.java

/**
 * Retrieves representation of an instance of com.oracle.products.ProductResource
 * @return an instance of java.lang.String
 *///w  w  w.ja va2 s .c  o m
@GET
@Produces(MediaType.APPLICATION_JSON)
public String getAllProducts() throws SQLException {
    if (co == null) {
        return "not connected";
    } else {
        String query = "Select * from product";
        PreparedStatement pstmt = co.prepareStatement(query);
        ResultSet rs = pstmt.executeQuery();
        String result = "";
        JSONArray productArr = new JSONArray();
        while (rs.next()) {
            Map productMap = new LinkedHashMap();
            productMap.put("productID", rs.getInt("product_id"));
            productMap.put("name", rs.getString("name"));
            productMap.put("description", rs.getString("description"));
            productMap.put("quantity", rs.getInt("quantity"));
            productArr.add(productMap);
        }
        result = productArr.toString();
        return result.replace("},", "},\n");
    }

}

From source file:ece356.UserDBAO.java

public static DoctorData queryDoctor(String userName) throws ClassNotFoundException, SQLException {
    Connection con = null;/* w  ww  .  j  av  a2s. co m*/
    PreparedStatement pstmt = null;
    DoctorData ret;
    try {
        con = getConnection();

        // Query for general doctor information
        String query = "SELECT * FROM doctorView where username = ?";
        pstmt = con.prepareStatement(query);
        pstmt.setString(1, userName);

        ResultSet resultSet;
        resultSet = pstmt.executeQuery();
        resultSet.next();
        ret = new DoctorData();
        ret.userName = resultSet.getString("username");
        ret.firstName = resultSet.getString("first_name");
        ret.lastName = resultSet.getString("last_name");
        ret.middleInitial = resultSet.getString("middle_initial");
        ret.gender = resultSet.getString("gender");
        ret.emailAddress = resultSet.getString("email_address");
        ret.yearsLicensed = resultSet.getInt("yearsLicensed");
        ret.averageRating = resultSet.getInt("averageRating");
        ret.numberOfReviews = resultSet.getInt("numberOfReviews");

        // Query for work addresses of doctor
        query = "SELECT * FROM doctorWorkAddressView where doc_address_username = ?";
        pstmt = con.prepareStatement(query);
        pstmt.setString(1, userName);
        resultSet = pstmt.executeQuery();

        ArrayList<WorkAddressData> workAddressList = new ArrayList<WorkAddressData>();
        ret.workAddressList = workAddressList;
        while (resultSet.next()) {
            WorkAddressData workAddress = new WorkAddressData();
            workAddress.city = resultSet.getString("city");
            workAddress.state = resultSet.getString("state");
            workAddress.postalCode = resultSet.getString("postal_code");
            workAddress.streetName = resultSet.getString("street_name");
            workAddress.streetNumber = resultSet.getInt("street_number");
            workAddress.unitNumber = resultSet.getString("street_unit_number");
            workAddressList.add(workAddress);
        }

        // Query for specializations of doctor
        query = "SELECT * FROM doctorSpecializationView where doc_spec_username = ?";
        pstmt = con.prepareStatement(query);
        pstmt.setString(1, userName);
        resultSet = pstmt.executeQuery();

        ArrayList<String> specializationList = new ArrayList<String>();
        ret.specializationList = specializationList;
        while (resultSet.next()) {
            String specialization = resultSet.getString("specTypeName");
            specializationList.add(specialization);
        }

        // Query for reviews of doctor
        query = "SELECT * FROM review where doc_username = ? order by date desc";
        pstmt = con.prepareStatement(query);
        pstmt.setString(1, userName);
        resultSet = pstmt.executeQuery();

        ArrayList<ReviewData> reviewList = new ArrayList<ReviewData>();
        ret.reviewList = reviewList;
        while (resultSet.next()) {
            ReviewData review = new ReviewData();
            review.comment = resultSet.getString("comment");
            review.reviewId = resultSet.getString("reviewId");
            review.doctorUsername = resultSet.getString("doc_username");
            review.patientUsername = resultSet.getString("patient_username");
            review.date = resultSet.getDate("date");
            review.rating = resultSet.getInt("rating");
            reviewList.add(review);
        }

        return ret;
    } catch (Exception e) {
        System.out.println("EXCEPTION:%% " + e);
    } finally {
        if (pstmt != null) {
            pstmt.close();
        }
        if (con != null) {
            con.close();
        }
    }
    return null;
}