使用Mybatis实现多表一对多查询实例之额外SQL查询方式(含代码及解释)
一对多查询之额外SQL查询实例
项目目录结构(maven):
一对多查询额外SQL查询方式灵魂之处解析:
DepartmentMapper.xml
EmployeeMapper.xml
注意:
创建表t_dept:(一方)
创建表t_emp:(多方)
在Maven配置文件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.xiongluoluo</groupId>
<artifactId>onetomany</artifactId>
<version>1.0.0</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</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>
创建实体类Department:
package com.xiongluoluo.bean;
import lombok.*;
import java.util.ArrayList;
import java.util.List;
/**
* Created by IBM on 2019/12/24.
*/
@Setter
@Getter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Department {
private Integer id ;
private String name ;
private List<Employee> employeeList = new ArrayList<Employee>();
/*private List<Employee> employeeList;*/
/*这个地方new不new都可以,没什么影响.因为employeeList是作为一个属性.具体情况具体分析*/
}
创建实体类Employee:
package com.xiongluoluo.bean;
import lombok.*;
/**
* Created by IBM on 2019/12/24.
*/
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Employee {
private Integer id ;
private String name ;
}
创建mybatis配置文件Mybatis.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"/>
<!--这里用的是resource属性,导入外部properties文件-->
<typeAliases>
<package name="com.xiongluoluo.bean"/>
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driverClassName}"/>
<!--这里是driver-->
<!--driverClassName是properties文件中的变量,通过${}取其对应的支持-->
<!--下面同理-->
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/xiongluoluo/mapper/EmployeeMapper.xml"/>
<mapper resource="com/xiongluoluo/mapper/DepartmentMapper.xml"/>
<!--这里是斜线(因为是目录),不是点,不是点,不是点,你能不能不要在一个错误上躺平,每天忘提交事务,每天写...,唉-->
<!--Mybatis的配置文件,也就是这个文件,是程序的入口.我们配置的mapper文件如果不在这里注册
那么程序是读不到的.所以一定别忘记在这里添加.
这里用的也是resource属性,引入外部的mapper.xml属性-->
</mappers>
</configuration
db.properties文件:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///mydb1228
username=root
password=1234
log4j.properties文件:
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.com.xiongluoluo.mapper=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
创建DepartmentMapper.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.xiongluoluo.mapper.DepartmentMapper">
<!--一对多额外SQL查询-->
<resultMap id="departmentResultMap" type="Department">
<id property="id" column="id"/>
<result property="name" column="name"/>
<!--collection标签:用户处理集合类型的关联对象
property:把结果集封装成那个属性
ofType:集合的泛型
select:发送那一条Sql查询关联的对象
column:查询部门的结果集中的那个列作为参数来查询关联信息-->
<collection property="employeeList"
column="id" ofType="Employee"
select="com.xiongluoluo.mapper.EmployeeMapper.selectEmoloyeeByDeptId"/>
</resultMap>
<select id="selectDepartmentById" resultMap="departmentResultMap" parameterType="int">
select id,name from t_dept where id=#{id}
</select>
</mapper>
创建DepartmentMapper.java:
package com.xiongluoluo.mapper;
import com.xiongluoluo.bean.Department;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface DepartmentMapper {
public Department selectDepartmentById(int id);
}
创建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.xiongluoluo.mapper.EmployeeMapper">
<!--一对多额外SQL查询的额外SQL查询语句-->
<select id="selectEmoloyeeByDeptId" parameterType="int" resultType="Employee">
select id,name,dept_id from t_emp where dept_id=#{id}
</select>
</mapper>
创建EmployeeMapper.java:
package com.xiongluoluo.mapper;
import com.xiongluoluo.bean.Employee;
import java.util.List;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface EmployeeMapper {
/*根据部门id查找员工*/
public List<Employee> selectEmoloyeeByDeptId(int dept_id);
}
创建DepartmentService.java:
package com.xiongluoluo.service;
import com.xiongluoluo.bean.Department;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface DepartmentService {
public Department findDepartmentById(int id);
}
创建DepartmentServiceImpl.java:
package com.xiongluoluo.service.impl;
import com.xiongluoluo.bean.Department;
import com.xiongluoluo.mapper.DepartmentMapper;
import com.xiongluoluo.service.DepartmentService;
import com.xiongluoluo.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public class DepartmentServiceImpl implements DepartmentService {
public Department findDepartmentById(int id) {
SqlSession sqlSession = MybatisUtil.getSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
Department department = mapper.selectDepartmentById(id);
return department;
}
}
创建EmployeeService.java:
package com.xiongluoluo.service;
import com.xiongluoluo.bean.Employee;
import java.util.List;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface EmployeeService {
public List<Employee> findEmployeeByDeptId(int dept_id);
}
创建EmployeeServiceImpl.java:
package com.xiongluoluo.service.impl;
import com.xiongluoluo.bean.Employee;
import com.xiongluoluo.mapper.EmployeeMapper;
import com.xiongluoluo.service.EmployeeService;
import com.xiongluoluo.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public class EmployeeServiceImpl implements EmployeeService {
public List<Employee> findEmployeeByDeptId(int dept_id) {
SqlSession sqlSession = MybatisUtil.getSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = mapper.selectEmoloyeeByDeptId(dept_id);
return employeeList;
}
}
创建测试类:
package com.xiongluoluo.test;
import com.xiongluoluo.bean.Department;
import com.xiongluoluo.service.DepartmentService;
import com.xiongluoluo.service.impl.DepartmentServiceImpl;
import org.junit.Test;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public class MybatisTest {
/*一对多查询-----测试*/
@Test
public void test1(){
DepartmentService departmentService = new DepartmentServiceImpl();
Department department = departmentService.findDepartmentById(2);
System.out.println(department);
}
}