| | |
|---|
| | | import org.springframework.data.jpa.repository.JpaRepository; |
|---|
| | | import org.springframework.data.jpa.repository.Query; |
|---|
| | | import org.springframework.data.repository.query.Param; |
|---|
| | | import org.springframework.stereotype.Repository; |
|---|
| | | |
|---|
| | | import java.util.List; |
|---|
| | | |
|---|
| | | @Repository |
|---|
| | | public interface ActivityRepository extends JpaRepository<Activity, Long> { |
|---|
| | | |
|---|
| | | // 查询比赛列表（pid=0表示比赛，非0表示阶段） |
|---|
| | | Page<Activity> findByPidAndStateOrderByCreateTimeDesc(Long pid, Integer state, Pageable pageable); |
|---|
| | | Page<Activity> findByPidAndStateAndNameContainingOrderByCreateTimeDesc(Long pid, int state, String name, Pageable pageable); |
|---|
| | | |
|---|
| | | // 按名称模糊查询比赛列表 |
|---|
| | | Page<Activity> findByPidAndStateAndNameContainingOrderByCreateTimeDesc(Long pid, Integer state, String name, Pageable pageable); |
|---|
| | | Page<Activity> findByPidAndNameContainingOrderByCreateTimeDesc(Long pid, String name, Pageable pageable); |
|---|
| | | |
|---|
| | | // 查询比赛的所有阶段 |
|---|
| | | List<Activity> findByPidAndStateOrderByCreateTimeAsc(Long pid, Integer state); |
|---|
| | | Page<Activity> findByPidOrderByCreateTimeDesc(Long pid, Pageable pageable); |
|---|
| | | |
|---|
| | | // 查询所有有效比赛（用于下拉选择） |
|---|
| | | List<Activity> findByPidAndStateOrderByNameAsc(Long pid, Integer state); |
|---|
| | | Page<Activity> findByPidAndStateOrderByCreateTimeDesc(Long pid, int state, Pageable pageable); |
|---|
| | | |
|---|
| | | // 临时测试：查询所有比赛（不使用state字段） |
|---|
| | | List<Activity> findByPidOrderByNameAsc(Long pid); |
|---|
| | | List<Activity> findByPidAndStateOrderByCreateTimeAsc(Long pid, int state); |
|---|
| | | |
|---|
| | | // 查询所有有效活动（包括比赛和阶段），按pid和名称排序 |
|---|
| | | List<Activity> findByStateOrderByPidAscNameAsc(Integer state); |
|---|
| | | List<Activity> findByStateOrderByPidAscNameAsc(int state); |
|---|
| | | |
|---|
| | | // 统计比赛数量 |
|---|
| | | @Query("SELECT COUNT(a) FROM Activity a WHERE a.pid = 0 AND a.state = 1") |
|---|
| | | long countActiveActivities(); |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.pid = 0 ORDER BY a.createTime DESC") |
|---|
| | | Page<Activity> findRecentActivities(Pageable pageable); |
|---|
| | | |
|---|
| | | // 查询进行中的比赛 |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.pid = 0 AND a.state = 1 AND a.matchTime <= CURRENT_TIMESTAMP AND a.signupDeadline >= CURRENT_TIMESTAMP") |
|---|
| | | /** |
|---|
| | | * 根据活动ID查询活动 |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.id = :id") |
|---|
| | | Activity findActivityById(@Param("id") Long id); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 根据状态查询活动数量 |
|---|
| | | */ |
|---|
| | | @Query("SELECT COUNT(a) FROM Activity a WHERE a.state = :state") |
|---|
| | | Long countByState(@Param("state") int state); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 统计当前进行中的活动数量（状态为1且pid=0的主活动） |
|---|
| | | */ |
|---|
| | | @Query("SELECT COUNT(a) FROM Activity a WHERE a.state = 1 AND a.pid = 0") |
|---|
| | | Long countActiveActivities(); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 查询所有父级活动（pid = 0） |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.pid = 0 ORDER BY a.createTime DESC") |
|---|
| | | List<Activity> findAllParentActivities(); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 根据父级ID查询子活动 |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.pid = :pid ORDER BY a.createTime DESC") |
|---|
| | | List<Activity> findChildActivitiesByPid(@Param("pid") Long pid); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 根据名称模糊查询活动 |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.name LIKE %:name% ORDER BY a.createTime DESC") |
|---|
| | | List<Activity> findByNameContaining(@Param("name") String name); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 查询指定状态的活动，按创建时间排序 |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.state = :state ORDER BY a.createTime DESC") |
|---|
| | | List<Activity> findByStateOrderByCreateTimeDesc(@Param("state") int state); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 分页查询指定状态的活动 |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.state = :state ORDER BY a.createTime DESC") |
|---|
| | | Page<Activity> findByStateOrderByCreateTimeDesc(@Param("state") int state, Pageable pageable); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 查询活动及其评分方案 |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a LEFT JOIN FETCH a.ratingScheme WHERE a.id = :id") |
|---|
| | | Activity findActivityWithRatingScheme(@Param("id") Long id); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 查询进行中的活动（状态为1的活动） |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.state = 1 ORDER BY a.createTime DESC") |
|---|
| | | List<Activity> findOngoingActivities(); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 查找指定活动的第一阶段（sortOrder=1） |
|---|
| | | */ |
|---|
| | | @Query("SELECT a FROM Activity a WHERE a.pid = :activityId AND a.sortOrder = 1 AND a.state = 1") |
|---|
| | | Activity findFirstStageByActivityId(@Param("activityId") Long activityId); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 根据父级ID和状态查询活动，按sortOrder排序 |
|---|
| | | */ |
|---|
| | | List<Activity> findByPidAndStateOrderBySortOrderAsc(Long pid, Integer state); |
|---|
| | | } |
|---|
