这里写的是后台数据库使用Mysql，

存储过程定义：

 1 PROCEDURE deyestest.procedure1(OUT json VARCHAR(2000))
 2 BEGIN DECLARE v_id        INT;
 3   DECLARE v_userName  VARCHAR(155);
 4   DECLARE v_mobileNum VARCHAR(55);
 5   DECLARE v_tmp       VARCHAR(1000);
 6   DECLARE v_info      VARCHAR(2000);
 7   DECLARE v_info2     VARCHAR(2000);
 8   DECLARE v_int       INT;
 9   
10 
11   DECLARE done        INT DEFAULT 0;
12 
13 
14   DECLARE myCursor CURSOR FOR
15   SELECT userId
16        , userName
17        , mobileNum
18   FROM
19     user_info
20   ORDER BY
21     userId
22   LIMIT
23     2, 10;
24   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
25 
26   OPEN myCursor;
27   SET v_info = concat('[');
28   FETCH myCursor INTO v_id, v_userName, v_mobileNum;
29   REPEAT
30 
31     SET v_tmp = concat('{', v_id, ',', v_userName, ',', v_mobileNum, '}', ',');
32     SET v_info = concat(v_info, v_tmp);
33     FETCH myCursor INTO v_id, v_userName, v_mobileNum;
34   UNTIL done
35   END REPEAT;
36   SET NAMES 'utf8';
37   SET v_int = char_length(v_info);
38 
39   SET v_info = left(v_info COLLATE utf8_bin, v_int - 1);
40 
41   SET v_info2 = concat(v_info, ']');
42   CLOSE myCursor;
43   SELECT v_info2
44 INTO
45   json;
46 END

View Code

java中Mapper配置：

<select id="getByProcedure2"  statementType="CALLABLE">
    
        {
            call procedure1(

             #{json,jdbcType=VARCHAR,javaType=java.lang.String,mode=OUT}             
            )
        }
</select>

javaMapper代码：

1 public interface PersonMapper {
2     public void getByProcedure2(Map<String,Object> map);
3 }

View Code

java调用测试

1 public  static void main(){
2 HashMap<String, Object> hashMap = new HashMap<String, Object>();
3         
4         wuShuaiMapper.getByProcedure2(hashMap);
5         System.out.println(hashMap.get("json"));
6 
7 }

View Code

如果使用Map作为进行java与数据库存储过程的数据输入与输出的参数，只需要配置select标签中“statementType="CALLABLE"”，然后在存储过程中的输出参数使用如下方式：SELECT v_info2 INTO json，其中json是存储过程中的输出参数。

posted on 2014-04-08 10:25 和硕亲王阅读(483) 评论(0) 编辑收藏举报

Mybatis使用存储过程（MySql）