mybatis的一对多和一对一的关系

来源：互联网发布：淘宝一定要交保证金吗编辑：程序博客网时间：2024/05/02 08:54

user表

user_role表

PS:user表中的id对应user_role表中的u_id

user类

public class User {       private Integer id;    private String username;    private String password;    private Integer isDelete;    private Date createDate;        private List<UserRole> list;//一对多的配置        private UserRole userRole;//一对一的配置    。。。。。。setget方法}

mapper接口

User selectByPrimaryKey(String username);    User selectByKey(String username);    User selectPrimaryKey(String username);

usermapper.xml

<resultMap id="BaseResultMap" type="com.cn.model.User" >    <id column="id" property="id" jdbcType="INTEGER" />    <result column="username" property="username" jdbcType="VARCHAR" />    <result column="password" property="password" jdbcType="VARCHAR" />    <result column="isDelete" property="isDelete" jdbcType="INTEGER" />    <result column="createDate" property="createDate" jdbcType="DATE" />    <collection property="list" ofType="UserRole">    <id column="ruid" property="ruid" jdbcType="INTEGER" />    <result column="u_id" property="uid" jdbcType="INTEGER" />    <result column="rr_id" property="rrid" jdbcType="INTEGER" />    </collection>  </resultMap>  <resultMap id="BaseMap" type="com.cn.model.User" >    <id column="id" property="id" jdbcType="INTEGER" />    <result column="username" property="username" jdbcType="VARCHAR" />    <result column="password" property="password" jdbcType="VARCHAR" />    <result column="isDelete" property="isDelete" jdbcType="INTEGER" />    <result column="createDate" property="createDate" jdbcType="DATE" />    <association property="userRole" javaType="UserRole">    <id column="ruid" property="ruid" jdbcType="INTEGER" />    <result column="u_id" property="uid" jdbcType="INTEGER" />    <result column="rr_id" property="rrid" jdbcType="INTEGER" />    </association>  </resultMap>  <resultMap id="BaseResult" type="com.cn.model.User" >    <id column="id" property="id" jdbcType="INTEGER" />    <result column="username" property="username" jdbcType="VARCHAR" />    <result column="password" property="password" jdbcType="VARCHAR" />    <result column="isDelete" property="isDelete" jdbcType="INTEGER" />    <result column="createDate" property="createDate" jdbcType="DATE" />    <association property="userRole" select="selectRole" column="id">//<span style="color:#ff0000;">此处的id为第一个sql语句查询出来的字段，是要传入下面一个sql的参数，注意类型一定要和下一个sql的入参类型一致</span>    </association>  </resultMap>

一、一对一

1、嵌套结果

使用嵌套结果映射来处理重复的联合结果的子集，封装联表查询的数据（去除重复的数据）

<select id="selectByKey" resultMap="BaseMap" parameterType="java.lang.String" >    select     *    from user u,user_role ur    where u.id = ur.u_id and username = #{username,jdbcType=VARCHAR}</select>

2、嵌套查询

通过执行另外一个SQL映射语句来返回预期的复杂类型

<select id="selectPrimaryKey" resultMap="BaseResult" parameterType="java.lang.String" >    select     *    from user     where username = #{username,jdbcType=VARCHAR}</select><select id="selectRole" resultType="UserRole" parameterType="java.lang.Integer" >    select     ruid ruid,u_id uid,rr_id rrid    from user_role     where u_id = #{id,jdbcType=INTEGER}</select>

PS:如果用resultType的话，必须取别名

二、一对多

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >    select     *    from user u,user_role ur    where u.id = ur.u_id and username = #{username,jdbcType=VARCHAR}</select>

controller类

/** * 一对多 * @return */@RequestMapping("/index")public ModelAndView index(){ModelAndView mv = new ModelAndView();User user = userService.selectByPrimaryKey("jack");System.out.println(user.getPassword());List<UserRole> list = user.getList();System.out.println(list.size());return mv;}/** * 一对一（嵌套结果） * @return */@RequestMapping("/index2")public ModelAndView index2(){ModelAndView mv = new ModelAndView();User user = userService.selectByKey("john");System.out.println(user.getPassword());UserRole userRole = user.getUserRole();System.out.println(userRole.getRrid());return mv;}/** * 一对一（嵌套查询） * @return */@RequestMapping("/index3")public ModelAndView index3(){ModelAndView mv = new ModelAndView();User user = userService.selectPrimaryKey("john");System.out.println(user.getPassword());UserRole userRole = user.getUserRole();System.out.println(userRole.getRrid());return mv;}

0 0