/**
* 描述：查找所有 <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 List<ReportTask> findTODOTasks() {
String sql =
"select * from report_tasks where status in ('%s', '%s') and ( gen_count IS NULL or gen_count <= %d )";
sql = String.format(sql, Status.planned, Status.inprogress, MAX_GENERATION_COUNT);
Query query = manager.createNativeQuery(sql, ReportTask.class);
query.setHint(QueryHints.HINT_CACHE_MODE, CacheMode.IGNORE);
List<?> result = query.getResultList();
List<ReportTask> tasks = (List<ReportTask>) result;
StringBuffer idSbs = new StringBuffer();
// fill project identifier
if (!tasks.isEmpty()) {
List<Long> pId = new ArrayList<Long>();
for (ReportTask task : tasks) {
pId.add(task.getProjectId());
}
Map<Long, String> projects = new HashMap<Long, String>();
sql = "select id, name from projects where id in ( %s )";
query = manager.createNativeQuery(String.format(sql, StringUtils.join(pId, ',')));
query.setHint(QueryHints.HINT_CACHE_MODE, CacheMode.IGNORE);
result = query.getResultList();
for (Object o : result) {
Object[] objs = (Object[]) o;
projects.put(((Number) objs[0]).longValue(), objs[1].toString());
}
for (ReportTask t : tasks) {
t.setProjectName(projects.get(t.getProjectId()));
idSbs.append(t.getId().toString()).append(",");
}
}
logger.info("Find " + tasks.size() + " tasks! :: " + idSbs);
return tasks;
}
/**
* 描述：更近sc统计相关信息条数 <br>
* 创建时间：2013-5-11 下午11:51:02
*
* @author liyang
* @param lockMode
* @param cacheName
* @param sc
* @return
*/
public Object statBySearchCriterion(
final LockModeType lockMode, final ConstantsEnum.EHCACHE cacheName, SearchCriterion sc) {
if (sc == null) {
sc = new SearchCriterion();
}
Map<String, Object> map = sc.getFilterMap();
Query query = entityManager.createQuery("select count(*) " + this.getJPQL(sc));
if (map != null && !map.isEmpty()) {
for (String str : map.keySet()) {
if (str.split("\\.").length > 1) {
query.setParameter(str.split("\\.")[1], map.get(str));
} else {
query.setParameter(str, map.get(str));
}
}
}
map = sc.getFilterValue();
if (map != null && !map.isEmpty()) {
for (String str : map.keySet()) {
if (str.split("\\.").length > 1) {
query.setParameter(str.split("\\.")[1], map.get(str));
} else {
query.setParameter(str, map.get(str));
}
}
}
if (lockMode != null) {
query.setLockMode(lockMode);
}
if (cacheName != null) {
query.setHint("org.hibernate.cacheable", true);
query.setHint("org.hibernate.cacheRegion", cacheName.getStr());
}
return query.getSingleResult();
}
/**
* Apply the current transaction timeout, if any, to the given JPA Query object.
*
* <p>This method sets the JPA 2.0 query hints "javax.persistence.lock.timeout" and
* "javax.persistence.query.timeout" accordingly.
*
* @param query the JPA Query object
* @param emf JPA EntityManagerFactory that the Query was created for
*/
public static void applyTransactionTimeout(Query query, EntityManagerFactory emf) {
EntityManagerHolder emHolder =
(EntityManagerHolder) TransactionSynchronizationManager.getResource(emf);
if (emHolder != null && emHolder.hasTimeout()) {
int timeoutValue = emHolder.getTimeToLiveInSeconds();
query.setHint("javax.persistence.lock.timeout", timeoutValue);
query.setHint("javax.persistence.query.timeout", timeoutValue);
}
}
/**
* 描述：根据属性查找相关信息 <br>
* 创建时间：2013-5-11 下午10:57:59
*
* @author liyang
* @param propertyName
* @param value
* @param lockMode
* @param cacheName
* @param rowStartIdxAndCount
* @return
*/
@SuppressWarnings("unchecked")
public List<T> findByProperty(
final String propertyName,
final Object value,
final LockModeType lockMode,
final ConstantsEnum.EHCACHE cacheName,
final List<SortCriterion> sortList,
final int... rowStartIdxAndCount) {
try {
StringBuilder sb = new StringBuilder();
sb.append("select model from ");
sb.append(clazz.getName());
sb.append(" model where model.");
sb.append(propertyName);
sb.append(" = :propertyValue ");
if (sortList != null && !sortList.isEmpty()) {
sb.append(" order by ");
for (SortCriterion s : sortList) {
sb.append(s.getSortField());
sb.append(" ");
sb.append(s.getSortType());
sb.append(",");
}
sb.replace(sb.length() - 1, sb.length(), "");
}
Query query = entityManager.createQuery(sb.toString());
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);
}
}
}
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();
} catch (RuntimeException re) {
logger.error("find by property " + propertyName + " failed", re);
throw re;
}
}
@Test
public void testJpaSecondLevelCache() {
String jpql = "FROM Department d";
EntityManager entityManager = entityManagerFactory.createEntityManager();
Query query = entityManager.createQuery(jpql);
List<Department> departments = query.setHint(QueryHints.HINT_CACHEABLE, true).getResultList();
entityManager.close();
entityManager = entityManagerFactory.createEntityManager();
query = entityManager.createQuery(jpql);
departments = query.setHint(QueryHints.HINT_CACHEABLE, true).getResultList();
entityManager.close();
}
/**
* 多条件查询
*
* @param lockMode
* @param cacheName 二级缓存的名字，ehcache定义的
* @param sc 多条件组合查询类
* @return 返回实体类集合
*/
@SuppressWarnings("unchecked")
public List<T> findBySearchCriterion(
final LockModeType lockMode, final ConstantsEnum.EHCACHE cacheName, SearchCriterion sc) {
// 拼装查询条件
if (sc == null) {
sc = new SearchCriterion();
}
Map<String, Object> map = sc.getFilterMap();
Query query = entityManager.createQuery(" select t " + this.getJPQL(sc));
if (map != null && !map.isEmpty()) {
for (String str : map.keySet()) {
if (str.split("\\.").length > 1) {
query.setParameter(str.split("\\.")[1], map.get(str));
} else {
query.setParameter(str, map.get(str));
}
}
}
map = sc.getFilterValue();
if (map != null && !map.isEmpty()) {
for (String str : map.keySet()) {
if (str.split("\\.").length > 1) {
query.setParameter(str.split("\\.")[1], map.get(str));
} else {
query.setParameter(str, map.get(str));
}
}
}
if (sc.getPager() != null) {
int rowStartIdx = Math.max(0, sc.getPager().getStartRow());
if (rowStartIdx > 0) {
query.setFirstResult(rowStartIdx);
}
int rowCount = Math.max(0, sc.getPager().getPageSize());
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();
}
private Query recreateQuery(Query current, String newSqlString) {
Query result = entityManager.createQuery(newSqlString);
for (Entry<String, Object> hint : current.getHints().entrySet()) {
result.setHint(hint.getKey(), hint.getValue());
}
return result;
}
@SuppressWarnings("unchecked")
public List<Store> readAllStoresByState(final String state) {
Query query = em.createNamedQuery("BC_FIND_ALL_STORES_BY_STATE");
query.setParameter("state", state);
query.setHint(QueryHints.HINT_CACHEABLE, true);
return query.getResultList();
}
/**
* @param user1
* @param user2
* @return
*/
@SuppressWarnings({"unused", "unchecked", "unchecked"})
private List<ContentItem> getUserEditingInteractivity(User user1, User user2) {
List<ContentItem> result = new LinkedList<ContentItem>();
String s =
" select ci from ContentItem ci join fetch ci.resource left outer join fetch ci.textContent tc "
+ " where ci.author.login =:user1 and tc.contentItem.author.login =:user2";
javax.persistence.Query q = entityManager.createQuery(s);
q.setParameter("user1", user1.getLogin());
q.setParameter("user2", user2.getLogin());
q.setHint("org.hibernate.cacheable", true);
try {
result = (List<ContentItem>) q.getResultList();
} catch (PersistenceException ex) {
ex.printStackTrace();
log.warn("error while listing user: query failed");
}
if (result == null) {
return Collections.EMPTY_LIST;
} else {
return result;
}
}
public void findAllEmployeesWithPhoneNumbers() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.isEmpty("phoneNumbers").not();
ReadAllQuery raq = new ReadAllQuery(Employee.class);
raq.setSelectionCriteria(whereClause);
raq.useDistinct();
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString = "SELECT DISTINCT e FROM Employee e, IN (e.phoneNumbers) l";
Query query = em.createQuery(ejbqlString);
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
query.setHint(QueryHints.SERIALIZED_OBJECT, "false");
}
List firstResult = query.getResultList();
String alternateEjbqlString = "SELECT e FROM Employee e WHERE e.phoneNumbers IS NOT EMPTY";
List secondResult = em.createQuery(alternateEjbqlString).getResultList();
// 14 employees returned
Assert.assertEquals(
"Ejbql statements returned different results: data validation error",
firstResult.size(),
14);
Assert.assertTrue(
"Equivalent Ejbql statements returned different results",
comparer.compareObjects(secondResult, firstResult));
Assert.assertTrue(
"Find all employees with phone numbers test failed",
comparer.compareObjects(expectedResult, firstResult));
}
/**
* @param user1
* @param user2
* @return
*/
@SuppressWarnings({"unused", "unchecked", "unchecked"})
private List<CommentActivity> getUserCommentInteractivity(User user1, User user2) {
List<CommentActivity> result = new LinkedList<CommentActivity>();
String s =
"select a "
+ "from CommentActivity a inner join fetch a.comment left outer join a.contentItem as cia "
+ "where cia.author = :user1 and a.comment.author = :user2";
Query q = entityManager.createQuery(s);
q.setParameter("user1", user1);
q.setParameter("user2", user2);
q.setHint("org.hibernate.cacheable", true);
try {
result = (List<CommentActivity>) q.getResultList();
} catch (PersistenceException ex) {
ex.printStackTrace();
log.warn("error while listing user: query failed");
}
if (result == null) {
return Collections.EMPTY_LIST;
} else {
return result;
}
}
@Override
public List<Dish> genDishByBudgetAndCate(double budget, int cate) {
if (budget > 0 && cate != 0) {
EntityManager em = entityManager;
try {
Query q =
em.createNativeQuery(
"Select * From dish Where price <= ? And id_category = ?", Dish.class);
q.setParameter(1, budget);
q.setParameter(2, cate);
q.setHint(QueryHints.MAINTAIN_CACHE, HintValues.FALSE);
List<Dish> tmpLstDish = q.getResultList();
if (tmpLstDish.size() > 10 && tmpLstDish != null) {
List<Dish> lstDish = new ArrayList<Dish>();
Collections.shuffle(tmpLstDish);
for (int i = 0; i < 10; i++) {
lstDish.add(tmpLstDish.get(i));
}
return lstDish;
} else {
return tmpLstDish;
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
@SuppressWarnings("unchecked")
public List<Store> readAllStores() {
Query query = em.createNamedQuery("BC_FIND_ALL_STORES");
query.setHint("org.hibernate.cacheable", true);
List results = query.getResultList();
return results;
}
private Query recreateQuery(Query current, String newSqlString) {
Query result = entityManager.createQuery(newSqlString);
Map<String, Object> hints = current.getHints();
for (String hintName : hints.keySet()) {
result.setHint(hintName, hints.get(hintName));
}
return result;
}
public List<Enfermedades> enfermByTipoAntc(String tipo) {
StringBuilder sb = new StringBuilder("SELECT object(o) FROM Enfermedades as o");
sb.append(" WHERE o.enfermedad LIKE :tipo");
Query q = em.createQuery(sb.toString());
q.setParameter("tipo", tipo);
q.setHint("eclipselink.refresh", true);
return q.getResultList();
}
public List<Grupo> listarPorAlumno(Alumno alumno) {
Query query = em.createNamedQuery("Grupo.listarPorAlumno");
query.setParameter("idAlumno", alumno.getIdAlumno());
query.setHint(QueryHints.REFRESH, HintValues.TRUE);
List<Grupo> grupos = query.getResultList();
return grupos;
}
public Grupo buscar(String idGrupo) {
Query query = em.createNamedQuery("Grupo.buscar");
query.setParameter("idGrupo", Integer.parseInt(idGrupo));
query.setHint(QueryHints.REFRESH, HintValues.TRUE);
Grupo grupo = (Grupo) query.getSingleResult();
return grupo;
}
@SuppressWarnings("unchecked")
public Store readStoreByStoreCode(final String storeCode) {
Query query = em.createNamedQuery("BC_FIND_STORE_BY_STORE_NAME");
query.setParameter("storeName", storeCode.toUpperCase());
query.setHint(QueryHints.HINT_CACHEABLE, true);
List result = query.getResultList();
return (result.size() > 0) ? (Store) result.get(0) : null;
}
/* (non-Javadoc)
* @see kiwi.api.history.HistoryService#listVisitsByUser(kiwi.model.user.User)
*/
public List<VisitActivity> listVisitsByUser(User user) {
log.debug("listing user visits...");
Query q = entityManager.createNamedQuery("activities.listLastUserVisits");
q.setParameter("login", user.getLogin());
q.setHint("org.hibernate.cacheable", true);
return q.getResultList();
}
public Collection<ConfigurationParameterEntity> findByPath(String path) {
Query query =
entityManager.createQuery("from ConfigurationParameterEntity where id like :path");
if (isCacheable()) {
query.setHint("org.hibernate.cacheable", true);
}
query.setParameter("path", path + "%");
return query.getResultList();
}
/* (non-Javadoc)
* @see kiwi.api.history.HistoryService#listLastDistinctSearchesByUser(kiwi.model.user.User)
*/
public List<SearchActivity> listLastDistinctSearchesByUser(User user) {
log.debug("listing last distinct user searches...");
Query q = entityManager.createNamedQuery("activities.listLastDistinctUserSearches");
q.setParameter("login", user.getLogin());
q.setHint("org.hibernate.cacheable", true);
q.setMaxResults(10);
return q.getResultList();
}
/* (non-Javadoc)
* @see kiwi.api.history.HistoryService#listLastTagsByUser(kiwi.model.user.User)
*/
public List<AddTagActivity> listLastTagsByUser(User user) {
log.debug("listing last user tags...");
Query q = entityManager.createNamedQuery("activities.listLastUserTags");
q.setParameter("login", user.getLogin());
q.setHint("org.hibernate.cacheable", true);
q.setMaxResults(histSize);
return q.getResultList();
}
@SuppressWarnings("unchecked")
public Store readStoreByStoreCode(final String storeCode) {
Query query = em.createNamedQuery("FIND_STORE_BY_STORE_CODE");
query.setParameter("abbreviation", storeCode.toUpperCase());
// TODO use the property injection for "org.hibernate.cacheable" like the other daos
query.setHint("org.hibernate.cacheable", true);
List result = query.getResultList();
return (result.size() > 0) ? (Store) result.get(0) : null;
}
private Query applyProperties(Query query) {
if (lockOptions.getLockMode() != LockMode.NONE) {
query.setLockMode(getLockMode(lockOptions.getLockMode()));
}
Object queryTimeout;
if ((queryTimeout = getProperties().get(QueryHints.SPEC_HINT_TIMEOUT)) != null) {
query.setHint(QueryHints.SPEC_HINT_TIMEOUT, queryTimeout);
}
return query;
}
/** Load all employees using Query from provided EntityManager */
@SuppressWarnings("unchecked")
public List<Employee> getAllEmployeesQuery(EntityManager em) {
Query query;
query = em.createQuery("from Employee");
query.setHint("org.hibernate.cacheable", true);
return query.getResultList();
}
public List<UUlcerasXPresion> listUlcerasXpresion(UOpcionPaciente opcPaciente, UUciMenu uciMenu) {
StringBuilder sb = new StringBuilder("select object(o) from UUlcerasXPresion o");
sb.append(
" where o.uOpcionPaciente = :opcPaciente AND O.uOpciones.uUciMenu = :uciMenu ORDER BY o.uOpciones.opcOrdinal");
Query q = em.createQuery(sb.toString());
q.setParameter("opcPaciente", opcPaciente);
q.setParameter("uciMenu", uciMenu);
q.setHint("eclipselink.refresh", true);
return q.getResultList();
}
/** Load employee using Query from provided EntityManager */
public Employee getEmployeeQuery(EntityManager em, int id) {
Query query;
query = em.createQuery("from Employee e where e.id=:id");
query.setParameter("id", id);
query.setHint("org.hibernate.cacheable", true);
return (Employee) query.getSingleResult();
}
@SuppressWarnings("unchecked")
public List<ReportTemplate> findReportTemplates(Long project_id) {
String sql =
"select * from report_templates where project_id = "
+ project_id
+ " order by position ASC ";
Query query = manager.createNativeQuery(sql, ReportTemplate.class);
query.setHint(QueryHints.HINT_CACHE_MODE, CacheMode.IGNORE);
List<?> result = query.getResultList();
return (List<ReportTemplate>) result;
}
public List<DiagnosticosPaciente> listDiagnosticoPacientes(Pacientes paciente) {
StringBuilder sb = new StringBuilder("select object(o) from DiagnosticosPaciente as o");
sb.append(" where o.pacientes = :pacientes");
sb.append(" and o.enfermedades.codigo between 'K00' and 'K14.9'");
sb.append(" order by o.fechaInicio desc");
Query q = em.createQuery(sb.toString());
q.setParameter("pacientes", paciente);
q.setHint("eclipselink.refresh", true);
// System.out.println("q "+q);
return q.getResultList();
}
