Mybatis应用mysql存储过程查询数据实例

1.创建mysql存储过程，这是个复杂查询加上了判断，比较复杂

create procedure searchalllist (
	in tradingareaid varchar (50),
	in categoryname varchar (100),
	in intelligencesort tinyint unsigned,
	in pricebegin double,
	in priceend double,
	in commodityname varchar (200),
	in flag tinyint unsigned
)
begin
if flag = 0 then
	select
		b.user_business_id businessid,
		b.shop_name,
		b.total_score,
		b.shop_logo,
		b.average_consume,
		d.category_name,
		b.shop_address
	from
		user_business_commodity a
	left join user_business b on b.user_business_id = a.user_business_id
	left join user_business_category c on c.business_id = b.user_business_id
	left join service_category d on d.category_id = c.category_one_id
	where
		1 = 1
	and
	if (
		categoryname is not null
		and length(trim(categoryname)) > 0,
		d.category_name = categoryname,
		1 = 1
	)
	and
	if (
		pricebegin != 0,
		b.average_consume >= pricebegin,
		1 = 1
	)
	and
	if (
		priceend != 0,
		b.average_consume <= priceend,
		1 = 1
	)
	and
	if (
		commodityname is not null
		and length(trim(commodityname)) > 0,
		a. name like concat('%', commodityname, '%'),
		1 = 1
	)
	and b.is_delete = 0
	and b.shop_setup_state = 1
	and a.is_delete = 0
	and c.is_delete = 0
	and d.is_delete = 0
	group by
		a.user_business_id
	order by
		case intelligencesort
	when 1 then
		'b.total_order desc'
	when 2 then
		'b.total_score desc'
	when 3 then
		'b.create_time desc'
	else
		'b.create_time asc'
	end;

else
	select
		b.user_business_id businessid,
		b.shop_name,
		b.total_score,
		b.shop_logo,
		b.average_consume,
		d.category_name,
		b.shop_address
	from
		user_business_commodity a
	left join user_business b on b.user_business_id = a.user_business_id
	left join user_business_category c on c.business_id = b.user_business_id
	left join service_category d on d.category_id = c.category_two_id
	where
		1 = 1
	and
	if (
		categoryname is not null
		and length(trim(categoryname)) > 0,
		d.category_name = categoryname,
		1 = 1
	)
	and
	if (
		pricebegin != 0,
		b.average_consume >= pricebegin,
		1 = 1
	)
	and
	if (
		priceend != 0,
		b.average_consume <= priceend,
		1 = 1
	)
	and
	if (
		commodityname is not null
		and length(trim(commodityname)) > 0,
		a. name like concat('%', commodityname, '%'),
		1 = 1
	)
	and b.is_delete = 0
	and b.shop_setup_state = 1
	and a.is_delete = 0
	and c.is_delete = 0
	and d.is_delete = 0
	group by
		a.user_business_id
	order by
		case intelligencesort
	when 1 then
		'b.total_order desc'
	when 2 then
		'b.total_score desc'
	when 3 then
		'b.create_time desc'
	else
		'b.create_time asc'
	end;

end if;
end;

2.查看存储过程是否创建成功：

show procedure status; 

3.sqlmapper文件：

<select id="searchalllist1" parametermap="searchalllistmap" statementtype="callable" resulttype="com.dongjia168.platform.vo.erp.crm.businessshopresp"> 
    call searchalllist(#{tradingareaid},#{categoryname},#{intelligencesort},#{pricebegin},#{priceend},#{commodityname},#{flag}); 
  </select> 

<parametermap id="searchalllistmap" type="com.dongjia168.platform.vo.erp.crm.businessshopreq"> 
    <parameter property="tradingareaid" jdbctype="varchar" mode="in"/> 
    <parameter property="categoryname" jdbctype="varchar" mode="in"/> 
    <parameter property="intelligencesort" jdbctype="integer" mode="in"/> 
    <parameter property="pricebegin" jdbctype="double" mode="in"/> 
    <parameter property="priceend" jdbctype="double" mode="in"/> 
    <parameter property="commodityname" jdbctype="varchar" mode="in"/> 
    <parameter property="flag" jdbctype="integer" mode="in"/> 
  </parametermap> 

其他和直接调用sql语句一样了

以上这篇mybatis应用mysql存储过程查询数据实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。