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.sf.ddao.ops.SelectSqlOperation.java

public Object exec(Context context, Object[] args) throws StatementFactoryException, SQLException {
    PreparedStatement preparedStatement = statementFactory.createStatement(context, false);
    ResultSet resultSet = preparedStatement.executeQuery();
    RSMapper RSMapper = rsMapperFactory.getInstance(args, resultSet);
    final Object res = RSMapper.handle(context, resultSet);
    resultSet.close();/* w  w  w  .jav a2s. com*/
    preparedStatement.close();
    return res;
}

From source file:com.wso2telco.dbUtil.DataBaseConnectUtils.java

/**
 * Get SP related configurations/* w  w  w.  j a v  a  2  s  .  c om*/
 *
 * @param correlationId
 * @return
 * @throws ConfigurationException
 * @throws CommonAuthenticatorException
 */
public static BackChannelRequestDetails getRequestDetailsById(String correlationId)
        throws ConfigurationException, CommonAuthenticatorException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    BackChannelRequestDetails backchannelRequestDetails = null;
    ResultSet resultSet = null;

    String getUserDetailsQuery = "select * FROM backchannel_request_details where correlation_id=?";

    try {
        connection = getConnectDBConnection();

        if (log.isDebugEnabled()) {
            log.debug("Executing the query " + getUserDetailsQuery);
        }

        preparedStatement = connection.prepareStatement(getUserDetailsQuery);
        preparedStatement.setString(1, correlationId);
        resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            backchannelRequestDetails = new BackChannelRequestDetails();
            backchannelRequestDetails.setSessionId(resultSet.getString("session_id"));
            backchannelRequestDetails.setAuthCode(resultSet.getString("auth_code"));
            backchannelRequestDetails.setCorrelationId(resultSet.getString("correlation_id"));
            backchannelRequestDetails.setMsisdn(resultSet.getString("msisdn"));
            backchannelRequestDetails
                    .setNotificationBearerToken(resultSet.getString("notification_bearer_token"));
            backchannelRequestDetails.setNotificationUrl(resultSet.getString("notification_url"));
            backchannelRequestDetails.setClientId(resultSet.getString("client_id"));
        }
    } catch (SQLException e) {
        handleException(
                "Error occurred while fetching SP related data for the Correlation Id: " + correlationId, e);
    } catch (NamingException e) {
        throw new ConfigurationException("DataSource could not be found in mobile-connect.xml");
    } finally {
        closeAllConnections(preparedStatement, connection, resultSet);
    }

    return backchannelRequestDetails;
}

From source file:dao.PblogTagsUserQuery.java

public HashSet run(Connection conn, String sString) throws BaseDaoException {

    if ((RegexStrUtil.isNull(sString) || conn == null)) {
        return null;
    }//from   w  ww  .j  a va 2s .  c  om

    ResultSet rs = null;
    StringBuffer sb = new StringBuffer("select distinct usertags from pblogtags where ");
    ArrayList columns = new ArrayList();
    columns.add("ownerid");
    sb.append(sqlSearch.getConstraint(columns, sString));
    //logger.info("PblogTagsUserQuery = " + sb.toString());

    try {
        PreparedStatement stmt = conn.prepareStatement(sb.toString());
        rs = stmt.executeQuery();
        Vector columnNames = null;
        Blog blog = null;
        HashSet pendingSet = new HashSet();

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

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

From source file:dao.DirectoryListUsersQuery.java

/** 
  * This method lists all users for a directory
  * @param conn the connection//w ww.  j  a v a  2s  .  c  om
  * @param directoryId the directory id
  * @return HashSet the set that has the list of users for this directory.
  * @throws BaseDaoException
  * @author Smitha Gudur (smitha@redbasin.com)
  * @version $Revision: 1.1 $
  */

/* Uses tables - directory, dirallow, hdlogin  */
public HashSet run(Connection conn, String directoryid) throws BaseDaoException {

    String sqlQuery = "select distinct CONCAT(hd.fname,' ',hd.lname) AS membername, "
            + "hd.login, hd.loginid, d1.directoryid, d1.dirname from directory d1, "
            + "dirallow d2, hdlogin hd where d2.loginid=hd.loginid "
            + "and d1.directoryid=d2.directoryid and d1.directoryid=" + directoryid + "";
    try {
        PreparedStatement stmt = conn.prepareStatement(sqlQuery);
        ResultSet rs = stmt.executeQuery();
        Vector columnNames = null;
        Directory directory = null;
        HashSet dirSet = 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++) {
                directory.setValue((String) columnNames.elementAt(j),
                        (String) rs.getString((String) columnNames.elementAt(j)));
            }
            dirSet.add(directory);
        }
        return dirSet;
    } catch (Exception e) {
        throw new BaseDaoException("Error occured DirectoryListUsersQuery run query " + sqlQuery, e);
    }
}

From source file:dao.MykeywordsUserQuery.java

public HashSet run(Connection conn, String sString) throws BaseDaoException {

    if ((RegexStrUtil.isNull(sString) || conn == null)) {
        return null;
    }//from   www.java2s.c om

    ResultSet rs = null;

    StringBuffer sb = new StringBuffer("select distinct keyword from mykeywords where ");

    ArrayList columns = new ArrayList();
    columns.add("loginid");
    sb.append(sqlSearch.getConstraint(columns, sString));
    logger.info("MykeywordUserQuery = " + sb.toString());

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

        Vector columnNames = null;
        Mykeywords mykeyword = null;
        HashSet pendingSet = new HashSet();

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

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

From source file:com.wso2telco.dep.verificationhandler.verifier.DatabaseUtils.java

/**
 * Gets the subscription id.//from  w ww  .  ja v a  2  s. c om
 *
 * @param apiID the api id
 * @param applicationID the application id
 * @return the subscription id
 * @throws NamingException the naming exception
 * @throws SQLException the SQL exception
 */

/*
    public int findSubscriptionId(String appId, String apiId) throws
    Exception {
        
String sql = SQLConstants.GET_SUBSCRIPTION_ID_FOR_API_AND_APP_SQL;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_DB);
    ps = conn.prepareStatement(sql);
    ps.setInt(1, Integer.parseInt(apiId));
    ps.setInt(2, Integer.parseInt(appId));
        
    rs = ps.executeQuery();
    while (rs.next()) {
        return rs.getInt("SUBSCRIPTION_ID");
    }
} catch (SQLException e) {
    System.out.println(e.toString());
    throw e;
} finally {
    DbUtils.closeAllConnections(ps, conn, rs);
        
}
throw new Exception(
        "No record found in table AM_SUBSCRIPTION for APPLICATION_ID = " + appId + " and API_ID = " + apiId);
    }*/

public static int getSubscriptionId(String apiID, String applicationID) throws NamingException, SQLException {

    String subscriptionId = null;

    // String sql = "select * from am_subscription";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {

        String sql = VerificationConstants.GET_SUBSCRIPTION_ID_FOR_API_AND_APP_SQL;

        conn = getAMDBConnection();
        ps = conn.prepareStatement(sql);

        ps.setInt(1, Integer.parseInt(apiID));
        ps.setInt(2, Integer.parseInt(applicationID));
        rs = ps.executeQuery();

        while (rs.next()) {
            return rs.getInt("SUBSCRIPTION_ID");
        }

    } catch (SQLException e) {
        log.error("Error occured while writing southbound record.", e);
        throw e;
    } catch (NamingException e) {
        log.error("Error while finding the Datasource.", e);
        throw e;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
    }

    return -1;
}

From source file:dao.DirectoryListAuthorQuery.java

/** 
  * This method lists all the authors for a directory
  * @param conn the connection//from www  .j  a  va2  s  .c  o  m
  * @param directoryId the directory id
  * @return HashSet the set that has the list of authors for this directory.
  * @throws BaseDaoException
  * @author Smitha Gudur (smitha@redbasin.com)
  * @version $Revision: 1.1 $
  */

/* Uses tables - directory, diradmin, hdlogin  */
public HashSet run(Connection conn, String directoryid) throws BaseDaoException {

    String sqlQuery = "select distinct CONCAT(hd.fname,' ',hd.lname) AS membername, "
            + "hd.login, hd.loginid, d1.directoryid, d1.dirname from directory d1, "
            + "diradmin d2, hdlogin hd where d2.ownerid=hd.loginid "
            + "and d1.directoryid=d2.directoryid and d1.directoryid=" + directoryid + " order by hd.login ASC";
    logger.info("sqlQuery = " + sqlQuery);
    try {
        PreparedStatement stmt = conn.prepareStatement(sqlQuery);
        ResultSet rs = stmt.executeQuery();
        Vector columnNames = null;
        Directory directory = null;
        HashSet dirSet = new HashSet();

        if (rs != null) {
            // get column names and types from rsmd and save to local array in object
            //DbUtils dbutils = new DbUtils();
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }

        while (rs.next()) {
            directory = (Directory) eop.newObject(DbConstants.DIRECTORY);
            for (int j = 0; j < columnNames.size(); j++) {
                directory.setValue((String) columnNames.elementAt(j),
                        (String) rs.getString((String) columnNames.elementAt(j)));
            }
            dirSet.add(directory);
        }
        return dirSet;
    } catch (Exception e) {
        throw new BaseDaoException(
                "Error occured while executing directory moderatorslist run query " + sqlQuery, e);
    }
}

From source file:com.autentia.tnt.bill.migration.support.OriginalInformationRecoverer.java

/**
 * Recupera la suma total de todos los conceptos de todas las facturas del tipo que se envie por parametro
 * @param billType tipo de factura//from   w  w  w .j  ava  2  s.c  o  m
 */
public static double getTotalFacturasOriginal(String billType) throws Exception {

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    double result = -1;

    try {
        log.info("RECOVERING TOTAL 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= ?";

        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, billType);

        rs = pstmt.executeQuery();

        while (rs.next()) {
            result = rs.getDouble(1);
            log.info("\t" + result);
        }

        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:com.senior.g40.service.AccidentService.java

public List<Accident> getAllAccidents() {
    List<Accident> accidents = null;
    Accident accident = null;// w ww.  j  a  v a  2s.c  om
    try {
        Connection conn = ConnectionBuilder.getConnection();
        String sqlCmd = "SELECT * FROM `accident`;";
        PreparedStatement pstm = conn.prepareStatement(sqlCmd);
        ResultSet rs = pstm.executeQuery();
        while (rs.next()) {
            accident = new Accident();
            if (accidents == null) {
                accidents = new ArrayList<Accident>();
            }
            setAccident(rs, accident);
            accidents.add(accident);
        }
        return accidents;
    } catch (SQLException ex) {
        Logger.getLogger(AccidentService.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;
}

From source file:dao.SearchInTagsQuery.java

/**
 * This method lists all the results for the search text from directories
 * @param conn the connection/*from w  w w .  j  av a  2  s  . c  o  m*/
 * @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 * from tags where ");

    ArrayList columns = new ArrayList();
    columns.add("tag");
    sb.append(sqlSearch.getConstraint(columns, sString));
    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);
    }
}