利用SSM(springmvc+spring+mybatis)实现多表联合查询

一、数据库

                         

Author表 （authorid主键）                                                                    NewsInfo表（newsno主键）                                     NewsType表（news_typeid主键）

二、实体类

package com.neusoft.po;import java.util.List;/** * 作者类 * @author hyc * */public class Author {private String authorid;//作者编号private String authorname;//作者名public Author() {super();}public Author(String authorid, String authorname) {super();this.authorid = authorid;this.authorname = authorname;}public String getAuthorid() {return authorid;}public void setAuthorid(String authorid) {this.authorid = authorid;}public String getAuthorname() {return authorname;}public void setAuthorname(String authorname) {this.authorname = authorname;}}

Author类

package com.neusoft.po;import java.util.Date;import java.util.List;/** * 新闻信息类 * @author hyc * */public class NewsInfo {private String newsno;//新闻编号private String news_title;//新闻标题private String news_author;//新闻作者private int news_type;//新闻种类private NewsType type;private Author author;public NewsInfo(String newsno, String news_title, String news_author,int news_type) {super();this.newsno = newsno;this.news_title = news_title;this.news_author = news_author;this.news_type = news_type;}public NewsInfo() {super();}public String getNewsno() {return newsno;}public void setNewsno(String newsno) {this.newsno = newsno;}public String getNews_title() {return news_title;}public void setNews_title(String news_title) {this.news_title = news_title;}public String getNews_author() {return news_author;}public void setNews_author(String news_author) {this.news_author = news_author;}public int getNews_type() {return news_type;}public void setNews_type(int news_type) {this.news_type = news_type;}}

NewsInfo类

package com.neusoft.po;import java.util.List;/** * 新闻类别类 * @author hyc * */public class NewsType {private  int news_typeid;//类别编号private String news_typename;//类别名称//一对多的关系    private List<NewsInfo> infoList;    public List<NewsInfo> getInfoList() {return infoList;}public void setInfoList(List<NewsInfo> infoList) {this.infoList = infoList;}public NewsType() {super();}public NewsType(int news_typeid, String news_typename,List<NewsInfo> infoList) {super();this.news_typeid = news_typeid;this.news_typename = news_typename;this.infoList = infoList;}public int getNews_typeid() {return news_typeid;}public void setNews_typeid(int news_typeid) {this.news_typeid = news_typeid;}public String getNews_typename() {return news_typename;}public void setNews_typename(String news_typename) {this.news_typename = news_typename;}}

NewsType类

三、Mapper

<resultMap type="com.neusoft.po.NewsInfo" id="newsInfoMap">    <id column="newsno" property="newsno" />    <result column="news_title" property="news_title"/>       <association property="author" javaType="com.neusoft.po.Author"><result column="authorname" property="authorname"/>   </association>      <association property="type" javaType="com.neusoft.po.NewsType">    <result column="news_typename" property="news_typename"/>   </association></resultMap>

    <select id="findnewsInfoMap" resultMap="newsInfoMap">select i.newsno,i.news_title,a.authorname,t.news_typename from news_info i,news_author a,news_type t where i.news_author=a.authorid and i.news_type=t.news_typeid   </select>

NewsInfoMapper.xml

package com.neusoft.mapper;import java.util.List;import com.neusoft.po.Author;import com.neusoft.po.NewsInfo;import com.neusoft.po.NewsType;public interface NewsInfoMapper {    /* public void insertAuthor(Author author);     public void deleteAuthor(String authorid);               public void insertNewsType(NewsType newsType);     public void deleteType(int news_typeid);               public void insertNewsInfo(NewsInfo newsInfo);     public void deleteInfo(String userno);*/     public List<NewsInfo> findnewsInfoMap();}

NewsInfoMapper.java
四、service
 
package com.neusoft.service;import java.util.List;import org.springframework.stereotype.Service;import com.neusoft.po.Author;import com.neusoft.po.NewsInfo;import com.neusoft.po.NewsType;public interface NewsInfoService {       public List<NewsInfo> allNewsInfo();}
NewsInfoService.java

@AutowiredNewsInfoMapper newsInfoMapper;@Overridepublic List<NewsInfo> allNewsInfo() {List<NewsInfo> newsInfoList=new ArrayList<NewsInfo>();newsInfoList=newsInfoMapper.findnewsInfoMap();return newsInfoList;}
NewsInfoServiceImpl.java
五、control
        @AutowiredNewsInfoService newsInfoService;@RequestMapping("searchInfo") public String searchNewsInfo(Model model){List<NewsInfo> newsList = newsInfoService.allNewsInfo();model.addAttribute("newsList", newsList);return "show"; }
  NewsInfoController.java
六、jsp
<a href="searchInfo.action">查询Info</a>    ----index.jsp(我的首页)
<c:forEach items="${newsList}" var="newsList">     ${newsList.newsno}<br>     ${newsList.news_title}<br>   </c:forEach>
-------show.jsp（利用EL表达式，forEach循环 表示查询的结果。注意不要忘记在jsp头部写下taglib引用c标签）
七、总结
多表查询主要是考究表与表之间的关系，这里我用的是三表查询；三表分别为：Author、NewsInfo、NewsType   
Author  :   NewsInfo  =  1  :  1
NewsType : NewsInfo = 1 : N
记住在po层里的NewsType声明一对多的关系时，别忘记在NewsInfo里声明多对一的关系
//一对多的关系（NewsType里写的）    private List<NewsInfo> infoList;

//NewsInfo里写的
    private NewsType type;    private Author author;