参考资料
1 ibatis3.0存储过存的一些细节
http://blog.csdn.net/axman/archive/2010/03/05/5349349.aspx
2 ibatis3.X如何调用Oracle存储过程定义的in数组
http://topic.csdn.net/u/20110125/20/ade11108-761e-4b2d-96c4-fae83907ea9f.html
3 ibatis3调用存储过程
http://www.iteye.com/topic/531456
4 ibatis2.0与ibatis3.0 调用存储过程(SQL)
http://blog.sina.com.cn/s/blog_62e744e60100olfr.html
对于MyIbatis调用存储过程的一个特点是:使用Map传递参数
此示例是在前二个MyIbatis示例基础之上得来的
一 环境:XP3+Oracle10g+MyEclipse6+(Tomcat)+JDK1.5
二 Oracle存储过程
1 添加用户
Java代码
- create or replace procedure addUser(u_id in varchar2,u_name in varchar2,u_age in integer,u_sex in varchar2,u_address in varchar2,u_pwd in varchar2,message out varchar2) is
- begin
- insert into users (id,name,age,sex,address,password)VALUES(u_id,u_name,u_age,u_sex,u_address,u_pwd);
- message := '插入用户表成功';
- commit;
- EXCEPTION
- WHEN OTHERS THEN
- message :='插入用户表失败';
- end addUser;
2 修改用户
Java代码
- create or replace procedure updateUser(u_id in varchar2,u_name in varchar2,u_age in integer,u_sex in varchar2,u_address in varchar2,u_pwd in varchar2,message out varchar2) is
- begin
- update users u set name=u_name,age=u_age,sex=u_sex,address=u_address,password=u_pwd where id=u_id;
- message := '更新用户表成功';
- commit;
- EXCEPTION
- WHEN OTHERS THEN
- message :='更新用户表失败';
- end updateUser;
3 删除用户
Java代码
- create or replace procedure delUser(u_id in varchar2,message out varchar2) is
- begin
- delete users where id=u_id;
- message := '删除用户表成功';
- commit;
- EXCEPTION
- WHEN OTHERS THEN
- message :='删除用户表失败';
- end delUser;
4 查询用户
Java代码
- create or replace package JUV is
- TYPE CUR_GETUSER IS REF CURSOR;
- end JUV;
-
- create or replace procedure getAllUser(userList out JUV.CUR_GETUSER)
- as
- begin
- open userList for select * from users;
- end getAllUser;
三 UserMapper.java
Java代码
- public interface UserMapper{
-
- public int checkUserExits(User user);
-
- public void addUser(User user);
- //测试存储过程插入
- public void addUserProc(Map user);
- public List<User> getAllUser(Map map);
- public void selectByProc(Map<String,Integer> p);
- public void editUserProc(Map map);
- public void delUserProc(Map map);
- public void testCounts(Map map);
- }
四 映射文件
Java代码
- <?"1.0" encoding="UTF-8"?>
- <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
- <mapper namespace="com.liuzd.ssm.mapper.UserMapper">
-
- <resultMap type="com.liuzd.ssm.entity.User" id="userMap">
- <id property="id" column="id" />
- <result property="name" column="name" />
- <result property="age" column="age" />
- <result property="sex" column="sex" />
- <result property="address" column="address" />
- <result property="password" column="password" />
- </resultMap>
-
- <select id="selectByProc" statementType="CALLABLE">
- {call pp(#{x},#{y},#{z,mode=OUT,jdbcType=INTEGER})}
- </select>
-
- <!-- 调用存储过程 -->
- <select id="getAllUser" statementType="CALLABLE">
- {call getAllUser(#{userList,<!-- 参数 -->
- mode=OUT,<!-- 参数类型 -->
- javaType=java.sql.ResultSet,<!-- 参数java类型 -->
- jdbcType=CURSOR,<!-- 参数jdbc类型 -->
- resultMap=userMap<!-- ResultSet需要resultMap参数 -->
- })}
- </select>
- <!-- 也可用insert标签
- <select id="addUserProc" statementType="CALLABLE" >
- {call addUser(#{id},#{name},#{age},#{sex},#{address},#{password},#{message, mode=OUT,javaType=string,jdbcType=VARCHAR})}
- </select>
- -->
-
- <insert id="addUserProc" statementType="CALLABLE">
- {call
- addUser(#{id},#{name},#{age},#{sex},#{address},#{password},#{message,
- mode=OUT,javaType=string,jdbcType=VARCHAR})}
- </insert>
- <update id="editUserProc" statementType="CALLABLE">
- {call
- updateUser(#{id},#{name},#{age},#{sex},#{address},#{password},#{message,
- mode=OUT,javaType=string,jdbcType=VARCHAR})}
- </update>
- <delete id="delUserProc" statementType="CALLABLE">
- {call delUser(#{id},#{message,
- mode=OUT,javaType=string,jdbcType=VARCHAR})}
- </delete>
- <select id="checkUserExits"
- parameterType="com.liuzd.ssm.entity.User" resultType="int">
- select count(*) from users where name=#{name} and
- password=#{password}
- </select>
-
- <select id="getCounts" resultType="long">
- select count(*) from users
- </select>
-
- <select id="getUserList" resultType="java.util.List"
- resultMap="userMap">
- select * from users
- </select>
-
- <select id="getUserListByQuery" parameterType="map"
- resultType="java.util.List" resultMap="userMap">
- select * from users
- <where>
- <if test="name != null and name != ''">
- and name like CONCAT(CONCAT('%', #{name}),'%')
- </if>
- </where>
- </select>
-
- <select id="getUserDynamicSql" parameterType="object[]"
- resultType="list">
- select * from users
- <where>
- <if test="array[0]">and name like #{array[0]}</if>
- <if test="array[1]">and sex = #{array[1]}</if>
- </where>
- </select>
-
- <select id="pageList" parameterType="map" resultType="list"
- resultMap="userMap">
-
- select ttt.* from(select tt.*,rownum rn from(select * from users
- <where>
- <if test="name != null and name != ''">
- <!--
- 特别提醒一下, $只是字符串拼接, 所以要特别小心sql注入问题。
- 在开发时使用: $,方便调试sql,发布时使用: #
- and name like #{name},
- -->
- and name like '%${name}%'
- </if>
- <if test="sex != null and sex != ''">and sex = #{sex}</if>
- </where>
- order by ${orderName} ${descAsc} )tt)ttt
- <where>
- <if test="startIndex != null and startIndex != ''">
- rn > ${startIndex}
- </if>
- <if test="endIndex != null and endIndex != ''">
- <![CDATA[ and rn <= ${endIndex} ]]>
- </if>
- </where>
- </select>
-
- <select id="pageCounts" parameterType="map" resultType="long">
- select count(*) from users
- <where>
- <if test="name != null and name != ''">
- and name like #{name}
- </if>
- <if test="sex != null and sex != ''">and sex = #{sex}</if>
- </where>
- </select>
-
- <select id="getUserListByQueryUser"
- parameterType="com.liuzd.ssm.entity.User" resultType="java.util.List"
- resultMap="userMap">
- select * from users where 1=1
- <if test="name != null and name != ''">
- and name like CONCAT(CONCAT('%', #{name}),'%')
- </if>
- </select>
-
- <select id="getUserByUid" parameterType="string"
- resultType="com.liuzd.ssm.entity.User">
- select * from users where id=#{id}
- </select>
-
- <select id="getUserByUids" parameterType="list"
- resultType="java.util.List" resultMap="userMap">
- select * from users where id in
- <foreach collection="list" item="classList" open="("
- separator="," close=")">
- #{classList}
- </foreach>
- </select>
-
- <update id="editUser" parameterType="com.liuzd.ssm.entity.User">
- update users
- <set>
- <if test="name != null and name != ''">name = #{name},</if>
- <if test="age > 0">age = #{age},</if>
- <if test="sex != null and sex != ''">sex = #{sex},</if>
- <if test="address != null and address != ''">
- address = #{address},
- </if>
- <if test="password != null and password != ''">
- password = #{password}
- </if>
- </set>
- where id=#{id}
- <!-- update users set name=#{name},age=#{age},sex=#{sex},address=#{address},password=#{password} where id=#{id} -->
- </update>
- <insert id="addUser" parameterType="com.liuzd.ssm.entity.User">
- insert into users
- (id,name,age,sex,address,password)VALUES(#{id},#{name},#{age},#{sex},#{address},#{password})
- </insert>
- <delete id="delUser" parameterType="string">
- delete users where id=#{id}
- </delete>
- </mapper>
五 测试用例
Java代码
- package com.liuzd.ssm.service;
-
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
-
- import org.apache.commons.lang3.RandomStringUtils;
- import org.junit.AfterClass;
- import org.junit.BeforeClass;
- import org.junit.Test;
- import org.springframework.context.ApplicationContext;
- import org.springframework.context.support.ClassPath
-
- import com.liuzd.ssm.entity.User;
-
- public class UserServiceTestProc {
-
- private static ApplicationContext ctx;
-
- @BeforeClass
- public static void beforeClass() {
- ctx = new ClassPath"applicationContext.
- }
-
- @AfterClass
- public static void afterClass() {
- ctx = null;
- }
-
- public UserService getUserService(){
- return (UserService)ctx.getBean("userService");
- }
-
- @Test
- public void testAddUser(){
- Map user = new HashMap();
- user.put("age",23);
- user.put("id",RandomStringUtils.randomAlphanumeric(32));
- user.put("name","李风");
- user.put("address","南京市");
- user.put("password","123");
- user.put("sex","1");
- getUserService().addUser2(user);
- System.out.println(user.get("message"));
- }
-
-
-
- @Test
- public void testUpdateUserProc(){
- Map user = new HashMap();
- user.put("id","7MhpyKczvyh5GaevvZdl49Box2nXvhHx");
- user.put("age",98);
- user.put("name","李风所");
- user.put("address","南京市");
- user.put("password","123");
- user.put("sex","1");
- getUserService().editUser2(user);
- System.out.println(user.get("message"));
- }
-
- @Test
- public void testDelUser(){
- Map map = new HashMap();
- map.put("id", "rl8hxuCW21hsZnVPopKmu0VohRow7yCk");
- getUserService().delUser2(map);
- System.out.println(map.get("message"));
- }
-
- @Test
- public void testGetUserListProc(){
- Map map = new HashMap();
- getUserService().getAllUser(map);
- List<User> list = (List<User>) map.get("userList");
- for (User user : list) {
- System.out.println(user.getAge() + "," + user.getName());
- }
- System.out.println("size :" + list.size());
- }
- }
原标题:MyIbatis:存储过程(增,删,改,查)
关键词: