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>    

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