多表连接二（更优解决办法）

package com.rey.controllers;

import com.rey.entities.AuthorPai;
import com.rey.entities.AuthorWork;
import com.rey.entities.UserRelation;
import com.rey.entities.UserResult;
import org.apache.log4j.Logger;
import org.hibernate.SessionFactory;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by psq on 2016/8/16.
 */
@RestController
@RequestMapping(value = "/common")
public class CommonController {
    private Logger logger = Logger.getLogger(CommonController.class);

    @Resource
    private SessionFactory sessionFactory;


    /**
     * 搜索模糊匹配查询
     * @return
     */
    @RequestMapping(value = "/search",produces = "application/json; charset=utf-8")
    @Transactional(propagation = Propagation.REQUIRED,readOnly = true)
    public ModelAndView getSearchInfo(HttpServletRequest request){
        logger.info("begin getSearchInfo");

        try{
            request.setCharacterEncoding("utf8");
        }catch(Exception e){
            e.printStackTrace();
        }

        String selectorName = request.getParameter("comBox");
        logger.info("selectorName:"+selectorName);

        ModelAndView mv = new ModelAndView();

        String keyWord = request.getParameter("keyWord");
        logger.info("keyWord:"+keyWord);

        List<UserResult> userList = new ArrayList<UserResult>();
        if(selectorName.equals("author")){
            queryUserByAuthor(userList, keyWord);
        } else if(selectorName.equals("pai")){

        } else if(selectorName.equals("work")){

        } else if(selectorName.equals("area")){

        }
        fillUserRelationList(userList);
        fillPaiNameList(userList);
        fillWorkNameList(userList);
        logger.info("userList:"+userList);
        logger.info("end getSearchInfo:" + keyWord);
        if(userList.size()>0){
            mv.addObject("userList",userList);
            mv.setViewName("result");
        }else{
            mv.setViewName("error");
        }
        return  mv;
    }

    /**
     * 根据用户姓名查询用户
     *
     * @param userList
     * @param keyWord
     */
    private void queryUserByAuthor(List<UserResult> userList, String keyWord) {
        String queryAuthor = "SELECT author_id, author_name, area_name FROM q_user_tbl where author_name = '" + keyWord + "';";
        List<Object[]> tempUserList = sessionFactory.getCurrentSession().createSQLQuery(queryAuthor).list();
        for (Object[] object : tempUserList) {
            UserResult userResult = new UserResult();
            userResult.setAuthorID(Integer.parseInt(object[0].toString()));
            userResult.setAuthorName(object[1].toString());
            userResult.setAreaName(object[2].toString());
            userList.add(userResult);
        }
    }

    /**
     * 填充用户作品信息
     *
     * @param userList
     */
    private void fillWorkNameList(List<UserResult> userList) {
        String userIdStr = buildUserIdStr(userList);
        if ("".equals(userIdStr)) {
            return;
        }
        String queryWork = "SELECT author_id, workName FROM q_author_work_tbl where author_id in " + userIdStr + ";";
        List<Object[]> tempWorkList = sessionFactory.getCurrentSession().createSQLQuery(queryWork).list();
        List<AuthorWork> authorWorkList = new ArrayList<AuthorWork>();
        for (Object[] object : tempWorkList) {
            AuthorWork authorWork = new AuthorWork();
            authorWork.setAuthorId(Integer.parseInt(object[0].toString()));
            authorWork.setWorkName(object[1].toString());
            authorWorkList.add(authorWork);
        }
        Map<Integer, List<String>> userIdAndWorkMap = new HashMap<Integer, List<String>>();
        for (AuthorWork authorWork : authorWorkList) {
            Integer userId = authorWork.getAuthorId();
            if (userIdAndWorkMap.get(userId) != null) {
                userIdAndWorkMap.get(userId).add(authorWork.getWorkName());
                continue;
            }
            List<String> workList = new ArrayList<String>();
            workList.add(authorWork.getWorkName());
            userIdAndWorkMap.put(userId, workList);
        }
        for (UserResult userResult : userList) {
            List<String> workList = userIdAndWorkMap.get(userResult.getAuthorID());
            if (workList == null) {
                workList = new ArrayList<String>();
            }
            userResult.setWorkNameList(workList);
        }

    }

    /**
     * 填充用户门派信息
     *
     * @param userList
     */
    private void fillPaiNameList(List<UserResult> userList) {
        String userIdStr = buildUserIdStr(userList);
        if ("".equals(userIdStr)) {
            return;
        }
        String queryRelation = "SELECT AuthorID, PaiName FROM q_author_pai_tbl where AuthorID in " + userIdStr + ";";
        List<Object[]> tempPaiList = sessionFactory.getCurrentSession().createSQLQuery(queryRelation).list();
        List<AuthorPai> authorPaiList = new ArrayList<AuthorPai>();
        for (Object[] object : tempPaiList) {
            AuthorPai authorPai = new AuthorPai();
            authorPai.setAuthorId(Integer.parseInt(object[0].toString()));
            authorPai.setPaiName(object[1].toString());
            authorPaiList.add(authorPai);
        }
        Map<Integer, List<String>> userIdAndPaiMap = new HashMap<Integer, List<String>>();
        for (AuthorPai authorPai : authorPaiList) {
            Integer userId = authorPai.getAuthorId();
            if (userIdAndPaiMap.get(userId) != null) {
                userIdAndPaiMap.get(userId).add(authorPai.getPaiName());
                continue;
            }
            List<String> paiList = new ArrayList<String>();
            paiList.add(authorPai.getPaiName());
            userIdAndPaiMap.put(userId, paiList);
        }
        for (UserResult userResult : userList) {
            List<String> PaiList = userIdAndPaiMap.get(userResult.getAuthorID());
            if (PaiList == null) {
                PaiList = new ArrayList<String>();
            }
            userResult.setPaiNameList(PaiList);
        }
    }

    /**
     * 填充用户关系信息
     *
     * @param userList
     */
    private void fillUserRelationList(List<UserResult> userList) {
        String userIdStr = buildUserIdStr(userList);
        if ("".equals(userIdStr)) {
            return;
        }
        String queryRelation = "SELECT AuthorID, RelationType, RelationAuthorName FROM q_user_releation_tbl where AuthorID in " + userIdStr + ";";
        List<Object[]> tempRelationList = sessionFactory.getCurrentSession().createSQLQuery(queryRelation).list();
        List<UserRelation> userRelationList = new ArrayList<UserRelation>();
        for (Object[] object : tempRelationList) {
            UserRelation userRelation = new UserRelation();
            userRelation.setAuthorID(Integer.parseInt(object[0].toString()));
            userRelation.setRelationType(object[1].toString());
            userRelation.setRelationAuthorName(object[2].toString());
            userRelationList.add(userRelation);
        }
        Map<Integer, List<UserRelation>> userIdAndUserRelationMap = new HashMap<Integer, List<UserRelation>>();
        for (UserRelation userRelation : userRelationList) {
            Integer userId = userRelation.getAuthorID();
            if (userIdAndUserRelationMap.get(userId) != null) {
                userIdAndUserRelationMap.get(userId).add(userRelation);
                continue;
            }
            List<UserRelation> userRelations = new ArrayList<UserRelation>();
            userRelations.add(userRelation);
            userIdAndUserRelationMap.put(userId, userRelations);
        }

        Map<Integer, List<UserRelation>> userIdAndUserRelationMapGroupByRelationType = new HashMap<Integer, List<UserRelation>>();
        for (Integer userId : userIdAndUserRelationMap.keySet()) {
            Map<String, String> relationTypeAndRelationNameMap = new HashMap<String, String>();
            List<UserRelation> userRelations = userIdAndUserRelationMap.get(userId);
            for (UserRelation userRelation : userRelations) {
                String relationType = userRelation.getRelationType();
                String relationName = "";
                if (relationTypeAndRelationNameMap.get(relationType) != null) {
                    String rawRelationName = relationTypeAndRelationNameMap.get(relationType);
                    relationName = rawRelationName + ", " + userRelation.getRelationAuthorName();
                } else {
                    relationName = userRelation.getRelationAuthorName();
                }
                relationTypeAndRelationNameMap.put(relationType, relationName);
            }
            List<UserRelation> relations = new ArrayList<UserRelation>();
            for (String relationType : relationTypeAndRelationNameMap.keySet()) {
                UserRelation userRelation = new UserRelation();
                userRelation.setAuthorID(userId);
                userRelation.setRelationType(relationType);
                userRelation.setRelationAuthorName(relationTypeAndRelationNameMap.get(relationType));
                relations.add(userRelation);
            }
            userIdAndUserRelationMapGroupByRelationType.put(userId, relations);
        }
        for (UserResult user : userList) {
            List<UserRelation> userRelations = userIdAndUserRelationMapGroupByRelationType.get(user.getAuthorID());
            if (userRelations == null) {
                userRelations = new ArrayList<UserRelation>();
            }
            user.setUserRelationList(userRelations);
        }
    }

    /**
     * 构建userId的sql语句
     *
     * @param userList
     * @return
     */
    private String buildUserIdStr(List<UserResult> userList) {
        List<Integer> userIdList = getUserIdList(userList);
        if (userIdList.size() < 1) {
            return "";
        }
        StringBuffer userIdStr = new StringBuffer(" (");
        for (Integer userId : userIdList) {
            userIdStr.append(userId + ",");
        }
        userIdStr.setLength(userIdStr.length() - 1); // 把最后的,去掉
        userIdStr.append(")");
        return userIdStr.toString();
    }

    /**
     * 获取userId
     *
     * @param userList
     * @return
     */
    private List<Integer> getUserIdList(List<UserResult> userList) {
        List<Integer> userIdList = new ArrayList<Integer>();
        for (UserResult user : userList) {
            userIdList.add(user.getAuthorID());
        }
        return userIdList;
    }


}