import java.io.Serializable;
import java.util.Date;
public class User implements Serializable{
private static final long serialVersionUID = -6919964218508186044L;
private int id;
private String name;
private Date birthday;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD iBatis Mapper 3.0 //EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="org.yhb.ibatis.dao.UserDAO">
<!-- 表结构
create table IBATIS_USER
(
ID NUMBER not null,
NAME VARCHAR2(20) not null,
BRITHDAY DATE not null
)
-->
<!-- 存储过程
create or replace procedure getAllUser(userList out sys_refcursor)
as
begin
open userList for select * from ibatis_user;
end;
-->
<!-- resultMap -->
<resultMap type="User" id="userMap">
<id column="id" property="id" />
<result column="name" property="name" />
<result column="birthday" property="birthday" />
</resultMap>
<!-- 调用存储过程 -->
<select id="getAllUser" statementType="CALLABLE">
{call
getAllUser(#{userList,<!-- 参数 -->
mode=OUT,<!-- 参数类型 -->
javajavaType=java.sql.ResultSet,<!-- 参数java类型 -->
jdbcType=CURSOR,<!-- 参数jdbc类型 -->
resultMap=userMap<!-- ResultSet需要resultMap参数 -->
})}
</select>
</mapper>
@Test
public void testProcedure() throws Exception {
Reader reader = null;
reader = Resources.getResourceAsReader("configuration.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
reader.close();
SqlSession session = ssf.openSession();
Map map = new HashMap();
session.selectOne("org.yhb.ibatis.dao.UserDAO.getAllUser", map);
System.out.println(map);
//返回的集合被放入了map中
List<User> userList = (List<User>) map.get("userList");
System.out.println(userList);
session.close();
}
---------------------------------------------------------------------
--定义包声明
create or replace package pkg_stu
as
type list_stu is ref cursor;--定义游标,通过游标将数据模型结果集返回给关系模型
procedure proc_findStuList(stulist out list_stu,s_id number);
function fun_findStuList(s_id number) return list_stu;
end pkg_stu;
--包主体声明
create or replace package body pkg_stu
as
procedure proc_findStuList(stulist out list_stu,s_id number)--实现查找所有学生的存储过程
is
sqlString varchar2(500);
begin
if s_id = 0 then
open stulist for select sid,sname,major,birth,socre from student order by sid;
else
sqlString := 'select sid,sname,major,birth,socre from student where sid=:sid';
open stulist for sqlString using s_id;
end if;
end proc_findStuList;
function fun_findStuList(s_id number) --实现查找所有学生的函数
return list_stu
is
stulist list_stu;
sqlString varchar2(500);
begin
if s_id = 0 then
open stulist for select sid,sname,major,birth,socre from student order by sid;
else
sqlString := 'select sid,sname,major,birth,socre from student where sid=:sid';
open stulist for sqlString using s_id;
end if;
return stulist;
end fun_findStuList;
end pkg_stu;
<!-- 声明结果集类型参数 -->
<resultMap class="Student" id="stuAccount">
<result property="sid" column="SID" columnIndex="1"/>
<result property="sname" column="SNAME" columnIndex="2"/>
<result property="major" column="MAJOR" columnIndex="3"/>
<result property="birth" column="BIRTH" columnIndex="4"/>
<result property="socre" column="SOCRE" columnIndex="4"/>
</resultMap>
<!-- -->
<parameterMap id="parameterMap" class="java.util.HashMap">
<parameter property="stulist" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT" />
<parameter property="s_id" jdbcType="NUMBER" javaType="java.lang.Integer" mode="IN"/>
</parameterMap>
<!-- 存储过程的调用方式 -->
<procedure id="pkgPro_stu_cursor" parameterMap="parameterMap" resultMap="stuAccount">
{call pkg_stu.proc_findStuList(?,?)}
</procedure>
<!-- 函数的调用方式 -->
<procedure id="fun_stu_cursor" parameterMap="parameterMap" resultMap="stuAccount">
{? = call pkg_stu.fun_findStuList(?) }
</procedure>
@SuppressWarnings("unchecked")
public static void main(String[] args) {
//存储过程的调用
// IStudentDAO dao = new IStudentDAOImpl();
//
// Map parameterMap = new HashMap();
// parameterMap.put("s_id", 0);
// List<Student> accoutList = dao.queryPkgProStudentList(parameterMap);
// if(accoutList.size()>0){
// for(Student temp:accoutList){
// if(temp!=null){
// System.out.println(temp.getBirth().toLocaleString()+"---"+temp.getMajor());
// }
// }
// }
// System.out.println("-------------------------");
// System.out.println("size:"+accoutList.size());
//函数的调用
IStudentDAO dao = new IStudentDAOImpl();
Map parameterMap = new HashMap();
parameterMap.put("s_id", 0);
List<Student> accoutList = dao.queryFunStudentList(parameterMap);
if(accoutList.size()>0){
for(Student temp:accoutList){
if(temp!=null){
System.out.println(temp.getBirth().toLocaleString()+"---"+temp.getMajor());
}
}
}
System.out.println("-------------------------");
System.out.println("size:"+accoutList.size());
}
@Override
public List queryFunStudentList(Map parameterMap) {
List result = null;
try {
result = sqlMapClient.queryForList("fun_stu_cursor",parameterMap);
System.out.println("size:"+result.size());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
@Override
public List queryPkgProStudentList(Map parameterMap) {
// TODO Auto-generated method stub
List result = null;
try {
result = sqlMapClient.queryForList("pkgPro_stu_cursor",parameterMap);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}