Spring Boot3+Java17+MariaDB：如何用JPQL获取列表中不在表列的元素

要直接通过查询返回nameList中不存在于表T的a列的元素，由于JPQL无法直接将内存列表作为查询数据源（它仅支持操作实体对应的数据库表），因此需要借助MariaDB原生SQL来实现，具体步骤如下：

1. 静态列表的原生SQL查询 #

如果nameList是固定的，可直接构造包含列表元素的临时表，通过左连接筛选出未匹配的元素：

SELECT names.val
FROM (
    VALUES ('alice'), ('bob'), ('john'), ('tom')
) AS names(val)
LEFT JOIN T ON names.val = T.a
WHERE T.a IS NULL;

在Spring Boot中，通过Spring Data JPA的@Query注解执行原生查询：

@Repository
public interface TRepository extends JpaRepository<TEntity, Long> {

    @Query(value = """
        SELECT names.val
        FROM (
            VALUES ('alice'), ('bob'), ('john'), ('tom')
        ) AS names(val)
        LEFT JOIN T ON names.val = T.a
        WHERE T.a IS NULL
        """, nativeQuery = true)
    List<String> findNamesNotInTable();
}

2. 动态列表的原生SQL查询 #

如果nameList是动态传入的，可通过EntityManager动态构建查询语句，避免硬编码：

@Service
public class TService {

    @PersistenceContext
    private EntityManager entityManager;

    public List<String> findNamesNotInTable(List<String> nameList) {
        // 构造UNION ALL子查询生成临时列表
        StringBuilder subQueryBuilder = new StringBuilder();
        for (int i = 0; i < nameList.size(); i++) {
            if (i != 0) {
                subQueryBuilder.append(" UNION ALL ");
            }
            subQueryBuilder.append("SELECT ?").append(i + 1).append(" AS val");
        }

        String sql = String.format("""
            SELECT names.val
            FROM (
                %s
            ) AS names(val)
            LEFT JOIN T ON names.val = T.a
            WHERE T.a IS NULL
            """, subQueryBuilder);

        Query query = entityManager.createNativeQuery(sql);
        // 绑定参数
        for (int i = 0; i < nameList.size(); i++) {
            query.setParameter(i + 1, nameList.get(i));
        }

        return query.getResultList();
    }
}

说明 #

JPQL本身不支持直接将内存中的列表作为查询的数据源，它只能针对实体对应的数据库表进行操作。因此要实现"直接由查询完成逻辑"的需求，必须依赖数据库的原生SQL特性来构造临时数据集，再通过左连接筛选出未匹配的元素。

内容的提问来源于stack exchange，提问作者Kazi Ziaul Hassan