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

?

存储过程如下:

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

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>

调用结果:
Mybatis如何调用oracle存储过程?入参为日期类型
?

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>

同样可以调用成功

?

上一篇:MySQL基础 - 基于成本的优化


下一篇:在win10环境下安装postgresql