Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

Introduction

In this page you can find the example usage for org.hibernate SQLQuery addScalar.

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:org.hil.core.dao.hibernate.ChildrenDaoHibernate.java

License:Open Source License

public List<RegionVaccinationReportData> getChildrenVaccinationReport(String timeFrom, String timeTo,
        Commune commune, District district) {

    List<RegionVaccinationReportData> statistics = new ArrayList<RegionVaccinationReportData>();

    String[] timeFromYM = timeFrom.split("/");
    String strTimeFrom = timeFromYM[1] + "-" + timeFromYM[0] + "-01 00:00:00";
    log.debug("From: " + strTimeFrom);

    Calendar calendar = Calendar.getInstance();
    String[] timeToYM = timeTo.split("/");
    int year = Integer.parseInt(timeToYM[1]);
    String strTimeFromY = year + "-01-01 00:00:00";
    calendar.set(year, Integer.parseInt(timeToYM[0]), 1);
    int maxDay = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
    String strTimeTo = year + "-" + timeToYM[0] + "-" + maxDay + " 23:59:59";
    log.debug("To: " + strTimeTo);

    String queryLocation = "";
    if (commune != null)
        queryLocation = " AND cm.id = :communeId";
    else if (district != null)
        queryLocation = " AND cm.id_district = :districtId GROUP BY cm.id ORDER BY cm.id ASC";

    String sql = this.buidlSQLforReport("commune", queryLocation, "strTimeFrom");
    log.debug(sql);//from w  w w  .  j ava 2  s  .  c o m

    SQLQuery qry = getSession().createSQLQuery(sql);
    if (commune != null)
        qry.setParameter("communeId", commune.getId());
    else if (district != null)
        qry.setParameter("districtId", district.getId());
    qry.setParameter("timeYear", year);
    qry.setParameter("strTimeFrom", strTimeFrom);
    qry.setParameter("strTimeTo", strTimeTo);

    qry.addScalar("regionName", Hibernate.STRING);
    qry.addScalar("childrenUnder1", Hibernate.INTEGER);
    qry.addScalar("VGBL24", Hibernate.INTEGER);
    qry.addScalar("VGBG24", Hibernate.INTEGER);
    qry.addScalar("BCG", Hibernate.INTEGER);
    qry.addScalar("DPT_VGB_Hib1", Hibernate.INTEGER);
    qry.addScalar("OPV1", Hibernate.INTEGER);
    qry.addScalar("DPT_VGB_Hib2", Hibernate.INTEGER);
    qry.addScalar("OPV2", Hibernate.INTEGER);
    qry.addScalar("DPT_VGB_Hib3", Hibernate.INTEGER);
    qry.addScalar("OPV3", Hibernate.INTEGER);
    qry.addScalar("measles1", Hibernate.INTEGER);
    qry.addScalar("eVGBL24", Hibernate.INTEGER);
    qry.addScalar("eVGBG24", Hibernate.INTEGER);
    qry.addScalar("eBCG", Hibernate.INTEGER);
    qry.addScalar("eDPT_VGB_Hib1", Hibernate.INTEGER);
    qry.addScalar("eOPV1", Hibernate.INTEGER);
    qry.addScalar("eDPT_VGB_Hib2", Hibernate.INTEGER);
    qry.addScalar("eOPV2", Hibernate.INTEGER);
    qry.addScalar("eDPT_VGB_Hib3", Hibernate.INTEGER);
    qry.addScalar("eOPV3", Hibernate.INTEGER);
    qry.addScalar("eMeasles1", Hibernate.INTEGER);
    qry.addScalar("protectedTetanusCases", Hibernate.INTEGER);
    qry.addScalar("reactionNormalCases", Hibernate.INTEGER);
    qry.addScalar("reactionSeriousCases", Hibernate.INTEGER);
    qry.addScalar("amountOfFinish", Hibernate.INTEGER);
    qry.setResultTransformer(Transformers.aliasToBean(RegionVaccinationReportData.class));

    statistics = qry.list();

    if (commune != null && statistics.size() > 0) {
        RegionVaccinationReportData same = new RegionVaccinationReportData();
        same.setAmountOfFinish(statistics.get(0).getAmountOfFinish());
        same.setAmountOfFinish(statistics.get(0).getAmountOfFinish());
        same.setBCG(statistics.get(0).getBCG());
        same.setChildrenUnder1(statistics.get(0).getChildrenUnder1());
        same.setDPT_VGB_Hib1(statistics.get(0).getDPT_VGB_Hib1());
        same.setDPT_VGB_Hib2(statistics.get(0).getDPT_VGB_Hib2());
        same.setDPT_VGB_Hib3(statistics.get(0).getDPT_VGB_Hib3());
        same.setMeasles1(statistics.get(0).getMeasles1());
        same.setOPV1(statistics.get(0).getOPV1());
        same.setOPV2(statistics.get(0).getOPV2());
        same.setOPV3(statistics.get(0).getOPV3());
        same.setVGBG24(statistics.get(0).getVGBG24());
        same.setVGBL24(statistics.get(0).getVGBL24());
        same.setProtectedTetanusCases(statistics.get(0).getProtectedTetanusCases());
        same.setReactionNormalCases(statistics.get(0).getReactionNormalCases());
        same.setReactionSeriousCases(statistics.get(0).getReactionSeriousCases());
        same.seteBCG(statistics.get(0).geteBCG());
        same.seteDPT_VGB_Hib1(statistics.get(0).geteDPT_VGB_Hib1());
        same.seteDPT_VGB_Hib2(statistics.get(0).geteDPT_VGB_Hib2());
        same.seteDPT_VGB_Hib3(statistics.get(0).geteDPT_VGB_Hib3());
        same.seteMeasles1(statistics.get(0).geteMeasles1());
        same.seteOPV1(statistics.get(0).geteOPV1());
        same.seteOPV2(statistics.get(0).geteOPV2());
        same.seteOPV3(statistics.get(0).geteOPV3());
        same.seteVGBG24(statistics.get(0).geteVGBG24());
        same.seteVGBL24(statistics.get(0).geteVGBL24());
        statistics.add(same);

        String sql2 = this.buidlSQLforReport("commune", queryLocation, "strTimeFromY");
        SQLQuery qry2 = getSession().createSQLQuery(sql2);
        qry2.setParameter("communeId", commune.getId());
        qry2.setParameter("timeYear", year);
        qry2.setParameter("strTimeTo", strTimeTo);
        qry2.setParameter("strTimeFromY", strTimeFromY);
        qry2.addScalar("regionName", Hibernate.STRING);
        qry2.addScalar("childrenUnder1", Hibernate.INTEGER);
        qry2.addScalar("VGBL24", Hibernate.INTEGER);
        qry2.addScalar("VGBG24", Hibernate.INTEGER);
        qry2.addScalar("BCG", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib1", Hibernate.INTEGER);
        qry2.addScalar("OPV1", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib2", Hibernate.INTEGER);
        qry2.addScalar("OPV2", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib3", Hibernate.INTEGER);
        qry2.addScalar("OPV3", Hibernate.INTEGER);
        qry2.addScalar("measles1", Hibernate.INTEGER);
        qry2.addScalar("eVGBL24", Hibernate.INTEGER);
        qry2.addScalar("eVGBG24", Hibernate.INTEGER);
        qry2.addScalar("eBCG", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib1", Hibernate.INTEGER);
        qry2.addScalar("eOPV1", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib2", Hibernate.INTEGER);
        qry2.addScalar("eOPV2", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib3", Hibernate.INTEGER);
        qry2.addScalar("eOPV3", Hibernate.INTEGER);
        qry2.addScalar("eMeasles1", Hibernate.INTEGER);
        qry2.addScalar("protectedTetanusCases", Hibernate.INTEGER);
        qry2.addScalar("reactionNormalCases", Hibernate.INTEGER);
        qry2.addScalar("reactionSeriousCases", Hibernate.INTEGER);
        qry2.addScalar("amountOfFinish", Hibernate.INTEGER);
        qry2.setResultTransformer(Transformers.aliasToBean(RegionVaccinationReportData.class));
        statistics.add((RegionVaccinationReportData) qry2.list().get(0));
    } else if (district != null && statistics.size() > 0) {
        String sql2 = this.buidlSQLforReport("district", queryLocation, "strTimeFrom");
        //log.debug(sql2);
        SQLQuery qry2 = getSession().createSQLQuery(sql2);
        qry2.setParameter("districtId", district.getId());
        qry2.setParameter("timeYear", year);
        qry2.setParameter("strTimeFrom", strTimeFrom);
        qry2.setParameter("strTimeTo", strTimeTo);

        qry2.addScalar("regionName", Hibernate.STRING);
        qry2.addScalar("childrenUnder1", Hibernate.INTEGER);
        qry2.addScalar("VGBL24", Hibernate.INTEGER);
        qry2.addScalar("VGBG24", Hibernate.INTEGER);
        qry2.addScalar("BCG", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib1", Hibernate.INTEGER);
        qry2.addScalar("OPV1", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib2", Hibernate.INTEGER);
        qry2.addScalar("OPV2", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib3", Hibernate.INTEGER);
        qry2.addScalar("OPV3", Hibernate.INTEGER);
        qry2.addScalar("measles1", Hibernate.INTEGER);
        qry2.addScalar("eVGBL24", Hibernate.INTEGER);
        qry2.addScalar("eVGBG24", Hibernate.INTEGER);
        qry2.addScalar("eBCG", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib1", Hibernate.INTEGER);
        qry2.addScalar("eOPV1", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib2", Hibernate.INTEGER);
        qry2.addScalar("eOPV2", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib3", Hibernate.INTEGER);
        qry2.addScalar("eOPV3", Hibernate.INTEGER);
        qry2.addScalar("eMeasles1", Hibernate.INTEGER);
        qry2.addScalar("protectedTetanusCases", Hibernate.INTEGER);
        qry2.addScalar("reactionNormalCases", Hibernate.INTEGER);
        qry2.addScalar("reactionSeriousCases", Hibernate.INTEGER);
        qry2.addScalar("amountOfFinish", Hibernate.INTEGER);
        qry2.setResultTransformer(Transformers.aliasToBean(RegionVaccinationReportData.class));
        statistics.add((RegionVaccinationReportData) qry2.list().get(0));

        String sql3 = this.buidlSQLforReport("district", queryLocation, "strTimeFromY");
        //log.debug(sql3);
        SQLQuery qry3 = getSession().createSQLQuery(sql3);
        qry3.setParameter("districtId", district.getId());
        qry3.setParameter("timeYear", year);
        qry3.setParameter("strTimeTo", strTimeTo);
        qry3.setParameter("strTimeFromY", strTimeFromY);
        qry3.addScalar("regionName", Hibernate.STRING);
        qry3.addScalar("childrenUnder1", Hibernate.INTEGER);
        qry3.addScalar("VGBL24", Hibernate.INTEGER);
        qry3.addScalar("VGBG24", Hibernate.INTEGER);
        qry3.addScalar("BCG", Hibernate.INTEGER);
        qry3.addScalar("DPT_VGB_Hib1", Hibernate.INTEGER);
        qry3.addScalar("OPV1", Hibernate.INTEGER);
        qry3.addScalar("DPT_VGB_Hib2", Hibernate.INTEGER);
        qry3.addScalar("OPV2", Hibernate.INTEGER);
        qry3.addScalar("DPT_VGB_Hib3", Hibernate.INTEGER);
        qry3.addScalar("OPV3", Hibernate.INTEGER);
        qry3.addScalar("measles1", Hibernate.INTEGER);
        qry3.addScalar("eVGBL24", Hibernate.INTEGER);
        qry3.addScalar("eVGBG24", Hibernate.INTEGER);
        qry3.addScalar("eBCG", Hibernate.INTEGER);
        qry3.addScalar("eDPT_VGB_Hib1", Hibernate.INTEGER);
        qry3.addScalar("eOPV1", Hibernate.INTEGER);
        qry3.addScalar("eDPT_VGB_Hib2", Hibernate.INTEGER);
        qry3.addScalar("eOPV2", Hibernate.INTEGER);
        qry3.addScalar("eDPT_VGB_Hib3", Hibernate.INTEGER);
        qry3.addScalar("eOPV3", Hibernate.INTEGER);
        qry3.addScalar("eMeasles1", Hibernate.INTEGER);
        qry3.addScalar("protectedTetanusCases", Hibernate.INTEGER);
        qry3.addScalar("reactionNormalCases", Hibernate.INTEGER);
        qry3.addScalar("reactionSeriousCases", Hibernate.INTEGER);
        qry3.addScalar("amountOfFinish", Hibernate.INTEGER);
        qry3.setResultTransformer(Transformers.aliasToBean(RegionVaccinationReportData.class));
        statistics.add((RegionVaccinationReportData) qry3.list().get(0));
    }

    log.debug("Report: " + statistics.size());
    return statistics;
}

From source file:org.hil.core.dao.hibernate.ChildrenDaoHibernate.java

License:Open Source License

public List<ChildrenPrintVO> searchChildrenForPrint(ChildrenSearchVO params) {
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
    String strDOBFrom = format.format(params.getDateOfBirthFrom());
    String strDOBTo = format.format(params.getDateOfBirthTo());

    String sql = "Select cvh.id_children as childId, c.full_name as fullName, c.date_of_birth as dateOfBirth, v.village_name as villageName, cm.commune_name as communeName,"
            + " c.child_code as childCode, c.mother_name as motherName, c.mother_birth_year as motherBirthYear, c.gender as gender, c.father_name as fatherName, c.father_birth_year as fatherBirthYear,"
            + " c.father_mobile as fatherMobile, c.mother_mobile as motherMobile, c.caretaker_name as caretakerName, c.caretaker_mobile as caretakerMobile, "
            + " group_concat(if(cvh.id_vaccination=1, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS VGB,"
            + " group_concat(if(cvh.id_vaccination=2, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS BCG,"
            + " group_concat(if(cvh.id_vaccination=3, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS DPT_VGB_Hib1,"
            + " group_concat(if(cvh.id_vaccination=4, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS OPV1,"
            + " group_concat(if(cvh.id_vaccination=5, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS DPT_VGB_Hib2,"
            + " group_concat(if(cvh.id_vaccination=6, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS OPV2,"
            + " group_concat(if(cvh.id_vaccination=7, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS DPT_VGB_Hib3,"
            + " group_concat(if(cvh.id_vaccination=8, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS OPV3,"
            + " group_concat(if(cvh.id_vaccination=9, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS measles1"
            + " FROM children c, children_vaccination_history cvh, village v, commune cm"
            + " where cvh.id_children=c.id and DATE(c.date_of_birth) >=:strDOBFrom and DATE(c.date_of_birth) <=:strDOBTo  and "
            + " c.id_village=v.id and v.id_commune=cm.id and cm.id=:communeId and (cvh.vaccinated=1 or cvh.vaccinated=0 or cvh.vaccinated=3)"
            + " group by cvh.id_children  order by c.id asc";

    SQLQuery qry = getSession().createSQLQuery(sql);
    qry.setParameter("communeId", params.getCommuneId());
    qry.setParameter("strDOBFrom", strDOBFrom);
    qry.setParameter("strDOBTo", strDOBTo);

    qry.addScalar("fullName", Hibernate.STRING);
    qry.addScalar("dateOfBirth", Hibernate.DATE);
    qry.addScalar("childCode", Hibernate.STRING);
    qry.addScalar("gender", Hibernate.BOOLEAN);
    qry.addScalar("villageName", Hibernate.STRING);
    qry.addScalar("communeName", Hibernate.STRING);
    qry.addScalar("fatherName", Hibernate.STRING);
    qry.addScalar("fatherBirthYear", Hibernate.INTEGER);
    qry.addScalar("fatherMobile", Hibernate.STRING);
    qry.addScalar("motherName", Hibernate.STRING);
    qry.addScalar("motherBirthYear", Hibernate.INTEGER);
    qry.addScalar("motherMobile", Hibernate.STRING);
    qry.addScalar("caretakerName", Hibernate.STRING);
    qry.addScalar("caretakerMobile", Hibernate.STRING);
    qry.addScalar("VGB", Hibernate.STRING);
    qry.addScalar("BCG", Hibernate.STRING);
    qry.addScalar("DPT_VGB_Hib1", Hibernate.STRING);
    qry.addScalar("OPV1", Hibernate.STRING);
    qry.addScalar("DPT_VGB_Hib2", Hibernate.STRING);
    qry.addScalar("OPV2", Hibernate.STRING);
    qry.addScalar("DPT_VGB_Hib3", Hibernate.STRING);
    qry.addScalar("OPV3", Hibernate.STRING);
    qry.addScalar("measles1", Hibernate.STRING);

    qry.setResultTransformer(Transformers.aliasToBean(ChildrenPrintVO.class));

    List<ChildrenPrintVO> list = qry.list();

    return list;/*  ww w. j av a 2s.co m*/
}

From source file:org.hil.core.dao.hibernate.ChildrenDaoHibernate.java

License:Open Source License

public List<ChildrenVaccinatedInLocationVO> getChildrenVaccinatedInLocationReport(String timeFrom,
        String timeTo, Commune commune, District district, Vaccination vaccine) {
    List<ChildrenVaccinatedInLocationVO> statistics = new ArrayList<ChildrenVaccinatedInLocationVO>();

    String[] timeFromYM = timeFrom.split("/");
    String strTimeFrom = timeFromYM[1] + "-" + timeFromYM[0] + "-01 00:00:00";
    log.debug("From: " + strTimeFrom);

    Calendar calendar = Calendar.getInstance();
    String[] timeToYM = timeTo.split("/");
    int year = Integer.parseInt(timeToYM[1]);
    String strTimeFromY = year + "-01-01 00:00:00";
    calendar.set(year, Integer.parseInt(timeToYM[0]), 1);
    int maxDay = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
    String strTimeTo = year + "-" + timeToYM[0] + "-" + maxDay + " 23:59:59";
    log.debug("To: " + strTimeTo);

    String sql = "";

    if (commune != null) {
        sql = "(SELECT c.id as id, cm.commune_name as communeName, v.village_name as villageName, c.child_code as childCode, "
                + " c.full_name as fullName, c.gender as gender, c.date_of_birth as dateOfBirth, c.mother_name as motherName, "
                + "   cvh.date_of_immunization as dateOfImmunization, cvh.other_vaccinated_location as otherLocation, null as vaccinatedCommune "
                + " FROM children_vaccination_history cvh, children c, village v, commune cm "
                + " WHERE cvh.id_vaccination=:vaccine AND cvh.vaccinated=1 AND cvh.other_vaccinated_location BETWEEN 1 AND 4 AND cvh.id_children=c.id "
                + " AND c.id_village=v.id AND v.id_commune=cm.id AND cm.id=:cId "
                + " AND DATE(cvh.date_of_immunization) BETWEEN :beginTime AND :endTime) " + " UNION ALL "
                + " (SELECT c.id as id, cm2.commune_name as communeName, v.village_name as villageName, c.child_code as childCode, "
                + " c.full_name as fullName, c.gender as gender, c.date_of_birth as dateOfBirth, c.mother_name as motherName, "
                + " cvh.date_of_immunization as dateOfImmunization, cvh.other_vaccinated_location as otherLocation, cm.commune_name as vaccinatedCommune "
                + " FROM children_vaccination_history cvh, children c, village v,commune cm, commune cm2 "
                + " WHERE cvh.id_vaccination=:vaccine AND cvh.vaccinated=1 AND cvh.id_vaccinated_location=cm.id AND cm.id=:cId "
                + " AND DATE(cvh.date_of_immunization) BETWEEN :beginTime AND :endTime "
                + " And cvh.id_children=c.id and v.id=c.id_village and cm2.id=v.id_commune) order by id";
    } else if (district != null) {
        sql = "(SELECT c.id as id, cm.commune_name as communeName, v.village_name as villageName, c.child_code as childCode, "
                + " c.full_name as fullName, c.gender as gender, c.date_of_birth as dateOfBirth, c.mother_name as motherName, "
                + " cvh.date_of_immunization as dateOfImmunization, cvh.other_vaccinated_location as otherLocation, null as vaccinatedCommune"
                + " FROM children_vaccination_history cvh, children c, village v, commune cm, district d "
                + " WHERE cvh.id_vaccination=:vaccine AND cvh.vaccinated=1 AND cvh.other_vaccinated_location BETWEEN 1 AND 4 AND cvh.id_children=c.id "
                + " AND c.id_village=v.id AND v.id_commune=cm.id AND cm.id_district=d.id AND d.id=:dId "
                + " AND DATE(cvh.date_of_immunization) BETWEEN :beginTime AND :endTime)" + " UNION ALL "
                + " (SELECT c.id as id, cm2.commune_name as communeName, v.village_name as villageName, c.child_code as childCode, "
                + " c.full_name as fullName, c.gender as gender, c.date_of_birth as dateOfBirth, c.mother_name as motherName, "
                + " cvh.date_of_immunization as dateOfImmunization, cvh.other_vaccinated_location as otherLocation, cm.commune_name as vaccinatedCommune "
                + " FROM children_vaccination_history cvh, children c, village v,commune cm, district d, commune cm2 "
                + " WHERE cvh.id_vaccination=:vaccine AND cvh.vaccinated=1 AND cvh.id_vaccinated_location=cm.id AND cm.id_district=d.id AND d.id=:dId"
                + " AND DATE(cvh.date_of_immunization) BETWEEN :beginTime AND :endTime "
                + " AND cvh.id_children=c.id and v.id=c.id_village and cm2.id=v.id_commune) ORDER BY id";
    }/*from  w  ww .ja va2s.  c om*/

    log.debug(sql);

    SQLQuery qry = getSession().createSQLQuery(sql);
    if (commune != null)
        qry.setParameter("cId", commune.getId());
    else if (district != null)
        qry.setParameter("dId", district.getId());
    qry.setParameter("vaccine", vaccine.getId());
    qry.setParameter("beginTime", strTimeFrom);
    qry.setParameter("endTime", strTimeTo);

    qry.addScalar("id", Hibernate.LONG);
    qry.addScalar("communeName", Hibernate.STRING);
    qry.addScalar("villageName", Hibernate.STRING);
    qry.addScalar("childCode", Hibernate.STRING);
    qry.addScalar("fullName", Hibernate.STRING);
    qry.addScalar("gender", Hibernate.BOOLEAN);
    qry.addScalar("dateOfBirth", Hibernate.DATE);
    qry.addScalar("motherName", Hibernate.STRING);
    qry.addScalar("dateOfImmunization", Hibernate.DATE);
    qry.addScalar("otherLocation", Hibernate.SHORT);
    qry.addScalar("vaccinatedCommune", Hibernate.STRING);

    qry.setResultTransformer(Transformers.aliasToBean(ChildrenVaccinatedInLocationVO.class));

    statistics = qry.list();

    log.debug("Report: " + statistics.size());

    return statistics;
}

From source file:org.hyperic.hq.appdef.server.session.AgentPluginStatusDAO.java

License:Open Source License

/**
 * Get our of sync plugins for agents, whose version is not older than that of
 * the server.//  ww w .  j  a  v a  2 s.c  om
 * @param agentId may be null
 * @return {@link List} of {@link Integer} which represents the AgentPluginStatusId
 */
@SuppressWarnings("unchecked")
private List<Integer> getOutOfSyncPlugins(Integer agentId) {
    String serverMajorVersion = serverConfigManager.getServerMajorVersion();
    final String agentSql = agentId == null ? "" : " s.agent_id = :agentId AND ";
    final String sql = new StringBuilder(256).append("select distinct s.id ")
            .append("from EAM_AGENT_PLUGIN_STATUS s ").append(LIMIT_S_TO_CURRENT_AGENTS).append(" AND (")
            .append(agentSql).append("not exists ( ").append("    select 1 ").append("    from EAM_PLUGIN p ")
            .append("    join EAM_AGENT_PLUGIN_STATUS st on p.md5 = st.md5 ")
            .append("    where st.agent_id = s.agent_id and s.md5 = st.md5 ").append("    and p.deleted = '0'")
            .append(") ").append("OR s.last_sync_status != :syncSuccess)").toString();
    final SQLQuery query = getSession().createSQLQuery(sql);
    if (agentId != null) {
        query.setParameter("agentId", agentId);
    }
    query.setParameter("serverVersion", serverMajorVersion);
    return query.addScalar("id", Hibernate.INTEGER)
            .setParameter("syncSuccess", AgentPluginStatusEnum.SYNC_SUCCESS.toString()).list();
}

From source file:org.hyperic.hq.appdef.server.session.AgentPluginStatusDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public Set<Integer> getAutoUpdatingAgentIDs() {
    String serverMajorVersion = serverConfigManager.getServerMajorVersion();
    final SQLQuery query = getSession().createSQLQuery(SYNCHABLE_AGENT_IDS_QUERY_STRING);
    query.setParameter("serverVersion", serverMajorVersion);
    final List<Integer> ids = query.addScalar("agent_id", Hibernate.INTEGER).list();
    final Set<Integer> idsSet = new HashSet<Integer>(ids);
    return idsSet;
}

From source file:org.hyperic.hq.appdef.server.session.AgentPluginStatusDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Agent> getCurrentNonSyncAgents() {
    String serverMajorVersion = serverConfigManager.getServerMajorVersion();
    final SQLQuery query = getSession().createSQLQuery(CUR_UNSYNCHABLE_AGENT_IDS_QUERY_STRING);
    query.setParameter("serverVersion", serverMajorVersion);
    final List<Integer> ids = query.addScalar("id", Hibernate.INTEGER).list();
    final Set<Integer> idsSet = new HashSet<Integer>(ids);

    final List<Agent> rtn = new ArrayList<Agent>(idsSet.size());
    for (final Integer agentId : idsSet) {
        rtn.add(agentDAO.findById(agentId));
    }/*from   w ww  .j a va2  s  .  c o  m*/
    return rtn;
}

From source file:org.jboss.seam.wiki.plugin.blog.BlogDAO.java

License:LGPL

public List<BlogEntry> findBlogEntriesInDirectory(WikiDirectory startDir, WikiDocument ignoreDoc, Pager pager,
        Integer year, Integer month, Integer day, String tag, boolean countComments) {

    final Map<Long, BlogEntry> blogEntryMap = new HashMap<Long, BlogEntry>();

    StringBuilder queryString = new StringBuilder();
    queryString.append("select").append(" ");
    for (int i = 0; i < getWikiDocumentSQLColumnNames().length; i++) {
        queryString.append(getWikiDocumentSQLColumnNames()[i]);
        if (i != getWikiDocumentSQLColumnNames().length - 1)
            queryString.append(", ");
    }/*from   ww w . j  a  va  2  s .  co  m*/
    queryString.append(", '0' as COMMENT_COUNT").append(" ");
    queryString.append(getblogEntryFromClause(tag));
    queryString.append(getBlogEntryWhereClause(ignoreDoc, year, month, day, tag));

    queryString.append(" ");
    queryString.append("order by doc2.CREATED_ON desc");

    SQLQuery query = getSession().createSQLQuery(queryString.toString());

    bindBlogEntryWhereClause(query, startDir, ignoreDoc, year, month, day, tag);

    query.setComment("Finding all blogEntry documents recursively in dir: " + startDir.getName());
    query.addEntity(WikiDocument.class);
    query.addScalar("COMMENT_COUNT", Hibernate.LONG);
    query.setFirstResult(pager.getQueryFirstResult());
    query.setMaxResults(pager.getQueryMaxResults());

    query.setResultTransformer(new ResultTransformer() {
        public Object transformTuple(Object[] result, String[] aliases) {
            BlogEntry be = new BlogEntry();
            be.setEntryDocument((WikiDocument) result[0]);
            blogEntryMap.put(be.getEntryDocument().getId(), be); // Put in map so we can attach comment count later
            return be;
        }

        public List transformList(List list) {
            return list;
        }
    });

    List<BlogEntry> result = (List<BlogEntry>) query.list();

    if (countComments && result.size() > 0) {
        // The risk here is that pager.getQueryMaxResults() is too large for the IN() operator of some DBs...
        StringBuilder commentQueryString = new StringBuilder();
        commentQueryString.append("select doc.NODE_ID as DOC_ID, count(c3.NODE_ID) as COMMENT_COUNT")
                .append(" ");
        commentQueryString.append("from WIKI_DOCUMENT doc").append(" ");
        commentQueryString.append("left outer join WIKI_NODE c1 on doc.NODE_ID = c1.PARENT_NODE_ID")
                .append(" ");
        commentQueryString.append("left outer join WIKI_COMMENT c2 on c1.NODE_ID = c2.NODE_ID").append(" ");
        commentQueryString.append("left outer join WIKI_COMMENT c3 on c2.NS_THREAD = c3.NS_THREAD").append(" ");
        commentQueryString.append("where doc.NODE_ID in (:blogEntriesIds)").append(" ");
        commentQueryString.append("group by doc.NODE_ID");

        SQLQuery commentQuery = getSession().createSQLQuery(commentQueryString.toString());
        commentQuery.setComment("Finding comment count for blog entries");
        commentQuery.addScalar("DOC_ID");
        commentQuery.addScalar("COMMENT_COUNT");
        commentQuery.setParameterList("blogEntriesIds", blogEntryMap.keySet());

        commentQuery.setResultTransformer(new ResultTransformer() {
            public Object transformTuple(Object[] result, String[] aliases) {
                BlogEntry be = blogEntryMap.get(((BigInteger) result[0]).longValue());
                be.setCommentCount(((BigInteger) result[1]).longValue());
                return null;
            }

            public List transformList(List list) {
                return list;
            }
        });
        commentQuery.list();
    }

    return result;
}

From source file:org.jboss.seam.wiki.plugin.blog.BlogDAO.java

License:LGPL

private List<BlogEntryCount> countBlogEntries(WikiDirectory startDir, WikiDocument ignoreDoc,
        final boolean projectYear, final boolean projectMonth, final boolean projectDay, Integer limitYear,
        Integer limitMonth, Integer limitDay, String tag) {

    // Sanity input check
    if (projectDay && (!projectMonth || !projectYear))
        throw new IllegalArgumentException("Can't project on day without months or year");
    if (projectMonth && !projectYear)
        throw new IllegalArgumentException("Can't project on month without year");

    StringBuilder queryString = new StringBuilder();

    queryString.append("select count(doc.NODE_ID) as NUM_OF_ENTRIES").append(" ");
    if (projectYear)
        queryString.append(", ").append("year(doc2.CREATED_ON) as YEAR");
    if (projectMonth)
        queryString.append(", ").append("month(doc2.CREATED_ON) as MONTH");
    if (projectDay)
        queryString.append(", ").append("day(doc2.CREATED_ON) as DAY");
    queryString.append(" ");

    queryString.append(getblogEntryFromClause(tag));
    queryString.append(getBlogEntryWhereClause(ignoreDoc, limitYear, limitMonth, limitDay, tag));

    if (projectYear || projectMonth || projectDay)
        queryString.append("group by").append(" ");
    if (projectYear)
        queryString.append("year(doc2.CREATED_ON)");
    if (projectMonth)
        queryString.append(", month(doc2.CREATED_ON)");
    if (projectDay)
        queryString.append(", day(doc2.CREATED_ON)");

    if (projectYear || projectMonth || projectDay)
        queryString.append("order by").append(" ");
    if (projectYear)
        queryString.append("YEAR desc");
    if (projectMonth)
        queryString.append(", MONTH desc");
    if (projectDay)
        queryString.append(", DAY desc");

    SQLQuery query = getSession().createSQLQuery(queryString.toString());

    bindBlogEntryWhereClause(query, startDir, ignoreDoc, limitYear, limitMonth, limitDay, tag);

    query.setComment("Finding blogEntry counts");
    query.addScalar("NUM_OF_ENTRIES", Hibernate.LONG);
    if (projectYear)
        query.addScalar("YEAR", Hibernate.INTEGER);
    if (projectMonth)
        query.addScalar("MONTH", Hibernate.INTEGER);
    if (projectDay)
        query.addScalar("DAY", Hibernate.INTEGER);

    query.setResultTransformer(new ResultTransformer() {
        public Object transformTuple(Object[] result, String[] aliases) {
            BlogEntryCount beCount = new BlogEntryCount();
            beCount.setNumOfEntries((Long) result[0]);
            if (projectYear)
                beCount.setYear((Integer) result[1]);
            if (projectMonth)
                beCount.setMonth((Integer) result[2]);
            if (projectDay)
                beCount.setDay((Integer) result[3]);
            return beCount;
        }//  w  w w  .  j a v  a2 s  .  com

        public List transformList(List list) {
            return list;
        }
    });

    return (List<BlogEntryCount>) query.list();
}

From source file:org.jbpm.test.Db.java

License:Open Source License

public static String verifyClean(ProcessEngine processEngine) {
    SessionFactory sessionFactory = processEngine.get(SessionFactory.class);
    // when running this with a remote ejb invocation configuration, there is no
    // session factory and no cleanup needs to be done
    if (sessionFactory == null) {
        return null;
    }//from   w w w.  jav a 2 s  .  c o  m

    String[] tableNames = tableNamesCache.get(processEngine);

    if (tableNames == null) {
        Configuration configuration = processEngine.get(Configuration.class);

        // loop over all foreign key constraints
        List<String> tableNamesList = new ArrayList<String>();
        Iterator iter = configuration.getTableMappings();
        while (iter.hasNext()) {
            Table table = (Table) iter.next();
            if (table.isPhysicalTable()) {
                tableNamesList.add(table.getName());
            }
        }

        tableNames = tableNamesList.toArray(new String[tableNamesList.size()]);

        tableNamesCache.put(processEngine, tableNames);
    }

    String recordsLeftMsg = "";
    Session session = sessionFactory.openSession();
    try {
        for (String tableName : tableNames) {
            String countSql = "select count(*) as RECORD_COUNT_ from " + tableName;
            SQLQuery sqlQuery = session.createSQLQuery(countSql);
            sqlQuery.addScalar("RECORD_COUNT_", Hibernate.LONG);
            Long recordCount = (Long) sqlQuery.uniqueResult();
            if (recordCount > 0L) {
                recordsLeftMsg += tableName + ":" + recordCount + ", ";
            }
        }
    } finally {
        session.close();
    }

    if (recordsLeftMsg.length() > 0) {
        clean(processEngine);
    }

    return recordsLeftMsg;
}

From source file:org.jcvi.ometa.hibernate.dao.EventDAO.java

License:Open Source License

/** Broad method for simply getting a list of meta attrib names.  Used above as T/F feed. */
private List<String> getSampleMetaAttributeNames(String projectName, String eventName, Session session) {
    SQLQuery query = session.createSQLQuery(SAMPLE_REQUIRED_QUERY);
    query.setParameter(PROJECT_NAME_PARAM, projectName);
    query.setParameter(EVENT_NAME_PARAM, eventName);
    query.addScalar(RETURN_VAL_PARAM, Hibernate.STRING);
    if (logger.isDebugEnabled()) {
        logger.debug("Query is " + query.toString());
    }/*  w  w  w  . j  av a2s. c  om*/
    return query.list();
}