Ibatis 简明教程

iBATIS - Quick Guide

iBATIS - Overview

iBATIS 是一个持久化框架，它在 Java、.NET 和 Ruby on Rails 中自动执行 SQL 数据库和对象之间的映射。映射与应用程序逻辑相分离，将 SQL 语句封装在 XML 配置文件中。

iBATIS is a persistence framework which automates the mapping between SQL databases and objects in Java, .NET, and Ruby on Rails. The mappings are decoupled from the application logic by packaging the SQL statements in XML configuration files.

iBATIS 是一个轻量级的持久化 API，适合持久化 POJO（旧式纯 Java 对象）。

iBATIS is a lightweight framework and persistence API good for persisting POJOs( Plain Old Java Objects).

iBATIS 是一个众所周知的数据映射器，负责在类属性和数据库表的列之间映射参数和结果。

iBATIS is what is known as a data mapper and takes care of mapping the parameters and results between the class properties and the columns of the database table.

iBATIS 和诸如 Hibernate 等其他持久化框架之间的一个显著差异是 iBATIS 强调使用 SQL，而其他框架通常使用自定义查询语言，如 Hibernate 查询语言 (HQL) 或企业 JavaBean 查询语言 (EJB QL)。

A significant difference between iBATIS and other persistence frameworks such as Hibernate is that iBATIS emphasizes the use of SQL, while other frameworks typically use a custom query language such has the Hibernate Query Language (HQL) or Enterprise JavaBeans Query Language (EJB QL).

iBATIS Design Philosophies

iBATIS 采用以下设计理念：

iBATIS comes with the following design philosophies −

Simplicity − iBATIS is widely regarded as being one of the simplest persistence frameworks available today.
Fast Development − iBATIS does all it can to facilitate hyper-fast development.
Portability − iBATIS can be implemented for nearly any language or platform such as Java, Ruby, and C# for Microsoft .NET.
Independent Interfaces − iBATIS provides database-independent interfaces and APIs that help the rest of the application remain independent of any persistence-related resources.
Open source − iBATIS is free and an open source software.

Advantages of iBATIS

iBATIS 提供以下优势：

iBATIS offers the following advantages −

Supports stored procedures − iBATIS encapsulates SQL in the form of stored procedures so that business logic is kept out of the database, and the application is easier to deploy and test, and is more portable.
Supports inline SQL − No precompiler is needed, and you have full access to all of the features of SQL.
Supports dynamic SQL − iBATIS provides features for dynamically building SQL queries based on parameters.
Supports O/RM − iBATIS supports many of the same features as an O/RM tool, such as lazy loading, join fetching, caching, runtime code generation, and inheritance

iBATIS 在开发面向数据库应用程序时使用 JAVA 编程语言。在继续阅读之前，请确保您理解过程式和面向对象编程的基础知识，比如控制结构、数据结构和变量、类、对象等。

iBATIS makes use of JAVA programming language while developing database oriented application. Before proceeding further, make sure that you understand the basics of procedural and object-oriented programming − control structures, data structures and variables, classes, objects, etc.

要详细了解 JAVA，您可以阅读我们的 JAVA Tutorial 。

To understand JAVA in detail you can go through our JAVA Tutorial.

iBATIS - Environment

在开始实际开发工作之前，您必须为 iBATIS 设置适当的环境。本章介绍如何为 iBATIS 设置工作环境。

You would have to set up a proper environment for iBATIS before starting off with actual development work. This chapter explains how to set up a working environment for iBATIS.

iBATIS Installation

执行以下简单步骤在您的 Linux 机器上安装 iBATIS −

Carry out the following simple steps to install iBATIS on your Linux machine −

Download the latest version of iBATIS from Download iBATIS.
Unzip the downloaded file to extract .jar file from the bundle and keep them in appropriate lib directory.
Set PATH and CLASSPATH variables at the extracted .jar file(s) appropriately.

$ unzip ibatis-2.3.4.726.zip
inflating: META-INF/MANIFEST.MF
   creating: doc/
   creating: lib/

   creating: simple_example/
   creating: simple_example/com/
   creating: simple_example/com/mydomain/
   creating: simple_example/com/mydomain/data/
   creating: simple_example/com/mydomain/domain/

   creating: src/

  inflating: doc/dev-javadoc.zip
  inflating: doc/user-javadoc.zip

  inflating: jar-dependencies.txt
  inflating: lib/ibatis-2.3.4.726.jar
  inflating: license.txt
  inflating: notice.txt
  inflating: release.txt

$pwd
/var/home/ibatis
$set PATH=$PATH:/var/home/ibatis/
$set CLASSPATH=$CLASSPATH:/var/home/ibatis\
      /lib/ibatis-2.3.4.726.jar

Database Setup

使用以下语法在任何 MySQL 数据库中创建“员工”表——

Create an EMPLOYEE table in any MySQL database using the following syntax −

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)
);

Create SqlMapConfig.xml

考虑以下内容——

Consider the following −

We are going to use JDBC to access the database testdb.
JDBC driver for MySQL is "com.mysql.jdbc.Driver".
Connection URL is "jdbc:mysql://localhost:3306/testdb".
We would use username and password as "root" and "root" respectively.
Our sql statement mappings for all the operations would be described in "Employee.xml".

根据上述假设，我们需要创建名为 SqlMapConfig.xml 的 XML 配置文件，其中包含以下内容。你需要在此配置中提供 iBatis 所需的所有配置——

Based on the above assumptions, we have to create an XML configuration file with name SqlMapConfig.xml with the following content. This is where you need to provide all configurations required for iBatis −

SqlMapConfig.xml 和 Employee.xml 两个文件都必须放在 class 路径中。现在，我们将保持 Employee.xml 文件内容为空，后续章节的内容将对其进行介绍。

It is important that both the files SqlMapConfig.xml and Employee.xml should be present in the class path. For now, we would keep Employee.xml file empty and we would cover its contents in subsequent chapters.

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

<sqlMapConfig>
   <settings useStatementNamespaces="true"/>

   <transactionManager type="JDBC">
      <dataSource type="SIMPLE">

         <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
         <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost:3306/testdb"/>
         <property name="JDBC.Username" value="root"/>
         <property name="JDBC.Password" value="root"/>

      </dataSource>
   </transactionManager>

   <sqlMap resource="Employee.xml"/>
</sqlMapConfig>

你还可以使用 SqlMapConfig.xml 文件设置以下可选属性——

You can set the following optional properties as well using SqlMapConfig.xml file −

<property name="JDBC.AutoCommit" value="true"/>
<property name="Pool.MaximumActiveConnections" value="10"/>
<property name="Pool.MaximumIdleConnections" value="5"/>
<property name="Pool.MaximumCheckoutTime" value="150000"/>
<property name="Pool.MaximumTimeToWait" value="500"/>
<property name="Pool.PingQuery" value="select 1 from Employee"/>
<property name="Pool.PingEnabled" value="false"/>

iBATIS - Create Operation

要执行任何使用iBATIS的创建、读取、更新和删除(CRUD)操作，您需要创建一个与该表对应的纯Java对象(POJO)类。此类描述将“建模”数据库表行的对象。

To perform any Create, Read, Update, and Delete (CRUD) operation using iBATIS, you would need to create a Plain Old Java Objects (POJO) class corresponding to the table. This class describes the objects that will "model" database table rows.

POJO 类具有实现执行所需操作的所有方法。

The POJO class would have implementation for all the methods required to perform desired operations.

我们假设在 MySQL 中具有以下 EMPLOYEE 表：

Let us assume 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)
);

Employee POJO Class

我们将 Employee.java 文件中的一个 Employee 类创建如下：

We would create an Employee class in Employee.java file 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;
   }
} /* End of Employee */

您可以定义用于在表中设置单个字段的方法。下一章将说明如何获取各个字段的值。

You can define methods to set individual fields in the table. The next chapter explains how to get the values of individual fields.

Employee.xml File

为了使用 iBATIS 定义 SQL 映射语句，我们将使用 <insert> 标记，在此标记定义内我们将定义一个“id”，该 id 将在 IbatisInsert.java 文件中用于在数据库上执行 SQL INSERT 查询。

To define SQL mapping statement using iBATIS, we would use <insert> tag and inside this tag definition, we would define an "id" which will be used in IbatisInsert.java file for executing SQL INSERT 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">

   <insert id="insert" parameterClass="Employee">
      insert into EMPLOYEE(first_name, last_name, salary)
      values (#first_name#, #last_name#, #salary#)

      <selectKey resultClass="int" keyProperty="id">
         select last_insert_id() as id
      </selectKey>
   </insert>

</sqlMap>

此处 parameterClass − 可根据要求采用字符串、int、float、double 或任何类对象作为值。在此示例中，在调用 SqlMap 类的 insert 方法时，我们将 Employee 对象作为参数传递。

Here parameterClass − could take a value as string, int, float, double, or any class object based on requirement. In this example, we would pass Employee object as a parameter while calling insert method of SqlMap class.

如果数据库表使用 IDENTITY、AUTO_INCREMENT 或 SERIAL 列，或者您已定义一个 SEQUENCE/GENERATOR，则可以在 <insert> 语句中使用 <selectKey> 元素以使用或返回该数据库生成的值。

If your database table uses an IDENTITY, AUTO_INCREMENT, or SERIAL column or you have defined a SEQUENCE/GENERATOR, you can use the <selectKey> element in an <insert> statement to use or return that database-generated value.

IbatisInsert.java File

此文件将具有插入 Employee 表中记录的应用程序级逻辑：

This file would have application level logic to insert records in 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 IbatisInsert{
   public static void main(String[] args)throws IOException,SQLException{
      Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

      /* This would insert one record in Employee table. */
      System.out.println("Going to insert record.....");
      Employee em = new Employee("Zara", "Ali", 5000);

      smc.insert("Employee.insert", em);

      System.out.println("Record Inserted 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.

Create Employee.xml as shown above.
Create Employee.java as shown above and compile it.
Create IbatisInsert.java as shown above and compile it.
Execute IbatisInsert binary to run the program.

您将获得以下结果，并且会在 EMPLOYEE 表中创建一条记录。

You would get the following result, and a record would be created in the EMPLOYEE table.

$java IbatisInsert
Going to insert record.....
Record Inserted Successfully

如果您检查 EMPLOYEE 表，它应显示以下结果：

If you check the EMPLOYEE table, it should display the following result −

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

iBATIS - Read Operation

我们在上一章中讨论了如何使用 iBATIS 对表执行 CREATE 操作。本章将说明如何使用 iBATIS 读取表。

We discussed, in the last chapter, how to perform CREATE operation on a table using iBATIS. This chapter explains how to read a table using iBATIS.

我们在 MySQL 中有以下“员工”表——

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)
);

此表仅有一个记录，如下所示 -

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, we would modify the 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> 标记，在该标记定义中，我们将定义一个将在 IbatisRead.java 文件中用于对数据库执行 SQL SELECT 查询的“id”。

To define SQL mapping statement using iBATIS, we would add <select> tag in Employee.xml file and inside this tag definition, we would define an "id" which will be used in IbatisRead.java file for executing 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">

   <insert id="insert" parameterClass="Employee">
      INSERT INTO EMPLOYEE(first_name, last_name, salary)
      values (#first_name#, #last_name#, #salary#)

      <selectKey resultClass="int" keyProperty="id">
         select last_insert_id() as id
      </selectKey>
   </insert>

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

</sqlMap>

此处，我们没有将 WHERE 子句与 SQL SELECT 语句一起使用。我们将在下一章中演示如何使用 WHERE 子句与 SELECT 语句一起使用，以及如何将值传递给该 WHERE 子句。

Here we did not use WHERE clause with SQL SELECT statement. We would demonstrate, in the next chapter, how you can use WHERE clause with SELECT statement and how you can pass values to that WHERE clause.

IbatisRead.java File

该文件具有从 Employee 表读取记录的应用程序级逻辑：

This file has application level logic to read 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 IbatisRead{
   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.....");
      List <Employee> ems = (List<Employee>)
         smc.queryForList("Employee.getAll", null);
      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.

Create Employee.xml as shown above.
Create Employee.java as shown above and compile it.
Create IbatisRead.java as shown above and compile it.
Execute IbatisRead binary to run the program.

你会获得以下结果，并且会从 EMPLOYEE 表中读取一条记录，如下所示：

You would get the following result, and a record would be read from the EMPLOYEE table as follows −

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

iBATIS - Update Operation

在上一章中，我们讨论了如何使用 iBATIS 对表执行 READ 操作。本章将解释如何使用 iBATIS 更新表中的记录。

We discussed, in the last chapter, how to perform READ operation on a table using iBATIS. This chapter explains how you can update records in a table using iBATIS.

我们在 MySQL 中有以下“员工”表——

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)
);

此表仅有一个记录，如下所示 -

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

要执行 udpate 操作，您需要如下修改 Employee.java 文件 -

To perform udpate operation, you would need to modify Employee.java file 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 required method definitions */
   public int getId() {
      return id;
   }

   public void setId(int id) {
      this.id = id;
   }

   public String getFirstName() {
      return first_name;
   }

   public void setFirstName(String fname) {
      this.first_name = fname;
   }

   public String getLastName() {
      return last_name;
   }
   public void setlastName(String lname) {
      this.last_name = lname;
   }

   public int getSalary() {
      return salary;
   }

   public void setSalary(int salary) {
      this.salary = salary;
   }

} /* End of Employee */

Employee.xml File

要使用 iBATIS 定义 SQL 映射语句，我们将在 Employee.xml 中添加 <update> 标签，并且在此标签定义内，我们将定义一个“id”，该 id 将在 IbatisUpdate.java 文件中用于对数据库执行 SQL UPDATE 查询。

To define SQL mapping statement using iBATIS, we would add <update> tag in Employee.xml and inside this tag definition, we would define an "id" which will be used in IbatisUpdate.java file for executing SQL UPDATE 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">

   <insert id="insert" parameterClass="Employee">
      INSERT INTO EMPLOYEE(first_name, last_name, salary)
      values (#first_name#, #last_name#, #salary#)

      <selectKey resultClass="int" keyProperty="id">
         select last_insert_id() as id
      </selectKey>
   </insert>

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

   <update id="update" parameterClass="Employee">
      UPDATE EMPLOYEE
      SET    first_name = #first_name#
      WHERE  id = #id#
   </update>

</sqlMap>

IbatisUpdate.java File

这个文件具有将记录更新到 Employee 表中的应用程序级逻辑 -

This file has application level logic to update records into 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 IbatisUpdate{
   public static void main(String[] args)
   throws IOException,SQLException{
      Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

      /* This would update one record in Employee table. */
      System.out.println("Going to update record.....");
      Employee rec = new Employee();
      rec.setId(1);
      rec.setFirstName( "Roma");
      smc.update("Employee.update", rec );
      System.out.println("Record updated Successfully ");

      System.out.println("Going to read records.....");
      List <Employee> ems = (List<Employee>)
         smc.queryForList("Employee.getAll", null);
      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.

Create Employee.xml as shown above.
Create Employee.java as shown above and compile it.
Create IbatisUpdate.java as shown above and compile it.
Execute IbatisUpdate binary to run the program.

您会得到以下结果，并且会更新 EMPLOYEE 表中的一条记录，然后从 EMPLOYEE 表中读取同一条记录。

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

Going to update record.....
Record updated Successfully
Going to read records.....
   1  Roma  Ali  5000
Records Read Successfully

iBATIS - Delete Operation

本章节描述如何使用 iBATIS 来从一张表中删除记录。

This chapter describes how to delete records from a table using iBATIS.

我们在 MySQL 中有以下“员工”表——

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)
);

假设这表有如下两条记录 −

Assume this table has two records as follows −

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

Employee POJO Class

为了执行删除操作，你不需要修改 Employee.java 文件。让我们先保留在上一章中的代码。

To perform delete operation, you do not need to modify Employee.java file. Let us keep it as it was in the last chapter.

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 required method definitions */
   public int getId() {
      return id;
   }

   public void setId(int id) {
      this.id = id;
   }

   public String getFirstName() {
      return first_name;
   }

   public void setFirstName(String fname) {
      this.first_name = fname;
   }

   public String getLastName() {
      return last_name;
   }

   public void setlastName(String lname) {
      this.last_name = lname;
   }

   public int getSalary() {
      return salary;
   }

   public void setSalary(int salary) {
      this.salary = salary;
   }

} /* End of Employee */

Employee.xml File

为了使用 iBATIS 来定义 SQL 映射语句，我们会在 Employee.xml 中添加 <delete> 标签，然后在这段标签定义中，我们会定义将被用来在 IbatisDelete.java 文件中执行 SQL DELETE 查询的一个“id”。

To define SQL mapping statement using iBATIS, we would add <delete> tag in Employee.xml and inside this tag definition, we would define an "id" which will be used in IbatisDelete.java file for executing SQL DELETE 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">

   <insert id="insert" parameterClass="Employee">
      INSERT INTO EMPLOYEE(first_name, last_name, salary)
      values (#first_name#, #last_name#, #salary#)

      <selectKey resultClass="int" keyProperty="id">
         select last_insert_id() as id
      </selectKey>
   </insert>

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

   <update id="update" parameterClass="Employee">
      UPDATE EMPLOYEE
      SET    first_name = #first_name#
      WHERE  id = #id#
   </update>

   <delete id="delete" parameterClass="int">
      DELETE FROM EMPLOYEE
      WHERE  id = #id#
   </delete>

</sqlMap>

IbatisDelete.java File

这个文件有应用程序级别的逻辑来从 Employee 表中删除记录 −

This file has application level logic to delete 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 IbatisDelete{
   public static void main(String[] args)
   throws IOException,SQLException{
      Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

      /* This would delete one record in Employee table. */
      System.out.println("Going to delete record.....");
      int id = 1;

      smc.delete("Employee.delete", id );
      System.out.println("Record deleted Successfully ");

      System.out.println("Going to read records.....");
      List <Employee> ems = (List<Employee>)
         smc.queryForList("Employee.getAll", null);
      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.

Create Employee.xml as shown above.
Create Employee.java as shown above and compile it.
Create IbatisDelete.java as shown above and compile it.
Execute IbatisDelete binary to run the program.

你会得到以下结果，一个 ID = 1的记录将从 EMPLOYEE 表中删除，而其余记录则会被读取。

You would get the following result, and a record with ID = 1 would be deleted from the EMPLOYEE table and the rest of the records would be read.

Going to delete record.....
Record deleted Successfully
Going to read records.....
   2  Roma  Ali  3000
Records Read Successfully

iBATIS - Result Maps

resultMap 元素是 iBATIS 中最重要的并且功能最强的元素。使用 iBATIS ResultMap 可以减少高达 90% 的 JDBC 编码，并且在某些情况下，它允许你执行 JDBC 甚至不支持的操作。

The resultMap element is the most important and powerful element in iBATIS. You can reduce up to 90% JDBC coding using iBATIS ResultMap and in some cases, it allows you to do things that JDBC does not even support.

ResultMaps 的设计使得对于简单的语句根本不需要明确的结果映射，而对于更复杂的语句，不需要指定超出绝对必要的范围来描述关系的信息。

The design of ResultMaps is such that simple statements don’t require explicit result mappings at all, and more complex statements require no more than is absolutely necessary to describe the relationships.

本章仅简单介绍 iBATIS ResultMaps。

This chapter provides just a simple introduction of iBATIS ResultMaps.

我们在 MySQL 中有以下“员工”表——

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)
);

此表有两个记录，如下所示：

This table has two records as follows −

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

Employee POJO Class

要使用 iBATIS ResultMap，你不必修改 Employee.java 文件。让我们将其保持在上一个章节中的样子。

To use iBATIS ResultMap, you do not need to modify the Employee.java file. Let us keep it as it was in the last chapter.

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 required method definitions */
   public int getId() {
      return id;
   }

   public void setId(int id) {
      this.id = id;
   }

   public String getFirstName() {
      return first_name;
   }

   public void setFirstName(String fname) {
      this.first_name = fname;
   }

   public String getLastName() {
      return last_name;
   }

   public void setlastName(String lname) {
      this.last_name = lname;
   }

   public int getSalary() {
      return salary;
   }

   public void setSalary(int salary) {
      this.salary = salary;
   }

} /* End of Employee */

Employee.xml File

在这里，我们将修改 Employee.xml 以引入 <resultMap></resultMap> 标记。此标记将具有一个 id，我们的 <select> 标记的 resultMap 属性需要它才能运行该 resultMap。

Here we would modify Employee.xml to introduce <resultMap></resultMap> tag. This tag would have an id which is required to run this resultMap in our <select> tag’s resultMap attribute.

<?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">

   <!-- Perform Insert Operation -->

   <insert id="insert" parameterClass="Employee">
      INSERT INTO EMPLOYEE(first_name, last_name, salary)
      values (#first_name#, #last_name#, #salary#)

      <selectKey resultClass="int" keyProperty="id">
         select last_insert_id() as id
      </selectKey>
   </insert>

   <!-- Perform Read Operation -->
   <select id="getAll" resultClass="Employee">
      SELECT * FROM EMPLOYEE
   </select>

   <!-- Perform Update Operation -->
   <update id="update" parameterClass="Employee">
      UPDATE EMPLOYEE
      SET    first_name = #first_name#
      WHERE  id = #id#
    </update>

   <!-- Perform Delete Operation -->
   <delete id="delete" parameterClass="int">
      DELETE FROM EMPLOYEE
      WHERE  id = #id#
   </delete>

   <!-- Using ResultMap -->
   <resultMap id="result" class="Employee">
      <result property="id" column="id"/>
      <result property="first_name" column="first_name"/>
      <result property="last_name" column="last_name"/>
      <result property="salary" column="salary"/>
   </resultMap>

   <select id="useResultMap" resultMap="result">
      SELECT * FROM EMPLOYEE
      WHERE id=#id#
   </select>

</sqlMap>

IbatisResultMap.java File

此文件具有使用 ResultMap 从 Employee 表读取记录的应用程序级逻辑：

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

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 IbatisResultMap{
   public static void main(String[] args)
   throws IOException,SQLException{
      Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

      int id = 1;
      System.out.println("Going to read record.....");
      Employee e = (Employee)smc.queryForObject ("Employee.useResultMap", id);

      System.out.println("ID:  " + e.getId());
      System.out.println("First Name:  " + e.getFirstName());
      System.out.println("Last Name:  " + e.getLastName());
      System.out.println("Salary:  " + e.getSalary());
      System.out.println("Record 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.

Create Employee.xml as shown above.
Create Employee.java as shown above and compile it.
Create IbatisResultMap.java as shown above and compile it.
Execute IbatisResultMap binary to run the program.

你会得到在 EMPLOYEE 表上执行的读取操作，其结果如下。

You would get the following result which is a read operation on the EMPLOYEE table.

Going to read record.....
ID:  1
First Name:  Zara
Last Name:  Ali
Salary:  5000
Record read Successfully

iBATIS - Stored Procedures

你可以使用 iBATIS 配置来调用存储过程。首先，让我们了解如何在 MySQL 中创建存储过程。

You can call a stored procedure using iBATIS configuration. First of all, let us understand how to create a stored procedure in MySQL.

我们在 MySQL 中有以下“员工”表——

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)
);

我们将在 MySQL 数据库中创建以下存储过程——

Let us create the following stored procedure in MySQL database −

DELIMITER $$

   DROP PROCEDURE IF EXISTS `testdb`.`getEmp` $$
   CREATE PROCEDURE `testdb`.`getEmp`
   (IN empid INT)

   BEGIN
      SELECT * FROM EMPLOYEE
      WHERE ID = empid;
   END $$

DELIMITER;

考虑 “员工” 表中有以下两条记录——

Let’s consider the EMPLOYEE table has two records as follows −

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

Employee POJO Class

若要使用存储过程，则无需修改 Employee.java 文件。让它保持上一章中的状态即可。

To use stored procedure, you do not need to modify the Employee.java file. Let us keep it as it was in the last chapter.

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 required method definitions */
   public int getId() {
      return id;
   }

   public void setId(int id) {
      this.id = id;
   }

   public String getFirstName() {
      return first_name;
   }

   public void setFirstName(String fname) {
      this.first_name = fname;
   }

   public String getLastName() {
      return last_name;
   }

   public void setlastName(String lname) {
      this.last_name = lname;
   }

   public int getSalary() {
      return salary;
   }

   public void setSalary(int salary) {
      this.salary = salary;
   }

} /* End of Employee */

Employee.xml File

在这里，我们将修改 Employee.xml 以引入 <procedure></procedure> 和 <parameterMap></parameterMap> 标记。<procedure></procedure> 标记有一个 ID，我们将在应用程序中使用此 ID 来调用存储过程。

Here we would modify Employee.xml to introduce <procedure></procedure> and <parameterMap></parameterMap> tags. Here <procedure></procedure> tag would have an id which we would use in our application to call the stored procedure.

<?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">

   <!-- Perform Insert Operation -->
   <insert id="insert" parameterClass="Employee">
      INSERT INTO EMPLOYEE(first_name, last_name, salary)
      values (#first_name#, #last_name#, #salary#)

      <selectKey resultClass="int" keyProperty="id">
         select last_insert_id() as id
      </selectKey>
   </insert>

   <!-- Perform Read Operation -->
   <select id="getAll" resultClass="Employee">
      SELECT * FROM EMPLOYEE
   </select>

   <!-- Perform Update Operation -->
   <update id="update" parameterClass="Employee">
      UPDATE EMPLOYEE
      SET    first_name = #first_name#
      WHERE  id = #id#
   </update>

   <!-- Perform Delete Operation -->
   <delete id="delete" parameterClass="int">
      DELETE FROM EMPLOYEE
      WHERE  id = #id#
   </delete>

   <!-- To call stored procedure. -->
   <procedure id="getEmpInfo" resultClass="Employee" parameterMap="getEmpInfoCall">
      { call getEmp( #acctID# ) }
   </procedure>

   <parameterMap id="getEmpInfoCall" class="map">
      <parameter property="acctID" jdbcType="INT" javaType="java.lang.Integer" mode="IN"/>
   </parameterMap>

</sqlMap>

IbatisSP.java File

此文件中包含应用程序级别的逻辑，用于使用 ResultMap 从“员工”表中读取员工的姓名——

This file has application level logic to read the names of the employees from the Employee table using ResultMap −

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 IbatisSP{
   public static void main(String[] args)
   throws IOException,SQLException{
      Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

      int id = 1;
      System.out.println("Going to read employee name.....");
      Employee e = (Employee) smc.queryForObject ("Employee.getEmpInfo", id);

      System.out.println("First Name:  " + e.getFirstName());
      System.out.println("Record name 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.

Create Employee.xml as shown above.
Create Employee.java as shown above and compile it.
Create IbatisSP.java as shown above and compile it.
Execute IbatisSP binary to run the program.

你会得到下列结果：

You would get the following result:

Going to read employee name.....
First Name:  Zara
Record name Successfully

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 −

如果您传递一个 ID，它将返回对应于该 ID 的所有记录。

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

否则，它将返回所有员工 ID 设置为 NULL 的记录。

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 −

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.

Create Employee.xml as shown above.
Create Employee.java as shown above and compile it.
Create IbatisReadDy.java as shown above and compile it.
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.

if Statement
choose, when, otherwise Statement
where Statement
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>

iBATIS - Debugging

在使用 iBATIS 时调试你的程序很容易。iBATIS 内置有日志记录支持，它与以下日志库协作，并按此顺序搜索它们。

It is easy to debug your program while working with iBATIS. iBATIS has built-in logging support and it works with the following logging libraries and searches for them in this order.

Jakarta Commons Logging (JCL).
Log4J
JDK logging

你可以将上面列出的任何库与 iBATIS 一起使用。

You can use any of the above listed libraries along with iBATIS.

Debugging with Log4J

假设你打算使用 Log4J 进行日志记录。在继续操作之前，你需要交叉检查以下几点 −

Assuming you are going to use Log4J for logging. Before proceeding, you need to cross-check the following points −

The Log4J JAR file (log4j-{version}.jar) should be in the CLASSPATH.
You have log4j.properties available in the CLASSPATH.

以下是 log4j.properties 文件。请注意，有些行已注释。如果你需要其他调试信息，你可以取消它们的注释。

Following is the log4j.properties file. Note that some of the lines are commented out. You can uncomment them if you need additional debugging information.

# Global logging configuration
log4j.rootLogger = ERROR, stdout

log4j.logger.com.ibatis = DEBUG

# shows SQL of prepared statements
#log4j.logger.java.sql.Connection = DEBUG

# shows parameters inserted into prepared statements
#log4j.logger.java.sql.PreparedStatement = DEBUG

# shows query results
#log4j.logger.java.sql.ResultSet = DEBUG

#log4j.logger.java.sql.Statement = DEBUG

# Console output
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = %5p [%t] − %m%n

你可以在 Apaches 网站上找到 Log4J 的完整文档− Log4J Documentation 。

You can find the complete documentation for Log4J from Apaches site − Log4J Documentation.

iBATIS Debugging Example

下面的 Java 类是一个非常简单的例子，它初始化 Log4J 日志库并随后将它用于 Java 应用程序。我们将使用前面提到的位于 CLASSPATH 中的属性文件。

The following Java class is a very simple example that initializes and then uses the Log4J logging library for Java applications. We would use the above-mentioned property file which lies in CLASSPATH.

import org.apache.log4j.Logger;

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 IbatisUpdate{
   static Logger log = Logger.getLogger(IbatisUpdate.class.getName());

   public static void main(String[] args)
   throws IOException,SQLException{
      Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

      /* This would insert one record in Employee table. */
      log.info("Going to update record.....");
      Employee rec = new Employee();
      rec.setId(1);
      rec.setFirstName( "Roma");
      smc.update("Employee.update", rec );
      log.info("Record updated Successfully ");

      log.debug("Going to read records.....");
      List <Employee> ems = (List<Employee>)
         smc.queryForList("Employee.getAll", null);
      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("");
      }
      log.debug("Records Read Successfully ");
   }
}

Compilation and Run

首先，确保在继续编译和执行之前你已适当地设置 PATH 和 CLASSPATH。

First of all, make sure you have set PATH and CLASSPATH appropriately before proceeding for compilation and execution.

Create Employee.xml as shown above.
Create Employee.java as shown above and compile it.
Create IbatisUpdate.java as shown above and compile it.
Create log4j.properties as shown above.
Execute IbatisUpdate binary to run the program.

你将得到以下结果。EMPLOYEE 表中的一条记录将被更新，然后从 EMPLOYEE 表中读取同一条记录。

You would get the following result. A record would be updated in the EMPLOYEE table and later, the same record would be read from the EMPLOYEE table.

DEBUG [main] - Created connection 28405330.
DEBUG [main] - Returned connection 28405330 to pool.
DEBUG [main] - Checked out connection 28405330 from pool.
DEBUG [main] - Returned connection 28405330 to pool.
   1  Roma  Ali  5000
   2  Zara  Ali  5000
   3  Zara  Ali  5000

Debug Methods

在上面的示例中，我们只使用了 info() 方法，但是你可以根据你的要求使用以下任何方法 −

In the above example, we used only info() method, however you can use any of the following methods as per your requirements −

public void trace(Object message);
public void debug(Object message);
public void info(Object message);
public void warn(Object message);
public void error(Object message);
public void fatal(Object message);

iBATIS - Hibernate

iBATIS和Hibernate之间有很大的不同。这两种解决方案在给定其特定领域时都能正常工作。如下所述建议使用iBATIS−

There are major differences between iBATIS and Hibernate. Both the solutions work well, given their specific domain. iBATIS is suggested in case −

You want to create your own SQL’s and you are willing to maintain them.
Your environment is driven by relational data model.
You have to work on existing and complex schemas.

如果环境是由对象模型驱动的并且需要自动生成SQL，则使用Hibernate。

Use Hibernate if the environment is driven by object model and needs to generate SQL automatically.

Difference between iBATIS and Hibernate

Hibernate和iBATIS都是业界可用的开源对象关系映射(ORM)工具。每种工具的使用取决于您使用它们的上下文。

Both Hibernate and iBATIS are open source Object Relational Mapping (ORM) tools available in the industry. Use of each of these tools depends on the context you are using them.

下表重点介绍了iBATIS和Hibernate之间的差异−

The following table highlights the differences between iBATIS and Hibernate −

iBATIS

Hibernate

iBATIS is simpler. It comes in a much smaller package size.

Hibernate generates SQL for you which means you don’t have to spend time on generating SQL.

iBATIS is flexible. It offers faster development time.

Hibernate is highly scalable. It provides a much more advanced cache.

iBATIS uses SQL which could be database dependent.

Hibernate uses HQL which is relatively independent of databases. It is easier to change db in Hibernate.

iBatis maps the ResultSet from JDBC API to your POJO Objets, so you don’t have to care about table structures.

Hibernate maps your Java POJO objects to the Database tables.

It is quite easy to use stored procedure in iBATIS.

Use of stored procedures is a little difficult in Hibernate.

Hibernate和iBATIS都受到SPRING框架的良好支持，因此选择其中一个应该不是问题。

Both Hibernate and iBATIS receive good support from the SPRING framework, so it should not be a problem to choose one of them.

iBATOR - Introduction

iBATOR 是一个 iBATIS 的代码生成器。iBATOR 会对一张或多张数据库表进行内省，并且生成可用于访问这些表的 iBATIS 工件。

iBATOR is a code generator for iBATIS. iBATOR introspects one or more database tables and generates iBATIS artifacts that can be used to access the tables.

之后你可以编写自己的自定义 SQL 代码或存储过程来满足你的需求。iBATOR 生成了以下工件 −

Later you can write your custom SQL code or stored procedure to meet your requirements. iBATOR generates the following artifacts −

SqlMap XML Files
Java Classes to match the primary key and fields of the table(s)
DAO Classes that use the above objects (optional)

iBATOR 可以作为一个独立的 JAR 文件，或作为一个 Ant 任务，或作为一个 Eclipse 插件来运行。本教程描述了从命令行生成 iBATIS 配置文件的最简单方式。

iBATOR can run as a standalone JAR file, or as an Ant task, or as an Eclipse plugin. This tutorial describes the simplest way of generating iBATIS configuration files from command line.

Download iBATOR

如果你使用的是除了 Eclipse 之外的 IDE，下载独立 JAR。独立 JAR 包含了一个运行 iBATOR 的 Ant 任务，或者你也可以从 Java 代码的命令行中运行 iBATOR。

Download the standalone JAR if you are using an IDE other than Eclipse. The standalone JAR includes an Ant task to run iBATOR, or you can run iBATOR from the command line of Java code.

You can download zip file from Download iBATOR.
You can check online documentation − iBATOR Documentation.

Generating Configuration File

要运行 iBATOR，请执行以下步骤：

To run iBATOR, follow these steps −

Step 1

适当创建并填写配置文件 ibatorConfig.xml。至少必须指定以下内容：

Create and fill a configuration file ibatorConfig.xml appropriately. At a minimum, you must specify −

A <jdbcConnection> element to specify how to connect to the target database.
A <javaModelGenerator> element to specify the target package and the target project for the generated Java model objects.
A <sqlMapGenerator> element to specify the target package and the target project for the generated SQL map files.
A <daoGenerator> element to specify the target package and the target project for the generated DAO interfaces and classes (you may omit the <daoGenerator> element if you don’t wish to generate DAOs).
At least one database <table> element

NOTE − 有关 iBATOR 配置文件的示例，请参阅 XML Configuration File Reference 页面。

NOTE − See the XML Configuration File Reference page for an example of an iBATOR configuration file.

Step 2

将该文件保存到便于查找的位置，例如：\temp\ibatorConfig.xml。

Save the file in a convenient location, for example, at: \temp\ibatorConfig.xml.

Step 3

现在从命令行运行 iBATOR，如下所示：

Now run iBATOR from the command line as follows −

java -jar abator.jar -configfile \temp\abatorConfig.xml -overwrite

这将告知 iBATOR 使用配置文件运行。它还将告知 iBATOR 覆盖同名的任何现有 Java 文件。如果您要保存任何现有 Java 文件，则省略 −overwrite 参数。

It will tell iBATOR to run using your configuration file. It will also tell iBATOR to overwrite any existing Java files with the same name. If you want to save any existing Java files, then omit the −overwrite parameter.

如果出现冲突，iBATOR 会以唯一名称保存新生成的文件。

If there is a conflict, iBATOR saves the newly generated file with a unique name.

运行 iBATOR 后，需要创建或修改标准 iBATIS 配置文件，以利用新生成代码。下一部分对此进行了说明。

After running iBATOR, you need to create or modify the standard iBATIS configuration files to make use of your newly generated code. This is explained in the next section.

Tasks After Running iBATOR

运行 iBATOR 后，需要创建或修改其他 iBATIS 配置工件。主要任务如下：

After you run iBATOR, you need to create or modify other iBATIS configuration artifacts. The main tasks are as follows −

Create or modify the SqlMapConfig.xml file.
Create or modify the dao.xml file (only if you are using the iBATIS DAO Framework).

下面详细描述每个任务：

Each task is described in detail below −

Updating the SqlMapConfig.xml File

iBATIS 使用名为 SqlMapConfig.xml 的 XML 文件来指定连接数据库的信息、事务管理方案以及 iBATIS 会话中使用的 SQL 映射 XML 文件。

iBATIS uses an XML file, commonly named SqlMapConfig.xml, to specify information for a database connection, a transaction management scheme, and SQL map XML files that are used in an iBATIS session.

iBATOR 无法为您创建此文件，因为它不知道您的执行环境。但是，此文件中的某些项直接关系到 iBATOR 生成的项。

iBATOR cannot create this file for you because it knows nothing about your execution environment. However, some of the items in this file relate directly to iBATOR generated items.

iBATOR 配置文件中的特定需求如下 -

iBATOR specific needs in the configuration file are as follows −

Statement namespaces must be enabled.
iBATOR generated SQL Map XML files must be listed.

例如，假设 iBATOR 生成了一个名为 MyTable_SqlMap.xml 的 SQL Map XML 文件，并且该文件已被放入您的项目的 test.xml 包中。SqlMapConfig.xml 文件应具有以下条目 -

For example, suppose iBATOR has generated an SQL Map XML file called MyTable_SqlMap.xml, and that the file has been placed in the test.xml package of your project. The SqlMapConfig.xml file should have these entries −

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

<sqlMapConfig>
   <!-- Statement namespaces are required for Abator -->
   <settings useStatementNamespaces="true" />

   <!-- Setup the transaction manager and data source that are
   appropriate for your environment
   -->

   <transactionManager type="...">
      <dataSource type="...">
      </dataSource>
   </transactionManager>

   <!-- SQL Map XML files should be listed here -->
   <sqlMap resource="test/xml/MyTable_SqlMap.xml" />

</sqlMapConfig>

如果有不止一个 SQL Map XML 文件（这是很常见的），那么可以在 <transactionManager> 元素之后使用重复的 <sqlMap> 元素以任意顺序列出这些文件。

If there is more than one SQL Map XML file (as is quite common), then the files can be listed in any order with repeated <sqlMap> elements after the <transactionManager> element.

Updating the dao.xml File

iBATIS DAO 框架由一个通常称为 dao.xml 的 xml 文件配置。

The iBATIS DAO framework is configured by an xml file commonly called dao.xml.

iBATIS DAO 框架使用这个文件来控制 DAO 的数据库连接信息，并且还可以列出 DAO 实现类和 DAO 接口。

The iBATIS DAO framework uses this file to control the database connection information for DAOs, and also to list the DAO implementation classes and DAO interfaces.

在这个文件中，您应该指定 SqlMapConfig.xml 文件的路径，以及所有 iBATOR 生成的 DAO 接口和实现类。

In this file, you should specify the path to your SqlMapConfig.xml file, and all the iBATOR generated DAO interfaces and implementation classes.

例如，假设 iBATOR 生成了一个名为 MyTableDAO 的 DAO 接口和一个名为 MyTableDAOImpl 的实现类，并且这些文件已被放入您项目的 test.dao 包中。

For example, suppose iBATOR has generated a DAO interface called MyTableDAO and an implementation class called MyTableDAOImpl, and that the files have been placed in the test.dao package of your project.

dao.xml 文件应该具有以下条目 -

The dao.xml file should have these entries −

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE daoConfig PUBLIC "-//ibatis.apache.org//DTD DAO Configuration 2.0//EN" "http://ibatis.apache.org/dtd/dao-2.dtd">

<daoConfig>

   <context>

      <transactionManager type="SQLMAP">
         <property name="SqlMapConfigResource" value="test/SqlMapConfig.xml"/>
      </transactionManager>

      <!-- DAO interfaces and implementations should be listed here -->
      <dao interface="test.dao.MyTableDAO" implementation="test.dao.MyTableDAOImpl" />
   </context>

</daoConfig>

NOTE − 仅当您为 iBATIS DAO 框架生成了 DAO 时，才需要此步骤。

NOTE − This step is required only if you generated DAOs for the iBATIS DAO framework.