Mybatis实现多表查询

建库建表

create table user_info
(
  u_id int primary key auto_increment,
  u_name varchar(20) not null
)
  select * from user_info;

# 插入几个测试用户
insert into user_info(u_name) values('张三'),('李四'),('王五'),('赵六'),('田七');

delete from user_info where U_id in(6,7,8,9,10);

  create table idCard_info
  (
    i_id int primary key  auto_increment,
    i_num varchar(20) not null,
    u_id int references user_info(u_id)
  );
# 为每个用户添加他的身份证
insert into idCard_info(i_num,u_id) values('360782199907165226',1),
                                          ('360782200003173305',2),
                                          ('360782200104136671',3),
                                          ('360782200205129987',4),
                                          ('360782200308110989',5);
select * from idCard_info;

#   间接关联地址
  create table address_info
  (
    a_id int primary key auto_increment,
    a_address  varchar(50) not null
  );
# 添加地址
insert into address_info(a_address) values('江西赣州'),
                                          ('江西兴国'),
                                          ('江西宜春'),
                                          ('江西抚州'),
                                          ('江西吉安'),
                                          ('江西九江'),
                                          ('江西庐山'),
                                          ('江西龙虎山'),
                                          ('江西共青城'),
                                          ('江西乐平'),
                                          ('江西安福'),
                                          ('江西井冈山'),
                                          ('江西萍乡'),
                                          ('江西泰和'),
                                          ('江西遂川'),
                                          ('江西瑞金'),
                                          ('江西南昌');
select * from address_info;

create table address_user
(
  id int primary key  auto_increment,
  u_id int not null,
  a_id int not null
);

# 关联用户的地址
insert into address_user(u_id,a_id) values(1,1),
                                          (1,2),
                                          (2,3),
                                          (2,4),
                                          (3,5),
                                          (3,6),
                                          (4,8),
                                          (4,9),
                                          (5,10),
                                          (5,11);


  create table tel_info
  (
    t_id int primary key auto_increment,
    t_num varchar(20) not null,
    u_id int references user_info(u_id)
  );

#   给用户添加电话号码
insert into tel_info(t_num,u_id) values('17770711761',1),
                                       ('18879711655',1),
                                       ('17679077987',2),
                                       ('15679062664',2),
                                       ('13209898909',3),
                                       ('15170789129',3),
                                       ('18179876789',4),
                                       ('13478655678',4),
                                       ('15598099098',5),
                                       ('15456789876',5);

select * from tel_info;
select * from address_user;

select ai.a_address,u.u_name,ti.t_num,ici.i_num from address_user au join address_info ai on au.a_id = ai.a_id join user_info u on au.u_id = u.u_id join tel_info ti on u.u_id = ti.u_id join idCard_info iCi on u.u_id = iCi.u_id where u.u_id = 1;

select * from address_user au join address_info ai on au.a_id = ai.a_id join user_info u on au.u_id = u.u_id join tel_info ti on u.u_id = ti.u_id join idCard_info iCi on u.u_id = iCi.u_id;

select ai.a_address,u.u_name,ti.t_num,ici.i_num from address_user au join address_info ai on au.a_id = ai.a_id join user_info u on au.u_id = u.u_id join tel_info ti on u.u_id = ti.u_id join idCard_info iCi on u.u_id = iCi.u_id where iCi.i_num = 360782199907165226;

 

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>
    <!-- typeAliases用于给类型指定别名 -->
    <typeAliases>
        <!-- 方式一：使用typeAlias设置别名
        type属性指定的是类的完整类名，alias属性指定的是别名 -->
        <!--<typeAlias type="edu.nf.ch01.entity.Users" alias="Users"/>-->
        <!-- 方式二：直接给整个实体包定义别名,name属性指定实体所在的包名
        ，这样会为每一个实体类自动生成一个别名，
        而这个别名就是实体类的类名并且首字母小写-->
　　　　　<!-- package name="指定完整包名下所有的实体类" -->

        <package name="edu.wang.studentsystem.entity"/>
    </typeAliases>

    <!-- environments用于配置数据源环境，里面可以配置多个。
         environment环境。
         每个数据源环境都包含在environment子标签中，
         default属性指定使用默认的数据源环境是哪个，
         对应environment标签的id的值-->
    <environments default="mysql">
        <!-- 配置一个mysql的数据源环境,id是自定义的 -->
        <environment id="mysql">
            <!-- 指定事务管理器，这里使用JDBC的本地事务 -->
            <transactionManager type="JDBC"/>
            <!-- 配置数据源,type指定使用mybatis自带的数据库连接池 -->
            <dataSource type="POOLED">
                <!-- 配置数据库的连接属性 -->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/users?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 将所有的mapper映射配置文件加入到mappers配置中 -->
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
        <mapper resource="mapper/IdCardMapper.xml" />
    </mappers>
</configuration>

UserMapper.xml文件配置SQL语句

<?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指定Dao接口的完整类名
    mybatis会依据这个接口动态创建一个实现类去实现这个接口，
    而这个实现类是一个Mapper对象-->
<mapper namespace="edu.wang.studentsystem.dao.UserDao">
    <!-- 基于mybatis的增删查改的sql映射配置 -->
    <!-- 插入操作,id指定UserDao接口中的方法名,
    parameterType指定方法参数的类型，
    也可以设置为typeAliases中定义的别名。
    useGeneratedKeys属性设置为true表示使用主键自增长,
     keyProperty属性指定实体中需要自增长的字段名称

      sql中的values设置参数的时候使用#{}，
     #{}是一个ognl表达式(对象图导航语言),
     这个表达式最终会被mybatis解析成具体的值，
     在这里#{}中指定的是实体中的字段名-->
    <resultMap id="UserMap" type="Users">
        <!-- 映射主键 -->
        <id property="id" column="u_id" />
        <!-- 映射名字 -->
        <result property="name" column="u_name" />
        <!-- 一对一或者多对一使用 association-->
        <association property="card" resultMap="IdCardMap"/>
        <!-- 一对多或多对多使用collection -->
        <!-- 映射电话号码 -->
        <collection property="tels" resultMap="TelMap"/>
        <!-- 映射地址 -->
        <collection property="addresses" resultMap="AddressMap"/>
    </resultMap>
    <!-- 使用resultMap映射IdCard对象 -->
    <resultMap id="IdCardMap" type="IdCard">
        <id property="id" column="i_id" />
        <result property="idCard" column="i_num"/>
    </resultMap>
    <!-- 使用resultMap映射Address对象 -->
    <resultMap id="AddressMap" type="Address">
        <id property="id" column="a_id" />
        <result property="address" column="a_address"/>
    </resultMap>
    <!-- 使用resultMap映射Tel对象 -->
    <resultMap id="TelMap" type="Tel">
        <id property="id" column="t_id" />
        <result property="number" column="t_num"/>
    </resultMap>
    <!-- 根据id查询用户信息 -->
    <select id="findUserById" parameterType="int" resultMap="UserMap">
select ai.a_address,u.u_name,ti.t_num,ici.i_num from
address_user au join address_info ai on au.a_id = ai.a_id
join user_info u on au.u_id = u.u_id join tel_info ti on
 u.u_id = ti.u_id join idCard_info iCi on u.u_id = iCi.u_id
where u.u_id = #{id}
    </select>

    <!-- 查询所有用户 -->
    <select id="findAllUser" resultMap="UserMap">
        select ai.a_address,u.u_name,ti.t_num,ici.i_num from
        address_user au join address_info ai on au.a_id = ai.a_id
        join user_info u on au.u_id = u.u_id join tel_info ti on
        u.u_id = ti.u_id join idCard_info iCi on u.u_id = iCi.u_id
    </select>


</mapper>

entity实体类对象

package edu.wang.studentsystem.entity;

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

/**
 * @Author Waverly
 * @Date 2018/9/13
 */
public class Users {
    /**
     *  用户id
     */
    private int id;
    /**
     * 用户姓名
     */
    private String name;
    /**
     *  用户身份证号
     */
    private IdCard card;
    /**
     * 用户手机
     */
    private List<Tel> tels = new ArrayList<>();
    /**
     * 用户地址
     */
    private List<Address> addresses = new ArrayList<>();

    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 IdCard getCard() {
        return card;
    }

    public void setCard(IdCard card) {
        this.card = card;
    }

    public List<Tel> getTels() {
        return tels;
    }

    @Override
    public String toString() {
        String addres = "";
        for(Address addresses : addresses){
            addres = addresses.getAddress();

        }
        String number = "";
        for (Tel tel :tels) {
            number = tel.getNumber();
        }

        return "Users{" +
                "  name='" + name + '\'' +
                ", card=" + card.getIdCard()+
                ", tels=" + number +
                ", addresses=" +addres;
    }

    public void setTels(List<Tel> tels) {
        this.tels = tels;
    }

    public List<Address> getAddresses() {
        return addresses;
    }

    public void setAddresses(List<Address> addresses) {
        this.addresses = addresses;
    }
}

package edu.wang.studentsystem.entity;

/**
 * @Author Waverly
 * @Date 2018/9/13
 */
public class IdCard {
    /**
     * 用户Id
     */
    private int id;
    /**
     * 用户Id
     */
    private String idCard;
    /**
     * 关联用户
     */
    private Users users;

    public int getId() {
        return id;
    }

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

    public String getIdCard() {
        return idCard;
    }

    public void setIdCard(String idCard) {
        this.idCard = idCard;
    }

    public Users getUsers() {
        return users;
    }

    public void setUsers(Users users) {
        this.users = users;
    }
}

package edu.wang.studentsystem.entity;

/**
 * @Author Waverly
 * @Date 2018/9/13
 */
public class Tel {
    /**
     * 号码id
     */
    private int Id;
    /**
     *
     */
    private String number;

    public int getId() {
        return Id;
    }

    public void setId(int id) {
        Id = id;
    }


    public String getNumber() {
        return number;
    }

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

获取sqlSession会话的工具类

package edu.wang.studentsystem.commons;

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.InputStream;

/**
 * @Author Waverly
 * @Date 2018/9/13
 */
public class MybatisUtil {
    /**
     * 创建sql工厂对象
     */
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try{
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        }catch (Exception e){
            throw  new RuntimeException(e.getMessage());
        }
    }
    public static SqlSession getSession(){
        return sqlSessionFactory.openSession();
    }
    public static SqlSession getSession(boolean autoCommit){
        return sqlSessionFactory.openSession(autoCommit);
    }
}

定义接口

package edu.wang.studentsystem.dao;

import edu.wang.studentsystem.entity.Users;

import java.util.List;

public interface UserDao {
    /**
     * 根据id查询用户信息
     */
    List<Users> findUserById(int id);
    /**
     * 查询所有用户信息
     */
    List<Users> findAllUser();
}

实现接口

package edu.wang.studentsystem.dao.impl;

import edu.wang.studentsystem.commons.MybatisUtil;
import edu.wang.studentsystem.dao.UserDao;
import edu.wang.studentsystem.entity.Users;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

/**
 * @Author Waverly
 * @Date 2018/9/13
 */
public class UserDaoImpl implements UserDao {

    @Override
    public List<Users> findUserById(int id) {
        List<Users> usersList = null;
        SqlSession sqlSession = MybatisUtil.getSession();
        try{
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            usersList = userDao.findUserById(id);
        }finally {
            sqlSession.close();
        }
        return usersList;
    }

    @Override
    public List<Users> findAllUser() {
        SqlSession sqlSession = MybatisUtil.getSession();
        List<Users> usersList = null;
        try{
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            usersList = userDao.findAllUser();
        }finally {
            sqlSession.close();
        }
        return usersList;

    }
}

测试接口

package edu.wang.studentsystem;

import edu.wang.studentsystem.dao.UserDao;
import edu.wang.studentsystem.dao.impl.UserDaoImpl;
import edu.wang.studentsystem.entity.Users;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

/**
 * @Author Waverly
 * @Date 2018/9/13
 */
public class UserDaoTest {
    private static UserDao userDao;
    @Before
    public void before(){
        userDao = new UserDaoImpl();
    }

    @Test
    public void testFindUserById(){
        List<Users> usersList = userDao.findUserById(1);
        for (Users users : usersList) {
            System.out.println(users);
        }
    }
    @Test
    public void testFindAllUser(){
        List<Users> usersList = userDao.findAllUser();
        for (Users users : usersList) {
            System.out.println(users);
        }
    }
}