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.autentia.tnt.bill.migration.support.OriginalInformationRecoverer.java

/**
 * Recupera la suma total de todos los conceptos de cada una de las facturas cuyo tipo se envia por parametro
 * @param billType tipo de factura/*from  w  ww .j  a  va  2  s.  c  o  m*/
 */
public static double[] getImporteFacturaOriginal(String billType) throws Exception {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    double[] result = new double[0];

    try {
        log.info("RECOVERING IMPORTE FACTURAS " + billType + " ORIGINALES");

        // connect to database
        Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER);
        con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION,
                BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); //NOSONAR
        con.setAutoCommit(false); //DATABASE_PASS vacio.            

        String sql = "SELECT sum((bb.units*bb.amount)*(1+(bb.iva/100))) as total from Bill b left join BillBreakDown bb on b.id=bb.billId, Organization o, Project p where b.projectId = p.id and p.organizationId = o.id and b.billType= ? group by b.id order by total";

        pstmt = con.prepareStatement(sql);

        rs = pstmt.executeQuery();
        pstmt.setString(1, billType);

        rs.last();
        result = new double[rs.getRow()];
        rs.beforeFirst();
        int counter = 0;

        while (rs.next()) {
            result[counter] = rs.getDouble(1);
            log.info("\t" + result[counter]);
            counter++;
        }
        con.commit();
    } catch (Exception e) {
        log.error("FAILED: WILL BE ROLLED BACK: ", e);
        if (con != null) {
            con.rollback();
        }
    } finally {
        cierraFichero(file);
        liberaConexion(con, pstmt, rs);
    }
    return result;
}

---

From source file:dao.VisitTrafficDailySearchTagCountQuery.java

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

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

    ResultSet rs = null;
    StringBuffer sb = new StringBuffer("select count(*) from visittraffic where ");

    ArrayList columns = new ArrayList();
    columns.add("url");
    sb.append(sqlSearch.getConstraint(columns, sString));
    sb.append(
            " and YEAR(visitdate)=YEAR(CURDATE()) and MONTH(visitdate)=MONTH(CURDATE()) and DAY(visitdate)=DAY(CURDATE())");

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

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

        Vector columnNames = null;
        Yourkeywords tag = null;
        List pendingList = new ArrayList();

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }
        while (rs.next()) {
            tag = (Yourkeywords) eop.newObject(DbConstants.YOURKEYWORDS);
            for (int j = 0; j < columnNames.size(); j++) {
                tag.setValue((String) columnNames.elementAt(j),
                        (String) rs.getString((String) columnNames.elementAt(j)));
            }
            pendingList.add(tag);
        }
        return pendingList;
    } catch (Exception e) {
        throw new BaseDaoException("Error occured while executing search in tag run query " + sb.toString(), e);
    }
}

---

From source file:dao.CollSearchQuery.java

/**
 * This method lists all the results for the search text
 * @param conn the connection//w ww  .  jav  a 2s.  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 collabrumid, name, LEFT(coldesc, 160) as info, hits from collabrum where ");

    ArrayList columns = new ArrayList();
    columns.add("coldesc");
    columns.add("name");
    columns.add("keywords");
    sb.append(sqlSearch.getConstraint(columns, stext));
    sb.append(" order by hits DESC");

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

        Vector columnNames = null;
        Collabrum collabrum = null;
        HashSet pendingSet = new HashSet();

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

        while (rs.next()) {
            collabrum = (Collabrum) eop.newObject(DbConstants.COLLABRUM);
            for (int j = 0; j < columnNames.size(); j++) {
                collabrum.setValue((String) columnNames.elementAt(j),
                        (String) rs.getString((String) columnNames.elementAt(j)));
            }
            pendingSet.add(collabrum);
        }
        return pendingSet;
    } catch (Exception e) {
        throw new BaseDaoException("Error occured while executing search in collabrum run query ", e);
    }
}

---

From source file:com.uiip.gviviani.esercizioweekend.interfaces.impl.DefaultPhoneDAO.java

@Override
public PhoneModel getPhoneInfo(String name) {
    PhoneModel phone = new PhoneModel();
    MysqlDataSource datasource = new MysqlDataSource();
    datasource.setUser("root");
    datasource.setPassword("root");
    datasource.setUrl("jdbc:mysql://localhost:3306/Rubrica");
    Connection connection = null;
    try {/* www. j  a  v a2  s .c om*/
        connection = datasource.getConnection();
        String sql = "SELECT id, name, brand, opsys, displaySize " + "FROM telefono WHERE name = ? ;";
        PreparedStatement stat = connection.prepareStatement(sql);
        stat.setString(1, name);
        ResultSet res = stat.executeQuery();
        if (res.first()) {
            phone.setNome(res.getString("name"));
            phone.setBrand(res.getString("brand"));
            phone.setOpsys(res.getString("opsys"));
            phone.setDisplay(res.getString("displaySize"));
        } else {
            phone = null;
        }
    } catch (SQLException e) {
        logger.error(e);
        phone = null;
    } finally {
        DbUtils.closeQuietly(connection);
    }
    return phone;
}

---

From source file:com.hendisantika.pasien.domain.PasienIdGenerator.java

public Serializable generate(SessionImplementor session, Object object) throws HibernateException {

    String prefix = "PAS-";
    //        String prefix = "1500";
    Connection connection = session.connection();
    try {/*from   ww  w . j  av a2  s.c  o m*/

        PreparedStatement ps = connection
                .prepareStatement("SELECT MAX(pasien_id) as value from db_pasien.pasien2");

        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            int id = rs.getInt("value") + 1;
            //                String id = rs.getString("value") + 1;
            String code = prefix + new Integer(id);
            //                String code = prefix + id;

            //                String code = prefix + StringUtils.leftPad("" + id, 3, '0');
            System.out.println("Generated pasienId: " + code);
            return code;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

---

From source file:com.uit.anonymousidentity.Repository.IssuerKeys.IssuerJDBCTemplate.java

@Override
public boolean isContainSid(String sid) throws SQLException {
    String sql = "select * from " + TABLE_NAME + " where " + SID + " = " + "'" + sid + "'";
    PreparedStatement pst = dataSource.getConnection().prepareStatement(sql);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        return true;
    } else/*from   ww w.  j av a 2 s.  c  om*/
        return false;
}

---

From source file:com.splicemachine.derby.utils.SpliceAdmin.java

/**
 * Be Careful when using this, as it will return conglomerate ids for all the indices of a table
 * as well as the table itself. While the first conglomerate SHOULD be the main table, there
 * really isn't a guarantee, and it shouldn't be relied upon for correctness in all cases.
 *///  www  .  ja va  2 s.  c  o  m
public static long[] getConglomNumbers(Connection conn, String schemaName, String tableName)
        throws SQLException {
    List<Long> conglomIDs = new ArrayList<>();
    if (schemaName == null)
        // default schema
        schemaName = SQLConfiguration.SPLICE_USER;

    String query;
    boolean isTableNameEmpty;

    if (tableName == null) {
        query = getSqlConglomsInSchema();
        isTableNameEmpty = true;
    } else {
        query = getSqlConglomsInTable();
        isTableNameEmpty = false;
    }

    ResultSet rs = null;
    PreparedStatement s = null;
    try {
        s = conn.prepareStatement(query);
        s.setString(1, schemaName.toUpperCase());
        if (!isTableNameEmpty) {
            s.setString(2, tableName.toUpperCase());
        }
        rs = s.executeQuery();
        while (rs.next()) {
            conglomIDs.add(rs.getLong(1));
        }

        if (conglomIDs.isEmpty()) {
            if (isTableNameEmpty) {
                throw PublicAPI
                        .wrapStandardException(ErrorState.LANG_SCHEMA_DOES_NOT_EXIST.newException(schemaName));
            }
            throw PublicAPI.wrapStandardException(ErrorState.LANG_TABLE_NOT_FOUND.newException(tableName));
        }
    } finally {
        if (rs != null)
            rs.close();
        if (s != null)
            s.close();
    }
    if (conglomIDs.isEmpty()) {
        return new long[0];
    }
    long[] congloms = new long[conglomIDs.size()];
    for (int i = 0; i < conglomIDs.size(); i++) {
        congloms[i] = conglomIDs.get(i);
    }
    /*
     * An index conglomerate id can be returned by the query before the main table one is,
    * but it should ALWAYS have a higher conglomerate id, so if we sort the congloms,
    * we should return the main table before any of its indices.
    */
    Arrays.sort(congloms);
    return congloms;
}

---

From source file:dao.CarryonSearchQuery.java

/**
 * This method lists all the results for the search text from directories
 * @param conn the connection//from  w  w  w. ja  v  a2  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 hdlogin.login, ownerid, entryid, carryontag.title as btitle, usertags, category, photos from hdlogin left join displayuser on hdlogin.loginid=displayuser.loginid left join carryontag on hdlogin.loginid=ownerid where category=1 and photos=1 and (");
    ArrayList columns = new ArrayList();
    columns.add("usertags");
    columns.add("carryontag.title");
    sb.append(sqlSearch.getConstraint(columns, sString));
    sb.append(")");

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

    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++) {
                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 executing CarryonSearchQuery " + sb.toString(), e);
    }
}

---

From source file:dao.CarryonSearchBizAwareQuery.java

/**
 * This method lists all the results for the search text from carryontag
 * @param conn the connection//w w w  .  j  a  va  2  s  .com
 * @param sString - search string
 * @param bid - bid
 * @return HashSet - result
 * @throws BaseDaoException - when error occurs
 **/
public HashSet run(Connection conn, String sString, String bid) throws BaseDaoException {

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

    StringBuffer sb = new StringBuffer(
            "select hdlogin.login, ownerid, entryid, title, usertags, hdlogin.bid, business.bsearch from business, hdlogin left join carryontag on hdlogin.loginid=ownerid where business.bid=hdlogin.bid and (");
    ArrayList columns = new ArrayList();
    columns.add("usertags");
    columns.add("title");
    sb.append(sqlSearch.getConstraint(columns, sString));
    sb.append(")");

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

    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++) {
                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 executing CarryonSearchBizAwareQuery " + sb.toString(), e);
    }
}

---

From source file:com.uit.anonymousidentity.Repository.IssuerKeys.IssuerJDBCTemplate.java

@Override
public Set<String> getAllSid() throws SQLException {
    String sql = "select " + SID + " from " + TABLE_NAME;
    PreparedStatement pst = dataSource.getConnection().prepareStatement(sql);
    ResultSet rs = pst.executeQuery();
    Set<String> ss = new HashSet<String>();
    if (rs.next()) {
        do {//from ww  w.ja va  2s  .  co  m
            ss.add(rs.getString(SID));
        } while (rs.next());
        return ss;
    } else {
        return null;
    }
}

---

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