Ibatis 简明教程

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