mybatis中注解使用
多表查询
两张表:部门表 与 员工表 进行 一对多的查询
部门表
-- 部门表 dept
CREATE TABLE `dept` (
`id` int(20) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
`name` varchar(255) NOT NULL COMMENT '部门名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO `dept` VALUES (1, '财务部');
INSERT INTO `dept` VALUES (2, '研发部');
INSERT INTO `dept` VALUES (3, '行政部');
-- 查询所有数据
select * from `dept`;
员工表
-- 员工表employee
CREATE TABLE `employee` (
`id` int(20) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
`name` varchar(255)NOT NULL COMMENT '员工姓名',
`dept_id` int(20) NULL DEFAULT NULL COMMENT '所属部门',
PRIMARY KEY (`id`) USING BTREE,
CONSTRAINT `FK_dept` FOREIGN KEY (`id`) REFERENCES `dept` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO `employee` VALUES (1, '张三', 1);
INSERT INTO `employee` VALUES (2, '李四', 1);
INSERT INTO `employee` VALUES (3, '王五', 2);
-- 查询所有数据
select * from `employee`;
布局
数据库properties配置文件
jdbc_mysql.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///mydb
username=root
password=root
mbatis核心配置文件
sqlMapConfig.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文件 读取外部配置文件(读取数据库配置文件)-->
<properties resource="jdbc_mysql.properties"/>
<!--配置环境-->
<environments default="use_mysql">
<!--环境中定义了,要访问的数据库连接池 和 事务管理配置-->
<environment id="use_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 接口类所在包同名(名字不区分大小写),并且当前包需要一级一级创建-->
<package name="com.hp.mybatis.mapper"/>
</mappers>
</configuration>
Dept.java
package com.hp.mybatis.entity;
import java.util.ArrayList;
import java.util.List;
/**
* 部门实体类
*/
public class Dept {
private Integer id; //部门编号
private String name; //部门名称
private List<Employee> emps = new ArrayList<>();
@Override
public String toString() {
return "Dept{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Employee> getEmps() {
return emps;
}
public void setEmps(List<Employee> emps) {
this.emps = emps;
}
}
Employee .java
package com.hp.mybatis.entity;
/**
* 员工实体类
*/
public class Employee {
private Integer id; //员工编号
private String name; //员工姓名
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
接口
DeptMapper.java
package com.hp.mybatis.mapper;
import com.hp.mybatis.entity.Dept;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface DeptMapper {
/**
* 查询所有部门
*
* @return
*/
@Select("select * from `dept`")
@Results(id = "base_emp", value = {
@Result(id = true, property = "id", column = "id"),
@Result(property = "name", column = "name"),
@Result(property = "emps", column = "id",
many = @Many(select = "com.hp.mybatis.mapper.EmployeeMapper.findById"))
})
List<Dept> findAll();
/**
* 通过部门编号查询部门信息
*
* @param id
* @return
*/
@Select("select * from `dept` where id = #{id}")
@ResultMap("base_emp")
Dept findById(Integer id);
/**
* 根据部门名称模糊查询部门信息
*
* @param name
* @return
*/
@Select("select * from `dept` where name like #{name}")
@ResultMap("base_emp")
List<Dept> findByName(String name);
}
接口
package com.hp.mybatis.mapper;
import com.hp.mybatis.entity.Employee;
import org.apache.ibatis.annotations.Select;
public interface EmployeeMapper {
/**
* 通过员工编号查询数据
*
* @param id
* @return
*/
@Select("select * from `employee` where dept_id = #{id}")
Employee findById(Integer id);
}
测试类
Dept_Emp_Test.java
package com.hp.mybatis.test;
import com.hp.mybatis.entity.Dept;
import com.hp.mybatis.entity.Employee;
import com.hp.mybatis.mapper.DeptMapper;
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.IOException;
import java.io.InputStream;
import java.util.List;
public class Dept_Emp_Test {
//定义全局变量
private SqlSession sqlSession;
@Before
//@Before 代表当前方法中的内容,在本类中首先会被执行
public void before() throws IOException {
//环境
InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
//获取 工厂类
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//获取 SqlSessionFactory 对象 ,依赖于 SqlSessionFactoryBuilder
SqlSessionFactory factory = builder.build(is);
//获取 SqlSession 对象
sqlSession = factory.openSession();
}
@After
//@After 当前类中的所有方法单独执行完毕后,都会执行该方法
public void after() {
//提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
@Test
//查询所有部门和部门下个员工信息
public void testFindAll() {
DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
List<Dept> deptLists = deptMapper.findAll();
for (Dept deptList : deptLists) {
System.out.print(deptList);
List<Employee> employeeLists = deptList.getEmps();
for (Employee employeeList : employeeLists) {
System.out.println(employeeList);
}
}
}
@Test
//根据部门编号查询部门信息和部门下员工的信息
public void testFindById() {
DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = deptMapper.findById(1);
System.out.println(dept);
List<Employee> employeeLists = dept.getEmps();
for (Employee employeeList : employeeLists) {
System.out.println(employeeList);
}
}
@Test
//根据部门名称模糊查询部门信息和部门下员工的信息
public void testFindByName() {
DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
List<Dept> deptLists = deptMapper.findByName("%财%");
for (Dept deptList : deptLists) {
System.out.print(deptList);
List<Employee> employeeLists = deptList.getEmps();
for (Employee employeeList : employeeLists) {
System.out.println(employeeList);
}
}
}
}