spring集成mybaits以注解方式完成连表查询练习

题目：
以注解的方式完成连表查询
1、查询全部员工信息，要求显示部门名称
2、根据姓名模糊查询员工信息

自己看完，建一个简单的员工表和部门表。

1、先创建一个maven模块，这里直接建的普通maven模块，你也可以用quickStart骨架

2、引入必要的依赖

    <dependencies>
        <!--junit：用于测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!--mysql连接器-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.24</version>
        </dependency>
        <!--druid：数据源,根据它连接到数据库-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.4</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
        <!--spring-context：IOC容器-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.22</version>
        </dependency>
        <!--spring-jdbc：用于spring连接数据库-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.3</version>
        </dependency>
        <!--mybatis-spring：Mybatis集成spring-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.5</version>
        </dependency>
    </dependencies>

别忘了刷新

3、创建实体类
Employee

package com.gc.bean;

public class Employee {
    private Integer emId;   //员工ID
    private String emName;  //员工姓名
    private String emAddress;//员工地主之
    private Dept dept;  //所在部门

    public Employee(){}

    public Employee(Integer emId, String emName, String emAddress, Dept dept) {
        this.emId = emId;
        this.emName = emName;
        this.emAddress = emAddress;
        this.dept = dept;
    }

    public Integer getEmId() {
        return emId;
    }

    public void setEmId(Integer emId) {
        this.emId = emId;
    }

    public String getEmName() {
        return emName;
    }

    public void setEmName(String emName) {
        this.emName = emName;
    }

    public String getEmAddress() {
        return emAddress;
    }

    public void setEmAddress(String emAddress) {
        this.emAddress = emAddress;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "emId=" + emId +
                ", emName='" + emName + '\'' +
                ", emAddress='" + emAddress + '\'' +
                ", dept=" + dept +
                '}';
    }
}

Dept

package com.gc.bean;

public class Dept {
    private Integer deptId; //部门编号
    private String deptName;//部门名称

    public Dept(){}
    public Dept(Integer deptId, String deptName) {
        this.deptId = deptId;
        this.deptName = deptName;
    }

    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptId=" + deptId +
                ", deptName='" + deptName + '\'' +
                '}';
    }
}

4、创建Dao接口

EmployeeDao

package com.gc.dao;

import com.gc.bean.Dept;
import com.gc.bean.Employee;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface EmployeeDao {
    /**
     * 1、查询全部员工信息
     * @return
     */
    @Select("select * from employee")
    @Results({
            @Result(column = "emId",property = "emId"),
            @Result(column = "emName",property = "emName"),
            @Result(column = "emAddress",property = "emAddress"),
            @Result(
                    property = "dept",
                    column = "deptId",
                    javaType = Dept.class,
                    one = @One(select = "com.gc.dao.DeptDao.selectByName")
            )
    })
    List<Employee> selectAll();

    /**
     * 2、根据姓名模糊查询员工信息
     * @return
     */
    @Select("select * from employee where emName like concat('%',#{name},'%')")
    @Results({
            @Result(column = "emId",property = "emId"),
            @Result(column = "emName",property = "emName"),
            @Result(column = "emAddress",property = "emAddress"),
            @Result(
                    property = "dept",
                    column = "deptId",
                    javaType = Dept.class,
                    one = @One(select = "com.gc.dao.DeptDao.selectByName")
            )
    })
    List<Employee> selectByName(String name);
}

DeptDao

package com.gc.dao;

import com.gc.bean.Dept;
import org.apache.ibatis.annotations.Select;

public interface DeptDao {
    /**
     * 根据dpetId查询部门对象
     * @return
     */
    @Select("select * from Dept where deptId=#{deptId}")
    Dept selectByName(Integer id);
}

5、创建Service接口和实现类

package com.gc.service;

import com.gc.bean.Employee;

import java.util.List;

public interface EmployeeService {
    List<Employee> selectAll();

    List<Employee> selectByName(String name);
}

package com.gc.service.impl;

import com.gc.bean.Employee;
import com.gc.dao.EmployeeDao;
import com.gc.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;
@Component("employeeService")   //将类的对象生成Bean，交给IOC容器管理
public class EmployeeServiceImpl implements EmployeeService {
    @Autowired      //将Dao接口对象生成Bean，交由IOC容器管理
    private EmployeeDao employeeDao;

    @Override
    public List<Employee> selectAll() {
        return employeeDao.selectAll();
    }

    @Override
    public List<Employee> selectByName(String name) {
        return employeeDao.selectByName(name);
    }
}

6、配置数据源、配置Mybatis(替代MybatisConfig.xml)和配置Spring(替代applicationContext.xml)

1）配置文件(db.properties)

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql:///mybatis_DB?characterEncoding=utf-8
jdbc.username=root
jdbc.password=1234

2）配置数据源(jdbcDataSource)

package com.gc.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;

public class jdbcDataSource {
    @Value("${jdbc.driver}")    //给属性赋值
    private String driver;
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.username}")
    private String userName;
    @Value("${jdbc.password}")
    private String password;

    @Bean("dataSource")     //将方法返回的对象生成Bean，交给IOC容器管理
    public DruidDataSource getDataSource(){
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driver);
            dataSource.setUrl(url);
            dataSource.setUsername(userName);
            dataSource.setPassword(password);
            return dataSource;
    }
}

3)配置Mybatis

package com.gc.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;

public class MybatisConfig {
    @Bean
    public SqlSessionFactoryBean getFactory(@Autowired DruidDataSource dataSource){
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        //factoryBean.setTypeAliasesPackage("com.gc.bean");   //设置别名,没有用到xml文件所以不需要
        return factoryBean;
    }

    @Bean
    public MapperScannerConfigurer getMapperScannerConfigurer(){
        MapperScannerConfigurer configurer = new MapperScannerConfigurer();
        configurer.setBasePackage("com.gc.dao");    //扫描指定包
        return configurer;
    }
}

4）配置Spring

package com.gc.config;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.context.annotation.PropertySource;

@Configuration          //将类设置为核心配置类
@ComponentScan("com.gc")//扫描只当包类上的注解
@PropertySource("classpath:db.properties") //读取根目录下的db.properties配置文件
@Import({jdbcDataSource.class,MybatisConfig.class}) //导入jdbcDataSource类和MybatisConfig类
public class SpringConfig {
}

7、测试

package com.gc.test;

import com.gc.bean.Employee;
import com.gc.config.SpringConfig;
import com.gc.service.EmployeeService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import java.util.List;

public class EmployeeTest {
    //返回EmployeeService对象
    public EmployeeService getService(){
        //加载核心配置类
        ApplicationContext cxt = new AnnotationConfigApplicationContext(SpringConfig.class);
        return (EmployeeService) cxt.getBean("employeeService");
    }

    @Test
    public void SelectAll(){
        List<Employee> employeeList = getService().selectAll();
        for(Employee employee:employeeList){
            System.out.println(employee);
        }
    }

    @Test
    public void selectByName(){
        List<Employee> employeeList = getService().selectByName("2");
        for(Employee employee:employeeList){
            System.out.println(employee);
        }
    }
}

最后看下，我的目录结构