【SSM - MyBatis06】一对一关联查询案例实现(Spring和MyBatis整合)

布满荆棘的人生 2022-12-10 14:46 155阅读 0赞

### 文章目录 ###

*   *  案例描述
     *   *  1. 创建数据库表person和card
         *  2. 创建bean对象
         *  3. 创建dao层映射层
         *  4. 创建连接数据库的属性文件db.properties(键值对形式)
         *  5. spring整合MyBatis，核心配置文件applicationContext.xml
         *  6. 创建映射文件mapper.xml
         *  7. 创建测试类
         *   *  测试结果

## 案例描述 ##

一个人有一个身份证，一个身份证只对应一个人所有。人和身份证之间的关系是一对一的关联关系。创建person表和card表，通过resultMap映射的一对一关联映射association实现。（这里设计表，person表有外键id\_card关联card表的主键id）

### 1. 创建数据库表person和card ###

(一对一关联，一个表的外键是另一个表的主键，在哪个表创建外键都可以)

-- ----------------------------
    -- 身份证card表，主键id自动增长
    -- ----------------------------
    DROP TABLE IF EXISTS `card`;
    CREATE TABLE `card` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `code` varchar(18) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    INSERT INTO `card` VALUES ('1', '111111111123456789');
    INSERT INTO `card` VALUES ('2', '222222222223456789');
    INSERT INTO `card` VALUES ('3', '333333333333456789');
    
    
    -- ----------------------------
    -- 人 person表，主键id自动增长，在person表中设置card表的主键id作为外键
    -- ----------------------------
    DROP TABLE IF EXISTS `person`;
    CREATE TABLE `person` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) DEFAULT NULL,
      `id_card` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `FK_person_card` (`id_card`),
      CONSTRAINT `FK_person_card` FOREIGN KEY (`id_card`) REFERENCES `card` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    INSERT INTO `person` VALUES ('1', '张三', '1');
    INSERT INTO `person` VALUES ('2', '李四', '2');
    INSERT INTO `person` VALUES ('3', '王五', '3');

### 2. 创建bean对象 ###

//person表
    public class Person { 
        private Integer id;
        private String username;
        private Card card; // 1 111111111123456789
        //省略getter/setter方法
    }
    
    //card表
    public class Card { 
        private Integer id;
        private String code;
        //省略getter/setter
    }

### 3. 创建dao层映射层 ###

@Repository
    public interface CardMapper { 
    
        //通过id来查询身份证表Card的信息
        public Card getCardById(Integer id);
    }
    
    @Repository
    public interface PersonMapper { 
    
        //通过一条Sql语句来查询
        Person getPersonById(int id);
    
        //通过嵌套查询
        Person getPersonById2(int id);
    
        //通过用户名查询
        Person getPersonByName(String username);
    }

### 4. 创建连接数据库的属性文件db.properties(键值对形式) ###

jdbc.driver = com.mysql.jdbc.Driver
    jdbc.url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8
    jdbc.username = root
    jdbc.password = 861221293

### 5. spring整合MyBatis，核心配置文件applicationContext.xml ###

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    
        
        <context:property-placeholder location="classpath*:db.properties" />
    
        
        <context:component-scan base-package="com.xgf.correlation.one_to_one"/>
    
        
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="driverClassName" value="${jdbc.driver}"/>
        </bean>
    
        
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
        </bean>
    
        
        <mybatis:scan base-package="com.xgf.correlation.one_to_one"/>
    
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"/>
        </bean>
        
        <tx:annotation-driven transaction-manager="transactionManager"/>
    </beans>

### 6. 创建映射文件mapper.xml ###

这里通过两种方式来查询

*  第一种(getPersonById)直接通过一条SQL语句查询
 *  第二种(getPersonById2)嵌套sql查询，第二种才需要创建card表的映射文件【推荐使用第二种】

PersonMapper.xml

<mapper namespace="com.xgf.mybatis.correlation.one_to_one.dao.PersonMapper">
    
        
        <resultMap id="personMap" type="com.xgf.correlation.one_to_one.bean.Person" autoMapping="true">
            
           <id property="id" column="id"/>
           <result property="username" column="username"/>
    
            
           <association property="card" javaType="com.xgf.correlation.one_to_one.bean.Card">
               <id property="id" column="id"/>
               <result property="code" column="code"/>
           </association>
    
        </resultMap>
    
    
        <select id="getPersonById" resultMap="personMap">
            select p.id,p.username,c.id,c.code
            from person p inner join card c on p.id_card = c.id
            where p.id = #{id}
        </select>
    
        <select id="getPersonByName" resultMap="personMap">
            select p.id,p.username,c.id,c.code
            from person p inner join card c on p.id_card = c.id
            where p.username = #{username}
        </select>
    
    
    
        
    
        
    
        <resultMap id="personMap2" type="com.xgf.correlation.one_to_one.bean.Person">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <association property="card" javaType="com.xgf.correlation.one_to_one.bean.Card" select="com.xgf.correlation.one_to_one.dao.CardMapper.getCardById" column="id" foreignColumn="id_card" />
        </resultMap>
    
        <select id="getPersonById2" resultMap="personMap2" parameterType="int">
            select id,username
            from person
            where id=#{id}
        </select>
    
    </mapper>

CardMapper.xml

<mapper namespace="com.xgf.correlation.one_to_one.dao.CardMapper">
    
        <select id="getCardById" parameterType="int" resultType="com.xgf.correlation.one_to_one.bean.Card">
            select id,code
            from card
            where id=#{id}
        </select>
    
    </mapper>

### 7. 创建测试类 ###

#### 测试结果 ####

> test01() 测试getPersonById结果(一条sql查两表)
> 
> > Person\{id=1, username=‘张三’, card=Card\{id=1, code=‘111111111123456789’\}\}
> 
> test02()测试getPersonById2结果(嵌套查询)
> 
> > Person\{id=2, username=‘李四’, card=Card\{id=2, code=‘222222222223456789’\}\}
> 
> test03()测试getPersonByName结果(用户名查找)
> 
> > Person\{id=2, username=‘王五’, card=Card\{id=2, code=‘333333333333456789’\}\}