关于Mybatis调用Oracle存储的一次应用

[Santorini_t]

引用

1.Oracle存储代码部分，返回值为游标

CREATE OR REPLACE PROCEDURE GETINVENTORY(
	FModel VARCHAR2,
	FCategory VARCHAR2,
	FStartDate VARCHAR2,
	FEndDate VARCHAR2,
	returnDS OUT sys_refcursor)

引用

2.控制层方法

@RequestMapping(value = {"inventoryData"})
    @ResponseBody
    public String inventoryData(Inventory inventory, HttpServletRequest request, HttpServletResponse response, Model model) {
//        格式化日期，存储中对日期参数有格式要求，在此格式一下
        SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");
        Page<Inventory> page = new Page<Inventory>(request, response);
//        map接收参数
        Map map = new HashMap();
        map.put("FModel", inventory.getSpecificationsId());
        map.put("FCategory", inventory.getSalverTypeID());
        map.put("FStartDate", date.format(inventory.getStartDate()));
        map.put("FEndDate", date.format(inventory.getEndDate()));
//        map.put("returnDS",OracleTypes.CURSOR);此处有的资料说要传入返回值参数，但是传了查不到结果 
        inventoryService.getList(map);
        List<Inventory> list = (List<Inventory>) map.get("returnDS");//在这接收返回的游标
        page.setList(list);
        return gson.toJson(page, Page.class);
    }

引用

service和dao接口方法

public List<Inventory> getList(Map map) {
        return inventoryDao.getList(map);
    }

 public List<Inventory> getList(Map map);

引用

3.实体对象及部分属性

public class Inventory extends DataEntity<Inventory> {

    private String no;
    private String specificationsId; //规格ID
    private String specificationsName; //规格名称
    private String salverTypeID; //分类
    private String salverType; //分类
    private Integer initAmount;//期初数量
    private Integer stageIntoAmount;//本期入库
    private Integer stageComeAmount;//本期出库
    private Integer endAmount;//期末结存
    private Integer realAmount;//在库数量
    private Integer maintainAmount;//维修数量
    private Integer scrapAmount;//报废数量
    private Date startDate;//开始日期
    private Date endDate;//结束日期

引用

4.sql语句xml

<!-- 结果集映射 column值为数据库中字段值，property值为实体对应字段-->  
    <resultMap id="InventoryMap" type="com.thinkgem.jeesite.modules.yg.store.entity.Inventory">  
        <result column="FModel" property="specificationsName" />  
        <result column="FCategory" property="salverType" />  
        <result column="FBegQty" property="initAmount" />  
        <result column="FPeriodInQty" property="stageIntoAmount" />  
        <result column="FPeriodOutQty" property="stageComeAmount" />  
        <result column="FEndQty" property="endAmount" />  
        <result column="FInventory" property="realAmount" />  
        <result column="FRepairQty" property="maintainAmount" />          
        <result column="FScrapQty" property="scrapAmount" />          
    </resultMap> 
    
    <select id="getList" parameterType="java.util.Map" statementType="CALLABLE">
    <![CDATA[ 
        {CALL GETINVENTORY (
            #{FModel,mode=IN,jdbcType=VARCHAR},
            #{FCategory,mode=IN,jdbcType=VARCHAR},
            #{FStartDate,mode=IN,jdbcType=VARCHAR},
            #{FEndDate,mode=IN,jdbcType=VARCHAR},
            #{returnDS,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=InventoryMap}
        )}
    ]]>
    </select>

引用

5查询结果

第一次发帖，欢迎指正！更好的方法，一起分享！