利用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





@Autowired
	NewsInfoMapper newsInfoMapper;
	@Override
	public List<NewsInfo> allNewsInfo() {
		List<NewsInfo> newsInfoList=new ArrayList<NewsInfo>();
		newsInfoList=newsInfoMapper.findnewsInfoMap();
		return newsInfoList;
	}
NewsInfoServiceImpl.java
五、control
        @Autowired
	NewsInfoService 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;