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>
P
S:如果用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;
}