SSM框架实现一对一级联查询

比眉伴天荒 2021-09-22 01:26 663阅读 0赞

引言：使用SSM框架完成一对一级联查询，也就是两张表进行关联，比如一个人只有一个身份证，或者身份证只属于一个人，这就是一对一，接下来通过tb\_user表和tb\_idcard表实现两张表的关联，这两张表外键是灵一张表的主键，可以把外键放在person表，也可以放在idcard表，本次以放在person表的card\_id为主，card\_id为外键

#### 环境准备 ####

> 开发工具：Intellij IDEA  
> 数据库：MySQL5.7  
> 环境：JDK1.8 、Maven环境

#### 数据库 ####

> 第一张tb\_person表，第一张tb\_idcard表  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center]  
> ![在这里插入图片描述][8f99c269d43c4549be4fcce49f499a9d.png_pic_center]

> 并在tb\_person和tb\_idcard表里添加几条数据  
> ![在这里插入图片描述][6a806ab6b3b0451dbce94cd66ec4620b.png_pic_center]![在这里插入图片描述][81cb8e6c166c47f9ab4f1f16f2efd445.png_pic_center]

#### Person实体 ####

package com.ssm.domain;
    public class Person {
        private Integer id;
        public Person() {
        }
        private String name;
        private Integer age;
        public IdCard getCard() {
            return card;
        }
        public void setCard(IdCard card) {
            this.card = card;
        }
        private IdCard card;
        public Integer getId() {
            return id;
        }
        @Override
        public String toString() {
            return "Person{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", age=" + age +
                    ", card=" + card +
                    ", sex='" + sex + '\'' +
                    '}';
        }
        public void setId(Integer id) {
            this.id = id;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public Integer getAge() {
            return age;
        }
       public void setAge(Integer age) {
            this.age = age;
        }
        public String getSex() {
            return sex;
        }
        public void setSex(String sex) {
            this.sex = sex;
        }
        private String sex;
    }

#### IdCar实体 ####

package com.ssm.domain;
    public class IdCard {
        public Integer getId() {
            return id;
        }
        public void setId(Integer id) {
            this.id = id;
        }
        public IdCard(Integer id, String code) {
            this.id = id;
            this.code = code;
        }
        @Override
        public String toString() {
            return "IdCard{" +
                    "id=" + id +
                    ", code='" + code + '\'' +
                    '}';
        }
        public IdCard() {
        }
        public String getCode() {
            return code;
        }
        public void setCode(String code) {
            this.code = code;
        }
        private Integer id;
        private String code;
    }

#### db.properties配置文件 ####

jdbc.driver = com.mysql.jdbc.Driver
    jdbc.url = jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf-8
    jdbc.username = root
    jdbc.password = 123456

#### 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>
        
        <properties  resource="db.properties"/>
        
        <settings>
            <setting name="lazyLoadingEnabled" value="true"/>
            
            <setting name="aggressiveLazyLoading" value="false"/>
        </settings>
        <typeAliases>
            <package name="com.ssm.domain"/>
        </typeAliases>
        <environments default="mysql">
            <environment id="mysql">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"></property>
                    <property name="url" value="${jdbc.url}"></property>
                    <property name="username" value="${jdbc.username}"></property>
                    <property name="password" value="${jdbc.password}"></property>
                </dataSource>
            </environment>
        </environments>
        <mappers>
                 
            <mapper resource="com.ssm.mapper/IdCardMapper.xml"></mapper>
            <mapper resource="com.ssm.mapper/PersonMapper.xml"></mapper>   
        </mappers>
    </configuration>

#### PersonMapper.xml ####

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="resources.com.ssm.mapper.PersonMapper">
    //第一种方法
        <select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
           select * from t_person where id=#{id}
        </select>
        <resultMap id="IdCardWithPersonResult" type="Person">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="sex" column="sex"/>
            
            <result property="age" column="age"/>
            <association property="card" column="card_id" javaType="IdCard" select="resources.com.ssm.mapper.IdCardMapper.findIdCardById"/>
        </resultMap>
    
    </mapper>

#### IdCardMapper.xml ####

#### 测试类 ####

@Test
       public void TestAssociation() throws IOException {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            //初始化mybatis，创建SqlSessionFactory类实例
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            //创建Session实例
            SqlSession session = sqlSessionFactory.openSession();
            Person person=session.selectOne("resources.com.ssm.mapper.PersonMapper.findPersonById",1);
            System.out.println(person);
        }

#### 效果截图，查询成功 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 1]

> 备注，若需要此案例，微信扫码关注“源码文集”主窗口回复2领取源码案例。  
> ![在这里插入图片描述][f4800dcbc2c045c4974b172d5c894ac2.png_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center]: /images/20210920/27fcb716d3704d2489301ed4d2d534ad.png
[8f99c269d43c4549be4fcce49f499a9d.png_pic_center]: /images/20210920/32e3fd6c1de54042b0996ef955c78251.png
[6a806ab6b3b0451dbce94cd66ec4620b.png_pic_center]: https://img-blog.csdnimg.cn/6a806ab6b3b0451dbce94cd66ec4620b.png#pic_center
[81cb8e6c166c47f9ab4f1f16f2efd445.png_pic_center]: /images/20210920/3ce13cbc64924c3390b98d086c702f28.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210920/7b1beb22976e4bfcad9edbd6459cbf98.png
[f4800dcbc2c045c4974b172d5c894ac2.png_pic_center]: /images/20210920/ec4d97a808af4538890a569c203382af.png