mybatis“集合嵌套查询”和“集合嵌套结果”两种方法实现数据库

两个实体类分别如下：User用户类和Goods商品类。一个用户对应多个商品（一对多） package com.leo.entity;import java.util.List;public class User {private Integer id;private String username;private Integer age;private String address;private ListG

两个实体类分别如下：User用户类和Goods商品类。一个用户对应多个商品（一对多）

package com.leo.entity;import java.util.List;public class User {	private Integer id;	private String username;	private Integer age;	private String address;	private List goodsList;	public List getGoodsList() {		return goodsList;	}	public void setGoodsList(List goodsList) {		this.goodsList = goodsList;	}	public Integer getId() {		return id;	}	public void setId(Integer id) {		this.id = id;	}	public String getUsername() {		return username;	}	public void setUsername(String username) {		this.username = username;	}	public Integer getAge() {		return age;	}	public void setAge(Integer age) {		this.age = age;	}	public String getAddress() {		return address;	}	public void setAddress(String address) {		this.address = address;	}	public User() {		super();		// TODO Auto-generated constructor stub	}	@Override	public String toString() {		return "User [id=" + id + ", username=" + username + ", age=" + age				+ ", address=" + address + ", goodsList=" + goodsList + "]";	}					}

Goods商品类

package com.leo.entity;public class Goods {	private Integer id;	private String goodsName;	private Integer goodsNumber;	private Integer user_id;				public Integer getId() {		return id;	}	public void setId(Integer id) {		this.id = id;	}	public String getGoodsName() {		return goodsName;	}	public void setGoodsName(String goodsName) {		this.goodsName = goodsName;	}	public Integer getGoodsNumber() {		return goodsNumber;	}	public void setGoodsNumber(Integer goodsNumber) {		this.goodsNumber = goodsNumber;	}	public Integer getUser_id() {		return user_id;	}	public void setUser_id(Integer user_id) {		this.user_id = user_id;	}				}

User实体类的mapper映射文件:UserDao.xml

<?xml version="1.0" encoding="UTF-8" ?>      																				<!---ecms -ecms 当表之间的关系是一对多时，用 collection-->			<!---ecms -ecms  这里的 column="u_id"是为了传参数到嵌套的查询select="....."-->							<!---ecms -ecms goodsList是User实体类中的 私有属性集合 -->																	    select 				u.id as u_id,				u.username,				u.age, 				u.address 			from				user u			 where 				u.id =${value};			 

Goods实体类的mapper映射文件:GoodsDao.xml

<?xml version="1.0" encoding="UTF-8" ?>   	<!---ecms -ecms  这就是那个嵌套的查询映射 -->   					  SELECT id,goodsName,goodsNumber,user_id FROM Goods WHERE user_id = #{value}			  

mabatis的环境配置文件mabatis-config.xml

<?xml version="1.0" encodin<b>本文来源gao@!dai!ma.com搞$$代^@码!网</b>g="UTF-8"?>        	<!---ecms -ecms  我把数据源的内容放在db.properties文件中 -->			<!---ecms -ecms start-类型别名 ：为mapper.xml中resultType取一个别名，看着不会很冗余-->		  	  		<!---ecms -ecms  end- 类型别名-->		<!---ecms -ecms  start- environments配置 -->                                                            <!---ecms -ecms  数据源配置 -->                                                                                       <!---ecms -ecms  end- environments配置 -->           <!---ecms -ecms  连接到实体类的映射文件资源-->                              

测试的servlet(也可以用main函数测试)

package com.leo.servlet;import java.io.IOException;import java.io.InputStream;import java.util.List;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import com.leo.entity.Goods;import com.leo.entity.User;import com.leo.mapper.GoodsDao;import com.leo.mapper.UserDao;/** * Servlet implementation class MybatisServlet */@WebServlet("/MybatisServlet")public class MybatisServlet extends HttpServlet {	private static final long serialVersionUID = 1L;	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {		InputStream is = Resources.getResourceAsStream("com/leo/resources/mybatis-config.xml");		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);		SqlSession session = factory.openSession();		//		UserDao ud = session.getMapper(UserDao.class);		GoodsDao gd = session.getMapper(GoodsDao.class);		List goodsList= gd.selectGoodsForUser(1);		//		User user = ud.getUserinfoById(1);		//		System.out.println(user);//		List goodsList  =  user.getGoodsList();		for (Goods goods : goodsList) {			System.out.println(goods.getId()+"   "+ goods.getGoodsName()+"   "+goods.getGoodsNumber()+ "  "+ goods.getUser_id());		}		session.commit();		session.close();			}		protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {		doGet(request, response);			}}

以上是集合嵌套查询，还有一种方式是集合嵌套结果，这种方式只需要一个实体类文件即可，它是一种级联查询，自动完成的

下面用集合嵌套结果这种方式：

只需要改动UserDao.xml,且只是用这一个映射文件就可以完成

<?xml version="1.0" encoding="UTF-8" ?>      																																								<!---ecms -ecms 两种方式的不同之处在这里，自己分析就可以知道-->						    select 				u.id as u_id,				u.username,				u.age,				u.address,				g.id as g_id,   <!---ecms -ecms 嵌套结果这种方式是使用了一次连接查询，而嵌套查询使用了两次 -->				g.goodsName,				g.goodsNumber,				g.user_id			 from				user u				inner join goods g on u.id = g.user_id			 where 				u.id =${value};				  

希望可以帮到大家，有什么措辞不正确，希望得到指正，希望进步

喜欢 (0)赏

[搞代码]

分享 (0)