Example usage for org.hibernate SQLQuery list

Introduction

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

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:com.bookselling.dao.SellingPostDaoImpl.java

@Override
public PaginationData<SellingPost> getBestSelling(int first, int items) {
    SQLQuery sqlQuery = getSession().createSQLQuery("select boughtEntityId, sum(quatity) "
            + "from tradedetail trd " + "group by boughtEntityId " + "order by sum(quatity) desc");
    sqlQuery.setFirstResult(first).setMaxResults(items);

    List result = sqlQuery.list();
    Integer boughtEntityIds[] = new Integer[result.size()];
    int index = 0;
    for (Object rows : boughtEntityIds) {
        Object[] row = (Object[]) rows;
        boughtEntityIds[index++] = Integer.valueOf((String) row[0]);
    }/*  ww w  .  j  a  va  2s . c om*/

    Criteria criteria = getSession().createCriteria(SellingPost.class);
    criteria.add(Restrictions.eq("status", SellingPostStatus.CONFIRM))
            .add(Restrictions.in("id", boughtEntityIds));

    Set<SellingPost> posts = new HashSet<>(criteria.list());
    HibernateInitSupport.setCls(SellingPost.class);
    for (SellingPost post : posts)
        HibernateInitSupport.initDomain(post);

    PaginationData paginationData = new PaginationData(
            (long) criteria.setProjection(Projections.rowCount()).uniqueResult(), items, first, posts);

    return paginationData;
}

---

From source file:com.bookselling.dao.SellingPostDaoImpl.java

@Override
public StatisticData<GenericChartUnit<Object>> countPost(Date fromDate, Date toDate) {
    StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>());
    statisticData.setLabel("Lt ng bi");

    SQLQuery sqlQuery = getSession().createSQLQuery("select DATE(createdDate) date, count(*) " + "from post "
            + "where createdDate between :fromDate and :toDate " + "group by date " + "order by date ");
    sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate);

    List<Object[]> rows = sqlQuery.list();
    for (Object[] row : rows) {
        GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(),
                new ArrayList<Object>());
        unit.getFigures().add(row[1]);/* w w w. j  a  v  a 2 s. c o m*/
        statisticData.getData().add(unit);
    }

    return statisticData;
}

---

From source file:com.bookselling.dao.SystemInvoiceDaoImpl.java

@Override
public StatisticData<GenericChartUnit<Object>> countTotalProfit(Date fromDate, Date toDate) {
    StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>());
    statisticData.setLabel("Doanh thu ng bi");

    SQLQuery sqlQuery = getSession().createSQLQuery("select DATE(createdDate) date, SUM(fee) "
            + "from systeminvoice " + "where createdDate >= DATE(:fromDate) and createdDate <= DATE(:toDate) "
            + "group by date " + "order by date asc ");

    sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate);

    List<Object[]> rows = sqlQuery.list();
    for (Object[] row : rows) {
        GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(),
                new ArrayList<Object>());
        unit.getFigures().add(row[1]);//from  www .  ja v  a  2s .  c o  m
        statisticData.getData().add(unit);
    }

    return statisticData;
}

---

From source file:com.bookselling.dao.UserDaoImpl.java

@Override
public StatisticData<GenericChartUnit<Object>> registrationStat(Date fromDate, Date toDate) {
    StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>());
    statisticData.setLabel("Lt ng k");

    SQLQuery sqlQuery = getSession().createSQLQuery("select DATE(createdDate) date, count(*) "
            + "from account acc " + "where createdDate >= :fromDate and createdDate <= :toDate "
            + "group by date " + "order by date asc ");
    sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate);

    List<Object[]> rows = sqlQuery.list();
    for (Object[] row : rows) {
        GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(),
                new ArrayList<Object>());
        unit.getFigures().add(row[1]);//  w ww  .j av  a 2  s  .  c  om
        statisticData.getData().add(unit);
    }

    return statisticData;
}

---

From source file:com.bookselling.dao.UserDaoImpl.java

@Override
public Set<User> whoHasMorePost() {
    SQLQuery sqlQuery = getSession()
            .createSQLQuery("select u.id " + "from user u, sellingPost slp " + "where u.id = slp.userId "
                    + "group by u.id " + "having COUNT(*) > 0 " + "order by COUNT(*) desc " + "limit 5 ");
    List<Integer> data = sqlQuery.list();

    Criteria criteria = getSession().createCriteria(User.class);
    if (data.size() != 0)
        criteria.add(Restrictions.in("id", data));

    Set<User> users = new HashSet<>(criteria.list());
    HibernateInitSupport.setCls(User.class);
    for (User user : users) {
        HibernateInitSupport.initDomain(user);
    }//  w ww .  j  a  v a 2s  .  c o  m

    return users;
}

---

From source file:com.bookselling.dao.UserDaoImpl.java

@Override
public Map<User, Double> mostValueableBuyer() {
    Map<User, Double> stat = new HashMap<>();

    SQLQuery sqlQuery = getSession().createSQLQuery(
            "select u.id, SUM(totalPrice) value " + "from user u, trade t " + "where u.id = t.buyerId "
                    + "group by u.id " + "having value > 0 " + "order by value desc " + "limit 5 ");
    List<Object[]> rows = sqlQuery.list();
    Set<Integer> ids = new HashSet<>();
    for (Object[] row : rows) {
        ids.add((Integer) row[0]);
    }//  w w  w .  j  av  a  2  s. c  om

    if (!ids.isEmpty()) {
        Criteria criteria = getSession().createCriteria(User.class);
        criteria.add(Restrictions.in("id", ids));

        List<User> users = criteria.list();
        int index = 0;
        HibernateInitSupport.setCls(User.class);
        for (User user : users) {
            HibernateInitSupport.initDomain(user);
            stat.put(user, (Double) rows.get(index)[1]);
        }
    }
    return stat;
}

---

From source file:com.bryan.crud.dao.impl.AmbitoDAOImpl.java

@Override
public ArrayList allAmbitos() {
    String sql = "SELECT N_AMBITO_PK AS id, C_NOMBRE_AMBITO AS nombreAmbito, N_TIPO_AMBITO AS tipoAmbito, N_CATEGORIA AS categoria, C_UBIGEO AS ubigeo,"
            + " C_DEPARTAMENTO AS departamento, C_PROVINCIA AS provincia, C_DISTRITO AS distrito "
            + " FROM TAB_AMBITO";
    String sql2 = "SELECT * FROM TAB_AMBITO";
    SQLQuery query = getSession().createSQLQuery(sql);
    query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    return new ArrayList(query.list());
}

---

From source file:com.bryan.crud.dao.impl.UserDaoImpl.java

@Override
public ArrayList allUsers() {
    String sql = "SELECT A.N_USUARIO_PK AS id, A.C_APELLIDO_MATERNO AS apellidoMaterno, A.C_APELLIDO_PATERNO AS apellidoPaterno, A.C_NOMBRE AS nombre"
            + " , B.C_NOMBRE_PERFIL as nombrePerfil, B.N_ESTADO as estado" + " FROM TAB_USUARIO A"
            + " LEFT OUTER JOIN TAB_PERFIL B ON A.N_PERFIL = B.N_PERFIL_PK";
    SQLQuery query = getSession().createSQLQuery(sql);
    query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    ArrayList list = new ArrayList(query.list());
    return list;/*from  www .j  av a2 s  .c  om*/
}

---

From source file:com.cms.dao.AppParamsDAO.java

License:Open Source License

public List<AppParamsDTO> getListProviderFromMineName(String mineName, Map<String, String> map) {
    List<AppParamsDTO> listAppParams = null;
    String taxAuthority = map.get("taxAuthority");
    String startFromDate = map.get("startFromDate");
    String endFromDate = map.get("endFromDate");
    String startToDate = map.get("startToDate");
    String endToDate = map.get("endToDate");
    String fromDateRegister = map.get("fromDateRegister");
    String toDateRegister = map.get("toDateRegister");
    StringBuilder sb = new StringBuilder();
    sb.append("      SELECT    PAR_ID parId,   ");
    sb.append("              DESCRIPTION description,   ");
    sb.append("              PAR_CODE parCode,   ");
    sb.append("              PAR_NAME parName,   ");
    sb.append("              PAR_ORDER parOrder,   ");
    sb.append("              PAR_TYPE parType,   ");
    sb.append("              STATUS status   ");
    sb.append("        FROM APP_PARAMS a   ");
    sb.append("        WHERE a.PAR_TYPE = 'PROVIDER' ");
    sb.append("                     AND lower(a.PAR_CODE) = ANY   ");
    sb.append("              (SELECT DISTINCT lower(ti.PROVIDER)   ");
    sb.append("                 FROM TERM_INFORMATION ti   ");
    sb.append(// w  w  w  .j  a va 2  s.c om
            "                 LEFT JOIN CUSTOMER_STATUS cs on ti.TAX_CODE=cs.TAX_CODE and ti.MINE_NAME = cs.MINE_NAME ");
    sb.append("                 JOIN CUSTOMER c on c.TAX_CODE = ti.TAX_CODE ");
    sb.append("                 WHERE ti.IS_CONTACT_INFO is null AND cs.TAX_CODE IS NULL   ");
    sb.append("                 AND ti.MINE_NAME = ANY (:mineName)   ");

    if (!DataUtil.isStringNullOrEmpty(taxAuthority)) {
        sb.append("         and c.TAX_AUTHORITY = ANY(:taxAuthority)   ");
    }

    if (!DataUtil.isStringNullOrEmpty(startFromDate)) {
        sb.append("         and ti.START_TIME >= TO_DATE(:startFromDate,'dd/MM/yyyy') - 1   ");
    }
    if (!DataUtil.isStringNullOrEmpty(endFromDate)) {
        sb.append("         and ti.START_TIME <= TO_DATE(:endFromDate,'dd/MM/yyyy')   + 1 ");
    }
    if (!DataUtil.isStringNullOrEmpty(startToDate)) {
        sb.append("         and ti.END_TIME >= TO_DATE(:startToDate,'dd/MM/yyyy') - 1    ");
    }
    if (!DataUtil.isStringNullOrEmpty(endToDate)) {
        sb.append("         and ti.END_TIME <= TO_DATE(:endToDate,'dd/MM/yyyy') + 1   ");
    }
    if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) {
        sb.append("   AND ti.DATE_REGISTER >= TO_DATE(:fromDateRegister,'dd/MM/yyyy') - 1 ");
    }
    if (!DataUtil.isStringNullOrEmpty(toDateRegister)) {
        sb.append("   AND ti.DATE_REGISTER <= TO_DATE(:toDateRegister,'dd/MM/yyyy') + 1");
    }
    sb.append("              )   ");
    sb.append("        ORDER BY a.PAR_CODE asc   ");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sb.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class));
        query.addScalar("parId", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("parCode", new StringType());
        query.addScalar("parName", new StringType());
        query.addScalar("parOrder", new StringType());
        query.addScalar("parType", new StringType());
        query.addScalar("status", new StringType());
        query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
        if (!DataUtil.isStringNullOrEmpty(taxAuthority)) {
            query.setParameterList("taxAuthority", DataUtil.parseInputListString(taxAuthority));
        }
        if (!DataUtil.isStringNullOrEmpty(startFromDate)) {
            query.setString("startFromDate", startFromDate);
        }
        if (!DataUtil.isStringNullOrEmpty(endFromDate)) {
            query.setString("endFromDate", endFromDate);
        }
        if (!DataUtil.isStringNullOrEmpty(startToDate)) {
            query.setString("startToDate", startToDate);
        }
        if (!DataUtil.isStringNullOrEmpty(endToDate)) {
            query.setString("endToDate", endToDate);
        }
        if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) {
            query.setString("fromDateRegister", fromDateRegister);
        }
        if (!DataUtil.isStringNullOrEmpty(toDateRegister)) {
            query.setString("toDateRegister", toDateRegister);
        }
        listAppParams = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return listAppParams;
}

---

From source file:com.cms.dao.AppParamsDAO.java

License:Open Source License

public List<AppParamsDTO> getProviderFromStaffCode(String staffCode, String mineName) {
    List<AppParamsDTO> lstCategoryList = null;
    StringBuilder sb = new StringBuilder();
    sb.append("   SELECT    PAR_ID parId,   ");
    sb.append("           DESCRIPTION description,   ");
    sb.append("           PAR_CODE parCode,   ");
    sb.append("           PAR_NAME parName,   ");
    sb.append("           PAR_ORDER parOrder,   ");
    sb.append("           PAR_TYPE parType,   ");
    sb.append("           STATUS status   ");
    sb.append("        FROM APP_PARAMS a   ");
    sb.append("    WHERE        a.PAR_TYPE       = 'PROVIDER' ");
    sb.append("         AND a.STATUS = '1' ");
    sb.append("         AND lower(a.PAR_CODE) IN ");
    sb.append("      ( SELECT DISTINCT lower(ti.provider) ");
    sb.append("        FROM TERM_INFORMATION ti ");
    sb.append("        WHERE ti.IS_CONTACT_INFO is NULL AND exists ");
    sb.append("             (   SELECT DISTINCT cs.tax_code ");
    sb.append("                 FROM customer_status cs ");
    sb.append("                 WHERE 1 = 1 ");
    sb.append("                 AND cs.tax_code = ti.tax_code  ");
    if (!StringUtils.isStringNullOrEmpty(staffCode)) {
        sb.append("             AND cs.staff_code = :staffCode ");
    } else { //Add 15/04/2017 Them dieu kien neu la admin
        return getProviderFromMineName(mineName);
    }//www . j  ava2  s. c o m
    if (!StringUtils.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sb.append("             AND cs.mine_name IN (:mineName) ");
        } else {
            sb.append("             AND cs.mine_name = :mineName ");
        }
    }
    sb.append("             ) ");
    sb.append("      ) ");
    try {
        SQLQuery query = getSession().createSQLQuery(sb.toString());
        //            if (!DataUtil.isStringNullOrEmpty(mineName)) {
        //                
        //                query.setParameter("mineName", mineName);
        //            }
        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!StringUtils.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }
        query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class));
        query.addScalar("parId", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("parCode", new StringType());
        query.addScalar("parName", new StringType());
        query.addScalar("parOrder", new StringType());
        query.addScalar("parType", new StringType());
        query.addScalar("status", new StringType());
        lstCategoryList = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstCategoryList;
}

---

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