mysql ibatis调用分页存储过程 resultmap_Ibatis调用存储过程实现增删改以及分页查询...

1、Ibatis实现增删改操作很简单了，通常我是将某一模块的增删改功能写在一个存储过程里，通过一个标识符去区分执行增加还是修改抑或删除操作。

statement：

{call PRO_STUDENT_SUBMIT(

#xs_id#,

#xs_name#,

#zy_id#,

#xs_num#,

#xs_sfz#,

#xs_tel#,

#xs_email#,

#xs_address#,

#xs_sex#,

#xs_date#,

#bq#

)}

procedure：

create or replace procedure PRO_STUDENT_SUBMIT(

v_xs_id in varchar2,

v_xs_name in varchar2,

v_zy_id in varchar2,

v_xs_num in varchar2,

v_xs_sfz in varchar2,

v_xs_tel in varchar2,

v_xs_email in varchar2,

v_xs_address in varchar2,

v_xs_sex in varchar2,

v_xs_date in varchar2,

v_bq in varchar2

) is

begin

--当bq为1时，执行添加操作；为2时，执行修改操作；为3时执行删除操作

if (v_bq = '1') then

insert into xs

(xs_id, xs_name, zy_id, xs_num, xs_sfz, xs_tel, xs_email, xs_address, xs_sex,xs_date)

values

(seq_xs.nextval , v_xs_name, v_zy_id, v_xs_num, v_xs_sfz, v_xs_tel, v_xs_email, v_xs_address, v_xs_sex,v_xs_date);

end if;

if (v_bq = '2') then

update xs

set xs_id = v_xs_id,

xs_name = v_xs_name,

zy_id = v_zy_id,

xs_num = v_xs_num,

xs_sfz = v_xs_sfz,

xs_tel = v_xs_tel,

xs_email = v_xs_email,

xs_address = v_xs_address,

xs_sex = v_xs_sex,

xs_date=v_xs_date

where xs_id = v_xs_id;

end if;

if (v_bq = '3') then

delete xs

where xs_id = v_xs_id;

end if;

end PRO_STUDENT_SUBMIT;

2、重要要说的是通过ibatis调用存储过程实现分页查询(带模糊查询)，我们需要有一个返回参数，该参数是游标类型。

通过调用存储过程实现的分页查询要比上文介绍的ibatis分页查询性能上好很多。

代码实例如下(经测试完全好使，ibatis版本2.3)

/**

* 在存储过程内实现查询的分页 与 模糊查询

* 需要传入到存储过程的参数 模糊查询的包括 学生姓名 学号 性别 入学时间的起止两个时间 一共5个

* 分页需要的参数 那就是 pageNo pageSize 一共2个 总共需要7个IN参数 返回的肯定是游标了 所以有一个OUT 类型的 CURSOR;

* 这些IN 参数都可以通过page对象传过去

*/

public String queryByPro (){

((XueShengPage)page).setXs_name(xs.getXs_name());

((XueShengPage)page).setXs_num(xs.getXs_num());

((XueShengPage)page).setDateFrom(xs.getPage_dateFrom());

((XueShengPage)page).setDateTo(xs.getPage_dateTo());

((XueShengPage)page).setXs_sex(xs.getXs_sex());

Date nowMilDate = new Date();

long time1 = nowMilDate.getTime();

xsList=this.xsService.queryByPro((XueShengPage)page);

Date time2 = new Date();

LogUtil.info("存储过程实现分页查询用时："+(time2.getTime()-time1));

return "query";

}

public List queryByPro(XueShengPage page) {

int rowCount=this.dao.queryForInt("xuesheng.queryListCount", page);//查询总条数

page.setRowCount(rowCount);

page.accountPageCount();//计算总页数

List xsList=new ArrayList();

Map map1 = new HashMap();

map1.put("xs_name",page.getXs_name());

map1.put("xs_num", page.getXs_num());

map1.put("xs_sex", page.getXs_sex());

map1.put("dateFrom", page.getDateFrom());

map1.put("dateTo", page.getDateTo());

map1.put("pageNo", page.getPageNo());

map1.put("pageSize", page.getPageSize());

this.dao.queryOne("xuesheng.pro_cursor", map1);

List list=(List) map1.get("backcursor");//调用存储过程进行查询

for(int i=0;i

XueSheng xs=new XueSheng();//需要传递模糊查询 和 分页所需要的参数(页号pageNo和每页显示多少条pageSize)

xs.setXs_id(list.get(i).get("xs_id").toString());

xs.setXs_address(list.get(i).get("xs_address").toString());

xs.setXs_date(list.get(i).get("xs_date").toString());

xs.setXs_email(list.get(i).get("xs_email").toString());

xs.setXs_name(list.get(i).get("xs_name").toString());

xs.setXs_num(list.get(i).get("xs_num").toString());

xs.setXs_sex(list.get(i).get("xs_sex").toString());

xs.setXs_sfz(list.get(i).get("xs_sfz").toString());

xs.setXs_tel(list.get(i).get("xs_tel").toString());

xs.setXy_name(list.get(i).get("xy_name").toString());

xs.setZy_id(list.get(i).get("zy_id").toString());

xs.setZy_name(list.get(i).get("zy_name").toString());

xsList.add(xs);

}

return xsList;

}

{call queryXsByPro(?,?,?,?,?,?,?,?)}

create or replace procedure queryXsByPro

(

my_cursor out sys_refcursor,

v_xs_name IN VARCHAR2,

v_xs_num in varchar2,

v_xs_sex in varchar2,

v_dateFrom in varchar2,

v_dateTo in varchar2,

v_pageNo in number,

v_pageSize in number

)

as

xs_begin number(10);--从哪条记录开始查

xs_end number(10);--查到哪条记录结束

v_sql varchar2(1000);

begin

xs_begin:=(v_pageNo-1)*v_pageSize+1;

xs_end :=xs_begin+v_pageSize;

v_sql :=

'SELECT

XS_ID,

XS_NAME,

XS.ZY_ID,

XS_NUM,

XS_SFZ,

XS_TEL,

XS_EMAIL,

XS_ADDRESS,

XS_DATE,

XS_SEX,

ZY.ZY_NAME,

XY.XY_NAME

FROM XS XS

INNER JOIN ZY ZY

ON XS.ZY_ID = ZY.ZY_ID

INNER JOIN XY

ON XY.XY_ID = ZY.XY_ID

WHERE 1=1 ';

if v_xs_sex is not null then

v_sql :=v_sql||' and xs_sex=v_xs_sex ';

end if;

if v_dateFrom is not null then

v_sql :=v_sql||' and to_date(xs_sex,''yyyy-MM-dd'')>=to_date(v_dateFrom,''yyyy-MM-dd'') ';

end if;

if v_dateTo is not null then

v_sql :=v_sql||' and to_date(xs_sex,''yyyy-MM-dd'')<=to_date(v_dateTo,''yyyy-MM-dd'') ';

end if;

v_sql:='select * from (select row_.*, rownum rownum_ from ('||v_sql;

v_sql:=v_sql || ')row_ where rownum<=' || xs_end || ' ) where rownum_>=' || xs_begin;

open my_cursor for v_sql;

end;