第一种,根据创建时间查询,搜索框中开始时间和结束时间,在搜索的时间段中包含创建时间的记录被查询出来
<if test="(null!=timeStart and timeStart !='' ) and (null!=timeEnd and timeEnd !='' )"> AND a.insert_date BETWEEN #{timeStart} AND #{timeEnd} </if>
第二种,模糊查询,根据关键字模糊查询
<if test="null!=carId and carId !=''">
AND a.car_id like '%${carId}%'
</if>
第三种,统计,如果统计结果只有一条并且只有一列,那么直接返回integer类型即可
int getCount(Map<String,Object> paramMap);
<select id="getCount" resultType="java.lang.Integer">
SELECT
COUNT(id)
FROM
`tb_admin`
<if test="province == null || province == ''">
where role_id IN(SELECT role_id FROM tb_role)
</if>
<if test="province != null">
where role_id IN(
SELECT role_id FROM tb_role where
province = #{province,jdbcType=VARCHAR}
<if test="city == null || city == ''">
AND city is not null
</if>
<if test="city != null and city != ''">
AND city = #{city,jdbcType=VARCHAR}
<if test="partition == null || partition == ''">
AND `PARTITION` is not null
</if>
<if test="partition != null">
AND `PARTITION` = #{partition,jdbcType=VARCHAR}
</if>
</if>
)
</if>
</select>
如果有多行并且有多列,那么,使用实体类对返回结果进行包装
实体类中
@Transient private String total;
@TableField("order_status") private String orderStatus;
mapper.xml中
<result column="total" property="total"/> <result column="order_status" property="orderStatus"/>
<select id="selectByStationStaffId" parameterType="java.lang.String" resultMap="BaseResultMap"> SELECT COUNT(order_no) AS total, order_status FROM tb_tax_order WHERE station_staff_id = #{stationStaffId} AND DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <![CDATA[ <= ]]> date(insert_date) GROUP BY order_status </select>