多表连接一

来源：互联网发布：什么是java servlet 编辑：程序博客网时间：2024/06/13 09:35
package com.rey.controllers;import com.rey.entities.UserEntity;import com.rey.entities.UserResult;import org.apache.log4j.Logger;import org.apache.poi.util.SystemOutLogger;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.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");        String selectorName = request.getParameter("comBox");        logger.info("selectorName:"+selectorName);        ModelAndView mv = new ModelAndView();        String keyWord = request.getParameter("keyWord");        if(selectorName.equals("author")){//            StringBuffer lookHql = new StringBuffer("from UserEntity where authorName = '");//            lookHql.append(keyWord).append("'");            StringBuffer author = new StringBuffer("SELECT\n" +                    "\tq_user_tbl.area_name,\n" +                    "\tq_user_tbl.author_id,\n" +                    "\tq_user_releation_tbl.AuthorName,\n" +                    "\tq_user_releation_tbl.RelationType,\n" +                    "\tq_user_releation_tbl.RelationAuthorName,\n" +                    "\tq_author_pai_tbl.PaiName,\n" +                    "\tq_author_work_tbl.workName\n" +                    "FROM\n" +                    "\tq_user_tbl,\n" +                    "\tq_user_releation_tbl,\n" +                    "\tq_author_work_tbl,\n" +                    "\tq_author_pai_tbl\n" +                    "WHERE\n" +                    "\tq_user_tbl.author_id = q_user_releation_tbl.AuthorID\n" +                    "AND\tq_user_releation_tbl.AuthorID = q_author_work_tbl.author_id\n" +                    "AND q_author_work_tbl.author_id = q_author_pai_tbl.AuthorID\n" +                    "AND q_user_releation_tbl.AuthorID IN (");            author.append("SELECT\n" +                    "\t\tauthor_id\n" +                    "\tFROM\n" +                    "\t\tq_user_tbl\n" +                    "\tWHERE\n" +                    "\t\tauthor_name = '");            author.append(keyWord).append("');");//            UserEntity user= (UserEntity)sessionFactory.getCurrentSession().createQuery(lookHql.toString()).setMaxResults(1).uniqueResult();//            List<UserEntity> userList = sessionFactory.getCurrentSession().createQuery(lookHql.toString()).list();//            List<Map<String,Object>> userList = sessionFactory.getCurrentSession().createQuery(author.toString()).list();            List<Object[]> tempUserList = sessionFactory.getCurrentSession().createSQLQuery(author.toString()).list();            List<UserResult> userList = new ArrayList<UserResult>();            for (Object[] object : tempUserList) {                UserResult userResult = new UserResult();                userResult.setAreaName(object[0].toString());                userResult.setAuthorID(Integer.parseInt(object[1].toString()));                userResult.setAuthorName(object[2].toString());                userResult.setRelationType(object[3].toString());                userResult.setRelationAuthorName(object[4].toString());                userResult.setPaiName(object[5].toString());                userResult.setWorkName(object[6].toString());                userList.add(userResult);            }            int count=1;            while(count<userList.size()){                int authorId = userList.get(count-1).getAuthorID();                int authorId1 = userList.get(count).getAuthorID();                if(authorId==authorId1){                    String relationType = userList.get(count-1).getRelationType();                    String relationType1 =userList.get(count).getRelationType();                    StringBuffer type = new StringBuffer();                    StringBuffer name = new StringBuffer();                    if(relationType.equals(relationType1)){                    }else {                        type.append(relationType1);                    }                }            }            UserResult test = new UserResult();            test.getAreaName();            logger.info("userList:"+userList);            logger.info("end getSearchInfo:" + keyWord);            if(userList.size()>0){//                传结果对象                mv.addObject("userList",userList);//                设置跳转的视图对象                mv.setViewName("result");            }else{                mv.setViewName("error");            }        }        else if(selectorName.equals("pai")){        }        else if(selectorName.equals("work")){        }        else if(selectorName.equals("area")){        }        return  mv;    }}
0 0