List of usage examples for org.hibernate SQLQuery addScalar
SQLQuery<T> addScalar(String columnAlias, Type type);
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(); }