// 默认顺序createtime desc
public List<T> findByMapWithCond(Map<String, Object> params, String orderBy, Boolean asc) {
StringBuilder hql = new StringBuilder("from " + className);
String orderStr;
if (null == orderBy) {
orderStr = " order by createtime desc";
} else {
String ascStr = asc ? " asc" : " desc";
orderStr = " order by " + orderBy + ascStr;
}
Map<String, Object> alterParams = new HashMap<>();
Query query;
if (null != params && !params.isEmpty()) {
hql.append(" where ");
for (String field : params.keySet()) {
int i = field.indexOf(" ");
Assert.isTrue(i != -1, "Wrong condition, must have space inside!");
String ramdonName = "_" + Utils.getRandomString(8);
hql.append(field).append(" :").append(ramdonName).append(" and ");
if (field.contains("like")) {
String likeStr = "%" + params.get(field) + "%";
alterParams.put(ramdonName, likeStr);
} else alterParams.put(ramdonName, params.get(field));
}
hql.append("1=1");
query = getSession().createQuery(hql.append(orderStr).toString());
for (String field : alterParams.keySet()) query.setParameter(field, alterParams.get(field));
} else query = getSession().createQuery(hql.append(orderStr).toString());
return query.list();
}
/**
* Returns a list of all active and finished one time orders going back n number of months,
* containing the given item id for the given user.
*
* @param userId user id of orders
* @param itemId item id of order lines
* @param months previous number of months to include (1 = this month plus the previous)
* @return list of found one-time orders, empty list if none found
*/
@SuppressWarnings("unchecked")
public List<OrderLineDTO> findOnetimeByUserItem(Integer userId, Integer itemId, Integer months) {
final String hql =
"select line "
+ " from OrderLineDTO line "
+ "where line.deleted = 0 "
+ " and line.item.id = :itemId "
+ " and line.purchaseOrder.baseUserByUserId.id = :userId "
+ " and line.purchaseOrder.orderPeriod.id = :period "
+ " and (line.purchaseOrder.orderStatus.orderStatusFlag = :active_status"
+ " or line.purchaseOrder.orderStatus.orderStatusFlag = :finished_status)"
+ " and line.purchaseOrder.deleted = 0 "
+ " and line.purchaseOrder.createDate > :startdate";
Query query = getSession().createQuery(hql);
query.setParameter("itemId", itemId);
query.setParameter("userId", userId);
query.setParameter("period", Constants.ORDER_PERIOD_ONCE);
query.setParameter("active_status", OrderStatusFlag.INVOICE.ordinal());
query.setParameter("finished_status", OrderStatusFlag.FINISHED.ordinal());
DateMidnight startdate = new DateMidnight().minusMonths(months);
query.setParameter("startdate", startdate.toDate());
return query.list();
}
@Override
public void deleteSubscription(int userId, int forumId) {
// TODO Auto-generated method stub
Session session = this.sessionFactory.openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
Query query =
session.createQuery(
"delete from Subscription where userId = :userId and forumId = :forumId");
query.setParameter("userId", userId);
query.setParameter("forumId", forumId);
int result = query.executeUpdate();
System.out.println("Execute query..");
if (result > 0) {
tx.commit();
// System.out.println("Subscription Removed..");
} else {
// System.out.println("Subscription does not exist");
tx.rollback();
}
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
}
public int getCountOfSubscribers(int forumId, int userId) {
// TODO Auto-generated method stub
Session session = this.sessionFactory.openSession();
Transaction tx = null;
int countOfSubscribers = 0;
try {
tx = session.beginTransaction();
Query query = null;
if (userId == 0) {
query = session.createQuery("select count(*) from Subscription where forumId = :forumId");
query.setParameter("forumId", forumId);
} else {
query =
session.createQuery(
"select count(*) from Subscription where forumId = :forumId and userId = :userId");
query.setParameter("forumId", forumId);
query.setParameter("userId", userId);
}
Long count = (Long) query.uniqueResult();
countOfSubscribers = count.intValue();
// System.out.println("No of subscribers.."+countOfSubscribers);
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return countOfSubscribers;
}
private boolean isGroupMember(final User user, final GroupImpl group) {
final Query query =
entityManager.createNativeQuery(
"select 1 from GROUP_USER where USER_ID=:userId AND GROUP_ID=:groupId");
query.setParameter("userId", user.getId());
query.setParameter("groupId", group.getId());
return query.getResultList().size() > 0;
}
public void partGroup(final User user, final Long groupId) {
final Query query =
entityManager.createNativeQuery(
"DELETE FROM GROUP_USER WHERE GROUP_ID=:groupId AND USER_ID=:userId");
query.setParameter("groupId", groupId);
query.setParameter("userId", user.getId());
query.executeUpdate();
}
public void deletePublicMessageFor(final Long messageId, final User user) {
final Query query =
entityManager.createNativeQuery(
"delete from PUBLIC_MESSAGES where ID=:id and sender_id=:user_id");
query.setParameter("user_id", user.getId());
query.setParameter("id", messageId);
query.executeUpdate();
}
public void joinGroup(final User user, final Long groupId) {
final Query query =
entityManager.createNativeQuery(
"insert GROUP_USER SET GROUP_ID=:groupId, USER_ID=:userId, JOINING_DATE=:joiningDate, LAST_VISIT=:joiningDate");
query.setParameter("groupId", groupId);
query.setParameter("userId", user.getId());
query.setParameter("joiningDate", new Date());
query.executeUpdate();
}
public List<User> fetchUsersBeginningByAndAddableToWorkout(final String prefix, final long id) {
final Query query =
entityManager.createQuery(
"select u from UserImpl u, WorkoutImpl workout where u.name<>'googlebot' AND u.name LIKE CONCAT(:prefix, '%')"
+ " AND u not MEMBER OF workout.participants AND workout.id = :id ");
query.setParameter("prefix", prefix);
query.setParameter("id", id);
return query.getResultList();
}
public static Customer findCustomerByIndexedParameter(
EntityManager entityManager, String first, String last) {
Query query =
entityManager.createQuery(
"from Customer c " + " where c.firstName=?1" + " and c.lastName=?2");
query.setParameter(1, first);
query.setParameter(2, last);
return (Customer) query.getSingleResult();
}
private void updateLastGroupVisit(final User user, final GroupImpl group) {
if (group != null) {
final Query query =
entityManager.createNativeQuery(
"update GROUP_USER SET LAST_VISIT=:now where GROUP_ID=:groupId and USER_ID=:userId");
query.setParameter("now", new Date());
query.setParameter("groupId", group.getId());
query.setParameter("userId", user.getId());
query.executeUpdate();
}
}
public void createTrack(
final User user, final String title, final String points, final double length) {
final Query query =
entityManager.createNativeQuery(
"INSERT INTO TRACKS SET OWNER_ID=:userId, TITLE=:title, POINTS=:points, LENGTH=:length");
query.setParameter("userId", user.getId());
query.setParameter("title", title);
query.setParameter("points", points);
query.setParameter("length", length);
query.executeUpdate();
}
public void updateMessage(
final User user, final Long messageId, final String content, final MessageKind kind)
throws AccessDeniedException {
final String table = kind == MessageKind.PRIVATE ? "MESSAGES" : "PUBLIC_MESSAGES";
final Query query =
entityManager.createNativeQuery(
"update " + table + " SET CONTENT=:content where ID=:id and SENDER_ID=:userId");
query.setParameter("content", content);
query.setParameter("id", messageId);
query.setParameter("userId", user.getId());
if (query.executeUpdate() != 1) throw new AccessDeniedException();
}
public StatisticsPageData fetchStatisticsPageData(final Long userId, final String discipline)
throws UserNotFoundException {
final User user = fetchUser(userId);
final String fromWhereClause =
" from WorkoutImpl w where :user MEMBER OF w.participants "
+ (discipline != null ? " and discipline = :discipline " : " ");
final Query disciplineQuery =
query(
"select w.discipline, count(*) from WorkoutImpl w "
+ "WHERE :user MEMBER OF w.participants GROUP BY w.discipline ORDER BY count(*) DESC");
disciplineQuery.setParameter("user", user);
final Query query = query("select sum(w.distance) " + fromWhereClause);
bindQuery(query, user, discipline);
final Query query2 =
query(
"select year(w.date), sum(w.distance), sum(w.duration), sum(w.energy) "
+ fromWhereClause
+ " GROUP BY year(w.date) ORDER BY year(w.date) DESC");
bindQuery(query2, user, discipline);
final Query query3 =
query(
"select year(w.date), month(w.date), sum(w.distance), sum(w.duration), sum(w.energy) "
+ fromWhereClause
+ "GROUP BY year(w.date), month(w.date) "
+ "ORDER BY year(w.date) DESC, month(w.date) DESC");
bindQuery(query3, user, discipline);
return new StatisticsPageData(
user,
disciplineQuery.getResultList(),
query.getSingleResult(),
query2.getResultList(),
query3.getResultList());
}
/** Read address. */
public void test() throws Exception {
EntityManager manager = createEntityManager();
Query query = manager.createQuery("Select a from Address a where a.street = :street");
query.setParameter("street", street);
Address address = (Address) uniqueResult(query, manager);
manager.close();
}
private void separatePrivateMessagesFromWorkout(final Long id) {
final Query query =
entityManager.createNativeQuery(
"UPDATE MESSAGES SET WORKOUT_ID=NULL WHERE WORKOUT_ID=:id AND RECEIVER_ID IS NOT NULL");
query.setParameter("id", id);
query.executeUpdate();
}
private Collection<ConversationSummary> fetchCorrespondents(final User user) {
final Map<String, ConversationSummary> correspondants =
new HashMap<String, ConversationSummary>();
{
final Query query =
query(
"select m.sender.name, m.receiver.name, count(m), m.read from PrivateMessageImpl m where (m.receiver=:user OR "
+ "(m.sender=:user)) AND(m.deleter IS NULL OR m.deleter <> :user) "
+ "group by m.sender.name, m.receiver.name, m.read");
query.setParameter("user", user);
for (final Object[] row : (List<Object[]>) query.getResultList()) {
final boolean sent = row[0].equals(((UserImpl) user).getBareName());
final String name = (String) (sent ? row[1] : row[0]);
final ConversationSummary previous = correspondants.get(name);
final long count = ((Number) row[2]).longValue();
final long newCount;
if (row[3].equals(Boolean.FALSE) && !sent) newCount = count;
else newCount = 0;
if (previous != null) {
correspondants.put(
name,
new ConversationSummary(
UserStringImpl.valueOf(name),
count + previous.messageCount,
newCount + previous.newMessageCount));
} else
correspondants.put(
name, new ConversationSummary(UserStringImpl.valueOf(name), count, newCount));
}
}
return new TreeSet<ConversationSummary>(correspondants.values());
}
private Double fetchGlobalDistance(final Group group) {
final Query query =
query(
"select sum(w.distance) from GroupImpl g left join g.members u left join u.workouts w where g=:group");
query.setParameter("group", group);
return (Double) query.getSingleResult();
}
public List<NewMessageData> fetchNewMessagesCount(final User user) {
final Query query =
query(
"select new com.nraynaud.sport.data.NewMessageData(m.sender.name, count(m)) from PrivateMessageImpl m where m.receiver = :user and m.read = false group by m.sender.name");
query.setParameter("user", user);
return query.getResultList();
}
public void testPagingInCollection() throws Exception {
// create objects in database
Formula formula = Formula.findByName("HTML TEST");
Ingredient ingredient = Ingredient.findByName("LECHE");
for (int x = 0; x <= 12; x++) {
FormulaIngredient fi = new FormulaIngredient();
fi.setFormula(formula);
fi.setIngredient(ingredient);
XPersistence.getManager().persist(fi);
}
XPersistence.commit();
//
execute("Mode.detailAndFirst");
assertValue("name", "HTML TEST");
assertCollectionRowCount("ingredients", 10);
checkRowCollection("ingredients", 0);
execute("List.goNextPage", "collection=ingredients");
execute("List.goPreviousPage", "collection=ingredients");
assertRowCollectionChecked("ingredients", 0);
// remove objects from database
String sentencia = " DELETE FROM FormulaIngredient WHERE ingredient.oid = :ingredient ";
Query query = XPersistence.getManager().createQuery(sentencia);
query.setParameter("ingredient", ingredient.getOid());
query.executeUpdate();
XPersistence.commit();
}
/** Read all address. */
public void test() throws Exception {
EntityManager manager = createEntityManager();
Query query = manager.createQuery("Select a from Address a where a.city = :city");
query.setParameter("city", "Ottawa");
List result = list(query, manager);
manager.close();
}
private PaginatedCollection<User> fetchGroupMembers(final Group group) {
final String queryString =
"select u from GroupImpl g inner join g.members u where g=:group order by u.name";
final Query query = query(queryString);
query.setParameter("group", group);
return paginateList(0, 100, query.getResultList());
}
public List<E> getObjectFromNamedQuery(String namedQuery, Object... parametros) {
EntityManager em = getEntityManager();
EntityTransaction tx = em.getTransaction();
int position = 1;
List<E> array = new ArrayList<E>();
try {
tx.begin();
Query q = em.createNamedQuery(namedQuery);
for (Object parametro : parametros) {
q.setParameter(position++, parametro);
}
tx.commit();
array = q.getResultList();
} finally {
if (tx.isActive()) {
tx.rollback();
}
em.close();
}
return array;
}
@Override
public void deleteAll(Trading trading) {
final Query query = em.createQuery("DELETE FROM Withdrawal w WHERE w.pk.trading=:trading");
query.setParameter("trading", trading);
query.executeUpdate();
}
/**
* Find order lines by user ID and where description is like the given string. This method can
* accept wildcard characters '%' for matching.
*
* @param userId user id
* @param like string to match against order line description
* @return list of found orders lines, empty if none
*/
@SuppressWarnings("unchecked")
public List<OrderLineDTO> findByDescriptionLike(Integer userId, String like) {
final String hql =
"select line "
+ " from OrderLineDTO line "
+ "where line.deleted = 0 "
+ " and line.purchaseOrder.deleted = 0 "
+ " and line.purchaseOrder.baseUserByUserId.id = :userId "
+ " and line.description like :description";
Query query = getSession().createQuery(hql);
query.setParameter("userId", userId);
query.setParameter("description", like);
return query.list();
}
@Override
public List<Reply> getRepliesToPost(int postId) {
// TODO Auto-generated method stub
Session session = sessionFactory.openSession();
Transaction tx = null;
List<Reply> replyArr = new ArrayList<Reply>();
try {
tx = session.beginTransaction();
Query query =
session.createQuery("from Reply where postId = :postId order by createdDate desc");
query.setParameter("postId", postId);
replyArr = query.list();
/* for(Reply reply : replyArr){
System.out.println("Reply - id----"+reply.getReplyId());
System.out.println("Reply - Description----"+reply.getDescription());
System.out.println("Reply - Post id----"+reply.getPostId());
}*/
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return replyArr;
}
private Collection<GroupData> fetchGroupDataForUser(
final User user, final boolean restrictToSuscribed) {
final String ifConnectedColumns =
"max(ifnull(USER_ID=:userId, false))>0, sum(ifnull(USER_ID=:userId AND LAST_VISIT<PUBLIC_MESSAGES.`DATE`, false))";
final Query query =
entityManager.createNativeQuery(
"select GROUPS.ID, name, count(DISTINCT GROUP_USER.USER_ID), "
+ (user != null ? ifConnectedColumns : " 0,0")
+ " from GROUPS left join GROUP_USER on GROUP_ID=ID "
+ " left join PUBLIC_MESSAGES on PUBLIC_MESSAGES.GROUP_ID=GROUP_USER.GROUP_ID "
+ (restrictToSuscribed ? " where GROUP_USER.USER_ID=:userId" : "")
+ " group by GROUPS.ID order by CREATION_DATE");
if (user != null) query.setParameter("userId", user.getId());
final List<Object[]> list = query.getResultList();
final Collection<GroupData> result = new ArrayList<GroupData>(list.size());
for (final Object[] o : list)
result.add(
new GroupData(
((Number) o[0]).longValue(),
UserStringImpl.valueOf(String.valueOf(o[1])),
((Number) o[2]).longValue(),
((Number) o[3]).intValue() != 0,
((Number) o[4]).intValue()));
return result;
}
public void deleteMessageFor(final Long id, final User user) {
final Query query =
entityManager.createNativeQuery(
"update MESSAGES SET deleted_by=:user_id where ID=:id and deleted_by IS NULL and receiver_id <> sender_id and (receiver_id=:user_id or sender_id=:user_id)");
query.setParameter("user_id", user.getId());
query.setParameter("id", id);
final int updated = query.executeUpdate();
if (updated == 0) {
final Query query2 =
entityManager.createNativeQuery(
"delete from MESSAGES where ID=:id and (receiver_id=:user_id or sender_id=:user_id)");
query2.setParameter("id", id);
query2.setParameter("user_id", user.getId());
query2.executeUpdate();
}
}
@Override
public List<Forum> getForumsOfaUser(int userId) {
// TODO Auto-generated method stub
List<Forum> forums = new ArrayList<Forum>();
Session session = sessionFactory.openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
Query query = session.createQuery("from Subscription where userId = :userId");
query.setString("userId", String.valueOf(userId));
List<Subscription> userSubscribedGroups = query.list();
for (Subscription subsc : userSubscribedGroups) {
query = session.createQuery("from Forum where forumId = :forumId");
query.setParameter("forumId", subsc.getForumId());
// add this to a list variable query.uniqueResult();
forums.add((Forum) query.uniqueResult());
}
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return forums;
}
public List<T> namedQueryListResult(
String namedQuery, int maxSize, Map<String, Object> parameters) {
Session session = openSession(true);
List<T> results = Lists.newArrayList();
try {
Query query = namedQuery(namedQuery);
for (Map.Entry<String, Object> entry : parameters.entrySet()) {
query.setParameter(entry.getKey(), entry.getValue());
}
if (maxSize != -1) {
query.setMaxResults(maxSize);
}
results = query.list();
for (T result : results) {
if (result != null) {
result.init();
}
}
commitTransaction(session, true);
} catch (Exception e) {
rollbackTransaction(session, true);
} finally {
closeSession(session);
}
return results;
}