Example usage for org.apache.commons.dbutils QueryRunner QueryRunner

Introduction

In this page you can find the example usage for org.apache.commons.dbutils QueryRunner QueryRunner.

Prototype

public QueryRunner(DataSource ds) 

Source Link

Document

Constructor for QueryRunner that takes a DataSource to use.

Usage

From source file:com.pinterest.deployservice.db.DBEnvironDAOImpl.java

@Override
public List<String> getTotalCapacityHosts(String envId, String envName, String envStage) throws Exception {
    List<String> totalHosts = new QueryRunner(dataSource).query(GET_HOSTS_BY_CAPACITY,
            SingleResultSetHandlerFactory.<String>newListObjectHandler(), envId, envId);
    List<String> overrideHosts = getOverrideHosts(envId, envName, envStage);
    if (!overrideHosts.isEmpty()) {
        totalHosts.removeAll(new HashSet<String>(overrideHosts));
    }/*from  w ww.  j  ava2s.c om*/
    return totalHosts;
}

---

From source file:com.pinterest.deployservice.db.DBDeployDAOImpl.java

@Override
public int updateStateSafely(String deployId, String currentState, DeployBean updateBean) throws Exception {
    SetClause setClause = updateBean.genSetClause();
    String clause = String.format(UPDATE_DEPLOY_SAFELY_TEMPLATE, setClause.getClause());
    setClause.addValue(deployId);/*w w w.ja va  2 s  . c  o m*/
    setClause.addValue(currentState);
    return new QueryRunner(dataSource).update(clause, setClause.getValueArray());
}

---

From source file:com.pinterest.deployservice.db.DBBuildDAOImpl.java

@Override
public List<String> getAllBuildNames() throws Exception {
    QueryRunner run = new QueryRunner(this.dataSource);
    return run.query(GET_ALL_BUILD_NAMES, SingleResultSetHandlerFactory.<String>newListObjectHandler());
}

---

From source file:com.pinterest.deployservice.db.DBAgentDAOImpl.java

@Override
public long countAgentByEnv(String envId) throws Exception {
    Long n = new QueryRunner(dataSource).query(GET_TOTAL,
            SingleResultSetHandlerFactory.<Long>newObjectHandler(), envId);
    return n == null ? 0 : n;
}

---

From source file:de.iritgo.aktario.jdbc.LoadObject.java

/**
 * Load a list of iobjects./*w  w  w. j  a  v a  2 s.c  o m*/
 *
 * @param dataSource The data source to load from.
 * @param owner The owner of the list.
 * @param list the object list to load.
 */
public void loadList(final DataSource dataSource, DataObject owner, final IObjectList list) {
    try {
        QueryRunner query = new QueryRunner(dataSource);

        query.query("select elemType, elemId from IritgoObjectList where id=? and attribute=?",
                new Object[] { new Long(owner.getUniqueId()), list.getAttributeName() },
                new ResultSetHandler() {
                    public Object handle(ResultSet rs) throws SQLException {
                        while (rs.next()) {
                            DataObject element = load(dataSource, rs.getString("elemType"),
                                    rs.getLong("elemId"));

                            if (element != null) {
                                list.add(element);
                            }
                        }

                        return null;
                    }
                });
    } catch (SQLException x) {
        Log.logError("persist", "LoadObject", "Error while loading the object list "
                + list.getOwner().getTypeId() + "." + list.getAttributeName() + ": " + x);
    }
}

---

From source file:com.pinterest.deployservice.db.DBHostDAOImpl.java

@Override
public List<HostBean> getHosts(String hostName) throws Exception {
    ResultSetHandler<List<HostBean>> h = new BeanListHandler<>(HostBean.class);
    return new QueryRunner(dataSource).query(GET_HOST_BY_NAME, h, hostName);
}

---

From source file:com.pinterest.deployservice.db.DBDeployDAOImpl.java

@Override
public long countDeploysByEnvId(String envId) throws Exception {
    Long n = new QueryRunner(dataSource).query(COUNT_TOTAL_BY_ENVID,
            SingleResultSetHandlerFactory.<Long>newObjectHandler(), envId);
    return n == null ? 0 : n;
}

---

From source file:azkaban.migration.scheduler.JdbcScheduleLoader.java

public void insertSchedule(Schedule s, EncodingType encType) throws ScheduleManagerException {

    String json = JSONUtils.toJSON(s.optionsToObject());
    byte[] data = null;
    try {// ww  w . j  a  v a  2  s  .  com
        byte[] stringData = json.getBytes("UTF-8");
        data = stringData;

        if (encType == EncodingType.GZIP) {
            data = GZIPUtils.gzipBytes(stringData);
        }
        logger.debug("NumChars: " + json.length() + " UTF-8:" + stringData.length + " Gzip:" + data.length);
    } catch (IOException e) {
        throw new ScheduleManagerException("Error encoding the schedule options. " + s.getScheduleName());
    }

    QueryRunner runner = new QueryRunner(dataSource);
    try {
        int inserts = runner.update(INSERT_SCHEDULE, s.getProjectId(), s.getProjectName(), s.getFlowName(),
                s.getStatus(), s.getFirstSchedTime(), s.getTimezone().getID(),
                Schedule.createPeriodString(s.getPeriod()), s.getLastModifyTime(), s.getNextExecTime(),
                s.getSubmitTime(), s.getSubmitUser(), encType.getNumVal(), data);
        if (inserts == 0) {
            throw new ScheduleManagerException("No schedule has been inserted.");
        }
    } catch (SQLException e) {
        logger.error(INSERT_SCHEDULE + " failed.");
        throw new ScheduleManagerException("Insert schedule " + s.getScheduleName() + " into db failed. ", e);
    }
}

---

From source file:com.pinterest.deployservice.db.DBBuildDAOImpl.java

@Override
public long countBuildsByName(String buildName) throws Exception {
    Long n = new QueryRunner(dataSource).query(GET_TOTAL_BY_NAME,
            SingleResultSetHandlerFactory.<Long>newObjectHandler(), buildName);
    return n == null ? 0 : n;
}

---

From source file:com.iana.boesc.dao.BOESCDaoImpl.java

@Override
public boolean getPopulatedDataAndInsert(EDI322Bean eb, String boescUserId, String userType, File file,
        List<String> finalErrorList, Map<Integer, Object> fileTransStatus) throws Exception {
    logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId
            + " userType ::" + userType);
    String status = "";
    logger.info("----- ISA  ............");
    String gsHeader = "";
    String gsControl = "";
    String st_control = "";
    String n7Header = "";
    String q5Header = "";
    String equip_prefix = "";
    String equip_number = "";
    String w2Header = "";
    String r4Header = "";
    String r13Header = "";
    String n9Header = "";
    String eventType = "";
    String port_qual = "";
    String iana_splc = "";

    QueryRunner qrun = new QueryRunner(getDataSource());
    logger.info("----- GE  ............");
    for (int i = 0; i < eb.getListGSDetails().size(); i++) {
        gsHeader = eb.getListGSDetails().get(i).getHeaderDetails();
        gsControl = eb.getListGSDetails().get(i).getGroupControlNumber();
        logger.info("gsControl ::" + gsControl + " gsHeader ::" + gsHeader);
        int startIndex = i + 1;
        logger.info("----- ST & SE  ............");
        for (int a = 0; a < eb.getSTDetailsMap().get(startIndex).size(); a++) {

            Connection conn = getConnection();
            conn.setAutoCommit(false);/*from w w w. ja  v a2  s.  c om*/
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            StringBuilder sbQuery = new StringBuilder(
                    "INSERT INTO BOESC_TRAN_SET (ISA_HEADER, GS_HEADER, INPUT_TYPE, SENDER_ID, SENDER_TYPE, ");
            sbQuery.append(
                    " ISA_DATETIME, GS_CONTROL, ST_CONTROL, EVENT_TYPE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, IEP_DOT, PORT_QUAL, IANA_SPLC, ");
            sbQuery.append(" POOL_ID, POOL_NAME, Q5_SEG, N7_SEG, W2_SEG, R4_SEG, N9_SEG, R13_SEG, ");

            if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) {
                sbQuery.append(" RECEIVER_ID, REC_STATUS   ");
            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) {
                sbQuery.append(" MRV_ID, MRV_STATUS   ");
            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) {
                sbQuery.append(" FO_ID, FO_STATUS ");

            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_SU)) {

            }

            try {
                status = "";
                int changedIndex = a + 1; //very important Variable
                if (fileTransStatus != null && fileTransStatus.size() > 0) {
                    logger.info("-------------------- changedIndex ::" + changedIndex
                            + " fileTransStatus.get(startIndex)  ::" + fileTransStatus.get(changedIndex));
                    if (fileTransStatus.get(changedIndex) == null) {
                        status = GlobalVariables.STATUS_PENDING;
                    } else {
                        status = GlobalVariables.STATUS_REJECTED;
                    }
                } else {
                    status = GlobalVariables.STATUS_PENDING;
                }

                r13Header = "";
                r4Header = "";
                n9Header = "";
                port_qual = "";
                iana_splc = "";
                GIERInfoDetails gierInfo = null;

                st_control = eb.getSTDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber();
                logger.info("  st_control :" + st_control);

                /*String transactionControlNumberSE  = eb.getSEDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber();
                logger.info("  transactionControlNumberSE :"+transactionControlNumberSE );*/

                logger.info("----- N7  ............");

                for (int q = 0; q < eb.getN7DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    n7Header = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    logger.info("n7Header ::" + n7Header);
                    equip_prefix = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getEquipmentInitial();
                    equip_number = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getEquipmentNumber();

                    logger.info("equip_prefix ::" + equip_prefix);
                    logger.info("equip_number ::" + equip_number);
                    equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? ""
                            : equip_prefix;
                    equip_number = equip_number == null || equip_number.trim().length() == 0 ? ""
                            : equip_number;
                    gierInfo = getDVIRAdditionaldetails(equip_prefix, equip_number);
                    //logger.info("gierInfo ::"+gierInfo);

                }

                logger.info("----- Q5  ............");
                for (int q = 0; q < eb.getQ5DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    q5Header = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    eventType = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getStatusCode();
                    logger.info("q5Header ::" + q5Header + " eventType ::" + eventType);
                }

                logger.info("----- W2  ............");
                for (int q = 0; q < eb.getW2DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    w2Header = eb.getW2DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    logger.info("w2Header ::" + w2Header);
                }

                logger.info("----- R4  ............");
                String tempR4Header = "";
                String tempPort_qual = "";
                String tempIana_splc = "";
                for (int q = 0; q < eb.getR4DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempR4Header = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    tempPort_qual = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getLocationQualifier();
                    tempIana_splc = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getLocationIdentifier();
                    r4Header = r4Header + GlobalVariables.FIELD_SEPARATOR + tempR4Header;
                    port_qual = port_qual + GlobalVariables.FIELD_SEPARATOR + tempPort_qual;
                    iana_splc = iana_splc + GlobalVariables.FIELD_SEPARATOR + tempIana_splc;
                    logger.info("r4Header ::" + r4Header + " port_qual:: " + port_qual + " iana_splc ::"
                            + iana_splc);

                }
                r4Header = r4Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r4Header.substring(1)
                        : r4Header;
                port_qual = port_qual.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? port_qual.substring(1)
                        : port_qual;
                iana_splc = iana_splc.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? iana_splc.substring(1)
                        : iana_splc;

                logger.info("----- R13  ............");
                String tempR13Header = "";
                for (int q = 0; q < eb.getR13DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempR13Header = eb.getR13DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    r13Header = r13Header + GlobalVariables.FIELD_SEPARATOR + tempR13Header;
                    logger.info("r13Header ::" + r13Header);
                }
                r13Header = r13Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? r13Header.substring(1)
                        : r13Header;

                logger.info("----- N9  ............");
                String tempN9Header = "";
                for (int q = 0; q < eb.getN9DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempN9Header = eb.getN9DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    n9Header = n9Header + GlobalVariables.FIELD_SEPARATOR + tempN9Header;
                    logger.info("n9Header ::" + n9Header);
                }
                n9Header = n9Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? n9Header.substring(1)
                        : n9Header;

                sbQuery.append(
                        " , CREATED_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

                if (gierInfo == null) {
                    gierInfo = new GIERInfoDetails(); //this situation happen when all segment are missing except : ISA,SE,ST,GE,GS,IEA
                }

                equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix;
                equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number;

                pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, eb.getISADetails().getHeaderDetails());
                pstmt.setString(2, gsHeader);
                pstmt.setString(3, GlobalVariables.INPUT_TYPE_BOESC_322);
                pstmt.setString(4, eb.getISADetails().getInterchangeSenderId());
                pstmt.setString(5, userType);
                pstmt.setString(6, eb.getISADetails().getInterchangeDate());
                pstmt.setString(7, gsControl);
                pstmt.setString(8, st_control);
                pstmt.setString(9, eventType);
                pstmt.setString(10, equip_prefix);
                pstmt.setString(11, equip_number);
                pstmt.setString(12, equip_prefix + equip_number);
                pstmt.setString(13, gierInfo.getCompanySCACCode() == null ? "" : gierInfo.getCompanySCACCode());
                pstmt.setString(14, gierInfo.getUsDotNumber() == null ? "" : gierInfo.getUsDotNumber());
                pstmt.setString(15, port_qual);
                pstmt.setString(16, iana_splc);
                pstmt.setString(17, gierInfo.getChassisPoolId() == null ? "" : gierInfo.getChassisPoolId());
                pstmt.setString(18, gierInfo.getChassisPoolName() == null ? "" : gierInfo.getChassisPoolName());
                pstmt.setString(19, q5Header);
                pstmt.setString(20, n7Header);
                pstmt.setString(21, w2Header);
                pstmt.setString(22, r4Header);
                pstmt.setString(23, n9Header);
                pstmt.setString(24, r13Header);
                pstmt.setString(25, boescUserId);
                pstmt.setString(26, status);
                pstmt.setObject(27, DateTimeFormater.getSqlSysTimestamp());

                logger.info("query :: " + sbQuery.toString());
                int dbStat = 0;
                int boescKey = 0;
                dbStat = pstmt.executeUpdate();
                rs = pstmt.getGeneratedKeys();
                if (dbStat != 0) {
                    if (rs != null) {
                        while (rs.next()) {
                            boescKey = rs.getInt(1);
                            logger.info("boescKey: " + boescKey);
                        }
                    }

                    conn.commit();
                } else {
                    conn.rollback();
                }
                if (boescKey != 0) {
                    //Update BOESC_UNIQUE_NO : using business logic
                    String sql = "UPDATE BOESC_TRAN_SET SET BOESC_UNIQUE_NO = ? WHERE BOESC_TRAN_ID = ? ";
                    qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(boescKey, "BOESC-"), boescKey });
                    logger.info("Record Inserted successfully for BOESC..." + file.getName());
                    return true;
                } else {
                    logger.error("Failure Data insertion in BOESC..");
                }
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (pstmt != null) {
                        pstmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException ex1) {
                    logger.error("Caught SQL exception while closing prepared statement /resultset "
                            + ex1.getMessage());
                    ex1.printStackTrace();
                    throw ex1;
                } catch (Exception e) {
                    logger.error("Caught SQL exception in finally block " + e.getMessage());
                    e.printStackTrace();
                    throw e;
                }
            }
        }
    }

    return false;
}

---

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