News

This week The apache Ignite book becomes one of the top books of leanpub

This week The apache Ignite book becomes one of the top books of leanpub.

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");

No comments :