mybatis查询和多表
resultMap标签
- 如果数据库返回结果的列名和要封装的实体的属性名完全一致的话用 resultType 属性
- 如果数据库返回结果的列名和要封装的实体的属性名有不一致的情况用 resultMap 属性
- 使用resultMap手动建立对象关系映射
- 代码测试:
UserMapper接口:
public interface UserMapper {
//ResultMap标签
public List<User> findAllResultMap();
}
UserMapper.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="cn.itcast.dao.UserMapper">
<!--
resultMap 手动建立映射
id="userResultMap"
type="cn.itcast.domain.User" 建立映射的java类型
id 标签 主键
column="uid" 列名
property="id" 实体属性名
result 标签 普通字段
column="name" 列名
property="username" 实体属性名
-->
<resultMap id="userResultMap" type="cn.itcast.domain.User">
<id column="uid" property="id"></id>
<result column="name" property="username"></result>
<result column="bir" property="birthday"></result>
<result column="gender" property="sex"></result>
<result column="address" property="address"></result>
</resultMap>
<!--模拟表与实体的属性不一致的情况-->
<select id="findAllResultMap" resultMap="userResultMap">
select id as uid , username as `name`, birthday as bir,sex as gender,address as address from user
</select>
</mapper>
测试代码:
package cn.itcast.test;
import cn.itcast.dao.UserMapper;
import cn.itcast.domain.User;
import cn.itcast.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.List;
public class UserMapperTest {
//模拟service
@Test
public void testfindAllResultMap() throws Exception {
//需要通过mybatis帮我们根据接口规范创建实现类
SqlSession sqlSession = MyBatisUtils.openSession();
//创建代理对象(实现类)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//执行sql
final List<User> list = userMapper.findAllResultMap();
for (User user : list) {
System.out.println(user);
}
//关闭会话
MyBatisUtils.close(sqlSession);
}
}
多条件查询(二种)
需求:根据id和username查询user表
UserMapper接口:
//多条件查询,方式一
public List<User> findByIdAndUsername1(@Param("id") Integer id, @Param("username") String username);
//多条件查询,方式二
public List<User> findByIdAndUsername2(User user);
UserMapper.XML映射配置文件:
<!--多条件查询方式一,如果传递多个参数parameterType属性省略不写-->
<select id="findByIdAndUsername1" resultType="cn.itcast.domain.User">
select * from user where id =#{id} and username=#{username}
</select>
<!--多条件查询方式二-->
<select id="findByIdAndUsername2" parameterType="cn.itcast.domain.User" resultType="cn.itcast.domain.User">
select * from user where id=#{id } and username=#{username}
</select>
测试代码:
//多条件查询
@Test
public void test01() throws Exception {
//需要通过mybatis帮我们根据接口规范创建实现类
SqlSession sqlSession = MyBatisUtils.openSession();
//创建代理对象(实现类)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//方式一
final List<User> list = userMapper.findByIdAndUsername1(41, "老王");
System.out.println(list);
//方式二
final User user = new User();
user.setId(41);
user.setUsername("老王");
final List<User> list1 = userMapper.findByIdAndUsername2(user);
System.out.println(list1);
}
模糊查询(四种)
需求:根据username模糊查询user表
UserMapper接口:
//模糊查询,方式一
public List<User> findByUsername1(String username);
//模糊查询,方式二
public List<User> findByUsername2(String username);
//模糊查询,方式三
public List<User> findByUsername3(String username);
//模糊查询,方式四
public List<User> findByUsername4(String username);
UserMapper.XML映射配置文件:
<!--模糊查询,方式一 缺点:java代码和sql语句有耦合性-->
<select id="findByUsername1" parameterType="string" resultType="User">
select * from user where username like #{username}
</select>
<!--方式二-->
<select id="findByUsername2" parameterType="string" resultType="User">
select * from user where username like "%" #{usrname} "%"
</select>
<!--方式三-->
<select id="findByUsername3" parameterType="string" resultType="User">
select * from user where username like '%${value}%'
</select>
<!--方式四 使用concat()函数拼接-->
<select id="findByUsername4" parameterType="string" resultType="User">
select * from user where username like concat(concat('%',#{username}),'%')
</select>
测试代码:
//模糊查询
@Test
public void test02() throws Exception {
//需要通过mybatis帮我们根据接口规范创建实现类
SqlSession sqlSession = MyBatisUtils.openSession();
//创建代理对象(实现类)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//方式一
final List<User> list = userMapper.findByUsername1("%王%");
System.out.println(list);
//方式二
final List<User> list1 = userMapper.findByUsername2("王");
System.out.println(list1);
//方式三
final List<User> list3 = userMapper.findByUsername3("王");
System.out.println(list3);
//方式四
final List<User> list4 = userMapper.findByUsername4("王");
System.out.println(list4);
}
${}和#{}的区别【面试题】
- ${}:底层Statement
- sql与参数拼接在一起,会出现sql注入问题
- 每次执行sql语句都会编译一次
- 接收简单数据类型,命名:${value}
- 接收引用数据类型,命名: ${属性名}
- 字符串类型需要加 ‘${value}’
- #{}:底层PreparedStatement
- sql与参数分离,不会出现sql注入问题
- sql只需要编译一次
- 接收简单数据类型,命名:#{随便写}
- 接收引用数据类型,命名:#{属性名}
mybatis映射文件深入
返回主键
应用场景:向数据库保存一个user对象后,然后在控制台记录下此新增user的主键值(id)
UserMapper接口:
package cn.itcast.dao;
import cn.itcast.domain.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserMapper {
//返回主键方式一
public void save1(User user);
//返回主键方式二
public void save2(User user);
}
UserMapper.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="cn.itcast.dao.UserMapper">
<!--
返回主键,方式一 useGeneratedKeys属性
useGeneratedKeys="true" 开启新增主键返回功能
keyColumn="id" user表中主键列
keyProperty="id" user实体主键属性
注意:仅支持主键自增类型的数据库 MySQL 和 SqlServer , oracle不支持
-->
<insert id="save1" parameterType="User" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})
</insert>
<!--
返回主键,方式二 <selectKey>
keyColumn="id" user表中主键列
keyProperty="id" user实体主键属性
resultType="int" user实体主键属性类型
order="AFTER" 表示此标签内部sql语句在insert执行之前(执行),还是之后执行(执行)
AFTER 之后执行【在自增主键时】
BEFORE 之前执行【使用指定主键时】
-->
<insert id="save2" parameterType="User">
<selectKey keyColumn="id" keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address})
</insert>
</mapper>
测试代码:
package cn.itcast.test;
import cn.itcast.dao.UserMapper;
import cn.itcast.domain.User;
import cn.itcast.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.Date;
import java.util.List;
public class UserMapperTest {
private SqlSession sqlSession = null;
//此方法在测试方法执行之前,执行
@Before
public void before() {
//需要通过mybatis帮我们根据接口规范创建实现类
sqlSession = MyBatisUtils.openSession();
}
//此方法在测试方法执行后,执行
@After
public void after() {
//关闭会话
MyBatisUtils.close(sqlSession);
}
//返回主键
@Test
public void test01() throws Exception {
//创建代理对象(实现类)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
final User user = new User();
user.setUsername("小六");
user.setBirthday(new Date());
user.setAddress("中国");
user.setSex("男");
//方式一
//userMapper.save1(user);
//System.out.println("新增时,主键返回" + user.getId());
//方式二
userMapper.save2(user);
System.out.println("新增时,主键返回" + user.getId());
}
}
动态SQL
什么是动态SQL
需求:把id和username封装到user对象中,将user对象中不为空的属性作为查询条件
这个时候我们执行的sql就有多种可能
-- 如果id和用户名不为空
select * from user where id= #{id} and username = #{username}
-- 如果只有id
select * from user where id= #{id}
-- 如果只有用户名
select * from user where username = #{username}
-- 如果id和用户名都为空
select * from user
像上面这样, 根据传入的参数不同, 需要执行的SQL的结构就会不同,这就是动态SQL
if条件判断
需求:把id和username封装到user对象中,将user对象中不为空的属性作为查询条件
UserMapper接口:
//if条件判断
public List<User> findByIdAndUsernaemIf(User user);
UserMapper.xml映射配置文件:
<!--
if标签 条件判断
where标签 相当于 where 1=1 功能,如果没有条件情况下 where语句不在sql语句拼接
可以去掉第一个 and 或者 or
-->
<select id="findByIdAndUsernaemIf" parameterType="User" resultType="User">
select * from user
<where>
<if test="id !=null">
and id =#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
</where>
</select>
测试代码:
@Test
public void test02() throws Exception {
//创建代理对象(实现类)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//拼接条件
final User param = new User();
param.setId(41);
param.setUsername("老王");
final List<User> list = userMapper.findByIdAndUsernaemIf(param);
for (User user : list) {
System.out.println(user);
}
}
set用于update语句
需求:动态更新user表数据,如果该属性有值就更新,没有值不做处理
UserMapper接口:
//set更新
public void updateIf(User user);
UserMapper.xml:
<!--
set标签 更新 ,将条件中的最后一个逗号抹除
-->
<update id="updateIf" parameterType="User">
update user
<set>
<if test="username!=null">
username=#{username},
</if>
<if test="birthday!=null">
birthday=#{birthday},
</if>
<if test="sex!=null">
sex=#{sex},
</if>
<if test="address!=null">
address=#{address},
</if>
</set>
where id =#{id}
</update>
测试代码:
@Test
public void test03() throws Exception {
//创建代理对象(实现类)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//设置更新内容
final User user = new User();
user.setId(51);
user.setUsername("小四");
userMapper.updateIf(user);
}
foreach用于循环遍历【重点】
需求:根据多个id查询,user对象的集合
select * from user where id in (41,43,46);
* <foreach>标签用于遍历集合,它的属性:
• collection:代表要遍历的集合元素
• open:代表语句的开始部分
• close:代表结束部分
• item:代表遍历集合的每个元素,生成的变量名
• sperator:代表分隔符
UserMapper接口:
//foreach标签,普通list集合
public List<User> findByList(List<Integer> ids);
//foreach标签,普通array数组
public List<User> findByArray(Integer[] ids);
//foreach标签,实体属性list集合
public List<User> findByQueryVo(QueryVo queryVo);
UserMapper.xml映射配置文件:
<!--
foreach标签,普通list集合
传递 普通类型list集合 collection="list" 属性取值:collection、list
-->
<select id="findByList" parameterType="list" resultType="User">
select * from user where id in
<foreach collection="list" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
<!--
foreach标签,普通array数组
传统 普通类型array数组 collection="array" 属性取值 array
-->
<select id="findByArray" parameterType="int" resultType="User">
select * from user where id in
<foreach collection="array" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
<!--
foreach标签,实体属性list集合
传递 实体中list属性集合的话,collection="ids" 取值,实体的属性名
-->
<select id="findByQueryVo" parameterType="QueryVo" resultType="User">
select * from user where id in
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
测试代码:
//foreach标签
@Test
public void test04() throws Exception {
//创建代理对象(实现类)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//普通list集合
//List ids = new ArrayList();
//ids.add(41);
//ids.add(46);
//final List list = userMapper.findByList(ids);
//System.out.println(list);
//普通array数组
//Integer[] ids={41,46,49};
//final List<User> list = userMapper.findByArray(ids);
//System.out.println(list);
//实体属性list集合
List ids = new ArrayList();
ids.add(41);
ids.add(46);
final QueryVo queryVo = new QueryVo();
queryVo.setIds(ids);
final List<User> list = userMapper.findByQueryVo(queryVo);
System.out.println(list);
}
QueryVo实体类:
package cn.itcast.domain;
import java.util.List;
//根据页面查询条件封装到实体中 View Object
public class QueryVo {
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
public List<Integer> ids;
}
SQL片段
映射文件中可将重复的 sql 提取出来,使用时用 include 引用即可,最终达到 sql 重用的目的
<!--
foreach标签,普通list集合
传递 普通类型list集合 collection="list" 属性取值:collection、list
-->
<select id="findByList" parameterType="list" resultType="User">
<include refid="selectUser"></include> where id in
<foreach collection="list" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
<!--
foreach标签,普通array数组
传统 普通类型array数组 collection="array" 属性取值 array
-->
<select id="findByArray" parameterType="int" resultType="User">
<include refid="selectUser"></include> where id in
<foreach collection="array" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
<!--
foreach标签,实体属性list集合
传递 实体中list属性集合的话,collection="ids" 取值,实体的属性名
-->
<select id="findByQueryVo" parameterType="QueryVo" resultType="User">
<include refid="selectUser"></include> where id in
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
<!--
将当前映射文件的共同的sql代码抽取一个片段,实现sql的复用性...
id="selectUser" 当前sql片段的唯一标识
-->
<sql id="selectUser">
select id,username,birthday,sex,address from user
</sql>
表关系回顾
-
特殊情况:
一个订单只能从属于一个用户,mybatis框架就把这个多对一看做成一对一来实现 -
数据建立表关系:通过主外键关联
-
实体建立关系:通过属性关联
mybatis多表查询
一对一(多对一)
一对一查询模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
Order实体类:
package cn.itcast.domain;
import java.util.Date;
public class Order {
private Integer id;
private Date ordertime;
private Double money;
//一个订单从属于一个用户
private User user;
public Order() {
}
public Order(Integer id, Date ordertime, Double money, User user) {
this.id = id;
this.ordertime = ordertime;
this.money = money;
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordertime=" + ordertime +
", money=" + money +
", user=" + user +
'}';
}
}
OrderMapper接口:
public interface OrderMapper {
//一对一关联查询
public Order findByIdWithUser(Integer id);
}
OrderMapper.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="cn.itcast.dao.OrderMapper">
<resultMap id="orderMap" type="cn.itcast.domain.Order">
<id column="id" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="money" property="money"></result>
<!--一对多关联 association标签
property="user" 关联实体的属性名
javaType="cn.itcast.domain.User" 关联实体java类型
-->
<association property="user" javaType="cn.itcast.domain.User">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<!--一对一关联查询
resultType:单表映射封装
resultMap:多表查询必须手动映射封装-->
<select id="findByIdWithUser" parameterType="int" resultMap="orderMap">
SELECT * FROM orders o INNER JOIN `user` u ON o.`uid` = u.`id` WHERE o.`id` = #{id}
</select>
</mapper>
测试代码:
package cn.itcast.test;
import cn.itcast.dao.OrderMapper;
import cn.itcast.domain.Order;
import cn.itcast.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
public class OrderMapperTest {
private SqlSession sqlSession = null;
// 此方法在测试方法执行之前,执行
@Before
public void before() {
// 获取sqlSession对象
sqlSession = MyBatisUtils.openSession();// 此方法必须线程内独享....
}
// 此方法在测试地方法执行之后,执行
@After
public void after() {
// 关闭sqlSession
MyBatisUtils.close(sqlSession);
}
//一对一关联测试
@Test
public void test01() throws Exception {
//获取代理对象
final OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
//根据id查询
final Order order = orderMapper.findByIdWithUser(1);
System.out.println(order);
}
}
一对多
一对多查询模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单
User实体类:
package cn.itcast.domain;
import java.util.Date;
import java.util.List;
//实体类
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
//一个用户具有多个订单
private List<Order> orderList;
public User() {
}
public User(Integer id, String username, Date birthday, String sex, String address, List<Order> orderList) {
this.id = id;
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
this.orderList = orderList;
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", orderList=" + orderList +
'}';
}
}
UserMapper接口:
package cn.itcast.dao;
import cn.itcast.domain.Order;
public interface OrderMapper {
//一对一关联查询
public Order findByIdWithUser(Integer id);
}
UserMapper.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="cn.itcast.dao.UserMapper">
<resultMap id="userMap" type="cn.itcast.domain.User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<!--一对多关联 collection标签
property="orderLlist" 关联实体的属性名
ofType="cn.itcast.domain.Order" 关联实体java类型(集合泛型的类型)
-->
<collection property="orderList" ofType="cn.itcast.domain.Order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<!--一对多查询关联-->
<select id="findByIdWithOrders" parameterType="int" resultMap="userMap">
SELECT *,o.id AS oid FROM `user` u INNER JOIN orders o ON u.`id` = o.`uid` WHERE u.`id`=#{id}
</select>
</mapper>
测试代码:
package cn.itcast.test;
import cn.itcast.dao.UserMapper;
import cn.itcast.domain.User;
import org.junit.Test;
public class UserMapperTest extends BaseMapperTest {
// 一对多测试
@Test
public void test01() throws Exception {
// 获取代理
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findByIdWithOrders(41);
System.out.println(user);
}
}
多对多(由两个一对多组成)
多对多查询的模型
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用
多对多查询的需求:查询用户同时查询出该用户的所有角色
在mybatis中多对多实现,跟一对多步骤是一样,区别就在于sql语句
User和Role实体:
package cn.itcast.domain;
import java.util.Date;
import java.util.List;
//实体类
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
//一个用户可以具有多个角色
private List<Role> roleList;
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
//一个用户具有多个订单
private List<Order> orderList;
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public User() {
}
public User(Integer id, String username, Date birthday, String sex, String address, List<Role> roleList, List<Order> orderList) {
this.id = id;
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
this.roleList = roleList;
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", roleList=" + roleList +
", orderList=" + orderList +
'}';
}
}
package cn.itcast.domain;
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
public Role() {
}
public Role(Integer id, String roleName, String roleDesc) {
this.id = id;
this.roleName = roleName;
this.roleDesc = roleDesc;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
UserMapper接口:
public interface UserMapper {
//一对多关联
public User findByIdWithOrders(Integer id);
//多对多关联
public User findByIdWithRoles(Integer id);
}
UserMapper.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="cn.itcast.dao.UserMapper">
<resultMap id="userMap" type="cn.itcast.domain.User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<!--一对多关联 collection标签
property="orderLlist" 关联实体的属性名
ofType="cn.itcast.domain.Order" 关联实体java类型(集合泛型的类型)
-->
<collection property="orderList" ofType="cn.itcast.domain.Order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<!--一对多查询关联-->
<select id="findByIdWithOrders" parameterType="int" resultMap="userMap">
SELECT *,o.id AS oid FROM `user` u INNER JOIN orders o ON u.`id` = o.`uid` WHERE u.`id`=#{id}
</select>
<!--多对多-->
<resultMap id="userWithRoleMap" type="cn.itcast.domain.User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<!--
多对多实现步骤和一对多一样(区别在于sql语句的不同)
-->
<collection property="roleList" ofType="cn.itcast.domain.Role">
<id column="rid" property="id"></id>
<result column="role_name" property="roleName"></result>
<result column="role_desc" property="roleDesc"></result>
</collection>
</resultMap>
<!--多对对的关联-->
<select id="findByIdWithRoles" parameterType="int" resultMap="userWithRoleMap">
select * from `user` u
inner join user_role ur onu.`id`=ur.`uid` -- 用户连接中间表
inner join role r on ur.`rid`=r.`id` -- 根据中间表连接角色
where u.id=#{id} -- 用户id作为条件
</select>
</mapper>
测试代码:
//多对多测试(根据用户查询角色)
@Test
public void test02() throws Exception {
//获取代理
final UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findByIdWithOrders(41);
System.out.println(user);
}
}