com.bookselling.dao.UserDaoImpl.java Source code

Java tutorial

Introduction

Here is the source code for com.bookselling.dao.UserDaoImpl.java

Source

/*
 * 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();
    }
}