05-mybatis中表关系映射(V1.2)

软件小生活

发布于 2021-10-20 15:36:10

2270

发布于 2021-10-20 15:36:10

文章被收录于专栏：软件小生活

Mybatis-表关联关系处理

0.表关联关系回顾

一对一关系

一对多关系

多对多

一个人可以拥有多个角色

一个角色也可以有多个用户

1. 商品订单数据模型

1.1.一对一查询

案例：查询所有订单信息，关联查询下单用户信息。

注意：因为一个订单信息只会是一个人下的订单，所以从查询订单信息出发关联查询用户信息为一对一查询。

1.1.1.方式1(自动映射)

1.1.1.1.sql语句分析

使用resultType，定义订单信息po类，此po类中包括了订单信息和用户信息：

SELECT * from `order` a,user b WHERE a.user_id = b.id;

SELECT a.*,b.id uid,b.username,b.birthday,b.sex,b.address from `order` a,`user` b WHERE a.user_id = b.id;

1.1.1.2.项目结构创建

1.1.1.3.代码编写

实体类

public class CustomerOrder extends Orders { private int uid; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; }}

CustomerOrderMapper.xml

xml version="1.0" encoding="UTF-8"?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> << span="">mapper namespace="com.shop.mapper.CustomerOrderMapper"> << span="">select id="selectOrderAndUserList" resultType="com.shop.domain.CustomerOrder"> SELECT a.*,b.id as uid,b.username,b.birthday,b.sex,b.address from `order` a,`user` bwhere a.user_id = b.id; select> mapper>

编写接口

package com.shop.mapper;import com.shop.domain.CustomerOrder;import java.util.List;public interface CustomerOrderMapper { ListselectOrderAndUserList();}

测试代码

@Testpublic void test3() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerOrderMapper customerOrderMapper = sqlSession.getMapper(CustomerOrderMapper.class); Listlist = customerOrderMapper.selectOrderAndUserList(); for (CustomerOrder customerOrder : list) { System.out.println(customerOrder); }}

观察打印结果

思考：如果使用user对象呢?

分析: 上面使用自动映射方式存在什么问题?

1.需要单独建立实体类、mapper接口、mapper.xml文件

2.实体的字段名和数据表的列名不一致，只能自己手动修改表或者实体字段名称

3.实体类中不能使用对象作为属性(User)

1.1.2.方式2(手动映射)

1.1.2.1.修改orderMapper.xml

xml version="1.0" encoding="UTF-8"?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> << span="">mapper namespace="com.shop.mapper.OrderMapper"> << span="">resultMap id="aaa" type="com.shop.domain.Orders"> << span="">id property="id" column="id">id> << span="">result property="userId" column="user_id">result> << span="">result property="number" column="number">result> << span="">result property="createtime" column="createtime">result> << span="">association property="user" javaType="com.shop.domain.User"> << span="">id property="id" column="uid">id> << span="">result property="username" column="username">result> << span="">result property="sex" column="sex">result> << span="">result property="birthday" column="birthday">result> << span="">result property="address" column="address">result> association> resultMap> << span="">select id="selectOrderAndUserList" resultMap="aaa">SELECT a.*,b.id as uid,b.username,b.birthday,b.sex,b.address from `order` a,`user` bwhere a.user_id = b.id; select> mapper>

1.2.2.修改order实体类

Order:

public class Orders { private Integer id; private Integer userId; private String number; private Date createtime; private String note; // 一个订单只有有一个用户 private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getNumber() { return number; } public void setNumber(String number) { this.number = number == null ? null : number.trim(); } public Date getCreatetime() { return createtime; } public void setCreatetime(Date createtime) { this.createtime = createtime; } public String getNote() { return note; } public void setNote(String note) { this.note = note == null ? null : note.trim(); } }

1.2.3.编写测试类

@Testpublic void test2() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); Listlist = orderMapper.selectOrderAndUserList(); for (Orders orders : list) { System.out.println(orders); }}

1.2.4.观察结果

1.2.一对多关系

1.2.1.分析

注意:

如果从用户信息出发查询用户下的订单信息则为一对多查询

因为一个用户可以下多个订单。

1.2.2.sql语句分析

从用户角度看订单 :一对多关系

SELECT * from `user` a ,`order` b WHERE a.id = b.user_id;

SELECT a.* ,b.id oid,b.user_id,b.number,b.createtime from `user`a,`order` b WHERE a.id = b.user_id;

1.2.3.实体类处理

public class User { private int id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 Listorders = new ArrayList(); public ListgetOrders() { return orders; } public void setOrders(Listorders) { this.orders = orders; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address + "]"; } }

1.2.4.xml配置文件处理

xml version="1.0" encoding="UTF-8" ?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><< span="">mapper namespace="com.shop.mapper.UserMapper"> << span="">resultMap id="aaa" type="com.shop.domain.User"> << span="">id property="id" column="id">id> << span="">result property="username" column="username">result> << span="">result property="sex" column="sex">result> << span="">result property="birthday" column="birthday">result> << span="">result property="address" column="address">result> << span="">collection property="orders" ofType="com.shop.domain.Orders"> << span="">id property="id" column="oid">id> << span="">result property="userId" column="user_id">result> << span="">result property="number" column="number">result> << span="">result property="createtime" column="createtime">result> << span="">result property="note" column="note">result> collection> resultMap><< span="">select id="selectUserOrderList" resultMap="aaa"> SELECT a.*,b.id oid,b.user_id,b.number,b.note from user a,`order` b where a.id = b.user_id;select>mapper>

1.2.5.编写测试代码

@Testpublic void test3() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); Listlist = userMapper.selectUserOrderList(); for (User user : list) { System.out.println(user); }}

1.2.6.观察打印

1.3.mybatis-config.xml

2.用户和角色表模型

2.1.用户和角色关系分析

一个人可以拥有多个角色

一个角色也可以有多个用户

2.2.多对多关系表建立

2.3.多对多表的sql语句分析

2.3.1.一个用户对应多个角色

SELECT u.*,r.id as rid,r.role_name,r.role_desc from user u left OUTER join user_role ur on u.id = ur.uidleft OUTER join role r on r.id = ur.rid

2.3.2.一个角色对应多个用户

角色表和用户角色表关联查询

SELECT * from role r left JOIN user_role ur on r.id = ur.rid;

角色表和用户角色表以及用户表关联查询

SELECT * from role r left join user_role ur on r.id = ur.rid left JOIN `user` u on u.id = ur.UID;

去掉中间表的数据以及处理 role 表的id 别名问题

select u.*,r.id as rid,r.role_name,r.role_desc from role r left join user_role ur on r.ID = ur.rid left join user u on u.id = ur.uid;

2.4.mybatis配置多对多关系

2.4.1.一个用户对应多个角色

2.4.1.1.编写实体类

基本格式

public class User { private Integer id; private String username; private Date birthday; private char sex; private String address; 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 char getSex() { return sex; } public void setSex(char sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; }}

引用关系设置

public class User { private Integer id; private String username; private Date birthday; private char sex; private String address; Listroles =new ArrayList(); public ListgetRoles() { return roles; } public void setRoles(Listroles) { this.roles = roles; } 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; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", sex=" + sex + ", address='" + address + '\'' + ", roles=" + roles + '}'; } public char getSex() { return sex; } public void setSex(char sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; }}

2.4.1.2.处理mapper.xml文件

xml version="1.0" encoding="UTF-8" ?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><< span="">mapper namespace="com.shop.mapper.UserMapper"> << span="">resultMap id="aaa" type="com.shop.domain.User"> << span="">id property="id" column="id">id> << span="">result property="username" column="username">result> << span="">result property="sex" column="sex">result> << span="">result property="birthday" column="birthday">result> << span="">result property="address" column="address">result> << span="">collection property="roles" ofType="com.shop.domain.Role"> << span="">id property="id" column="rid">id> << span="">result property="role_name" column="role_name">result> << span="">result property="role_desc" column="role_desc">result> collection> resultMap><< span="">select id="selectUserRoleList" resultMap="aaa"> SELECT u.*,r.id as rid,r.role_name,r.role_desc from user u left OUTER join user_role ur on u.id = ur.uidleft OUTER join role r on r.id = ur.ridselect>mapper>

2.4.1.3.编写测试类

@Testpublic void test1() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); Listlist = userMapper.selectUserRoleList(); for (User user : list) { System.out.println(user); System.out.println("------------"); System.out.println(user.getRoles()); }}

2.4.1.4.观察打印结果

2.4.2.一个角色对应多个用户

2.4.2.1.编写实体类

package com.shop.domain;import java.util.ArrayList;import java.util.List;public class Role { private Integer id; private String role_name; private String role_desc; private Listusers = new ArrayList(); public ListgetUsers() { return users; } public void setUsers(Listusers) { this.users = users; } @Override public String toString() { return "Role{" + "id=" + id + ", role_name='" + role_name + '\'' + ", role_desc='" + role_desc + '\'' + ", users=" + users + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getRole_name() { return role_name; } public void setRole_name(String role_name) { this.role_name = role_name; } public String getRole_desc() { return role_desc; } public void setRole_desc(String role_desc) { this.role_desc = role_desc; }}

2.4.2.2.处理mapper.xml文件

xml version="1.0" encoding="UTF-8" ?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><< span="">mapper namespace="com.shop.mapper.RoleMapper"> << span="">resultMap id="aaa" type="com.shop.domain.Role"> << span="">id property="id" column="rid">id> << span="">result property="role_name" column="role_name">result> << span="">result property="role_desc" column="role_desc">result> << span="">collection property="users" ofType="com.shop.domain.User"> << span="">id property="id" column="id">id> << span="">result property="username" column="username">result> << span="">result property="birthday" column="birthday">result> << span="">result property="sex" column="sex">result> << span="">result property="address" column="address">result> collection> resultMap><< span="">select id="selectRoleUserList" resultMap="aaa">select u.*,r.id as rid,r.role_name,r.role_desc from role r left join user_role ur on r.ID = ur.rid left join user u on u.id = ur.uid;select>mapper>

2.4.2.3.编写测试类

@Testpublic void test2() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); Listlist = roleMapper.selectRoleUserList(); for (Role role : list) { System.out.println(role); }}