MyBatis中调用存储过程和函数

清疚 2022-04-02 06:43 452阅读 0赞

# mybatis调用oracle存储过程返回的二维数组（type是table的） #

2013年10月19日 19:04:22 [龙鞥][Link 1] 阅读数：4828

最近做个项目有很多的报表统计，涉及到几十张表抽取数据，形成一系列统计数据。

举个例子，比如统计一个单位不同职务级别女人数量、少数民族数量、30岁以上人员数量，40以上人员数量等。如：

董事长：0 0 0 1

中层领导：2 2 3 8

职员：20 40 80 200

刚开始想办法拼成了一个超级长的sql来做，但是最后长度超过vachar2的最大长度了，虽然有其他办法解决，虽然最大sql看起来很美好，但是想想后期的优化和维护，还是决定重新来规划。中间的过程就不说了，最终确定先定义一行数据存number型object（类似一维数组），形式如（0,0,1)。然后放到二维数组里。闲言少叙，如下定义：

1.  `create or replace`
2.  `TYPE C_ROW IS OBJECT(`
3.  `COUNT_SEX NUMBER, --性别女统计`
4.  `COUNT_NATION NUMBER, --少数民族统计`
5.  `COUNT_30AGE NUMBER --30岁统计`
6.  `);`

1.  `create or replace`
2.  `TYPE C_ROW_CLM IS TABLE OF C_ROW;`

关于以上的语法我就不解释了。C\_ROW\_CLM 就是二维数组。

一个简单的存储过程如下：就是往二维数组放了三列。

1.  `PROCEDURE COUNT_TEST(C_VALUE OUT C_ROW_CLM) AS`
2.  `  BEGIN`
3.  `      --初始化二维数组，存放格式如：（0,1,2,3),（0,0,2,3)`
4.  `      C_VALUE:=C_ROW_CLM();`
5.   
6.   
7.  `      C_VALUE.extend;`
8.  `      C_VALUE(1) := C_ROW(0,0,0);`
9.  `      `
10. `      C_VALUE.extend;`
11. `      C_VALUE(2) := C_ROW(1,2,3);`
12. `      `
13. `      C_VALUE.extend;`
14. `      C_VALUE(3) := C_ROW(7,8,9);`
15.  
16.  
17. `    NULL;`
18. `  END COUNT_TEST;`

以上存储过程生成而为数据已经有了，我们有一个out输出。现在就看mybatis如何实现了。

目前能查到的文章大部分是关于java如何自定义类型到数据库的举例，官方文档也是，找到一篇文章很不容易，不过最终还是找到了。现在回头看很简单，由于我们自己定义的二维数组java不认识，需要我们自定义类型做数据转换，mybatis提供了TypeHandler接口，只要实现它就可以。里面有几个方法，官方例子主要介绍了setParameter、getResult(ResultSet arg0, int arg1)两个方法，这两个方法共同完成的是java对数据库的操作以及数据类型转换，而我们的存储过程调用是不用他们的，我们用的是getResult(CallableStatement cs, int arg1)这个方法，在这里实现数据类型转换即可。具体事例如下：

1.  `package com.broadengate.handler;`
2.   
3.  `import java.sql.CallableStatement;`
4.  `import java.sql.PreparedStatement;`
5.  `import java.sql.ResultSet;`
6.  `import java.sql.SQLException;`
7.  `import java.util.ArrayList;`
8.  `import java.util.List;`
9.   
10. `import oracle.sql.Datum;`
11. `import oracle.sql.STRUCT;`
12.  
13. `import org.apache.ibatis.type.JdbcType;`
14. `import org.apache.ibatis.type.TypeHandler;`
15.  
16. `import com.XX.bean.CountRefMgrPerson;`
17.  
18. `/**`
19. `* 自定义Handler`
20. `*/`
21. `public class CRMPTypeHandler implements TypeHandler<Object> {`
22.  
23.  
24. `public Object getResult(ResultSet arg0, String arg1) throws SQLException {`
25. `// TODO Auto-generated method stub`
26. `return null;`
27. `}`
28.  
29. `public Object getResult(ResultSet arg0, int arg1) throws SQLException {`
30. `// TODO Auto-generated method stub`
31. `return null;`
32. `}`
33.  
34. `/**`
35. `* 调用存储过程实现该方法即可。`
36. `* @param cs 返回结果`
37. `* @param arg1 返回索引`
38. `*/`
39. `public Object getResult(CallableStatement cs, int arg1)`
40. `throws SQLException {`
41. `// cs.getConnection();`
42. `// 此处是excel y方向数据`
43. `String names[] ={"董事长","中层领导","职员"};`
44. `// 遍历y方向数据`
45. `int i = 0;`
46. `// 定义结果集， XXDTO是我们自己定义的bean，用来与统计数量一行数据一一对应。`
47. `List< XXDTO > dtos = new ArrayList< XXDTO >();`
48. `ResultSet rs = cs.getArray(arg1).getResultSet();`
49. `while (rs.next()) {`
50. `Datum[] data = ((STRUCT)rs.getObject(2)).getOracleAttributes();`
51. `XXDTO dto = new CountRefMgrPerson();`
52. `// key_value赋值`
53. `dto.setDataValue(names[i]);`
54. `i ++;`
55. `// 以下操作取得数据库中数据值赋给相应的bean。`
56. `dto.setSex(data[0].intValue());`
57. `dto.setNation(data[1].intValue());`
58. `dto.setDegreeCodeDoctor(data[2].intValue());`
59. `// 获得list`
60. `dtos.add(dto);`
61. `}`
62. `return dtos;`
63.  
64. `}`
65.  
66. `public void setParameter(PreparedStatement ps, int arg1, Object arg2,`
67. `JdbcType arg3) throws SQLException {`
68. `// TODO Auto-generated method stub`
69. `}`
70.  
71.  
72.  
73. `}`

1.  `package com.xx.bean;`
2.   
3.   
4.   
5.  `public class CountRefMgrPerson extends BaseBean {`
6.   
7.   
8.  `private Integer id;// null`
9.  `private String dataValue;// 纵列数值（ {"董事长","中层领导","职员"};等值 ）`
10. `private Integer countNation;// 民族（少数民族）数量`
11. `private Integer countSex;// 性别（女）数量`

<pre name="code" class="html"><span style="white-space:pre">	</span>private Integer count30;// age数量

get、set方法略\}

这样我们就完成了数据转换的操作，剩下的工作就是配置mybatis了。为了找到这个方法，憋屈了一天，国内几乎没啥资料，老外的帖子也几乎翻遍了，后来终于在一个老外的回复中找到了答案。不啰嗦了，直接上答案：

1.  `<resultMap type="com.broadengate.bean.CountRefMgrPerson" id="clist">`
2.  ``
3.  `<result property="sex" column="COUNT_SEX"/>`
4.  ``
5.  `<result property="nation" column="COUNT_NATION" />`
6.  ``
7.  `<result property="degreeCodeDoctor" column="COUNT_BOSHI"/>`
8.  `</resultMap>`

1.  ``
2.  `<select id="queryByList" statementType="CALLABLE" parameterType="Object">  `
3.  `        {call TEST.COUNT_TEST(`
4.  `        #{result ,mode=OUT, javaType=java.util.List,jdbcType=ARRAY,jdbcTypeName=C_ROW_CLM, resultMap=clist,typeHandler=com.xx.handler.CRMPTypeHandler}`
5.  `         )}  `
6.  `</select>`

相信上面的配置，用mybatis的人都知道，这里我只强调两点，一是typeHandler=com.xx.handler.CRMPTypeHandler需要指定我们自己的自定义类型，而是必须加jdbcTypeName，这个name坑爹啊，让我找了很长时间，其实就是我们out的那个类型C\_ROW\_CLM。值得一提的是，不要把一维数组和二维数组定义在包体内，调用存储过程是找不到的。

对了顺便放一下，我接受out参数的类。

1.  `import java.util.List;`
2.   
3.  `import com.xx.bean.XXDTO;`
4.   
5.  `public class XXModel {`
6.   
7.   
8.   
9.  `private List< XXDTO > result; // 调用存储过程返回的结果集`
10.  
11. `public List< XXDTO > getResult() {`
12. `return result;`
13. `}`
14.  
15. `public void setResult(List< XXDTO > result) {`
16. `this.result = result;`
17. `}`
18.  
19.  
20.  
21. `}`

类里的result与mybatis配置里的返回结果result对应。

ok了。

[https://blog.csdn.net/dirful/article/details/12873183][https_blog.csdn.net_dirful_article_details_12873183]

# [MyBatis中调用存储过程和函数][MyBatis] #

一.调用存储过程

1.首先在数据库中定义存储过程，定义的存储过程的代码如下：

![复制代码][2018122516541931]

//定义存储过程
    create or replace procedure pag_add(p1 varchar2,p2 varchar2,p3 out varchar2)
       as
        begin
              p3:=p1+p2;
          end;
    
    //定义函数

create or replace function fun\_add(p1 number ,p2 number)  
　　return number  
　　as  
begin  
　　　　return p1+p2;  
end;

![复制代码][2018122516541931]

2.通过接口映射Sql,定义接口

![复制代码][2018122516541931]

package cn.et.mybatis.lesson02.func;
    
    import java.util.List;
    import java.util.Map;
    
    public interface CallMapper {
    //    public void queryEmp(Map map);
            //查询存储过程的方法
            public void callProcedure(Map map);
            //查询函数的方法
            public void callFunction(Map map);
    }

![复制代码][2018122516541931]

3.定义映射文件

![复制代码][2018122516541931]

<?xml version="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="cn.et.mybatis.lesson02.func.CallMapper">
        
        <select id="queryEmp" parameterType="map" statementType="CALLABLE">
        {
            call pag_add(
                #{p1,mode=IN,jdbcType=NUMERIC},
                #{p2,mode=IN,jdbcType=NUMERIC},
                #{p3,mode=OUT,jdbcType=NUMERIC}
            )
        }
        </select>
        
        <select id="callProcedure" parameterType="map" statementType="CALLABLE" >
          {
             call prg_add(
                   #{p1,mode=IN,jdbcType=NUMERIC},
                   #{p2,mode=IN,jdbcType=NUMERIC},
                   #{p3,mode=OUT,jdbcType=NUMERIC}
             )
          }
      </select>
      
      <select id="callFunction" parameterType="map" statementType="CALLABLE">
              {
                  #{p3,mode=OUT,jdbcType=NUMERIC}=call fun_add(
                      #{p1,mode=IN,jdbcType=NUMERIC},
                   #{p2,mode=IN,jdbcType=NUMERIC}
                  )
              }
      </select>
    </mapper>

![复制代码][2018122516541931]

4.定义主mybaits.xml文件

![复制代码][2018122516541931]

<?xml version="1.0" encoding="UTF-8" ?>
    
    <!DOCTYPE configuration
      PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-config.dtd">
    
    <configuration>
        
        <properties resource="cn/et/mybatis/lesson02/jdbc.properties">
        </properties>
        
      <environments default="development">
        <environment id="development">
        
          <transactionManager type="JDBC"/>
          
          <dataSource type="POOLED">
            <property name="driver" value="${driverClass}"/>
            <property name="url" value="${url}"/>
            <property name="username" value="${userAccount}"/>
            <property name="password" value="${password}"/>
          </dataSource>
        </environment>
      </environments>
      
      <mappers>
          
        <mapper resource="cn/et/mybatis/lesson02/func/call_mapper.xml"/>
      </mappers>
    </configuration>

![复制代码][2018122516541931]

5.定义测试类

![复制代码][2018122516541931]

package cn.et.mybatis.lesson02.func;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.HashMap;
    import java.util.Map;
    
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.Test;
    
    public class TestMyBatis {
        //封装session的返回
        public static SqlSession getSession(){
            String resource = "/cn/et/mybatis/lesson02/mybatis.xml";
            InputStream inputStream =TestMyBatis.class.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            //操作数据库的类 SqlSession
            SqlSession session=sqlSessionFactory.openSession();
            return session;
        }
        //调用存储过程
        public static void main(String[] args) throws IOException {
            SqlSession session=getSession();
            CallMapper cm=session.getMapper(CallMapper.class);
            Map map=new HashMap();
            map.put("p1", 100);
            map.put("p2", 200);
            cm.callProcedure(map);
            System.out.println(map.get("p3"));
        }
        
        @Test
        //调用函数
        public void funTest(){
            SqlSession session=getSession();
            CallMapper cm=session.getMapper(CallMapper.class);
            Map map=new HashMap();
            map.put("p1", 100);
            map.put("p2", 400);
            cm.callFunction(map);
            System.out.println(map.get("p3"));
        }
    }

![复制代码][2018122516541931]

以上为存储过程和函数的调用

//查询函数的方法public void callFunction(Map map);

分类: [MyBaits][]

好文要顶 关注我 收藏该文 ![20181225165419183][] ![20181225165419200][]

[![20181225165419216][]][20181225165419216 1]

[lszan][20181225165419216 1]  
[关注 - 7][- 7]  
[粉丝 - 3][- 3]

\+加关注

[« ][Link 2]上一篇：[MyBatis][Link 2]  
[» ][Link 3]下一篇：[MyBatis中selectKey的查询][Link 3]

posted @ 2017-06-13 10:19 [lszan][] 阅读(37990) 评论(1) [编辑][Link 4] [收藏][Link 5]

[https://www.cnblogs.com/xushirong/p/6999568.html][MyBatis]

[孤独青鸟][Link 6]

忍受得了孤独，拒绝得了诱惑，才能有涅槃的重生，否则一辈子碌碌无为。

*   
 *   
 *  [新随笔][Link 7]
 *  [联系][Link 8]
 *   
 *  [管理][Link 9]

随笔-283  文章-1  评论-9

# [MyBatis调用存储过程，含有返回结果集、return参数和output参数][MyBatis_return_output] #

Ibatis是我们经常使用的O/R映射框架，mybats是ibatis被Google收购后重新命名的一个工程，当然也做了大量的升级。而调用存储过程也是一次额C/S架构模式下经常使用的手段，我们知道，ibatis调用存储过程有一个专门的标签<procedure>，在mybats里面已经没有这本标签了，而是通过一个参数statementType=*"CALLABLE"*来区分。废话不多说，直接看怎么写吧！

测试环境：mybats3.0.4 + sqlserver2008 +Spring3.1.1

存储过程：

![复制代码][20181225173054606]

use base_passport
    
    go
    
     
    
    create proc yhj_test
    
        @input_a int,
    
        @input_b int,
    
        @output_c int out
    
    as
    
    begin
    
        select * from config.city;
    
        set @output_c = @input_a*@input_b;
    
        return @input_a+@input_b;
    
    end

![复制代码][20181225173054606]

Mybats配置：

![复制代码][20181225173054606]

![复制代码][20181225173054606]

Java代码

![复制代码][20181225173054606]

Map<String, Object> paramMap = new HashMap<String, Object>();
    
           paramMap.put("a", 2);
    
           paramMap.put("b", 3);
    
           List<City> citys = (List<City>) getTemplate().selectList(getNameSpaceAndMethod("testByProc"), paramMap);
    
           return citys;

![复制代码][20181225173054606]

通过以上代码我们即可获取对应的结果集，return参数和output参数。其中return参数和output参数放在传入的paramMap中,其中,c是存储过程的output参数，而d为return参数。

需要注意的事项：

1、  存储过程的参数和名称无关，只和顺序有关系

2、  存储过程的output参数，只能通过传入的map获取

3、  存储过程返回的结果集可直接用返回的map接收

4、  存储过程的return结果需要使用?=call procName(?,?)的第一个参数接收，需要指定对应的mode为OUT类型

5、  存储过程对应的数据类型为枚举类型，需要使用大写，如*VARCHAR*

**要点：output是在存储过程中的参数的返回值(输出参数)，而ReturnValue是存储过程返回的值(使用return关键字)，一个存储过程可以有任意多个依靠参数返回的值，但只有一个ReturnValue。**

获取存储过程的返回值--通过查询分析器获得  
     存储过程大体分成三类：

1、返回记录集的存储过程:执行结果是一个记录集，例如，从数据库中检索出符合某一个或几个条件的记录。

2、返回数值得的存储过程(也可称为标量存储过程)，其执行完后返回一个值，例如数据库中执行一个有返回值的函数或命令。

3、行为存储过程，用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。

另外：含有return的存储过程其返回值为return返回的那个值。

没有return的存储过程，无论执行结果又无结果集，其返回值都是0，

带有输出参数的存储过程，如果有RETURN则返回RETURN返回的那个值，如果要SELECT输出参数，则出现输出参数的值，和有无RETURN无关。

*转自：http://yhjhappy234.blog.163.com/blog/static/316328322012455714892/*

好文要顶 关注我 收藏该文 ![20181225173054731][] ![20181225173054748][]

[![20181225173054766][]][20181225173054766 1]

[孤独青鸟][20181225173054766 1]  
[关注 - 540][- 540]  
[粉丝 - 18][- 18]

\+加关注

[« ][Link 10]上一篇：[查看linux中某个端口（port）是否被占用（netstat,lsof）][Link 10]  
[» ][Link 11]下一篇：[Mybatis一级、二级缓存][Link 11]

posted @ 2016-01-20 13:50 [孤独青鸟][Link 6] 阅读(15848) 评论(0) [编辑][Link 12] [收藏][Link 13]

[https://www.cnblogs.com/wuxiang/p/5144940.html][MyBatis_return_output]

[Link 1]: https://me.csdn.net/dirful
[https_blog.csdn.net_dirful_article_details_12873183]: https://blog.csdn.net/dirful/article/details/12873183
[MyBatis]: https://www.cnblogs.com/xushirong/p/6999568.html
[2018122516541931]: /images/20220402/216198fc34b64ffda4794a9825e9b4ca.png
[MyBaits]: https://www.cnblogs.com/xushirong/category/1022038.html
[20181225165419183]: https://img-blog.csdnimg.cn/20181225165419183
[20181225165419200]: /images/20220402/c01f2b100084457ea6faefd609f67c43.png
[20181225165419216]: /images/20220402/bf02a179580149a59f09cb6fee4ba624.png
[20181225165419216 1]: http://home.cnblogs.com/u/xushirong/
[- 7]: http://home.cnblogs.com/u/xushirong/followees
[- 3]: http://home.cnblogs.com/u/xushirong/followers
[Link 2]: https://www.cnblogs.com/xushirong/p/6974128.html
[Link 3]: https://www.cnblogs.com/xushirong/p/6999854.html
[lszan]: https://www.cnblogs.com/xushirong/
[Link 4]: https://i.cnblogs.com/EditPosts.aspx?postid=6999568
[Link 5]: https://www.cnblogs.com/xushirong/p/6999568.html#
[Link 6]: https://www.cnblogs.com/wuxiang/
[Link 7]: https://i.cnblogs.com/EditPosts.aspx?opt=1
[Link 8]: https://msg.cnblogs.com/send/%E5%AD%A4%E7%8B%AC%E9%9D%92%E9%B8%9F
[Link 9]: https://i.cnblogs.com/
[MyBatis_return_output]: https://www.cnblogs.com/wuxiang/p/5144940.html
[20181225173054606]: /images/20220402/8b198e9adcc943988f7bc6588f963055.png
[20181225173054731]: https://img-blog.csdnimg.cn/20181225173054731
[20181225173054748]: /images/20220402/653d2e8aaef641d482130af530cd131f.png
[20181225173054766]: /images/20220402/bb7e483400f0441d8589d5f8847fa939.png
[20181225173054766 1]: http://home.cnblogs.com/u/wuxiang/
[- 540]: http://home.cnblogs.com/u/wuxiang/followees
[- 18]: http://home.cnblogs.com/u/wuxiang/followers
[Link 10]: https://www.cnblogs.com/wuxiang/p/5121798.html
[Link 11]: https://www.cnblogs.com/wuxiang/p/5150354.html
[Link 12]: https://i.cnblogs.com/EditPosts.aspx?postid=5144940
[Link 13]: https://www.cnblogs.com/wuxiang/p/5144940.html#