MyBatis 1对1 1对多关联查询

谁践踏了优雅 2022-10-25 14:06 129阅读 0赞

1、SQL

create table user (
    id int primary key,
    name varchar(400)
    );
    insert user info VALUES(1, 'ConstXiong1');
    
    
    create table info (
    user_id int primary key,
    name varchar(400)
    );
    insert into info VALUES(1, '大熊');
    
    create table article (
    user_id int,
    title varchar(400)
    );
    insert into article VALUES(1, '文章1');
    insert into article VALUES(1, '文章2');

2、Maven 依赖

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <parent>
            <groupId>constxiong</groupId>
            <artifactId>demo</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </parent>
        <artifactId>mybatis</artifactId>
    
        <dependencies>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.21</version>
            </dependency>
    
            
            <dependency>
    		  <groupId>org.mybatis</groupId>
    		  <artifactId>mybatis</artifactId>
    		  <version>3.5.5</version>
    		</dependency>
    
        </dependencies>
    
        <build>
    
        </build>
    </project>

3、mybatis-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>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://172.31.32.184:3306/constxiong?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="constxiong@123"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper resource="constxiong/mapper/UserMapper.xml"/>
        </mappers>
    </configuration>

4、Mapper 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="constxiong.mapper.UserMapper">
        
        <select id="selectUserWithInfo" resultMap="UserWithInfo">
            select user.id, user.name, info.user_id, info.name as info_name from user,info where user.id = info.user_id
        </select>
    
        <select id="selectUserWithArticles" resultMap="UserWithArticles">
            select user.id, user.name, article.user_id, article.title from user,article where user.id = article.user_id
        </select>
    
        <resultMap id="UserWithInfo" type="constxiong.po.User">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <association property="info" javaType="constxiong.po.Info">
                <id property="userId" column="user_id"/>
                <result property="name" column="info_name"/>
            </association>
        </resultMap>
    
        <resultMap id="UserWithArticles" type="constxiong.po.User">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <collection property="articles" ofType="constxiong.po.Article">
                <id property="userId" column="user_id"/>
                <result property="title" column="title"/>
            </collection>
        </resultMap>
    
    </mapper>

5、Mapper 接口

package constxiong.mapper;
    
    import constxiong.po.User;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    import java.util.Map;
    
    /**
     * UserMapper 接口，映射对数据库的操作
     */
    public interface UserMapper {
    
        User selectUserWithInfo();
    
        User selectUserWithArticles();
    
    }

6、PO 类

package constxiong.po;
    
    import java.util.List;
    
    /**
     * 用户表模型
     */
    public class User {
        private Integer id;
    
        private String name;
    
        private String mc;
    
        private Info info;
    
        private List<Article> articles;
    
        public User() {
        }
    
        public User(Integer id, String name) {
            this.id = id;
            this.name = name;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getMc() {
            return mc;
        }
    
        public void setMc(String mc) {
            this.mc = mc;
        }
    
        public Info getInfo() {
            return info;
        }
    
        public void setInfo(Info info) {
            this.info = info;
        }
    
        public List<Article> getArticles() {
            return articles;
        }
    
        public void setArticles(List<Article> articles) {
            this.articles = articles;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", mc='" + mc + '\'' +
                    ", info=" + info +
                    ", articles=" + articles +
                    '}';
        }
    }

package constxiong.po;
    
    public class Info {
    
        private int userId;
    
        private String name;
    
        public int getUserId() {
            return userId;
        }
    
        public void setUserId(int userId) {
            this.userId = userId;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        @Override
        public String toString() {
            return "Info{" +
                    "userId=" + userId +
                    ", name=" + name +
                    '}';
        }
    }

package constxiong.po;
    
    public class Article {
    
        private int userId;
    
        private String title;
    
        public int getUserId() {
            return userId;
        }
    
        public void setUserId(int userId) {
            this.userId = userId;
        }
    
        public String getTitle() {
            return title;
        }
    
        public void setTitle(String title) {
            this.title = title;
        }
    
        @Override
        public String toString() {
            return "Article{" +
                    "userId=" + userId +
                    ", title='" + title + '\'' +
                    '}';
        }
    }

7、测试代码

String resource = "mybatis-config.xml";//xml配置文件路径
    InputStream inputStream = Resources.getResourceAsStream(resource);//读取配置
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//构建 SqlSessionFactory
    SqlSession sqlSession = sqlSessionFactory.openSession(true);//通过 SqlSessionfactory 获取 SqlSession
    
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);//通过 SqlSession 获取 Mapper 接口
    
    System.out.println("------ selectUserWithInfo ------");
    User user = userMapper.selectUserWithInfo();
    System.out.println(user);
    
    System.out.println("------ selectUserWithArticles ------");
    user = userMapper.selectUserWithArticles();
    System.out.println(user);
    
    //关闭 SqlSession
    sqlSession.close();

打印结果：

------ selectUserWithInfo ------
    User{id=1, name='ConstXiong1', mc='null', info=Info{userId=1, name=大熊}, articles=null}
    ------ selectUserWithArticles ------
    User{id=1, name='ConstXiong1', mc='null', info=null, articles=[Article{userId=1, title='文章1'}, Article{userId=1, title='文章2'}]}

--------------------

### 【Java学习资源】整理推荐 ###

*  [Java 8 Stream API Demo][]
 *  [Druid 使用][Druid]
 *  [借助 libreoffice，word 转 pdf 体验][libreoffice_word _ pdf]
 *  [Java 动态代理][Java]
 *  [openjdk jol 工具打印 Java 对象内存布局][openjdk jol _ Java]
 *  [Spring整合MyBatis][Spring_MyBatis]
 *  [MyBatis 自定义插件][MyBatis]
 *  [MyBatis 延迟加载][MyBatis 1]
 *  [MyBatis 1对1 1对多 关联查询][MyBatis 1_1 1_]
 *  [MyBatis insert 获取自增主键][MyBatis insert]
 *  [MyBatis 批量插入][MyBatis 2]
 *  [分页插件 Mybatis-PageHelper][Mybatis-PageHelper]
 *  [MyBatis 原生使用][MyBatis 3]
 *  [Sharding-JDBC 实现分库分表][Sharding-JDBC]
 *  [JDK1.8 中的日期与时间 API][JDK1.8 _ API]
 *  [Sharding-JDBC 实现读写分离][Sharding-JDBC 1]

--------------------

### 【Java面试题与答案】整理推荐 ###

*  [基础与语法][Link 1]
 *  [集合][Link 2]
 *  [网络编程][Link 3]
 *  [并发编程][Link 4]
 *  [Web][]
 *  [安全][Link 5]
 *  [设计模式][Link 6]
 *  [框架][Link 7]
 *  [算法与数据结构][Link 8]
 *  [异常][Link 9]
 *  [文件解析与生成][Link 10]
 *  [Linux][]
 *  [MySQL][]
 *  [Oracle][]
 *  [Redis][]
 *  [Dubbo][]

[Java 8 Stream API Demo]: https://www.javanav.com/val/5be3f821d00e4cc5b6177c9d0e5dd30f.html
[Druid]: https://www.javanav.com/val/79860880b84442cfb0f6f5e094d53105.html
[libreoffice_word _ pdf]: https://www.javanav.com/val/501ababe5c48493780ed23dbbc7b2cbc.html
[Java]: https://www.javanav.com/val/ab11038d7de64b4b8c0e384d1d4a9ab0.html
[openjdk jol _ Java]: https://www.javanav.com/val/66e564087e244b2f99f85091de029605.html
[Spring_MyBatis]: https://www.javanav.com/val/687c224b31a34d3c9f99fee67e3d5bcc.html
[MyBatis]: https://www.javanav.com/val/a5535343f9b545eda9665f03d62345ba.html
[MyBatis 1]: https://www.javanav.com/val/973ded541e9244aa8b3169b9fb869d60.html
[MyBatis 1_1 1_]: https://www.javanav.com/val/a9fe4555c1614b40b0da07afeabf2a66.html
[MyBatis insert]: https://www.javanav.com/val/3ac331d2674b4c108469cce54ae126f3.html
[MyBatis 2]: https://www.javanav.com/val/2d21b1463f2e4faeaf0def0c49df34a4.html
[Mybatis-PageHelper]: https://www.javanav.com/val/a8a8009f878d47dc9167bf20b9e0ab57.html
[MyBatis 3]: https://www.javanav.com/val/07f60d7946204b3b893abd5dc8bd2fa3.html
[Sharding-JDBC]: https://www.javanav.com/val/eefddd846ae845668daf9d798755c057.html
[JDK1.8 _ API]: https://www.javanav.com/val/fefecf2f089e48b294708e99954c57a1.html
[Sharding-JDBC 1]: https://www.javanav.com/val/f05fe12cb8fd444aaa59fb7f747a3224.html
[Link 1]: https://www.javanav.com/interview/93b0069472fd479393006c0e73043fc4.html
[Link 2]: https://www.javanav.com/interview/4147d95e693945e7a50345fd33ec817e.html
[Link 3]: https://www.javanav.com/interview/68a57ffc78de4133bb890bdd502ac418.html
[Link 4]: https://www.javanav.com/interview/36a24fa3258e45c89873f474ce914912.html
[Web]: https://www.javanav.com/interview/4e27cb63adb440c6825a159d35cf4010.html
[Link 5]: https://www.javanav.com/interview/03affb2ce3f04fda8853f714e5764906.html
[Link 6]: https://www.javanav.com/interview/07a32a9ff17a4006a53550ce658cafed.html
[Link 7]: https://www.javanav.com/interview/5c466456e9b54ab0a749b5948e51f1d3.html
[Link 8]: https://www.javanav.com/interview/943f917041ee4f05add24539f5b861b2.html
[Link 9]: https://www.javanav.com/interview/d3f058cfc05d4c6dadd821b622e84d36.html
[Link 10]: https://www.javanav.com/interview/67a50996c28f418e83b109ea42fc3eff.html
[Linux]: https://www.javanav.com/interview/00cccb81729540d1923bd0a0ef49533a.html
[MySQL]: https://www.javanav.com/interview/d311e78502a845f1a4bb307bd6aec2b3.html
[Oracle]: https://www.javanav.com/interview/5f29e9677edf4a16be3dce30d257c9ab.html
[Redis]: https://www.javanav.com/interview/066c62b3af4d4827b2243a822c3084a9.html
[Dubbo]: https://www.javanav.com/interview/9a3e7a7740324757abaab953f7ebc455.html