MyBatis的CRUD操作

前言

上篇文章写道了如何进行Mybatis项目的基础搭建，上篇文章链接：https://blog.youkuaiyun.com/hehui1998/article/details/116157273
普通模式,也称为传统DAO模式,就是在传统DAO模式下,定义接口和实现类,如 interface EmpDao class EmpDaoImpl implements EmpDao. 在Q实现类中,用SLSession对象调用select insert delete update 等方法实现.目前极为少见.在传统模式下,我们需要知道SqlSession对象 实现CURD和 参数传递的处理

一、sqlSession查询的三种方式

SqlSession对象本身的API中就有三个查询方法,分别能够实现如下查询方式
1、返回单个对象 selectOne
2、返回对象List集合 selectList
3、返回对象Map集合 selectMap

1、返回单个对象 selectOne

首先创建数据表对应的实体类，然后创建TeacherMapper.xml文件，并使用select标签进行sql语句编写

<?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="TeacherMapper">
    <!--
        返回单个对象
        public Teacher findOne();
        id 相当于方法名
        resultType 相当于返回值类型
            sql语句的查询结果用哪个类来进行封装 如果返回值类型是集合,这里写的也是集合中的元素对应的类,不是集合本身作为类型
        paramaterType 参数类型
        SQL语句就是具体的方法体的实现
    -->
    <select id="getTeacher" resultType="teacher">
        select * from teachers where teacher_id=1
    </select>   
</mapper>

在mybatis配置文件中进行mapper映射文件的添加

<?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>
   <!--添加mapper映射文件-->
    <mappers>
        <mapper resource="com/xiaohui/mapper/StudentMapper.xml"/>
        <mapper resource="com/xiaohui/mapper/TeacherMapper.xml"/>
    </mappers>
</configuration>

在测试类中进行测试，

public class TestDemo01 {
    private SqlSession sqlSession;
    @Before
    public void init(){
        SqlSessionFactoryBuilder sessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream inputStream=null;
        try {
            inputStream = Resources.getResourceAsStream("SqlSessionFactory.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sessionFactory = sessionFactoryBuilder.build(inputStream);
        sqlSession = sessionFactory.openSession();
    }
    @Test
    public void test02(){
        //查询单个对象
        Teacher getTeacher = sqlSession.selectOne("getTeacher");
        System.out.println(getTeacher);
    }
}

测试结果：Teacher(teacher_id=1, teacher_name=张三, teacher_salary=3000)

2、返回对象List集合 selectList

TeacherMapper.xml

    <!--返回多个对象集合-->
    <select id="teacherAll" resultType="teacher">
        select * from teachers
    </select>

TestDemo01

    /**
     * 查询所有对象
     */
    @Test
    public void teacherAll(){
        List<Teacher> teacherAll = sqlSession.selectList("teacherAll");
//        for (Teacher teacher : teacherAll){
//            System.out.println(teacher);
//        }
        teacherAll.forEach(System.out::println);
    }

测试结果：

Teacher(teacher_id=1, teacher_name=张三, teacher_salary=3000)
Teacher(teacher_id=2, teacher_name=李四, teacher_salary=4000)
Teacher(teacher_id=3, teacher_name=老王, teacher_salary=3500)

3、返回对象Map集合 selectMap

TeacherMapper.xml

    <!--返回多个对象的Map集合
        把查询出来的数据中的某一列作为键,整条数据封装的对象作为值
        public Map<key,Emp> findEmpMap()
        <empno,Emp>
        <key,Emp>
    -->
    <select id="teacherAllMap" resultType="map">
        select * from teachers
    </select>

TestDemo01

    /**
     * 查询多个对象的Map集合
     */
    @Test
    public void teacherAllMap(){
        Map<Integer, Teacher> teacherMap = sqlSession.selectMap("teacherAllMap", "teacher_id");
        Set<Integer> keySet = teacherMap.keySet();
        for (Integer integer : keySet) {
            System.out.println(integer+"："+teacherMap.get(integer));
        }
    }

测试结果：

1：{teacher_name=张三, teacher_salary=3000, teacher_id=1}
2：{teacher_name=李四, teacher_salary=4000, teacher_id=2}
3：{teacher_name=老王, teacher_salary=3500, teacher_id=3}

二、sqlSession传递参数的三种方式

1、测试单个基本数据类型作为参数

<?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="TeacherMapper2">
    <!--
        参数为一个基本数据类型
        根据员工工号查询员工的全部信息,返回单个员工对象
        public Teacher getTeacher(int teacher_id);
        parameterType 在有参数情况下也是可以省略不写  mybatis 可以根据实际情况自动判断
        如果要写parameterType 那么就要写对
        在SQL语句上可以使用${}  #{} 代表参数的占位
        如果参数是单个基本数据类型,{}中名字可以随便写,见名知意
        ${} 代表mybatis底层使用Statment语句对象,参数是以字符串拼接的形式设置
        #{} 代表mybatis底层使用的preparedStatment语句对象,参数使用?作为占位符处理
        #{} 以后常用
    -->
    <select id="getTeacher" resultType="teacher" parameterType="int">
        select * from teachers where teacher_id=#{teacher_id}
    </select>
</mapper>

测试代码：

    @Test
    public void getTeacher(){
        Teacher getTeacher = sqlSession.selectOne("TeacherMapper2.getTeacher", "1");
        System.out.println(getTeacher);
    }

结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@25be7b63]
DEBUG - ==>  Preparing: select * from teachers where teacher_id=1 
DEBUG - ==> Parameters: 
DEBUG - <==      Total: 1
Teacher(teacher_id=1, teacher_name=老郑, teacher_salary=3000)

如果是使用#{}方式来进行条件查询时，直接指定占位符的数据，
结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5afa3c9]
DEBUG - ==>  Preparing: select * from teachers where teacher_id=?  （这里的参数会变成问号，而下一行则是指定为Integer类型的参数）
DEBUG - ==> Parameters: 1(Integer)
DEBUG - <==      Total: 1
Teacher(teacher_id=1, teacher_name=老郑, teacher_salary=3000)

2、测试Map集合作为参数

    <!--
        参数为map集合
        查询指定员工和指定最低薪资的员工信息
        public List<Teacher> getTeacherMap(String name,int salary);
        <  >  最好要进行转译处理,参照HTML转译  w3school在线文档中有转译符号对应规则
         Map<String,Object> args=new HashMap<>();
            args.put("name", 老罗);
            args.put("salary", 2000);
        #{}中写的是map集合中,参数的键
    -->
    <select id="getTeacherMap" resultType="teacher" parameterType="map">
        select * from teachers where teacher_name = #{name} and teacher_salary &gt;= #{salary}
    </select>

测试代码：

    @Test
    public void getTeacherMap(){
        //测试Map集合作为参数
        Map<String,Object> args= new HashMap<>();
        args.put("name","老罗");
        args.put("salary",2000);
        List<Teacher> getTeacherMap = sqlSession.selectList("getTeacherMap", args);
        getTeacherMap.forEach(System.out::println);
    }

结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5e2c3d18]
DEBUG - ==>  Preparing: select * from teachers where teacher_name = ? and teacher_salary >= ? 
DEBUG - ==> Parameters: 老罗(String), 2000(Integer)
DEBUG - <==      Total: 3
Teacher(teacher_id=2, teacher_name=老罗, teacher_salary=4000)
Teacher(teacher_id=4, teacher_name=老罗, teacher_salary=2500)
Teacher(teacher_id=6, teacher_name=老罗, teacher_salary=3000)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5e2c3d18]

3、测试对象作为参数

    <!--
        参数为对象
        teacher >>>  teacher_id   teacher_name
        参数是我们自定义的类型,那么 #{}中写的是参数的属性名
    -->
    <select id="getTeacherArg" resultType="teacher" parameterType="teacher">
        select * from teachers where teacher_id = #{teacher_id} and teacher_name = #{teacher_name}
    </select>

测试代码

    @Test
    public void getTeacherArg(){
        //测试对象作为参数
        Teacher teacher = new Teacher();
        teacher.setTeacher_id(2);
        teacher.setTeacher_name("老罗");
        List<Teacher> getTeacherMap = sqlSession.selectList("getTeacherArg", teacher);
        getTeacherMap.forEach(System.out::println);
    }

结果

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@909217e]
DEBUG - ==>  Preparing: select * from teachers where teacher_id = ? and teacher_name = ? 
DEBUG - ==> Parameters: 2(Integer), 老罗(String)
DEBUG - <==      Total: 1
Teacher(teacher_id=2, teacher_name=老罗, teacher_salary=4000)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@909217e]

三、sqlSession完成DML所有操作

1、增加操作

新创建一个mapper映射文件，方便进行增删改操作，TeacherMapper3.xml
注意：
增删改时，需要提交事务{
1、sqlSession.commit();手动提交事务
2、sqlSession = sessionFactory.openSession(true);设置事务自动提交
}

<?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="TeacherMapper3">
    <!--
    增删方法的返回值类型都是int
    resultType就无需指定了
    insert update delete 标签中没有resultType
    但是仍然可以有paramaterType
    -->
    <!--增加方法
    public int addTeacher(Teacher teacher)
    -->
    <insert id="addTeacher" parameterType="teacher">
        insert into teachers(teacher_name,teacher_salary) values(#{teacher_name},#{teacher_salary})
    </insert>
</mapper>

测试代码

package com.xiaohui.test;

import com.xiaohui.entity.Teacher;
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;

/**
 * @ClassName TestCRUDDemo3
 **/
public class TestCRUDDemo3 {
    private SqlSession sqlSession;
    @Before
    public void init(){
        SqlSessionFactoryBuilder sessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream inputStream=null;
        try {
            inputStream = Resources.getResourceAsStream("SqlSessionFactory.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sessionFactory = sessionFactoryBuilder.build(inputStream);
        sqlSession = sessionFactory.openSession(true);
    }

    @Test
    public void insertTeacher(){
        Teacher teacher = new Teacher(null,"张三",5000);
        int addTeacher = sqlSession.insert("addTeacher", teacher);
        System.out.println(addTeacher);
    }

    @After
    public void release(){
        //关闭sqlSession
        sqlSession.close();
    }
}

结果

DEBUG - Created connection 352598575.
DEBUG - ==>  Preparing: insert into teachers(teacher_name,teacher_salary) values(?,?) 
DEBUG - ==> Parameters: 张三(String), 5000(Integer)
DEBUG - <==    Updates: 1
1
DEBUG - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@15043a2f]
DEBUG - Returned connection 352598575 to pool.

2、修改操作

    <!--修改方法
    public int updateTeacher(Teacher teacher)
    -->
    <update id="updateTeacher" parameterType="teacher">
        update
            teachers
        set
            teacher_name = #{teacher_name},teacher_salary = #{teacher_salary}
        where
            teacher_id = #{teacher_id}
    </update>

测试方法

    @Test
    public void updateTeacher(){
        //修改方法
        Teacher teacher = new Teacher(1,"早上好",3500);
        int updateTeacher = sqlSession.insert("updateTeacher", teacher);
        System.out.println(updateTeacher);
    }

结果

DEBUG - Created connection 1281414889.
DEBUG - ==>  Preparing: update teachers set teacher_name = ?,teacher_salary = ? where teacher_id = ? 
DEBUG - ==> Parameters: 早上好(String), 3500(Integer), 1(Integer)
DEBUG - <==    Updates: 1
1
DEBUG - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c60d6e9]
DEBUG - Returned connection 1281414889 to pool.

3、删除操作

    <!--删除方法
    public int deleteTeacher(int teacher_id)
    -->
    <delete id="deleteTeacher" parameterType="int">
        delete from teachers where teacher_id &gt; #{teacher_id}
    </delete>

测试方法

    @Test
    public void deleteTeacher(){
        //删除方法
        int deleteTeacher = sqlSession.insert("deleteTeacher", 8);
        System.out.println(deleteTeacher);
    }

结果

DEBUG - Created connection 1281414889.
DEBUG - ==>  Preparing: delete from teachers where teacher_id > ? 
DEBUG - ==> Parameters: 8(Integer)
DEBUG - <==    Updates: 2
2
DEBUG - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c60d6e9]
DEBUG - Returned connection 1281414889 to pool.

四、使用注解完成CRUD操作

在mapper接口文件中使用@Select，@Update ，@Insert，@Delete注解的方式进行sql操作，
1.使用注解没有实现Java代码和SQL语句的解耦
2.无法实现SQL语句的动态拼接
3.进行多表的查询时定制ResultMap比较麻烦
以下是xml和注解的优点：

• XML：
  • 1.类和类之间的解耦
  • 2.利于修改。直接修改XML文件，无需到源代码中修改。
  • 3.配置集中在XML中，对象间关系一目了然，利于快速了解项目和维护
  • 4.容易和其他系统进行数据交交换
• 注解：
  • 1.简化配置
  • 2.使用起来直观且容易，提升开发效率
  • 3.类型安全，编译器进行校验，不用等到运行期才会发现错误。
  • 4.注解的解析可以不依赖于第三方库，可以直接使用Java自带的反射

public interface DeptMapper {
    @Select("select * from dept where deptno = #{deptno}")
    Dept findByDeptno(int deptno);

    @Update("update dept set dname = #{dname} , loc = #{loc} where deptno = #{deptno}")
    int updateDept(Dept dept);

    @Insert("insert into dept values(DEFAULT,#{dname},#{loc})")
    int insertDept(Dept dept);

    @Delete("delete from dept where deptno = #{deptno}")
    int deleteDept(int deptno);
}

测试代码：

    @Test
    public void testAddDept(){
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = new Dept();
        dept.setDname("总部");
        dept.setLoc("北京");
        int i = mapper.insertDept(dept);
        sqlSession.commit();
    }

    @Test
    public void testUpdateDept(){
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = new Dept();
        dept.setDeptno(44);
        dept.setDname("后勤");
        dept.setLoc("教育网");
        int i = mapper.updateDept(dept);
        sqlSession.commit();
    }

    @Test
    public void testSelectDept(){
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept byDeptno = mapper.findByDeptno(44);
        System.out.println(byDeptno);
    }

    @Test
    public void testDeleteDept(){
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        int i = mapper.deleteDept(44);
        sqlSession.commit();
    }

结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@65987993]
DEBUG - ==>  Preparing: insert into dept values(DEFAULT,?,?) 
DEBUG - ==> Parameters: 总部(String), 北京(String)
DEBUG - <==    Updates: 1
DEBUG - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@65987993]

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c163e3]
DEBUG - ==>  Preparing: update dept set dname = ? , loc = ? where deptno = ? 
DEBUG - ==> Parameters: 后勤(String), 教育网(String), 44(Integer)
DEBUG - <==    Updates: 1
DEBUG - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c163e3]

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6b695b06]
DEBUG - ==>  Preparing: select * from dept where deptno = ? 
DEBUG - ==> Parameters: 45(Integer)
DEBUG - <==      Total: 1
Dept(deptno=44, dname=后勤, loc=教育网, empList=null)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6b695b06]

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c430e6c]
DEBUG - ==>  Preparing: delete from dept where deptno = ? 
DEBUG - ==> Parameters: 45(Integer)
DEBUG - <==    Updates: 1
DEBUG - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c430e6c]