Spring Data JPA多条件AND组合查询：实体类至少一个字段必填

Hey there! Let's walk through how to build this dynamic query that meets your exact requirements. I'll use Java examples (with both ORM and native JDBC approaches) since it's widely used for such scenarios, but the core logic translates easily to other languages too.

Let's start with the entity class matching your four String fields (I've adjusted naming to follow Java camelCase conventions, which pairs nicely with common database column naming like snake_case):

public class EntityProfile {
    private String refId;     // Maps to "Ref Id"
    private String billingId; // Maps to "billing ID"
    private String customerId;// Maps to "customer ID"
    private String profileId; // Maps to "profile ID"

    // Standard getters, setters, and constructors go here
}

MyBatis makes dynamic SQL super straightforward with its built-in tags. Here's how you'd set it up:

Mapper Interface #

import org.apache.ibatis.annotations.Param;
import java.util.List;

public interface EntityProfileMapper {
    List<EntityProfile> findByDynamicConditions(@Param("profile") EntityProfile profile);
}

XML Mapping File #

The <where> tag automatically handles removing leading AND keywords, so you don't have to worry about messy SQL syntax:

<select id="findByDynamicConditions" resultType="com.yourpackage.EntityProfile">
    SELECT * FROM your_database_table
    <where>
        <if test="profile.refId != null and profile.refId != ''">
            AND ref_id = #{profile.refId}
        </if>
        <if test="profile.billingId != null and profile.billingId != ''">
            AND billing_id = #{profile.billingId}
        </if>
        <if test="profile.customerId != null and profile.customerId != ''">
            AND customer_id = #{profile.customerId}
        </if>
        <if test="profile.profileId != null and profile.profileId != ''">
            AND profile_id = #{profile.profileId}
        </if>
    </where>
</select>

You need to ensure the caller provides at least one query parameter to avoid an unfiltered full-table scan (which is bad for performance). Add this check in your service layer:

import org.apache.commons.lang3.StringUtils;
import java.util.List;

public class EntityProfileService {
    private final EntityProfileMapper mapper;

    // Constructor for dependency injection
    public EntityProfileService(EntityProfileMapper mapper) {
        this.mapper = mapper;
    }

    public List<EntityProfile> queryProfiles(EntityProfile profile) {
        // Check if at least one field is non-empty
        boolean hasValidCondition = StringUtils.isNotBlank(profile.getRefId())
                || StringUtils.isNotBlank(profile.getBillingId())
                || StringUtils.isNotBlank(profile.getCustomerId())
                || StringUtils.isNotBlank(profile.getProfileId());

        if (!hasValidCondition) {
            throw new IllegalArgumentException("At least one query condition (Ref Id, billing ID, customer ID, profile ID) must be provided.");
        }

        return mapper.findByDynamicConditions(profile);
    }
}

If you're not using an ORM, here's how to implement this with plain JDBC (still using parameterized queries to avoid SQL injection):

import org.apache.commons.lang3.StringUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EntityProfileDao {
    private Connection getConnection() throws SQLException {
        // Replace with your database connection logic
        return DriverManager.getConnection("jdbc:your_db_url", "username", "password");
    }

    public List<EntityProfile> queryWithJDBC(EntityProfile profile) throws SQLException {
        List<EntityProfile> results = new ArrayList<>();
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM your_database_table WHERE 1=1");
        List<Object> parameters = new ArrayList<>();

        // Append conditions for non-empty fields
        if (StringUtils.isNotBlank(profile.getRefId())) {
            sqlBuilder.append(" AND ref_id = ?");
            parameters.add(profile.getRefId());
        }
        if (StringUtils.isNotBlank(profile.getBillingId())) {
            sqlBuilder.append(" AND billing_id = ?");
            parameters.add(profile.getBillingId());
        }
        if (StringUtils.isNotBlank(profile.getCustomerId())) {
            sqlBuilder.append(" AND customer_id = ?");
            parameters.add(profile.getCustomerId());
        }
        if (StringUtils.isNotBlank(profile.getProfileId())) {
            sqlBuilder.append(" AND profile_id = ?");
            parameters.add(profile.getProfileId());
        }

        // Validate at least one condition exists
        if (parameters.isEmpty()) {
            throw new IllegalArgumentException("At least one query condition is required.");
        }

        // Execute query
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sqlBuilder.toString())) {

            // Set parameters
            for (int i = 0; i < parameters.size(); i++) {
                stmt.setString(i + 1, (String) parameters.get(i));
            }

            // Process results
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                EntityProfile result = new EntityProfile();
                result.setRefId(rs.getString("ref_id"));
                result.setBillingId(rs.getString("billing_id"));
                result.setCustomerId(rs.getString("customer_id"));
                result.setProfileId(rs.getString("profile_id"));
                results.add(result);
            }
        }
        return results;
    }
}

Key Notes to Remember #

• Always use parameterized queries: Never concatenate user input directly into SQL strings—this protects against SQL injection attacks.
• Validate input upfront: Catching missing conditions early prevents unnecessary database calls and full-table scans.
• Match column names: Ensure your entity field names align with your actual database column names (adjust the rs.getString() calls and XML conditions if your schema uses different naming).

内容的提问来源于stack exchange，提问作者Anshuman Tripathy