// 默认顺序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; }