IBatis (MyBatis): Working with Stored Procedures

this tutorial will walk you through how to setup ibatis ( mybatis ) in a simple java project and will present how to work with stored procedures using mysql.the goal os this tutorial is to demonstrate how to execute/call stored procedures using ibatis/ mybatis .

pre-requisites

for this tutorial i am using:

ide: eclipse (you can use your favorite one)
database: mysql
libs/jars: mybatis , mysql conector and junit (for testing)

this is how your project should look like:

sample database

please run the script into your database before getting started with the project implementation. you will find the script (with dummy data) inside the sql folder.

as we are going to work with stored procedures, you will also have to execute a script with procedures. here are the procedures:

use `blog_ibatis`;drop procedure if exists `gettotalcity`;delimiter $$use `blog_ibatis`$$create procedure `blog_ibatis`.`gettotalcity` (out total integer)begin    select count(*) into total    from city;end$$delimiter ; -- -------------------------------------------------------------------------------- use `blog_ibatis`;drop procedure if exists `gettotalcitystateid`;delimiter $$use `blog_ibatis`$$create procedure `blog_ibatis`.`gettotalcitystateid` (in stateid smallint, out total integer)begin    select count(*) into total    from city    where state_id = stateid;end$$delimiter ; -- -------------------------------------------------------------------------------- use `blog_ibatis`;drop procedure if exists `getstates`;delimiter $$use `blog_ibatis`$$create procedure `blog_ibatis`.`getstates` ()begin    select state_id, state_code, state_name    from state;end$$delimiter ;

1 – spmapper – xml

i did not find anything on the user manual about how to call stored procedures, so i decided to search on the mailing list. and i found some tips of how to call stores procedures.

on the previous version, ibatis has a special xml tag for stored procedures. but there is no xml tag for it on current mybatis version (version 3).

to call a stored procedure usgin mybatis/ibatis 3 you will have to follow some tips:

1. must set the statement type to callable
2. must use the jdbc standard escape sequence for stored procedures: { call xxx (parm1, parm2) }
3. must set the mode of all parameters ( in, out, inout )
4. all in, out, and inout parameters must be a part of the parametertype or parametermap (discouraged). the only exception is if you are using a map as a parameter object. in that case you do not need to add out parameters to the map before calling , mybatis will add them for you automatically.
5. resulttype or resultmap (more typically) is only used if the procedure returns a result set.
6. important : oracle ref cursors are usually returned as parameters, not directly from the stored proc. so with ref cursors, resultmap and/or resulttype is usually not used.

first example:

we want to call the procedure gettotalcity and this procedure only have one out parameter, and no in/inout parameter. how to do it?

we are going to ser inline parameters in this first example. to use inline parameters, create a pojo class to represent your parameters, set the parametertype to the class you created and you are going to use this notation to represent each parameter:

#{parametername, mode=out, jdbctype=integer}

• mode can be in, out, inout
• and specify the jdbctype of your parameter

to create the mybatis xml configuration, you can use the select ou update tag. do not forget to set the statementtype to callable .

here is how our mybatis statement is going to look like:

<select id="callgettotalcity" parametertype="param" statementtype="callable">    { call gettotalcity(#{total, mode=out, jdbctype=integer})}</select>

and this is the pojo class which represents the parameter for gettotalcity procedure:

package com.loiane.model; public class param {     private int total;     public int gettotal() {        return total;    }     public void settotal(int total) {        this.total = total;    }}

second example:

now we are going to try to call the same stored procedure we demonstrated on the first example, but we are going to use a parametermap, like you used to do in version 2.x.

a very important note: this is discouraged, please use inline parameters.

let’s declare the param pojo class as a parametermap:

<parametermap type="param" id="testparametermap">    <parameter property="total" jdbctype="integer" mode="out" /></parametermap>

and the stored procedure statment:

<update id="callgettotalcity2" parametermap="testparametermap" statementtype="callable">    { call gettotalcity(?) }</update>

note that now we use “ ? ” (question mark) to represent each parameter.

third example:

now we are going to call a stored procedure with in and out parameters. let’s follow the same rules as the fisrt example.

we are going to use inline parameters and we are going to create a pojo class to represent our parameter.

mybatis code:

<select id="callgettotalcitystateid" parametertype="param2" statementtype="callable">    { call gettotalcitystateid(        #{stateid, mode=in, jdbctype=integer},        #{total, mode=out, jdbctype=integer})}</select>

param2 pojo:

package com.loiane.model; public class param2 {     private int total;    private int stateid;     public int gettotal() {        return total;    }    public void settotal(int total) {        this.total = total;    }    public int getstateid() {        return stateid;    }    public void setstateid(int stateid) {        this.stateid = stateid;    }}

fourth example:

now let’s try to retrieve a resultset from the stored procedure. for this we are going to use a resultmap.

<resultmap type="state" id="resultstate">    <result property="id" column="state_id"/>    <result property="name" column="state_name"/>    <result property="code" column="state_code"/></resultmap> <select id="callgetstates" resultmap="resultstate" statementtype="callable">    { call getstates()}</select>

state pojo class:

package com.loiane.model; public class state {     private int id;    private string code;    private string name;     public int getid() {        return id;    }    public void setid(int id) {        this.id = id;    }    public string getcode() {        return code;    }    public void setcode(string code) {        this.code = code;    }    public string getname() {        return name;    }    public void setname(string name) {        this.name = name;    }}

2- spmapper – annotations

now let’s try to do the same thing we did using xml config.

annotation for first example (xml):

@select(value= "{ call gettotalcity( #{total, mode=out, jdbctype=integer} )}")@options(statementtype = statementtype.callable)object callgettotalcityannotations(param param);

it is very similiar to a simple select statement, but we have to set the statement type to callable. to do it, we can use the annotation @options .

with annotations, we can only use inline parameters, so we will not be able to represent the second exemple using annotations.

annotation for third example (xml):

the explanation is the same as first example, i am just going to list the code:

@select(value= "{ call gettotalcitystateid( #{stateid, mode=in, jdbctype=integer}, #{total, mode=out, jdbctype=integer})}")@options(statementtype = statementtype.callable)object callgettotalcitystateidannotations(param2 param2);

annotation for fourth example (xml):

i tried to set the fourth example with annotation, but the only thing i’ve got is this:

//todo: set resultmap with annotations/*@select(value= "{ call gettotalcitystateid()}")@options(statementtype = statementtype.callable)/*@results(value = {    @result(property="id", column="state_id"),    @result(property="name", column="state_name"),    @result(property="code", column="state_code"),})*/list<state> callgetstatesannotations();

and it does not work. i tried to search on the mailing list, no luck. i could not find a way to represent a resultmap with annotation and stored procedures. i don’t know if it is a limitation. if you have any clue how to do it, please leave a comment, i will appreciate it!

download