Thursday

Call pl/sql package by Ibatis 3

Last few weeks i have been working with a project ,where we decided to use Ibatis for generating complex report. Ibatis is a very small smart ORM tools to execute complex query but, in version 3, developers made a vast change and it's difficult to migrate from version 2 to version 3.
In this post i am going to describe how to call pl/sql package function from within Ibatis3.
First of all we will create two small tables and a pl/sql package for demonstration:
-- Create table
create table ADDRESSES
(
ADR_ID      INTEGER not null,
ADR_CITY    VARCHAR2(15),
ADR_COUNTRY VARCHAR2(15) not null
);
alter table ADDRESSES
add primary key (ADR_ID);
create table PERSONS
(
PRS_ID         INTEGER not null,
PRS_FATHER_ID  INTEGER,
PRS_MOTHER_ID  INTEGER,
PRS_ADR_ID     INTEGER,
PRS_FIRST_NAME VARCHAR2(15),
PRS_SURNAME    VARCHAR2(15)
);
/
alter table PERSONS
add constraint PRS_ADR_FK foreign key (PRS_ADR_ID)
references ADDRESSES (ADR_ID);
alter table PERSONS
add constraint PRS_PRS_FATHER_FK foreign key (PRS_FATHER_ID)
references PERSONS (PRS_ID);
alter table PERSONS
add constraint PRS_PRS_MOTHER_FK foreign key (PRS_MOTHER_ID)
references PERSONS (PRS_ID);

create or replace package IbatisTest is

function getPersonsById(p_id integer) return varchar2;
function addPerson(p_name varchar2, p_fname varchar2, p_add integer)return integer;

end IbatisTest;
/
create or replace package body IbatisTest is

function getPersonsById(p_id integer) return varchar2 is
l_name varchar2(200);
begin
select 
p.prs_first_name
into l_name       
from persons p
where p.prs_id = p_id;    

return(l_name);
end;
function addPerson(p_name varchar2, p_fname varchar2, p_add integer)return integer 
is
p_id integer;       
begin
select common_seq.nextval 
into p_id 
from dual;
insert into persons(prs_id, prs_first_name, prs_surname, prs_adr_id) 
values (p_id, p_fname, p_name, p_add) returning prs_id into p_id;
commit;       
return (p_id);
end;  

begin
null;
end IbatisTest;
/

next we we will develop our *Mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.blue.ibatis.test.dao.FooMapper">
<parameterMap id="parameters1" type="LRU">
<parameter property="name" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="id" jdbcType="NUMERIC" javaType="java.lang.Long" mode="IN"/>
</parameterMap>

<parameterMap id="addParameters" type="LRU">
<parameter property="p_id" jdbcType="NUMERIC" javaType="java.lang.Long" mode="OUT"/>
<parameter property="p_name" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="p_fname" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="p_add" jdbcType="NUMERIC" javaType="java.lang.Long" mode="IN"/>
</parameterMap>

<select statementType="CALLABLE" id="getPersonsById" parameterMap="parameters1" resultType="String">
{ ? = call IbatisTest.getPersonsById( ? ) }
</select>

<select statementType="CALLABLE" id="addPerson" parameterMap="addParameters" resultType="Integer">
{ ? = call IbatisTest.addPerson( ?,?,? ) }
</select>
</mapper>

and on finish we have a few fragments of java code to call pl/sql package function
// query to addPersons
Map pMap = new HashMap();
pMap.put("p_name","Xyz");
pMap.put("p_fname","qwe");
pMap.put("p_add", Long.valueOf(11l));

session.selectOne("com.blue.ibatis.test.dao.FooMapper.addPerson", pMap);
pMap.get("p_id");
Post a Comment