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

112 阅读 0 评论 74 点赞

我是靠谱客的博主轻松铅笔，最近开发中收集的这篇文章主要介绍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 List<Goods> goodsList;

	public List<Goods> getGoodsList() {
		return goodsList;
	}
	public void setGoodsList(List<Goods> 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" ?>   
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">   
	<mapper namespace="com.leo.mapper.UserDao">		
		<resultMap type="User"  id="userMap">
			<id column="u_id" property="id" />
			<result column="username" property="username" />
			<result column="age" property="age" />
			<result column="address" property="address" />
			<!--当表之间的关系是一对多时，用 collection-->			<!-- 这里的 column="u_id"是为了传参数到嵌套的查询select="....."-->
			<collection property="goodsList" ofType="Goods" column="u_id" select="com.leo.mapper.GoodsDao.selectGoodsForUser" />
		</resultMap>		<!--goodsList是User实体类中的 私有属性集合 -->													
		<select id="getUserinfoById" parameterType="int"  resultMap="userMap">
		    select 
				u.id as u_id,
				u.username,
				u.age, 
				u.address 
			from
				user u
			 where 
				u.id =${value};
		</select>
	</mapper>

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

<?xml version="1.0" encoding="UTF-8" ?>   
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
	<!-- 这就是那个嵌套的查询映射 -->   
	<mapper namespace="com.leo.mapper.GoodsDao">
		<select id="selectGoodsForUser" parameterType="int" resultType="Goods">
		  SELECT id,goodsName,goodsNumber,user_id FROM Goods WHERE user_id = #{value}
		</select>
	</mapper>

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

<?xml version="1.0" encoding="UTF-8"?>   
<!DOCTYPE configuration  
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
  "http://mybatis.org/dtd/mybatis-3-config.dtd">     
<configuration>
	<!-- 我把数据源的内容放在db.properties文件中 -->
	<properties resource="com/leo/resources/db.properties" />
	
	<!--start-类型别名 ：为mapper.xml中resultType取一个别名，看着不会很冗余-->
	<typeAliases>
	  <typeAlias alias="User" type="com.leo.entity.User"/>
	  <typeAlias alias="Goods" type="com.leo.entity.Goods"/>
	</typeAliases>
	<!-- end- 类型别名-->
	
	<!-- start- environments配置 -->
    <environments default="development">   
       <environment id="development">   
           <transactionManager type="JDBC"/>   
           <dataSource type="POOLED">   
               <property name="driver" value="${driverClass}"/><!-- 数据源配置 --> 
               <property name="url" value="${url}"/>   
               <property name="username" value="${username}"/>   
               <property name="password" value="${password}"/>   
           </dataSource>   
       </environment>   
    </environments>
    <!-- end- environments配置 -->   
    
    <!-- 连接到实体类的映射文件资源-->
    <mappers>   
        <mapper resource="com/leo/entity/UserDao.xml" />
        <mapper resource="com/leo/entity/GoodsDao.xml" />
    </mappers>   
</configuration>

测试的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<Goods> goodsList= gd.selectGoodsForUser(1);
		
//		User user = ud.getUserinfoById(1);		
//		System.out.println(user);
//		List<Goods> 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" ?>   
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">   
	<mapper namespace="com.leo.mapper.UserDao">
		
		
		<resultMap type="Goods" id="goodsMap">
			<id column="g_id" property="id"/>
			<result column="goodsName" property="goodsName"/>
			<result column="goodsNumber" property="goodsNumber"/>
			<result column="user_id" property="user_id"/>
		</resultMap>
		
		<resultMap type="User"  id="userMap">
			<id column="u_id" property="id" />
			<result column="username" property="username" />
			<result column="age" property="age" />
			<result column="address" property="address" />
			<collection property="goodsList" ofType="Goods" resultMap="goodsMap" /><!--两种方式的不同之处在这里，自己分析就可以知道-->
		</resultMap>
		<select id="getUserinfoById" parameterType="int" resultMap="userMap">
		    select 
				u.id as u_id,
				u.username,
				u.age,
				u.address,
				g.id as g_id,   <!--嵌套结果这种方式是使用了一次连接查询，而嵌套查询使用了两次 -->
				g.goodsName,
				g.goodsNumber,
				g.user_id
			 from
				user u
				inner join goods g on u.id = g.user_id
			 where 
				u.id =${value};
		</select>	
	</mapper>

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