java多字段模糊搜索

今天产品提了一个需求要求多个字段模糊搜索

 

 

 

 

具体实现看代码：

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>