?
存储过程如下:
传入开始、结束日期,并返回对应日期内的数据!
create or replace PROCEDURE PROC_GETGONGGUREPORT(
in_beginDate Date, //入参:开始日期
in_endDate Date, //入参:结束日期
cursor_out out sys_refcursor //出参:游标
)
AS in_nextDate date :=in_endDAte +1;
BEGIN
。。。。//省略代码
?
1. 使用Map封装入参、出参数据
Controller:
@RequestMapping("/getNoCarInfo")
@ResponseBody
public R TESTPRO2(
@RequestParam(value = "startTime", required = false) String startTime,
@RequestParam(value = "endTime", required = false) String endTime
) throws ParseException {
HashMap map = new HashMap();
map.put("in_beginDate", startTime); //入参:开始日期
map.put("in_endDate", endTime); //入参:结束日期
map.put("cursor", null); //出参:游标
nocarService.getNocarInfo(map); //调用存储过程
System.out.println(map.get("cursor") + "***************");
List<Map> returnMap = (List<Map>) map.get("cursor"); //以map的形式获取结果
Service
@Service
public class NocarService {
@Autowired
NocarDao nocarDao;
public void getNocarInfo(HashMap map){
nocarDao.getNocarInfo(map);
}
}
Dao
@Mapper
public interface NocarDao {
void getNocarInfo(@Param("map") HashMap map);
}
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.chinalife.renshang.nocar.NocarDao">
<!--游标结果封装 -->
<resultMap id="cursorNocarMap" type="java.util.LinkedHashMap"></resultMap>
<!--调用存储过程 -->
<select id="getNocarInfo" statementType="CALLABLE" parameterType="map">
<![CDATA[
call PROC_GETGONGGUREPORT(
to_date( #{map.in_beginDate,mode=IN,jdbcType=DATE},‘yyyy-MM-dd‘),
to_date(#{map.in_endDate,mode=IN,jdbcType=DATE},‘yyyy-MM-dd‘),
#{map.cursor,mode=OUT,jdbcType=CURSOR,resultMap=cursorNocarMap
})
]]>
</select>
</mapper>
调用结果:
?
2. 使用对象封装入参、出参数据
对象
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TestParam {
private Date in_beginDate; //入参:开始日期
private Date in_endDate; //入参:结束日期
private List<Map> cursor; //出参:游标
}
Controller
@RequestMapping("/getCarInfo")
@ResponseBody
public R getCarInfo(
@RequestParam(value = "startTime", required = false) String startTime,
@RequestParam(value = "endTime", required = false) String endTime
) throws ParseException {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date start = format.parse(startTime);
Date end = format.parse(endTime);
//封装对象
TestParam testParam = new TestParam(start, end, null);
//调用存储过程
carService.getCarInfo1(testParam);
List<Map> carMap = testParam.getCursor();
Dao
@Mapper
public interface CarDao {
void getCarInfo1(@Param("testParam")TestParam testParam);
}
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.chinalife.renshang.car.CarDao">
<resultMap id="cursorCarMap" type="java.util.LinkedHashMap"></resultMap>
<!--调用存储过程 -->
<select id="getCarInfo1" statementType="CALLABLE" parameterType="com.chinalife.renshang.car.TestParam" >
<![CDATA[
call PROC_GETGONGGUREPORT(
#{testParam.in_beginDate,mode=IN,jdbcType=DATE},
#{testParam.in_endDate,mode=IN,jdbcType=DATE},
#{testParam.cursor,mode=OUT,jdbcType=CURSOR,resultMap=cursorNocarMap
})
]]>
</select>
</mapper>
同样可以调用成功
?