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>    

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