Mybatis provides the concept of collection to solve many-to-many query. Collection attribute is a subset of resultMap advanced result mapping. First of all, in this example, we use collection elements to solve many-to-many query. Then you’ll notice that there’s a new “ofType” property. This property is important to distinguish the type of a JavaBean (or field) property from the collection stored type. In a collection ofType refers to the type of the element in the collection.

First, a brief introduction to Demo:

  • Entity classes: The User class and the Role class, whose properties are posted in the code below
  • Relationship: A user can have multiple roles, and a role can be assigned to multiple users
  • Database table structure: User table, role table, intermediate table (used to store relationships between users and roles)
  • In this example, the user is queried to obtain information about the roles that the user owns. (In this example, the user is queried to obtain information about the users that the roles belong to, which is the same as the SQL statement in the SELECT.)

User entity class and role entity class

public class User implements Serializable{ private Integer id; private String username; private Date birthday; private String sex; private String address; private List<Role> roles; The get and set methods omit...... } public class Role implements Serializable{ private Integer roleId; private String roleName; private String roleDesc; private List<User> users; The get and set methods omit...... }Copy the code

Add the List attribute of the role owned by the user and the List attribute of the user to which the role belongs. This attribute is used in the resultMap mapping later.

2. Database table structure

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 'birthday ', sex char(1) default NULL COMMENT' gender ', Address varchar(256) default NULL COMMENT 'address ', PRIMARY KEY (id) ENGINE=InnoDB default CHARSET=utf8 INSERT INTO 'user' VALUES ('41', '2018-02-27 17:47:08', '男') 'Shijiazhuang '); INSERT INTO ` user ` VALUES (' 45 ', 'li', '2018-02-27 17:47:08', 'male', 'shijiazhuang); INSERT INTO ` user ` VALUES (' and ', 'guo', '2018-02-27 17:47:08' and 'male', 'shijiazhuang); INSERT INTO ` user ` VALUES (' 47 ', 'mde approach based', '2019-06-26 15:04:25', 'female' and 'henan); INSERT INTO ` user ` VALUES (' 48 ', 'nan', '2019-08-01 15:04:54', 'female' and 'hefei); DROP TABLE IF EXISTS role; CREATE TABLE role(ID int(11) NOT NULL COMMENT 'ID ', ROLE_NAME VARCHAR(30) DEFAULT NULL COMMENT' ID ', ROLE_DESC VARCHAR(60) DEFAULT NULL COMMENT 'Role description ', PRIMARY KEY (ID) )ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO role (ID,ROLE_NAME,ROLE_DESC) VALUES (1, 'dean', 'management of the whole college), (2,' President ', 'management of the whole company), (3,' President ', 'management of the whole school); DROP TABLE IF EXISTS user_role; CREATE TABLE user_role(UID int(11) NOT NULL COMMENT 'user id ', RID int(11) NOT NULL COMMENT' user id ', PRIMARY KEY (UID,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 VALUES (UID, rids) (41, 1), (45, 1) and (41, 2);Copy the code

The main thing here is to add intermediate tables.

FindAll (); findAll();

** @return */ List<User> findAll();Copy the code

4. Configure the findAll() method in userdao.xml

<! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < mapper namespace="com.example.dao.UserDao"> <resultMap id="userMap" type="com.example.domain.User"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="birthday" column="birthday"/> <result property="sex" column="sex"/> <result property="address" column="address"/> <collection property="roles" ofType="com.example.domain.Role" resultMap="roleMap"/> </resultMap> <resultMap id="roleMap" type="com.example.domain.Role"> <id property="roleId" column="rid"/> <result property="roleName" column="ROLE_NAME"/> <result property="roleDesc" column="ROLE_DESC"/> </resultMap> <select id="findAll" resultMap="userMap"> SELECT u.*,r.ID as rid,r.ROLE_DESC,r.ROLE_NAME FROM user u LEFT OUTER JOIN user_role ur on u.id = ur.UID LEFT OUTER JOIN role r on ur.RID = r.ID </select> </mapper>Copy the code

The main work of implementing many-to-many relational query is put here. First, the result mapping of the User class is declared through the resultMap. Tags such as ID and result are the basic attributes of the User class.
OfType is used to identify the type of the element in the collection, whereas resultMap is used to refer to other result maps to specify the attributes of the element in the collection, in this case roleMap. If roleMap is not used elsewhere, it is also possible to configure the attributes of the role directly into a subset of the Collection attributes, as shown in the following format.

<resultMap id="userMap" type="com.example.domain.User"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="birthday" column="birthday"/> <result property="sex" column="sex"/> <result property="address" column="address"/> <! --<collection property="roles" ofType="com.example.domain.Role" resultMap="roleMap"/>--> <collection property="roles" OfType = "com. Example. Domain. The Role" > <! Mysql > select id from role table; mysql > select id from role table; mysql > select id from role table; <id property="roleId" column="rid"/> <result property="roleName" column="ROLE_NAME"/> <result property="roleDesc" column="ROLE_DESC"/> </collection> </resultMap>Copy the code

Select * from SQL;

 <select id="findAll" resultMap="userMap">
       SELECT u.*,r.ID as rid,r.ROLE_DESC,r.ROLE_NAME FROM user u
         LEFT OUTER JOIN user_role ur on u.id = ur.UID
          LEFT OUTER JOIN role r on ur.RID = r.ID
    </select>
Copy the code

Id as rid: rid USER u LEFT OUTER JOIN user_role ur on U.ID = ur.UID: The preceding table joins the following table left to the same id in the User table as the UID in the User_role table

5. Test code

public class UserDaoTest { private InputStream in; private SqlSession session; private UserDao userDao; private SqlSessionFactory factory; @ Before public void init () throws the Exception {/ / get the configuration file = Resources in getResourceAsStream (" SqlMapConfig. XML "); Factory = new SqlSessionFactoryBuilder().build(in); session = factory.openSession(); userDao = session.getMapper(UserDao.class); } @After public void destory()throws Exception{ session.commit(); session.close(); in.close(); } @Test public void findAllTest(){ List<User> userList = userDao.findAll(); For (User User: userList){system.out.println (" info for each User "); System.out.println(user); System.out.println(user.getRoles()); }}Copy the code

6. Test results