Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.bookselling.dao; import com.bookselling.domain.Address; import com.bookselling.domain.Name; import com.bookselling.domain.PhoneNumber; import com.bookselling.domain.User; import com.bookselling.domain.statistic.GenericChartUnit; import com.bookselling.domain.statistic.StatisticData; import com.bookselling.domain.status.AccountStatus; import com.bookselling.form.filter.UserFilterForm; import com.bookselling.form.orderby.SortType; import com.bookselling.form.orderby.UserOrderType; import com.bookselling.form.searchby.UserFilterType; import com.bookselling.util.PaginationData; import java.io.Serializable; import java.math.BigInteger; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.LinkedHashSet; import java.util.List; import java.util.Map; import java.util.Set; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.criterion.Order; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; import org.springframework.stereotype.Repository; /** * * @author Phan Phat */ @Repository public class UserDaoImpl extends GenericDao<User> implements UserDao { private int minPost = 2; private int USER_ID = 1; @Override public PaginationData get(int first, int items) { Criteria criteria = getSession().createCriteria(User.class); criteria.createAlias("account", "acc").createAlias("acc.role", "rl").add(Restrictions.eq("rl.id", USER_ID)); Set<User> users = new HashSet<>(criteria.list()); HibernateInitSupport.setCls(User.class); for (User user : users) HibernateInitSupport.initDomain(user); //Pagination PaginationData paginationData = new PaginationData( (long) criteria.setProjection(Projections.rowCount()).uniqueResult(), items, first, users); return paginationData; } @Override public Class<User> registeredClass() { return User.class; } @Override public User getUserByEmai(String email) { Query query = getSession() .createQuery("select distinct us from User us join us.account acc where acc.email = :email"); query.setParameter("email", email); try { User user = (User) query.list().iterator().next(); HibernateInitSupport.initUser(user); return user; } catch (Exception ex) { return null; } } @Override public User getUserByUsername(String username) { Query query = getSession() .createQuery("select distinct us from User us join us.account acc where acc.username = :username"); query.setParameter("username", username); try { User user = (User) query.list().iterator().next(); HibernateInitSupport.initUser(user); return user; } catch (Exception ex) { return null; } } @Override public PaginationData filter(UserFilterForm form, int first, int items) { Criteria criteria = getSession().createCriteria(User.class); //Get form data String keyword = form.getKeyword(); AccountStatus[] accStatus = form.getAccStatus(); UserFilterType searchBy = form.getSearchBy(); Date fromDate = form.getFromDate(); Date toDate = form.getToDate(); UserOrderType orderBy = form.getOrderBy(); SortType sortType = form.getSortType(); //To criteria criteria.createAlias("account", "acc").createAlias("acc.role", "rls").add(Restrictions.eq("rls.id", 1)); if (keyword != null && !keyword.isEmpty()) { keyword = "%" + keyword + "%"; if (searchBy == UserFilterType.ADDRESS) { Address address = new Address(); address.setAddress(keyword); criteria.add(Restrictions.like("contact.address", address)); } else if (searchBy == UserFilterType.EMAIL) { criteria.add(Restrictions.like("acc.email", keyword)); } else if (searchBy == UserFilterType.OWNER) { Name name = new Name(); name.setName(keyword); criteria.add(Restrictions.like("name", name)); } else if (searchBy == UserFilterType.PHONE) { PhoneNumber phone = new PhoneNumber(); phone.setPhoneNumber(keyword); criteria.add(Restrictions.like("contact.phone", phone)); } else if (searchBy == UserFilterType.USERNAME) { criteria.add(Restrictions.like("acc.username", keyword)); } } if (accStatus.length != 0) { criteria.add(Restrictions.in("acc.status", accStatus)); } if (fromDate != null) criteria.add(Restrictions.ge("acc.createdDate", fromDate)); if (toDate != null) criteria.add(Restrictions.le("acc.createdDate", toDate)); String propertyName = null; if (orderBy == UserOrderType.CREATEDDATE) propertyName = "acc.createdDate"; else if (orderBy == UserOrderType.NAME) propertyName = "name"; else if (orderBy == UserOrderType.STATUS) propertyName = "acc.status"; else if (orderBy == UserOrderType.USERNAME) propertyName = "acc.username"; //Ly s dng long rowCount = (long) criteria.setProjection(Projections.countDistinct("id")).uniqueResult(); //Ly id criteria.setProjection(null).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .setProjection(Projections.distinct(Projections.id())).setFirstResult(first).setMaxResults(items) .addOrder(sortType == SortType.ASC ? Order.asc(propertyName) : Order.desc(propertyName)); List<Integer> ids = new ArrayList<>(); for (Iterator<Integer> temp = criteria.list().iterator(); temp.hasNext();) ids.add(temp.next()); //Criteria ph Criteria subCriteria = getSession().createCriteria(User.class); subCriteria.createAlias("account", "acc").createAlias("acc.role", "rls").add(Restrictions.eq("rls.id", 1)) .add(Restrictions.in("id", ids.size() > 0 ? ids : Arrays.asList(-1))) .addOrder(sortType == SortType.ASC ? Order.asc(propertyName) : Order.desc(propertyName)); //get list Set<User> users = new LinkedHashSet<>(subCriteria.list()); for (User user : users) { HibernateInitSupport.initUser(user); } //Pagination PaginationData paginationData = new PaginationData(rowCount, items, first, users); return paginationData; } @Override public StatisticData<GenericChartUnit<Object>> registrationStat(Date fromDate, Date toDate) { StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>()); statisticData.setLabel("Lt ng k"); SQLQuery sqlQuery = getSession().createSQLQuery("select DATE(createdDate) date, count(*) " + "from account acc " + "where createdDate >= :fromDate and createdDate <= :toDate " + "group by date " + "order by date asc "); sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate); List<Object[]> rows = sqlQuery.list(); for (Object[] row : rows) { GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(), new ArrayList<Object>()); unit.getFigures().add(row[1]); statisticData.getData().add(unit); } return statisticData; } @Override public Set<User> whoHasMorePost() { SQLQuery sqlQuery = getSession() .createSQLQuery("select u.id " + "from user u, sellingPost slp " + "where u.id = slp.userId " + "group by u.id " + "having COUNT(*) > 0 " + "order by COUNT(*) desc " + "limit 5 "); List<Integer> data = sqlQuery.list(); Criteria criteria = getSession().createCriteria(User.class); if (data.size() != 0) criteria.add(Restrictions.in("id", data)); Set<User> users = new HashSet<>(criteria.list()); HibernateInitSupport.setCls(User.class); for (User user : users) { HibernateInitSupport.initDomain(user); } return users; } @Override public Map<User, Double> mostValueableBuyer() { Map<User, Double> stat = new HashMap<>(); SQLQuery sqlQuery = getSession().createSQLQuery( "select u.id, SUM(totalPrice) value " + "from user u, trade t " + "where u.id = t.buyerId " + "group by u.id " + "having value > 0 " + "order by value desc " + "limit 5 "); List<Object[]> rows = sqlQuery.list(); Set<Integer> ids = new HashSet<>(); for (Object[] row : rows) { ids.add((Integer) row[0]); } if (!ids.isEmpty()) { Criteria criteria = getSession().createCriteria(User.class); criteria.add(Restrictions.in("id", ids)); List<User> users = criteria.list(); int index = 0; HibernateInitSupport.setCls(User.class); for (User user : users) { HibernateInitSupport.initDomain(user); stat.put(user, (Double) rows.get(index)[1]); } } return stat; } @Override public int totalUser() { SQLQuery sqlQuery = getSession().createSQLQuery("select count(*) " + "from user"); BigInteger figure = (BigInteger) sqlQuery.uniqueResult(); return figure != null ? figure.intValue() : 0; } @Override public void ratingUser(Serializable id) { SQLQuery sqlQuery = getSession().createSQLQuery( "update account as acc_1 " + " inner join ( " + " select acc.id as accId, " + " avg(rt.point) as avgpoint, " + " count(slp.id) as numslp, " + " case " + " when avg(rt.point) > 3.0 then 'GOOD' " + " when avg(rt.point) < 1.5 then 'BAD' " + " else 'NATURAL' " + " end as userRating " + " from account as acc " + " join `user` as u on u.accountId = acc.id " + " join sellingpost as slp on slp.userId = u.id " + " join rating as rt on rt.sellingPostId = slp.id " + " where acc.id = :id " + " group by acc.id " + " having numslp >= :minPost " + " ) as temp on acc_1.id = temp.accId " + "set acc_1.rating = userRating"); sqlQuery.setInteger("id", (int) id).setInteger("minPost", minPost).executeUpdate(); } }