Dynamic SQL

Note

Dynamic SQL is a powerful query technique that generates SQL statements at runtime according to changing conditions. It is particularly useful for conditional search, batch operations, and flexible data access. When used well, it improves reuse and maintainability while reducing redundant SQL.

Juice provides rich support for dynamic SQL through XML configuration. The main elements are described below.

Conditional Logic with if

The if element includes or skips a SQL fragment according to the result of an expression:

<select id="SelectByCondition">
    select * from user
    <if test='name != ""'>
        where name = #{name}
    </if>
</select>

Tip

Conditional expressions support common operators and functions such as &&, ||, !, >, <, ==, and !=.

Dynamic WHERE with where

The where element manages the WHERE clause automatically. It adds WHERE only when needed and strips leading AND or 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 automatically handles the following cases:

• no WHERE keyword is emitted when no condition matches

• leading AND and OR are removed

• the generated SQL remains syntactically valid

Dynamic SET with set

The set element is designed for UPDATE statements. It manages the SET clause and removes trailing commas:

<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 automatically:

• adds the SET keyword

• removes the final extra comma

• keeps the generated SQL valid

Iterating Collections with foreach

The foreach element iterates over a collection. It is commonly used for IN conditions and batch operations:

<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 supports these attributes:

• collection: the collection to iterate

• item: the current element

• index: the current index

• open: opening text

• close: closing text

• separator: separator between items

Custom Wrapping with trim

The trim element lets you define custom prefix and suffix handling:

<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 attributes:

• prefix: text to prepend

• prefixOverrides: prefixes to remove

• suffix: text to append

• suffixOverrides: suffixes to remove

Conditional Branching with choose

The choose element works like a switch statement:

<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>

Reusing SQL Fragments with sql and include

The sql and include elements define reusable SQL fragments:

<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>

Cross-Namespace References

You can reference SQL fragments from another namespace:

<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

The id attribute on sql is required and must be a valid identifier.

Parameterized SQL Fragments

sql fragments can also be parameterized with 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>

In this example, field and value are defined with bind and then used inside the SQL fragment.

Parameter Scope and Precedence

Scope rules:

• bind can be declared at the top level of a statement or nested inside dynamic tags such as if, where, and foreach.

• Nested bind variables have local scope and are visible only within the current tag and its children.

• Variables defined in a parent scope are visible to child tags.

• Variables defined in an inner scope override variables with the same name from an outer scope.

Parameter lookup precedence:

1. Parameters defined by bind

2. User-provided parameters

3. Built-in system parameters such as _databaseId and _parameter

That means a bind variable can override a user-provided parameter with the same name.

Advanced Examples

1. String processing

<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>

2. Numeric calculation

<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>

3. Complex object handling

<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>

4. Scope override example

<select id="ScopedBindExample">
    <bind name="pattern" value='"%"'/>
    SELECT * FROM users
    <where>
        <if test='name != ""'>
            <bind name="pattern" value='"%" + name + "%"'/>
            AND name LIKE #{pattern}
        </if>
    </where>
    OR description LIKE #{pattern}
</select>

Tip

Dynamic SQL best practices:

1. Reuse SQL fragments where it improves maintainability.

2. Be aware of the runtime cost of complex conditions.

3. Prefer parameterized queries to avoid SQL injection.

4. Keep SQL readable.

5. Add comments for complex dynamic SQL logic when needed.

Real-World Query Patterns

Multi-Condition Search

E-commerce Product Search Example

<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 &lt;= #{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>

User Permission Query Example

<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>

Complex Statistical Queries

Sales Statistics Example

<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 &lt; #{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>

Dynamic Batch Operations

Batch Update Example

<update id="BatchUpdateProductPrices">
    <foreach collection="products" item="product" separator=";">
        UPDATE products
        SET
            price = #{product.price},
            updated_at = NOW()
        WHERE id = #{product.id}
    </foreach>
</update>

Conditional Batch Delete

<delete id="BatchDeleteInactiveUsers">
    DELETE FROM users
    <where>
        <if test='status != nil'>
            AND status = #{status}
        </if>

        <if test='createdBefore != ""'>
            AND created_at &lt; #{createdBefore}
        </if>

        <if test='lastLoginBefore != ""'>
            AND last_login_at &lt; #{lastLoginBefore}
        </if>

        <if test='excludeIds != nil and len(excludeIds) > 0'>
            AND id NOT IN
            <foreach collection="excludeIds" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
    </where>
    LIMIT 1000
</delete>

Performance Optimization Tips

Avoid Common Performance Pitfalls

1. Avoid unnecessary conditions

Not recommended:

<select id="GetUsers">
    SELECT * FROM users
    <if test='true'>
        WHERE status = 1
    </if>
</select>

Recommended:

<select id="GetUsers">
    SELECT * FROM users
    WHERE status = 1
</select>

2. Use foreach carefully

Not recommended when the IN list is too large:

<select id="GetUsersByIds">
    SELECT * FROM users
    WHERE id IN
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

Recommended: split into batches or use a temporary table.

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. Avoid N+1 queries

Not recommended:

orders, _ := GetOrders(ctx)
for _, order := range orders {
    items, _ := GetOrderItems(ctx, order.ID)
    order.Items = items
}

Recommended: use JOIN or batched IN queries.

<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>

Index Optimization Suggestions

1. Ensure WHERE conditions use indexes

<select id="SearchUsers">
    SELECT * FROM users
    <where>
        <if test='email != ""'>
            AND email = #{email}
        </if>

        <if test='status > 0'>
            AND status = #{status}
        </if>
        <if test='createdAfter != ""'>
            AND created_at > #{createdAfter}
        </if>
    </where>
</select>

2. Avoid functions on indexed columns

Not recommended:

<select id="GetUsersByDate">
    SELECT * FROM users
    WHERE DATE(created_at) = #{date}
</select>

Recommended:

<select id="GetUsersByDate">
    SELECT * FROM users
    WHERE created_at >= #{date}
    AND created_at &lt; DATE_ADD(#{date}, INTERVAL 1 DAY)
</select>

Query Optimization

1. Select only the fields you need

Not recommended:

<select id="GetUserNames">
    SELECT * FROM users
</select>

Recommended:

<select id="GetUserNames">
    SELECT id, name FROM users
</select>

2. Use LIMIT to bound result sets

<select id="GetRecentOrders">
    SELECT * FROM orders
    ORDER BY created_at DESC
    LIMIT 100
</select>

3. Use subqueries carefully

Not recommended:

<select id="GetUsersWithOrderCount">
    SELECT
        u.*,
        (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
    FROM users u
</select>

Recommended:

<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>

Batch Operation Optimization

1. Control batch size with 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. Optimize bulk updates

Use CASE WHEN for batch updates:

<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>

Debugging and Monitoring

SQL Debugging Tips

1. Enable debug mode

Globally:

<settings>
    <setting name="debug" value="true"/>
</settings>

For a single statement:

<select id="GetUsers" debug="true">
    SELECT * FROM users
</select>

2. Add a custom logging middleware

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()

        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
    }
}

Performance Analysis

1. Monitor slow queries

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. Collect query statistics

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])
    }
}

Best Practices Summary

Dynamic SQL Design Principles

1. Keep it simple

   • Avoid deeply nested conditions where possible.

   • Split very complex logic into multiple statements.

   • Prefer straightforward condition checks.

2. Prioritize performance

   • Make sure generated SQL can use indexes.

   • Avoid full table scans.

   • Use LIMIT where appropriate.

3. Keep it maintainable

   • Add clear comments when needed.

   • Use meaningful variable names.

   • Keep formatting consistent.

4. Keep it safe

   • Always prefer parameter binding such as #{param}.

   • Avoid string replacement such as ${param} unless absolutely necessary.

   • Validate user input.

Checklist

Before writing dynamic SQL, confirm the following:

[ ] Do you really need dynamic SQL here?
[ ] Are the conditions reasonable?
[ ] Could this introduce an N+1 query pattern?
[ ] Are the relevant indexes available?
[ ] Is the result set size bounded?
[ ] Are parameters safely bound?
[ ] Have you added comments where the logic is complex?
[ ] Have you performance-tested the query?

Tip

Recommended tools:

• use EXPLAIN to analyze the query plan

• use the Juice IDEA plugin to check SQL syntax

• use slow-query logs to monitor performance

• review and optimize SQL regularly

Warning

Common mistakes:

• executing queries inside loops

• putting too many values into an IN clause

• applying functions to indexed columns

• ignoring NULL handling

• forgetting to use LIMIT where appropriate