Java MySQL dynamic statement writing implementation method

In Java backend development, the construction of dynamic SQL statements is a very common requirement, especially when dealing with complex query conditions. Here are some commonly used implementation methods:

1. Dynamically splice SQL using JDBC

This is the most basic method, but it can easily lead to SQL injection risks and is not recommended for use in production environments.

public List&lt;User&gt; findUsers(String name, Integer age, String email) {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    List&lt;User&gt; users = new ArrayList&lt;&gt;();
    try {
        conn = ();
        // Basic SQL        StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");
        // Dynamically add conditions        List&lt;Object&gt; params = new ArrayList&lt;&gt;();
        if (name != null) {
            (" AND name = ?");
            (name);
        }
        if (age != null) {
            (" AND age = ?");
            (age);
        }
        if (email != null) {
            (" AND email LIKE ?");
            ("%" + email + "%");
        }
        stmt = (());
        // Set parameters        for (int i = 0; i &lt; (); i++) {
            (i + 1, (i));
        }
        rs = ();
        while (()) {
            User user = new User();
            (("id"));
            (("name"));
            (("age"));
            (("email"));
            (user);
        }
    } catch (SQLException e) {
        ();
    } finally {
        // Close the resource    }
    return users;
}

2. Using the JPA Criteria API

JPA provides a type-safe dynamic query construction method:

public List<User> findUsers(String name, Integer age, String email) {
    CriteriaBuilder cb = ();
    CriteriaQuery<User> query = ();
    Root<User> root = ();
    List<Predicate> predicates = new ArrayList<>();
    if (name != null) {
        ((("name"), name));
    }
    if (age != null) {
        ((("age"), age));
    }
    if (email != null) {
        ((("email"), "%" + email + "%"));
    }
    ((new Predicate[0]));
    return (query).getResultList();
}

3. Specification using Spring Data JPA

Spring Data JPA provides a more concise way of Specification:

public List<User> findUsers(String name, Integer age, String email) {
    return ((root, query, cb) -> {
        List<Predicate> predicates = new ArrayList<>();
        if (name != null) {
            ((("name"), name));
        }
        if (age != null) {
            ((("age"), age));
        }
        if (email != null) {
            ((("email"), "%" + email + "%"));
        }
        return ((new Predicate[0]));
    });
}

4. Dynamic SQL using MyBatis

MyBatis provides powerful dynamic SQL features:

Mapper interface

public interface UserMapper {
    List<User> findUsers(@Param("name") String name, 
                        @Param("age") Integer age, 
                        @Param("email") String email);
}

XML map file

<select  resultType="User">
    SELECT * FROM users
    <where>
        <if test="name != null">
            AND name = #{name}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="email != null">
            AND email LIKE CONCAT('%', #{email}, '%')
        </if>
    </where>
</select>

Dynamic SQL Tag Description

<if>: Conditional judgment
<where>: Automatically handle WHERE keywords and AND/OR prefixes
<choose>, <when>, <otherwise>: Similar to switch-case
<foreach>: traversal of the collection
<set>: Automatically process SET keywords and commas when updated

5. Use MyBatis-Plus conditional constructor

MyBatis-Plus provides a more convenient way to construct query conditions:

public List<User> findUsers(String name, Integer age, String email) {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    if (name != null) {
        ("name", name);
    }
    if (age != null) {
        ("age", age);
    }
    if (email != null) {
        ("email", email);
    }
    return (queryWrapper);
}

6. Dynamic SQL using JOOQ

JOOQ provides type-safe DSL:

public List<User> findUsers(String name, Integer age, String email) {
    return (USERS)
        .where(name == null ? noCondition() : (name))
        .and(age == null ? noCondition() : (age))
        .and(email == null ? noCondition() : ("%" + email + "%"))
        .fetchInto();
}

Best Practice Recommendations

Security: Always use parameterized queries to prevent SQL injection
readability: Keep SQL statements clear and easy to read
performance: Consider index usage and avoid full table scanning
Maintenance: Complex queries consider using annotations or XML separation
Tool selection:
- Simple Project: Spring Data JPA Specification
- Medium Complexity: MyBatis Dynamic SQL
- Highly flexible requirements: JOOQ

This is the article about the implementation of Java MySQL dynamic statement writing and implementation. For more related contents of Java MySQL dynamic statements, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!