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.hangum.tadpole.db.bander.cubrid.CubridExecutePlanUtils.java

/**
 * cubrid execute plan/*from  w  ww .ja va 2 s . c o m*/
 * 
 * @param userDB
 * @param sql
 * @return
 * @throws Exception
 */
public static String plan(UserDBDAO userDB, String sql) throws Exception {
    if (!sql.toLowerCase().startsWith("select")) {
        logger.error("[cubrid execute plan ]" + sql);
        throw new Exception("This statment not select. please check.");
    }
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;

    try {
        //         Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
        //         conn = DriverManager.getConnection(userDB.getUrl(), userDB.getUsers(), userDB.getPasswd());
        //         conn.setAutoCommit(false); //     auto commit? false  .
        conn = TadpoleSQLManager.getInstance(userDB).getDataSource().getConnection();
        conn.setAutoCommit(false); //     auto commit? false  .

        sql = StringUtils.trim(sql).substring(6);
        if (logger.isDebugEnabled())
            logger.debug("[qubrid modifying query]" + sql);
        sql = "select " + RECOMPILE + sql;

        pstmt = conn.prepareStatement(sql);
        ((CUBRIDStatement) pstmt).setQueryInfo(true);
        rs = pstmt.executeQuery();

        String plan = ((CUBRIDStatement) pstmt).getQueryplan(); //  ?    .
        //         conn.commit();

        if (logger.isDebugEnabled())
            logger.debug("cubrid plan text : " + plan);

        return plan;

    } finally {
        if (rs != null)
            rs.close();
        if (pstmt != null)
            pstmt.close();
        if (conn != null)
            conn.close();
    }
}

From source file:com.autentia.tnt.bill.migration.support.MigratedInformationRecoverer.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  ww.j av a  2  s  .  c  o  m*/
 */
public static double getTotalFacturasMigrated(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 + " MIGRADAS");

        // 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 es nula               

        String sql = "SELECT sum(bp.amount) FROM BillPayment bp, Bill b where bp.billId = b.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);
        }

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

/**
 * Recupera la fecha de pago o cobro de cada una de las facturas cuyo tipo se envia por parametro
 * @param billType tipo de factura/*w ww .j av  a 2s.co  m*/
 */
public static Date[] getFechaFacturaOriginal(String billType) throws Exception {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    Date[] result = new Date[0];

    try {
        log.info("RECOVERING FECHAS " + 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 vacia
        String sql = "SELECT date_add(creationDate, INTERVAL expiration DAY) as date FROM Bill B where billType = ? order by date";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, billType);
        rs = pstmt.executeQuery();

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

        while (rs.next()) {
            result[counter] = rs.getDate(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:com.att.pirates.controller.GlobalDataController.java

private static LinkedList<Company> getCummulativeNotesForPrismId(String prismId) {

    LinkedList<Company> notes = new LinkedList<Company>();
    ResultSet rs = null;/* w  ww.j a v a 2 s  . c o  m*/
    Connection conn = null;
    PreparedStatement preparedStatement = null;

    try {
        conn = DBUtility.getDBConnection();
        // SQL query command
        String SQL = " SELECT NoteId, ApplicationName " + "  ,n.Notes " + "  ,n.DateCreated " + "  ,e.FullName "
                + "  FROM CummulativeNotes n Join ATT_Employees e " + "  On e.UUID = n.CreatedBy "
                + "  where n.PRISMId = ? " + "  Order By n.DateCreated Desc ";

        preparedStatement = conn.prepareStatement(SQL);
        preparedStatement.setString(1, prismId);
        rs = preparedStatement.executeQuery();

        while (rs.next()) {
            String AppName = rs.getString("ApplicationName");
            String note = rs.getString("Notes");
            String dateCreated = rs.getString("DateCreated");
            String createdBy = rs.getString("FullName");
            int noteId = rs.getInt("NoteId");

            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat yFormat = new SimpleDateFormat("MM/dd/yyyy");
            dateCreated = yFormat.format(format.parse(dateCreated));
            String rowId = "row_" + Integer.toString(noteId);
            String edit = "<a href='#' class='glyphicon glyphicon-pencil' data-toggle='modal' data-target='#updateNoteModal' onclick='return NoteRowButtonClickedPerNote(this);' id='btnNoteEdit_"
                    + Integer.toString(noteId) + "' ></a> ";

            // Company p = new Company(Integer.toString(noteId),AppName,note,createdBy,dateCreated, rowId);
            Company p = new Company(rowId, AppName, note, createdBy, dateCreated, rowId, edit);
            notes.add(p);
        }
    } catch (SQLException e) {
        logger.error(e.getMessage());
    } catch (Exception e) {
        logger.error(e.getMessage());
    } finally {
        try {
            if (rs != null)
                rs.close();
        } catch (Exception e) {
        }
        ;
        try {
            if (preparedStatement != null)
                preparedStatement.close();
        } catch (Exception e) {
        }
        ;
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
        ;
    }

    return notes;
}

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

/**
 * Recupera la fecha de pago o cobro de cada una de las facturas cuyo tipo se envia por parametro
 * @param billType tipo de factura//from   w w  w. j a  va  2  s . c  om
 */
public static Date[] getFechaFacturaMigrated(String billType) throws Exception {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    Date[] result = new Date[0];

    try {
        log.info("RECOVERING FECHAS " + billType + " MIGRADAS");

        // 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 bp.expirationDate FROM BillPayment bp, Bill b where bp.billId = b.id and b.billType = ? order by bp.expirationDate";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, billType);

        rs = pstmt.executeQuery();

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

        while (rs.next()) {
            result[counter] = rs.getDate(1);
            log.info("\t" + result[counter]);
            counter++;
        }

    } 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.hangum.tadpole.rdb.core.editors.main.utils.plan.CubridExecutePlanUtils.java

/**
 * cubrid execute plan/*from   w w  w . j  av a  2 s  . co  m*/
 * 
 * @param userDB
 * @param reqQuery
 * @return
 * @throws Exception
 */
public static String plan(UserDBDAO userDB, final RequestQuery reqQuery) throws Exception {
    String sql = reqQuery.getSql();
    if (!sql.toLowerCase().startsWith("select")) {
        logger.error("[cubrid execute plan ]" + sql);
        throw new Exception("This statment not select. please check.");
    }
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;

    try {
        conn = TadpoleSQLManager.getInstance(userDB).getDataSource().getConnection();
        conn.setAutoCommit(false); //     auto commit? false  .

        sql = StringUtils.trim(sql).substring(6);
        if (logger.isDebugEnabled())
            logger.debug("[qubrid modifying query]" + sql);
        sql = "select " + RECOMPILE + sql;

        pstmt = conn.prepareStatement(sql);
        if (reqQuery.getSqlStatementType() == SQL_STATEMENT_TYPE.PREPARED_STATEMENT) {
            final Object[] statementParameter = reqQuery.getStatementParameter();
            for (int i = 1; i <= statementParameter.length; i++) {
                pstmt.setObject(i, statementParameter[i - 1]);
            }
        }
        ((CUBRIDStatement) pstmt).setQueryInfo(true);
        rs = pstmt.executeQuery();

        String plan = ((CUBRIDStatement) pstmt).getQueryplan(); //  ?    .
        if (logger.isDebugEnabled())
            logger.debug("cubrid plan text : " + plan);

        return plan;
    } finally {
        if (rs != null)
            rs.close();
        if (pstmt != null)
            pstmt.close();
        if (conn != null)
            conn.close();
    }
}

From source file:com.autentia.tnt.bill.migration.support.MigratedInformationRecoverer.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  ava  2 s  . c  o  m
 */
public static double[] getImporteFacturaMigrated(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 + " MIGRADAS");

        // 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 bp.amount FROM BillPayment bp, Bill b where bp.billId = b.id and b.billType = ? order by amount";

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

        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++;
        }

    } 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.concursive.connect.web.modules.search.utils.SearchUtils.java

/**
 * Generates a list of projects that the user has access to
 *
 * @param db/*from  ww w.  j ava2s. c o m*/
 * @param userId
 * @param specificProjectId
 * @param specificCategoryId
 * @return
 * @throws SQLException
 */
public static String generateValidProjects(Connection db, int userId, int specificProjectId,
        int specificCategoryId) throws SQLException {
    if (userId < 1) {
        return "";
    }
    // @todo get ids from user cache
    // @update cache everytime a user is added or removed from a project team
    // get the projects for the user
    // get the project permissions for each project
    // if user has access to the data, then add to query
    StringBuffer projectList = new StringBuffer();
    PreparedStatement pst = db
            .prepareStatement("SELECT project_id " + "FROM project_team " + "WHERE user_id = ? "
                    + "AND status IS NULL " + (specificProjectId > -1 ? "AND project_id = ? " : "")
                    + (specificCategoryId > -1
                            ? "AND project_id IN (SELECT project_id FROM projects WHERE category_id = ?) "
                            : ""));
    int i = 0;
    pst.setInt(++i, userId);
    if (specificProjectId > -1) {
        pst.setInt(++i, specificProjectId);
    }
    if (specificCategoryId > -1) {
        pst.setInt(++i, specificCategoryId);
    }
    ResultSet rs = pst.executeQuery();
    while (rs.next()) {
        int projectId = rs.getInt("project_id");
        // these projects override the lower access projects
        if (projectList.length() > 0) {
            projectList.append(" OR ");
        }
        projectList.append(projectId);
    }
    rs.close();
    pst.close();
    return projectList.toString();
}

From source file:com.wso2telco.dep.reportingservice.dao.OperatorDAO.java

/**
 * Gets the approved operators by application.
 *
 * @param applicationId the application id
 * @param operator the operator//from  w  ww  .  jav  a  2 s  .  com
 * @return the approved operators by application
 */
public static String getApprovedOperatorsByApplication(int applicationId, String operator) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    String sql = "SELECT opco.operatorname FROM " + ReportingTable.OPERATORAPPS + " opcoApp INNER JOIN "
            + ReportingTable.OPERATORS
            + " opco ON opcoApp.operatorid = opco.id WHERE opcoApp.isactive = 1 AND opcoApp.applicationid = ? AND opco.operatorname like ?";

    String approvedOperators = "";

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        ps.setInt(1, applicationId);

        if (operator.equals("__ALL__")) {
            ps.setString(2, "%");
        } else {
            ps.setString(2, operator);
        }

        log.debug("getApprovedOperatorsByApplication");
        rs = ps.executeQuery();
        while (rs.next()) {
            String temp = rs.getString("operatorname");
            approvedOperators = approvedOperators + ", " + temp;
        }
    } catch (Exception e) {
        log.error("Error occured while getting approved operators of application from the database" + e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, rs);
    }
    if (approvedOperators == "") {
        approvedOperators = "NONE";
    } else {
        approvedOperators = approvedOperators.replaceFirst(",", "");
    }

    return approvedOperators;
}

From source file:com.silverpeas.gallery.dao.MediaDAO.java

/**
 * Adding all data of photos.//from ww  w. j  av  a2s .c o m
 * @param con
 * @param media
 * @param photos
 * @throws SQLException
 */
private static void decoratePhotos(final Connection con, List<Media> media, Map<String, Photo> photos)
        throws SQLException {
    if (!photos.isEmpty()) {
        Collection<Collection<String>> idGroups = CollectionUtil.split(new ArrayList<String>(photos.keySet()));
        StringBuilder queryBase = new StringBuilder(SELECT_INTERNAL_MEDIA_PREFIX)
                .append("P.resolutionW, P.resolutionH from SC_Gallery_Internal I join SC_Gallery_Photo P on I"
                        + ".mediaId = P.mediaId where I.mediaId in ");
        for (Collection<String> mediaIds : idGroups) {
            PreparedStatement prepStmt = null;
            ResultSet rs = null;
            try {
                prepStmt = con.prepareStatement(DBUtil.appendListOfParameters(queryBase, mediaIds).toString());
                DBUtil.setParameters(prepStmt, mediaIds);
                rs = prepStmt.executeQuery();
                while (rs.next()) {
                    String mediaId = rs.getString(1);
                    mediaIds.remove(mediaId);
                    Photo currentPhoto = photos.get(mediaId);
                    decorateInternalMedia(rs, currentPhoto);
                    currentPhoto.setDefinition(Definition.of(rs.getInt(8), rs.getInt(9)));
                }
            } finally {
                DBUtil.close(rs, prepStmt);
            }
            // Not found
            for (String mediaIdNotFound : mediaIds) {
                Photo currentPhoto = photos.remove(mediaIdNotFound);
                media.remove(currentPhoto);
                SilverTrace.warn(GalleryComponentSettings.COMPONENT_NAME, "MediaDAO.decoratePhotos()",
                        "root.MSG_GEN_PARAM_VALUE",
                        "photo not found (removed from result): " + mediaIdNotFound);
            }
        }
    }
}