动态SQL
Note
动态SQL是一种强大的数据库查询技术，它允许在运行时根据不同条件动态生成SQL语句。这种技术特别适用于复杂的查询场景，如条件搜索、批量操作和灵活的数据操作。动态SQL不仅提高了代码的复用性和可维护性，还能优化查询性能，减少冗余代码。
Juice提供了丰富的动态SQL支持，通过XML配置方式实现灵活的SQL构建。以下是支持的主要动态SQL元素：
条件判断（if）
if 元素用于条件判断，根据表达式结果决定是否包含特定的SQL片段：
<select id="SelectByCondition">
select * from user
<if test='name != ""'>
where name = #{name}
</if>
</select>
Tip
条件表达式支持各种运算符和函数，如：&&、||、!、>, <、==、!= 等。
动态WHERE（where）
where 元素智能处理WHERE子句，自动添加WHERE关键字并去除多余的AND/OR：
<select id="SelectByCondition">
select * from user
<where>
<if test='name != ""'>
and name = #{name}
</if>
<if test='age > 0'>
and age > #{age}
</if>
</where>
</select>
Note
where 元素会自动处理以下情况：
如果where中的条件都不满足，则不会添加where关键字
自动去除开头的AND/OR
确保生成的SQL语法正确
动态SET（set）
set 元素用于UPDATE语句，自动处理SET子句并去除多余的逗号：
<update id="UpdateUser">
update user
<set>
<if test='name != ""'>
name = #{name},
</if>
<if test='age > 0'>
age = #{age},
</if>
<if test='email != null'>
email = #{email},
</if>
</set>
where id = #{id}
</update>
Tip
set 元素会自动：
- 添加SET关键字
- 去除最后多余的逗号
- 如果没有要更新的字段，会生成有效的SQL
集合遍历（foreach）
foreach 元素用于遍历集合，常用于IN条件和批量操作：
<select id="SelectByIds">
select * from user where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
<!-- 批量插入示例 -->
<insert id="BatchInsert">
insert into user (name, age) values
<foreach collection="users" item="user" separator=",">
(#{user.name}, #{user.age})
</foreach>
</insert>
Note
foreach支持的属性：
collection: 要遍历的集合item: 当前遍历的元素index: 当前遍历的索引open: 开始字符close: 结束字符separator: 分隔符
条件修饰（trim）
trim 元素用于自定义SQL语句的修饰规则：
<select id="SelectWithTrim">
select * from user
<trim prefix="where" prefixOverrides="and |or ">
<if test='name != null'>
and name like #{name}
</if>
<if test='age > 0'>
or age > #{age}
</if>
</trim>
</select>
Tip
trim的属性说明：
prefix: 要添加的前缀prefixOverrides: 要去除的前缀suffix: 要添加的后缀suffixOverrides: 要去除的后缀
多重条件选择（choose）
choose 元素提供类似switch的条件选择功能：
<select id="SelectByChoice">
select * from user
<where>
<choose>
<when test='searchType == "name"'>
and name like concat('%', #{keyword}, '%')
</when>
<when test='searchType == "email"'>
and email = #{keyword}
</when>
<otherwise>
and id = #{keyword}
</otherwise>
</choose>
</where>
</select>
SQL片段复用（sql/include）
sql 和 include 元素用于SQL片段的定义和复用：
<mapper namespace="user">
<sql id="userColumns">
id, name, age, email, create_time
</sql>
<select id="GetUsers">
select
<include refid="userColumns"/>
from user
where status = 1
</select>
</mapper>
跨namespace 引用
在多个namespace之间，可以使用 sql/include 元素来引用另一个namespace中的SQL片段。
<mapper namespace="user">
<sql id="userColumns">
id, name, age, email, create_time
</sql>
</mapper>
<mapper namespace="admin">
<select id="GetUsers">
select
<include refid="user.userColumns"/>
from user
where status = 1
</select>
</mapper>
Tip
sql 的 id 属性是必须的，但是必须是合法的变量名。
sql 片段参数化
sql 元素支持参数化，可以通过 <bind> 元素传递参数：
<mapper namespace="user">
<sql id="selectByField">
select * from user where ${field} = #{value}
</sql>
<select id="GetUsersByDynamicField">
<bind name="field" value='name'/> <!-- 动态字段名 -->
<bind name="value" value='"eatmoreapple"'/> <!-- 静态值 -->
<include refid="selectByField"/>
</select>
</mapper>
在上面的例子中，我们通过 <bind> 元素定义了两个参数 field 和 value，并在 sql 片段中使用它们来动态生成 SQL 语句。
参数作用域和优先级
作用域限制 - bind 标签不仅可以在语句的顶层定义，还可以嵌套在动态 SQL 标签（如 <if>、<where>、<foreach> 等）内部使用 - 嵌套的 bind 标签具有局部作用域，只在当前标签及其子标签内有效 - 父标签定义的 bind 变量对子标签可见，子标签可直接使用 - 内部作用域定义的变量会覆盖外部作用域的同名变量
参数查找优先级 参数查找时遵循以下优先级顺序：
bind 定义的参数 - 优先级最高
传递的参数 - 用户传入的参数
系统内置参数 - 如 _databaseId、_parameter 等
这意味着 bind 定义的参数可以覆盖用户传递的同名参数。
高级用法示例
字符串处理
<select id="searchUsers">
<bind name="searchPattern" value='"%" + name + "%"'/>
<bind name="upperName" value='name.toUpperCase()'/>
SELECT * FROM users WHERE name LIKE #{searchPattern} OR UPPER(name) = #{upperName}
</select>
数值计算
<select id="getPageUsers">
<bind name="offset" value='pageNum * pageSize'/>
<bind name="limit" value='pageSize'/>
SELECT * FROM users ORDER BY id LIMIT #{limit} OFFSET #{offset}
</select>
复杂对象处理
<select id="complexSearch">
<bind name="userAge" value='user.age'/>
<bind name="userName" value='user.name'/>
<bind name="isActive" value='user.active'/>
SELECT * FROM users WHERE age >= #{userAge} AND name = #{userName} AND active = #{isActive}
</select>
作用域与覆盖示例
<select id="scopedBindExample">
<bind name="pattern" value='"%"'/>
SELECT * FROM users
<where>
<if test='name != ""'>
<!-- 覆盖外部的 pattern 变量 -->
<bind name="pattern" value='"%" + name + "%"'/>
AND name LIKE #{pattern}
</if>
</where>
<!-- 这里的 pattern 仍然是 "%" -->
OR description LIKE #{pattern}
</select>
Tip
动态SQL的最佳实践：
合理使用SQL片段复用，提高维护性
注意条件判断的性能影响
使用参数化查询防止SQL注入
保持SQL语句的可读性
适当添加注释说明复杂的动态SQL逻辑
复杂查询实战
多条件动态搜索
电商商品搜索示例
<select id="SearchProducts">
SELECT
p.id, p.name, p.price, p.stock,
c.name as category_name,
b.name as brand_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id
<where>
<!-- 商品状态过滤 -->
<if test='status != nil'>
AND p.status = #{status}
</if>
<!-- 关键词搜索：商品名称或描述 -->
<if test='keyword != ""'>
AND (
p.name LIKE concat('%', #{keyword}, '%')
OR p.description LIKE concat('%', #{keyword}, '%')
)
</if>
<!-- 价格区间过滤 -->
<if test='minPrice > 0'>
AND p.price >= #{minPrice}
</if>
<if test='maxPrice > 0'>
AND p.price <= #{maxPrice}
</if>
<!-- 分类过滤：支持多个分类 -->
<if test='categoryIds != nil and len(categoryIds) > 0'>
AND p.category_id IN
<foreach collection="categoryIds" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
<!-- 品牌过滤 -->
<if test='brandIds != nil and len(brandIds) > 0'>
AND p.brand_id IN
<foreach collection="brandIds" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
<!-- 库存过滤 -->
<if test='inStock == true'>
AND p.stock > 0
</if>
<!-- 标签过滤 -->
<if test='tags != nil and len(tags) > 0'>
AND EXISTS (
SELECT 1 FROM product_tags pt
WHERE pt.product_id = p.id
AND pt.tag_id IN
<foreach collection="tags" item="tag" open="(" close=")" separator=",">
#{tag}
</foreach>
)
</if>
</where>
<!-- 动态排序 -->
ORDER BY
<choose>
<when test='sortBy == "price_asc"'>
p.price ASC
</when>
<when test='sortBy == "price_desc"'>
p.price DESC
</when>
<when test='sortBy == "sales"'>
p.sales_count DESC
</when>
<when test='sortBy == "newest"'>
p.created_at DESC
</when>
<otherwise>
p.id DESC
</otherwise>
</choose>
<!-- 分页 -->
<if test='limit > 0'>
LIMIT #{limit} OFFSET #{offset}
</if>
</select>
用户权限查询示例
<select id="GetUserPermissions">
SELECT DISTINCT
p.id, p.code, p.name, p.resource_type
FROM permissions p
<where>
<!-- 通过用户角色获取权限 -->
<if test='userId > 0'>
AND EXISTS (
SELECT 1 FROM user_roles ur
INNER JOIN role_permissions rp ON ur.role_id = rp.role_id
WHERE ur.user_id = #{userId}
AND rp.permission_id = p.id
AND ur.status = 1
AND rp.status = 1
)
</if>
<!-- 权限类型过滤 -->
<if test='resourceTypes != nil and len(resourceTypes) > 0'>
AND p.resource_type IN
<foreach collection="resourceTypes" item="type" open="(" close=")" separator=",">
#{type}
</foreach>
</if>
<!-- 只查询启用的权限 -->
AND p.status = 1
</where>
ORDER BY p.sort_order ASC
</select>
复杂统计查询
销售数据统计
<select id="GetSalesStatistics">
SELECT
<choose>
<!-- 按日统计 -->
<when test='groupBy == "day"'>
DATE(o.created_at) as date_key,
</when>
<!-- 按月统计 -->
<when test='groupBy == "month"'>
DATE_FORMAT(o.created_at, '%Y-%m') as date_key,
</when>
<!-- 按年统计 -->
<when test='groupBy == "year"'>
YEAR(o.created_at) as date_key,
</when>
</choose>
COUNT(DISTINCT o.id) as order_count,
COUNT(DISTINCT o.user_id) as customer_count,
SUM(o.total_amount) as total_sales,
AVG(o.total_amount) as avg_order_value,
SUM(o.item_count) as total_items
FROM orders o
<where>
<!-- 时间范围过滤 -->
<if test='startDate != ""'>
AND o.created_at >= #{startDate}
</if>
<if test='endDate != ""'>
AND o.created_at < #{endDate}
</if>
<!-- 订单状态过滤 -->
<if test='statuses != nil and len(statuses) > 0'>
AND o.status IN
<foreach collection="statuses" item="status" open="(" close=")" separator=",">
#{status}
</foreach>
</if>
<!-- 商品分类过滤 -->
<if test='categoryId > 0'>
AND EXISTS (
SELECT 1 FROM order_items oi
INNER JOIN products p ON oi.product_id = p.id
WHERE oi.order_id = o.id
AND p.category_id = #{categoryId}
)
</if>
</where>
GROUP BY date_key
ORDER BY date_key DESC
</select>
动态批量操作
批量更新示例
<update id="BatchUpdateProductPrices">
<foreach collection="products" item="product" separator=";">
UPDATE products
SET
price = #{product.price},
updated_at = NOW()
WHERE id = #{product.id}
</foreach>
</update>
条件批量删除
<delete id="BatchDeleteInactiveUsers">
DELETE FROM users
<where>
<!-- 删除指定状态的用户 -->
<if test='status != nil'>
AND status = #{status}
</if>
<!-- 删除指定时间之前创建的用户 -->
<if test='createdBefore != ""'>
AND created_at < #{createdBefore}
</if>
<!-- 删除最后登录时间早于指定时间的用户 -->
<if test='lastLoginBefore != ""'>
AND last_login_at < #{lastLoginBefore}
</if>
<!-- 排除特定用户ID -->
<if test='excludeIds != nil and len(excludeIds) > 0'>
AND id NOT IN
<foreach collection="excludeIds" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
</where>
<!-- 安全限制：最多删除1000条 -->
LIMIT 1000
</delete>
性能优化技巧
避免常见性能陷阱
1. 避免不必要的条件判断
❌ 不推荐：
<select id="GetUsers">
SELECT * FROM users
<if test='true'>
WHERE status = 1
</if>
</select>
✅ 推荐：
<select id="GetUsers">
SELECT * FROM users
WHERE status = 1
</select>
2. 合理使用 foreach
❌ 不推荐：IN 条件包含过多值
<!-- 当 ids 包含 10000 个值时性能很差 -->
<select id="GetUsersByIds">
SELECT * FROM users
WHERE id IN
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
✅ 推荐：使用临时表或分批查询
// 分批查询
func GetUsersByIds(ctx context.Context, ids []int64) ([]*User, error) {
const batchSize = 1000
var allUsers []*User
for i := 0; i < len(ids); i += batchSize {
end := i + batchSize
if end > len(ids) {
end = len(ids)
}
batch := ids[i:end]
users, err := queryBatch(ctx, batch)
if err != nil {
return nil, err
}
allUsers = append(allUsers, users...)
}
return allUsers, nil
}
3. 避免 N+1 查询问题
❌ 不推荐：
// 先查询所有订单
orders, _ := GetOrders(ctx)
// 然后为每个订单查询详情（N+1 问题）
for _, order := range orders {
items, _ := GetOrderItems(ctx, order.ID)
order.Items = items
}
✅ 推荐：使用 JOIN 或 IN 查询
<select id="GetOrdersWithItems">
SELECT
o.id as order_id,
o.total_amount,
oi.id as item_id,
oi.product_id,
oi.quantity,
oi.price
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 1
</select>
索引优化建议
1. 确保 WHERE 条件使用索引
<select id="SearchUsers">
SELECT * FROM users
<where>
<!-- 确保 email 字段有索引 -->
<if test='email != ""'>
AND email = #{email}
</if>
<!-- 确保 (status, created_at) 有复合索引 -->
<if test='status > 0'>
AND status = #{status}
</if>
<if test='createdAfter != ""'>
AND created_at > #{createdAfter}
</if>
</where>
</select>
2. 避免在索引列上使用函数
❌ 不推荐：
<select id="GetUsersByDate">
SELECT * FROM users
WHERE DATE(created_at) = #{date} <!-- 索引失效 -->
</select>
✅ 推荐：
<select id="GetUsersByDate">
SELECT * FROM users
WHERE created_at >= #{date}
AND created_at < DATE_ADD(#{date}, INTERVAL 1 DAY)
</select>
查询优化
1. 只查询需要的字段
❌ 不推荐：
<select id="GetUserNames">
SELECT * FROM users <!-- 查询所有字段 -->
</select>
✅ 推荐：
<select id="GetUserNames">
SELECT id, name FROM users <!-- 只查询需要的字段 -->
</select>
2. 使用 LIMIT 限制结果集
<select id="GetRecentOrders">
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 100 <!-- 限制返回数量 -->
</select>
3. 合理使用子查询
❌ 不推荐：相关子查询
<select id="GetUsersWithOrderCount">
SELECT
u.*,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u
</select>
✅ 推荐：JOIN 或独立子查询
<select id="GetUsersWithOrderCount">
SELECT
u.*,
COALESCE(o.order_count, 0) as order_count
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id
</select>
批量操作优化
1. 使用 batchSize 控制批次大小
<insert id="BatchInsertProducts" batchSize="500">
INSERT INTO products (name, price, stock)
VALUES
<foreach collection="products" item="p" separator=",">
(#{p.name}, #{p.price}, #{p.stock})
</foreach>
</insert>
2. 批量更新优化
使用 CASE WHEN 进行批量更新：
<update id="BatchUpdatePrices">
UPDATE products
SET price = CASE id
<foreach collection="products" item="p">
WHEN #{p.id} THEN #{p.price}
</foreach>
END
WHERE id IN
<foreach collection="products" item="p" open="(" close=")" separator=",">
#{p.id}
</foreach>
</update>
调试和监控
SQL 调试技巧
1. 启用调试模式
全局启用：
<settings>
<setting name="debug" value="true"/>
</settings>
单个语句启用：
<select id="GetUsers" debug="true">
SELECT * FROM users
</select>
2. 使用自定义日志中间件
type SQLLogger struct {
logger *log.Logger
}
func (m *SQLLogger) QueryContext(stmt juice.Statement, cfg juice.Configuration, next juice.QueryHandler) juice.QueryHandler {
return func(ctx context.Context, query string, args ...any) (sql.Rows, error) {
start := time.Now()
// 记录SQL和参数
m.logger.Printf("[SQL] %s", query)
m.logger.Printf("[ARGS] %v", args)
rows, err := next(ctx, query, args...)
duration := time.Since(start)
if err != nil {
m.logger.Printf("[ERROR] %v (took %v)", err, duration)
} else {
m.logger.Printf("[SUCCESS] took %v", duration)
}
return rows, err
}
}
性能分析
1. 慢查询监控
type SlowQueryMonitor struct {
threshold time.Duration
reporter func(query string, duration time.Duration, args []any)
}
func (m *SlowQueryMonitor) QueryContext(stmt juice.Statement, cfg juice.Configuration, next juice.QueryHandler) juice.QueryHandler {
return func(ctx context.Context, query string, args ...any) (sql.Rows, error) {
start := time.Now()
rows, err := next(ctx, query, args...)
duration := time.Since(start)
if duration > m.threshold {
m.reporter(query, duration, args)
}
return rows, err
}
}
2. 查询统计
type QueryStats struct {
mu sync.RWMutex
queryCount map[string]int64
totalTime map[string]time.Duration
avgTime map[string]time.Duration
}
func (s *QueryStats) Record(stmtID string, duration time.Duration) {
s.mu.Lock()
defer s.mu.Unlock()
s.queryCount[stmtID]++
s.totalTime[stmtID] += duration
s.avgTime[stmtID] = s.totalTime[stmtID] / time.Duration(s.queryCount[stmtID])
}
func (s *QueryStats) Report() {
s.mu.RLock()
defer s.mu.RUnlock()
for stmtID, count := range s.queryCount {
fmt.Printf("Statement: %s\n", stmtID)
fmt.Printf(" Count: %d\n", count)
fmt.Printf(" Total Time: %v\n", s.totalTime[stmtID])
fmt.Printf(" Avg Time: %v\n", s.avgTime[stmtID])
}
}
最佳实践总结
动态 SQL 设计原则
保持简单
避免过度复杂的嵌套条件
复杂逻辑考虑拆分为多个语句
优先使用简单的条件判断
性能优先
确保动态生成的 SQL 能使用索引
避免全表扫描
合理使用 LIMIT
可维护性
添加清晰的注释
使用有意义的变量名
保持一致的代码风格
安全性
始终使用参数绑定（#{param}）
避免使用字符串替换（${param}）除非必要
验证用户输入
检查清单
在编写动态 SQL 前，请确认：
☐ 是否真的需要动态 SQL？
☐ 条件判断是否合理？
☐ 是否会导致 N+1 查询？
☐ 是否使用了索引？
☐ 是否限制了结果集大小？
☐ 是否使用了参数绑定？
☐ 是否添加了注释？
☐ 是否进行了性能测试？
Tip
推荐工具：
使用 EXPLAIN 分析查询计划
使用 Juice IDEA 插件检查 SQL 语法
使用慢查询日志监控性能
定期 review 和优化 SQL
Warning
常见错误：
在循环中执行查询（N+1 问题）
IN 条件包含过多值
在索引列上使用函数
忽略 NULL 值处理
不使用 LIMIT 限制结果