/**
* Find all News entities with a specific property value.
*
* @param propertyName the name of the News property to query
* @param value the property value to match
* @param rowStartIdxAndCount Optional int varargs. rowStartIdxAndCount[0] specifies the the row
* index in the query result-set to begin collecting the results. rowStartIdxAndCount[1]
* specifies the the maximum number of results to return.
* @return List<News> found by query
*/
@SuppressWarnings("unchecked")
public List<News> findByProperty(
String propertyName, final Object value, final int... rowStartIdxAndCount) {
LogUtil.log(
"finding News instance with property: " + propertyName + ", value: " + value,
Level.INFO,
null);
try {
final String queryString =
"select model from News model where model." + propertyName + "= :propertyValue";
Query query = entityManager.createQuery(queryString);
query.setParameter("propertyValue", value);
if (rowStartIdxAndCount != null && rowStartIdxAndCount.length > 0) {
int rowStartIdx = Math.max(0, rowStartIdxAndCount[0]);
if (rowStartIdx > 0) {
query.setFirstResult(rowStartIdx);
}
if (rowStartIdxAndCount.length > 1) {
int rowCount = Math.max(0, rowStartIdxAndCount[1]);
if (rowCount > 0) {
query.setMaxResults(rowCount);
}
}
}
return query.getResultList();
} catch (RuntimeException re) {
LogUtil.log("find by property name failed", Level.SEVERE, re);
throw re;
}
}
private Query createQueryParam(String _jpql, List<SqlQueryItem> _paramList, Pageable _pageable) {
Query query = manager.createQuery(_jpql);
if (_pageable != null) {
int maxResult = _pageable.getPageSize();
query.setFirstResult(_pageable.getPageNumber() * _pageable.getPageSize());
query.setMaxResults(maxResult);
}
if (_paramList != null) {
for (SqlQueryItem param : _paramList) {
Object value = param.getValue();
if (param.getAction() == SqlActionType.LIKE) {
String val = param.getValue().toString().trim();
if (!val.startsWith("%")) {
val = "%" + val;
}
if (!val.endsWith("%")) {
val = val + "%";
}
value = val;
}
query.setParameter(param.getPropertyName().replace(".", ""), value);
}
}
return query;
}
@SuppressWarnings("unchecked")
private String actionQuery(
EntityManager em,
BundleJobBean bundleBean,
Map<String, Timestamp> times,
List<BulkResponseImpl> responseList)
throws ParseException {
Query q = em.createNamedQuery("BULK_MONITOR_ACTIONS_QUERY");
StringBuilder getActions = new StringBuilder(q.toString());
StringBuilder conditionClause = new StringBuilder();
conditionClause.append(
coordNamesClause(bulkFilter.get(BulkResponseImpl.BULK_FILTER_COORD_NAME)));
conditionClause.append(statusClause(bulkFilter.get(BulkResponseImpl.BULK_FILTER_STATUS)));
int offset = getActions.indexOf("ORDER");
getActions.insert(offset - 1, conditionClause);
timesClause(getActions, offset, times);
q = em.createQuery(getActions.toString());
Iterator<Entry<String, Timestamp>> iter = times.entrySet().iterator();
while (iter.hasNext()) {
Entry<String, Timestamp> time = iter.next();
q.setParameter(time.getKey(), time.getValue());
}
q.setParameter("bundleId", bundleBean.getId());
// pagination
q.setFirstResult(start - 1);
q.setMaxResults(len);
List<Object[]> response = q.getResultList();
for (Object[] r : response) {
BulkResponseImpl br = getResponseFromObject(bundleBean, r);
responseList.add(br);
}
return q.toString();
}
// see: http://en.wikibooks.org/wiki/Java_Persistence/Querying#Pagination.2C_Max.2FFirst_Results
// for limiting results... and ordering correclty...
@SuppressWarnings("unchecked")
public List<URL> getBackLinkURLs(int targetURLId, int limitStart, Integer limitEnd) {
log.info(
"Entering getBackLinkURLs: Start = "
+ Integer.toString(limitStart)
+ ". End = "
+ Integer.toString(limitEnd));
List<URL> result = null;
EntityManagerFactory emf = PersistenceManager.getInstance().getEntityManagerFactory();
EntityManager mgr = emf.createEntityManager();
Query query =
mgr.createQuery(
"SELECT u FROM URL u WHERE u.url_id = ANY (SELECT l.source_id FROM Link l WHERE l.target_id = :id) ORDER BY u.url_id");
query.setParameter("id", targetURLId);
query.setFirstResult(limitStart);
query.setMaxResults(limitEnd);
try {
result = query.getResultList();
log.info("getBackLinkURLs: No Of Results: " + Integer.toString(result.size()));
} catch (Exception e) {
String msg = "Exception thrown. URLService: getBackLinkURLs. ";
log.severe(msg + e.getMessage());
} finally {
mgr.close();
}
return result;
}
/**
* Get all flagged activities.
*
* @param inRequest the request containing the start and end index
* @return a paged set of ActivityDTOs
*/
@Override
@SuppressWarnings("unchecked")
public PagedSet<ActivityDTO> execute(final GetFlaggedActivitiesRequest inRequest) {
if (inRequest.getRequestingUserAccountId() == null) {
log.error("Missing requesting user account.");
throw new RuntimeException("Missing requesting user account.");
}
// get the total number of flagged activities
Long flaggedActivityCount = (Long) buildQuery("count(*)").getSingleResult();
List<ActivityDTO> activities;
if (flaggedActivityCount > 0) {
log.info(
"Found "
+ flaggedActivityCount
+ " flagged activity ids - passing them to the bulk activity mapper for ActivityDTOs.");
Query q = buildQuery("id");
q.setFirstResult(inRequest.getStartIndex());
q.setMaxResults(inRequest.getEndIndex() - inRequest.getStartIndex() + 1);
List<Long> activityIds = q.getResultList();
activities = activitiesMapper.execute(activityIds);
} else {
log.info("Found no flagged activity ids.");
activities = new ArrayList<ActivityDTO>();
}
return new PagedSet<ActivityDTO>(
inRequest.getStartIndex(),
inRequest.getEndIndex(),
flaggedActivityCount.intValue(),
activities);
}
private List<PedidoVenta> findPedidoVentaEntities(boolean all, int maxResults, int firstResult) {
EntityManager em = getEntityManager();
try {
Query q = em.createQuery("select object(o) from PedidoVenta as o");
if (!all) {
q.setMaxResults(maxResults);
q.setFirstResult(firstResult);
}
List<PedidoVenta> result = q.getResultList();
for (PedidoVenta pedidoVenta : result) {
pedidoVenta.getCliente();
pedidoVenta.getFormaDePago();
pedidoVenta.getUsuario();
Collection<PedidoVentaDetalle> detalleVentaPedidoCollection =
pedidoVenta.getPedidoVentaDetalleCollection();
for (PedidoVentaDetalle detalleVentaPedido : detalleVentaPedidoCollection) {
Producto producto = detalleVentaPedido.getProducto();
}
Collection<PedidoVentaEstado> pedidoVentaEstadoCollection =
pedidoVenta.getPedidoVentaEstadoCollection();
for (PedidoVentaEstado pedidoVentaEstado : pedidoVentaEstadoCollection) {}
}
return result;
} finally {
em.close();
}
}
private Query prepareQuery(
String namedQuery,
final Map<String, ? extends Object> parameters,
int firstResult,
int maxResults) {
Query query = null;
if (parameters != null && !parameters.isEmpty()) {
query =
createNamedQuery(
namedQuery,
(String[]) parameters.keySet().toArray(new String[parameters.keySet().size()]),
parameters.values().toArray());
} else {
query = createNamedQuery(namedQuery);
}
if (firstResult != 0) {
query.setFirstResult(firstResult);
}
if (maxResults != 0) {
query.setMaxResults(maxResults);
}
return query;
}
@Override
public List<Request> findEntitiesWithAvailableStatus(boolean all, int maxResults, int firstResult)
throws Exception {
EntityManager entityManager = getEntityManager();
RequestDAO rdao = new RequestDAO();
Long resultIdLong;
Request cr;
Query nquery =
entityManager.createNativeQuery(nativeQuerySequenceFindRequestWithAvailableProducts);
try {
if (!all) {
nquery.setMaxResults(maxResults);
nquery.setFirstResult(firstResult);
}
List resultListId = nquery.getResultList();
List<Request> resultList = new ArrayList<Request>();
for (Object elem : resultListId) {
resultIdLong = Long.parseLong(elem.toString());
cr = rdao.findById(resultIdLong);
resultList.add(cr);
}
for (Object elem : resultList) {}
return resultList;
} catch (NullPointerException e) {
LogBean.getLogger().debug(FINDMSG + " " + java.util.Calendar.getInstance().getTime());
return null;
}
}
/**
* Find all News entities.
*
* @param rowStartIdxAndCount Optional int varargs. rowStartIdxAndCount[0] specifies the the row
* index in the query result-set to begin collecting the results. rowStartIdxAndCount[1]
* specifies the the maximum count of results to return.
* @return List<News> all News entities
*/
@SuppressWarnings("unchecked")
public List<News> findAll(final int... rowStartIdxAndCount) {
LogUtil.log("finding all News instances", Level.INFO, null);
try {
final String queryString = "select model from News model";
Query query = entityManager.createQuery(queryString);
if (rowStartIdxAndCount != null && rowStartIdxAndCount.length > 0) {
int rowStartIdx = Math.max(0, rowStartIdxAndCount[0]);
if (rowStartIdx > 0) {
query.setFirstResult(rowStartIdx);
}
if (rowStartIdxAndCount.length > 1) {
int rowCount = Math.max(0, rowStartIdxAndCount[1]);
if (rowCount > 0) {
query.setMaxResults(rowCount);
}
}
}
return query.getResultList();
} catch (RuntimeException re) {
LogUtil.log("find all failed", Level.SEVERE, re);
throw re;
}
}
public Map<Object, Object> getFailedTransactionsAndTotalCount(
int start, ListFilterCriteria filterCriteria, Date fromDate, Date toDate) {
String subQuery =
"from PaymentTransaction o where o.createdAt BETWEEN :fromDate AND :toDate"
+ " and paymentTransactionStatus = 'FAILURE' and o.";
String transactionListQueryString =
"select o "
+ subQuery
+ filterCriteria.getColumn()
+ " like '%"
+ filterCriteria.getValueToSearch()
+ "%' order by o."
+ filterCriteria.getSortBy()
+ " "
+ filterCriteria.getSortOrder();
Query transactionListQuery =
getEntityManagerForActiveEntities().createQuery(transactionListQueryString);
transactionListQuery.setParameter("fromDate", fromDate);
transactionListQuery.setParameter("toDate", toDate);
transactionListQuery.setFirstResult(start);
transactionListQuery.setMaxResults(filterCriteria.getItemsPerPage());
List<PaymentTransaction> transactionList = transactionListQuery.getResultList();
Map<Object, Object> transactionDetails = new HashMap<Object, Object>();
transactionDetails.put("list", transactionList);
transactionDetails.put("totalCount", getTotalCount(filterCriteria, fromDate, toDate));
return transactionDetails;
}
public Map<String, Object> find(
Person examplePerson, int first, int max, String[] order, boolean[] desc) {
Map<String, Object> result = new HashMap<String, Object>(4);
String from = "from Person e ";
String where = "where lower(e.lastName) like :lastName ";
String orderBy =
order != null && order.length > 0
? "order by e." + order[0] + (desc[0] ? " desc" : "")
: "";
String lastName = examplePerson.getLastName() != null ? examplePerson.getLastName() : "";
Query qc = entityManager.createQuery("select count(e) " + from + where);
qc.setParameter("lastName", "%" + lastName.toLowerCase() + "%");
long resultCount = (Long) qc.getSingleResult();
if (max == 0) max = 36;
Query ql = entityManager.createQuery("select e " + from + where + orderBy);
ql.setFirstResult(first);
ql.setMaxResults(max);
ql.setParameter("lastName", "%" + lastName.toLowerCase() + "%");
List<?> resultList = ql.getResultList();
result.put("firstResult", first);
result.put("maxResults", max);
result.put("resultCount", resultCount);
result.put("resultList", resultList);
return result;
}
@SuppressWarnings("unchecked")
public <E extends IEntity, obj extends Object> List<E> getEntities(
Class<E> inElementClass,
StringBuffer query,
Hashtable<String, obj> criteria,
Integer pageNumber,
Integer pageSize) {
Query qry = entityManager.createQuery(query.toString());
Enumeration<String> keys = criteria.keys();
while (keys.hasMoreElements()) {
String key = keys.nextElement();
qry.setParameter(key, criteria.get(key));
}
Object result;
try {
if (pageNumber != -1 && pageSize != -1) {
qry = qry.setFirstResult(pageSize * (pageNumber - 1));
qry.setMaxResults(pageSize);
}
result = qry.getResultList();
} catch (NoResultException nre) {
return null;
}
return (List<E>) result;
}
@Override
public <T extends BaseDto> List<Object[]> queryFieldValues(
Class<T> entityClass,
String[] queryfields,
String wheresql,
Object[] queryParams,
int startRow,
int rows) {
String entityname = sqlBuilder.getEntityName(entityClass);
Query query =
getEntityManager()
.createQuery(
sqlBuilder.buildSelect(queryfields, "o")
+ " FROM "
+ entityname
+ " o "
+ (wheresql == null ? "" : "WHERE " + wheresql));
setQueryParams(query, queryParams);
if (startRow >= 0) {
query.setFirstResult(startRow);
}
if (rows > 0) {
query.setMaxResults(rows);
}
return query.getResultList();
}
@SuppressWarnings("unchecked")
public <T> List<T> findWithNamedQuery(String queryName, Map<String, Object> params) {
Set<Entry<String, Object>> rawParameters = params.entrySet();
Query query = entityManager.createNamedQuery(queryName);
// if has limit.
if (params.containsKey(QueryParameters.LIMIT_START)) {
int start = new Integer(params.get(QueryParameters.LIMIT_START).toString());
int n = (Integer) params.get(QueryParameters.LIMIT_N);
params.remove(QueryParameters.LIMIT_START);
params.remove(QueryParameters.LIMIT_N);
query.setFirstResult(start);
query.setMaxResults(n);
}
// if has order by.
if (params.containsKey(QueryParameters.ORDERBY)) {
String orderbyField = (String) params.get(QueryParameters.ORDERBY);
params.remove(QueryParameters.ORDERBY);
query.setParameter("orderby", String.format("%s", orderbyField));
if (params.containsKey(QueryParameters.ORDERBY_SORT)) {
params.remove(QueryParameters.ORDERBY_SORT);
}
}
// Add parameters
for (Entry<String, Object> entry : rawParameters) {
query.setParameter(entry.getKey(), entry.getValue());
}
return query.getResultList();
}
/** @return */
private ArrayList<ReferenceDataRefDataType> getUsedRefSpecialtySchemes() {
System.out.println("\n========> Getting Used specialtyscheme references");
ArrayList<ReferenceDataRefDataType> refColl = new ArrayList<ReferenceDataRefDataType>();
Query query = null;
int startRec = refDetails.getStartingRecNumber();
int maxDisplay = refDetails.getMaxRecordsToShow();
if ((refDetails.getLookupSystem().getCodeSystem() == null)
|| (refDetails.getLookupSystem().getCodeSystem().isEmpty())) {
query = em.createNamedQuery("KMSpecialty.findAllDistinctScheme");
} else {
query = em.createNamedQuery("KMSpecialty.findAllDistinctSchemeByWildcard");
query.setParameter(
"terminologyScheme", refDetails.getLookupSystem().getCodeSystem().toUpperCase() + "%");
}
query.setFirstResult(startRec);
query.setMaxResults(maxDisplay);
List<String> foundList = (List<String>) query.getResultList();
for (int r = 0; r < foundList.size(); r++) {
ReferenceDataRefDataType aRefData = new ReferenceDataRefDataType();
aRefData.setId(
0); // TODO Need to pull the ID for each one of this type from ref_specialty_type
// ..another call
aRefData.setName(foundList.get(r));
refColl.add(aRefData);
}
return refColl;
}
public Page<Object[]> getCustomerConsist(String type, int pageNo, int pageSize) {
String hql =
"select count(id) from Customer c "
+ "where c."
+ type
+ " is not null "
+ "group by c."
+ type;
Query query = entityManager.createQuery(hql);
int total = query.getResultList().size();
hql =
"select c."
+ type
+ " , count(id) from Customer c where c."
+ type
+ " is not null "
+ "group by c."
+ type;
query = entityManager.createQuery(hql);
query.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize);
List content = query.getResultList();
PageRequest pageable = new PageRequest(pageNo - 1, pageSize);
PageImpl<Object[]> page = new PageImpl<Object[]>(content, pageable, total);
return page;
}
/**
* 描述：查找所有 <br>
* 创建时间：2013-5-11 下午10:51:46
*
* @author liyang
* @param lockMode
* @param cacheName
* @param rowStartIdxAndCount Optional int varargs. rowStartIdxAndCount[0] specifies the the row
* index in the query result-set to begin collecting the results. rowStartIdxAndCount[1]
* specifies the the maximum number of results to return.
* @return
*/
@SuppressWarnings("unchecked")
public List<T> findAll(
final LockModeType lockMode,
final ConstantsEnum.EHCACHE cacheName,
final int... rowStartIdxAndCount) {
Query query = entityManager.createQuery("from " + clazz.getName() + " ");
if (rowStartIdxAndCount != null && rowStartIdxAndCount.length > 0) {
int rowStartIdx = Math.max(0, rowStartIdxAndCount[0]);
if (rowStartIdx > 0) {
query.setFirstResult(rowStartIdx);
}
if (rowStartIdxAndCount.length > 1) {
int rowCount = Math.max(0, rowStartIdxAndCount[1]);
if (rowCount > 0) {
query.setMaxResults(rowCount);
}
}
}
if (lockMode != null) {
query.setLockMode(lockMode);
}
if (cacheName != null) {
query.setHint("org.hibernate.cacheable", true);
query.setHint("org.hibernate.cacheRegion", cacheName.getStr());
}
return query.getResultList();
}
@SuppressWarnings("unchecked")
public <E extends IEntity> List<E> findAllEntities(
Class<E> inEntityClass, String orderby, Integer pageNumber, Integer pageSize) {
String entityClsName = inEntityClass.getSimpleName();
Entity entity = inEntityClass.getAnnotation(Entity.class);
if (entity != null) {
String eName = entity.name();
if (eName != null && eName.length() > 0) {
entityClsName = eName;
}
}
String sQuery = "SELECT instance FROM " + entityClsName + " instance";
if (orderby == null) {
sQuery = sQuery + " order by instance.createdOn ASC";
} else {
sQuery = sQuery + " order by instance." + orderby;
}
Query query = entityManager.createQuery(sQuery);
query = query.setFirstResult(pageSize * (pageNumber - 1));
query.setMaxResults(pageSize);
return (List<E>) query.getResultList();
}
@SuppressWarnings("unchecked")
public static List<MdlUser> findMdlUserEntries(
String searchString, int firstResult, int maxResults) {
Query q = queryFindMdlUserEntries(searchString, false);
return q.setFirstResult(firstResult).setMaxResults(maxResults).getResultList();
}
public List<Employee> getAll(int pageNumber) {
int pageSize = 5;
Query query = em.createQuery("SELECT c FROM Employee c");
query.setFirstResult((pageNumber - 1) * pageSize);
query.setMaxResults(pageSize);
return query.getResultList();
}
public Map<Object, Object> getBannerListAndTotalCount(
int start, ListFilterCriteria filterCriteria) {
log.debug("BannerDao.getBannerListAndTotalCount() ");
EntityManager entityManager = getEntityManagerForActiveEntities();
Query bannerCountQuery =
entityManager.createNativeQuery(
"select count(*) from banner b where b."
+ filterCriteria.getColumn()
+ " like '%"
+ filterCriteria.getValueToSearch()
+ "%'");
Query bannerListQuery =
entityManager.createQuery(
"select b from Banner b where b."
+ filterCriteria.getColumn()
+ " like '%"
+ filterCriteria.getValueToSearch()
+ "%' order by "
+ filterCriteria.getSortBy()
+ " "
+ filterCriteria.getSortOrder());
bannerListQuery.setFirstResult(start);
bannerListQuery.setMaxResults(filterCriteria.getItemsPerPage());
List<Banner> bannersList = bannerListQuery.getResultList();
Map<Object, Object> bannerDetails = new HashMap<Object, Object>();
bannerDetails.put("list", bannersList);
bannerDetails.put("totalCount", ((BigInteger) bannerCountQuery.getSingleResult()).intValue());
return bannerDetails;
}
// 员工管理
@Override
public List<Object[]> ygglfindygglByCondition(Employee Employee, Integer start, Integer limit) {
String strHql =
"select t.employee_id,t.yggl_djrq ,t.employee_no,t.yggl_rybhbj,t.employee_name,t.yggl_ywxm,t1.organization_id,t1.organization_no,t1.organization_name, t2.xbsd_id,t2.xbsd_bh,t2.xbsd_mc ,t.yggl_sfzh,t3.jzzt_id,t3.jzzt_bh,t3.jzzt_mc,t.yggl_csrq,t4.hyzk_id,t4.hyzk_bh,t4.hyzk_mc,t5.xxsd_id,t5.xxsd_bh, t5.xxsd_mc ,t6.jgsd_id,t6.jgsd_bh,t6.jgsd_mc ,t7.gjsd_id,t7.gjsd_bh,t7.gjsd_mc ,t8.mzsd_id,t8.mzsd_bh,t8.mzsd_mc, t9.sdzw_id,t9.sdzw_bh,t9.sdzw_mc, t10.xsl_id,t10.xsl_bh,t10.xsl_mc,t.yggl_zzmm,t.yggl_rzrq,t.yggl_zzrq,t.yggl_syqc,t.yggl_lzrq,t11.jszc_id,t11.jszc_bh,t11.jszc_mc,t12.sdgw_id,t12.sdgw_bh, t12.sdgw_mc,t.yggl_dzyj,t.yggl_hjdh,t.yggl_hjyb,t.yggl_hjdz,t.yggl_jtdh,t.yggl_jtyb,t.yggl_jtzz,t.yggl_xdh,t.yggl_xyb,t.yggl_xzz,t.yggl_jjlxr, t.yggl_jjlxyb,t.yggl_jjlxdh,t.yggl_jjlxrdz,t13.xlsd_id,t13.xlsd_bh,t13.xlsd_mc ,t.yggl_xw,t.yggl_byyx,t.yggl_sxzy,t.yggl_wysp,t.yggl_tjrq,t.yggl_dqrq, t.yggl_rjrq,t.yggl_hzhm,t.yggl_htqs,t.yggl_htjs,t.yggl_htqc,t.yggl_gzfa,t.yggl_jbxz,t.yggl_fxfs,t.yggl_yhkh,t.yggl_sdsksfs,t.yggl_bz "
+ "from T_EMPLOYEE t ,t_organization t1 ,t_xbsd t2 ,t_jzzt t3 ,t_hyzk t4 , t_xxsd t5 ,t_jgsd t6 ,t_gjsd t7 ,t_mzsd t8 ,t_sdzw t9, t_xsl t10 ,t_jszc t11 ,t_sdgw t12 ,t_xlsd t13 where t.organization_id=t1.organization_id and t.xbsd_id =t2.xbsd_id and t.xbsd_id=t2.xbsd_id and t.jzzt_id =t3.jzzt_id and t.hyzk_id=t4.hyzk_id and t.xxsd_id=t5.xxsd_id and t.jgsd_id=t6.jgsd_id and t.gjsd_id=t7.gjsd_id and t.mzsd_id=t8.mzsd_id and t.sdzw_id=t9.sdzw_id and t.xsl_id=t10.xsl_id and t.jszc_id=t11.jszc_id and t.sdgw_id=t12.sdgw_id and t.xlsd_id =t13.xlsd_id";
// 人员姓名
if (null != Employee.getEmployeeName() && !"".equals(Employee.getEmployeeName().trim())) {
strHql += " and t.employee_name LIKE('%" + Employee.getEmployeeName().trim() + "%')";
}
// 所属部门
if (null != Employee.getYgglbz() && !"".equals(Employee.getYgglbz())) {
strHql += " and t1.organization_name LIKE('%" + Employee.getYgglbz() + "%')";
}
if (SearchUntil.search_strHql != null) {
strHql += SearchUntil.search_strHql;
SearchUntil.search_strHql = "";
}
strHql += " ORDER BY t.employee_id desc";
Query query = entityManager.createNativeQuery(strHql);
if (null != start && null != limit) {
if (0 == start) start = 1;
query.setFirstResult((start.intValue() - 1) * limit);
query.setMaxResults(limit.intValue());
}
List<Object[]> lstO = query.getResultList();
return lstO;
}
@SuppressWarnings("unchecked")
private <T> T queryStringWithParameters(
Map<String, Object> params,
boolean singleResult,
LockModeType lockMode,
Class<T> clazz,
Query query) {
;
if (lockMode != null) {
query.setLockMode(lockMode);
}
if (params != null && !params.isEmpty()) {
for (String name : params.keySet()) {
if (FIRST_RESULT.equals(name)) {
query.setFirstResult((Integer) params.get(name));
continue;
}
if (MAX_RESULTS.equals(name)) {
query.setMaxResults((Integer) params.get(name));
continue;
}
if (FLUSH_MODE.equals(name)) {
query.setFlushMode(FlushModeType.valueOf((String) params.get(name)));
continue;
}
query.setParameter(name, params.get(name));
}
}
if (singleResult) {
return (T) query.getSingleResult();
}
return (T) query.getResultList();
}
public List<HumanDto> load(
int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
StringBuffer sql =
new StringBuffer(
" SELECT DISTINCT s.human_id,s.organization_id,s.org_root_id,s.first_name,s.last_name,s.full_name,s.avatar_url,s.birthday,s.national_id,s.province_id, "
+ "s.district_id,s.street_id,s.address,s.phone,s.tel,s.email_address,s.possition,s.gender,s.cmt,s.cmt_approve_date,s.cmt_issue_plance_id, "
+ "s.yahoo,s.skype, s.status human_status,s.comments,u.*, u.password as retype_password, s.modified_date "
+ "from human s LEFT JOIN users u on s.human_id = u.human_id ");
this.buildQuery(sql, filters);
if (sortField != null && sortOrder != SortOrder.UNSORTED) {
sql.append(" order by s.")
.append(sortField)
.append(" ")
.append(sortOrder == SortOrder.ASCENDING ? "ASC" : "DESC");
} else {
sql.append(" order by s.modified_date desc");
}
Query query = em.createNativeQuery(sql.toString(), HumanDto.class);
String birthday = (String) filters.get(IHumanDtoService.USER_BITHDAY);
if (birthday != null) {
query.setParameter(
1,
DateTimeUtils.convertStringToDate(birthday, DateTimeUtils.ddMMyyyy),
TemporalType.DATE);
}
query.setFirstResult(first);
query.setMaxResults(pageSize);
return query.getResultList();
}
public Page<Object[]> getCustomerService(int pageNo, int pageSize, Date minDate, Date maxDate) {
String hql =
"select count(cs.serviceType) from CustomerService cs "
+ " where cs.customer is not null And cs.serviceType is not null "
+ " and cs.createDate between ? and ?";
Query query = entityManager.createQuery(hql);
query.setParameter(1, minDate);
query.setParameter(2, maxDate);
long total = (long) query.getSingleResult();
hql =
"select cs.serviceType , count(cs.serviceType) from CustomerService cs "
+ "where cs.customer is not null and cs.serviceType is not null and cs.createDate between ? and ? "
+ " group by cs.serviceType";
query = entityManager.createQuery(hql);
query.setParameter(1, minDate);
query.setParameter(2, maxDate);
query.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize);
List<Object[]> content = query.getResultList();
PageRequest pageable = new PageRequest(pageNo - 1, pageSize);
PageImpl<Object[]> page = new PageImpl<Object[]>(content, pageable, total);
return page;
}
@SuppressWarnings("unchecked")
@RolesAllowed({"BANKADMIN"})
public List<Transaction> getAllTransactions(int start, int size) {
Query q = em.createNamedQuery("getAllTransaction");
q.setMaxResults(size);
q.setFirstResult(start);
return q.getResultList();
}
public List<Programas> findRange(int[] range) {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
cq.select(cq.from(Programas.class));
Query q = em.createQuery(cq);
q.setMaxResults(range[1] - range[0]);
q.setFirstResult(range[0]);
return q.getResultList();
}
/**
* Returns the number of records that will be used with lazy loading / pagination
*
* @param namedQueryName
* @param start
* @param end
* @return List
*/
@ExcludeClassInterceptors
public List findWithNamedQuery(String namedQueryName, int start, int end) {
Query query = this.em.createNamedQuery(namedQueryName);
query.setMaxResults(end - start);
query.setFirstResult(start);
return query.getResultList();
}
public List<T> findRange(int[] range) {
CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
cq.select(cq.from(entityClass));
Query q = getEntityManager().createQuery(cq);
q.setMaxResults(range[1] - range[0] + 1);
q.setFirstResult(range[0]);
return q.getResultList();
}
public List<CajaView> findRange(int[] range) {
javax.persistence.criteria.CriteriaQuery cq = this.em.getCriteriaBuilder().createQuery();
cq.select(cq.from(CajaView.class));
javax.persistence.Query q = this.em.createQuery(cq);
q.setMaxResults(range[1] - range[0]);
q.setFirstResult(range[0]);
return q.getResultList();
}