MyBatis表关联多对多简单项目练习

叁歲伎倆 2021-09-02 03:47 384阅读 0赞

【需求】

1.  添加用户信息
2.  根据用户id获取用户信息
3.  根据用户姓名模糊查询获取用户信息
4.  添加门户信息
5.  根据门户id获取门户信息
6.  添加用户门户

【数据库】

user用户表：

![2020102314204381.png][]

![20201023142104551.png][]

group门户表

![20201023142129652.png][]

![20201023142149507.png][]

user\_group用户和门户的关系表

![20201023142240233.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70][]

【项目结构】

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1][]

【Config.xml配置类】

<?xml version='1.0' encoding='UTF-8'?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <settings>
            <setting name="logImpl" value="STDOUT_LOGGING"/>
        </settings>
    
        <typeAliases>
            <typeAlias alias="User" type="main.java.com.mybatisstudy5.pojo.User" />
            <typeAlias alias="UserGroup" type="main.java.com.mybatisstudy5.pojo.UserGroup" />
            <typeAlias alias="Group" type="main.java.com.mybatisstudy5.pojo.Group" />
        </typeAliases>
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC" />
                <dataSource type="POOLED">
                    <property name="driver" value="org.postgresql.Driver" />
                    <property name="url" value="jdbc:postgresql://127.0.0.1:5432/people" />
                    <property name="username" value="postgres" />
                    <property name="password" value="root" />
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            
            <mapper class="main.java.com.mybatisstudy5.mapper.GroupMapper" />
            <mapper class="main.java.com.mybatisstudy5.mapper.UserGroupMapper" />
            <mapper class="main.java.com.mybatisstudy5.mapper.UserMapper" />
        </mappers>
    </configuration>

【实体类】

User类

package main.java.com.mybatisstudy5.pojo;
    
    import java.util.List;
    
    public class User {
        private Integer id;
        private String username;
        private String mobile;
        private List<Group> groups;
    
        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 getMobile() {
            return mobile;
        }
    
        public void setMobile(String mobile) {
            this.mobile = mobile;
        }
    
        public List<Group> getGroups() {
            return groups;
        }
    
        public void setGroups(List<Group> groups) {
            this.groups = groups;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", mobile='" + mobile + '\'' +
                    ", groups=" + groups +
                    '}';
        }
    }

Group类

package main.java.com.mybatisstudy5.pojo;
    
    import java.util.List;
    
    public class Group {
        private int groupId;
        private String groupName;
        private List<User> users;
    
        public int getGroupId() {
            return groupId;
        }
    
        public void setGroupId(int groupId) {
            this.groupId = groupId;
        }
    
        public String getGroupName() {
            return groupName;
        }
    
        public void setGroupName(String groupName) {
            this.groupName = groupName;
        }
    
        public List<User> getUsers() {
            return users;
        }
    
        public void setUsers(List<User> users) {
            this.users = users;
        }
    
        @Override
        public String toString() {
            return "Group{" +
                    "groupId=" + groupId +
                    ", groupName='" + groupName + '\'' +
                    ", users=" + users +
                    '}';
        }
    }

UserGroup类

package main.java.com.mybatisstudy5.pojo;
    
    public class UserGroup {
        private int userId;
        private int groupId;
    
        public int getUserId() {
            return userId;
        }
    
        public void setUserId(int userId) {
            this.userId = userId;
        }
    
        public int getGroupId() {
            return groupId;
        }
    
        public void setGroupId(int groupId) {
            this.groupId = groupId;
        }
    }

【mapper】

UserMapper以及UserMapper.xml

package main.java.com.mybatisstudy5.mapper;
    
    import main.java.com.mybatisstudy5.pojo.Group;
    import main.java.com.mybatisstudy5.pojo.User;
    
    import java.util.List;
    
    public interface UserMapper {
        /**
         * 添加用户
         * @param user
         * @return
         */
        int insertUser(User user);
    
        /**
         * 根据id获取用户信息
         * @param id
         * @return
         */
        User getUserById(Integer id);
    
        /**
         * 模糊查询获取User
         * @param username
         * @return
         */
        User getUserByUsername(@Param("username") String username);
    }

<?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="main.java.com.mybatisstudy5.mapper.UserMapper">
        <insert id="insertUser" parameterType="User">
            insert into "user"(username,mobile) values(#{username},#{mobile})
        </insert>
    
        <select id="getGroupsByUserId" resultMap="resultGroup" parameterType="int">
            select g.*
            from "group" g,user_group u
            where g.group_id=u.group_id and u.user_id =#{user_id}
        </select>
    
        
        <resultMap id ="resultGroup" type="Group">
            <result property="groupId" column="id"/>
            <result property="groupName" column="group_name"/>
        </resultMap>
    
        <resultMap id="resultUser" type="User">
            <result property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="mobile" column="mobile"/>
            <collection property="groups" column="id" select="getGroupsByUserId" />
        </resultMap>
    
        <select id="getUserById" resultMap="resultUser" parameterType="Integer">
            select * from "user" where id=#{id}
        </select>
    
        <select id="getUserByUsername" resultType="User" parameterType="String">
            SELECT * FROM "user"
            WHERE username like '%${username}%'
        </select>
    
    </mapper>

GroupMapper以及对应的GroupMapper.xml

package main.java.com.mybatisstudy5.mapper;
    
    import main.java.com.mybatisstudy5.pojo.Group;
    
    public interface GroupMapper {
        /**
         * 添加门户
         * @param group
         * @return
         */
        int insertGroup(Group group);
    
        /**
         * 根据groupId获取门户信息
         * @param groupId
         * @return
         */
        Group getGroupByGroupId(Integer groupId);
    
    }

UserGroupMapper以及对应的UserGroupMapper.xml

package main.java.com.mybatisstudy5.mapper;
    
    import main.java.com.mybatisstudy5.pojo.Group;
    import main.java.com.mybatisstudy5.pojo.UserGroup;
    
    import java.util.List;
    
    public interface UserGroupMapper {
    
        /**
         * 添加UserGroup
         * @param userGroup
         * @return
         */
        int insertUserGroup(UserGroup userGroup);
    }

【test测试】

package test.java.com.mybatisstudy;
    
    import main.java.com.mybatisstudy5.mapper.GroupMapper;
    import main.java.com.mybatisstudy5.mapper.UserGroupMapper;
    import main.java.com.mybatisstudy5.mapper.UserMapper;
    import main.java.com.mybatisstudy5.pojo.Group;
    import main.java.com.mybatisstudy5.pojo.User;
    import main.java.com.mybatisstudy5.pojo.UserGroup;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.jupiter.api.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    
    public class Mybatisstudy5 {
    
        @Test
        public void getUserByUsernameTest() throws IOException {
            InputStream inputStream=Resources.getResourceAsStream("main/java/com/mybatisstudy5/Config.xml");
            SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession=sqlSessionFactory.openSession();
            UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
            User user = userMapper.getUserByUsername("zha");
            sqlSession.commit();
            System.out.println(user.toString());
        }
    
        @Test
        public void insertUserGroupTest() throws IOException {
            InputStream inputStream=Resources.getResourceAsStream("main/java/com/mybatisstudy5/Config.xml");
            SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession=sqlSessionFactory.openSession();
            UserGroupMapper ugm=sqlSession.getMapper(UserGroupMapper.class);
            UserGroup ug=new UserGroup();
            ug.setGroupId(2);
            ug.setUserId(3);
            int result = ugm.insertUserGroup(ug);
            sqlSession.commit();
            System.out.println(result>0?"添加成功":"添加失败");
    
        }
    
        @Test
        public void getGroupByGroupIdTest() throws IOException {
            InputStream inputStream = Resources.getResourceAsStream("main/java/com/mybatisstudy5/Config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            GroupMapper groupMapper = sqlSession.getMapper(GroupMapper.class);
            Group group = groupMapper.getGroupByGroupId(1);
            sqlSession.commit();
            sqlSession.close();
            System.out.println(group.toString());
        }
    
        @Test
        public void insertGroupTest() throws IOException {
            InputStream inputStream = Resources.getResourceAsStream("main/java/com/mybatisstudy5/Config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            GroupMapper groupMapper = sqlSession.getMapper(GroupMapper.class);
            Group group = new Group();
            group.setGroupName("Group_3");
            int result = groupMapper.insertGroup(group);
            sqlSession.commit();
            System.out.println(result > 0 ? "添加门户成功" : "添加门户失败");
    
        }
    
        @Test
        public void getUserByIdTest() throws IOException {
            InputStream inputStream = Resources.getResourceAsStream("main/java/com/mybatisstudy5/Config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.getUserById(1);
            sqlSession.commit();
            System.out.println(user.toString());
        }
    
        @Test
        public void insertUserTest() throws IOException {
            InputStream inputStream = Resources.getResourceAsStream("main/java/com/mybatisstudy5/Config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserGroup userGroup = new UserGroup();
            User user = new User();
            user.setUsername("测试");
            user.setMobile("123344");
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int result = userMapper.insertUser(user);
            sqlSession.commit();
            System.out.println(result > 0 ? "添加成功" : "添加失败");
        }
    }

[2020102314204381.png]: /images/20210811/66302daa23844cf492cb6a1d5a506041.png
[20201023142104551.png]: /images/20210811/d746a693924046fcb525ab39a605ed2a.png
[20201023142129652.png]: /images/20210811/85d73d7dacd9471c91ff6c19ef1d2226.png
[20201023142149507.png]: /images/20210811/dbbc07b8f4ef4980b255b2ed230ab38a.png
[20201023142240233.png]: /images/20210811/8e1d8e372434428483310985213f5dda.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70]: /images/20210811/0e1d9d370e2543399eb4ce2c95d7563a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1]: /images/20210811/c90158261c524c26911556898a51a629.png