mybatis advanced query - one way query

Catalog

I. demand

Two, analysis

Three, design

4, Mapper layer

5, Test code

Six, thinking

I. demand

Query the detailed information of a user. In addition to the basic information of the user, it also includes the user's department and the user's role

Two, analysis

Users and departments:

1. A user can only belong to one department, and there are multiple users in one department, so the relationship between user and department is one to many

2. Through the user query department, only one side of many needs to be remembered, so one to many one-way data is used

Users and roles:

1. A user has multiple roles, and a role can be assigned to multiple users, so the relationship between users and roles is many to many

2. Querying roles through users

Three, design

Entity design

@Data
public class User {

    private Integer id;
    private String userName;
    private String password;
    private Date birthday;

    private Dept dept;
    private List<Role> roles;

}

@Data
public class Role {

    private Integer id;
    private String name;
    private String comment;

}

@Data
public class Dept {

    private Integer id;
    private String name;

}

Database table design

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

Table data

INSERT INTO `dept` (`id`,`name`) VALUES (1, 'R & D department'),(2, 'Sales Department'),(3, 'After sales department');
INSERT INTO `role` (`id`,`name`,`comment`) VALUES (1, 'General manager', 'General manager's overall situation'),(2, 'sales manager', 'Responsible for sales'),(3, 'Minister', 'Management role');
INSERT INTO `user` (`id`,`user_name`,`password`,`birthday`,`dept_id`) VALUES (1, 'Zhang Sanfeng', 'aaabbb', '2019-11-12', 1),(2, 'tom', '66888999', '2019-11-10', 2),(3, 'kitty', 'aaaabbb', '2018-08-12', 3);
INSERT INTO `user_role` (`id`,`user_id`,`role_id`) VALUES (1, 1, 1),(2, 1, 3),(3, 2, 1),(4, 2, 2);

4, Mapper layer

Interface:

public interface UserMapper {

    User selectUserById(Integer id);

}

mapper mapping description file:

    <resultMap id="user" type="com.lcy.pojo.User">
        <result property="id"        column="u_id"/>
        <result property="userName"  column="u_user_name"/>
        <result property="password"  column="u_password"/>
        <result property="birthday"  column="u_birthday" javaType="java.util.Date"/>

        <association property="dept" javaType="Dept">
            <id property="id" column="d_id"/>
            <result property="name" column="d_name" />
        </association>

        <collection property="roles" ofType="Role">
            <id property="id" column="r_id"/>
            <result property="name" column="r_name" />
            <result property="comment" column="r_comment" />
        </collection>
    </resultMap>

    <!--select * from user where id = #{id}-->
    <select id="selectUserById" resultMap="user">
      SELECT
            u.id AS u_id,
            u.user_name AS u_user_name,
            u.`password` AS u_password,
            u.birthday AS u_birthday,
            d.id AS d_id,
            d.`name` AS d_name,
            r.id AS r_id,
            r.`name` AS r_name,
            r.COMMENT AS r_comment
        FROM
            `user` u
            LEFT JOIN dept d ON u.dept_id = d.id
            LEFT JOIN user_role ur ON u.id = ur.user_id
            LEFT JOIN role r ON ur.role_id = r.id
        where u.id = #{id}
    </select>

5, Test code

    UserMapper mapper;
    SqlSession session;

    @Before
    public void init() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        session = sqlSessionFactory.openSession();
        mapper = session.getMapper(UserMapper.class);
    }

    @After
    public void destory() {
        session.commit();
        session.close();
    }

    @Test
    public void selectUserById() {
        User user = mapper.selectUserById(2);
        System.out.println(user);
    }

Six, thinking

New demand:

In the past, you used to query departments by users. Can you also query all users of the user's department?

That is, to add the attribute list < user > user; to the department class, first query the Department of the user through the user ID, and then obtain all users under the Department?

Pseudo code:

mapper.selectUserById(1).getDept().getUsers() / / get all colleagues with user id 1

Analysis:

This problem, which I met before when I was learning hibernate, is briefly described here

Class design:

In the User class: there is a Dept attribute

Dept class: User collection

Then we are using user.getDept() to get the Department, but if we do two-way data binding, the obtained Department contains the User collection, then Hibernate will query the User again, query the User, but the User has the Dept, which will cause a dead cycle!!!

Conclusion:

No matter it is one-to-one or one to many, do not do two-way data binding, otherwise it will cause the dead cycle problem in the query!!!

84 original articles published, 7 praised, 10000 visitors+
Private letter follow

Tags: Session Attribute Hibernate Database

Posted on Wed, 15 Jan 2020 04:53:50 -0500 by phpdragon