Example usage for org.hibernate SQLQuery addEntity

Introduction

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

Prototype

SQLQuery<T> addEntity(Class entityType);

Source Link

Document

Declare a "root" entity, without specifying an alias.

Usage

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public List<LocationDetails> getLocationDetails(final Integer locationId, final Integer start,
        final Integer numOfRows) {
    try {//from   w w w.  j ava2s .  c o  m

        final StringBuilder query = new StringBuilder()
                .append("select l.lname as location_name,l.locid,l.ltype as ltype,")
                .append(" g.lat as latitude, g.lon as longitude, g.alt as altitude,")
                .append(" c.cntryid as cntryid, c.isofull as country_full_name, l.labbr as location_abbreviation,")
                .append(" ud.fname as location_type,")
                .append(" ud.fdesc as location_description, l.program_uuid,")
                .append(" c.isoabbr as cntry_name, province.lname AS province_name, province.locid as province_id")
                .append(" from location l").append(" left join georef g on l.locid = g.locid")
                .append(" left join cntry c on l.cntryid = c.cntryid")
                .append(" left join udflds ud on ud.fldno = l.ltype").append(" ,location province");

        if (locationId != null) {
            query.append(" where l.locid = :id");
            query.append(" AND province.locid = l.snl1id");

            final SQLQuery sqlQuery = this.getSession().createSQLQuery(query.toString());
            sqlQuery.setParameter("id", locationId);
            sqlQuery.setFirstResult(start);
            sqlQuery.setMaxResults(numOfRows);
            sqlQuery.addEntity(LocationDetails.class);

            return sqlQuery.list();
        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationDetails", "id",
                String.valueOf(locationId), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }
    return new ArrayList<>();
}

---

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public List<Location> getAllProvincesByCountry(final Integer countryId) {
    if (countryId == null || countryId == 0) {
        return new ArrayList<>();
    }/*from  w ww  .jav  a  2s . co m*/

    try {
        final SQLQuery query = this.getSession().createSQLQuery(Location.GET_PROVINCE_BY_COUNTRY);
        query.addEntity(Location.class);
        query.setParameter("countryId", countryId);
        return query.list();
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getAllProvinces", "", null,
                e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }
}

---

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public List<Location> getAllProvinces() {

    try {//from   ww  w  .j  av  a2s  .c  om
        final SQLQuery query = this.getSession().createSQLQuery(Location.GET_ALL_PROVINCES);
        query.addEntity(Location.class);
        return query.list();
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getAllProvinces", "", null,
                e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }
}

---

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public List<LocationDetails> getFilteredLocations(final Integer countryId, final Integer locationType,
        final String locationName, final String programUUID) {

    try {//from   ww w.ja v a 2s . c  o m

        final StringBuilder queryString = new StringBuilder()
                .append("SELECT l.lname as location_name,l.locid,l.ltype as ltype,")
                .append(" g.lat as latitude, g.lon as longitude, g.alt as altitude,")
                .append(" c.cntryid as cntryid, c.isofull as country_full_name, l.labbr as location_abbreviation,")
                .append(" ud.fname as location_type,")
                .append(" ud.fdesc as location_description, l.program_uuid")
                .append(" ,c.isoabbr as cntry_name, province.lname AS province_name, province.locid as province_id")
                .append(" FROM location l").append(" LEFT JOIN georef g on l.locid = g.locid")
                .append(" LEFT JOIN cntry c on l.cntryid = c.cntryid")
                .append(" LEFT JOIN udflds ud on ud.fldno = l.ltype").append(" ,location province ")
                .append(" WHERE (l.program_uuid = '").append(programUUID).append("'")
                .append(" or l.program_uuid is null) ").append(" and province.locid = l.snl1id ");

        if (countryId != null) {
            queryString.append(" AND c.cntryid = ");
            queryString.append(countryId);
        }

        if (locationType != null) {
            queryString.append(" AND l.ltype = ");
            queryString.append(locationType);
        }

        if (locationName != null && !locationName.isEmpty()) {
            queryString.append(" AND l.lname REGEXP '");
            queryString.append(locationName);
            queryString.append("' ");
        }

        queryString.append(" ORDER BY UPPER(l.lname) ");

        final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
        query.addEntity(LocationDetails.class);

        return query.list();

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getFilteredLocations", "", null,
                e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }
}

---

From source file:org.generationcp.middleware.dao.LocdesDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Locdes> getAllLocationDescriptionsByFilters(final String fcode, final String[] dval)
        throws MiddlewareQueryException {
    try {/*from w  w  w .j  ava2s.  c  o m*/
        final StringBuilder sqlString = new StringBuilder();

        sqlString.append("SELECT ld.ldid,ld.locid,ld.dtype,ld.duid,ld.dval,ld.ddate,ld.dref ") //
                .append(" FROM locdes ld, udflds ud") //
                .append(" WHERE ld.dtype = ud.fldno");

        if (fcode != null) {
            sqlString.append(" and ud.fcode= '").append(fcode).append("' ");
        }

        if (dval != null) {
            sqlString.append(" and ld.dval in ('").append(StringUtils.join(dval, "','")).append("')");
        }

        final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString());
        query.addEntity(Locdes.class);

        return query.list();
    } catch (HibernateException e) {
        LocdesDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getAllLocationDescriptionsByFilters",
                "", null, e.getMessage(), LocdesDAO.CLASS_NAME_LOCDES), e);
    }
}

---

From source file:org.generationcp.middleware.dao.LocdesDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Locdes> getAllLocationDescriptionsByFilters(final String fcode, final Integer locid,
        final String dval) throws MiddlewareQueryException {
    try {//  ww  w  .  j a  va 2 s  .c  om
        final StringBuilder sqlString = new StringBuilder();

        sqlString.append("SELECT ld.ldid,ld.locid,ld.dtype,ld.duid,ld.dval,ld.ddate,ld.dref ") //
                .append(" FROM locdes ld, udflds ud") //
                .append(" WHERE ld.dtype = ud.fldno");

        if (fcode != null) {
            sqlString.append(" and ud.fcode= '").append(fcode).append("' ");
        }

        if (locid != null) {
            sqlString.append(" and ld.locid= ").append(locid);
        }

        if (dval != null) {
            sqlString.append(" and ld.dval= '").append(dval).append("' ");
        }

        final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString());
        query.addEntity(Locdes.class);

        return query.list();
    } catch (HibernateException e) {
        LocdesDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getAllLocationDescriptionsByFilters",
                "", null, e.getMessage(), LocdesDAO.CLASS_NAME_LOCDES), e);
    }
}

---

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

License:Open Source License

public List<Children> getListChildrenDueByDueTimeCommune(String dueTime, Commune commune) {
    List<Children> listChildren = new ArrayList<Children>();

    String sql = "Select c.* from children c, children_vaccination_history cvh, village vl, vaccination v"
            + " where cvh.id_children=c.id and c.id_village =vl.id and vl.id_commune=:communeId and c.locked!=true"
            + " and cvh.id_vaccination=v.id and cvh.vaccinated=0 and " + " ("
            + " (v.id_dependent_vaccination is null)" + " or" + " (v.id_dependent_vaccination is not null"
            + " and  (Select id from children_vaccination_history cvh2 where cvh2.id_vaccination=v.id_dependent_vaccination and cvh2.id_children=c.id "
            + "and cvh2.vaccinated=1 and DATE(DATE_ADD(cvh2.date_of_immunization, INTERVAL v.gap DAY)) <= :dueTime) is not null"
            + " )" + " ) "
            + " and (DATE(DATE_ADD(c.date_of_birth, INTERVAL v.age*30*v.age_unit DAY)) <= :dueTime)"
            + " and (v.limit_days is null or (v.limit_days > 0 and DATE(DATE_ADD(c.date_of_birth, INTERVAL (v.limit_days + 1) DAY)) >= :dueTime))"
            + " group by c.id order by c.date_of_birth asc";

    SQLQuery qry = getSession().createSQLQuery(sql);
    qry.addEntity(Children.class);
    qry.setParameter("dueTime", dueTime);
    qry.setParameter("communeId", commune.getId());

    //log.debug("SQL: " + sql);

    listChildren = qry.list();//from  ww  w .ja v  a  2 s  .co  m

    return listChildren;
}

---

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

License:Open Source License

public List<Vaccination> getListVaccinationByChild(String dueTime, Children child, Boolean overdue) {
    List<Vaccination> result = new ArrayList<Vaccination>();

    String sql = "Select v.* from children c, children_vaccination_history cvh, vaccination v"
            + " where cvh.id_children=:childId and c.id=:childId"
            + " and cvh.id_vaccination=v.id and cvh.vaccinated=0 and " + " ("
            + "  (v.id_dependent_vaccination is null)" + "  or" + "  (v.id_dependent_vaccination is not null"
            + "   and  (Select id from children_vaccination_history cvh2 where cvh2.id_vaccination=v.id_dependent_vaccination and cvh2.id_children=c.id "
            + "and cvh2.vaccinated=1 and DATE(DATE_ADD(cvh2.date_of_immunization, INTERVAL v.gap DAY)) <= :dueTime) is not null"
            + "  )" + " ) "
            + " and (DATE(DATE_ADD(c.date_of_birth, INTERVAL v.age*30*v.age_unit DAY)) <= :dueTime)";
    String ymtime = "";
    if (overdue != null) {
        ymtime = dueTime.substring(0, 7);
        if (overdue.booleanValue() == true) {
            sql += " and  date_format(DATE_ADD(c.date_of_birth, INTERVAL v.age*30*v.age_unit DAY),'%Y-%m')!=:ymtime ";
        } else if (overdue.booleanValue() == false)
            sql += " and  date_format(DATE_ADD(c.date_of_birth, INTERVAL v.age*30*v.age_unit DAY),'%Y-%m')=:ymtime ";
    }/*from w ww .jav a2  s .com*/

    sql += " and (v.limit_days is null or (v.limit_days > 0 and DATE(DATE_ADD(c.date_of_birth, INTERVAL (v.limit_days + 1) DAY)) >= :dueTime))"
            + " group by v.id";

    //log.debug(dueTime + " | " + ymtime + " | " + sql);
    SQLQuery qry = getSession().createSQLQuery(sql);
    qry.addEntity(Vaccination.class);
    qry.setParameter("dueTime", dueTime);
    if (overdue != null)
        qry.setParameter("ymtime", ymtime);
    qry.setParameter("childId", child.getId());
    result = qry.list();
    return result;
}

---

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

License:Open Source License

public List<VaccinationDay> getDaySMS(Integer beforedays) {
    List<VaccinationDay> listVP = new ArrayList<VaccinationDay>();
    Integer month = (new Date()).getMonth() + 1;
    Integer year = (new Date()).getYear() + 1900;
    String sql = "Select * from vaccination_day vd WHERE DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL " + beforedays
            + " DAY),'%e') = date_in_month";

    SQLQuery qry = getSession().createSQLQuery(sql);
    qry.addEntity(VaccinationDay.class);
    listVP = qry.list();//from  w  ww  . j  a  v a2  s .co  m

    return listVP;
}

---

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 www .j a v  a2s  . 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;
}

---

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