MyBatis

Mybatis上手教程–增删改查

一 增删改查说明

前面的例子中「Mybatis上手教程–面向接口编程」,我们通过定义EmployeeMapper这个接口,并定义了一个查询方法:

package com.knockatdatabase.dao;

import com.knockatdatabase.Employee;

/**
* @Author:asher
* @Date:2021/10/2 10:24
* @Description:com.knockatdatabase.dao
* @Version:1.0
*/
public interface EmployeeMapper {
   Employee getEmployeeById(Integer id);
}

然后,在SQL mapper文件里把namespace映射到该接口的全类名,SQL id映射该接口的方法名,最后在测试类中获取SqlSession对象之后,调用getMapper()方法,并通过传入该参数的实现类的对象,进而通过反射的方式,调用该接口中的方法getEmployeeById(),并最终通过将接口和SQL mapper文件绑定的方式,来实现Java代码和SQL解耦,并最终实现的。

二 添加方法addEmployee()的流程

1 在接口中添加addEmployee()的方法
package com.knockatdatabase.dao;

import com.knockatdatabase.Employee;

/**
* @Author:asher
* @Date:2021/10/2 10:24
* @Description:com.knockatdatabase.dao
* @Version:1.0
*/
public interface EmployeeMapper {
   Employee getEmployeeById(Integer id);

   void insertEmployee(Employee employee);
}

方法返回值是void,参数是一个Employee对象。

2 在SQL mapper文件中添加映射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="com.knockatdatabase.dao.EmployeeMapper">
<insert id="insertEmployee" parameterType="com.knockatdatabase.Employee">
  insert into tbl_employee(last_name,gender,email)
  values (#{lastName},#{gender},#{mail})
</insert>
<select id="getEmployeeById" resultType="com.knockatdatabase.Employee">
  select id,last_name ,gender,email as mail from tbl_employee where id = #{id}
 </select>
</mapper>

其中,由于后端MySQL数据库中设定的id是自增主键,所以,这里的SQL中,可以直接忽略id字段。同时values()中,直接写#{},在大括号里直接写实体类Employee里的成员属性名即可。

问题,为什么直接写成员变量即可呢?Mybatis是怎么读取到该成员变量的值的呢?

3测试类中测试
@Test
   public void testInsert() {
       String resource = "mybatis-config.xml";
       Employee employee = new Employee(null, "Huang", "男", "hensi163@163.com");
       SqlSession sqlSession = null;
       try {
           InputStream inputStream = Resources.getResourceAsStream(resource);
           SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
           sqlSession = sqlSessionFactory.openSession();
           EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
           mapper.insertEmployee(employee);
           sqlSession.commit();
      } catch (IOException e) {
           e.printStackTrace();
      }
       finally {
           sqlSession.close();
      }
  }

思路还是一样的,先读取配置文件,通过SqlSessionFactoryBuilder获取一个SqlSessionFactory的对象,然后调用openSession()方法,得到一个SqlSession的对象,注意:此时方法的返回值有2种类型,一种是autocommit的,一种是非自动提交事务的,那么后面执行完事务之后,就要显示的手工提交事务。

同时,该测试方法中,先new 一个Employee类的对象,此时要在Employee的Java bean中,添加无参、有参构造方法。且,此处的id赋值为NULL,因为id要通过MySQL数据库自身的auto increment来生成。

后面,调用mapper.insertEmployee(employee);把它当做参数传递进去。

最后,不要忘记关闭SqlSession对象。

4 验证测试结果
[mysql@ppasdev ~]$ mysql  -umybatis_project -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2576
Server version: 5.6.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mybatis_project;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from tbl_employee;
+----+-----------+--------+-------------------+
| id | last_name | gender | email             |
+----+-----------+--------+-------------------+
|  1 | Huang     | 1      | 3dian14@gmail.com |
|  2 | Huang     | ?      | hensi163@163.com  |
+----+-----------+--------+-------------------+
2 rows in set (0.00 sec)

mysql>

可以看到,数据库中的确多了一条记录,但是有个小问题,性别列呈现的是乱码。

5 解决中文乱码问题

解决该问题的方法,可以是在数据源的配置里面,加上“?useUnicode=true&characterEncoding=utf-8“。

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://172.16.11.35:3306/mybatis_project?useUnicode=true&characterEncoding=utf-8
jdbc.username=mybatis_project
jdbc.password=mybatis_project

如果是直接配置在mybatis-config.xml文件里的话,则需要转义一下&字符,用这种方式:?useUnicode=true&characterEncoding=utf-8

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://172.16.11.35:3306/mybatis_project?useUnicode=true&amp;characterEncoding=utf-8
jdbc.username=mybatis_project
jdbc.password=mybatis_project

重新测试,就不再有乱码了:

mysql> select * from tbl_employee;
+----+-----------+--------+-------------------+
| id | last_name | gender | email             |
+----+-----------+--------+-------------------+
|  1 | Huang     | 1      | 3dian14@gmail.com |
|  2 | Huang     | ?      | hensi163@163.com  |
|  3 | Huang     | 男     | hensi163@163.com  |
+----+-----------+--------+-------------------+
3 rows in set (0.00 sec)

mysql>
6 补充Mybatis使用自增主键

Mybatis支持对MySQL自增主键,就是在SQL mapper文件中,这样配置额外的2个属性:useGeneratedKeys=”true” keyProperty=”id”,表示支持自增主键,同时把这个自增主键映射的Java bean的id字段上。

<?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="com.knockatdatabase.dao.EmployeeMapper">
<insert id="insertEmployee" parameterType="com.knockatdatabase.Employee" useGeneratedKeys="true" keyProperty="id">
  insert into tbl_employee(last_name,gender,email)
  values (#{lastName},#{gender},#{mail})
</insert>
<select id="getEmployeeById" resultType="com.knockatdatabase.Employee">
  select id,last_name ,gender,email as mail from tbl_employee where id = #{id}
 </select>
</mapper>

这样的话,就可以在Java测试类的代码中,通过Java代码获取到MySQL自动帮我们生成的自增主键值:System.out.println(employee.getId());。

三更新方法流程

1 接口中添加更新方法

方法返回值为Boolean。

package com.knockatdatabase.dao;

import com.knockatdatabase.Employee;

/**
* @Author:asher
* @Date:2021/10/2 10:24
* @Description:com.knockatdatabase.dao
* @Version:1.0
*/
public interface EmployeeMapper {
   Employee getEmployeeById(Integer id);

   void insertEmployee(Employee employee);

   Boolean updateEmployee(Employee employee);
}
2 SQL mapper文件中添加映射
<?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="com.knockatdatabase.dao.EmployeeMapper">
<insert id="insertEmployee" parameterType="com.knockatdatabase.Employee">
  insert into tbl_employee(last_name,gender,email)
  values (#{lastName},#{gender},#{mail})
</insert>
   <update id="updateEmployee">
      update tbl_employee set last_name=#{lastName},gender=#{gender},email=#{mail}
      where id=#{id}
   </update>
   <select id="getEmployeeById" resultType="com.knockatdatabase.Employee">
  select id,last_name ,gender,email as mail from tbl_employee where id = #{id}
 </select>
</mapper>
3 编写测试方法
@Test
public void testUpdate() {
   Employee employee = new Employee(1, "黄伟", "男", "huangwei@knockatdatabase.com");
   SqlSession sqlSession = null;
   try{
       String resource = "mybatis-config.xml";
       InputStream inputStream = Resources.getResourceAsStream(resource);
       SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
       sqlSession = sqlSessionFactory.openSession();
       EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
       Boolean updateEmployee = mapper.updateEmployee(employee);
       System.out.println("Update successfully ? "+updateEmployee);
       sqlSession.commit();
  } catch (IOException e) {
       e.printStackTrace();
  } finally {
       sqlSession.close();
  }
}
//执行结果:
Update successfully ? true
4 数据库验证
mysql> select * from tbl_employee;
+----+-----------+--------+------------------------------+
| id | last_name | gender | email                        |
+----+-----------+--------+------------------------------+
|  1 | 黄伟      | 男     | huangwei@knockatdatabase.com |
|  2 | Huang     | ?      | hensi163@163.com             |
|  3 | Huang     | 男     | hensi163@163.com             |
+----+-----------+--------+------------------------------+
3 rows in set (0.00 sec)

mysql>

可以看到,前面添加的那条记录,已经被更新为这条新new出来的对象。

这就是用一个对象去更新另外一个对象的场景。说实话,我还是不太明白或者不能足够理解这种场景。回头找同事问问。

四删除方法流程

1接口中定义删除方法

返回值为Boolean:

package com.knockatdatabase.dao;

import com.knockatdatabase.Employee;

/**
* @Author:asher
* @Date:2021/10/2 10:24
* @Description:com.knockatdatabase.dao
* @Version:1.0
*/
public interface EmployeeMapper {
   Employee getEmployeeById(Integer id);

   void insertEmployee(Employee employee);

   Boolean updateEmployee(Employee employee);

   Boolean deleteEmployeeById(Integer id);
}
2 SQL mapper文件添加映射
<?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="com.knockatdatabase.dao.EmployeeMapper">
<insert id="insertEmployee" parameterType="com.knockatdatabase.Employee">
  insert into tbl_employee(last_name,gender,email)
  values (#{lastName},#{gender},#{mail})
</insert>
   <update id="updateEmployee">
      update tbl_employee set last_name=#{lastName},gender=#{gender},email=#{mail}
      where id=#{id}
   </update>
   <delete id="deleteEmployeeById">
      delete from tbl_employee where id=#{id}
   </delete>
   <select id="getEmployeeById" resultType="com.knockatdatabase.Employee">
  select id,last_name ,gender,email as mail from tbl_employee where id = #{id}
 </select>
</mapper>
3 测试方法
@Test
public void testDelete() {
   SqlSession sqlSession = null;
   try{
       String resource = "mybatis-config.xml";
       InputStream inputStream = Resources.getResourceAsStream(resource);
       SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
       sqlSession = sqlSessionFactory.openSession();
       EmployeeMapper mapper = (EmployeeMapper) sqlSession.getMapper(Class.forName("com.knockatdatabase.dao.EmployeeMapper"));
       Boolean deleteEmployeeById = mapper.deleteEmployeeById(3);
       System.out.println("删除成功吗? "+deleteEmployeeById);
       sqlSession.commit();
  } catch (IOException | ClassNotFoundException e) {
       e.printStackTrace();
  }finally {
       sqlSession.close();
  }
}
//执行结果:
删除成功吗? true
4 数据库验证
mysql> select * from tbl_employee;
+----+-----------+--------+------------------------------+
| id | last_name | gender | email                        |
+----+-----------+--------+------------------------------+
|  1 | 黄伟      | 男     | huangwei@knockatdatabase.com |
|  2 | Huang     | ?      | hensi163@163.com             |
+----+-----------+--------+------------------------------+
2 rows in set (0.00 sec)

mysql>

五小结

  • 通过基础的增删改查来联系使用mybatis和数据库交互;
  • 体会到定义接口,定义方法,将接口和SQL mapper绑定在一起使用;
  • 体会如何通过反射类获取接口的实现类的对象,最后再通过这个对象来调用接口中的方法;而接口中的方法又和mybatis 的SQL mapper文件绑定,进而执行SQL语句,最后和数据库完成交互操作。
  • 但是,也有几个小问题,回头得跟同事多讨论,多强化自己在这方面的欠缺的知识,问题就是文章中的加黄颜色的部分;

留言

您的电子邮箱地址不会被公开。 必填项已用*标注