【Mybatis】多表操作-一对一的实现

首先看一下数据库的表

user表和orders表

然后我想通过以下语句查询两个表的联系，得到另一张表

SELECT *, o.id oid FROM orders o, USER u WHERE o.uid=u.id;

 但是如何使用Mybatis配置实现呢？

首先创建两个类分别是User 和 Order

package com.znan.domain;

import java.util.Date;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;

    public Date getBirthday() {
        return birthday;
    }

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

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

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

package com.znan.domain;

import javax.sql.DataSource;
import java.util.Date;

public class Order {
    private int id;
    private Date ordertime;
    private int total;
    //表示当前订单属于哪一个用户
    private User user;

    public int getId() {
        return id;
    }

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

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }

    public int getTotal() {
        return total;
    }

    public void setTotal(int total) {
        this.total = total;
    }

    public User getUser() {
        return user;
    }

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

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", total=" + total +
                ", user=" + user +
                '}';
    }
}

并创建对应的Mapper接口

package com.znan.dao;

import com.znan.domain.Order;

import java.util.List;

public interface OrderMapper {
    public List<Order> findAll();
}

然后创建OrderMapper.xml配置文件，注意不是resultType而是resultMap，然后定义一个orderMap，并一一将键值对应。使用两种方式都可以（加注释）。

<?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="com.znan.dao.OrderMapper">
    <resultMap id="orderMap" type="order">
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
<!--        <result column="uid" property="user.id"></result>-->
<!--        <result column="username" property="user.username"></result>-->
<!--        <result column="password" property="user.password"></result>-->
<!--        <result column="birthday" property="user.birthday"></result>-->

        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <id column="username" property="username"></id>
            <id column="password" property="password"></id>
            <id column="birthday" property="birthday"></id>
        </association>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        SELECT *, o.id oid FROM orders o, USER u WHERE o.uid=u.id;
    </select>
</mapper>

之后就是在核心配置文件中注册OrderMapper.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标签加载外部properties文件-->
    <properties resource="jdbc.properties"></properties>


    <!--    定义别名-->
    <typeAliases>
        <typeAlias type="com.znan.domain.User" alias="user"></typeAlias>
        <typeAlias type="com.znan.domain.Order" alias="order"></typeAlias>
    </typeAliases>
    
<!--    注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.znan.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>

<!--    配置分页助手插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>
<!--    数据源的环境-->
    <environments default="development">
        <environment id="development">
            <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>

<!--    加载映射文件-->
    <mappers>
        <mapper resource="cn/eva/mapper/UserMapper.xml"></mapper>
        <mapper resource="cn/eva/mapper/OrderMapper.xml"></mapper>
    </mappers>

</configuration>

测试

package com.znan;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.znan.dao.OrderMapper;
import com.znan.dao.UserMapper;
import com.znan.domain.Order;
import com.znan.domain.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.Test;

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

public class MapperTest {
    @Test
    public void Test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }

}