MyBatis实现一对多查询
新建Department和Employee表
sql语句
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`d_id` int(11) NOT NULL AUTO_INCREMENT,
`d_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`d_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, '财务部');
INSERT INTO `department` VALUES (2, '王五');
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`e_id` int(11) NOT NULL AUTO_INCREMENT,
`e_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`e_gender` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`e_age` int(11) DEFAULT NULL,
`e_depart_id` int(11) DEFAULT NULL,
PRIMARY KEY (`e_id`) USING BTREE,
INDEX `e_depart_id`(`e_depart_id`) USING BTREE,
CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`e_depart_id`) REFERENCES `department` (`d_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, '李四', '男', 20, 1);
INSERT INTO `employee` VALUES (2, '王五', '男', 19, 1);
INSERT INTO `employee` VALUES (3, '李三', '男', 18, 2);
INSERT INTO `employee` VALUES (4, '李丽', '女', 22, 2);
INSERT INTO `employee` VALUES (5, NULL, NULL, NULL, NULL);
SET FOREIGN_KEY_CHECKS = 1;
pom依赖
<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.47</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
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>
<!-- 环境配置:支持多环境开发 -->
<environments default="development">
<!-- 开发环境 -->
<environment id="development">
<!-- 事务管理器 -->
<transactionManager type="JDBC"/>
<!-- 数据源:驱动、连接、用户名、密码 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/person?useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</dataSource>
</environment>
</environments>
<!-- 配置映射文件:SQL语句所在位置 -->
<mappers>
<package name="com.springbmybtis.mapper"/>
</mappers>
</configuration>
dao类
Department
public class Department {
private int id;
private String name;
private List<Employee> employeeList;
public Department() {
}
public Department(int id, String name) {
this.id = id;
this.name = name;
}
public Department(int id, String name, List<Employee> employeeList) {
this.id = id;
this.name = name;
this.employeeList = employeeList;
}
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 List<Employee> getEmployeeList() {
return employeeList;
}
public void setEmployeeList(List<Employee> employeeList) {
this.employeeList = employeeList;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", name='" + name + '\'' +
", employeeList=" + employeeList +
'}';
}
}
Employee
public class Employee {
private int id;
private String name;
private String gender;
private Integer age;
public Employee() {
}
public Employee(int id, String name, String gender, Integer age) {
this.id = id;
this.name = name;
this.gender = gender;
this.age = age;
}
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 String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", age=" + age +
'}';
}
}
Mapper
DepartmentMapper
public interface DepartmentMapper {
Department queryDepartment(int id);
}
EmployeeMapper
public interface EmployeeMapper {
}
MapperXml
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">
<!-- namespace:命名空间,隔离SQL -->
<mapper namespace="com.springbmybtis.mapper.EmployeeMapper">
<!--
id:SQL语句名称,具有唯一性
resultType:返回结果类型 实体类全限定名
parameterType:输入参数类型 int Integer
-->
<resultMap id="EmMap" type="com.springbmybtis.dao.Employee">
<id column="e_id" property="id"></id>
<result column="e_name" property="name"></result>
<result column="e_gender" property="gender"></result>
<result column="e_age" property="age"></result>
</resultMap>
</mapper>
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">
<!-- namespace:命名空间,隔离SQL -->
<mapper namespace="com.springbmybtis.mapper.DepartmentMapper">
<!--
id:SQL语句名称,具有唯一性
resultType:返回结果类型 实体类全限定名
parameterType:输入参数类型 int Integer
-->
<resultMap id="DeMap" type="com.springbmybtis.dao.Department">
<id column="d_id" property="id"></id>
<result column="d_name" property="name"></result>
<collection property="employeeList" ofType="com.springbmybtis.dao.Employee"
resultMap="com.springbmybtis.mapper.EmployeeMapper.EmMap">
</collection>
</resultMap>
<select id="queryDepartment" parameterType="int" resultMap="DeMap">
SELECT d.*,e.* FROM department d, employee e WHERE e.e_depart_id=d.d_id AND d.d_id=#{id}
</select>
</mapper>
测试类
@Test
public void queryById() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
Department department = mapper.queryDepartment(1);
System.out.println(department);
sqlSession.close();
}
查询结果
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Department{id=1, name='财务部', employeeList=[Employee{id=1, name='李四', gender='男', age=20}, Employee{id=2, name='王五', gender='男', age=19}]}
Process finished with exit code 0