mybatis的sql标签使用实例

本文来说个mybatis的sql标签使用的例子说明下sql标签应该如何使用。

---

sql标签说明

mybatis文档说明：https://mybatis.org/mybatis-3/sqlmap-xml.html

---

实例说明

表结构

mapper文件信息

<?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="cn.wideth.mapper.OrderMapper">

    <sql id="timeRange">
        <choose>
            <when test="type==1">
                AND TO_DAYS(${columnName}) = TO_DAYS(NOW())
            </when>
            <when test="type==2">
                AND  YEARWEEK(${columnName},1) = YEARWEEK(NOW(),1)
            </when>
            <when test="type==3">
                AND  MONTH(${columnName}) = MONTH(NOW())
            </when>
            <otherwise>
                AND YEAR(${columnName}) = YEAR(NOW())
            </otherwise>
        </choose>
    </sql>

    <select id="getOrderInfo" resultType="java.lang.Integer">
        SELECT COUNT(T.ID) number
        FROM order_info T
        WHERE 1=1
        <include refid="timeRange">
            <property name="columnName" value="time"/>
        </include>
    </select>
    
</mapper>

controller

@Slf4j
@RestController
@RequestMapping("/api/order")
@Api(tags = "订单的基本信息")
public class OrderController {

    @Autowired
    private IOrderService iOrderService;


    @GetMapping("/getOrderInfo")
    @ApiOperation(value = "订单的基本信息")
    public ResponseBean<?> getOrderInfo(String type) {

        Integer data = iOrderService.getOrderInfo(type);
        if (data == null) {
            return new ResponseBean<>(400, "fail", Collections.emptyList());
        }
        return new ResponseBean<>(200, "success", data);
    }


}

service

@Service
public class OrderServiceImpl implements IOrderService {

    @Autowired
    private OrderMapper studentMapper;

    @Override
    public Integer getOrderInfo(String type) {

        Integer data = studentMapper.getOrderInfo(type);
        return data;
    }
    
}