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

女爷i 2021-09-02 08:01 320阅读 0赞

【需求】获取本篇帖子对应的所有信息（即本篇帖子关联所属用户信息）

【数据表】

vip\_user表

![20201019091827716.png][]

![20201019092625908.png][]

post表

![20201019091906741.png][]

![2020101909264098.png][]

【表结构】

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70][]

【配置文件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="VipUser" type="main.java.com.mybatisstudy4.entity.VipUser" />
            <typeAlias alias="Post" type="main.java.com.mybatisstudy4.entity.Post" />
        </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://localhost:5432/people?characterEncoding=UTF-8" />
                    <property name="username" value="postgres" />
                    <property name="password" value="root" />
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <mapper class="main.java.com.mybatisstudy4.mapper.VipUserMapper" />
        </mappers>
    </configuration>

【实体类】

用户实体：

package main.java.com.mybatisstudy4.entity;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class VipUser implements Serializable {
        private Integer pkId;
        private String userName;
        private String mobile;
        private List<Post> posts;
    
        public Integer getPkId() {
            return pkId;
        }
    
        public void setPkId(Integer pkId) {
            this.pkId = pkId;
        }
    
        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<Post> getPosts() {
            return posts;
        }
    
        public void setPosts(List<Post> posts) {
            this.posts = posts;
        }
    
        @Override
        public String toString() {
            return "VipUser{" +
                    "pkId=" + pkId +
                    ", userName='" + userName + '\'' +
                    ", mobile='" + mobile + '\'' +
                    ", posts=" + posts +
                    '}';
        }
    }

帖子实体：

package main.java.com.mybatisstudy4.entity;
    
    import java.io.Serializable;
    
    public class Post implements Serializable {
        private int id;
        private VipUser user;
        private String title;
        private String content;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public VipUser getUser() {
            return user;
        }
    
        public void setUser(VipUser user) {
            this.user = user;
        }
    
        public String getTitle() {
            return title;
        }
    
        public void setTitle(String title) {
            this.title = title;
        }
    
        public String getContent() {
            return content;
        }
    
        public void setContent(String content) {
            this.content = content;
        }
    
        @Override
        public String toString() {
            return "Post{" +
                    "id=" + id +
                    ", user=" + user +
                    ", title='" + title + '\'' +
                    ", content='" + content + '\'' +
                    '}';
        }
    }

【VipUserMapper.xml】

<?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.mybatisstudy4.mapper.VipUserMapper">
        
        <resultMap type="Post" id="resultPostsMap">
            <result property="id" column="post_id"/>
            <result property="title" column="title"/>
            <result property="content" column="content"/>
            <association property="user" javaType="VipUser">
                <id property="pkId" column="pk_id"/>
                <result property="userName" column="username"/>
                <result property="mobile" column="mobile"/>
            </association>
        </resultMap>
    
        <select id="getPosts" resultMap="resultPostsMap" parameterType="int">
    		SELECT u.*,p.*
    		FROM vip_user u, post p
    		WHERE u.pk_id=p.user_id AND p.post_id=#{id}
        </select>
    </mapper>

【VipUserMapper.java】

package main.java.com.mybatisstudy4.mapper;
    
    import main.java.com.mybatisstudy4.entity.Post;
    
    public interface VipUserMapper {
        /**
         * 根据pkId获取VipUser
         * @param id
         * @return
         */
        Post getPosts(int id);
    }

【测试】

package test.java.com.mybatisstudy;
    
    
    import main.java.com.mybatisstudy4.entity.Post;
    import main.java.com.mybatisstudy4.mapper.VipUserMapper;
    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 MybatisStudy4 {
        @Test
        public void getPostsTest(){
            String resource="main/java/com/mybatisstudy4/Config.xml";
            InputStream inputStream=null;
            try {
                inputStream=Resources.getResourceAsStream(resource);
                SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
                SqlSession sqlSession=sqlSessionFactory.openSession();
                VipUserMapper vipUserMapper=sqlSession.getMapper(VipUserMapper.class);
                Post post = vipUserMapper.getPosts(1);
                System.out.println("查询结果为："+post.toString());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

【输出】

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1][]

[20201019091827716.png]: /images/20210811/20477a188b26400f8ba5950fb8b7c93e.png
[20201019092625908.png]: /images/20210811/66aa7414443447fb8c89442068fd6efa.png
[20201019091906741.png]: /images/20210811/1ef3e21e47d9430d936f84f92f7c6c0a.png
[2020101909264098.png]: /images/20210811/4835edd3aa904b38ae8bf6eee53648cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70]: /images/20210811/5c3012cb88b84b91bd317d422c569d06.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1]: /images/20210811/8db3024e6f1b45ed8e2ee23065201988.png