Executing Oracle stored procedures using MyBatis

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

CREATE OR REPLACE Procedurecascadeoperation as  Begin   Delete  fromTeacherWhereId=1; UpdateStudentdetailSetAddress='Ningbo Haishu District' WhereStudentID=Ten; 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

< Delete id="Cascadeoperation" statementtype=>         {call Cascadeoperation}      </Delete>  

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

CREATE OR REPLACE ProcedureQueryteacher (FIDinch Integer, Typeinch Varchar, Name outVarchar)   as  Begin  IfType='1'  Then  SelectName intoName fromStudentWhereId=ID; Else ifType='2'  Then  SelectName intoName fromTeacherWhereId=FID; ElseName:='Error'; End If; End If; End;

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

Declare   Name  Varchar2(+);   Begin   queryteacher (3,'2', Name);  Dbms_output.put_line (Name);   End ;   /  

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

 <  select  id=  "Queryteacher" Statementtype=  "callable" Parametertype=  "Java.util.Map" >   =in , Jdbctype=  integer },#{type,mode=in , Jdbctype=  =  out,jdbctype=  varchar  })}  </ 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

map<string,object> mm=New hashmap<string,object>();  Mm.put ("FID", 3);  Mm.put ("type", 2);  M.queryteacher (mm);  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

CREATE OR REPLACE Procedure getteacher (cur_arg out Sys_refcursor)  As  begin      for Select * from  teacher;  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

<resultmap id= "resultMap3" type= "Org.lxh.module.usefunction.info.Teacher" >  <result property= "address "column=" Address "/>  <result property=" name "column=" name "/>  <result property=" id "column=" id "/ >    </resultMap>   <select id= "Getallteacherinfo" statementtype= "callable" parametertype= " Java.util.Map ">         {call Getteacher (#{result,jdbctype=cursor,mode=out,javatype=resultset, Resultmap =resultMap3})}      </select>  

The Java code is a little bit more complicated here.

map<string, object> map =NewHashmap<string, object>();          M.getallteacher (map); Set<map.entry<string, object>> set =Map.entryset ();  for(iterator<map.entry<string, object>> it =Set.iterator (); it. Hasnext ();) {Map.entry<string, object> entry = (map.entry<string, object>) it. Next (); //System.out.println (Entry.getkey () + "--->" +//(Teacher) Entry.getvalue ()); list<teacher> t = (list<teacher>) Entry.getvalue (); Iterator<Teacher> Itera =T.iterator ();  while(Itera.hasnext ()) {Teacher TT=Itera.next (); System.out.println (Tt.getname ()+ "," +tt.getaddress ()); }            }  

Here is the result of the execution

Here the stored procedure is almost there, it has been studied for a long time to get it out, the other to execute the stored procedure with JDBC I will add the article later.

A cursor can also be returned directly using the following method

New HashMap ();        Map.put ("Jid", Jid);        Userinfomapper.getfriendlist (map);         // Result is a returned results name        that is written when the XML file is MyBatis list<userinfo> list = (list<userinfo>) map.get ("result");         return list;

This article transferred from: http://my.oschina.net/lujianing/blog/296427

Executing Oracle stored procedures using MyBatis