Mybatis如何调用oracle存储过程？入参为日期类型

文章目录

1. 使用Map封装入参、出参数据2. 使用对象封装入参、出参数据

存储过程如下：

传入开始、结束日期，并返回对应日期内的数据！

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")@ResponseBodypublic 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

@Servicepublic class NocarService {@AutowiredNocarDao nocarDao;public void getNocarInfo(HashMap map){nocarDao.getNocarInfo(map);}}

Dao

@Mapperpublic interface NocarDao {void getNocarInfo(@Param("map") HashMap map);}

XML文件

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/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@AllArgsConstructorpublic class TestParam {private Date in_beginDate; //入参：开始日期private Date in_endDate;//入参：结束日期private List<Map> cursor;//出参：游标}

Controller

@RequestMapping("/getCarInfo")@ResponseBodypublic 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

@Mapperpublic interface CarDao {void getCarInfo1(@Param("testParam")TestParam testParam);}

XML文件

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/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>

同样可以调用成功