核心内容摘要
动漫角色“桶”出新宇宙！——不止于此的次元魅力
在数据库查询中模糊查询是最常用的功能之一。
然而当查询条件变得复杂多变时静态SQL往往显得力不从心。
今天我们来探讨如何通过动态SQL实现灵活、安全的模糊查询。
为什么需要动态SQL模糊查询
1 传统模糊查询的局限性-- 静态SQL示例 SELECT * FROM users WHERE username LIKE %张% AND email LIKE %example.com%;这种写法的问题在于当某个条件为空时查询会失效条件组合多变时需要写大量重复代码难以应对复杂的业务逻辑
2 动态SQL的优势灵活性根据实际参数动态生成SQL可维护性代码更简洁易于维护性能优化避免不必要的查询条件
MyBatis动态SQL实现模糊查询
1 基础示例单条件模糊查询!-- MyBatis Mapper XML -- select idsearchUsers resultTypeUser SELECT * FROM users where if testkeyword ! null and keyword ! AND (username LIKE CONCAT(%, #{keyword}, %) OR email LIKE CONCAT(%, #{keyword}, %) OR phone LIKE CONCAT(%, #{keyword}, %)) /if /where /select
2 多条件组合模糊查询select idadvancedSearch resultTypeUser SELECT * FROM users where !-- 姓名模糊查询 -- if testname ! null and name ! AND username LIKE CONCAT(%, #{name}, %) /if !-- 邮箱模糊查询 -- if testemail ! null and email ! AND email LIKE CONCAT(%, #{email}, %) /if !-- 电话号码模糊查询支持中间四位*号 -- if testphonePattern ! null and phonePattern ! AND phone LIKE REPLACE(#{phonePattern}, *, %) /if !-- 地址多字段模糊查询 -- if testaddress ! null and address ! AND ( province LIKE CONCAT(%, #{address}, %) OR city LIKE CONCAT(%, #{address}, %) OR detail LIKE CONCAT(%, #{address}, %) ) /if /where ORDER BY create_time DESC /select
3 使用choose实现条件选择select idsmartSearch resultTypeUser SELECT * FROM users where choose when testsearchType name and keyword ! null AND username LIKE CONCAT(%, #{keyword}, %) /when when testsearchType email and keyword ! null AND email LIKE CONCAT(%, #{keyword}, %) /when when testsearchType phone and keyword ! null AND phone LIKE CONCAT(%, #{keyword}, %) /when otherwise AND status ACTIVE /otherwise /choose /where /select
Java代码中的动态构建
1 使用StringBuilder动态构建SQL// 服务层代码示例 public ListUser dynamicSearch(UserSearchCriteria criteria) { StringBuilder sql new StringBuilder(SELECT * FROM users WHERE
; ListObject params new ArrayList(); // 姓名模糊查询 if (StringUtils.isNotBlank(criteria.getName())) { sql.append( AND username LIKE ?); params.add(% criteria.getName() %); } // 邮箱模糊查询 if (StringUtils.isNotBlank(criteria.getEmail())) { sql.append( AND email LIKE ?); params.add(% criteria.getEmail() %); } // 分页处理 if (criteria.getPageSize()
{ sql.append( LIMIT ?, ?); params.add(criteria.getOffset()); params.add(criteria.getPageSize()); } return jdbcTemplate.query(sql.toString(), params.toArray(), new BeanPropertyRowMapper(User.class)); }
2 使用JPA Specification实现Spring Data JPA// 使用Specification构建动态查询 public class UserSpecifications { public static SpecificationUser nameContains(String name) { return (root, query, cb) - StringUtils.isBlank(name) ? cb.conjunction() : cb.like(root.get(username), % name %); } public static SpecificationUser emailContains(String email) { return (root, query, cb) - StringUtils.isBlank(email) ? cb.conjunction() : cb.like(root.get(email), % email %); } public static SpecificationUser multiFieldSearch(String keyword) { return (root, query, cb) - { if (StringUtils.isBlank(keyword)) { return cb.conjunction(); } String pattern % keyword %; return cb.or( cb.like(root.get(username), pattern), cb.like(root.get(email), pattern), cb.like(root.get(phone), pattern) ); }; } } // 使用示例 public ListUser searchUsers(String name, String email) { return userRepository.findAll( Specification.where(UserSpecifications.nameContains(name)) .and(UserSpecifications.emailContains(email)) ); }
高级技巧与优化
1 防止SQL注入// 使用预编译语句永远不要直接拼接用户输入 String safePattern % escapeSql(keyword) %; // MyBatis自动处理参数防止SQL注入 if testkeyword ! null AND username LIKE CONCAT(%, #{keyword}, %) /if
2 性能优化建议-- 为经常查询的字段创建索引 CREATE INDEX idx_username ON users(username); CREATE INDEX idx_email ON users(email); -- 避免前导通配符导致索引失效的情况 -- 不推荐LIKE %keyword% -- 推荐LIKE keyword%如果业务允许
3 使用全文索引提升模糊查询性能-- MySQL全文索引示例 ALTER TABLE users ADD FULLTEXT INDEX ft_search (username, email); -- 使用全文索引进行模糊查询 SELECT * FROM users WHERE MATCH(username, email) AGAINST(张* example* IN BOOLEAN MODE);
实际应用场景
1 电商商品搜索select idsearchProducts resultTypeProduct SELECT * FROM products where if testproductName ! null AND product_name LIKE CONCAT(%, #{productName}, %) /if if testcategoryId ! null AND category_id #{categoryId} /if if testminPrice ! null AND price #{minPrice} /if if testmaxPrice ! null AND price #{maxPrice} /if !-- 模糊搜索商品描述 -- if testkeyword ! null AND ( product_name LIKE CONCAT(%, #{keyword}, %) OR description LIKE CONCAT(%, #{keyword}, %) OR tags LIKE CONCAT(%, #{keyword}, %) ) /if /where ORDER BY choose when testsortBy priceprice ${sortOrder}/when when testsortBy salessales_count DESC/when otherwisecreate_time DESC/otherwise /choose /select
2 日志查询系统public ListLog searchLogs(LogQuery query) { StringBuilder sql new StringBuilder( SELECT * FROM system_logs WHERE
; // 模糊匹配操作内容 if (StringUtils.isNotBlank(query.getContent())) { sql.append( AND content LIKE ?); params.add(% query.getContent() %); } // 模糊匹配用户IP if (StringUtils.isNotBlank(query.getIp())) { sql.append( AND ip_address LIKE ?); params.add(query.getIp() %); // IP前缀匹配 } // 时间范围查询 if (query.getStartTime() ! null) { sql.append( AND create_time ?); params.add(query.getStartTime()); } return jdbcTemplate.query(sql.toString(), params.toArray(), new BeanPropertyRowMapper(Log.class)); }
最佳实践
总结安全性第一始终使用参数化查询防止SQL注入性能优化为频繁查询的字段建立索引考虑使用全文搜索代码可读性保持SQL语句的清晰和可维护性适度使用避免过度复杂的动态SQL必要时拆分查询测试覆盖确保各种条件组合都能正确工作结语动态SQL模糊查询是现代应用开发中不可或缺的技能。
通过合理运用MyBatis动态标签、JPA Specification或自定义SQL构建我们可以在保证安全性的同时实现灵活高效的查询功能。
记住好的查询设计不仅能让程序跑得更快也能让代码更易于维护和扩展。