List of usage examples for org.hibernate SQLQuery setMaxResults
@Override
Query<R> setMaxResults(int maxResult);
From source file:ch.algotrader.dao.AbstractDao.java
License:Open Source License
protected SQLQuery prepareSQLQuery(final LockOptions lockOptions, final String queryString, final int maxResults) { Session currentSession = getCurrentSession(); SQLQuery query = currentSession.createSQLQuery(queryString); if (lockOptions != null) { query.setLockOptions(lockOptions); }/* w w w . j a v a 2 s .co m*/ if (maxResults > 0) { query.setMaxResults(maxResults); } return query; }
From source file:ch.algotrader.dao.AbstractDao.java
License:Open Source License
protected SQLQuery prepareSQLQuery(final LockOptions lockOptions, final String queryString, final int maxResults, final Object... params) { Session currentSession = getCurrentSession(); SQLQuery query = currentSession.createSQLQuery(queryString); if (lockOptions != null) { query.setLockOptions(lockOptions); }//from w w w . j a v a2 s . c o m if (maxResults > 0) { query.setMaxResults(maxResults); } applyParameters(query, params); return query; }
From source file:ch.algotrader.dao.AbstractDao.java
License:Open Source License
protected SQLQuery prepareSQLQuery(final LockOptions lockOptions, final String queryString, final int maxResults, final NamedParam... params) { Session currentSession = getCurrentSession(); SQLQuery query = currentSession.createSQLQuery(queryString); if (lockOptions != null) { query.setLockOptions(lockOptions); }/* w w w . j a va2 s. c o m*/ if (maxResults > 0) { query.setMaxResults(maxResults); } applyParameters(query, params); return query; }
From source file:com.abssh.util.GenericDao.java
License:Apache License
/** * ?SQL//from w ww.ja v a2s . c o m * * @param pageSize * ??,0? * @param pageNo * ??1? * @param sql * SQL * @param param * ? * @return ??object */ @SuppressWarnings("unchecked") public List findList(int pageSize, int pageNo, String sql, Object... param) { if (pageSize <= 0 || pageNo <= 0) { // ? return findList(sql, param); } SQLQuery query = getSession().createSQLQuery(sql); query.setFirstResult(((pageNo - 1) * pageSize)); query.setMaxResults(pageSize); if (param != null && param.length > 0) { for (int i = 0; i < param.length; i++) { query.setParameter(i, param[i]); } } return query.list(); }
From source file:com.abssh.util.GenericDao.java
License:Apache License
/** * SQL/*from ww w. j a v a 2s.c om*/ * * @param pageSize * ??,0? * @param pageNo * ??1? * @param sql * SQL * @param clazz * ?list * @param param * ? * @return */ @SuppressWarnings("unchecked") public List findList(int pageSize, int pageNo, String sql, Class clazz, Object... param) { if (pageSize <= 0 || pageNo <= 0) { // ? return findList(sql, clazz, param); } SQLQuery query = getSession().createSQLQuery(sql).addEntity(clazz); query.setFirstResult(((pageNo - 1) * pageSize)); query.setMaxResults(pageSize); if (param != null && param.length > 0) { for (int i = 0; i < param.length; i++) { query.setParameter(i, param[i]); } } return query.list(); }
From source file:com.cms.dao.CustomerDAO.java
License:Open Source License
public List<CustomerDTO> searchCustomers(CustomerDTO customer, int maxResult) { List<CustomerDTO> lstCustomers; List params = new ArrayList(); StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append(" SELECT DISTINCT a.TAX_CODE taxCode, "); sqlQuery.append(" a.NAME name, "); sqlQuery.append(" a.REPRESENTATIVE_NAME representativeName, "); sqlQuery.append(" a.TEL_NUMBER telNumber, "); sqlQuery.append(" a.EMAIL email, "); sqlQuery.append(" a.OFFICE_ADDRESS officeAddress, "); sqlQuery.append(" a.TAX_AUTHORITY taxAuthority, "); //Neu truong so dien thoai duoc dien vao thi them dieu kien // if (!DataUtil.isStringNullOrEmpty(customer.getStaffId()) // || !DataUtil.isStringNullOrEmpty(customer.getStatus())) { // sqlQuery.append(" b.MINE_NAME mineName, "); // } else { sqlQuery.append(" d.MINE_NAME mineName, "); // }// www .ja v a 2 s. com // sqlQuery.append(" e.notes notes, "); // sqlQuery.append(" to_char(e.create_date,'dd/MM/yyyy HH24:Mi:ss') createDate, "); // sqlQuery.append(" e.status status "); //25/04/2017: Lay thong tin tu bang customer status sqlQuery.append(" d.service service, "); sqlQuery.append(" b.last_notes notes, "); sqlQuery.append(" to_char(b.last_updated,'dd/MM/yyyy HH24:Mi:ss') createDate, "); sqlQuery.append(" b.status status "); sqlQuery.append(" FROM CUSTOMER a "); sqlQuery.append(" JOIN TERM_INFORMATION d "); sqlQuery.append(" ON d.TAX_CODE = a.TAX_CODE "); //Dich vu if (!DataUtil.isStringNullOrEmpty(customer.getService())) { sqlQuery.append(" AND d.Service = :service "); } //Danh sach khai thac if (!DataUtil.isStringNullOrEmpty(customer.getMineName())) { sqlQuery.append(" AND d.MINE_NAME = :mineName "); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(customer.getProvider())) { sqlQuery.append(" AND lower(d.PROVIDER) in (:provider) "); } if (!DataUtil.isStringNullOrEmpty(customer.getStaffId()) || !DataUtil.isStringNullOrEmpty(customer.getStatus()) || !DataUtil.isStringNullOrEmpty(customer.getCustCareHistoryCreatedDate())) { sqlQuery.append(" JOIN Customer_status b "); sqlQuery.append(" ON b.TAX_CODE = a.TAX_CODE "); } else { sqlQuery.append(" LEFT JOIN Customer_status b "); sqlQuery.append(" ON b.TAX_CODE = a.TAX_CODE "); } sqlQuery.append(" AND b.TAX_CODE = d.TAX_CODE "); // if (!DataUtil.isStringNullOrEmpty(customer.getService())) { sqlQuery.append(" AND b.Service = d.service "); // } // if (!DataUtil.isStringNullOrEmpty(customer.getMineName())) { sqlQuery.append(" AND b.MINE_NAME = d.mine_name "); // } //Trang thai cua bang khach hang - dich vu if (!DataUtil.isStringNullOrEmpty(customer.getStatus())) { sqlQuery.append(" AND b.STATUS = :status "); } //Them ngay cham soc khach hang if (!DataUtil.isStringNullOrEmpty(customer.getCustCareHistoryCreatedDate())) { sqlQuery.append(" AND TO_CHAR(b.LAST_UPDATED,'dd/MM/yyyy') = :createDate "); } // //Neu truong trang thai duoc dien vao thi them dieu kien // if (!DataUtil.isStringNullOrEmpty(customer.getStatus()) // || !DataUtil.isStringNullOrEmpty(customer.getStaffId()) // || !DataUtil.isStringNullOrEmpty(customer.getMineName())) { // } //Neu truong email duoc dien vao thi them dieu kien if (!DataUtil.isStringNullOrEmpty(customer.getEmail()) || !DataUtil.isStringNullOrEmpty(customer.getTelNumber())) { sqlQuery.append(" LEFT JOIN CUSTOMER_CONTACT c "); sqlQuery.append(" ON c.TAX_CODE = a.TAX_CODE "); } sqlQuery.append(" WHERE 1=1 "); //MST if (!DataUtil.isStringNullOrEmpty(customer.getTaxCode())) { sqlQuery.append(" AND lower(a.TAX_CODE) LIKE lower(:taxCode) "); } //Staff id if (!DataUtil.isStringNullOrEmpty(customer.getStaffId())) { sqlQuery.append(" AND b.STAFF_ID = :staffId "); } //Ten cong ty if (!DataUtil.isStringNullOrEmpty(customer.getName())) { sqlQuery.append(" AND lower(a.NAME) LIKE lower(:custName) "); } //Dia chi dang ky kinh doanh if (!DataUtil.isStringNullOrEmpty(customer.getOfficeAddress())) { sqlQuery.append(" AND lower(a.OFFICE_ADDRESS) LIKE lower(:officeAdd) "); } if (!DataUtil.isStringNullOrEmpty(customer.getEmail())) { sqlQuery.append( " AND ( (lower(a.EMAIL) like :email1) OR (lower(c.EMAIL) like :email2) OR (lower(d.EMAIL) like :email3))"); } if (!DataUtil.isStringNullOrEmpty(customer.getTelNumber())) { sqlQuery.append( " AND ( (lower(a.TEL_NUMBER) like :tel1) OR (lower(c.TEL_NUMBER) LIKE :tel2 ) OR (lower(d.PHONE) LIKE :tel3 ))"); } //Neu ma tinh duoc dien tim kiem theo ma tinh if (!DataUtil.isStringNullOrEmpty(customer.getTaxAuthority())) { sqlQuery.append(" AND a.TAX_AUTHORITY in (:taxAuthority) "); } sqlQuery.append(" ORDER BY To_date(createDate,'dd/MM/yyyy HH24:Mi:ss') desc, taxCode"); try { SQLQuery query = getSession().createSQLQuery(sqlQuery.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(CustomerDTO.class)); query.addScalar("taxCode", new StringType()); query.addScalar("name", new StringType()); query.addScalar("representativeName", new StringType()); query.addScalar("telNumber", new StringType()); query.addScalar("email", new StringType()); query.addScalar("officeAddress", new StringType()); query.addScalar("taxAuthority", new StringType()); query.addScalar("mineName", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("createDate", new StringType()); query.addScalar("status", new StringType()); query.addScalar("service", new StringType()); // query.addScalar("provider", new StringType()); //MST if (!DataUtil.isStringNullOrEmpty(customer.getTaxCode())) { query.setParameter("taxCode", "%" + customer.getTaxCode() + "%"); } //Staff id if (!DataUtil.isStringNullOrEmpty(customer.getStaffId())) { query.setParameter("staffId", customer.getStaffId()); } //Ten cong ty if (!DataUtil.isStringNullOrEmpty(customer.getName())) { query.setParameter("custName", "%" + customer.getName() + "%"); } //Dia chi dang ky kinh doanh if (!DataUtil.isStringNullOrEmpty(customer.getOfficeAddress())) { query.setParameter("officeAdd", "%" + customer.getOfficeAddress() + "%"); } //Danh sach khai thac if (!DataUtil.isStringNullOrEmpty(customer.getMineName())) { query.setParameter("mineName", customer.getMineName()); } //Dich vu if (!DataUtil.isStringNullOrEmpty(customer.getService())) { query.setParameter("service", customer.getService()); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(customer.getProvider())) { query.setParameterList("provider", DataUtil.parseInputListString(customer.getProvider().toLowerCase())); } //Trang thai cua bang khach hang - dich vu if (!DataUtil.isStringNullOrEmpty(customer.getStatus())) { query.setParameter("status", customer.getStatus()); } if (!DataUtil.isStringNullOrEmpty(customer.getEmail())) { query.setParameter("email1", "%" + customer.getEmail().toLowerCase() + "%"); query.setParameter("email2", "%" + customer.getEmail().toLowerCase() + "%"); query.setParameter("email3", "%" + customer.getEmail().toLowerCase() + "%"); } //So dien thoai cua bang lich su giao dich if (!DataUtil.isStringNullOrEmpty(customer.getTelNumber())) { query.setParameter("tel1", "%" + customer.getTelNumber() + "%"); query.setParameter("tel2", "%" + customer.getTelNumber() + "%"); query.setParameter("tel3", "%" + customer.getTelNumber() + "%"); } //Neu ma tinh duoc dien tim kiem theo ma tinh if (!DataUtil.isStringNullOrEmpty(customer.getTaxAuthority())) { query.setParameterList("taxAuthority", DataUtil.parseInputListString(customer.getTaxAuthority())); } //Them ngay cham soc khach hang if (!DataUtil.isStringNullOrEmpty(customer.getCustCareHistoryCreatedDate())) { query.setParameter("createDate", customer.getCustCareHistoryCreatedDate()); } if (maxResult != Integer.MAX_VALUE) { query.setMaxResults(maxResult); } //Day du lieu ra danh sach doi tuong lstCustomers = query.list(); } catch (Exception e) { e.printStackTrace(); lstCustomers = null; } return lstCustomers; }
From source file:com.cms.dao.CustomerDAO.java
License:Open Source License
public List<CustomerDTO> getListCustomerWithTermInfo(List<ConditionBean> lstConditions) { String fromStartTime = null;/*from w w w .j av a2 s.c o m*/ String toStartTime = null; String fromEndTime = null; String toEndTime = null; String fromDateRegister = null; String toDateRegister = null; String provider = null; String service = null; String mineName = null; String taxAuthority = null; String maxSearch = null; SQLQuery query; for (ConditionBean c : lstConditions) { if ("service".equalsIgnoreCase(c.getField())) { service = c.getValue(); } if ("provider".equalsIgnoreCase(c.getField())) { provider = c.getValue(); } if ("taxAuthority".equalsIgnoreCase(c.getField())) { taxAuthority = c.getValue(); } if ("mineName".equals(c.getField())) { mineName = c.getValue(); } if ("maxSearch".equals(c.getField())) { maxSearch = c.getValue(); } if ("startTime".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromStartTime = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toStartTime = c.getValue(); } } if ("endTime".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromEndTime = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toEndTime = c.getValue(); } } if ("dateRegister".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromDateRegister = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toDateRegister = c.getValue(); } } } List<CustomerDTO> lstCustomers = null; List params = new ArrayList(); StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append(" SELECT DISTINCT a.TAX_CODE taxCode, "); sqlQuery.append(" a.NAME name, "); sqlQuery.append(" a.REPRESENTATIVE_NAME representativeName, "); sqlQuery.append(" a.TEL_NUMBER telNumber, "); sqlQuery.append(" a.EMAIL email, "); sqlQuery.append(" a.STATUS status, "); sqlQuery.append(" a.OFFICE_ADDRESS officeAddress, "); sqlQuery.append(" d.MINE_NAME mineName, "); sqlQuery.append(" d.SERVICE service, "); sqlQuery.append(" to_char(d.END_TIME,'dd/MM/yyyy') endTime, "); sqlQuery.append(" to_char(d.Start_time,'dd/MM/yyyy') startTime, "); sqlQuery.append(" a.TAX_AUTHORITY taxAuthority "); sqlQuery.append(" FROM CUSTOMER a "); // if (!DataUtil.isStringNullOrEmpty(provider) // || !DataUtil.isStringNullOrEmpty(fromStartTime) // || !DataUtil.isStringNullOrEmpty(toStartTime) // || !DataUtil.isStringNullOrEmpty(fromEndTime) // || !DataUtil.isStringNullOrEmpty(toEndTime) // || !DataUtil.isStringNullOrEmpty(mineName)) { // } sqlQuery.append(" JOIN TERM_INFORMATION d "); sqlQuery.append(" ON d.TAX_CODE = a.TAX_CODE "); sqlQuery.append(" LEFT JOIN CUSTOMER_STATUS b "); sqlQuery.append(" ON b.TAX_CODE = a.TAX_CODE "); sqlQuery.append(" WHERE 1=1 "); sqlQuery.append(" AND b.TAX_CODE IS NULL "); if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { sqlQuery.append(" AND a.TAX_AUTHORITY in (:tax) "); } if (!DataUtil.isStringNullOrEmpty(service)) { sqlQuery.append(" AND d.SERVICE = :service "); // params.add(service); } if (!DataUtil.isStringNullOrEmpty(mineName)) { sqlQuery.append(" AND d.MINE_NAME = :mineName "); // params.add(mineName); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(provider)) { sqlQuery.append(" AND lower(d.PROVIDER) in (:provider) "); } if (!DataUtil.isStringNullOrEmpty(fromStartTime)) { sqlQuery.append(" AND d.START_TIME >= TO_DATE(:fromStartTime,'dd/MM/yyyy') - 1 "); // params.add(fromStartTime); } if (!DataUtil.isStringNullOrEmpty(toStartTime)) { sqlQuery.append(" AND d.START_TIME <= TO_DATE(:toStartTime,'dd/MM/yyyy') + 1 "); // params.add(toStartTime); } if (!DataUtil.isStringNullOrEmpty(fromEndTime)) { sqlQuery.append(" AND d.END_TIME >= TO_DATE(:fromEndTime,'dd/MM/yyyy') - 1 "); // params.add(fromEndTime); } if (!DataUtil.isStringNullOrEmpty(toEndTime)) { sqlQuery.append(" AND d.END_TIME <= TO_DATE(:toEndTime,'dd/MM/yyyy') + 1"); // params.add(toEndTime); } if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { sqlQuery.append(" AND d.DATE_REGISTER >= TO_DATE(:fromDateRegister,'dd/MM/yyyy') - 1 "); // params.add(fromDateRegister); } if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { sqlQuery.append(" AND d.DATE_REGISTER <= TO_DATE(:toDateRegister,'dd/MM/yyyy') + 1 "); // params.add(toDateRegister); } sqlQuery.append(" ORDER BY endTime desc, startTime desc "); try { query = getSession().createSQLQuery(sqlQuery.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(CustomerDTO.class)); query.addScalar("taxCode", new StringType()); query.addScalar("name", new StringType()); query.addScalar("representativeName", new StringType()); query.addScalar("telNumber", new StringType()); query.addScalar("email", new StringType()); query.addScalar("status", new StringType()); query.addScalar("officeAddress", new StringType()); query.addScalar("mineName", new StringType()); query.addScalar("service", new StringType()); query.addScalar("startTime", new StringType()); query.addScalar("endTime", new StringType()); query.addScalar("taxAuthority", new StringType()); // for (int i = 0; i < params.size(); i++) { // query.setParameter(i, params.get(i)); // } //Danh sach khai thac if (!DataUtil.isStringNullOrEmpty(mineName)) { query.setParameter("mineName", Long.parseLong(mineName)); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(provider)) { query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase())); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { query.setParameterList("tax", DataUtil.parseInputListString(taxAuthority)); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(service)) { query.setParameter("service", Long.parseLong(service)); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromStartTime)) { query.setParameter("fromStartTime", fromStartTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toStartTime)) { query.setParameter("toStartTime", toStartTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromEndTime)) { query.setParameter("fromEndTime", fromEndTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toEndTime)) { query.setParameter("toEndTime", toEndTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { query.setParameter("fromDateRegister", fromDateRegister); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { query.setParameter("toDateRegister", toDateRegister); } //Day du lieu ra danh sach doi tuong if (!DataUtil.isStringNullOrEmpty(maxSearch)) { if (DataUtil.isInteger(maxSearch)) { query.setMaxResults(Integer.parseInt(maxSearch)); } } lstCustomers = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCustomers; }
From source file:com.cms.dao.CustomerDAO.java
License:Open Source License
public List<CustomerDTO> getListCustomerFromTermInfoWithoutTaxCodes(List<ConditionBean> lstConditions, List<String> taxCodesExecuted) { String fromStartTime = null;// www . j av a 2 s. c o m String toStartTime = null; String fromEndTime = null; String toEndTime = null; String fromDateRegister = null; String toDateRegister = null; String provider = null; String service = null; String mineName = null; String taxAuthority = null; String maxSearch = null; SQLQuery query; for (ConditionBean c : lstConditions) { if ("service".equalsIgnoreCase(c.getField())) { service = c.getValue(); } if ("provider".equalsIgnoreCase(c.getField())) { provider = c.getValue(); } if ("taxAuthority".equalsIgnoreCase(c.getField())) { taxAuthority = c.getValue(); } if ("mineName".equals(c.getField())) { mineName = c.getValue(); } if ("maxSearch".equals(c.getField())) { maxSearch = c.getValue(); } if ("startTime".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromStartTime = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toStartTime = c.getValue(); } } if ("endTime".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromEndTime = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toEndTime = c.getValue(); } } if ("dateRegister".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromDateRegister = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toDateRegister = c.getValue(); } } } List<CustomerDTO> lstCustomers = null; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append(" SELECT e.taxCode, " + " e.name, " + " e.representativeName, " + " e.telNumber," + " e.email, " + " e.status, " + " e.officeAddress, " + " e.mineName, " + " e.service, " + " to_char(max(e.endTime),'dd/MM/yyyy') endTime, " + " to_char(min(e.startTime),'dd/MM/yyyy') startTime, " + " e.taxAuthority " + "FROM " + " ( "); sqlQuery.append(" SELECT a.TAX_CODE taxCode, "); sqlQuery.append(" a.NAME name, "); sqlQuery.append(" a.REPRESENTATIVE_NAME representativeName, "); sqlQuery.append(" a.TEL_NUMBER telNumber, "); sqlQuery.append(" a.EMAIL email, "); sqlQuery.append(" a.STATUS status, "); sqlQuery.append(" a.OFFICE_ADDRESS officeAddress, "); sqlQuery.append(" d.MINE_NAME mineName, "); sqlQuery.append(" d.SERVICE service, "); sqlQuery.append(" d.END_TIME endTime, "); sqlQuery.append(" d.START_TIME startTime, "); sqlQuery.append(" a.TAX_AUTHORITY taxAuthority "); sqlQuery.append(" FROM CUSTOMER a "); sqlQuery.append(" JOIN TERM_INFORMATION d "); sqlQuery.append(" ON d.TAX_CODE = a.TAX_CODE "); sqlQuery.append(" WHERE 1=1 "); sqlQuery.append(" AND d.IS_CONTACT_INFO is null "); sqlQuery.append(" AND NOT EXISTS (select c.tax_code, c.MINE_NAME "); sqlQuery.append(" from customer_status c "); sqlQuery.append(" where c.tax_code = d.tax_code "); sqlQuery.append(" AND c.mine_name = d.mine_name) "); if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { sqlQuery.append(" AND a.TAX_AUTHORITY in (:tax) "); } if (!DataUtil.isStringNullOrEmpty(service)) { sqlQuery.append(" AND d.SERVICE = :service "); } if (!DataUtil.isStringNullOrEmpty(mineName)) { sqlQuery.append(" AND d.MINE_NAME = :mineName "); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(provider)) { sqlQuery.append(" AND lower(d.PROVIDER) in (:provider) "); } if (!DataUtil.isStringNullOrEmpty(fromStartTime)) { sqlQuery.append(" AND d.START_TIME >= TO_DATE(:fromStartTime,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(toStartTime)) { sqlQuery.append(" AND d.START_TIME <= TO_DATE(:toStartTime,'dd/MM/yyyy') + 1 "); } if (!DataUtil.isStringNullOrEmpty(fromEndTime)) { sqlQuery.append(" AND d.END_TIME >= TO_DATE(:fromEndTime,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(toEndTime)) { sqlQuery.append(" AND d.END_TIME <= TO_DATE(:toEndTime,'dd/MM/yyyy') + 1"); } if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { sqlQuery.append(" AND d.DATE_REGISTER >= TO_DATE(:fromDateRegister,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { sqlQuery.append(" AND d.DATE_REGISTER <= TO_DATE(:toDateRegister,'dd/MM/yyyy') + 1 "); } //Them viec bo nhung tax code da phan bo if (!DataUtil.isListNullOrEmpty(taxCodesExecuted)) { for (int i = 0; i < taxCodesExecuted.size(); i++) { sqlQuery.append(" AND a.tax_code not in (:taxCode").append(i).append(" ) "); } } // sqlQuery.append(" ORDER BY endTime desc, startTime desc "); sqlQuery.append( " ) e GROUP BY taxCode, name,representativeName,telNumber,email,status,officeAddress,mineName,service,taxAuthority\n" + " ORDER BY taxCode, name,representativeName,telNumber,email,status,officeAddress,mineName,service,taxAuthority"); try { query = getSession().createSQLQuery(sqlQuery.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(CustomerDTO.class)); query.addScalar("taxCode", new StringType()); query.addScalar("name", new StringType()); query.addScalar("representativeName", new StringType()); query.addScalar("telNumber", new StringType()); query.addScalar("email", new StringType()); query.addScalar("status", new StringType()); query.addScalar("officeAddress", new StringType()); query.addScalar("mineName", new StringType()); query.addScalar("service", new StringType()); query.addScalar("startTime", new StringType()); query.addScalar("endTime", new StringType()); query.addScalar("taxAuthority", new StringType()); //Danh sach khai thac if (!DataUtil.isStringNullOrEmpty(mineName)) { query.setParameter("mineName", Long.parseLong(mineName)); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(provider)) { query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase())); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { query.setParameterList("tax", DataUtil.parseInputListString(taxAuthority)); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(service)) { query.setParameter("service", Long.parseLong(service)); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromStartTime)) { query.setParameter("fromStartTime", fromStartTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toStartTime)) { query.setParameter("toStartTime", toStartTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromEndTime)) { query.setParameter("fromEndTime", fromEndTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toEndTime)) { query.setParameter("toEndTime", toEndTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { query.setParameter("fromDateRegister", fromDateRegister); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { query.setParameter("toDateRegister", toDateRegister); } //Them viec bo nhung tax code da phan bo if (!DataUtil.isListNullOrEmpty(taxCodesExecuted)) { for (int i = 0; i < taxCodesExecuted.size(); i++) { query.setParameterList("taxCode" + i, DataUtil.parseInputListString(taxCodesExecuted.get(i))); } } //Day du lieu ra danh sach doi tuong if (!DataUtil.isStringNullOrEmpty(maxSearch)) { if (DataUtil.isInteger(maxSearch)) { query.setMaxResults(Integer.parseInt(maxSearch)); } } lstCustomers = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCustomers; }
From source file:com.duroty.application.files.manager.FilesManager.java
License:Open Source License
/** * DOCUMENT ME!/* www . j ava 2 s. c o m*/ * * @param hsession DOCUMENT ME! * @param repositoryName DOCUMENT ME! * @param folderName DOCUMENT ME! * @param page DOCUMENT ME! * @param messagesByPage DOCUMENT ME! * @param order DOCUMENT ME! * @param orderType DOCUMENT ME! * * @return DOCUMENT ME! * * @throws FilesException DOCUMENT ME! */ public Vector getFiles(Session hsession, String repositoryName, String folderName, int label, int page, int messagesByPage, int order, String orderType) throws FilesException { Vector files = new Vector(); try { Users user = getUser(hsession, repositoryName); Locale locale = new Locale(user.getUseLanguage()); TimeZone timeZone = TimeZone.getDefault(); Date now = new Date(); Calendar calendar = Calendar.getInstance(timeZone, locale); calendar.setTime(now); SimpleDateFormat formatter1 = new SimpleDateFormat("MMM dd", locale); SimpleDateFormat formatter2 = new SimpleDateFormat("HH:mm:ss", locale); SimpleDateFormat formatter3 = new SimpleDateFormat("MM/yy", locale); Query hquery = null; String[] folderNameList = new String[0]; try { folderName = parseFolder(folderName); folderNameList = new String[] { folderName }; if (folderName.equals(this.folderAll) || folderName.equals(this.folderHidden)) { folderNameList = new String[] { this.folderAll, this.folderDraft, this.folderHidden, this.folderImportant, this.folderInbox, this.folderSent }; } } catch (Exception ex) { } if ((folderNameList.length == 0) && (label <= 0)) { hquery = hsession.getNamedQuery("attachments"); } else if ((folderNameList.length > 0) && (label <= 0)) { hquery = hsession.getNamedQuery("attachments-by-folder"); } else if ((folderNameList.length == 0) && (label > 0)) { hquery = hsession.getNamedQuery("attachments-by-label"); } else if ((folderNameList.length > 0) && (label > 0)) { hquery = hsession.getNamedQuery("attachments-by-folder-label"); } String aux = hquery.getQueryString(); switch (order) { case ORDER_BY_SIZE: if (orderType.equals("ASC")) { aux += " order by att_size asc"; } else { aux += " order by att_size desc"; } break; case ORDER_BY_DATE: if (orderType.equals("ASC")) { aux += " order by mes_date asc"; } else { aux += " order by mes_date desc"; } break; case ORDER_BY_TYPE: if (orderType.equals("ASC")) { aux += " order by att_content_type asc"; } else { aux += " order by att_content_type desc"; } break; default: if (!orderType.equals("ASC")) { aux += " order by att_name desc"; } else { aux += " order by att_name asc"; } break; } SQLQuery h2query = hsession.createSQLQuery(aux); if ((folderNameList.length == 0) && (label <= 0)) { h2query.setParameterList("no_boxes", new String[] { this.folderTrash, this.folderChat, this.folderSpam, FOLDER_DELETE }); h2query.setInteger("user", getUser(hsession, repositoryName).getUseIdint()); } else if ((folderNameList.length > 0) && (label <= 0)) { h2query.setParameterList("boxes", folderNameList); h2query.setInteger("user", getUser(hsession, repositoryName).getUseIdint()); } else if ((folderNameList.length == 0) && (label > 0)) { h2query.setInteger("label", label); h2query.setParameterList("no_boxes", new String[] { this.folderTrash, this.folderChat, this.folderSpam, FOLDER_DELETE }); h2query.setInteger("user", getUser(hsession, repositoryName).getUseIdint()); } else if ((folderNameList.length > 0) && (label > 0)) { h2query.setInteger("label", label); h2query.setParameterList("boxes", folderNameList); h2query.setInteger("user", getUser(hsession, repositoryName).getUseIdint()); } h2query.setFirstResult(page * messagesByPage); h2query.setMaxResults(messagesByPage); h2query.addEntity("testo", AttachmentWithDate.class); ScrollableResults scroll = h2query.scroll(); while (scroll.next()) { AttachmentWithDate attachment = (AttachmentWithDate) scroll.get(0); AttachmentObj obj = new AttachmentObj(); obj.setContentType(attachment.getAttContentType()); Date date = attachment.getAttDate(); if (date != null) { Calendar calendar2 = Calendar.getInstance(timeZone, locale); calendar2.setTime(date); if ((calendar.get(Calendar.YEAR) == calendar2.get(Calendar.YEAR)) && (calendar.get(Calendar.MONTH) == calendar2.get(Calendar.MONTH)) && (calendar.get(Calendar.DATE) == calendar2.get(Calendar.DATE))) { obj.setDateStr(formatter2.format(calendar2.getTime())); } else if (calendar.get(Calendar.YEAR) == calendar2.get(Calendar.YEAR)) { obj.setDateStr(formatter1.format(calendar2.getTime())); } else { obj.setDateStr(formatter3.format(calendar2.getTime())); } } obj.setDate(date); obj.setDate(date); obj.setIdint(attachment.getAttIdint()); obj.setName(attachment.getAttName()); obj.setPart(attachment.getAttPart()); int size = attachment.getAttSize(); size /= 1024; if (size > 1024) { size /= 1024; obj.setSize(size + " MB"); } else { obj.setSize(((size > 0) ? (size + "") : "<1") + " kB"); } String extension = (String) this.extensions.get(attachment.getAttContentType()); if (StringUtils.isBlank(extension)) { extension = "generic"; } obj.setExtension(extension); Message message = attachment.getMessage(); if (message.isMesFlagged()) { obj.setFlagged(true); } else { obj.setFlagged(false); } if (message.getLabMeses() != null) { Iterator it = message.getLabMeses().iterator(); StringBuffer lab = new StringBuffer(); while (it.hasNext()) { if (lab.length() > 0) { lab.append(", "); } LabMes labMes = (LabMes) it.next(); lab.append(labMes.getId().getLabel().getLabName()); } if (lab.length() > 0) { obj.setLabel(lab.toString()); } else { } } obj.setBox(message.getMesBox()); obj.setMid(message.getMesName()); files.addElement(obj); } return files; } catch (Exception e) { throw new FilesException(e); } finally { GeneralOperations.closeHibernateSession(hsession); } }
From source file:com.duroty.application.mail.manager.MailManager.java
License:Open Source License
/** * DOCUMENT ME!/*w w w.j a v a 2s .c o m*/ * * @param hsession DOCUMENT ME! * @param repositoryName DOCUMENT ME! * @param label DOCUMENT ME! * @param page DOCUMENT ME! * @param messagesByPage DOCUMENT ME! * @param order DOCUMENT ME! * @param orderType DOCUMENT ME! * * @return DOCUMENT ME! * * @throws MailException DOCUMENT ME! */ public Vector getMessages(Session hsession, String repositoryName, Label label, int page, int messagesByPage, int order, String orderType) throws MailException { Vector messages = new Vector(); try { Users user = getUser(hsession, repositoryName); Locale locale = new Locale(user.getUseLanguage()); TimeZone timeZone = TimeZone.getDefault(); Date now = new Date(); Calendar calendar = Calendar.getInstance(timeZone, locale); calendar.setTime(now); SimpleDateFormat formatter1 = new SimpleDateFormat("MMM dd", locale); SimpleDateFormat formatter2 = new SimpleDateFormat("HH:mm:ss", locale); SimpleDateFormat formatter3 = new SimpleDateFormat("MM/yy", locale); Query hquery = hsession.getNamedQuery("messages-by-label"); String aux = hquery.getQueryString(); switch (order) { case ORDER_BY_IMPORTANT: if (orderType.equals("ASC")) { aux += " order by mes_flagged asc"; } else { aux += " order by mes_flagged desc"; } break; case ORDER_BY_ADDRESS: if (orderType.equals("ASC")) { aux += " order by mes_from asc"; } else { aux += " order by mes_from desc"; } break; case ORDER_BY_SIZE: if (orderType.equals("ASC")) { aux += " order by mes_size asc"; } else { aux += " order by mes_size desc"; } break; case ORDER_BY_SUBJECT: if (orderType.equals("ASC")) { aux += " order by mes_subject asc"; } else { aux += " order by mes_subject desc"; } break; case ORDER_BY_DATE: if (orderType.equals("ASC")) { aux += " order by mes_date asc"; } else { aux += " order by mes_date desc"; } break; case ORDER_BY_UNREAD: if (orderType.equals("ASC")) { aux += " order by mes_recent asc"; } else { aux += " order by mes_recent desc"; } break; default: aux += " order by mes_date desc"; break; } SQLQuery h2query = hsession.createSQLQuery(aux); h2query.addEntity("i", Message.class); h2query.setInteger("label", label.getLabIdint()); h2query.setInteger("user", user.getUseIdint()); h2query.setString("folderTrash", this.folderTrash); h2query.setString("folderSpam", this.folderSpam); h2query.setString("folderDelete", FOLDER_DELETE); h2query.setFirstResult(page * messagesByPage); h2query.setMaxResults(messagesByPage); ScrollableResults scroll = h2query.scroll(); while (scroll.next()) { Message message = (Message) scroll.get(0); MessageObj obj = new MessageObj(message.getMesName()); obj.setBox(message.getMesBox()); obj.setFrom(message.getMesFrom()); if ((message.getAttachments() != null) && (message.getAttachments().size() > 0)) { obj.setHasAttachment(true); } else { obj.setHasAttachment(false); } int size = message.getMesSize(); size /= 1024; if (size > 1024) { size /= 1024; obj.setSize(size + " MB"); } else { obj.setSize(((size > 0) ? (size + "") : "<1") + " kB"); } if (message.getMesBox().equals(folderSent)) { try { obj.setEmail(message.getMesTo()); } catch (Exception e) { obj.setEmail("unknown to"); } } else { obj.setEmail(message.getMesFrom()); } Date date = message.getMesDate(); if (date != null) { Calendar calendar2 = Calendar.getInstance(timeZone, locale); calendar2.setTime(date); if ((calendar.get(Calendar.YEAR) == calendar2.get(Calendar.YEAR)) && (calendar.get(Calendar.MONTH) == calendar2.get(Calendar.MONTH)) && (calendar.get(Calendar.DATE) == calendar2.get(Calendar.DATE))) { obj.setDateStr(formatter2.format(calendar2.getTime())); } else if (calendar.get(Calendar.YEAR) == calendar2.get(Calendar.YEAR)) { obj.setDateStr(formatter1.format(calendar2.getTime())); } else { obj.setDateStr(formatter3.format(calendar2.getTime())); } } obj.setDate(date); if (message.getLabMeses() != null) { Iterator it = message.getLabMeses().iterator(); StringBuffer buff = new StringBuffer(); while (it.hasNext()) { if (buff.length() > 0) { buff.append(", "); } LabMes labMes = (LabMes) it.next(); buff.append(labMes.getId().getLabel().getLabName()); } obj.setLabel(buff.toString()); } try { if (StringUtils.isBlank(message.getMesSubject())) { obj.setSubject("(no subject)"); } else { obj.setSubject(message.getMesSubject()); } } catch (Exception ex) { obj.setSubject("(no subject)"); } if (message.isMesFlagged()) { obj.setFlagged(true); } else { obj.setFlagged(false); } if (message.isMesRecent()) { obj.setRecent(true); } else { obj.setRecent(false); } String priority = "normal"; if (MessageUtilities.isHighPriority(message.getMesHeaders())) { priority = "high"; } else if (MessageUtilities.isLowPriority(message.getMesHeaders())) { priority = "low"; } obj.setPriority(priority); messages.addElement(obj); } return messages; } catch (Exception e) { throw new MailException(e); } finally { GeneralOperations.closeHibernateSession(hsession); } }