需求
查询出当前用户在4张应征表中的部分字段。并且应征状态不等于3(应征失败)
存储过程脚本（含分页）
create procedure myApplyList (
in apply_id varchar(50),
in startRecord int,
in pageSize int
)
select * from (
select
base.order_id as order_id,
base.creator_id as creator_id,
base.contact_name as contact_name,
base.order_type as order_type,
base.order_status as order_status,
base.order_title as order_title,
unix_timestamp(base.create_date) as create_date,
enq.user_id as applicant_id
from
base_order as base,
apply_1 as enq
where
base.order_id = enq.order_id and enq.`status` != 3 and enq.user_id=apply_id
union
select
base.order_id as order_id,
base.creator_id as creator_id,
base.contact_name as contact_name,
base.order_type as order_type,
base.order_status as order_status,
base.order_title as order_title,
unix_timestamp(base.create_date) as create_date,
oth.applicant_id as applicant_id
from
base_order as base,
apply_2 as oth
where
base.order_id = oth.order_id and oth.apply_state != 3 and oth.applicant_id=apply_id
union
select
base.order_id as order_id,
base.creator_id as creator_id,
base.contact_name as contact_name,
base.order_type as order_type,
base.order_status as order_status,
base.order_title as order_title,
unix_timestamp(base.create_date) as create_date,
adv.applicant_id as applicant_id
from
base_order as base,
apply_3 as adv
where
base.order_id = adv.order_id and adv.apply_state != 3 and adv.applicant_id=apply_id
union
select
base.order_id as order_id,
base.creator_id as creator_id,
base.contact_name as contact_name,
base.order_type as order_type,
base.order_status as order_status,
base.order_title as order_title,
unix_timestamp(base.create_date) as create_date,
coo.applicant_id as applicant_id
from
base_order as base,
apply_4 as coo
where
base.order_id = coo.order_id and coo.apply_state != 3 and coo.applicant_id=apply_id
) apply_All order by create_date desc limit startRecord, pageSize
-- 调用示例：call myApplyList('4c15c296-cb08-4811-84ad-58b5e0774001',0,20);
sql结果
Mybatis代码调用
JavaBean
public class BaseOrderApplyList {
private String orderId;
private int orderType;
private int orderStatus;
private String orderTitle;
private Long createDate;
private String creatorId;
private String contactName;
// 省略getter/setter代码
}
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.wusong.firefly.dao.mysql.firefly.BaseOrderApplyListMapper">
<resultMap id="BaseResultMap" type="com.wusong.firefly.domain.firefly.BaseOrderApplyList">
<result column="order_id" property="orderId"/>
<result column="creator_id" property="creatorId"/>
<result column="contact_name" property="contactName"/>
<result column="order_type" property="orderType"/>
<result column="order_status" property="orderStatus"/>
<result column="order_title" property="orderTitle"/>
<result column="create_date" property="createDate"/>
</resultMap>
<sql id="Base_Column_List">
order_id, creator_id, contact_name, order_type, order_status, order_title, create_date
</sql>
<select id="listBaseOrderByApplyId" resultMap="BaseResultMap" parameterType="Map" statementType="CALLABLE">
{
call myApplyList(
#{applyId, jdbcType=VARCHAR, mode=IN},
#{startRecord, jdbcType=INTEGER, mode=IN},
#{pageSize, jdbcType=INTEGER, mode=IN}
)
}
</select>
</mapper>
Mapper接口
package com.wusong.firefly.dao.mysql.firefly;
import com.wusong.firefly.domain.firefly.BaseOrderApplyList;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface BaseOrderApplyListMapper {
/**
* 我的接单列表，包含所有类型订单，除了应征失败所有订单
* @param applyId
* @param startRecord
* @param pageSize
* @return
*/
List<BaseOrderApplyList> listBaseOrderByApplyId(@Param("applyId") String applyId, @Param("startRecord") Integer startRecord, @Param("pageSize") Integer pageSize);
}
service接口
/** * 我的接单列表 * @param applyId * @param pageNo * @param pageSize * @return */ List<BaseOrderApplyList> listBaseOrderByApplyId(String applyId, Integer pageNo, Integer pageSize);
service接口实现类
@Override
public List<BaseOrderApplyList> listBaseOrderByApplyId(String applyId, Integer pageNo, Integer pageSize) {
int startRecord = pageNo * pageSize;
return baseOrderApplyListMapper.listBaseOrderByApplyId(applyId, startRecord, pageSize);
}
其他步骤与普通的Controller调用相同，不做过多编写。主要关注点，在于xml的映射中，如何调用存储过程。
List<BaseOrderApplyList> baseOrderApplyLists = baseOrderService.listBaseOrderByApplyId(userId, pageNo, pageSize);
相关阅读
MySql的存储过程：https://www.youcl.com/info/3195
【SSM】MyBatis对数据库的增删改查操作：https://www.youcl.com/info/3197
【数据库】存储过程和函数的区别：https://www.youcl.com/info/3242
