MyBatis中的多表操作情形一：一对一(方式1：一条sql语句查询)

￡神魔★判官ぃ 2022-05-12 02:48 125阅读 0赞

*  MyBatis支持多表操作，即可以将数据库中多表的关系映射到对象之间的关系中
 *  表与表之间的关系可以有：一对一，一对多，多对多
 *  关系一演示案例：人和身份证是一对一的，分别建两个表person和card，其中person的cardid字段外键关联card的id，当需求是查询某人姓名、年龄及身份证号时就需要外连接，查询结果在MyBatis中是如何映射到对象中的呢？
 *  备注：未用MyBatis框架前，我们通常将多表查询的结果放在dto或vo包中的结果类里进行封装，有利有弊，代码多，而且碰到一对多的关系时会出现大量数据不可避免地重复封装。  
    **person表**  
    ![这里写图片描述][70]  
    **card表**  
    ![这里写图片描述][70 1]  
    **相应的两个实体类**

package com.yicha.entity;
    //Card.java
    public class Card {
        private int id;
        private String cardNum;
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getCardNum() {
            return cardNum;
        }
        public void setCardNum(String cardNum) {
            this.cardNum = cardNum;
        }
    
    }

package com.yicha.entity;
    //Person.java
    public class Person {
    
        private int id;
        private String name;
        private int age;
        private Card card;
    
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public int getAge() {
            return age;
        }
        public void setAge(int age) {
            this.age = age;
        }
        public Card getCard() {
            return card;
        }
        public void setCard(Card card) {
            this.card = card;
        }
    
    }

Person.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD SQL Map 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
    <mapper namespace="com.yicha.entity.Person">
        
        <resultMap type="Person" id="myMap">
            <result property="name" column="name"/>
            <result property="age" column="age"/>
            
            
            <association property="card" javaType="Card">
                <result property="cardNum" column="cardnum"/>
            </association>
        </resultMap>
        
        <select id="findPerson" resultMap="myMap">
            SELECT a.name,a.age,b.cardnum 
            FROM person a
            LEFT JOIN card b
            ON a.cardid=b.id
            WHERE a.id=1
        </select>
    </mapper>

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
    <configuration>
        
        <typeAliases>
            <typeAlias type="com.yicha.entity.Person" alias="Person"/>
            <typeAlias type="com.yicha.entity.Card" alias="Card"/>
        </typeAliases>
    
        
        <environments default="e1">
            <environment id="e1">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/test"/>
                    <property name="username" value="root"/>
                    <property name="password" value="root1205"/>
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
            <mapper resource="com/yicha/entity/Person.xml"/>
        </mappers>
    </configuration>

测试类

package com.yicha.test;
    //Demo1.java
    import java.io.IOException;
    import java.io.Reader;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    public class Demo1 {
    
        public static void main(String[] args) {
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = null;
            SqlSession session = null;
            try {
                Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
                factory = builder.build(reader);
                session = factory.openSession();
            } catch (IOException e) {
                e.printStackTrace();
            }
            session.selectOne("findPerson"); 
        }
    }

结果  
![这里写图片描述][70 2]

[70]: /images/20220512/f32af5e27a7a4110be45047b0c674861.png
[70 1]: /images/20220512/c5c4b7b66bc5453ab204231e29fea827.png
[70 2]: /images/20220512/b22432d516224024ab1cc16a8dff7032.png