Mybatis（一对一、一对多、多对多）操作

* 首先列出示例中用到的数据库表

user表：

 accout表：

role表：

 user_role表：

 建表语句如下：

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` datetime default NULL COMMENT '生日',
  `sex` char(1) default NULL COMMENT '性别',
  `address` varchar(256) default NULL COMMENT '地址',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



insert  into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正');





DROP TABLE IF EXISTS `account`;

CREATE TABLE `account` (
  `ID` int(11) NOT NULL COMMENT '编号',
  `UID` int(11) default NULL COMMENT '用户编号',
  `MONEY` double default NULL COMMENT '金额',
  PRIMARY KEY  (`ID`),
  KEY `FK_Reference_8` (`UID`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



insert  into `account`(`ID`,`UID`,`MONEY`) values (1,41,1000),(2,45,1000),(3,41,2000);



DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `ID` int(11) NOT NULL COMMENT '编号',
  `ROLE_NAME` varchar(30) default NULL COMMENT '角色名称',
  `ROLE_DESC` varchar(60) default NULL COMMENT '角色描述',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



insert  into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');





DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `UID` int(11) NOT NULL COMMENT '用户编号',
  `RID` int(11) NOT NULL COMMENT '角色编号',
  PRIMARY KEY  (`UID`,`RID`),
  KEY `FK_Reference_10` (`RID`),
  CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `role` (`ID`),
  CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `user_role`(`UID`,`RID`) values (41,1),(45,1),(41,2);
上述建表语句

一、一对一操作（账户表[account]和用户表[user]之间）

java项目目录结构

前提：用户表和账户表之间存在外键关系。一个用户可以拥有多个账号，一个账号只能被一个用户拥有。

问题：如何查询所有账户并将该账户的用户信息一同获取到？

解析：一个账户只能存在一个用户，因此为一对一关系。

1、在domain包下创建User实体类和Account实体类

User实体类：

 1 package sun.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.Date;
 5 import java.util.List;
 6 
 7 public class User implements Serializable {
 8     private Integer id;
 9     private String username;
10     private Date birthday;
11     private String sex;
12     private String address;
13 
14     public Integer getId() {
15         return id;
16     }
17 
18     public void setId(Integer id) {
19         this.id = id;
20     }
21 
22     public String getUsername() {
23         return username;
24     }
25 
26     public void setUsername(String username) {
27         this.username = username;
28     }
29 
30     public Date getBirthday() {
31         return birthday;
32     }
33 
34     public void setBirthday(Date birthday) {
35         this.birthday = birthday;
36     }
37 
38     public String getSex() {
39         return sex;
40     }
41 
42     public void setSex(String sex) {
43         this.sex = sex;
44     }
45 
46     public String getAddress() {
47         return address;
48     }
49 
50     public void setAddress(String address) {
51         this.address = address;
52     }
53 
54     @Override
55     public String toString() {
56         return "User{" +
57                 "id=" + id +
58                 ", username='" + username + '\'' +
59                 ", birthday=" + birthday +
60                 ", sex='" + sex + '\'' +
61                 ", address='" + address + '\'' +
62                 '}';
63     }
64 }
User实体类

Account实体类：

 1 package sun.domain;
 2 
 3 import java.io.Serializable;
 4 
 5 /**
 6  * @Classname Account
 7  * @Description TODO
 8  * @Date 2020/9/11 12:11
 9  * @Created by Administrator
10  */
11 public class Account implements Serializable {
12     private int id;
13     private int uid;
14     private double money;
15     // 用于封装User对象
16     private User user;
17 
18     public User getUser() {
19         return user;
20     }
21 
22     public void setUser(User user) {
23         this.user = user;
24     }
25 
26     public int getId() {
27         return id;
28     }
29 
30     public void setId(int id) {
31         this.id = id;
32     }
33 
34     public int getUid() {
35         return uid;
36     }
37 
38     public void setUid(int uid) {
39         this.uid = uid;
40     }
41 
42     public double getMoney() {
43         return money;
44     }
45 
46     public void setMoney(double money) {
47         this.money = money;
48     }
49 
50     @Override
51     public String toString() {
52         return "Account{" +
53                 "id=" + id +
54                 ", uid=" + uid +
55                 ", money=" + money +
56                 '}';
57     }
58 }
Account实体类

由于查询主体是账户表而且查询结果要包含账户对应的用户，因此在Account实体类中封装User对象。

2、在dao包中创建AccountDao接口，定义查询方法

 1 package sun.dao;
 2 
 3 import sun.domain.Account;
 4 import sun.domain.AccountUser;
 5 
 6 import java.util.List;
 7 
 8 /**
 9  * @Classname AccountDao
10  * @Description TODO
11  * @Date 2020/9/11 13:13
12  * @Created by Administrator
13  */
14 public interface AccountDao {
15     /**
16      * 查询所有账户信息和该账户的所属者
17      */
18     List<Account> findAll();
19 
20 }
AccountDao接口

3、配置AccountDao.xml映射文件

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 
 6 <mapper namespace="sun.dao.AccountDao">
 7     <!--封装Account类-->
 8     <resultMap id="accountUserMap" type="account">
 9         <id property="id" column="aid"></id>
10         <result property="uid" column="uid"></result>
11         <result property="money" column="money"></result>
12         <!--封装User对象-->
13         <!--javaType指的是封装对象类型-->
14         <association property="user" column="uid" javaType="user">
15             <id property="id" column="id"></id>
16             <result property="username" column="username"></result>
17             <result property="sex" column="sex"></result>
18             <result property="address" column="address"></result>
19             <result property="birthday" column="birthday"></result>
20         </association>
21     </resultMap>
22 
23     <!--查询所有-->
24     <select id="findAll" resultMap="accountUserMap">
25         <!--SELECT * from account;-->
26         SELECT u.*,a.id as aid,a.uid,a.money from user u,account a where a.uid=u.id
27     </select>
28 
29     <select id="findAllAccount" resultType="accountuser">
30         SELECT a.*,u.username,u.address from user u,account a where a.uid=u.id
31     </select>
32 </mapper>
AccountDao.xml

4、AccountTest测试类进行查询测试

 1 package sun.test;
 2 
 3 
 4 import org.apache.ibatis.io.Resources;
 5 import org.apache.ibatis.session.SqlSession;
 6 import org.apache.ibatis.session.SqlSessionFactory;
 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 8 import org.junit.After;
 9 import org.junit.Before;
10 import org.junit.Test;
11 import org.omg.CORBA.PUBLIC_MEMBER;
12 import sun.dao.AccountDao;
13 import sun.dao.UserDao;
14 import sun.domain.Account;
15 import sun.domain.AccountUser;
16 import sun.domain.User;
17 
18 import java.io.IOException;
19 import java.io.InputStream;
20 import java.util.List;
21 
22 public class AccountTest {
23 
24     private InputStream in;
25     private SqlSession sqlSession;
26     private AccountDao accountDao;
27 
28     @Before
29     public void init() throws IOException {
30         // 读取配置文件
31         in = Resources.getResourceAsStream("SqlMapConfig.xml");
32         // 创建SqlSessionFactory
33         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
34         SqlSessionFactory factory = builder.build(in);
35         // 使用工厂生产sqlsession对象
36         sqlSession = factory.openSession();
37         // 使用sqlsession创建UserDao接口代理对象
38         accountDao = sqlSession.getMapper(AccountDao.class);
39     }
40 
41     @After
42     public void destory() throws IOException {
43         sqlSession.commit();
44         sqlSession.close();
45         in.close();
46     }
47 
48     @Test
49     public void findAllTest() {
50         // 使用代理对象执行方法
51         List<Account> all = accountDao.findAll();
52         for (Account account : all) {
53             System.out.println("----------------");
54             System.out.println(account);
55             System.out.println(account.getUser());
56         }
57     }
58     @Test
59     public void findAllAccountTest(){
60         List<AccountUser> allAcount = accountDao.findAllAccount();
61         for (AccountUser accountUser : allAcount) {
62             System.out.println(accountUser);
63         }
64     }
65 
66 }
AccountTest测试类

测试结果：

 

 

二、一对多操作（用户表[user]和账户表[account]之间）

java项目目录结构

前提：用户表和账户表之间存在外键关系。一个用户可以拥有多个账号，一个账号只能被一个用户拥有。

问题：如何查询所有用户并将该用户的所有账号信息一同获取到？

解析：一个用户可以拥有多个账号，因此为一对多关系。

1、在domain包下创建User实体类和Account实体类

User类：

 1 package sun.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.Date;
 5 import java.util.List;
 6 
 7 public class User implements Serializable {
 8     private Integer id;
 9     private String username;
10     private Date birthday;
11     private String sex;
12     private String address;
13 //    封装该实体所拥有的账户对象
14     private List<Account> accounts;
15 
16     public List<Account> getAccounts() {
17         return accounts;
18     }
19 
20     public void setAccounts(List<Account> accounts) {
21         this.accounts = accounts;
22     }
23 
24     public Integer getId() {
25         return id;
26     }
27 
28     public void setId(Integer id) {
29         this.id = id;
30     }
31 
32     public String getUsername() {
33         return username;
34     }
35 
36     public void setUsername(String username) {
37         this.username = username;
38     }
39 
40     public Date getBirthday() {
41         return birthday;
42     }
43 
44     public void setBirthday(Date birthday) {
45         this.birthday = birthday;
46     }
47 
48     public String getSex() {
49         return sex;
50     }
51 
52     public void setSex(String sex) {
53         this.sex = sex;
54     }
55 
56     public String getAddress() {
57         return address;
58     }
59 
60     public void setAddress(String address) {
61         this.address = address;
62     }
63 
64     @Override
65     public String toString() {
66         return "User{" +
67                 "id=" + id +
68                 ", username='" + username + '\'' +
69                 ", birthday=" + birthday +
70                 ", sex='" + sex + '\'' +
71                 ", address='" + address + '\'' +
72                 '}';
73     }
74 }
User实体类

Account类：

 1 package sun.domain;
 2 
 3 import java.io.Serializable;
 4 
 5 /**
 6  * @Classname Account
 7  * @Description TODO
 8  * @Date 2020/9/11 12:11
 9  * @Created by Administrator
10  */
11 public class Account implements Serializable {
12     private int id;
13     private int uid;
14     private double money;
15 
16     public int getId() {
17         return id;
18     }
19 
20     public void setId(int id) {
21         this.id = id;
22     }
23 
24     public int getUid() {
25         return uid;
26     }
27 
28     public void setUid(int uid) {
29         this.uid = uid;
30     }
31 
32     public double getMoney() {
33         return money;
34     }
35 
36     public void setMoney(double money) {
37         this.money = money;
38     }
39 
40     @Override
41     public String toString() {
42         return "Account{" +
43                 "id=" + id +
44                 ", uid=" + uid +
45                 ", money=" + money +
46                 '}';
47     }
48 }
Account实体类

由于查询主体是用户表而且查询结果要包含该用户的所有账号信息，因此在User实体类中封装List<Account>集合对象。

2、在dao包中创建UserDao接口，定义查询方法

 1 package sun.dao;
 2 
 3 import sun.domain.User;
 4 
 5 import java.util.List;
 6 
 7 public interface UserDao {
 8     /**
 9      * 查询所有用户和该用户名下的账户信息
10      * @return
11      */
12     List<User> findAll();
13 
14 }
UserDao接口

3、配置UserDao.xml映射文件

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 
 6 <mapper namespace="sun.dao.UserDao">
 7     <resultMap id="userAccountMap" type="user">
 8         <id property="id" column="id"></id>
 9         <result property="username" column="username"></result>
10         <result property="sex" column="sex"></result>
11         <result property="birthday" column="birthday"></result>
12         <result property="address" column="address"></result>
13         <!--封装用户名下的账户信息-->
14         <collection property="accounts" ofType="account">
15             <id property="id" column="aid"></id>
16             <result property="uid" column="uid"></result>
17             <result property="money" column="money"></result>
18         </collection>
19     </resultMap>
20 
21     <!--查询所有-->
22     <select id="findAll" resultMap="userAccountMap">
23         SELECT u.*,a.id as aid,a.uid,a.money FROM USER u LEFT OUTER JOIN account a ON u.id=a.uid
24     </select>
25 </mapper>
UserDao.xml

4、UserTest测试类进行查询测试

 1 package sun.test;
 2 
 3 
 4 import org.apache.ibatis.io.Resources;
 5 import org.apache.ibatis.session.SqlSession;
 6 import org.apache.ibatis.session.SqlSessionFactory;
 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 8 import org.junit.After;
 9 import org.junit.Before;
10 import org.junit.Test;
11 import sun.dao.UserDao;
12 import sun.domain.User;
13 
14 import java.io.IOException;
15 import java.io.InputStream;
16 import java.util.Date;
17 import java.util.List;
18 
19 public class UserTest {
20 
21     private InputStream in;
22     private SqlSession sqlSession;
23     private UserDao userDao;
24 
25     @Before
26     public void init() throws IOException {
27         // 读取配置文件
28         in = Resources.getResourceAsStream("SqlMapConfig.xml");
29         // 创建SqlSessionFactory
30         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
31         SqlSessionFactory factory = builder.build(in);
32         // 使用工厂生产sqlsession对象
33         sqlSession = factory.openSession();
34         // 使用sqlsession创建UserDao接口代理对象
35         userDao = sqlSession.getMapper(UserDao.class);
36     }
37 
38     @After
39     public void destory() throws IOException {
40         sqlSession.commit();
41         sqlSession.close();
42         in.close();
43     }
44 
45     @Test
46     public void findAllTest() {
47         // 使用代理对象执行方法
48         List<User> all = userDao.findAll();
49         for (User user : all) {
50             System.out.println("---------------");
51             System.out.println(user);
52             System.out.println(user.getAccounts());
53         }
54     }
55 
56 }
UserTest测试类

测试结果：

 

 

三、多对多操作（用户表[user]和角色表[role]之间）

java项目目录结构

 

 

 

前提：用户表和角色表属于多对多关系。

问题：如何查询所有用户并且返回每个用户的所有角色信息？

解析：一个用户可以有多个角色，一个角色可以被多个用户拥有，因此为多对多关系。（由于是多对多关系只测试一种就可，另一种相同方式）

1、在domain包下创建User实体类和Role实体类

User类：

 1 package sun.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.Date;
 5 import java.util.List;
 6 
 7 public class User implements Serializable {
 8     private Integer id;
 9     private String username;
10     private Date birthday;
11     private String sex;
12     private String address;
13     private List<Role> roles;
14 
15     public List<Role> getRoles() {
16         return roles;
17     }
18 
19     public void setRoles(List<Role> roles) {
20         this.roles = roles;
21     }
22 
23     public Integer getId() {
24         return id;
25     }
26 
27     public void setId(Integer id) {
28         this.id = id;
29     }
30 
31     public String getUsername() {
32         return username;
33     }
34 
35     public void setUsername(String username) {
36         this.username = username;
37     }
38 
39     public Date getBirthday() {
40         return birthday;
41     }
42 
43     public void setBirthday(Date birthday) {
44         this.birthday = birthday;
45     }
46 
47     public String getSex() {
48         return sex;
49     }
50 
51     public void setSex(String sex) {
52         this.sex = sex;
53     }
54 
55     public String getAddress() {
56         return address;
57     }
58 
59     public void setAddress(String address) {
60         this.address = address;
61     }
62 
63     @Override
64     public String toString() {
65         return "User{" +
66                 "id=" + id +
67                 ", username='" + username + '\'' +
68                 ", birthday=" + birthday +
69                 ", sex='" + sex + '\'' +
70                 ", address='" + address + '\'' +
71                 '}';
72     }
73 }
User实体类

Role类：

 1 package sun.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 
 6 /**
 7  * @Classname Role
 8  * @Description TODO
 9  * @Date 2020/9/11 14:51
10  * @Created by Administrator
11  */
12 public class Role implements Serializable {
13     private int id;
14     private String roleName;
15     private String roleDesc;
16     private List<User> users;
17 
18     public List<User> getUsers() {
19         return users;
20     }
21 
22     public void setUsers(List<User> users) {
23         this.users = users;
24     }
25 
26     public int getId() {
27         return id;
28     }
29 
30     public void setId(int id) {
31         this.id = id;
32     }
33 
34     public String getRoleName() {
35         return roleName;
36     }
37 
38     public void setRoleName(String roleName) {
39         this.roleName = roleName;
40     }
41 
42     public String getRoleDesc() {
43         return roleDesc;
44     }
45 
46     public void setRoleDesc(String roleDesc) {
47         this.roleDesc = roleDesc;
48     }
49 
50     @Override
51     public String toString() {
52         return "Role{" +
53                 "id=" + id +
54                 ", roleName='" + roleName + '\'' +
55                 ", roleDesc='" + roleDesc + '\'' +
56                 '}';
57     }
58 }
Role实体类

2、在dao包中创建RoleDao接口，定义查询方法

 1 package sun.dao;
 2 
 3 import sun.domain.Role;
 4 
 5 import java.util.List;
 6 
 7 /**
 8  * @Classname RoleDao
 9  * @Description TODO
10  * @Date 2020/9/11 14:53
11  * @Created by Administrator
12  */
13 public interface RoleDao {
14     /**
15      * 获取所有角色记录
16      * @return
17      */
18     List<Role> findAll();
19 }
RoleDao接口

3、配置RoleDao.xml映射文件

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 
 6 <mapper namespace="sun.dao.RoleDao">
 7     <resultMap id="roleMap" type="role">
 8         <id property="id" column="rid"></id>
 9         <result property="roleName" column="role_name"></result>
10         <result property="roleDesc" column="role_desc"></result>
11         <!--封装users-->
12         <collection property="users" ofType="user">
13             <id property="id" column="id"></id>
14             <result property="username" column="username"></result>
15             <result property="sex" column="sex"></result>
16             <result property="birthday" column="birthday"></result>
17             <result property="address" column="address"></result>
18         </collection>
19     </resultMap>
20 
21     <!--查询所有-->
22     <select id="findAll" resultMap="roleMap">
23         SELECT
24           u.*,
25           r.ID AS rid,
26           r.ROLE_NAME,
27           r.ROLE_DESC
28         FROM role r
29         LEFT OUTER JOIN user_role ur
30           ON r.ID = ur.RID
31         LEFT OUTER JOIN USER u
32           ON u.id = ur.UID
33     </select>
34 
35 </mapper>
RoleDao.xml

4、RoleTest测试类进行查询测试

 1 package sun.test;
 2 
 3 
 4 import org.apache.ibatis.io.Resources;
 5 import org.apache.ibatis.session.SqlSession;
 6 import org.apache.ibatis.session.SqlSessionFactory;
 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 8 import org.junit.After;
 9 import org.junit.Before;
10 import org.junit.Test;
11 import sun.dao.RoleDao;
12 import sun.dao.UserDao;
13 import sun.domain.Role;
14 import sun.domain.User;
15 
16 import java.io.IOException;
17 import java.io.InputStream;
18 import java.util.Date;
19 import java.util.List;
20 
21 public class MybatisTest {
22 
23     private InputStream in;
24     private SqlSession sqlSession;
25     private RoleDao roleDao;
26 
27     @Before
28     public void init() throws IOException {
29         // 读取配置文件
30         in = Resources.getResourceAsStream("SqlMapConfig.xml");
31         // 创建SqlSessionFactory
32         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
33         SqlSessionFactory factory = builder.build(in);
34         // 使用工厂生产sqlsession对象
35         sqlSession = factory.openSession();
36         // 使用sqlsession创建UserDao接口代理对象
37         roleDao = sqlSession.getMapper(RoleDao.class);
38     }
39 
40     @After
41     public void destory() throws IOException {
42         sqlSession.commit();
43         sqlSession.close();
44         in.close();
45     }
46 
47     @Test
48     public void findAllTest() {
49         // 使用代理对象执行方法
50         List<Role> all = roleDao.findAll();
51         for (Role role : all) {
52             System.out.println("-------------");
53             System.out.println(role);
54             System.out.println(role.getUsers());
55         }
56     }
57 
58 }
RoleTest测试类

测试结果：

 

posted @ 2020-09-11 17:42  佛祖让我来巡山  阅读(405)  评论(0)  编辑  收藏  举报

佛祖让我来巡山博客站 - 创建于 2018-08-15

开发工程师个人站，内容主要是网站开发方面的技术文章，大部分来自学习或工作，部分来源于网络,希望对大家有所帮助。

Bootstrap中文网