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 Procedure cascadeoperationasbegin Delete from teacher Where id=1; Update studentdetail Set address= ' Ningbo Haishu District ' Where studentid=10; 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= "callable" >   {call cascadeoperation}</delete>

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

CREATE OR REPLACE Procedure queryteacher (FID in Integer,type on Varchar,name out Varchar) asbeginif type= ' 1 ' thenselect Nam E into Name from student Where Id=fid; Else if type= ' 2 ' thenselect name into name from teacher Where Id=fid; Else  name:= ' error '; End If; End If; End;

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

Declarename  Varchar2 (50); Beginqueryteacher (3, ' 2 ', name);D bms_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" >   {call Queryteacher (#{ Fid,mode=in,jdbctype=integer},#{type,mode=in,jdbctype=varchar},#{name,mode=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) asbegin    open Cur_arg 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 = new hashmap<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.

Reprint: http://www.360doc.com/content/14/0728/16/7662927_397682668.shtml

Executing Oracle stored procedures using MyBatis