MyBatis关联查询案例

目录

1、AccountMapper接口

2、AccountMapper.xml文件代码

3、pojo代码

        3.1 Account类

        3.2 User类

4、mybatis-config.xml代码

5、测试代码

1、AccountMapper接口

package cn.aix.mapper;

import cn.aix.pojo.Account;

public interface AccountMapper {
    Account getAccountById(Integer id);
}

2、AccountMapper.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="cn.aix.mapper.AccountMapper">
    <resultMap id="getAccountByIdResultMap" type="account">
        <id column="aid" property="id"></id>
        <result column="uid" property="uid"></result>
        <result column="money" property="money"></result>
        <!--
            一对一查询使用association标签
            property="user"：Account的属性名
            javaType="user"：等价于resultType
        -->
        <association property="user" javaType="user">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="birthday" property="birthday"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
    <select id="getAccountById" parameterType="int" resultMap="getAccountByIdResultMap">
        SELECT a.id aid, a.money money, a.uid uid, u.* FROM account a LEFT JOIN USER u ON a.uid = u.id WHERE a.id=1
    </select>
</mapper>

3、pojo代码

        3.1 Account类

package cn.aix.pojo;

import java.io.Serializable;

//一个
public class Account implements Serializable {

    private Integer id;
    private Integer uid;
    private Double money;
    //一个
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

        3.2 User类

package cn.aix.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public void setId(Integer id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

4、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"></properties>
    <typeAliases>
        <!--<typeAlias type="com.by.pojo.User" alias="User"></typeAlias>-->
        <!--批量给pojo定义别名，推荐使用小写-->
        <package name="cn.aix.pojo"/>
    </typeAliases>
    <!--使用dev环境-->
    <environments default="dev">
        <!--dev环境-->
        <environment id="dev">
            <!--事务-->
            <transactionManager type="JDBC"></transactionManager>
            <!--使用连接池中的数据源-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 加载mapper映射文件 -->
    <mappers>
        <!--直接引入映射文件-->
        <!--<mapper resource="com/by/mapper/UserMapper.xml"></mapper>-->
        <!--按mapper接口的名称引入映射文件，要求 mapper 接口名称和 mapper 映射文件名称相同-->
        <!--<mapper class="com.by.mapper.UserMapper"></mapper>-->
        <!--批量按mapper接口的名称引入映射文件-->
        <package name="cn.aix.mapper"/>
    </mappers>
</configuration>

5、测试代码

package cn.aix.test;

import cn.aix.mapper.AccountMapper;
import cn.aix.mapper.UserMapper;
import cn.aix.pojo.Account;
import cn.aix.pojo.User;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MyBatisTest {
    InputStream inputStream;
    SqlSession sqlSession;

    /**
     * 测试方法前先执行该方法
     *
     * @throws IOException
     */
    @Before
    public void init() throws IOException {
        //加载配置文件
        String resource = "mybatis-config.xml";
        inputStream = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //获得数据的会话实例
        sqlSession = sessionFactory.openSession();
    }

    @Test
    public void testGetAccountById() throws IOException {
        AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class);
        Account account = accountMapper.getAccountById(1);
        System.out.println(account);
    }

    @After
    public void close() throws IOException {
        sqlSession.close();
        inputStream.close();
    }
}