【编程不良人】MyBatis从入门到精通学习笔记02-Mybatis中增删改查使用

快来打我* 2023-10-09 14:00 47阅读 0赞

### 1.查(select) ###

**配套视频：**[【编程不良人】Mybatis 从入门到精通\_哔哩哔哩\_bilibili][Mybatis _bilibili]

**mybatis数据库中表student：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16][]

![632e459c2cc040b9a756326fe07bb25b.png][]

**java：entity.Student.java**

package entity;
      
      /**
       * @ClassName Student
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/7 9:17
       * @Version 1.0
       */
      public class Student {
          private int id;
          private String name;
          private int age;
      
          public Student() {
          }
    
          public Student(int id, String name, int age) {
              this.id = id;
              this.name = name;
              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 int getAge() {
              return age;
          }
      
          public void setAge(int age) {
              this.age = age;
          }
      
          @Override
          public String toString() {
              return "Student{" +
                      "id=" + id +
                      ", name='" + name + '\'' +
                      ", age=" + age +
                      '}';
          }
      }

**java：dao.StudentDAO.java**

package dao;
      
      import entity.Student;
      import org.apache.ibatis.annotations.Param;
      
      import java.util.List;
      
      /**
       * @ClassName StudentDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/7 9:18
       * @Version 1.0
       */
      public interface StudentDAO {
          //根据id查询学生
          public Student findById(int id);
      
          //根据name查询学生
          public Student findByName(String name);
      
          //多参数查询（注意接口中的参数必须使用@Param注解绑定）：根据id和name查询学生
          public Student findByIdAndName(@Param("id")int id,@Param("name")String name);
          
          //查询所有
          public List<Student> findAll();
      
      }

**resources: dao/StudentDAOMapper.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="dao.StudentDAO">
          
          
          <select id="findById" parameterType="int" resultType="entity.Student">
              select id,name,age from student where id=#{id}
          </select>
      
          
          <select id="findByName" parameterType="String" resultType="entity.Student">
              select id,name,age from student where name=#{name}
          </select>
      
          
          <select id="findByIdAndName" resultType="entity.Student">
              select id,name,age from student where id=#{id} and name=#{name}
          </select>
      
          
          <select id="findAll" resultType="entity.Student">
              select id,name,age from student
          </select>
      
      </mapper>

**resources: mybatis-config.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.cj.jdbc.Driver"/>
                      <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false"/>
                      <property name="username" value="root"/>
                      <property name="password" value="123456"/>
                  </dataSource>
              </environment>
          </environments>
      
          
          <mappers>
              
              <mapper resource="dao/StudentDAOMapper.xml"/>
          </mappers>
      
      </configuration>

**java：test.StudentDAOTest.java**

package test;
      
      import dao.StudentDAO;
      import entity.Student;
      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.Test;
      
      import java.io.IOException;
      import java.io.Reader;
      import java.util.List;
      
      /**
       * @ClassName StudentDAOTest
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/7 9:26
       * @Version 1.0
       */
      public class StudentDAOTest {
          //根据id查询
          @Test
          public void findByIdTest() throws IOException {
              Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
              SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
              SqlSession session = sessionFactory.openSession();
              StudentDAO studentDAO = session.getMapper(StudentDAO.class);
              Student student = studentDAO.findById(1);
              System.out.println(student);
          }
      
          //根据name查询
          @Test
          public void findByNameTest() throws IOException {
              Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
              SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
              SqlSession session = sessionFactory.openSession();
              StudentDAO studentDAO = session.getMapper(StudentDAO.class);
              Student student = studentDAO.findByName("张三");
              //Student student = studentDAO.findByName("zhangsan");
              System.out.println(student);
          }
      
          //根据id和name查询
          @Test
          public void findByIdAndNameTest() throws IOException {
              Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
              SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
              SqlSession session = sessionFactory.openSession();
              StudentDAO studentDAO = session.getMapper(StudentDAO.class);
              Student student = studentDAO.findByIdAndName(1,"张三");
              //Student student = studentDAO.findByIdAndName(2,"zhangsan");
              System.out.println(student);
          }
      
          //查询所有
          @Test
          public void findAllTest() throws IOException {
              Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
              SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
              SqlSession session = sessionFactory.openSession();
              StudentDAO studentDAO = session.getMapper(StudentDAO.class);
              List<Student> students= studentDAO.findAll();
              for (Student student : students) {
                  System.out.println(student);
              }
          }
      }

**测试结果：**

（1）根据id查询

Student{id=1, name='张三', age=15}

（2）根据id和name查询

Student{id=1, name='张三', age=15}

（3）查询所有

Student{id=1, name='张三', age=15}
      Student{id=2, name='zhangsan', age=20}
      Student{id=3, name='李四', age=25}
      Student{id=4, name='王五', age=30}
      Student{id=5, name='赵六', age=35}

#### 小结 ####

**1.单个参数查询**

a. 查询结果自动封装为实体对象，要求查询结果的列名必须与实体中属性名一致，实体中属性一定要有set、get方法，实体必须存在无参数构造；

b. 当查询结果与实体类中的属性名不一致时，可使用数据库别名的方式解决；

**2.多参数查询**

a. 在使用多个参数时，select标签中的parameterType属性省略不写；

b. 在select标签内获取多个参数的值，要求接口中的参数必须使用@Param注解绑定

**3.查询所有**

a. select标签中的parameterType属性省略不写

b. 无论是查询一个还是查询多个，最终resultType的值都是要封装的实体类全名（包名.类名）

c. 当进行单表简单查询时且返回值类型是基本类型时，一般尽量使用resultType；当进行表关联查询时，或者说xml中定义了相关的resultMap标签，那么就一般尽量使用resultMap；且resultType和resultMap不能同时使用。

**4.注意事项**

**a. mybatis报错：Could not find resource com.study.dao.UserMapper.xml**

**可能原因：**

1.  映射配置文件位置要与映射器位置不一致，如：映射器在java下面的com.study.dao里，那么配置文件就应该在resources下面的com/study/dao目录下，两者要对应起来，否则找不到。注意mappers标签里面的mapper中resource内的层级结构用**/**而不使用**.**，例如com/study/dao/UserMapper.xml而不是com.study.dao.UserMapper.xml。

**b. mybatis使用中文作为查询条件查询时，数据库有结果，但查询结果为null**

**可能原因：**

1.  中文在查询时变成乱码，导致查询不到结果，解决办法是在数据库连接时配置中文查询 characterEncoding=utf-8，具体使用时添加"&characterEncoding=utf-8"或者"& amp;characterEncoding=utf-8"(提示：&后面多加了个空格使用时要删掉，此处使用时存在转义)
2.  java实体类的属性名与查询结果的列名（表的字段名）不一致，没有一一对应，表字段名太长时使用别名
3.  sql语句有问题，仔细检查后修改

**c. 从数据库中进行查询或者进行其他操作时控制台会出现以下警告：WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.**

**可能原因：**

1.  需要在配置文件中连接数据库的url后面添加"&useSSL=false"，注意有时候要考虑转义字符，此时要添加"& amp;useSSL=false"(提示：&后面多加了个空格使用时要删掉，此处使用时存在转义)
2.  如果上面配置正确，则可能是连接数据库时的驱动版本问题，在pom.xml中更换成其他版本的依赖即可。

**完整的mysql数据库url value：**

jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false

### 2.增(insert) ###

**配套视频：**[【编程不良人】Mybatis 从入门到精通\_哔哩哔哩\_bilibili][Mybatis _bilibili 1]

**StudentDAO.java**

//插入学生信息
      public void insert(Student student);

**StudentDAOMapper.xml**

<insert id="insert" parameterType="entity.Student">
          insert into student values(#{id},#{name},#{age})
      </insert>

**StudentDAOTest.java**

//插入学生信息
          @Test
          public void insertTest() throws IOException {
              SqlSession session = null;
              try {
                  Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
                  SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
                  session = sessionFactory.openSession();
                  StudentDAO studentDAO = session.getMapper(StudentDAO.class);
                  studentDAO.insert(new Student(5,"小猪猪",28));
                  //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
                  session.commit();
              } catch (IOException e) {
                  e.printStackTrace();
                  //失败回滚
                  session.rollback();
              }
          }

### 3.删(delete) ###

**StudentDAO.java**

//根据id删除学生信息
      public void delete(int id);

**StudentDAOMapper.xml**

<delete id="delete" parameterType="int">
          delete from student where id=#{id}
      </delete>

**StudentDAOTest.java**

//根据id删除学生信息
          @Test
          public void deleteTest() throws IOException {
              SqlSession session = null;
              try {
                  Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
                  SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
                  session = sessionFactory.openSession();
                  StudentDAO studentDAO = session.getMapper(StudentDAO.class);
                  studentDAO.delete(5);
                  //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
                  session.commit();
              } catch (IOException e) {
                  e.printStackTrace();
                  //失败回滚
                  session.rollback();
              }
          }

### 4.改(update) ###

**StudentDAO.java**

//更新学生信息
      public void update(Student student);

**StudentDAOMapper.xml**

<update id="update" parameterType="entity.Student">
          update student set name=#{name},age=#{age} where id=#{id};
      </update>

**StudentDAOTest.java**

//更新学生信息
          @Test
          public void updateTest() throws IOException {
              SqlSession session = null;
              try {
                  Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
                  SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
                  session = sessionFactory.openSession();
                  StudentDAO studentDAO = session.getMapper(StudentDAO.class);
                  studentDAO.update(new Student(2,"小呆呆",18));
                  //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
                  session.commit();
              } catch (IOException e) {
                  e.printStackTrace();
                  //失败回滚
                  session.rollback();
              }
          }

#### 小结 ####

mybatis在进行增删改操作时默认事务是以手动方式提交的（setAutoCommit(false)），此时需要进行手动提交（session.commit()），否则不能使数据库内容发生改变。

### 5.MybatisUtil封装 ###

**配套视频：**[【编程不良人】Mybatis 从入门到精通\_哔哩哔哩\_bilibili][Mybatis _bilibili 2]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

**具体实现：**

**MybatisUtil.java**

package util;
      
      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 java.io.IOException;
      import java.io.Reader;
      
      /**
       * @ClassName MybatisUtil
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/8 10:01
       * @Version 1.0
       */
      public class MybatisUtil {
          //工厂
          private static SqlSessionFactory sqlSessionFactory;
      
          //ThreadLocal 保证service与dao层session一致
          private static final ThreadLocal<SqlSession> t = new ThreadLocal<SqlSession>();
      
          static {
              try {
                  Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
                  sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
              } catch (IOException e) {
                  e.printStackTrace();
              }
          }
      
          /**
           * @MethodName getSqlSession
           * @Description 返回sqlSession对象
           * @return: org.apache.ibatis.session.SqlSession
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:06
           */
          public static SqlSession getSqlSession(){
              SqlSession sqlSession = t.get();
              if(sqlSession == null){
                  sqlSession = sqlSessionFactory.openSession();
                  t.set(sqlSession);
              }
              return sqlSession;
          }
      
          /**
           * @MethodName close
           * @Description 关闭sqlSession对象
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:09
           */
          public static void close(){
              SqlSession sqlSession = t.get();
              if(sqlSession != null){
                  sqlSession.close();
                  t.remove();
              }
          }
      
          /**
           * @MethodName commit
           * @Description 提交事务
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:13
           */
          public static void commit(){
              getSqlSession().commit();
              close();
          }
      
          /**
           * @MethodName rollback
           * @Description 回滚事务
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:14
           */
          public static void rollback(){
              getSqlSession().rollback();
              close();
          }
      
      }

**StudentDAOTest2.java（优化StudentDAOTest）**

package test;
      
      import dao.StudentDAO;
      import entity.Student;
      import org.apache.ibatis.session.SqlSession;
      import org.junit.Test;
      import util.MybatisUtil;
      
      import java.util.List;
      
      /**
       * @ClassName StudentDAOTest2
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/8 10:15
       * @Version 1.0
       */
      public class StudentDAOTest2 {
          /**
           * @MethodName findByIdTest
           * @Description 测试根据id查询学生信息
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:40
           */
          @Test
          public void findByIdTest(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
              Student student = studentDAO.findById(1);
              System.out.println(student);
              sqlSession.close();
          }
      
          /**
           * @MethodName findByNameTest
           * @Description 测试根据name查询学生信息
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:43
           */
          @Test
          public void findByNameTest(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
              Student student = studentDAO.findByName("小呆呆");
              System.out.println(student);
              sqlSession.close();
          }
      
          /**
           * @MethodName findByIdAndNameTest
           * @Description 测试根据id和name查询学生信息
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:44
           */
          @Test
          public void findByIdAndNameTest(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
              Student student = studentDAO.findByIdAndName(1, "张三");
              System.out.println(student);
              sqlSession.close();
          }
      
          /**
           * @MethodName findAllTest
           * @Description 测试查询所有
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:47
           */
          @Test
          public void findAllTest(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
              List<Student> students = studentDAO.findAll();
              for (Student student : students) {
                  System.out.println(student);
              }
              sqlSession.close();
          }
      
          /**
           * @MethodName updateTest
           * @Description 测试更新数据
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:49
           */
          @Test
          public void updateTest(){
              SqlSession sqlSession = null;
              try {
                  sqlSession = MybatisUtil.getSqlSession();
                  StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
                  studentDAO.update(new Student(3,"光头强",60));
                  sqlSession.commit();
              } catch (Exception e) {
                  e.printStackTrace();
                  sqlSession.rollback();
              }
              sqlSession.close();
          }
      
          /**
           * @MethodName deleteTest
           * @Description 测试根据id删除
           * @Author Jiangnan Cui
           * @Date 2022/4/8 10:57
           */
          @Test
          public void deleteTest(){
              SqlSession sqlSession = null;
              try {
                  sqlSession = MybatisUtil.getSqlSession();
                  StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
                  studentDAO.delete(4);
                  sqlSession.commit();
              } catch (Exception e) {
                  e.printStackTrace();
                  sqlSession.rollback();
              }
              sqlSession.close();
          }
      
          /**
           * @MethodName insertTest
           * @Description 测试插入学生信息
           * @Author Jiangnan Cui
           * @Date 2022/4/8 11:00
           */
          @Test
          public void insertTest(){
              SqlSession sqlSession = null;
              try {
                  sqlSession = MybatisUtil.getSqlSession();
                  StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
                  studentDAO.insert(new Student(4,"喜洋洋",6));
                  sqlSession.commit();
              } catch (Exception e) {
                  e.printStackTrace();
                  sqlSession.rollback();
              }
              sqlSession.close();
          }
      }

### 6.insert、update细节 ###

**配套视频：**[【编程不良人】Mybatis 从入门到精通\_哔哩哔哩\_bilibili][Mybatis _bilibili 3]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 2][]**注意：**

Mybatis框架在执行插入（insert）、更新（update）操作时，默认不允许插入或修改NULL值到数据库中，解决办法：

1.  尝试设置一个值进去，实质并没有解决问题
2.  再取值语句中设置一个jdbcType类型
    
    例如：\#\{age,jdbcType=INTEGER\}
    
    \#\{name,jdbcType=VARCHAR\}
    
    \#\{birthday,jdbcType=DATE\}

**附：Mybatis中支持的jdbcType**![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

### 7. 总结![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 4][] ###

[Mybatis _bilibili]: https://www.bilibili.com/video/BV1EE411W7Q1?p=3
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/6b1d1f5f0499488abc70fe2fef135b13.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQ29kZXJfQ3Vp,size_20,color_FFFFFF,t_70,g_se,x_16
[632e459c2cc040b9a756326fe07bb25b.png]: https://img-blog.csdnimg.cn/632e459c2cc040b9a756326fe07bb25b.png
[Mybatis _bilibili 1]: https://www.bilibili.com/video/BV1EE411W7Q1?p=4
[Mybatis _bilibili 2]: https://www.bilibili.com/video/BV1EE411W7Q1?p=5
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/776a124757b34b35b5364728deac5531.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQ29kZXJfQ3Vp,size_20,color_FFFFFF,t_70,g_se,x_16
[Mybatis _bilibili 3]: https://www.bilibili.com/video/BV1EE411W7Q1?p=6
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/6856a07de79041c389fcf9992e93d8b4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQ29kZXJfQ3Vp,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/49e4f99356034336ae42771569933bbe.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQ29kZXJfQ3Vp,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/50e0c526e6db47fab4d9b86687d81f1e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQ29kZXJfQ3Vp,size_20,color_FFFFFF,t_70,g_se,x_16