xml书写sql参数包含数组,like

本文探讨了如何在SQL查询中高效利用AND与LIKE操作符,通过智能合并条件并实现模糊搜索,提升数据库查询性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

<if test"table.column!=null and table.column!='' ">

AND <foreach collection = "table.column"iteam = "iteam">

       open="("close=")"separator="or"

         column = #{iteam}

         </foreach>

</if>

 

<if test"table.column!=null and table.column!='' ">

AND table like '${table.column}%'

</if>

<think>我们面临的需求是:在MyBatis中实现多选职称查询,同时支持其他动态查询条件。职称分类包括:主任医师、副主任医师、主治医师、医师、其他(其中“其他”包括数据库中存储的“不详”、“未知”、“其他”、空白和null等)。此外,我们还需要支持其他动态条件(比如科室、姓名等)的查询。 根据之前的讨论,职称分类的规则如下: - 主任医师:所有含“主任”的职称,不包括副主任医师。 - 副主任医师:所有含“副主任”的职称。 - 主治医师:所有含“主治”或“主管”的职称。 - 医师:所有含“医师”的职称,且不包含以上三类前缀的医师。 - 其他:除以上归类之外的所有职称名称,以及“不详”、“未知”、“其他”、空白和null等。 但是,数据库中存储的职称字段可能是具体的字符串(如“主任医师”、“副主任医师”等),也可能是“不详”、“未知”、“其他”、空白或null。而我们的页面标签是:全部、主任医师、副主任医师、主治医师、医师、其他。 注意:由于数据库中存储的职称字段是具体的字符串,而我们需要按照规则进行归类,因此不能简单地使用等值匹配,而是需要使用LIKE或者CASE WHEN等条件判断。 然而,在实现多选查询时,如果用户选择了多个标签(比如同时选择“主任医师”和“副主任医师”),我们需要将符合这两类规则的记录都查出来。 此外,我们还需要支持其他动态条件(如科室、姓名等)的查询。 因此,我们可以采用以下方案: 1. 在SQL中,我们使用CASE表达式将职称字段按照规则映射到上述五个分类(主任医师、副主任医师、主治医师、医师、其他)。然后,我们根据前端传递的多个分类标签,来过滤数据。 2. 但是,由于职称分类规则比较复杂,且每个分类规则需要匹配多个模式(比如主任医师需要匹配含“主任”但不含“副”),我们可以先构建一个虚拟的分类列,然后在这个分类列上进行多选。 3. 由于MyBatis的动态SQL支持,我们可以将前端的多个选择(如['主任医师','副主任医师'])传递到Mapper中,然后使用IN条件。 4. 但是,注意“其他”这个分类,它包含了多种情况(包括空白、null、未知、其他,以及不符合前四类规则的其他字符串)。所以我们在构建虚拟分类列时,需要将这些情况都归为“其他”。 5. 另外,由于我们可能还需要根据其他条件(如科室、姓名等)进行查询,因此需要将这些条件也动态拼接到SQL中。 具体步骤: 步骤1:在SQL中,使用CASE表达式将职称字段(假设字段名为`title`)映射为五个分类之一。映射规则如下: CASE WHEN title LIKE '%主任%' AND title NOT LIKE '%副主任%' THEN '主任医师' WHEN title LIKE '%副主任%' THEN '副主任医师' WHEN title LIKE '%主治%' OR title LIKE '%主管%' THEN '主治医师' WHEN title LIKE '%医师%' AND title NOT LIKE '%主任%' AND title NOT LIKE '%副主任%' AND title NOT LIKE '%主治%' AND title NOT LIKE '%主管%' THEN '医师' ELSE '其他' END AS title_category 但是,注意:我们还需要将数据库中存储的“不详”、“未知”、“其他”、空白和null归为“其他”。上面的ELSE已经包含了空白和null(因为空白和null在LIKE比较中都会被视为不匹配,所以会落入ELSE)。另外,如果字段值就是“不详”、“未知”、“其他”,那么它们也会落入ELSE(因为不符合前面的任何条件),所以被归为“其他”。 步骤2:在查询的WHERE条件中,我们可以根据前端传递的多个分类标签(例如:['主任医师','副主任医师'])来过滤,即要求title_category IN ( ... )。 步骤3:同时,我们还需要支持其他动态条件,比如科室、姓名等。这些条件可以通过MyBatis的动态SQL(如if、choose等)来拼接。 但是,这里有一个问题:使用CASE表达式作为条件,在WHERE子句中直接使用这个别名(title_category)是不行的,因为SQL的执行顺序是WHERE在SELECT之前。因此,我们可以采用以下两种方法之一: 方法一:将上述分类逻辑放在WHERE子句的条件表达式中重复书写(不推荐,因为重复且难以维护)。 方法二:将整个查询作为子查询,然后在外部查询中使用WHERE条件过滤。或者使用公用表表达式(CTE),但考虑到兼容性,我们可以使用子查询。 示例SQL结构: SELECT * FROM ( SELECT *, CASE WHEN title LIKE '%主任%' AND title NOT LIKE '%副主任%' THEN '主任医师' WHEN title LIKE '%副主任%' THEN '副主任医师' WHEN title LIKE '%主治%' OR title LIKE '%主管%' THEN '主治医师' WHEN title LIKE '%医师%' AND title NOT LIKE '%主任%' AND title NOT LIKE '%副主任%' AND title NOT LIKE '%主治%' AND title NOT LIKE '%主管%' THEN '医师' ELSE '其他' END AS title_category FROM your_table ) t WHERE <if test="titleCategories != null and titleCategories.size() > 0"> title_category IN <foreach collection="titleCategories" item="category" open="(" separator="," close=")"> #{category} </foreach> </if> <!-- 其他动态条件 --> <if test="deptId != null"> AND dept_id = #{deptId} </if> <if test="name != null"> AND name LIKE CONCAT('%', #{name}, '%') </if> 但是,这种方法在数据量大的情况下可能性能不佳,因为需要对全表进行扫描并计算title_category,然后才过滤。 方法三:为了避免子查询,我们可以将CASE表达式放在WHERE子句中,但需要重复这个表达式。例如: SELECT * FROM your_table WHERE <if test="titleCategories != null and titleCategories.size() > 0"> ( CASE WHEN title LIKE '%主任%' AND title NOT LIKE '%副主任%' THEN '主任医师' WHEN title LIKE '%副主任%' THEN '副主任医师' WHEN title LIKE '%主治%' OR title LIKE '%主管%' THEN '主治医师' WHEN title LIKE '%医师%' AND title NOT LIKE '%主任%' AND title NOT LIKE '%副主任%' AND title NOT LIKE '%主治%' AND title NOT LIKE '%主管%' THEN '医师' ELSE '其他' END ) IN <foreach collection="titleCategories" item="category" open="(" separator="," close=")"> #{category} </foreach> </if> <!-- 其他动态条件 --> 这种方法避免了子查询,但是CASE表达式较长,且重复书写(如果多个地方需要用到则重复)。另外,注意在WHERE子句中使用CASE表达式可能会影响索引使用(但在这个场景中,由于使用了LIKE,索引可能本来就用不上)。 考虑到可读性和维护性,我建议使用方法二(子查询)或者使用视图(如果职称分类规则固定,可以创建一个视图)。但在MyBatis中,我们使用子查询方式。 另外,我们还需要注意:如果用户选择了“全部”,那么就不需要过滤职称分类。因此,我们可以这样处理: <if test="titleCategories != null and titleCategories.size() > 0"> <!-- 如果titleCategories中包含“全部”,则跳过这个条件 --> <if test="!titleCategories.contains('全部')"> ... 职称分类过滤 ... </if> </if> 或者,前端在传递“全部”时,可以选择不传递任何职称分类(即不包含这个条件),这样更简单。因此,我们可以在前端处理:当用户选择“全部”时,不传递titleCategories参数;当用户选择其他组合时,传递选中的分类(不包含“全部”)。 步骤4:在MyBatis的Mapper接口中,我们需要定义相应的方法,参数包含职称分类列表(titleCategories)和其他动态条件。 示例Mapper接口: List<Doctor> queryDoctors( @Param("titleCategories") List<String> titleCategories, @Param("deptId") Long deptId, @Param("name") String name ); 步骤5:编完整的动态SQL。 考虑到性能,如果表数据量很大,我们可以考虑在数据库表中增加一个冗余字段(title_category)并建立索引,然后通过定时任务或触发器维护这个字段。但是,这属于数据库设计优化,不在当前需求范围内。 下面是一个完整的MyBatis动态SQL示例(使用子查询方式): ```xml <select id="queryDoctors" resultType="com.example.Doctor"> SELECT * FROM ( SELECT *, CASE WHEN title LIKE '%主任%' AND title NOT LIKE '%副主任%' THEN '主任医师' WHEN title LIKE '%副主任%' THEN '副主任医师' WHEN title LIKE '%主治%' OR title LIKE '%主管%' THEN '主治医师' WHEN title LIKE '%医师%' AND title NOT LIKE '%主任%' AND title NOT LIKE '%副主任%' AND title NOT LIKE '%主治%' AND title NOT LIKE '%主管%' THEN '医师' ELSE '其他' END AS title_category FROM doctor ) t <where> <if test="titleCategories != null and titleCategories.size() > 0"> t.title_category IN <foreach collection="titleCategories" item="category" open="(" separator="," close=")"> #{category} </foreach> </if> <if test="deptId != null"> AND t.dept_id = #{deptId} </if> <if test="name != null and name != ''"> AND t.doctor_name LIKE CONCAT('%', #{name}, '%') </if> </where> </select> ``` 注意:如果数据库是Oracle,LIKE的拼接要用'%'||#{name}||'%';如果是SQL Server,则用'%'+#{name}+'%'。这里使用了MySQL的CONCAT函数。 另外,如果用户没有选择任何职称分类(即titleCategories为空),那么就不会添加职称分类的条件,即查询所有职称分类。 但是,这里有一个问题:如果用户选择了“全部”,那么前端应该不传递titleCategories,或者传递一个空列表。这样,这个条件就不会生效,即查询所有职称分类。 因此,前端在用户点击“全部”时,可以不传递titleCategories,或者传递空数组。如果用户选择了多个标签(包括“全部”),那么前端应该只传递非“全部”的标签(因为选择了“全部”就相当于不按职称分类过滤)。 所以,我们可以在前端这样处理: - 当用户选择“全部”时,不传递titleCategories参数(或者传递空数组)。 - 当用户选择其他组合(比如主任医师和副主任医师)时,传递['主任医师','副主任医师']。 这样,我们的SQL就可以正确执行。 但是,如果用户同时选择了“全部”和其他标签,那么我们应该忽略其他标签,只按“全部”处理(即不按职称分类过滤)。这个逻辑最好在前端处理,避免传递这样的参数。 另外,关于空白和null的处理:在数据库中,如果title字段是null,那么CASE表达式的结果是ELSE(即'其他')。同样,如果title是空字符串,也会被归为'其他'。这符合需求。 最后,我们还需要考虑性能。由于这个查询使用了子查询和LIKE,可能无法使用索引。如果数据量很大,可以考虑使用全文索引或者将分类规则固化到数据库字段中(如增加一个分类字段,通过触发器或应用层维护)。 以上方案实现了多选职称查询,并支持其他动态条件。 如果还有其他动态条件,可以按照类似的方式添加。 下面,我们根据引用[1][2][3][4][5]中的内容,总结动态SQL的使用: - 使用<where>标签可以避免WHERE关键字后面直接跟AND/OR的问题。 - 使用<foreach>遍历集合。 - 使用<if>进行条件判断。 因此,我们的实现符合MyBatis动态SQL的最佳实践。 如果存在其他动态条件,可以继续在<where>标签内添加。 注意:在子查询中,我们使用了CASE表达式,这个表达式较长,但逻辑清晰。 如果数据库支持,我们也可以使用视图(View)来预先定义这个分类字段,然后在查询中直接使用视图。这样就不需要每次子查询了。但是,这需要数据库权限。 综上所述,我们提供了在MyBatis中实现多选职称查询并支持其他动态条件的方案。</think>在 MyBatis 中实现多选职称查询并支持其他动态条件,可以通过以下步骤实现: ### 解决方案 #### 1. 参数对象设计 ```java public class DoctorQueryParam { private List<String> titleCategories; // 职称分类列表 private String name; // 姓名条件 private Long deptId; // 科室ID private String expertise; // 擅长领域 // 其他查询条件... // Getter & Setter } ``` #### 2. MyBatis 动态 SQL 实现 ```xml <select id="selectDoctorsByConditions" resultType="Doctor"> SELECT * FROM doctor <where> <!-- 职称多选条件 --> <if test="titleCategories != null and titleCategories.size() > 0"> AND ( <trim prefixOverrides="OR"> <foreach collection="titleCategories" item="category"> <choose> <when test="category == '主任医师'"> OR (title LIKE '%主任%' AND title NOT LIKE '%副主任%') </when> <when test="category == '副主任医师'"> OR title LIKE '%副主任%' </when> <when test="category == '主治医师'"> OR (title LIKE '%主治%' OR title LIKE '%主管%') </when> <when test="category == '医师'"> OR (title LIKE '%医师%' AND title NOT LIKE '%主任%' AND title NOT LIKE '%副主任%' AND title NOT LIKE '%主治%' AND title NOT LIKE '%主管%') </when> <when test="category == '其他'"> OR ( title IS NULL OR title = '' OR title = '不详' OR title = '未知' OR title = '其他' OR ( title NOT LIKE '%主任%' AND title NOT LIKE '%副主任%' AND title NOT LIKE '%主治%' AND title NOT LIKE '%主管%' AND title NOT LIKE '%医师%' ) ) </when> </choose> </foreach> </trim> ) </if> <!-- 其他动态条件 --> <if test="name != null and name != ''"> AND name LIKE CONCAT('%', #{name}, '%') </if> <if test="deptId != null"> AND dept_id = #{deptId} </if> <if test="expertise != null and expertise != ''"> AND expertise LIKE CONCAT('%', #{expertise}, '%') </if> <!-- 可继续添加其他条件 --> </where> </select> ``` #### 3. Mapper 接口定义 ```java public interface DoctorMapper { List<Doctor> selectDoctorsByConditions(@Param("param") DoctorQueryParam param); } ``` ### 关键实现说明 1. **职称分类处理**: - 使用 `<choose>` + `<when>` 实现职称分类的逻辑判断 - 通过 `LIKE` 和 `NOT LIKE` 组合实现复杂匹配规则 - "其他"类别包含特殊值(NULL、空值、不详等)和不符合其他分类的值 2. **多选查询**: - 使用 `<foreach>` 遍历前端传递的职称分类列表 - `<trim prefixOverrides="OR">` 处理多个 `OR` 条件的前缀问题 3. **动态条件扩展**: - 使用 `<if>` 标签实现其他条件的动态拼接 - 支持姓名模糊查询、科室精确匹配等常见需求 - 可自由扩展其他查询条件(如擅长领域、性别等) 4. **安全性与性能**: - 使用 `#{}` 防止 SQL 注入 - 当未选择职称条件时,自动跳过职称过滤 - 建议对常用字段(如 dept_id)建立索引优化查询 ### 使用示例 前端传递参数示例(JSON): ```json { "titleCategories": ["主任医师", "副主任医师"], "deptId": 1024, "name": "张" } ``` 生成 SQL 示例: ```sql SELECT * FROM doctor WHERE ( (title LIKE '%主任%' AND title NOT LIKE '%副主任%') OR title LIKE '%副主任%' ) AND dept_id = 1024 AND name LIKE '%张%' ``` ### 优化建议 1. **数据库层面**: ```sql -- 添加职称分类冗余字段并建立索引 ALTER TABLE doctor ADD COLUMN title_category VARCHAR(20); CREATE INDEX idx_title_category ON doctor(title_category); ``` 2. **缓存机制**: ```java // 使用MyBatis二级缓存 @CacheNamespace(implementation = MybatisRedisCache.class) public interface DoctorMapper {...} ``` 3. **分页支持**: ```xml <select id="selectDoctorsByConditions" resultType="Doctor"> SELECT * FROM doctor <where>...</where> ORDER BY id DESC LIMIT #{param.offset}, #{param.limit} </select> ``` 此方案实现了: 1. 复杂的职称分类逻辑处理 2. 多选标签的动态查询 3. 与其他查询条件的灵活组合 4. 可扩展的动态 SQL 结构
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值