今天产品提了一个需求要求多个字段模糊搜索
具体实现看代码:
import com.talebase.common.core.base.BasePageQuery;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
/**
* 人员表分页请求DTO
*
* @author zipeng.yuan
* @date 2021-12-01 22:21:44
*/
@Data
public class ListPagePersonDTO extends BasePageQuery implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 组织架构部门id
*/
@ApiModelProperty(value = "组织架构部门id")
private Integer personDeptId;
/**
* 搜索关键字,包含姓名、职位、部门搜索
*/
@ApiModelProperty(value = "搜索关键字,包含姓名、职位、部门搜索")
private List<String> searchKey;
}
<select id="listPagePersonByDeptId" resultType="com.talebase.base.vo.person.ListPagePersonVO">
SELECT
p.id,
p.`name`,
p.account,
p.gender,
TIMESTAMPDIFF(YEAR, p.birthday, CURDATE()) AS age,
pd.`name` AS personDeptName,
pp.`name` AS personPostName
FROM
person_dept_rel pdr
LEFT JOIN person p ON pdr.person_id = p.id
LEFT JOIN person_dept pd ON pdr.person_dept_id = pd.id
LEFT JOIN person_post_rel ppr ON ppr.person_id = p.id
LEFT JOIN person_post pp ON ppr.person_post_id = pp.id
WHERE 1=1
<if test="listPagePersonDTO.personDeptId != '' and listPagePersonDTO.personDeptId != null">
AND pd.id = #{listPagePersonDTO.personDeptId}
</if>
<if test="listPagePersonDTO.searchKey != null and listPagePersonDTO.searchKey.size() > 0">
AND
<foreach item="item" index="index" collection="listPagePersonDTO.searchKey" open="(" separator="OR" close=")">
p.`name` LIKE CONCAT('%', #{item},'%')
OR pd.`name` LIKE CONCAT('%', #{item},'%')
OR pp.`name` LIKE CONCAT('%', #{item},'%')
</foreach>
</if>
order by p.id
</select>