一、创建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...");
}