1.问题:mybatis 框架下 无法处理 FOR XML PATH('')函数
2.处理思路:把sql放到储存过程中
3.写存储过程:
ALTER PROCEDURE [dbo].[PR_D]
@para1 AS varchar(1000) ,
@para2 AS int ,
@sqlbegin AS varchar(max),
@string AS varchar(max) ,
@sqlend AS varchar(max)
AS
BEGIN
set @sqlbegin ='SELECT a.dinnerDate, a.mealTime, a.distributionAreaID, a.distributionAreaName, a.deptName, a.empName , LEFT (a.description, LEN(a.description) - 1) AS description
FROM ( SELECT s.mealTime,s.dinnerDate,e.empName,d.deptName,di.id AS distributionAreaID,di.name AS distributionAreaName , ( SELECT a.foodName +'' ''+ CONVERT (VARCHAR, a.share)+'' , ''
FROM EmpDinner a
WHERE a.delFlag = 0
AND a.isReturn = 0
AND a.dinnerType IN (0, 1)
AND a.mealTime = s.mealTime
AND a.dinnerDate = s.dinnerDate
AND a.empID = e.empID
FOR XML PATH('''')
) AS description
FROM EmpDinner s
LEFT JOIN Employee e ON s.empID = e.empID
LEFT JOIN Depart d ON e.deptID = d.deptID
LEFT JOIN DistributionArea di ON CHARINDEX( '','' + CONVERT (VARCHAR, d.deptID) + '','', '','' + di.distributionDeptID + '','' )>0
WHERE s.delFlag = 0 AND s.isReturn = 0 and s.dinnerType in(0,1)
and s.dinnerDate is not null and s.empID is not NULL and di.name is not null '
set @sqlend =' GROUP BY e.empID,e.empName,d.deptName, s.dinnerDate,s.mealTime, di.name,di.id )a
ORDER BY a.dinnerDate desc ,a.mealTime,a.distributionAreaName,a.empName'
if @para1 <>'' and @distributionAreaID is not null
SET @string =@string+ ' and a.para1 ='+''''+@para1 +''''
if @para2 >0
SET @string =@string+ ' and a.para2 ='+convert(varchar,@para2 )
EXEC( @sqlbegin+@string+@sqlend )
END
4.mybatis 调用:
<select id="name" resultType="entity">
EXEC PR_D @para1 =#{para1 },@para1 =#{para1 } ,@sqlbegin='',@string='',@sqlend=''
</select>