Java框架学习——Mybatis实现多表查询

一.创建数据库文件使用Mysql数据库

1创建user表

/*
SQLyog Enterprise - MySQL GUI v8.14 
MySQL - 5.5.15 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `user` (
	`id` double ,
	`username` varchar (39),
	`birthday` date ,
	`sex` varchar (3),
	`address` varchar (75)
); 
insert into `user` (`id`, `username`, `birthday`, `sex`, `address`) values('1','tom','2020-12-12','M','beijing ');
insert into `user` (`id`, `username`, `birthday`, `sex`, `address`) values('2','user','2020-01-01','F','lanzhou');
insert into `user` (`id`, `username`, `birthday`, `sex`, `address`) values('3','user','1996-09-22','M','shanghai');
insert into `user` (`id`, `username`, `birthday`, `sex`, `address`) values('4','jack','2001-11-01','M','zhonguo');

2创建account表

/*
SQLyog Enterprise - MySQL GUI v8.14 
MySQL - 5.5.15 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `account` (
	`id` double ,
	`uid` double ,
	`money` double 
); 
insert into `account` (`id`, `uid`, `money`) values('1','1','1000');
insert into `account` (`id`, `uid`, `money`) values('2','3','1000');

注意：两表之间关系为一个用户可以有多个账户，一个账户对应一个用户，实现多表查询时用户可以查询到账户信息，也可以是账户查到用户信息。

二.创建java项目

创建好的项目文件结构如下图：

1.建立User.java和Account.java

①User.java

package com.lut.domain;

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

public class User implements Serializable {
    private  Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //一对多关系映射，主表实体应该包含从表实体的集合引用
    private List<Account> accounts;
    public List<Account> getAccounts() {
        return accounts;
    }
    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }
    public User() {
    }
    public User(Integer id, String username, Date birthday, String sex, String address) {
        this.id = id;
        this.username = username;
        this.birthday = birthday;
        this.sex = sex;
        this.address = address;
    }
    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 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 + '\'' +
                '}';
    }
}

②Account.java

package com.lut.domain;
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;
    }
    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
    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;
    }
}

2.pom.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.lut</groupId>
    <artifactId>day04</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

②jdbcConfig.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
jdbc.username=root
jdbc.password=123456

③sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.ory//DTD Config 3.0//EN"
        "htpp://mybatis.org/dtd/mybatis-3-config.dtd">
<!--mybatis的主配置文件-->
<configuration>
    
    <properties resource="jdbcConfig.properties"></properties>
    <!--配置参数-->
    <settings>
        <!--开启mybatis支持延迟加载-->
    <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
        <!--支持二级缓存-->
        <setting name="cacheEnabled" value="true"/>
    </settings>
    <!--使用typeAliases配置别名，它只能配置domain中类的别名-->
    <typeAliases>
        <!--用于指定要配置的别名的包，当指定之后，该包下的实体类都会注册别名，并且类名就是别名，不再区分大小写-->
        <package name="com.lut.domain"></package>
    </typeAliases>
    <!--配置环境-->
    <environments default="mysql">
        <!--配置mysql的环境-->
        <environment id="mysql">
            <!--配置事务的类型-->
            <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>

    <!--指定映射配置文件的位置，映射配置文件指的是每个dao独立的配置文件-->
    <mappers>
        <!--package标签用户指定dao接口所在的包，当指定完成之后，就不需要再写mapper以及resource或者class了-->
        <package name="com.lut.dao"></package>
    </mappers>
</configuration>

3.编写dao文件

①UserDao

public interface UserDao {
    //查询所有用户，同时获取到用户下所有账户的信息
    List<User> findAll();
//通过用户id查询
    User findById(Integer userId);
}

②IAccount

public interface IAccountDao {
    //查询所有账户，同时获取当前账户的所属用户信息
    List<Account> findAll();
    //根据用户id查询账户信息
    List<Account> findAccountByUid(Integer uid);
}

4.添加dao的映射文件

①UserDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.ory//DTD Mapper 3.0//EN"
        "htpp://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lut.dao.UserDao">
<!--开启user支持二级缓存-->
    <cache/>
    <!--定义user的resultMap-->
    <resultMap id="userAccountMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="address" column="address"></result>
        <result property="sex" column="sex"></result>
        <result property="birthday" column="birthday"></result>
        <!--配置user对象中accounts集合的映射-->
        <collection property="accounts" ofType="account" select="com.lut.dao.IAccountDao.findAccountByUid" 
                    column="id">
        </collection>
    </resultMap>
    <!--查询所有-->
    <select id="findAll" resultMap="userAccountMap" useCache="true"><!--userCache二级缓存支持-->
        select * from user
    </select>
    <!--根据id查询用户-->
    <select id="findById" parameterType="int" resultType="user">
        select * from user where id=#{uid}
    </select>
</mapper>

②IAccountDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.ory//DTD Mapper 3.0//EN"
        "htpp://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lut.dao.IAccountDao">

    <!--定义封装account和user的resultMap-->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!--一对一的关系映射 select属性指定的内容：查询用户的唯一标识
        column属性指定的内容：用户根据id查询时，所需要的参数的值-->
        <association property="user" column="id" javaType="user" select="com.lut.dao.UserDao.findById">
        </association>
    </resultMap>
    <!--查询所有-->
    <select id="findAll" resultMap="accountUserMap">
        select * from account
    </select>
    <!--根据用户id查询用户列表-->
    <select id="findAccountByUid" parameterType="int" resultType="account">
        select * from account where uid=#{uid}
    </select>

</mapper>

三.测试编写用于项目功能的测试

①UserTest

package com.lut.test;

import com.lut.dao.UserDao;
import com.lut.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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;

public class UserTest {
    private InputStream in;
    private SqlSession sqlSession;
    private UserDao userDao;
    @Before//用于测试方法之前执行
    public void init() throws Exception{
        //读取配置文件
        in= Resources.getResourceAsStream("sqlMapConfig.xml");
        //创建sqlSessionFactory工厂
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(in);
        //使用工厂生产sqlSession对象
        sqlSession=factory.openSession();
        //使用sqlSession对象创建Dao接口的代理对象
        userDao=sqlSession.getMapper(UserDao.class);
    }
    @After//用于在测试方法执行之后执行
    public void destroy() throws Exception{
        //释放资源
        sqlSession.close();
        in.close();
    }
    //测试查询所有
    @Test
    public void testFindAll(){
        List<User> users=userDao.findAll();
        for(User user:users){
            System.out.println(user);
            System.out.println(user.getAccounts());
        }
    }

}

②AccountTest

package com.lut.test;

import com.lut.dao.IAccountDao;
import com.lut.domain.Account;
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.InputStream;
import java.util.List;

public class AccountTest {
    private InputStream in;
    private SqlSession sqlSession;
    private IAccountDao accountDao;
    @Before//用于测试方法之前执行
    public void init() throws Exception{
        //读取配置文件
        in= Resources.getResourceAsStream("sqlMapConfig.xml");
        //创建sqlSessionFactory工厂
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(in);
        //使用工厂生产sqlSession对象
        sqlSession=factory.openSession();
        //使用sqlSession对象创建Dao接口的代理对象
        accountDao=sqlSession.getMapper(IAccountDao.class);
    }
    @After//用于在测试方法执行之后执行
    public void destroy() throws Exception{
        //释放资源
        sqlSession.close();
        in.close();
    }
    //测试查询所有
    @Test
    public void testFindAll(){
        List<Account> accounts=accountDao.findAll();
        for(Account account:accounts){
            System.out.println(account);
            System.out.println(account.getUser());
        }
    }

}

到此利用Mybatis实现多表查询的功能就实现了。