MyBatis之手动映射之一对多关系

案例背景:

数据库表中一个用户对应着多个订单信息

根据一个用户ID只可以查询出多条订单记录

我们需要查询出指定的用户信息并且信息中包含有对应的订单记录

补充:

同以往User的定义不同,User中需要封装List<Order>属性

一.案例展示

1.测试准备

①数据库mysql一枚

②database名为batistest且表名为user和order

③项目工程

2.代码展示(着重关注OrderMapper.xml的配置)

***pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.howie</groupId>
    <artifactId>mybatis_manual_mapping_one2More</artifactId>
    <version>1.0-SNAPSHOT</version>
    
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    <dependencies>
        <!--单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <!--log4j-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

</project>

***bean.properties

UserService=com.howie.service.impl.UserServiceImpl

***db.properties

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

***log4j.properties

#log4j日志级别如下：
#A：off     最高等级，用于关闭所有日志记录。
#B：fatal   指出每个严重的错误事件将会导致应用程序的退出。
#C：error   指出虽然发生错误事件，但仍然不影响系统的继续运行。
#D：warn    表明会出现潜在的错误情形。
#E：info    一般和在粗粒度级别上，强调应用程序的运行全程。
#F：debug   一般用于细粒度级别上，对调试应用程序非常有帮助。
#G：all     最低等级，用于打开所有日志记录。
#但log4j只建议使用4个级别，优先级从高到低分别是:
#error>warn>info>debug
log4j.rootLogger=debug,systemOut,logFile
#输出到控制台
log4j.appender.systemOut=org.apache.log4j.ConsoleAppender
log4j.appender.systemOut.layout=org.apache.log4j.PatternLayout
log4j.appender.systemOut.layout.ConversionPattern=[%-5p][%-22d{yyyy/MM/dd HH:mm:ssS}][%l]%n%m%n
log4j.appender.systemOut.Target=System.out
#输出到文件
log4j.appender.logFile=org.apache.log4j.FileAppender
log4j.appender.logFile.layout=org.apache.log4j.PatternLayout
log4j.appender.logFile.layout.ConversionPattern=[%-5p][%-22d{yyyy/MM/dd HH:mm:ssS}][%l]%n%m%n
log4j.appender.logFile.File=E:/log/log4j.log
log4j.appender.logFile.Encoding=UTF-8
#将日志输记录到MySQL数据库
#log4j.appender.logDB = org.apache.log4j.jdbc.JDBCAppender
#log4j.appender.logDB.layout = org.apache.log4j.PatternLayout
#log4j.appender.logDB.Driver = com.mysql.jdbc.Driver
#log4j.appender.logDB.URL = jdbc:mysql://localhost:3306/log4j?characterEncoding=utf-8
#log4j.appender.logDB.User = root
#log4j.appender.logDB.Password = root
#log4j.appender.logDB.Sql = INSERT INTO t_log4j(project_name,create_date,level,category,file_name,thread_name,line,all_category,message)values('mybatis','%d{yyyy-MM-ddHH:mm:ss}','%p','%c','%F','%t','%L','%l','%m')
#若出现问题：Log4j: ERROR Failed to load driver, java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
#解决方法：将log4j.properties 中每行后面的空格都删除就解决了.

***SqlMapConfig.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>

    <!--
        从外部文件db.properties读取数据库连接信息
    -->
    <properties resource="db.properties"/>

    <!--配置log4j日志-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <!--批量起别名-->
    <typeAliases>
        <package name="com.howie.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--获取db文件中的数据-->
                <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="XxxMapper.xml"/>-->
        <package name="com.howie.mapper"/>
    </mappers>
</configuration>

***Order.java

package com.howie.pojo;

import java.util.Date;

/**
 * @Author weihuanwen
 * @Date 2019/8/12 11:23
 * @Version 1.0
 */
public class Order {
    private int id;
    private int userId;
    private String number;
    private Date createtime;
    private String note;

    public int getId() {
        return id;
    }

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

    public int getUserId() {
        return userId;
    }

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

    public String getNumber() {
        return number;
    }

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

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", userId=" + userId +
                ", number='" + number + '\'' +
                ", createtime=" + createtime +
                ", note='" + note + '\'' +
                '}';
    }
}

***User.java

package com.howie.pojo;

import java.util.Date;
import java.util.List;

/**
 * @Author weihuanwen
 * @Date 2019/8/9 9:54
 * @Version 1.0
 */
public class User {

    private Integer id;
    private String username;
    private Integer sex;
    private Date birthday;
    private String address;
    private List<Order> orderList;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public Integer getSex() {
        return sex;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getAddress() {
        return address;
    }

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

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

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

***BeanFactory.java

package com.howie.utils;


import org.apache.ibatis.session.SqlSession;

import java.lang.reflect.Constructor;
import java.util.ResourceBundle;

/**
 *  Bean工厂对象
 *  方法:
 *    传递接口,传递的是接口的class文件对象
 *    返回实现类的对象
 *
 *    泛型: 本身没有类型,未知的数据类型
 *    调用的时候,传递什么,就是什么类型
 */
public class BeanFactory {
    public static <T>T newInstance(Class<T> clazz){
        //获取传递的接口名
        String name = clazz.getSimpleName();
        //读取配置文件
        ResourceBundle resourceBundle = ResourceBundle.getBundle("bean");
        String className = resourceBundle.getString(name);
        //反射创建对象
        try {
            return (T)Class.forName(className).newInstance();
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return null;
    }

    /**
     *  带参构造
     * @param clazz
     * @param sqlSession
     * @param <T>
     * @return
     */
    public static <T>T newInstance(Class<T> clazz, SqlSession sqlSession){
        //获取传递的接口名
        String name = clazz.getSimpleName();
        //读取配置文件
        ResourceBundle resourceBundle = ResourceBundle.getBundle("bean");
        String className = resourceBundle.getString(name);
        //获取带参构造函数
        try {
            Constructor constructor = Class.forName(className).getConstructor(SqlSession.class);
            Object[] params = {sqlSession};
            //反射创建对象
            return (T)constructor.newInstance(params);
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return null;
    }
}

***UserMapper.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="com.howie.mapper.UserMapper">
    <!--是否开启二级缓存(开启,则打开注释)-->
    <!--<cache/>-->
    <!--多条件查询用户信息-->
    <select id="findUserByCondition" resultMap="userResultMap" parameterType="user">
        SELECT
            u.`id`,
            u.`username`,
            u.`birthday`,
            u.`address`,
            o.`id` oid,
            o.`number`,
            o.`note`
        FROM
          `user` u
        LEFT JOIN `order` o ON u.`id` = o.`user_id`
      <where>
          <if test="id!=null and id!=''">
              AND id=#{id}
          </if>
          <if test="username!=null and username!=''">
              AND username=#{username}
          </if>
          <if test="sex!=null and sex!=''">
              AND sex=#{sex}
          </if>
      </where>
    </select>

   <resultMap id="userResultMap" type="user">
       <!--封装基本属性-->
       <id property="id" column="id"/>
       <result property="username" column="username"/>
       <result property="birthday" column="birthday"/>
       <result property="address" column="address"/>
       <!--
       封装order列表数据
       ofType:集合的泛型
       javaType:对象
       -->
       <collection property="orderList" ofType="Order">
           <id property="id" column="oid"/>
           <result property="number" column="number"/>
           <result property="note" column="note"/>
       </collection>
   </resultMap>
</mapper>

***UserMapper.java

package com.howie.mapper;

import com.howie.pojo.User;

import java.util.List;

/**
 * @Author weihuanwen
 * @Date 2019/8/9 10:03
 * @Version 1.0
 */
public interface UserMapper {

    /**
     * 多条件查询用户信息
     * @param user
     * @return
     */
    List<User> findUserByCondition(User user);
}

***UserService.java

package com.howie.service;

import com.howie.pojo.User;

import java.util.List;

/**
 * @Author weihuanwen
 * @Date 2019/8/9 10:56
 * @Version 1.0
 */
public interface UserService {

    /**
     * 多条件查询用户信息
     * @param user
     * @return
     */
    List<User> findUserByCondition(User user);
}

***UserServiceImpl.java

package com.howie.service.impl;

import com.howie.mapper.UserMapper;
import com.howie.pojo.User;
import com.howie.service.UserService;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @Author weihuanwen
 * @Date 2019/8/9 10:56
 * @Version 1.0
 */
public class UserServiceImpl implements UserService {

    private static SqlSession sqlSession;
    private static UserMapper userMapperImpl;

    static {
        try {
            SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
            //加载SqlMapConfig.xml文件
            InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
            SqlSessionFactory ssf = ssfb.build(is);
            sqlSession = ssf.openSession();
            userMapperImpl = sqlSession.getMapper(UserMapper.class);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 多条件查询用户信息
     *
     * @param user
     * @return
     */
    @Override
    public List<User> findUserByCondition(User user) {
        return userMapperImpl.findUserByCondition(user);
    }
}

***UserController.java

package com.howie.controller;

import com.howie.pojo.User;
import com.howie.service.UserService;
import com.howie.utils.BeanFactory;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

/**
 * @Author weihuanwen
 * @Date 2019/8/13 12:25
 * @Version 1.0
 */
public class UserController {
    private UserService userService = BeanFactory.newInstance(UserService.class);

    /**
     * 多条件查询用户信息
     */
    @Test
    public void findUserByCondition(){
        User user4Search = new User();
        user4Search.setUsername("王五");
        List<User> userList = userService.findUserByCondition(user4Search);
        System.out.println(userList);
    }
}

3.测试

执行findUserByCondition()控制台输出

[User{id=3, username='王五', sex=null, birthday=Tue Jul 10 00:00:00 CST 2018, address='广州', orderList=[Order{id=1, userId=0, number='1000010', createtime=null, note='质量很好'}, Order{id=2, userId=0, number='1000011', createtime=null, note='非常漂亮'}]}]