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

时间 2014-12-09 21:13:21  CSDN博客

原文  http://blog.csdn.net/liuao107329/article/details/41829767

主题 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}@Overridepublic 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 fromuser 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 fromuser uinner join goods g on u.id = g.user_id where u.id =${value};</select></mapper>

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