Example usage for org.hibernate SQLQuery setParameter

Introduction

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

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

From source file:dao.AdDao.java

public LinkedHashMap<String, Integer> getCatsWithCountsBySearch(String wish, List<Long> catIds, Region region,
        List<Long> booleanIds, List<Long> booleanVals, List<Long> stringIds, List<String> stringVals,
        List<BilateralCondition> numVals, List<BilateralCondition> dateVals, List<Long> selIds,
        List<Long> selVals, String multyVals[], Double priceFrom, Double priceTo) {
    String sql = "select c.name,count(sel.ad_id) cc from (select ad.ad_id,ad.category_id from ad where ad.date_from<:now and :now<ad.date_to";
    if (wish == null) {
        wish = "";
    }//from w  w  w  .java2 s  .c om
    List<String> splitted = splitted(wish);
    if (!splitted.isEmpty()) {
        sql += " and (1!=1";
        for (String st : splitted) {
            sql += " or (ad.name like :wish" + splitted.indexOf(st) + ")";
        }
        for (String st : splitted) {
            sql += " or (ad.description like :wish" + splitted.indexOf(st) + ")";
        }
        sql += ")";
    }

    if (!catIds.isEmpty()) {
        sql += " and (1!=1";
        for (Long id : catIds) {
            sql += " or ad.category_id=:catId" + catIds.indexOf(id);
        }
        sql += ")";
    }

    if (region != null && !region.isAllRussia()) {
        sql += " and ad.ad_id in (select ad_id from ads_at_locals where locality_id in (:localIds))";
    }

    /**
     * ?? ? *
     */

    if (priceFrom != null) {
        sql += " and ad.price>=:priceFrom";
    }
    if (priceTo != null) {
        sql += " and ad.price<=:priceTo";
    }
    Integer paramsCount = 0;
    Boolean queryWithParams = false;
    if ((stringVals != null && !stringVals.isEmpty()) || (booleanVals != null && !booleanVals.isEmpty())
            || (numVals != null && !numVals.isEmpty()) || (dateVals != null && !dateVals.isEmpty())
            || (selVals != null && !selVals.isEmpty()) || (multyVals != null && multyVals.length > 0)) {

        queryWithParams = true;
        sql += " and exists(select 1 from (select count(pv.ad_id) cnt,pv.ad_id id from parametr_value pv where (1!=1)";
        int i = 0;

        if (stringVals != null && !stringVals.isEmpty()) {
            i = 0;
            for (String val : stringVals) {
                sql += " or (parametr_id=:stringId" + i + " and string_value like '%:stringVal" + i + "%')";
                paramsCount++;
                i++;
            }

        }

        if (numVals != null && !numVals.isEmpty()) {
            i = 0;
            for (BilateralCondition c : numVals) {
                Double numFrom = (Double) c.getFrom();
                Double numTo = (Double) c.getTo();
                if (numFrom != null || numTo != null) {

                    if (numFrom != null && numTo != null) {
                        sql += " or (parametr_id=:numId" + i + " and number_value >= :numFrom" + i
                                + " and number_value <= :numTo" + i + ")";
                    } else {
                        if (numFrom != null) {
                            sql += " or (parametr_id=:numId" + i + " and number_value >= :numFrom" + i + ")";
                        }
                        if (numTo != null) {
                            sql += " or (parametr_id=:numId" + i + " and number_value <= :numTo" + i + ")";
                        }
                    }
                    paramsCount++;
                    i++;
                }
            }

        }

        if (dateVals != null && !dateVals.isEmpty()) {
            i = 0;
            for (BilateralCondition c : dateVals) {
                Date dateFrom = (Date) c.getFrom();
                Date dateTo = (Date) c.getTo();
                if (dateFrom != null || dateTo != null) {
                    if (dateFrom != null && dateTo != null) {
                        sql += " or (parametr_id=:dateId" + i + " and date_value >= :dateFrom" + i
                                + " and date_value <= :dateTo" + i + ")";
                    } else {
                        if (dateFrom != null) {
                            sql += " or (parametr_id=:dateId" + i + " and date_value >= :dateFrom" + i + ")";
                        }
                        if (dateTo != null) {
                            sql += " or (parametr_id=:dateId" + i + " and date_value <= :dateTo" + i + ")";
                        }
                    }
                    paramsCount++;
                    i++;
                }
            }
        }

        if (booleanVals != null && !booleanVals.isEmpty()) {
            i = 0;
            for (Long val : booleanVals) {
                if (val != null) {
                    sql += " or (parametr_id=:booleanId" + i + " and select_value=:booleanVal" + i + ")";
                    paramsCount++;
                    //ex+=i+":"+val+"; ";
                }
                i++;
            }
        }

        if (selVals != null && !selVals.isEmpty()) {
            i = 0;
            for (Long val : selVals) {
                if (val != null) {
                    sql += " or (parametr_id=:selId" + i + " and select_value=:selVal" + i + ")";
                    paramsCount++;
                    //ex+=i+":"+val+"; ";
                }
                i++;
            }
        }

        if (multyVals != null && multyVals.length > 0) {

            for (String rawVal : multyVals) {
                String idValArr[] = rawVal.split("_");
                if (idValArr.length == 2) {
                    String strId = idValArr[0];
                    String strVal = idValArr[1];
                    Long paramId = Long.valueOf(strId);
                    Long val = Long.valueOf(strVal);
                    if (val != null) {
                        sql += " or (parametr_id=" + paramId + " and select_value=" + val + ")";
                        paramsCount++;
                    }
                }
            }

        }

        sql += " group by pv.ad_id) as tmp where tmp.cnt=:paramsCount and tmp.id=ad.ad_id)";
    }
    /**
     * \?? ? *
     */
    sql += ") sel left join category c on sel.category_id=c.category_id group by sel.category_id order by cc desc";
    SQLQuery query = getCurrentSession().createSQLQuery(sql);

    if (!splitted.isEmpty()) {
        for (String st : splitted) {
            query.setParameter("wish" + splitted.indexOf(st), st);
        }
    }
    if (!catIds.isEmpty()) {
        for (Long id : catIds) {
            query.setParameter("catId" + catIds.indexOf(id), id);
        }
    }
    if (priceFrom != null) {
        query.setParameter("priceFrom", priceFrom);
    }
    if (priceTo != null) {
        query.setParameter("priceTo", priceTo);
    }
    if (stringVals != null && !stringVals.isEmpty()) {
        int i = 0;
        for (String s : stringVals) {
            query.setParameter("stringId" + i, stringIds.get(i));
            query.setParameter("stringVal" + i, stringVals.get(i));
            i++;
        }
    }
    if (numVals != null && !numVals.isEmpty()) {
        int i = 0;
        for (BilateralCondition c : numVals) {
            Long id = c.getId();
            Double numFrom = (Double) c.getFrom();
            Double numTo = (Double) c.getTo();
            query.setParameter("numId" + i, id);
            if (numFrom != null) {
                query.setParameter("numFrom" + i, numFrom);
            }
            if (numTo != null) {
                query.setParameter("numTo" + i, numTo);
            }
            i++;
        }
    }
    if (dateVals != null && !dateVals.isEmpty()) {
        int i = 0;
        for (BilateralCondition c : dateVals) {
            Long id = c.getId();
            Date dateFrom = (Date) c.getFrom();
            Date dateTo = (Date) c.getTo();
            query.setParameter("dateId" + i, id);
            if (dateFrom != null) {
                query.setParameter("dateFrom" + i, dateFrom);
            }
            if (dateTo != null) {
                query.setParameter("dateTo" + i, dateTo);
            }
            i++;
        }
    }
    if (booleanVals != null && !booleanVals.isEmpty()) {
        int i = 0;
        for (Long v : booleanVals) {
            if (v != null) {
                query.setParameter("booleanId" + i, booleanIds.get(i));
                query.setParameter("booleanVal" + i, v);
            }
            i++;
        }

    }
    if (selVals != null && !selVals.isEmpty()) {
        int i = 0;
        for (Long v : selVals) {
            if (v != null) {
                query.setParameter("selId" + i, selIds.get(i));
                query.setParameter("selVal" + i, v);
            }
            i++;
        }

    }
    if (queryWithParams) {
        query.setParameter("paramsCount", paramsCount);
    }
    query.setParameter("now", new Date());
    if (region != null && !region.isAllRussia()) {
        query.setParameterList("localIds", getLocIds(region));
    }
    List<Object[]> rawRes = query.list();
    LinkedHashMap<String, Integer> res = new LinkedHashMap();
    if (!rawRes.isEmpty()) {
        for (Object[] o : rawRes) {
            String catName = (String) o[0];
            Integer count = ((BigInteger) o[1]).intValue();
            if (0 != count) {
                res.put(catName, count);
            }
        }
    }
    return res;
}

---

From source file:dao.AdDao.java

public List<Ad> getAdsByWishInNameOrDescription(String wish, List<Long> catIds, Region region, String order,
        List<Long> booleanIds, List<Long> booleanVals, List<Long> stringIds, List<String> stringVals,
        List<BilateralCondition> numVals, List<BilateralCondition> dateVals, List<Long> selIds,
        List<Long> selVals, String multyVals[], Double priceFrom, Double priceTo) throws Exception {
    if (order == null) {
        order = "show_count desc";
    }/*  w w w.  j av  a 2  s . c  om*/
    String sql = "select * from ad where date_from<:now and :now<date_to";
    if (wish == null) {
        wish = "";
    }
    List<String> splitted = splitted(wish);
    if (!splitted.isEmpty()) {
        sql += " and (1!=1";
        for (String st : splitted) {
            sql += " or (name like :wish" + splitted.indexOf(st) + ")";
        }
        for (String st : splitted) {
            sql += " or (description like :wish" + splitted.indexOf(st) + ")";
        }
        sql += ")";
    }

    if (!catIds.isEmpty()) {
        sql += " and (1!=1";
        for (Long id : catIds) {
            sql += " or category_id=:catId" + catIds.indexOf(id);
        }
        sql += ")";
    }

    if (region != null && !region.isAllRussia()) {
        sql += " and ad_id in (select ad_id from ads_at_locals where locality_id in (:localIds))";
    }

    /**
     * ?? ? *
     */

    if (priceFrom != null) {
        sql += " and price>=:priceFrom";
    }
    if (priceTo != null) {
        sql += " and price<=:priceTo";
    }
    Integer paramsCount = 0;
    Boolean queryWithParams = false;
    if ((stringVals != null && !stringVals.isEmpty()) || (booleanVals != null && !booleanVals.isEmpty())
            || (numVals != null && !numVals.isEmpty()) || (dateVals != null && !dateVals.isEmpty())
            || (selVals != null && !selVals.isEmpty()) || (multyVals != null && multyVals.length > 0)) {

        queryWithParams = true;
        sql += " and exists(select 1 from (select count(pv.ad_id) cnt,pv.ad_id id from parametr_value pv where (1!=1)";
        int i = 0;

        if (stringVals != null && !stringVals.isEmpty()) {
            i = 0;
            for (String val : stringVals) {
                sql += " or (parametr_id=:stringId" + i + " and string_value like '%:stringVal" + i + "%')";
                paramsCount++;
                i++;
            }

        }

        if (numVals != null && !numVals.isEmpty()) {
            i = 0;
            for (BilateralCondition c : numVals) {
                Double numFrom = (Double) c.getFrom();
                Double numTo = (Double) c.getTo();
                if (numFrom != null || numTo != null) {

                    if (numFrom != null && numTo != null) {
                        sql += " or (parametr_id=:numId" + i + " and number_value >= :numFrom" + i
                                + " and number_value <= :numTo" + i + ")";
                    } else {
                        if (numFrom != null) {
                            sql += " or (parametr_id=:numId" + i + " and number_value >= :numFrom" + i + ")";
                        }
                        if (numTo != null) {
                            sql += " or (parametr_id=:numId" + i + " and number_value <= :numTo" + i + ")";
                        }
                    }
                    paramsCount++;
                    i++;
                }
            }

        }

        if (dateVals != null && !dateVals.isEmpty()) {
            i = 0;
            for (BilateralCondition c : dateVals) {
                Date dateFrom = (Date) c.getFrom();
                Date dateTo = (Date) c.getTo();
                if (dateFrom != null || dateTo != null) {
                    if (dateFrom != null && dateTo != null) {
                        sql += " or (parametr_id=:dateId" + i + " and date_value >= :dateFrom" + i
                                + " and date_value <= :dateTo" + i + ")";
                    } else {
                        if (dateFrom != null) {
                            sql += " or (parametr_id=:dateId" + i + " and date_value >= :dateFrom" + i + ")";
                        }
                        if (dateTo != null) {
                            sql += " or (parametr_id=:dateId" + i + " and date_value <= :dateTo" + i + ")";
                        }
                    }
                    paramsCount++;
                    i++;
                }
            }
        }

        /*if (booleanVals != null && booleanVals.length > 0) {
        i = 0;
        while (i < booleanIds.length) {
            Long paramId = booleanIds[i];
            Long val = booleanVals[i];
            if (val != null) {
                sql += " or (parametr_id=" + paramId + " and select_value=" + val + ")";
                paramsCount++;
            }
            i++;
        }
        }
                
        if (selVals != null && selVals.length > 0) {
        i = 0;
        while (i < selIds.length) {
            Long paramId = selIds[i];
            Long val = selVals[i];
            if (val != null) {
                sql += " or (parametr_id=" + paramId + " and select_value=" + val + ")";
                paramsCount++;
            }
            i++;
        }
        }*/

        if (booleanVals != null && !booleanVals.isEmpty()) {
            i = 0;
            for (Long val : booleanVals) {
                if (val != null) {
                    sql += " or (parametr_id=:booleanId" + i + " and select_value=:booleanVal" + i + ")";
                    paramsCount++;
                    //ex+=i+":"+val+"; ";
                }
                i++;
            }
        }

        if (selVals != null && !selVals.isEmpty()) {
            i = 0;
            for (Long val : selVals) {
                if (val != null) {
                    sql += " or (parametr_id=:selId" + i + " and select_value=:selVal" + i + ")";
                    paramsCount++;
                    //ex+=i+":"+val+"; ";
                }
                i++;
            }
        }

        if (multyVals != null && multyVals.length > 0) {

            for (String rawVal : multyVals) {
                String idValArr[] = rawVal.split("_");
                if (idValArr.length == 2) {
                    String strId = idValArr[0];
                    String strVal = idValArr[1];
                    Long paramId = Long.valueOf(strId);
                    Long val = Long.valueOf(strVal);
                    if (val != null) {
                        sql += " or (parametr_id=" + paramId + " and select_value=" + val + ")";
                        paramsCount++;
                    }
                }
            }

        }

        sql += " group by pv.ad_id) as tmp where tmp.cnt=:paramsCount and tmp.id=ad.ad_id)";
    }
    /**
     * \?? ? *
     */
    sql += " order by status asc," + order;

    SQLQuery query = getCurrentSession().createSQLQuery(sql);

    if (!splitted.isEmpty()) {
        for (String st : splitted) {
            query.setParameter("wish" + splitted.indexOf(st), st);
        }
    }
    if (!catIds.isEmpty()) {
        for (Long id : catIds) {
            query.setParameter("catId" + catIds.indexOf(id), id);
        }
    }
    if (priceFrom != null) {
        query.setParameter("priceFrom", priceFrom);
    }
    if (priceTo != null) {
        query.setParameter("priceTo", priceTo);
    }
    if (stringVals != null && !stringVals.isEmpty()) {
        int i = 0;
        for (String s : stringVals) {
            query.setParameter("stringId" + i, stringIds.get(i));
            query.setParameter("stringVal" + i, stringVals.get(i));
            i++;
        }
    }
    if (booleanVals != null && !booleanVals.isEmpty()) {
        int i = 0;
        for (Long v : booleanVals) {
            if (v != null) {
                query.setParameter("booleanId" + i, booleanIds.get(i));
                query.setParameter("booleanVal" + i, v);
            }
            i++;
        }

    }
    if (selVals != null && !selVals.isEmpty()) {
        int i = 0;
        for (Long v : selVals) {
            if (v != null) {
                query.setParameter("selId" + i, selIds.get(i));
                query.setParameter("selVal" + i, v);
            }
            i++;
        }

    }
    if (numVals != null && !numVals.isEmpty()) {
        int i = 0;
        for (BilateralCondition c : numVals) {
            Long id = c.getId();
            Double numFrom = (Double) c.getFrom();
            Double numTo = (Double) c.getTo();
            query.setParameter("numId" + i, id);
            if (numFrom != null) {
                query.setParameter("numFrom" + i, numFrom);
            }
            if (numTo != null) {
                query.setParameter("numTo" + i, numTo);
            }
            i++;
        }
    }
    if (dateVals != null && !dateVals.isEmpty()) {
        int i = 0;
        for (BilateralCondition c : dateVals) {
            Long id = c.getId();
            Date dateFrom = (Date) c.getFrom();
            Date dateTo = (Date) c.getTo();
            query.setParameter("dateId" + i, id);
            if (dateFrom != null) {
                query.setParameter("dateFrom" + i, dateFrom);
            }
            if (dateTo != null) {
                query.setParameter("dateTo" + i, dateTo);
            }
            i++;
        }
    }
    if (queryWithParams) {
        query.setParameter("paramsCount", paramsCount);
    }
    query.setParameter("now", new Date());
    if (region != null && !region.isAllRussia()) {
        query.setParameterList("localIds", getLocIds(region));
    }
    query.addEntity(Ad.class);
    if (1 == 1) {
        //throw new Exception("e="+ex+"; bv:"+booleanVals.size()+"; sv:"+selVals.size()+"; pc:"+paramsCount);
    }
    //throw new Exception(sql);

    return query.list();
}

---

From source file:dao.ParametrDao.java

public List<Parametr> getParamsFromCat(Long catId) {
    String sql = "select p.* from param_category_link l left join parametr p on l.parametr_id=p.parametr_id where l.category_id=:catId";
    SQLQuery query = getCurrentSession().createSQLQuery(sql);
    query.addEntity(Parametr.class);
    query.setParameter("catId", catId);
    return query.list();
}

---

From source file:dao.ParametrDao.java

public List<Object[]> getParamsAndNeedsFromCat(Long catId) {
    String sql = "select p.*,l.req_type from param_category_link l left join parametr p on l.parametr_id=p.parametr_id where l.category_id=:catId order by p.name asc";
    SQLQuery query = getCurrentSession().createSQLQuery(sql);
    query.addEntity("p", Parametr.class);
    query.addScalar("req_type", StandardBasicTypes.INTEGER);
    query.setParameter("catId", catId);
    return query.list();
}

---

From source file:daoImpl.GroupsDAOImpl.java

@Override
public List<SearchResult> findNearestGroups(float lati, float longi, float maxDistance) {
    Session session = sFac.openSession();
    SQLQuery getDistances = session.createSQLQuery("Select groups.group_id, "
            + "(6371 * acos( cos( radians(:lati )) * cos( radians( groups.latitude ) ) * cos( radians( groups.longitude ) - radians(:longi) ) + sin( radians(:lati) ) * sin( radians( groups.latitude ) ) ) ) as distance "
            + "from groups having distance < :maxDistance order by distance;");
    getDistances.setParameter("lati", lati);
    getDistances.setParameter("longi", longi);
    getDistances.setParameter("maxDistance", maxDistance);
    getDistances.addScalar("group_id", StandardBasicTypes.INTEGER);
    getDistances.addScalar("distance", StandardBasicTypes.FLOAT);

    List<Object[]> closestGroups = getDistances.list();

    if (closestGroups.isEmpty())
        return new ArrayList<>();

    ArrayList groupsToGet = new ArrayList<>();

    for (Object[] r : closestGroups) {
        int i = (int) r[0];
        groupsToGet.add(i);//  w ww.j  ava  2 s . c  o  m
    }

    Query getGroups = session.createQuery(
            "from Groups G left join fetch G.tagses left join fetch G.usersGroupses where G.groupId in (:ids)");
    getGroups.setParameterList("ids", groupsToGet);
    getGroups.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Groups> groupsFound = getGroups.list();

    ArrayList<SearchResult> results = new ArrayList<>();

    for (int i = 0; i < groupsFound.size(); i++) {
        SearchResult s = new SearchResult(groupsFound.get(i), (float) (closestGroups.get(i)[1]));
        results.add(s);
    }
    session.close();
    return results;
}

---

From source file:daoImpl.GroupsDAOImpl.java

@Override
public List<SearchResult> findNearestGroupsByName(float lati, float longi, float maxDistance,
        String searchTerm) {/*from  w  ww  .j  a v  a2s  .c o m*/
    Session session = sFac.openSession();
    SQLQuery getDistances = session.createSQLQuery("Select groups.group_id, "
            + "(6371 * acos( cos( radians(:lati )) * cos( radians( groups.latitude ) ) * cos( radians( groups.longitude ) - radians(:longi) ) + sin( radians(:lati) ) * sin( radians( groups.latitude ) ) ) ) as distance "
            + "from groups where groups.groupname like concat('%', :name , '%') having distance < :maxDistance order by distance;");
    getDistances.setParameter("lati", lati);
    getDistances.setParameter("longi", longi);
    getDistances.setParameter("maxDistance", maxDistance);
    getDistances.setParameter("name", searchTerm);
    getDistances.addScalar("group_id", StandardBasicTypes.INTEGER);
    getDistances.addScalar("distance", StandardBasicTypes.FLOAT);
    List<Object[]> closestGroups = getDistances.list();

    if (closestGroups.isEmpty())
        return new ArrayList<>();

    ArrayList groupsToGet = new ArrayList<>();

    for (Object[] r : closestGroups) {
        int i = (int) r[0];
        groupsToGet.add(i);
    }

    Query getGroups = session.createQuery(
            "from Groups G left join fetch G.tagses left join fetch G.usersGroupses where G.groupId in (:ids)");
    getGroups.setParameterList("ids", groupsToGet);
    getGroups.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Groups> groupsFound = getGroups.list();

    ArrayList<SearchResult> results = new ArrayList<>();

    for (int i = 0; i < groupsFound.size(); i++) {
        SearchResult s = new SearchResult(groupsFound.get(i), (float) (closestGroups.get(i)[1]));
        results.add(s);
    }
    session.close();
    return results;
}

---

From source file:daoImpl.GroupsDAOImpl.java

@Override
public List<SearchResult> findNearestGroupsByDesc(float lati, float longi, float maxDistance,
        String description) {//from  w w  w  .j  a v a 2 s  .c  om
    Session session = sFac.openSession();
    SQLQuery getDistances = session.createSQLQuery("Select groups.group_id, "
            + "(6371 * acos( cos( radians(:lati )) * cos( radians( groups.latitude ) ) * cos( radians( groups.longitude ) - radians(:longi) ) + sin( radians(:lati) ) * sin( radians( groups.latitude ) ) ) ) as distance "
            + "from groups where groups.description like concat('%', :name , '%') having distance < :maxDistance order by distance;");
    getDistances.setParameter("lati", lati);
    getDistances.setParameter("longi", longi);
    getDistances.setParameter("maxDistance", maxDistance);
    getDistances.setParameter("name", description);
    getDistances.addScalar("group_id", StandardBasicTypes.INTEGER);
    getDistances.addScalar("distance", StandardBasicTypes.FLOAT);
    List<Object[]> closestGroups = getDistances.list();

    if (closestGroups.isEmpty())
        return new ArrayList<>();

    ArrayList groupsToGet = new ArrayList<>();

    for (Object[] r : closestGroups) {
        int i = (int) r[0];
        groupsToGet.add(i);
    }

    Query getGroups = session.createQuery(
            "from Groups G left join fetch G.tagses left join fetch G.usersGroupses where G.groupId in (:ids)");
    getGroups.setParameterList("ids", groupsToGet);
    getGroups.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Groups> groupsFound = getGroups.list();

    ArrayList<SearchResult> results = new ArrayList<>();

    for (int i = 0; i < groupsFound.size(); i++) {
        SearchResult s = new SearchResult(groupsFound.get(i), (float) (closestGroups.get(i)[1]));
        results.add(s);
    }
    session.close();
    return results;
}

---

From source file:daoImpl.GroupsDAOImpl.java

@Override
public String getSecretKey(String name) {
    Session session = sFac.openSession();
    SQLQuery getKey = session.createSQLQuery("Select key_value from secret_keys where key_name=:name");
    getKey.setParameter("name", name);
    String result = (String) getKey.uniqueResult();
    session.close();/*from  ww w. ja  v  a 2 s.com*/
    return result;
}

---

From source file:daoImpl.GroupsDAOImpl.java

@Override
public List<SearchResult> findNearestGroupsByTag(float latitude, float longitude, float maxDistance,
        String tag) {/*from w  w  w .  j  a v a  2  s.  c o  m*/
    Session session = sFac.openSession();
    SQLQuery query = session.createSQLQuery(
            "Select groups.group_id, (6371 * acos( cos( radians(:lati )) * cos( radians( groups.latitude ) ) * cos( radians( groups.longitude ) - radians(:longi) ) + sin( radians(:lati) ) * sin( radians( groups.latitude ) ) ) ) as distance from groups join groups_tags on (groups.group_id=groups_tags.group_id_fk) join tags on (groups_tags.tag_id_fk=tags.tag_id) where tags.tag_name = :tagname having distance < :dist order by distance;");
    query.setParameter("lati", latitude);
    query.setParameter("longi", longitude);
    query.setParameter("tagname", tag);
    query.setParameter("dist", maxDistance);
    query.addScalar("group_id", StandardBasicTypes.INTEGER);
    query.addScalar("group_id", StandardBasicTypes.FLOAT);

    List<Object[]> closestGroups = query.list();

    if (closestGroups.isEmpty())
        return new ArrayList<>();

    ArrayList groupsToGet = new ArrayList<>();

    for (Object[] r : closestGroups) {
        int i = (int) r[0];
        groupsToGet.add(i);
    }

    Query getGroups = session.createQuery(
            "from Groups G left join fetch G.tagses left join fetch G.usersGroupses where G.groupId in (:ids)");
    getGroups.setParameterList("ids", groupsToGet);
    getGroups.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Groups> groupsFound = getGroups.list();

    ArrayList<SearchResult> results = new ArrayList<>();

    for (int i = 0; i < groupsFound.size(); i++) {
        SearchResult s = new SearchResult(groupsFound.get(i), (float) (closestGroups.get(i)[1]));
        results.add(s);
    }
    session.close();
    return results;
}

---

From source file:de.arago.rike.commons.data.ChartTimeSeries.java

License:Open Source License

public static Map<String, List<List<Long>>> query(String str, Object[] parameters) {
    //Session s = factory.getCurrentSession();
    //Transaction tr = s.beginTransaction();
    DataHelperRike<Object> helper = new DataHelperRike<Object>(Object.class);
    org.hibernate.SQLQuery query = helper.createSQLQuery(str).addScalar("name", Hibernate.STRING)
            .addScalar("value", Hibernate.LONG).addScalar("moment", Hibernate.DATE);

    if (parameters != null) {
        for (int i = 0; i < parameters.length; ++i) {
            query.setParameter(i, parameters[i]);
        }/*from  w  w w  .j  a  v  a2 s .  co m*/

    }

    Map<String, List<List<Long>>> data = new LinkedHashMap<String, List<List<Long>>>();
    for (Object first : query.list()) {
        Object[] arr = (Object[]) first;
        String name = (String) arr[0];
        Long value = (Long) arr[1];
        Date moment = (Date) arr[2];
        List<List<Long>> ts;
        if (!data.containsKey(name)) {
            ts = new LinkedList<List<Long>>();
            data.put(name, ts);
        } else {
            ts = data.get(name);
        }
        List<Long> tmp = new ArrayList<Long>(2);
        tmp.add(moment.getTime());
        tmp.add(value);
        ts.add(tmp);
    }

    helper.finish(query);
    //tr.commit();
    return data;
}

---

• «
• 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
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »