Mybatis 学习之路其三：编写sql

最新推荐文章于 2022-10-28 12:58:50 发布

amvft2324

最新推荐文章于 2022-10-28 12:58:50 发布

阅读量126

点赞数

文章标签： java 数据库 git

原文链接：http://www.cnblogs.com/JiKio/p/9724975.html

版权

一、利用 mysql 官网提供的测试数据

在第一个简单的程序中，我们已经写了一个简单的 sql，下面，我们要尝试写一些比较复杂的 sql。

为了能够有足够的数据够我们测试使用，我们可以从 mysql 的 github 里下载 mysql 提供的测试数据。具体操作如下：

首先，请确保安装 mysql 的虚拟机已经安装了Git，然后我们创建一个专门放置 github repositories 的文件夹，进入该文件夹运行命令：

git clone https://github.com/datacharmer/test_db.git

完成之后，该文件夹下，会多出一个 test_db 文件夹，接着我们进入到该文件夹，运行命令:

mysql -h 192.168.52.128 -u root -p < employees.sql

完成之后，继续运行命令验证：

mysql -h 192.168.52.128 -u root -p -t < test_employees_md5.sql

输出结果：

+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name   | expected_records | expected_crc                     |
+--------------+------------------+----------------------------------+
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name   | found_records    | found_crc                        |
+--------------+------------------+----------------------------------+
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | ok        |
| departments  | OK            | ok        |
| dept_manager | OK            | ok        |
| dept_emp     | OK            | ok        |
| titles       | OK            | ok        |
| salaries     | OK            | ok        |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:11         |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC     | OK     |
| count   | OK     |
+---------+--------+

然后，我们再 db.properties 里把数据库修改为 employees。

为了能够更清楚的了解程序运行中 sql 的执行，我们使用 log4j 来打印我们执行的 sql，具体配置信息参考：https://www.cnblogs.com/JiKio/p/9734384.html。

二、创建一个生产 SqlSession 的工具类

为了方面构造 SqlSession，我们新建一个 Utils 的包，并在里面创建一个 SqlSessionFactoryUtils 的工具类：

 1 package Utils;
 2 
 3 import java.io.IOException;
 4 import java.io.InputStream;
 5 
 6 import org.apache.ibatis.io.Resources;
 7 import org.apache.ibatis.session.SqlSession;
 8 import org.apache.ibatis.session.SqlSessionFactory;
 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10 
11 public class SqlSessionFactoryUtils {
12     private final static Class<SqlSessionFactoryUtils> LOCK = SqlSessionFactoryUtils.class;
13     private static SqlSessionFactory sqlSessionFactory = null;
14     private SqlSessionFactoryUtils() {}
15     public static SqlSessionFactory getSqlSessionFactory() {
16         synchronized(LOCK) {
17             if(sqlSessionFactory !=null ) {
18                 return sqlSessionFactory;
19             }
20             String resource = "mybatis-config.xml";
21             InputStream inputStream;
22             try {
23                 inputStream = Resources.getResourceAsStream(resource);
24                 sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
25             }catch(IOException e) {
26                 e.printStackTrace();
27                 return null;
28             }
29             return sqlSessionFactory;
30         }
31     }
32     public static SqlSession openSqlSession() {
33         if(sqlSessionFactory!=null) {
34             getSqlSessionFactory();
35         }
36         return sqlSessionFactory.openSession();
37     }
38 }

这个类是完成了上篇文章 SqlSessionTest 里创建 SqlSession 的过程，同样也是通过 Resources.getResourceAsStream 加载 mybatis-config.xml 来生成 SqlSessiongFactory 的。它的构造方法是 private 的，所以只能通过 openSession() 去获取一个固定的 sqlSession。

三、select 元素——查询语句

在 mybatis 里 select 元素是使用最频繁的，也是最强大的，首先，我们先看 select 元素有哪些配置：

元素	说明	备注
id	和 Mapper 的命名空间组合起来是唯一的，供mybatis 调用	如果不唯一，会报错
patameterType	类的全名，或者定义过的别名	一般表示传入参数的类型
resultType	类的全名，或者定义过的别名，在允许自动匹配的情况下，结果集将通过 java bean 的规范映射	不能和 resultMap 同时使用
resutlMap	自定义个映射集
flushCache	调用 sql 后，是否要求 mybatis 清空之间查询本地缓存和二级缓存	默认值为 false
useCache	启动二级缓存的开关，是否要求 mybatis 将此次结果缓存	默认值为 true
timeout	设置超时参数，超时将抛出异常，单位为秒	默认是 jdbc 提供的时间
fetchSize	获取记录的总条数设定
statementType	告诉 mybatis 使用哪个 JDBC 的 statement 工作，取值为 STATEMENT,PERPARED,CALLABLE	默认值是 PERPARED
resultSetType
databaseId	辨别数据库所用，本篇不说明
resultOrder
resultSets

查询语句的返回结果往往对应一个 POJO，所以，我们先创建一个 employee 的 model 类：

package employee.model;

import java.util.Date;

public class EmployeeModel {
    private int empNo;
    private Date birth_date;
    private String firstName;
    private String lastName;
    private String gender;
    private Date hireDate;
    public int getEmpNo() {
        return empNo;
    }
    public void setEmpNo(int empNo) {
        this.empNo = empNo;
    }
    public Date getBirth_date() {
        return birth_date;
    }
    public void setBirth_date(Date birth_date) {
        this.birth_date = birth_date;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    public Date getHireDate() {
        return hireDate;
    }
    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }
    
    public String toString() {
        return "empNo : " + empNo + " firstName : " + + " lastName : " + lastName;
    }
}

然后尝试从数据库获取一条信息：

先编写 dao 的接口文件：

package employee.dao;

import java.util.List;

import org.apache.ibatis.session.RowBounds;

import employee.model.EmployeeModel;

public interface EmployeeDao {
    
    //根据员工编号获取员工信息
    EmployeeModel getEmployeeByEmpNo(int id);
}

然后编写其对应的 mapper 文件：

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3   PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4   "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="employee.dao.EmployeeDao">
 6   <select id="getEmployeeByEmpNo" resultType="employee.model.EmployeeModel">
 7     select * from employees where emp_no = #{emp_no}
 8   </select>
 9 </mapper>

测试根据员工编号来获取员工信息：

SqlSession sqlSession = SqlSessionFactoryUtils.openSqlSession();         
        EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
        EmployeeModel employee = employeeDao.getEmployeeByEmpNo(10001);
        System.out.print(employee);

输出为：

empNo : 0 firstName : null lastName : null

这说明，我们的确获取到了一条记录，但是映射到 POJO 时出现了问题，为了避免这种个问题，我们最好为结果集和 POJO 指定明确的映射关系，而不是单纯的依赖 mybatis 的自动映射机制（可以通过合理的命名去实现自动映射，但是不推荐），映射代码如下，把它添加到 EmployeeMapper.xml 的 <mapper> 标签内即可：

<resultMap type = "employee.model.EmployeeModel" id = "employee">
    <id column = "emp_no" property = "empNo"/>
    <result column = "first_name" property = "firstName"/>
    <result column = "birth_date" property = "birthDate"/>
    <result column = "last_name" property = "lastName"/>
    <result column = "gender" property = "gender"/>
    <result column = "hire_date" property = "hireDate"/>
</resultMap>

这就是一个 Map，它把我们从数据库获取的结果集的每一列都和 POJO 的每个属性对应起来，mybatis 获取结果后，会自动创建一个对应的 POJO，然后把每一个属性通过 setter 的方式注入值。

  <select id="getEmployeeByEmpNo" resultMap="employee">
    select * from employees where emp_no = #{emp_no}
  </select>

用 resultMap 的 id 代表它，同时在 select 元素里不在使用 resultType 而是 resultMap。注意 resultType 表示返回的是一个 POJO，resultMap 表示返回的是自定义的 resultMap。

现在再运行主程序，输出为：

empNo : 10001 firstName : Georgi lastName : Facello

在这边，我们虽然传递了一个参数 empNo 但是在 mapper.xml 里并没有传入参数的配置，而我们依然成功的获取到了想要的信息，这说明 mybatis 是能够对参数进行自动匹配的。那么一个参数可以自动匹配，多个呢？将 getEmployeeByEmpNo 稍加修改：

//根据员工编号和姓氏来获取员工信息
EmployeeModel getEmployeeByFirstName(int id,String firstName);

<select id="getEmployeeByFirstName" resultType="employee.model.EmployeeModel">
    select * from employees where emp_no = #{emp_no} and first_name = #{first_name}
</select>

进行测试：

EmployeeModel employee1 = employeeDao.getEmployeeByFirstName(10001, "Georgi");
System.out.print(employee1);

抛出异常。

那么当需要多个参数时，应该如何去配置，有两种方法。

第一，通过注解来添加参数，首先是 dao:

//根据员工编号和姓氏来获取员工信息
EmployeeModel getEmployeeByFirstName(@Param("empNo")int id,@Param("firstName")String firstName);

然后再 Mapper.xml 里就可以根据括号里的名称去引用参数：

  <select id="getEmployeeByFirstName" resultType="employee.model.EmployeeModel">
    select * from employees where emp_no = #{empNo} and first_name = #{firstName}
  </select>

第二种，参数类型为 POJO

//根据员工对象获取员工信息
EmployeeModel getEmployeeByObj(EmployeeModel employee);

  <select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee">
    select * from employees where emp_no = #{empNo} and first_name = #{firstName}
  </select>

传入对象为参数是，我们可以通过 #{属性名} 来获取对象内对应属性的值。这种方式在 insert 语句中尤为常见。

第三种，参数类型为 map

这个和上述类似，区别在于将信息分装在 map 里而不是 POJO 里，当然它的 resultType 也要对应的改为 "map"。

获取单独的一条记录已经实现，那么如何获取多条呢？

一般将多条数据放置在一个 List 中，所以 dao 里的返回类型应该是 List<T>：

//获取员工列表(分页)
List<EmployeeModel> getEmployeeList(RowBounds rowBounds);

这边传入的参数是分页信息，通常我们会设置它两个属性，开始行 A ，和返回行数 B，返回的数据从 A 开始，一共 B 条数据。

Mapper:

  <select id="getEmployeeList" resultMap="employee">
    select * from employees
  </select>

在 Mapper 不需要对 rowBounds 参数做处理，mybatis 会自己处理。这里不难发现，dao 里我们返回的应该是 List，但是这里我们返回的事 employee。说明对于多条参数，mybatis 会自动将其封装到我们给定的容器里。

测试:

RowBounds rowBounds = new RowBounds(0,20);
        List<EmployeeModel> employees = employeeDao.getEmployeeList(rowBounds);
        for(EmployeeModel f : employees) {
            System.out.print(f);
        }

输出：

empNo : 10001 firstName : Georgi lastName : FacelloempNo : 10002 firstName : Bezalel lastName : SimmelempNo ...

在之前的 sql 语句里，我偷懒的使用了 select * 的方式去获取所有列，这样不会出错，但是不够优雅，又或者，我们有时候并不需要查询所有列。但是把列全部列出来很麻烦，所以，我们最好能的只列出 1 次，以后都可以复用。<sql> 元素可以帮我们实现。

<sql> 简而言之就是给一串 sql 设置一个简短的名称，然后以后复用这个 sql 时，使用它的名称就行，比如，employee 的列：

<sql id = "employeeCols">
    emp_no,first_name,birth_date,last_name,gender,hire_date
</sql>

id 就是这串代码的名称，然后，我们就可以用这个 id 去写 sql 了，比如：

<select id="getEmployeeList" resultMap="employee">
    select <include refid = "employeeCols" /> from employees
</select>

引用的方式为：<include refid = "{我们命名的名称}" />

四、Insert 语句

Insert 没有 select 那么全面，只需要把语句放在 <insert> 元素内即可，而插入值可以通过传入参数的方式去实现，这点在 select 里也说明了，所以 insert 这里不在赘述。

在使用 Insert 时，可能需要注意的是主键的问题。

主键回填：

主键回填时值，我们插入数据之后，这条数据的主键是由 mysql 自动递增形成的，而我们希望能够获取到这个值。再使用一次 select 显然太麻烦，我们可以利用 insert 的 userGenerateedKeys 属性来实现，这个属性默认是 false 的，当打开之后，insert 语句会通过 jdbc 的 getGeneratedKes 方法来获取到自动生成的主键（jdbc 不支持则无效）。获取到值之后，还需要指定，把值放到 POJO 的那个属性里。这就需要我们定义 insert 的 keyProperty 属性。由于我们的测试库没有自增的字段，所以不在这里做测试。

假设，我们 employee 的 emp_no 是 mysql 自动自增来生成的，在 java 里应该是这样：

EmployeeModel model = new EmployeeModel();
        {
            //设置除了 emp_no 以外的属性值
            ...
        }
        employeeDao.insert(model)

记住这个 model，它现在的 emp_no 是空的，但是 insert 之后，它会被设置成新生成的主键。

Mapper:

  <insert id = "insert" parameterType = "employee.model.EmployeeModel"
          useGenerateKeys = "true" keyProperty = "emp_no">
          insert into employees(...) values (#{},#{}...)
  </insert>

另外，我们也会遇到这样的情况，employee 的 emp_no 设置为当前 emp_no 的最大值 + 1，并且我不想先去 select 这个最大值。这里我们可以是用 <selectKey> 元素。

以下是示例：

dao：

//插入员工信息
void insertEmployee(EmployeeModel model);

Mapper:

<insert id = "insertEmployee" parameterType = "employee.model.EmployeeModel">
    <selectKey keyProperty = "empNo" resultType="int" order = "BEFORE">
      select if(max(emp_no) = null,1,max(emp_no) + 1) from employees
    </selectKey>
    insert into employees(emp_no,birth_date,first_name,last_name,gender,hire_date)
    values(#{empNo},#{birthDate},#{firstName},#{lastName},#{gender},#{hireDate})
  </insert>

五、动态sql

动态 sql 最常用的部分就是多条件查询时使用 <if> 去判断查询条件，查询一个员工的方式除了根据员工号查询，还可以根据姓名，性别、入职日期以及它们的组合。为了省去写多个方法和 sql 的麻烦，我们通常会使用 <if> 去判断处哪些条件是有真实值传过来的，然后把这些加入到筛选条件里。

以员工查询为例，我们原来传入的查询参数是 emp_no，但是现在我们传入一个 employee 对象，然后只要该对象属性是有值得，则作为查询的参数：

<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee">
    select * from employees where emp_no = #{empNo} and first_name = #{firstName}
  </select>

这是先前写过的一段代码，它只能满足固定的通过 empNo 和 firstName 进行查询。现在用 <if> 对其修改：

<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee">
    select <include refid = "employeeCols" /> from employees 
    <where>
      <if test="empNo != null">
        and emp_no = #{empNo}
      </if>
      <if test="firstName != null">
        and first_name = #{firstName}
      </if>
      <if test="lastName != null">
        and last_name = #{lastName}
      </if>
    </where>
  </select>

test = “” 双引号里面是放的判断语句，用来配合 <if> 使用，<where> 的作用是，如果所有<if> 都判断为空，则不在 sql 后面跟 where 语句。如果有一个不是空，才会有 where。避免了类似 "select * from table where" 这样的情况出现。又或者可以这样：

<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee">
    select <include refid = "employeeCols" /> from employees 
    where 1=1
      <if test="empNo != null">
        and emp_no = #{empNo}
      </if>
      <if test="firstName != null">
        and first_name = #{firstName}
      </if>
      <if test="lastName != null">
        and last_name = #{lastName}
      </if>
  </select>

根据使用习惯选择即可。

有 if 自然也有 switch 不过 mybatis 使用的是 choose 元素，下面是示例：

<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee">
    select <include refid = "employeeCols" /> from employees 
    where 1=1
    <choose>
      <when test="empNo != null">
        and emp_no = #{empNo}
      </when>
      <when test="firstName != null">
        and first_name = #{firstName}
      </when>
      <when test="lastName != null">
        and last_name = #{lastName}
      </when>
      <otherwise>
        and birth_date = #{birthDate}
      </otherwise>
    </choose>
  </select>

chosse when otherwise 对应 java 里的 switch case default，所以不难理解。

<where> 元素可以防止出现，where 多余的情况，<set> 元素则可以避免出现 , 多余的情况。

转载于:https://www.cnblogs.com/JiKio/p/9724975.html

amvft2324

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis 学习之路其三：编写sql

一、利用 mysql 官网提供的测试数据在第一个简单的程序中，我们已经写了一个简单的 sql，下面，我们要尝试写一些比较复杂的 sql。为了能够有足够的数据够我们测试使用，我们可以从 mysql 的 github 里下载 mysql 提供的测试数据。具体操作如下：首先，请确保安装 mysql 的虚拟机已经安装了Git，然后我们创建一个专门放置 github repositor...
复制链接

扫一扫