《Mybatis实现数据库多表查询》——————多对一

1.准备工作

1.1数据库准备

在数据库中建立员工表（t_emp）和部门表(t_dept)

 

Idea准备

项目目录结构

1.2.1pom.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.ujiuye</groupId>
    <artifactId>mybatis02</artifactId>
    <version>1.0.0</version>
    <dependencies>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>


        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.25</version>
            <scope>test</scope>
        </dependency>
    </dependencies>


</project>

1.2.2资源文件 

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///db3
username=root
password=1234

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

    <typeAliases>
        <package name="com.ujiuye.bean"/>
    </typeAliases>

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

   <mappers>
       <mapper resource="com/ujiuye/mapper/EmployeeMapper.xml"/>
   </mappers>
</configuration>

 EmployeeMapper.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.ujiuye.mapper.EmployeeMapper">

    <resultMap id="employeeResultMap" type="Employee">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--
        不起别名:javaType="com.ujiuye.bean.Department"
        起别名:javaType="Department"

        property是实体类中,关联对的属性名
        column 是对应的外键名
        javaType是这个关联对象的类型
        <id property :关联对象的属性名
            column:要注意辅表和主表字段名冲突的问题,如果冲突,需要起别名
        -->
        <association property="department" column="department_id" javaType="Department">
            <id property="id" column="dept_id"/>
            <result property="depName" column="dept_name"/>
        </association>

    </resultMap>

    <select id="findAll" resultMap="employeeResultMap">
        select e.id,e.name,d.id dept_id,d.dept_name
        from t_emp e left join t_dept d
        on e.department_id=d.id
    </select>
</mapper>

 

log4j.properties(日志文件）

# ERROR>WARN>INFO>DEBUG>TRACE

# Global logging configuration
log4j.rootLogger=TRACE, stdout
# MyBatis logging configuration...
log4j.logger.com.ujiuye.mybatis=TRACE


# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

 1.2.3Mybatis工具类

package com.ujiuye.utils;


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;

public class MybatisUtil {
    private static  SqlSessionFactory factory = null;
    //写在静态代码块的好处,只加载一次
    static {
        InputStream is = null;
        try {
            is = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        factory = new SqlSessionFactoryBuilder().build(is);
    }

    public static SqlSession getSession(){
        return factory.openSession();
    }
}

2.代码实现 

 2.1实体类

2.1.1Employee

package com.ujiuye.bean;

import lombok.*;

@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@ToString
public class Employee {
    private Integer id;
    private String name;
    //关联表
    private Department department;
}

2.1.2Department

package com.ujiuye.bean;

import lombok.*;

//必须提供getter和setter方法,因为映射文件中需要通过Employee类的对象,取该类的属性
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@ToString
public class Department {
    private Integer id;
    private String depName;
}

2.2EmployeeMapper

package com.ujiuye.mapper;


import com.ujiuye.bean.Employee;

import java.util.List;

public interface EmployeeMapper {
    public List<Employee> findAll();
}

2.3业务层

2.3.1EmployeeService

package com.ujiuye.service;

import com.ujiuye.bean.Employee;

import java.util.List;

public interface EmployeeService {
    public List<Employee> findAll();
}

2.3.2EmployeeServiceImpl

package com.ujiuye.service.impl;


import com.ujiuye.bean.Employee;
import com.ujiuye.mapper.EmployeeMapper;
import com.ujiuye.service.EmployeeService;
import com.ujiuye.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

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

public class EmployeeServiceImpl implements EmployeeService{
    public List<Employee> findAll() {
        List<Employee> list = new ArrayList<Employee>();

        SqlSession sqlSession = MybatisUtil.getSession();
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

        list = employeeMapper.findAll();

        return list;
    }
}

2.4测试

package com.ujiuye.many2one;

import com.ujiuye.bean.Employee;
import com.ujiuye.service.EmployeeService;
import com.ujiuye.service.impl.EmployeeServiceImpl;
import org.junit.Test;

import java.util.List;

public class MybatisTest {

    @Test
    public void testFindAll(){
        EmployeeService employeeService = new EmployeeServiceImpl();
        List<Employee> list = employeeService.findAll();
        for(Employee employee : list){
            System.out.println(employee);
        }
    }
}