Ibatis 简明教程

iBATIS - Dynamic SQL

动态 SQL 是 iBATIS 中一项非常强大的功能。有时，您不得不在根据您的参数对象的状态更改 WHERE 子句条件。在这种情况下，iBATIS 提供了一组动态 SQL 标签，可在映射的语句中使用，以增强 SQL 的可重用性和灵活性。

Dynamic SQL is a very powerful feature of iBATIS. Sometimes you have to change the WHERE clause criterion based on your parameter object’s state. In such situations, iBATIS provides a set of dynamic SQL tags that can be used within mapped statements to enhance the reusability and flexibility of the SQL.

所有逻辑都使用一些其他标签放入 .XML 文件中。以下是一个 SELECT 语句将以两种方式工作的示例：

All the logic is put in .XML file using some additional tags. Following is an example where the SELECT statement would work in two ways −

  1. If you pass an ID, then it would return all the records corresponding to that ID.

  2. Otherwise, it would return all the records where employee ID is set to NULL.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Employee">

   <select id="findByID" resultClass="Employee">
      SELECT * FROM EMPLOYEE

      <dynamic prepend="WHERE ">
         <isNull property="id">
            id IS NULL
         </isNull>

         <isNotNull property="id">
            id = #id#
         </isNotNull>
      </dynamic>

   </select>
</sqlMap>

您可以按照以下方式使用 <isNotEmpty> 标记检查条件。在此，仅当传递的属性不为空时，才会添加条件。

You can check a condition using the <isNotEmpty> tag as follows. Here a condition would be added only when a passed property is not empty.

..................
<select id="findByID" resultClass="Employee">
   SELECT * FROM EMPLOYEE

   <dynamic prepend="WHERE ">
      <isNotEmpty property="id">
         id = #id#
      </isNotEmpty>
   </dynamic>

</select>
..................

如果您想要一个查询，其中我们可以选择一个 id 和/或一个 Employee 的第一个名称，您的 SELECT 语句如下 −

If you want a query where we can select an id and/or the first name of an Employee, your SELECT statement would be as follows −

..................
<select id="findByID" resultClass="Employee">
   SELECT * FROM EMPLOYEE

   <dynamic prepend="WHERE ">
      <isNotEmpty prepend="AND" property="id">
         id = #id#
      </isNotEmpty>

      <isNotEmpty prepend="OR" property="first_name">
         first_name = #first_name#
      </isNotEmpty>
   </dynamic>
</select>
..................

Dynamic SQL Example

以下示例显示了如何使用动态 SQL 编写 SELECT 语句。考虑在 MySQL 中，我们有以下 EMPLOYEE 表 −

The following example shows how you can write a SELECT statement with dynamic SQL. Consider, we have the following EMPLOYEE table in MySQL −

CREATE TABLE EMPLOYEE (
   id INT NOT NULL auto_increment,
   first_name VARCHAR(20) default NULL,
   last_name  VARCHAR(20) default NULL,
   salary     INT  default NULL,
   PRIMARY KEY (id)
);

让我们假设此表只有一个记录，如下所示 −

Let’s assume this table has only one record as follows −

mysql> select * from EMPLOYEE;
+----+------------+-----------+--------+
| id | first_name | last_name | salary |
+----+------------+-----------+--------+
|  1 | Zara       | Ali       |   5000 |
+----+------------+-----------+--------+
1 row in set (0.00 sec)

Employee POJO Class

为了执行读取操作，让我们在 Employee.java 中创建一个 Employee 类，如下所示 −

To perform read operation, let us have an Employee class in Employee.java as follows −

public class Employee {
   private int id;
   private String first_name;
   private String last_name;
   private int salary;

   /* Define constructors for the Employee class. */
   public Employee() {}

   public Employee(String fname, String lname, int salary) {
      this.first_name = fname;
      this.last_name = lname;
      this.salary = salary;
   }

   /* Here are the method definitions */
   public int getId() {
      return id;
   }

   public String getFirstName() {
      return first_name;
   }

   public String getLastName() {
      return last_name;
   }

   public int getSalary() {
      return salary;
   }

} /* End of Employee */

Employee.xml File

为了使用 iBATIS 定义 SQL 映射语句，我们会在 Employee.xml 中添加以下修改后的 <select> 标记，并在该标记定义内部，我们将定义一个 "id"，它将在 IbatisReadDy.java 中用于对数据库执行动态 SQL SELECT 查询。

To define SQL mapping statement using iBATIS, we would add the following modified <select> tag in Employee.xml and inside this tag definition, we would define an "id" which will be used in IbatisReadDy.java for executing Dynamic SQL SELECT query on database.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Employee">
   <select id="findByID" resultClass="Employee">
      SELECT * FROM EMPLOYEE

      <dynamic prepend="WHERE ">
         <isNotNull property="id">
            id = #id#
         </isNotNull>
      </dynamic>

   </select>
</sqlMap>

上面的 SELECT 语句将以两种方式工作 −

The above SELECT statement would work in two ways −

  1. If you pass an ID, then it returns records corresponding to that ID Otherwise, it returns all the records.

IbatisReadDy.java File

此文件具有应用程序级别的逻辑，用于从 Employee 表中读取条件记录 −

This file has application level logic to read conditional records from the Employee table −

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.io.*;
import java.sql.SQLException;
import java.util.*;

public class IbatisReadDy{
   public static void main(String[] args) throws IOException,SQLException{

      Reader rd=Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlMapClient smc=SqlMapClientBuilder.buildSqlMapClient(rd);

      /* This would read all records from the Employee table.*/
      System.out.println("Going to read records.....");
      Employee rec = new Employee();
      rec.setId(1);

      List <Employee> ems = (List<Employee>)
         smc.queryForList("Employee.findByID", rec);
      Employee em = null;

      for (Employee e : ems) {
         System.out.print("  " + e.getId());
         System.out.print("  " + e.getFirstName());
         System.out.print("  " + e.getLastName());
         System.out.print("  " + e.getSalary());
         em = e;
         System.out.println("");
      }
      System.out.println("Records Read Successfully ");
   }
}

Compilation and Run

以下是编译和运行上述软件的步骤。在继续编译和执行之前，请确保已正确设置 PATH 和 CLASSPATH。

Here are the steps to compile and run the above mentioned software. Make sure you have set PATH and CLASSPATH appropriately before proceeding for compilation and execution.

  1. Create Employee.xml as shown above.

  2. Create Employee.java as shown above and compile it.

  3. Create IbatisReadDy.java as shown above and compile it.

  4. Execute IbatisReadDy binary to run the program.

您将得到以下结果，并且会从 EMPLOYEE 表中读取一条记录。

You would get the following result, and a record would be read from the EMPLOYEE table.

Going to read records.....
   1  Zara  Ali  5000
Record Reads Successfully

通过传递 null 作为 smc.queryForList("Employee.findByID", null) 来尝试上面的示例。

Try the above example by passing null as smc.queryForList("Employee.findByID", null).

iBATIS OGNL Expressions

iBATIS 提供了强大的基于 OGNL 的表达式，以消除大多数其他元素。

iBATIS provides powerful OGNL based expressions to eliminate most of the other elements.

  1. if Statement

  2. choose, when, otherwise Statement

  3. where Statement

  4. foreach Statement

The if Statement

在动态 SQL 中最常见的事情是有条件地包含 where 子句的一部分。例如 −

The most common thing to do in dynamic SQL is conditionally include a part of a where clause. For example −

<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">
   SELECT * FROM BLOG
   WHERE state = 'ACTIVE.

   <if test="title != null">
      AND title like #{title}
   </if>

</select>

该语句提供了一个可选的文本搜索类型功能。如果您没有传递标题，那么它会返回所有活动的博客。但是，如果您确实传递了一个标题，它将使用给定的 like 条件查找标题。

This statement provides an optional text search type of functionality. If you pass in no title, then all active Blogs are returned. But if you do pass in a title, it will look for a title with the given like condition.

您可以包含多个 if 条件，如下所示 −

You can include multiple if conditions as follows −

<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">
   SELECT * FROM BLOG
   WHERE state = 'ACTIVE.

   <if test="title != null">
      AND title like #{title}
   </if>

   <if test="author != null">
      AND author like #{author}
   </if>

</select>

The choose, when, and otherwise Statements

iBATIS 提供了一个 choose 元素，类似于 Java 的 switch 语句。它有助于在许多选项中只选择一个 case。

iBATIS offers a choose element which is similar to Java’s switch statement. It helps choose only one case among many options.

以下示例只根据标题进行搜索（如果提供了标题的话），然后根据给定的作者进行搜索。如果没有提供标题或作者，它只返回精选的博客 −

The following example would search only by title if one is provided, then only by author if one is provided. If neither is provided, it returns only featured blogs −

<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">
   SELECT * FROM BLOG
   WHERE state = 'ACTIVE.

   <choose>
      <when test="title != null">
         AND title like #{title}
      </when>

      <when test="author != null and author.name != null">
         AND author like #{author}
      </when>

      <otherwise>
         AND featured = 1
      </otherwise>
   </choose>

</select>

The where Statement

查看我们之前的示例，看看没有满足任何条件时会发生什么。您最终将得到一条类似这样的 SQL 语句 −

Take a look at our previous examples to see what happens if none of the conditions are met. You would end up with an SQL that looks like this −

SELECT * FROM BLOG
WHERE

这会失败，但 iBATIS 借助一个简单的更改就提供了一个简单的解决方案，一切都正常运行 −

This would fail, but iBATIS has a simple solution with one simple change, everything works fine −

<select id="findActiveBlogLike" parameterType="Blog" resultType="Blog">
   SELECT * FROM BLOG

   <where>
      <if test="state != null">
         state = #{state}
      </if>

      <if test="title != null">
         AND title like #{title}
      </if>

      <if test="author != null>
         AND author like #{author}
      </if>
   </where>

</select>

where 元素只在包含它的标记返回任何内容时才插入 WHERE。此外，如果该内容以 AND 或 OR 开头，它知道要剔除它。

The where element inserts a WHERE only when the containing tags return any content. Furthermore, if that content begins with AND or OR, it knows to strip it off.

The foreach Statement

foreach 元素允许你指定一个集合并声明可以在元素内部使用的项目和索引变量。

The foreach element allows you to specify a collection and declare item and index variables that can be used inside the body of the element.

它还允许你指定开始字符串和结束字符串以及添加一个分隔符以置于迭代之间。你可以按照如下方法构建一个 IN 条件 −

It also allows you to specify opening and closing strings, and add a separator to place in between iterations. You can build an IN condition as follows −

<select id="selectPostIn" resultType="domain.blog.Post">
   SELECT *
   FROM POST P
   WHERE ID in

   <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
      #{item}
   </foreach>

</select>