iBatis procedure

一、创建procedure(mysql)

注意：mydb是数据库名

DELIMITER $$

DROP PROCEDURE IF EXISTS `mydb`.`getEmp` $$
CREATE PROCEDURE `mydb`.`getEmp` 
   (IN empid INT)
BEGIN
   SELECT * FROM EMPLOYEE
   WHERE ID = empid;
END $$

DELIMITER;

二、修改Employee.xml文件

<?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="model.Employee">
		
		insert into Employee (name,gender,salary)
		values (#name#,#gender#,#salary#)
		
		<selectKey resultClass="int" keyProperty="id">
			select last_insert_id() as id
		</selectKey>
	</insert>
	
	<select id="select*" resultClass="java.util.HashMap" parameterClass="int">
		select * from Employee where id = #id#
	</select>
	
	<update id="update#name" parameterClass="model.Employee">
		update Employee set name = #name# where id = #id#
	</update>
	
	<delete id="delete" parameterClass="int">
		delete from Employee where id = #id#
	</delete>
	
	<resultMap id="resultMap" class="model.Employee">
		<result property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="gender" column="gender"/>
		<result property="salary" column="salary"/>
	</resultMap>
	<select id="selectForResultMap" resultMap="resultMap" parameterClass="int">
		select id,name,gender,salary from employee where id = #id#
	</select>
	
	<!-- call stored procedure -->
	<parameterMap id="procedureParam" class="map">
		<parameter property="empId" jdbcType="INT" javaType="java.lang.Integer" mode="IN"/>
	</parameterMap>
	<procedure id="procedure" parameterMap="procedureParam" resultClass="model.Employee">
		{ call getEmp(?) }
	</procedure>
	
</sqlMap>

三、java测试程序

public static void main(String[] args) throws IOException, SQLException{
		Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
		SqlMapClient sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
		
		
		//procedure--------------------------------------------------
		
		System.out.println("ready to call procedure...");
		
		Map<String,Integer> param = new HashMap<String,Integer>();
		param.put("empId", 1);
		
		Employee employee = (Employee) sqlMapClient.queryForObject("Employee.procedure",param);
		
		System.out.println("finish to call procedure...");
	}