Mybatis（四）多表操作

数据库如下：

一、创建数据库所对应的bean类

public class User {
    private Integer uId;
    private String username;
    private String sex;
    private String address;

    public Integer getuId() {
        return uId;
    }

    public void setuId(Integer uId) {
        this.uId = uId;
    }

    public String getUsername() {
        return username;
    }

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

    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{" +
                "uId=" + uId +
                ", username='" + username + '\'' +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

public class Order {

    private Integer oId;
    private Integer number;
    private String orderName;
    private Integer userId;

    private User user;

    public Integer getoId() {
        return oId;
    }

    public void setoId(Integer oId) {
        this.oId = oId;
    }

    public Integer getNumber() {
        return number;
    }

    public void setNumber(Integer number) {
        this.number = number;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public User getUser() {
        return user;
    }

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

    @Override
    public String toString() {
        return "Order{" +
                "oId=" + oId +
                ", number=" + number +
                ", orderName='" + orderName + '\'' +
                ", userId=" + userId +
                ", user=" + user +
                '}';
    }
}

二、核心配置文件sqlMapConfig.xml

　　（1）、jdbc.properties与sqlMapperConfig.xml文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/school?characterEncoding=utf-8
jdbc.username=root
jdbc.password=dyhroot

<?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 resource="jdbc.properties"/>

    <!--别名的应用，一定要特别注意标签是有顺序的，不然会报异常-->
    <typeAliases>
        <!--这样写就是该文件中所有的"dyh.bean.User" 都用User代替了-->
        <!--这样写有一个弊端，就是除了用户之外还有其他实体类，比如Order、Customer…… 那就要写n别名了，所以不推荐使用-->
        <!--<typeAlias type="dyh.bean.User" alias="User"/>-->

        <!--推荐使用包的形式-->
        <package name="dyh.bean"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <!-- 使用jdbc事务管理 -->
            <transactionManager type="JDBC" />
            <!-- 数据库连接池 -->
            <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的位置  Mapper.xml 写Sql语句的文件的位置 -->
    <mappers>
        <mapper resource="orderMapper/OrderMapper.xml"/>
        <!--使用mapper接口类路径
        如：<mapper class="cn.itcast.mybatis.mapper.UserMapper"/>
        注意：此种方法要求mapper接口名称和mapper映射文件名称相同，且放在同一个目录中。-->

        <!--<package name="userMapper"/>-->

        <!--注册指定包下的所有mapper接口
        如：<package name="cn.itcast.mybatis.mapper"/>
        注意：此种方法要求mapper接口名称和mapper映射文件名称相同，且放在同一个目录中。-->

        <!--注意：但是这两种引入sql映射文件的办法，用IDEA的maven工程，这样写有问题，还没找到原因，有可能是不可以这么书写-->
    </mappers>

</configuration>

三、代理接口OrderMapper与对应的映射文件OrderMapper.xml

public interface OrderMapper {
    //一对一关联查询，以订单为中心关联用户
    public List<Order> selectOrders();
}

<?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="dyh.mapper.OrderMapper">

    <!--一对一关联查询，以订单为中心关联用户
    public List<Order> selectOrders();-->

    <resultMap id="order" type="Order">
        <!--orders表操作，数据库字段user_id 与 Order类中的属性userId 不一致,其他字段不可省略-->
        <result property="userId" column="user_id"/>
        <result property="orderName" column="orderName"/>
        <result property="oId" column="oId"/>
        <result property="number" column="number"/>

        <association property="user" javaType="User">
            <id property="uId" column="uId"/>
            <result property="username" column="username"/>
            <result property="sex" column="sex"/>
            <result property="address" column="address"/>
        </association>

    </resultMap>

    <select id="selectOrders" resultMap="order">
      SELECT
      o.orderName,
      o.number,
      o.user_id,
      o.oId,
      u.uId,
      u.username,
      u.sex,
      u.address
      FROM
      orders o
      LEFT JOIN
      users u
      ON
      o.user_id = u.uId;
    </select>
</mapper>

四、测试

package dyh.test;

import dyh.bean.Order;
import dyh.mapper.OrderMapper;
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.InputStream;
import java.util.List;

public class MybatisTest {

    @Test
    public void testMapper() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //SqlSEssion帮我生成一个实现类  （给接口）
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

        List<Order> orders = orderMapper.selectOrders();
        for (Order order : orders) {
            System.out.println(order);
        }
    }
}

测试结果：

 

 

其他：

　　一对多关联（步骤同上）

public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    private Integer id;
    private String username;
    private String sex;
    private Date birthday;
    private String address;

//一个用户对应多张订单
    private List<Orders> ordersList;

 

     //一对多关联
    public List<User> selectUserList(); -->
    <resultMap type="User" id="user">
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <!-- 一对多 -->
        <collection property="ordersList" ofType="Orders">
            <id column="id" property="id"/>
            <result column="number" property="number"/>
        </collection>
    </resultMap>
    <select id="selectUserList" resultMap="user">
        SELECT 
         o.id,
        o.user_id, 
        o.number,
         o.createtime,
         u.username 
         FROM user u
         left join orders o 
         on o.user_id = u.id
    </select>