Example usage for org.hibernate.transform Transformers aliasToBean

List of usage examples for org.hibernate.transform Transformers aliasToBean

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.transform.*
  5. Transformers
  6. aliasToBean

Introduction

In this page you can find the example usage for org.hibernate.transform Transformers aliasToBean.

Prototype

public static ResultTransformer aliasToBean(Class target)

Source Link

Document

Creates a resulttransformer that will inject aliased values into instances of Class via property methods or fields.

Usage

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

License:Open Source License

public List<ObjectsDTO> getListObjectByRole(RolesDTO role) {
    List<ObjectsDTO> lstObjects;
    StringBuilder sql = new StringBuilder();
    //        List lstParams = new ArrayList();
    sql.append("     SELECT DISTINCT a.OBJECT_ID objectId, ");
    sql.append("                     a.CODE code, ");
    sql.append("                     a.NAME name,");
    sql.append("                     a.DESCRIPTION description, ");
    sql.append("                     a.OBJECT_TYPE objectType, ");
    sql.append("                     a.STATUS status, ");
    sql.append("                     a.URL url, ");
    sql.append("                     b.ID roleObjectId ");
    sql.append("        FROM OBJECTS a ");
    sql.append("        JOIN ROLE_OBJECTS b ");
    sql.append("             ON b.OBJECT_ID = a.OBJECT_ID ");
    sql.append("            AND b.ROLE_ID = ? ");
    try {/*w  ww. ja va 2  s .co m*/
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(ObjectsDTO.class));
        query.addScalar("objectId", new StringType());
        query.addScalar("code", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("objectType", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("url", new StringType());
        query.addScalar("roleObjectId", new StringType());

        query.setParameter(0, role.getRoleId());

        lstObjects = query.list();
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
    return lstObjects;
}

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

License:Open Source License

/**
 * Lay danh sach vai tro du nhan vien/*  ww w . ja v a  2s.  c o m*/
 *
 * @param staffId
 * @return
 */
public List<RolesDTO> getListRolesByStaffId(String staffId) {
    List<RolesDTO> lstRoles;
    StringBuilder sql = new StringBuilder();
    sql.append("     SELECT DISTINCT a.ROLE_ID roleId, ");
    sql.append("                     a.CODE code, ");
    sql.append("                     a.NAME name, ");
    sql.append("                     a.DESCRIPTION description, ");
    sql.append("                     a.STATUS status, ");
    sql.append("                     b.MAP_ID mapId ");
    sql.append("        FROM roles a ");
    sql.append("        JOIN MAP_STAFF_ROLES b ");
    sql.append("                  ON b.ROLE_ID   = a.ROLE_ID ");
    sql.append("                 AND b.STAFF_ID = ? ");
    try {
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(RolesDTO.class));
        query.addScalar("roleId", new StringType());
        query.addScalar("code", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("mapId", new StringType());

        query.setParameter(0, staffId);

        lstRoles = query.list();
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
    return lstRoles;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListProvinces() {
    List<TaxAuthorityDTO> lstTaxAuthorities;
    //        List params = new ArrayList();
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("       SELECT a.MA_CQT maCqt, ");
    sqlQuery.append("         a.TEN_CQT tenCqt, ");
    sqlQuery.append("         a.MA_QUAN_HUYEN maQuanHuyen, ");
    sqlQuery.append("         a.MA_TINH maTinh, ");
    sqlQuery.append("         a.STATUS status, ");
    sqlQuery.append("         a.MA_MST maMST ");
    sqlQuery.append("       FROM tax_authority a ");
    sqlQuery.append("       WHERE SUBSTR(a.MA_CQT,4,2) ='00' ");

    try {/* ww w .  jav a2s .c om*/
        SQLQuery query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("maCqt", new StringType());
        query.addScalar("tenCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("maMST", new StringType());

        //Truyen cac tham so truyen vao de thuc hien tim kiem
        //            for (int i = 0; i < params.size(); i++) {
        //                query.setParameter(i, params.get(i));
        //            }
        //Day du lieu ra danh sach doi tuong
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
        lstTaxAuthorities = null;
    }
    return lstTaxAuthorities;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineName(String mineName, Map<String, String> map) {
    String startFromDate = map.get("startFromDate");
    String provider = map.get("provider");
    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");
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("       SELECT ID id,   ");
    sqlQuery.append("         MA_CQT maCqt,   ");
    sqlQuery.append("         MA_QUAN_HUYEN maQuanHuyen,   ");
    sqlQuery.append("         MA_TINH maTinh,   ");
    sqlQuery.append("         STATUS status,   ");
    sqlQuery.append("         TEN_CQT tenCqt   ");
    sqlQuery.append("       FROM TAX_AUTHORITY ta   ");
    sqlQuery.append("       WHERE ta.MA_CQT = ANY   ");
    sqlQuery.append("         ( SELECT DISTINCT c.TAX_AUTHORITY   ");
    sqlQuery.append("         FROM CUSTOMER c   ");
    sqlQuery.append("         JOIN    TERM_INFORMATION ti ON ti.TAX_CODE = c.TAX_CODE ");
    sqlQuery.append("           WHERE ti.IS_CONTACT_INFO is NULL AND ti.MINE_NAME = ANY (:mineName)   ");
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("         and lower(ti.PROVIDER) = ANY (:provider)   ");
    }/* w  w  w.j  a  v  a2  s.co m*/
    if (!DataUtil.isStringNullOrEmpty(startFromDate)) {
        sqlQuery.append("         and ti.START_TIME >= TO_DATE(:startFromDate,'dd/MM/yyyy') - 1   ");
    }
    if (!DataUtil.isStringNullOrEmpty(endFromDate)) {
        sqlQuery.append("         and ti.START_TIME <= TO_DATE(:endFromDate,'dd/MM/yyyy') + 1    ");
    }
    if (!DataUtil.isStringNullOrEmpty(startToDate)) {
        sqlQuery.append("         and ti.END_TIME >= TO_DATE(:startToDate,'dd/MM/yyyy') - 1   ");
    }
    if (!DataUtil.isStringNullOrEmpty(endToDate)) {
        sqlQuery.append("         and ti.END_TIME <= TO_DATE(:endToDate,'dd/MM/yyyy')   + 1   ");
    }
    if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) {
        sqlQuery.append("   AND ti.DATE_REGISTER >= TO_DATE(:fromDateRegister,'dd/MM/yyyy') - 1 ");
    }
    if (!DataUtil.isStringNullOrEmpty(toDateRegister)) {
        sqlQuery.append("   AND ti.DATE_REGISTER <= TO_DATE(:toDateRegister,'dd/MM/yyyy')  + 1");
    }
    sqlQuery.append("       AND NOT " + "                EXISTS ( " + "                    SELECT "
            + "                        cs.tax_code, " + "                        cs.mine_name "
            + "                    FROM " + "                        customer_status cs"
            + "                    WHERE " + "                            cs.tax_code = ti.tax_code "
            + "                        AND " + "                            cs.mine_name = ti.mine_name "
            + "                ) ");
    sqlQuery.append("       )  ");
    sqlQuery.append("       ORDER BY ta.TEN_CQT asc ");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());
        query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
        if (!DataUtil.isStringNullOrEmpty(provider)) {
            query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase()));
        }
        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);
        }
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineName(String mineName, String staffCode) {
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    if (DataUtil.isStringNullOrEmpty(staffCode)) {
        return getListTaxAuthorityFromMineName(mineName);
    }//from  w  w  w . j a  v  a2s. co m
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM customer_status cs  ");
    sqlQuery.append("                WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("             AND cs.mine_name IN (:mineName) ");
        } else {
            sqlQuery.append("             AND cs.mine_name = :mineName ");
        }
        //            sqlQuery.append("            AND cs.mine_name = :mineName  ");
    }
    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sqlQuery.append("                AND cs.staff_code  = :staffCode ");
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }

        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineNameAndStaffCodeAndProvider(String mineName,
        String staffCode, String provider, String status) {
    if (DataUtil.isStringNullOrEmpty(staffCode)) {
        return getListTaxAuthorityFromMineNameAndProvider(mineName, provider, status);
    }/*from  ww  w . j  a  va2  s.  com*/
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM customer_status cs  ");
    if (!DataUtil.isStringNullOrEmpty(provider) || !DataUtil.isStringNullOrEmpty(mineName)) {
        sqlQuery.append("            JOIN term_information ti ON ti.TAX_CODE = cs.TAX_CODE ");
    }
    sqlQuery.append("                WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("            AND cs.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("            AND cs.mine_name = :mineName  ");
        }
    }
    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sqlQuery.append("                AND cs.staff_code  = :staffCode ");
    }
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("            AND lower(ti.provider) = ANY (:provider)  ");
    }
    if (!DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("            AND cs.status = ANY (:status)  ");
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        System.out.println(sqlQuery.toString());
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(provider)) {
            query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase()));
        }
        if (!DataUtil.isStringNullOrEmpty(status)) {
            query.setParameterList("status", DataUtil.parseInputListString(status));
        }
        if (!DataUtil.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }

        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineName(String mineName) {
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append(/* ww w . j  a v a 2 s  .c om*/
            "                FROM CUSTOMER cs INNER JOIN TERM_INFORMATION ti ON ti.TAX_CODE = cs.TAX_CODE ");
    sqlQuery.append("                WHERE 1=1 AND ti.IS_CONTACT_INFO is NULL ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("            AND ti.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("            AND ti.mine_name = :mineName  ");
        }
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineNameAndProvider(String mineName, String provider,
        String status) {//from   w  w  w .  j a v  a2 s .  co m
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT c.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM CUSTOMER c ");
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                INNER JOIN TERM_INFORMATION ti ON ti.TAX_CODE = c.TAX_CODE ");
    }
    if (!DataUtil.isStringNullOrEmpty(mineName) || !DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("                INNER JOIN CUSTOMER_STATUS cs ON c.TAX_CODE = cs.TAX_CODE ");
    }
    sqlQuery.append("                   WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                   AND ti.IS_CONTACT_INFO is NULL ");
    }
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("                   AND cs.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("                   AND cs.mine_name = :mineName  ");
        }
    }
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                   AND lower(ti.provider) = ANY (:provider)  ");
    }
    if (!DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("                   AND cs.STATUS = :status  ");
    }
    sqlQuery.append("                ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(provider)) {
            query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase()));
        }
        if (!DataUtil.isStringNullOrEmpty(status)) {
            query.setParameter("status", status);
        }
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

From source file:com.consult.app.dao.impl.CargoMessageDaoImpl.java

/**
 * Common Search cargo, used by 274 Version!
 * This supports multiple truck length//from   w w  w .j av a2s . com
 * 
 */
private List<Object> getMultipleTruckLengthSearch(SearchCargoRequest req, City endCity) {

    Long today = TimeUtils.getStartOfDay(System.currentTimeMillis());
    StringBuilder sb = new StringBuilder(String.format(Constant.CARGO_SEARCH_MULTIPLE_TRUCKLENGTH, today));

    if (Double.valueOf(req.getTruckLength()) >= 0) {
        sb.append(" and find_in_set(").append(req.getTruckLength()).append(",message.truck_length_set)");
    }

    if (req.getTruckType() >= 0) {
        sb.append(" and message.truck_type=").append(req.getTruckType());
    }

    if (req.getWeightRange() > 0) {
        sb.append(" and message.cargo_weight_range=").append(req.getWeightRange());
    }

    sb.append(" and (message.start=").append(req.getStart()).append(" or message.start_father=")
            .append(req.getStart()).append(" or message.start_grand=").append(req.getStart())
            .append(") and (message.end=").append(endCity.getId()).append(" or message.end_father=")
            .append(endCity.getId()).append(" or message.end_grand=").append(endCity.getId()).append(" or 0=")
            .append(endCity.getId()).append(")");

    sb.append(" and message.type>=? and message.type<4 order by message.update_time desc");

    Long triggerTime = req.getAfter();
    if (triggerTime.equals(Long.MAX_VALUE)) {
        triggerTime = System.currentTimeMillis();
    }
    CargoMessageInterceptor inter = new CargoMessageInterceptor();
    int count = req.getCount();
    //      for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) {
    Session session = null;
    try {
        //            Long messageId, int type,
        //            double weight, double capacity, int truckType,
        //            String contact, String telephone, int start, int end, 
        //            String description, long updateTime, double truckLength,
        //            String companyName, String companyAddress, String landlines, 
        //            String picture, int avatarAuthenticate, Long userId
        inter.setShardCriteria(System.currentTimeMillis());
        session = sessionFactory.openSession(inter);
        Query query = session.createSQLQuery(sb.toString()).addScalar("messageId", StandardBasicTypes.LONG)
                .addScalar("type", StandardBasicTypes.INTEGER).addScalar("weight", StandardBasicTypes.DOUBLE)
                .addScalar("capacity", StandardBasicTypes.DOUBLE)
                .addScalar("truckType", StandardBasicTypes.INTEGER)
                .addScalar("contact", StandardBasicTypes.STRING)
                .addScalar("telephone", StandardBasicTypes.STRING)
                .addScalar("start", StandardBasicTypes.INTEGER).addScalar("end", StandardBasicTypes.INTEGER)
                .addScalar("description", StandardBasicTypes.STRING)
                .addScalar("updateTime", StandardBasicTypes.LONG)
                .addScalar("truckLength", StandardBasicTypes.DOUBLE)
                .addScalar("companyName", StandardBasicTypes.STRING)
                .addScalar("companyAddress", StandardBasicTypes.STRING)
                .addScalar("landlines", StandardBasicTypes.STRING)
                .addScalar("picture", StandardBasicTypes.STRING)
                .addScalar("avatarAuthenticate", StandardBasicTypes.INTEGER)
                .addScalar("userId", StandardBasicTypes.LONG)
                .addScalar("licenseAuthenticate", StandardBasicTypes.INTEGER)
                .addScalar("cargoType", StandardBasicTypes.INTEGER)
                .addScalar("score", StandardBasicTypes.DOUBLE)
                .addScalar("orderCount", StandardBasicTypes.INTEGER)
                .addScalar("charges", StandardBasicTypes.INTEGER)
                .addScalar("messageCount", StandardBasicTypes.LONG)
                .addScalar("truckLengthSet", StandardBasicTypes.STRING).setResultTransformer(
                        Transformers.aliasToBean(com.consult.app.response.cargo.CargoMessageItem.class));
        query.setLong(0, req.getBefore());
        query.setLong(1, triggerTime);
        query.setInteger(2, Constant.TYPE_NORMAL);
        query.setFirstResult(0);
        query.setMaxResults(count);
        @SuppressWarnings("unchecked")
        List<Object> tmpList = query.list();
        //            if(tmpList != null && tmpList.size() > 0) {
        //               list.addAll(tmpList);
        //               if(tmpList.size() >= count) {
        //                  tx.commit();
        //                  return list;
        //               }
        //               count -= tmpList.size();
        ////               min = metaList.get(metaList.size() - 1).getUpdateTime();
        //            }
        //            if(inter.isFinishSearch(req.getBefore())) {
        //               tx.commit();
        //               return list;
        //            }
        //            triggerTime = inter.getTriggerTime();
        return tmpList;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    } finally {
        if (session != null) {
            session.close();
            session = null;
        }
    }
}

From source file:com.consult.app.dao.impl.CargoMessageDaoImpl.java

/**
 * Search Nearby cargoMessages by multiple trucklength
 * @param req/* ww w  .  ja  va2  s .c  o  m*/
 * @param endCity
 * @return
 */
private List<Object> getNearbyMultipleTruckLengthSearch(SearchCargoRequest req, City endCity) {
    //      List<Object> list = new ArrayList<Object>();

    Long today = TimeUtils.getStartOfDay(System.currentTimeMillis());
    StringBuilder sb = new StringBuilder(
            String.format(Constant.NEARBY_CARGO_SEARCH_MULTIPLE_TRUCKLENGTH, today));

    if (Double.valueOf(req.getTruckLength()) >= 0) {
        sb.append(" and find_in_set(").append(req.getTruckLength()).append(",message.truck_length_set)");
    }

    if (req.getTruckType() >= 0) {
        sb.append(" and message.truck_type=").append(req.getTruckType());
    }

    if (req.getWeightRange() > 0) {
        sb.append(" and message.cargo_weight_range=").append(req.getWeightRange());
    }

    sb.append(" and city.id=").append(req.getStart()).append(
            " and (find_in_set(message.start, city.near_by) or find_in_set(message.start_father, city.near_by))")
            .append(" and (message.end=").append(endCity.getId()).append(" or message.end_father=")
            .append(endCity.getId()).append(" or message.end_grand=").append(endCity.getId()).append(" or 0=")
            .append(endCity.getId()).append(")");

    sb.append(" and message.type>=? and message.type<4 order by message.update_time desc");

    Long triggerTime = req.getAfter();
    if (triggerTime.equals(Long.MAX_VALUE)) {
        triggerTime = System.currentTimeMillis();
    }
    CargoMessageInterceptor inter = new CargoMessageInterceptor();
    int count = req.getCount();
    //      for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) {
    Session session = null;
    try {
        //            Long messageId, int type,
        //            double weight, double capacity, int truckType,
        //            String contact, String telephone, int start, int end, 
        //            String description, long updateTime, double truckLength,
        //            String companyName, String companyAddress, String landlines, 
        //            String picture, int avatarAuthenticate, Long userId
        inter.setShardCriteria(triggerTime);
        session = sessionReadFactory.openSession(inter);
        Query query = session.createSQLQuery(sb.toString()).addScalar("messageId", StandardBasicTypes.LONG)
                .addScalar("type", StandardBasicTypes.INTEGER).addScalar("weight", StandardBasicTypes.DOUBLE)
                .addScalar("capacity", StandardBasicTypes.DOUBLE)
                .addScalar("truckType", StandardBasicTypes.INTEGER)
                .addScalar("contact", StandardBasicTypes.STRING)
                .addScalar("telephone", StandardBasicTypes.STRING)
                .addScalar("start", StandardBasicTypes.INTEGER).addScalar("end", StandardBasicTypes.INTEGER)
                .addScalar("description", StandardBasicTypes.STRING)
                .addScalar("updateTime", StandardBasicTypes.LONG)
                .addScalar("truckLength", StandardBasicTypes.DOUBLE)
                .addScalar("companyName", StandardBasicTypes.STRING)
                .addScalar("companyAddress", StandardBasicTypes.STRING)
                .addScalar("landlines", StandardBasicTypes.STRING)
                .addScalar("picture", StandardBasicTypes.STRING)
                .addScalar("avatarAuthenticate", StandardBasicTypes.INTEGER)
                .addScalar("userId", StandardBasicTypes.LONG)
                .addScalar("licenseAuthenticate", StandardBasicTypes.INTEGER)
                .addScalar("cargoType", StandardBasicTypes.INTEGER)
                .addScalar("score", StandardBasicTypes.DOUBLE)
                .addScalar("orderCount", StandardBasicTypes.INTEGER)
                .addScalar("charges", StandardBasicTypes.INTEGER)
                .addScalar("messageCount", StandardBasicTypes.LONG)
                .addScalar("truckLengthSet", StandardBasicTypes.STRING).setResultTransformer(
                        Transformers.aliasToBean(com.consult.app.response.cargo.CargoMessageItem.class));
        query.setLong(0, req.getBefore());
        query.setLong(1, triggerTime);
        query.setInteger(2, Constant.TYPE_NORMAL);
        query.setFirstResult(0);
        query.setMaxResults(count);
        @SuppressWarnings("unchecked")
        List<Object> tmpList = query.list();
        //            if(tmpList != null && tmpList.size() > 0) {
        //               list.addAll(tmpList);
        //               if(tmpList.size() >= count) {
        //                  tx.commit();
        //                  return list;
        //               }
        //               count -= tmpList.size();
        ////               min = metaList.get(metaList.size() - 1).getUpdateTime();
        //            }
        //            if(inter.isFinishSearch(req.getBefore())) {
        //               tx.commit();
        //               return list;
        //            }
        //            triggerTime = inter.getTriggerTime();
        return tmpList;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    } finally {
        if (session != null) {
            session.close();
            session = null;
        }
    }
    //      }
}