The statement

This is some summary notes I made after learning Mybatis. If you are interested, you can go to see the relevant video. It is very good and there is not much nonsense.

instructions

Many-to-many means that multiple records in one table correspond to multiple records in another table when joining queries. The diagram below:

The SQL statement is:

        select u.*,r.id as rid, r.role_name, r.role_desc from role r
         left outer join user_role ur on r.id = ur.rid
         left outer join user u on u.id = ur.uid
Copy the code

Such queries also need to go through an intermediate relational table

The code for creating a table in the database is placed directly here:

DROP TABLE IF EXISTS `user`; CREATE TABLE 'user' (' id 'int(11) NOT NULL AUTO_increment,' username 'varchar(32) NOT NULL COMMENT' user ', 'birthday' datetime default NULL COMMENT 'birthday ',' sex 'char(1) default NULL COMMENT' sex ', 'address' varchar(256) default NULL COMMENT 'address ', PRIMARY KEY (' id')) ENGINE=InnoDB default CHARSET=utf8; Insert into 'user' (' id ',' username ',' sex ',' address ') values (41,' birthday ','2018-02-27 ' 17:47:08 'and' male 'and' Beijing '), (42, 'king of small 2', '2018-03-02 15:09:37', 'female' and 'Beijing Jin Yanlong'), (43, 'king of small 2', '2018-03-04 Jin Yanlong 11:34:34 'and' female 'and' Beijing '), (45 'preach wisdom podcasts,' the 2018-03-04 12:04:06 'and' male ', 'Beijing Jin Yanlong'), (46, 'Lao wang', '2018-03-07 17:37:26 'and' male 'and' Beijing '), (48, 'ma Po li', '2018-03-08 11:44:00', 'female' and 'Beijing correction'); 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 'ID ', 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`), 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 ` `, ` rids `) values (41, 1), (45, 1) and (41, 2);Copy the code

1. Write object entity classes

You need two object entity classes, User and Role

public class Role implements Serializable { private Integer roleId; private String roleName; private String roleDesc; // Many-to-many mapping; A role can be assigned to multiple users. Private List<User> Users; public List<User> getUsers() { return users; } public void setUsers(List<User> users) { this.users = users; } @Override public String toString() { return "Role{" + "roleId=" + roleId + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } public Integer getRoleId() { return roleId; } public void setRoleId(Integer roleId) { this.roleId = roleId; } 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; }}Copy the code

public class User implements Serializable { private Integer id; private String username; private String address; private String sex; private Date birthday; // Many-to-many mapping: a user can have multiple roles private List<Role> roles; public List<Role> getRoles() { return roles; } public void setRoles(List<Role> roles) { this.roles = roles; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", address='" + address + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + '}'; } 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 String getAddress() { return address; } public void setAddress(String address) { this.address = address; } 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; }}Copy the code

2. Write the Role persistence layer interface

Public interface IRoleDao {/** * query all roles * @return */ List<Role> findAll(); }Copy the code

3. Create a mapping file

<? The 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="com.zhouman.dao.IRoleDao"> <! ResultMap -> <resultMap ID ="roleMap" type="role"> < ID property="roleId" column="rid"></ ID >< result property="roleName" column="ROLE_NAME"></result> <result property="roleDesc" column="ROLE_DESC"></result> <collection property="users" ofType="user"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> </collection> </resultMap> <! <select id="findAll" resultMap="roleMap"> select u.*,r.id as rid, r.role_name r.role_desc from role r left outer join user_role ur on r.id = ur.rid left outer join user u on u.id = ur.uid </select> </mapper>Copy the code

4. Write test methods

public class RoleTest { private InputStream inputStream; private SqlSession sqlSession; private IRoleDao roleDao; @Before public void init() throws Exception { //1. Reading configuration files, the generated byte input stream inputStream. = the Resource class. The getResourceAsStream ("/SqlMapConfig. XML "); SqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession = factory.openSession(); RoleDao = sqlSession.getMapper(iroleDao.class); } @after public void destroy() throws Exception {// Commit transaction sqlsession.mit (); inputStream.close(); sqlSession.close(); @throws Exception */ @test public void testFindAll() throws Exception {List<Role> Roles = roleDao.findAll(); For (Role Role: roles) {System. Out. Println (" -- -- -- -- -- each Role of information -- -- -- -- -- - "); System.out.println(role); System.out.println(role.getUsers()); }}}Copy the code

Additional instructions

Above, Role connects to User

What about users connecting to roles?

SQL statement fine tuning:

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.uid
 left outer join role r on r.id = ur.rid
Copy the code

Then add User:

// Many-to-many mapping: a user can have multiple roles private List<Role> roles; public List<Role> getRoles() { return roles; } public void setRoles(List<Role> roles) { this.roles = roles; }Copy the code

Then it is to write the corresponding mapping file:

<? The 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="com.zhouman.dao.IUserDao"> <! <resultMap ID ="userMap" type=" User ">< ID property=" ID "column=" ID "></ ID >< result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> <collection property="roles" ofType="role"> <id property="roleId" column="rid"></id> <result property="roleName" column="ROLE_NAME"></result> <result property="roleDesc" column="ROLE_DESC"></result> </collection> </resultMap> <! Select * from all; <select id="findAll" resultMap="userMap"> 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.uid left outer join role r on r.id = ur.rid </select> </mapper>Copy the code

Final test method

public class UserTest { private InputStream inputStream; private SqlSession sqlSession; private IUserDao userDao; @Before public void init() throws Exception { //1. Reading configuration files, the generated byte input stream inputStream. = the Resource class. The getResourceAsStream ("/SqlMapConfig. XML "); SqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession = factory.openSession(); UserDao = sqlsession.getMapper (iUserDao.class); //4. } @after public void destroy() throws Exception {// Commit transaction sqlsession.mit (); inputStream.close(); sqlSession.close(); } /** * Query all * @throws Exception */ @test public void testFindAll() throws Exception {List<User> users = userDao.findAll(); For (User User: users) {System. Out. Println (" -- -- -- -- -- each User's information -- -- -- -- -- - "); System.out.println(user); System.out.println(user.getRoles()); }}}Copy the code