引入依赖
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
<scope>provided</scope>
</dependency>
部门实体
@Data
@Builder
public class Department {
private Integer id;
private String name;
}
员工实体类
@Data
@Builder
public class Employee {
private Integer id;
private String name;
private Integer age;
private Integer did;
}
编写工具类
public class MyBatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession(true);
}
}
代码实现
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="org.example.mapper.DepartmentMapper">
<!-- 返回当前主键1-->
<!-- <insert id="insertDepartment" parameterType="department" useGeneratedKeys="true"
keyColumn="id" keyProperty="id">-->
<insert id="insertDepartment" parameterType="department">
<!-- 返回当前主键2-->
<selectKey keyProperty="id" keyColumn="id" order="AFTER" resultType="int">
select last_insert_id()
</selectKey>
insert into tb_depart (name)
values (#{name});
</insert>
<delete id="deleteDepartmentById">
delete from tb_depart where id=#{id}
</delete>
<select id="getDepartmentAll" resultType="org.example.pojo.Department">
select * from tb_depart
</select>
<select id="getDepartmentToMap" resultType="java.util.Map">
select * from tb_depart where id=#{id}
</select>
<!-- 嵌套查询-->
<resultMap id="depMap" type="department">
<id column="did" property="id"/>
<result column="dname" property="name"/>
<collection property="employees" ofType="Employee">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
</collection>
</resultMap>
<select id="getDepartmentById" resultMap="depMap">
SELECT d.id did,d.name dname,e.id,e.name,e.age
FROM tb_depart d LEFT JOIN tb_employee e ON d.id=e.did
WHERE d.id=#{id}
</select>
<!-- 分步查询-->
<resultMap id="depMap1" type="department">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="employees" select="org.example.mapper.EmployeeMapper.getEmployeesByDid" column="{did=id}" fetchType="lazy"/>
</resultMap>
<select id="getDepartByIdStep" resultMap="depMap1">
select id,name from tb_depart where id=#{id}
</select>
<!-- <select id="getDepartmentById" resultType="department">-->
<!-- select * from tb_depart where id=#{id}-->
<!-- </select>-->
<select id="getDepartmentsToMap" resultType="java.util.Map">
select * from tb_depart
</select>
<select id="getDepartmentsToMap2" resultType="java.util.Map">
select * from tb_depart
</select>
<!-- select * from tb_depart where name like "%"#{name}"%"-->
<!-- select * from tb_depart where name like '%${name}%'-->
<select id="getDepartmentLikeName" resultType="org.example.pojo.Department">
select * from tb_depart where name like concat('%',#{name},'%')
</select>
<select id="getByTableName" resultType="org.example.pojo.Department">
select * from ${tableName}
</select>
</mapper>
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="org.example.mapper.DepartmentMapper">
<!-- 返回当前主键1-->
<!-- <insert id="insertDepartment" parameterType="department" useGeneratedKeys="true"
keyColumn="id" keyProperty="id">-->
<insert id="insertDepartment" parameterType="department">
<!-- 返回当前主键2-->
<selectKey keyProperty="id" keyColumn="id" order="AFTER" resultType="int">
select last_insert_id()
</selectKey>
insert into tb_depart (name)
values (#{name});
</insert>
<delete id="deleteDepartmentById">
delete from tb_depart where id=#{id}
</delete>
<select id="getDepartmentAll" resultType="org.example.pojo.Department">
select * from tb_depart
</select>
<select id="getDepartmentToMap" resultType="java.util.Map">
select * from tb_depart where id=#{id}
</select>
<!-- 嵌套查询-->
<resultMap id="depMap" type="department">
<id column="did" property="id"/>
<result column="dname" property="name"/>
<collection property="employees" ofType="Employee">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
</collection>
</resultMap>
<select id="getDepartmentById" resultMap="depMap">
SELECT d.id did,d.name dname,e.id,e.name,e.age
FROM tb_depart d LEFT JOIN tb_employee e ON d.id=e.did
WHERE d.id=#{id}
</select>
<!-- 分步查询-->
<resultMap id="depMap1" type="department">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="employees" select="org.example.mapper.EmployeeMapper.getEmployeesByDid" column="{did=id}" fetchType="lazy"/>
</resultMap>
<select id="getDepartByIdStep" resultMap="depMap1">
select id,name from tb_depart where id=#{id}
</select>
<!-- <select id="getDepartmentById" resultType="department">-->
<!-- select * from tb_depart where id=#{id}-->
<!-- </select>-->
<select id="getDepartmentsToMap" resultType="java.util.Map">
select * from tb_depart
</select>
<select id="getDepartmentsToMap2" resultType="java.util.Map">
select * from tb_depart
</select>
<!-- select * from tb_depart where name like "%"#{name}"%"-->
<!-- select * from tb_depart where name like '%${name}%'-->
<select id="getDepartmentLikeName" resultType="org.example.pojo.Department">
select * from tb_depart where name like concat('%',#{name},'%')
</select>
<select id="getByTableName" resultType="org.example.pojo.Department">
select * from ${tableName}
</select>
</mapper>
测试类——MyBatisCRUDTest
import org.apache.ibatis.session.SqlSession;
import org.example.mapper.DepartmentMapper;
import org.example.mapper.EmployeeMapper;
import org.example.pojo.Department;
import org.example.pojo.Employee;
import org.example.utils.MyBatisUtils;
import org.junit.Test;
import java.sql.Array;
import java.util.*;
public class MyBatisCRUDTest {
@Test
public void testInsert(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);
Department department = Department.builder().name("财务部").build();
departmentMapper.insertDepartment(department);
sqlSession.close();
System.out.println(department);
}
@Test
public void testBatchInsert(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = new ArrayList<>();
employees.add(Employee.builder().name("zs").age(22).did(3).build());
employees.add(Employee.builder().name("ls").age(43).did(3).build());
employees.add(Employee.builder().name("ws").age(21).did(1).build());
mapper.batchInsertEmployee(employees);
sqlSession.close();
}
@Test
public void testUpdate(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
mapper.updateEmployee(Employee.builder().id(6).name("小王").age(30).did(2).build());
sqlSession.close();
}
@Test
public void testDelete() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
mapper.deleteDepartmentById(7);
sqlSession.close();
}
@Test
public void batchDeleteEmployee(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
mapper.batchDeleteEmployee(Arrays.asList( 7, 8, 9,10));
sqlSession.close();
System.out.println("批量删除成功");
}
@Test
public void testGetEmployeesByDid(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.getEmployeesByDid(1);
sqlSession.close();
employees.forEach(System.out::println);
}
@Test
public void testGetEmployeeByName() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.getEmployeesByName("zs");
sqlSession.close();
employees.forEach(System.out::println);
}
@Test
public void testGetEmployeesByNameAndAge() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.getEmployeesByNameAndAge("wx",15);
sqlSession.close();
employees.forEach(System.out::println);
}
@Test
public void testGetEmployeesByMap() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map map = new HashMap();
map.put("name","王明");
map.put("age",27);
List<Employee> employees = mapper.getEmployeesByMap(map);
sqlSession.close();
employees.forEach(System.out::println);
}
@Test
public void getDepartmentAll(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
List<Department> departments = mapper.getDepartmentAll();
sqlSession.close();
departments.forEach(System.out::println);
}
@Test
public void testGetCount() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
int count = mapper.getCount();
sqlSession.close();
System.out.println(count);
}
@Test
public void testGetDepartmentToMap() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
Map<String, Object> map = mapper.getDepartmentToMap(5);
sqlSession.close();
System.out.println(map);
}
@Test
public void testgetDepartmentsToMap() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
List<Map<String, Object>> maps = mapper.getDepartmentsToMap();
sqlSession.close();
System.out.println(maps);
}
@Test
public void testgetDepartmentsToMap2() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
Map<String, Object> map = mapper.getDepartmentsToMap2();
sqlSession.close();
System.out.println(map);
}
@Test
public void testGetDepartmentLikeName() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
List<Department> departments = mapper.getDepartmentLikeName("部");
sqlSession.close();
departments.forEach(System.out::println);
}
@Test
public void testGetEmployeesInIds() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.getEmployeesInIds(Arrays.asList(2,3,4));
sqlSession.close();
employees.forEach(System.out::println);
}
@Test
public void testGetByTableName() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
List<Department> departments = mapper.getByTableName("tb_depart");
sqlSession.close();
departments.forEach(System.out::println);
}
@Test
public void testGetById() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getById(11);
sqlSession.close();
System.out.println(employee);
}
}