1.创建一个包
此处创建一个包,是为了创建存储过程时,用游标作为out输出参数时声明为游标类型用的.
--创建一个包
create or replace
package types
as
type empListCursor is ref cursor;
end types;
2.创建存储过程
用in表示存储过程的输入参数,用out表示存储过程的输出参数,此处输出参数为游标.
CREATE OR REPLACE PROCEDURE QUERYEMPSBYDEPTNO(pdeptno in Integer,empList out types.empListCursor) is
BEGIN
if pdeptno=0 then
open empList for select * from emp;
else
open empList for select * from emp where deptno=pdeptno;
end if;
END QUERYEMPSBYDEPTNO;
3.EmpMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.casic.dao.EmpMapper">
<resultMap id="resultMap3" type="com.casic.model.Emp">
<result property="empno" column="empno"/>
<result property="ename" column="ename"/>
<result property="job" column="job"/>
<result property="mgr" column="mgr"/>
<result property="hiredate" column="hiredate"/>
<result property="sal" column="sal"/>
<result property="comm" column="comm"/>
<result property="deptno" column="deptno"/>
</resultMap>
<!-- 根据部门编号 查询员工信息列表 -->
<!--
statementType="CALLABLE" :表明调用的是存储过程;
parameterType="java.util.Map" :参数是一个map,所以在传参时需要传入一个map集合.(我尝试了其他的类型,比如int,不行!这点跟mysql不一样);
-->
<select id="queryEmpByDeptno" statementType="CALLABLE" parameterType="java.util.Map" >
<!--传入传出参数要注明mode=IN/OUT 并要注明jdbcType(在网上可以查询mybatis支持哪些jdbcType类型),返回参数要注明对应的resultMap
注意:这里pdeptno,result都是参数map集合的key值.
-->
{call QUERYEMPSBYDEPTNO(#{pdeptno,mode=IN,jdbcType=INTEGER},#{result,jdbcType=CURSOR,mode=OUT,javaType=ResultSet, resultMap=resultMap3})}
</select>
</mapper>
4.EmpMapper.java
package com.casic.dao;
import java.util.List;
import java.util.Map;
import com.casic.model.Emp;
public interface EmpMapper {
/*
* 根据部门编号加载员工信息列表
*/
List<Emp> queryEmpByDeptno(Map<String, Object> param );
}
5.EmpService.java
package com.casic.service;
import java.util.List;
import java.util.Map;
import com.casic.model.Emp;
public interface EmpService {
/*
* 根据部门编号,加载员工信息列表
*/
List<Emp> queryDeptEmps(Map<String, Object> param);
}
6.EmpServiceImpl.java
package com.casic.service.impl;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.casic.dao.EmpMapper;
import com.casic.model.Emp;
import com.casic.service.EmpService;
@Service("empService")
public class EmpServiceImp implements EmpService {
@Autowired
private EmpMapper empMapper;
public List<Emp> queryDeptEmps(Map<String, Object> param) {
//查询的过程中,已经将结果集封装到了param集合中
empMapper.queryEmpByDeptno(param);
//根据key获取到结果集,并进行强转
List<Emp> empList=(List<Emp>)param.get("result");
return empList;
}
}
7.EmpController.java
package com.casic.controller;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import com.casic.model.Emp;
import com.casic.service.EmpService;
import oracle.jdbc.driver.OracleTypes;
@Controller
@RequestMapping("/empController")
public class EmpController {
@Autowired
private EmpService empService;
/*
* 根据部门编号查询员工信息列表
*/
@RequestMapping("/queryEmp")
public String showDeptEmps(Emp emp,Model model){
Map<String, Object> param = new HashMap<String, Object>();
//对于in参数赋值
param.put("pdeptno",emp.getDeptno());
//对于out参数 申明
param.put("result",OracleTypes.CURSOR);
List<Emp> emps = empService.queryDeptEmps(param);
model.addAttribute("emps", emps);
return "showEmps";
}
}
8.showEmps.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>emp list</title>
</head>
<body>
<table cellspacing="1" bordercolor="#990000" cellpadding="1" border="1px">
<tr>
<td>
<form action="queryEmp" method="get">
<label>部门编号:</label>
<select name="deptno">
<option value="0">全部</option>
<option value="10">10</option>
<option value="20">20</option>
<option value="30">30</option>
<option value="40">40</option>
</select>
<input type="submit" value="Research">
</form>
</td>
</tr>
<tr>
<th>序号</th>
<th>编号</th>
<th>姓名</th>
<th>职位</th>
<th>领导编号</th>
<th>入职日期</th>
<th>工资</th>
<th>奖金</th>
<th>部门编号</th>
</tr>
<c:forEach items="${emps}" var="emp" varStatus="vs">
<tr>
<td>${vs.count }</td>
<td>${emp.empno }</td>
<td>${emp.ename }</td>
<td>${emp.job }</td>
<td>${emp.mgr }</td>
<td><fmt:formatDate pattern="yyyy-MM-dd" value="${emp.hiredate}" /></td>
<td>${emp.sal }</td>
<td>${emp.comm }</td>
<td>${emp.deptno }</td>
</tr>
</c:forEach>
</table>
</body>
</html>