MyBatis call stored procedure without parameters, with input and output parameter, output cursor type of storage

Stored procedures are not used in small companies, but if the business is complex or the performance requirements are more stringent when the stored procedures come in handy, some of the earlier version of Ibatis does not seem to support stored procedures so I chose MyBatis to do the experiment.

1. No input and output parameters stored procedures, I wrote a relatively simple, it should be noted that the Oracle non-parametric stored procedures can not write parentheses

[SQL]

1. CREATE OR REPLACE Procedure cascadeoperation
2. As
3. Begin
4. Delete from teacher Where id=1;
5. Update studentdetail Set address=' Ningbo Haishu District ' Where studentid=10;
6. End;

Here do 2 operations, may have used mybatis people will be confused when the implementation of the use of the update tag or delete tag, in fact, I have tried the select tag is OK, the following is part of the configuration file

[HTML]

1. <delete id= "cascadeoperation" statementtype="callable" >
2. {Call Cascadeoperation}
3. </Delete>

2. Stored procedures with input and output parameters, I've added a few judgments of if else here

[SQL]

1. CREATE OR REPLACE Procedure queryteacher (FID in Integer,type with Varchar,Name out C12>varchar)
2. As
3. Begin
4. If type=' 1 ' Then
5. Select name into name from student Where Id=fid;
6. Else if type=' 2 ' Then
7. Select name into name from teacher Where Id=fid;
8. Else
9. name:=' error ';
10. End If;
11. End If;
12. End;

I'll post the stored procedure statements that I executed in the command-line window by the way.

[SQL]

1. Declare
2. Name Varchar2 (50);
3. Begin
4. Queryteacher (3,' 2 ',Name);
5. Dbms_output.put_line (Name);
6. End;
7. /

You may not see any output when you execute a similar statement, so don't worry, just use set serveroutput on the command line, and you can see my execution results.

See the results, the following use MyBatis to execute this stored procedure, the following is the mapping file

[HTML]

2. <select id="Queryteacher" statementtype="callable" parametertype= "java.util.Map" >
4. {Call Queryteacher (#{fid,mode= in,jdbctype=integer},#{type,mode= in,jdbctype= Varchar},#{name,mode= out,jdbctype=VARCHAR})}
6. </Select>

How to get the contents of the return, in fact, as long as the stored procedure is executed after the map has value, Java code roughly as follows

[Java]

1. map<string,object> mm=New hashmap<string,object> ();
2. Mm.put ("FID", 3);
3. Mm.put ("type", 2);
4. M.queryteacher (mm);
5. System.out.println (Mm.get ("name"));

The following is the result of the console output

3. There is also a stored procedure that returns a cursor that is similar to a collection of such

[SQL]

1. CREATE OR REPLACE Procedure getteacher (cur_arg out sys_refcursor)
2. As
3. Begin
4. Open Cur_arg for Select * from teacher;
5. End;

This situation, in the mybatis is slightly different, at this time Jdbctype is Cursor,javatype is resultset, here can also turn the results into Resultmap, as shown below

[HTML]

1. <resultmap id="resultMap3" type="Org.lxh.module.usefunction.info.Teacher">
2. <result property="Address" column="Address"/>
3. <result property="name" column="name"/>
4. <result property="id" column="id"/>
5. </resultmap>

[HTML]

1. <select id="Getallteacherinfo" statementtype="callable" parametertype=" Java.util.Map " >
2. {Call Getteacher (#{result,jdbctype=CURSOR,mode= out,javatype=ResultSet, resultmap=resultMap3})}
3. </Select>

The Java code is a little bit more complicated here.

[Java]

1. map<string, object> map = new hashmap<string, object> ();
2. M.getallteacher (map);
3. Set<map.entry<string, object>> set = Map.entryset ();
4. for (iterator<map.entry<string, object>> it = Set.iterator (); it
5. . Hasnext ();) {
6. map.entry<string, object> Entry = (map.entry<string, object>) it
7.  Next ();
8. //System.out.println (Entry.getkey () + "--->" +
9. //(Teacher) Entry.getvalue ());
10. list<teacher> t = (list<teacher>) entry.getvalue ();
11. iterator<teacher> Itera = T.iterator ();
12. While (Itera.hasnext ()) {
13. Teacher TT = Itera.next ();
14. System.out.println (Tt.getname () + "," + tt.getaddress ());
15. }
16. }

Here is the result of the execution

A cursor can be returned directly using the following method

Map map = new HashMap();

Map.put("jid", jid);

userInfoMapper.getFriendList(map);

//result is the return result name written in the mybatis xml file

List<UserInfo> list = (List<UserInfo>)map.get("result");

Return list;

MyBatis call stored procedure without parameters, with input and output parameter, output cursor type of storage