Spring Boot后端游戏机制筛选功能异常问题排查

问题背景 #

使用Java Spring Boot开发后端时，实现游戏列表按机制名称筛选功能，单机制筛选正常，但多机制筛选无法正确过滤游戏。游戏数据结构如下：

{
  "id_game": 1,
  "code": "DR54HY",
  "name": "Name",
  "age": 4,
  "price": 29,
  "description": "Description",
  "nbGamer": 2,
  "mechanisms": [
     {
       "id_mechanism": 1,
       "name": "Card"
     },
     {
       "id_mechanism": 2,
       "name": "Adventure"
     }
  ]
}

现有代码实现 #

GameRepository.java #

@Repository
public interface GameRepository extends JpaRepository<Game, Integer> {
    Page<Game> findAll(Pageable pageable);
    Page<Game> findAllByMechanismsName(Pageable pageable, List<String> mechanismsName);
}

GameService.java #

@Service
public class GameService {
    
    @Autowired
    private GameRepository gameRepository;
    
    public Page<Game> getGames(Pageable pageable, List<String> mechanismsName) {
        
        if (mechanismsName != null) {
            return gameRepository.findAllByMechanismsName(pageable, mechanismsName);
         } else {
            return gameRepository.findAll(pageable);
        }
   }
}

GameController.java #

@RestController
public class GameController {
    
    @Autowired
    private GameService gameService;
    
    @GetMapping("/games")
    public ResponseEntity<Page<Game>> getGames(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "10") int pageSize,
            @RequestParam(required = false) List<String> mechanismsName
            
    ) {
        
        Pageable pageable = PageRequest.of(page, pageSize);
        Page<Game> gamesPage = gameService.getGames(pageable, mechanismsName);
        
        if (gamesPage.isEmpty()) {
            return new ResponseEntity<>(HttpStatus.NO_CONTENT);
        } else {
            return new ResponseEntity<>(gamesPage, HttpStatus.OK);
        }
    }
}

请求示例 #

GET /games?page=0&pageSize=10&mechanismsName=Card

GET /games?page=0&pageSize=10&mechanismsName=Card,Power

问题原因 #

1. 查询逻辑不符合需求：当前findAllByMechanismsName方法，Spring Data JPA会生成OR逻辑的SQL（即游戏包含任意一个指定机制就会被返回），但如果需求是筛选同时包含所有指定机制的游戏，该逻辑就会失效。
2. 方法命名不规范：针对集合类型的参数，Spring Data JPA推荐使用In关键字明确表示OR逻辑，原方法名findAllByMechanismsName的行为未明确，可能导致解析异常。

解决方案 #

情况1：需求为筛选同时包含所有指定机制的游戏 #

修改GameRepository，使用自定义JPQL实现AND逻辑：

@Repository
public interface GameRepository extends JpaRepository<Game, Integer> {
    Page<Game> findAll(Pageable pageable);

    @Query("SELECT g FROM Game g JOIN g.mechanisms m WHERE m.name IN :mechanismsName GROUP BY g.id_game HAVING COUNT(DISTINCT m.name) = :mechanismsSize")
    Page<Game> findAllByMechanismsAllMatch(Pageable pageable, @Param("mechanismsName") List<String> mechanismsName, @Param("mechanismsSize") int mechanismsSize);
}

修改GameService中的调用逻辑，传入机制名称列表的长度：

public Page<Game> getGames(Pageable pageable, List<String> mechanismsName) {
    if (mechanismsName != null && !mechanismsName.isEmpty()) {
        return gameRepository.findAllByMechanismsAllMatch(pageable, mechanismsName, mechanismsName.size());
    } else {
        return gameRepository.findAll(pageable);
    }
}

情况2：需求为筛选包含任意一个指定机制的游戏 #

只需修改GameRepository的方法名，使用In关键字明确OR逻辑：

@Repository
public interface GameRepository extends JpaRepository<Game, Integer> {
    Page<Game> findAll(Pageable pageable);
    Page<Game> findAllByMechanismsNameIn(Pageable pageable, List<String> mechanismsName);
}

额外验证 #

确认Spring MVC是否正确解析逗号分隔的参数：默认情况下，Spring会自动将mechanismsName=Card,Power解析为包含两个元素的List。如果解析异常，可在参数上添加@RequestParam的delimiter属性（Spring Boot 2.3+支持）：

@RequestParam(required = false, delimiter = ",") List<String> mechanismsName

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