Mybatis增删改查

最新推荐文章于 2024-07-27 13:31:57 发布

Z.959

最新推荐文章于 2024-07-27 13:31:57 发布

阅读量52

点赞数

文章标签： mybatis java 前端

本文链接：https://blog.csdn.net/qq_56751692/article/details/131849532

版权

引入依赖

<!-- 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);
    }
}