如何用JPQL/SQL查询参数集合中未存入数据库的文件？

SQL 解决方案 #

根据不同数据库语法，可通过构造SFTP文件集合的临时表，左连接已处理文件表筛选出未处理文件：

MySQL 8.0+ #

利用VALUES语法构造临时文件列表：

SELECT file_name
FROM (VALUES ('file2'), ('file4')) AS sftp_files(file_name)
LEFT JOIN return_files rf ON sftp_files.file_name = rf.file_name
WHERE rf.file_name IS NULL;

动态传入Set<String>时，需通过程序安全生成VALUES子句，优先使用参数绑定避免SQL注入。

PostgreSQL #

使用unnest函数直接将字符串数组转为表：

SELECT file_name
FROM unnest(ARRAY['file2', 'file4']) AS sftp_files(file_name)
LEFT JOIN return_files rf ON sftp_files.file_name = rf.file_name
WHERE rf.file_name IS NULL;

JPQL 解决方案 #

JPA支持通过子查询或参数集合直接筛选：

方式1：NOT IN 子查询 #

@Query("SELECT file FROM :availableFiles file WHERE file NOT IN (SELECT rf.fileName FROM ReturnFile rf)")
List<String> findUnprocessedFiles(@Param("availableFiles") Set<String> availableFiles);

方式2：NOT EXISTS 子查询（更高效，尤其已处理文件量大时） #

@Query("SELECT file FROM :availableFiles file WHERE NOT EXISTS (SELECT 1 FROM ReturnFile rf WHERE rf.fileName = file)")
List<String> findUnprocessedFiles(@Param("availableFiles") Set<String> availableFiles);

方式3：原生SQL绑定（兼容复杂场景） #

若JPA对集合参数支持有限，可使用原生SQL结合参数绑定：

@Query(value = "SELECT file_name FROM (VALUES (:availableFiles)) AS sftp_files(file_name) LEFT JOIN return_files rf ON sftp_files.file_name = rf.file_name WHERE rf.file_name IS NULL", nativeQuery = true)
List<String> findUnprocessedFiles(@Param("availableFiles") Set<String> availableFiles);

注意：需根据所用数据库调整原生SQL语法（如MySQL/PostgreSQL的临时表构造方式）。

注意事项 #

• 所有动态参数必须通过参数绑定传递，禁止直接拼接字符串以避免SQL注入。
• 若availableFiles规模较大，建议分批处理，避免单次查询性能问题。

内容的提问来源于stack exchange，提问作者Jonathan S. Fisher