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

爱被打了一巴掌 2021-09-02 06:43 351阅读 0赞

【需求】：查询某用户发的所有帖子

【javaweb项目目录结构】：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70][]

【数据库】postgresql

用户vip\_user表单：

![20201016154532418.png][]

![20201016154803649.png][]

发帖post表单：

![20201016154624767.png][]

![20201016154847189.png][]

【配置文件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.mybatisstudy3.entity.VipUser" />
            <typeAlias alias="Post" type="main.java.com.mybatisstudy3.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.mybatisstudy3.mapper.VipUserMapper" />
        </mappers>
    </configuration>

【实体类】

用户：

package main.java.com.mybatisstudy3.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 +
                    '}';
        }
    }

帖子：

【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.mybatisstudy3.mapper.VipUserMapper">
        
        <resultMap type="VipUser" id="resultUserMap">
            <result property="pkId" column="pk_id" />
            <result property="userName" column="username" />
            <result property="mobile" column="mobile" />
            <collection property="posts" ofType="main.java.com.mybatisstudy3.entity.Post" column="user_id">
                <id property="id" column="post_id" javaType="int" jdbcType="INTEGER"/>
                <result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
                <result property="content" column="content" javaType="string" jdbcType="VARCHAR"/>
            </collection>
        </resultMap>
    
        <select id="getVipUser" resultMap="resultUserMap" parameterType="int">
    		SELECT u.*,p.*
    		FROM vip_user u, post p
    		WHERE u.pk_id=p.user_id AND pk_id=#{pkId}
      </select>
    
    </mapper>

【VipUserMapper.java】

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

【测试】

package test.java.com.mybatisstudy;
    
    import main.java.com.mybatisstudy.entity.PersonDO;
    import main.java.com.mybatisstudy.mapper.PersonMapper;
    import main.java.com.mybatisstudy3.entity.VipUser;
    import main.java.com.mybatisstudy3.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;
    import java.util.ArrayList;
    import java.util.List;
    
    public class MybatisStudy3 {
        @Test
        public void getVipUserTest(){
            String resource="main/java/com/mybatisstudy3/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);
                VipUser vipUser = vipUserMapper.getVipUser(1);
                System.out.println("查询结果为："+vipUser.toString());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

【输出】：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70]: /images/20210811/e75c9dcf493e4232a6d5cde1cb655148.png
[20201016154532418.png]: /images/20210811/6c7e2d27377e4f268d2466d8c2bb75fd.png
[20201016154803649.png]: /images/20210811/e27ecc586d7e41aba7287717fc38759e.png
[20201016154624767.png]: /images/20210811/ccf30e45f53d4279b26b3a4f0b6cf719.png
[20201016154847189.png]: /images/20210811/91e4f8cf1eb049e1912ad51935ea86cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1]: /images/20210811/cbdc5aeedbfc482b943a8e7e20255282.png