如何用布尔参数查询数据库中Null/非Null的Job分配状态数据

Got it, let's tackle this problem you're dealing with. The core issue here is that your current query tries to directly match a boolean parameter (true/false) against the assigned field—which is a foreign key (stores an ID when assigned, NULL when unassigned). That type and logic mismatch is why it's not working.

Here's how to adjust your query to map the boolean parameter correctly to the NULL/non-NULL state of the assigned field:

Option 1: JPQL Query (for JPA/Hibernate) #

If you're using standard JPQL, update your query to use conditional logic that links the boolean parameter to the field's nullability:

@Query("SELECT j FROM Job j WHERE j.location IN (:loc) AND " +
       "((:assigned = TRUE AND j.assigned IS NOT NULL) OR " +
       "(:assigned = FALSE AND j.assigned IS NULL))")
public List<Job> getJobs(@Param("loc") String[] loc, @Param("assigned") boolean assigned);

Option 2: Native SQL Query #

If you prefer using native SQL (like your original query), the logic stays the same—just adapt the syntax slightly:

@Query(value = "SELECT * FROM job j WHERE j.location IN (:loc) AND " +
               "((:assigned = TRUE AND j.assigned IS NOT NULL) OR " +
               "(:assigned = FALSE AND j.assigned IS NULL))", 
       nativeQuery = true)
public List<Job> getJobs(@Param("loc") String[] loc, @Param("assigned") boolean assigned);

What this does: #

• When the assigned parameter is true, the query filters for jobs where j.assigned is not null (i.e., already assigned to someone/something)
• When the parameter is false, it filters for jobs where j.assigned is null (unassigned)

I also adjusted the location condition to use IN since your original method uses String... (variable arguments)—this lets you pass one or more location values and match any of them. If you only ever pass a single location, you can switch back to j.location = :loc.

This solution works without needing to modify your database schema or the URL parameters you're receiving—all the mapping happens right in the query logic.

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