小组项目day8
今天完成了分页查询，发现了一个很有意思的问题，那就是由于增和改操作都用的同一个函数导致出了乐观锁这样之前从未听说过的问题。分页查询部分主要改动了仓库的代码。
package com.example.springbootdemo.repository;
import com.example.springbootdemo.entity.safetyrisk;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
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.Date;
import java.util.List;
@Repository
public interface SafetyriskRepository extends JpaRepository<safetyrisk, Integer> {
@Query("SELECT s FROM safetyrisk s WHERE (:id IS NULL OR s.id = :id) AND (:riskLevel IS NULL OR :riskLevel = '' OR s.risk_level = :riskLevel)")
List
List<safetyrisk> findAllById(Integer id);
@Query("SELECT s FROM safetyrisk s WHERE " +
"(:id IS NULL OR s.id = :id) AND " +
"(:risk_code IS NULL OR s.risk_code = :risk_code) AND " +
"(:company IS NULL OR s.company = :company) AND " +
"(:new_department IS NULL OR s.new_department = :new_department) AND " +
"(:work_area IS NULL OR s.work_area = :work_area) AND " +
"(:team IS NULL OR s.team = :team) AND " +
"(:professional_system IS NULL OR s.professional_system = :professional_system) AND " +
"(:risk_category IS NULL OR s.risk_category = :risk_category) AND " +
"(:risk_item IS NULL OR s.risk_item = :risk_item) AND " +
"(:risk_point IS NULL OR s.risk_point = :risk_point) AND " +
"(:risk_level IS NULL OR s.risk_level = :risk_level) AND " +
"(:harm_degree IS NULL OR s.harm_degree = :harm_degree) AND " +
"(:control_measures IS NULL OR s.control_measures = :control_measures) AND " +
"(:control_position IS NULL OR s.control_position = :control_position) AND " +
"(:control_personnel IS NULL OR s.control_personnel = :control_personnel) AND " +
"(:quantified_requirements IS NULL OR s.quantified_requirements = :quantified_requirements) AND " +
"(:entry_date IS NULL OR s.entry_date = :entry_date) AND " +
"(:review_status IS NULL OR s.review_status = :review_status) AND " +
"(:review_date IS NULL OR s.review_date = :review_date)")
Page<safetyrisk> findByMultipleConditions(
@Param("id") Integer id,
@Param("risk_code") String risk_code,
@Param("company") String company,
@Param("new_department") String new_department,
@Param("work_area") String work_area,
@Param("team") String team,
@Param("professional_system") String professional_system,
@Param("risk_category") String risk_category,
@Param("risk_item") String risk_item,
@Param("risk_point") String risk_point,
@Param("risk_level") String risk_level,
@Param("harm_degree") String harm_degree,
@Param("control_measures") String control_measures,
@Param("control_position") String control_position,
@Param("control_personnel") String control_personnel,
@Param("quantified_requirements") String quantified_requirements,
@Param("entry_date") Date entry_date,
@Param("review_status") String review_status,
@Param("review_date") Date review_date,
Pageable pageable
);
@Query("SELECT s FROM safetyrisk s WHERE s.risk_level = :riskLevel")
List<safetyrisk> findByRiskLevel(@Param("riskLevel") String riskLevel);
@Query("SELECT s FROM safetyrisk s " +
"WHERE (:id IS NULL OR s.id = :id) AND " +
"(:risk_code IS NULL OR s.risk_code = :risk_code) AND " +
"(:company IS NULL OR s.company = :company) AND " +
"(:new_department IS NULL OR s.new_department = :new_department) AND " +
"(:work_area IS NULL OR s.work_area = :work_area) AND " +
"(:team IS NULL OR s.team = :team) AND " +
"(:professional_system IS NULL OR s.professional_system = :professional_system) AND " +
"(:risk_category IS NULL OR s.risk_category = :risk_category) AND " +
"(:risk_item IS NULL OR s.risk_item = :risk_item) AND " +
"(:risk_point IS NULL OR s.risk_point = :risk_point) AND " +
"(:risk_level IS NULL OR s.risk_level = :risk_level) AND " +
"(:harm_degree IS NULL OR s.harm_degree = :harm_degree) AND " +
"(:control_measures IS NULL OR s.control_measures = :control_measures) AND " +
"(:control_position IS NULL OR s.control_position = :control_position) AND " +
"(:control_personnel IS NULL OR s.control_personnel = :control_personnel) AND " +
"(:quantified_requirements IS NULL OR s.quantified_requirements = :quantified_requirements) AND " +
"(:entry_date IS NULL OR s.entry_date = :entry_date) AND " +
"(:review_status IS NULL OR s.review_status = :review_status) AND " +
"(:review_date IS NULL OR s.review_date = :review_date)")
List<safetyrisk> findByMultipleConditions(
@Param("id") Integer id,
@Param("risk_code") String risk_code,
@Param("company") String company,
@Param("new_department") String new_department,
@Param("work_area") String work_area,
@Param("team") String team,
@Param("professional_system") String professional_system,
@Param("risk_category") String risk_category,
@Param("risk_item") String risk_item,
@Param("risk_point") String risk_point,
@Param("risk_level") String risk_level,
@Param("harm_degree") String harm_degree,
@Param("control_measures") String control_measures,
@Param("control_position") String control_position,
@Param("control_personnel") String control_personnel,
@Param("quantified_requirements") String quantified_requirements,
@Param("entry_date") Date entry_date,
@Param("review_status") String review_status,
@Param("review_date") Date review_date
);
@Query("SELECT s FROM safetyrisk s WHERE s.risk_code = :risk_code")
List<safetyrisk> findByRiskCode(@Param("risk_code") String risk_code);
@Query("SELECT s FROM safetyrisk s " +
"WHERE (:company IS NULL OR s.company = :company) AND " +
"(:new_department IS NULL OR s.new_department = :new_department) AND " +
"(:work_area IS NULL OR s.work_area = :work_area)")
List<safetyrisk> findByDepartmentHierarchy(
@Param("company") String company,
@Param("new_department") String new_department,
@Param("work_area") String work_area
);
@Query("SELECT s.company AS name, s.risk_level AS level, COUNT(s) AS count " +
"FROM safetyrisk s " +
"GROUP BY s.company, s.risk_level")
List<Object[]> countByCompanyAndLevel();
@Query("SELECT s.company, s.new_department, s.risk_level, COUNT(s) " +
"FROM safetyrisk s " +
"GROUP BY s.company, s.new_department, s.risk_level")
List<Object[]> countByCompanyDepartmentAndLevel();
@Query("SELECT s.company, s.new_department, s.work_area, s.risk_level, COUNT(s) " +
"FROM safetyrisk s " +
"GROUP BY s.company, s.new_department, s.work_area, s.risk_level")
List<Object[]> countByCompanyDepartmentWorkAreaAndLevel();
@Query("SELECT s.company, s.new_department, s.work_area, s.team, s.risk_level, COUNT(s) " +
"FROM safetyrisk s " +
"GROUP BY s.company, s.new_department, s.work_area, s.team, s.risk_level")
List<Object[]> countByFullHierarchyAndLevel();
@Query("SELECT s.id FROM safetyrisk s WHERE " +
"(CASE WHEN :company IS NULL OR s.company = :company THEN 1 ELSE 0 END * 1 + " +
"CASE WHEN :new_department IS NULL OR s.new_department = :new_department THEN 1 ELSE 0 END * 1 + " +
"CASE WHEN :work_area IS NULL OR s.work_area = :work_area THEN 1 ELSE 0 END * 1 + " +
"CASE WHEN :team IS NULL OR s.team = :team THEN 1 ELSE 0 END * 1 + " +
"CASE WHEN :professional_system IS NULL OR s.professional_system = :professional_system THEN 1 ELSE 0 END * 2 + " +
"CASE WHEN :risk_category IS NULL OR s.risk_category = :risk_category THEN 1 ELSE 0 END * 2 + " +
"CASE WHEN :risk_item IS NULL OR s.risk_item = :risk_item THEN 1 ELSE 0 END * 2 + " +
"CASE WHEN :risk_point IS NULL OR s.risk_point = :risk_point THEN 1 ELSE 0 END * 2 + " +
"CASE WHEN :risk_level IS NULL OR s.risk_level = :risk_level THEN 1 ELSE 0 END * 2 + " +
"CASE WHEN :harm_degree IS NULL OR s.harm_degree = :harm_degree THEN 1 ELSE 0 END * 2 + " +
"CASE WHEN :control_measures IS NULL OR s.control_measures = :control_measures THEN 1 ELSE 0 END * 2 + " +
"CASE WHEN :control_position IS NULL OR s.control_position = :control_position THEN 1 ELSE 0 END * 2 + " +
"CASE WHEN :control_personnel IS NULL OR s.control_personnel = :control_personnel THEN 1 ELSE 0 END * 3 + " +
"CASE WHEN :quantified_requirements IS NULL OR s.quantified_requirements = :quantified_requirements THEN 1 ELSE 0 END * 3) >= 18")
List<Integer> findSimilarRiskIds(
@Param("company") String company,
@Param("new_department") String new_department,
@Param("work_area") String work_area,
@Param("team") String team,
@Param("professional_system") String professional_system,
@Param("risk_category") String risk_category,
@Param("risk_item") String risk_item,
@Param("risk_point") String risk_point,
@Param("risk_level") String risk_level,
@Param("harm_degree") String harm_degree,
@Param("control_measures") String control_measures,
@Param("control_position") String control_position,
@Param("control_personnel") String control_personnel,
@Param("quantified_requirements") String quantified_requirements
);
@Query("SELECT s.risk_level, COUNT(s) FROM safetyrisk s " +
"WHERE (:startDate IS NULL OR s.entry_date >= :startDate) AND (:endDate IS NULL OR s.entry_date <= :endDate) " +
"AND (:company IS NULL OR s.company = :company) AND (:new_department IS NULL OR s.new_department = :new_department) " +
"AND (:work_area IS NULL OR s.work_area = :work_area) AND (:team IS NULL OR s.team = :team) " +
"GROUP BY s.risk_level")
List<Object[]> countByRiskLevel(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
@Param("company") String company, @Param("new_department") String new_department,
@Param("work_area") String work_area, @Param("team") String team);
@Query("SELECT s.review_status, COUNT(s) FROM safetyrisk s " +
"WHERE (:startDate IS NULL OR s.entry_date >= :startDate) AND (:endDate IS NULL OR s.entry_date <= :endDate) " +
"AND (:company IS NULL OR s.company = :company) AND (:new_department IS NULL OR s.new_department = :new_department) " +
"AND (:work_area IS NULL OR s.work_area = :work_area) AND (:team IS NULL OR s.team = :team) " +
"GROUP BY s.review_status")
List<Object[]> countByReviewStatus(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
@Param("company") String company, @Param("new_department") String new_department,
@Param("work_area") String work_area, @Param("team") String team);
@Query("SELECT FUNCTION('YEAR', s.entry_date), FUNCTION('MONTH', s.entry_date), COUNT(s) FROM safetyrisk s " +
"WHERE (:startDate IS NULL OR s.entry_date >= :startDate) AND (:endDate IS NULL OR s.entry_date <= :endDate) " +
"AND (:company IS NULL OR s.company = :company) AND (:new_department IS NULL OR s.new_department = :new_department) " +
"AND (:work_area IS NULL OR s.work_area = :work_area) AND (:team IS NULL OR s.team = :team) " +
"AND (:riskLevel IS NULL OR s.risk_level = :riskLevel) " +
"GROUP BY FUNCTION('YEAR', s.entry_date), FUNCTION('MONTH', s.entry_date) " +
"ORDER BY FUNCTION('YEAR', s.entry_date), FUNCTION('MONTH', s.entry_date)")
List<Object[]> countByYearAndMonth(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
@Param("company") String company, @Param("new_department") String new_department,
@Param("work_area") String work_area, @Param("team") String team,
@Param("riskLevel") String riskLevel);
@Query("SELECT FUNCTION('YEAR', s.entry_date), (FUNCTION('QUARTER', s.entry_date)), COUNT(s) FROM safetyrisk s " +
"WHERE (:startDate IS NULL OR s.entry_date >= :startDate) AND (:endDate IS NULL OR s.entry_date <= :endDate) " +
"AND (:company IS NULL OR s.company = :company) AND (:new_department IS NULL OR s.new_department = :new_department) " +
"AND (:work_area IS NULL OR s.work_area = :work_area) AND (:team IS NULL OR s.team = :team) " +
"AND (:riskLevel IS NULL OR s.risk_level = :riskLevel) " +
"GROUP BY FUNCTION('YEAR', s.entry_date), FUNCTION('QUARTER', s.entry_date) " +
"ORDER BY FUNCTION('YEAR', s.entry_date), FUNCTION('QUARTER', s.entry_date)")
List<Object[]> countByYearAndQuarter(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
@Param("company") String company, @Param("new_department") String new_department,
@Param("work_area") String work_area, @Param("team") String team,
@Param("riskLevel") String riskLevel);
@Query("SELECT FUNCTION('YEAR', s.entry_date), FUNCTION('MONTH', s.entry_date), COUNT(s) FROM safetyrisk s " +
"WHERE (:startYear IS NULL OR FUNCTION('YEAR', s.entry_date) BETWEEN :startYear AND :endYear) " +
"AND FUNCTION('MONTH', s.entry_date) = :month " +
"AND (:company IS NULL OR s.company = :company) AND (:new_department IS NULL OR s.new_department = :new_department) " +
"AND (:work_area IS NULL OR s.work_area = :work_area) AND (:team IS NULL OR s.team = :team) " +
"AND (:riskLevel IS NULL OR s.risk_level = :riskLevel) " +
"GROUP BY FUNCTION('YEAR', s.entry_date), FUNCTION('MONTH', s.entry_date) " +
"ORDER BY FUNCTION('YEAR', s.entry_date)")
List<Object[]> countByYearMonthYoY(@Param("startYear") Integer startYear, @Param("endYear") Integer endYear,
@Param("month") Integer month, @Param("company") String company,
@Param("new_department") String new_department, @Param("work_area") String work_area,
@Param("team") String team, @Param("riskLevel") String riskLevel);
@Query("SELECT FUNCTION('YEAR', s.entry_date), FUNCTION('QUARTER', s.entry_date), COUNT(s) FROM safetyrisk s " +
"WHERE (:startYear IS NULL OR FUNCTION('YEAR', s.entry_date) BETWEEN :startYear AND :endYear) " +
"AND FUNCTION('QUARTER', s.entry_date) = :quarter " +
"AND (:company IS NULL OR s.company = :company) AND (:new_department IS NULL OR s.new_department = :new_department) " +
"AND (:work_area IS NULL OR s.work_area = :work_area) AND (:team IS NULL OR s.team = :team) " +
"AND (:riskLevel IS NULL OR s.risk_level = :riskLevel) " +
"GROUP BY FUNCTION('YEAR', s.entry_date), FUNCTION('QUARTER', s.entry_date) " +
"ORDER BY FUNCTION('YEAR', s.entry_date)")
List<Object[]> countByYearQuarterYoY(@Param("startYear") Integer startYear, @Param("endYear") Integer endYear,
@Param("quarter") Integer quarter, @Param("company") String company,
@Param("new_department") String new_department, @Param("work_area") String work_area,
@Param("team") String team, @Param("riskLevel") String riskLevel);
// ================= 组织树查询相关方法 =================
@Query("SELECT DISTINCT s.company FROM safetyrisk s WHERE s.company IS NOT NULL")
List<String> findAllCompanies();
@Query("SELECT DISTINCT s.new_department FROM safetyrisk s WHERE s.company = :company AND s.new_department IS NOT NULL")
List<String> findDepartmentsByCompany(@Param("company") String company);
@Query("SELECT DISTINCT s.work_area FROM safetyrisk s WHERE s.new_department = :department AND s.work_area IS NOT NULL")
List<String> findWorkAreasByDepartment(@Param("department") String newDepartment);
@Query("SELECT DISTINCT s.team FROM safetyrisk s WHERE s.work_area = :workArea AND s.team IS NOT NULL")
List<String> findTeamsByWorkArea(@Param("workArea") String workArea);
@Query("SELECT COUNT(s) FROM safetyrisk s WHERE s.company = :company")
Long countRisksByCompany(@Param("company") String company);
@Query("SELECT COUNT(s) FROM safetyrisk s WHERE s.new_department = :department")
Long countRisksByDepartment(@Param("department") String department);
@Query("SELECT COUNT(s) FROM safetyrisk s WHERE s.work_area = :workArea")
Long countRisksByWorkArea(@Param("workArea") String workArea);
@Query("SELECT COUNT(s) FROM safetyrisk s WHERE s.team = :team")
Long countRisksByTeam(@Param("team") String team);
@Query("SELECT s.risk_level, COUNT(s) FROM safetyrisk s WHERE s.company = :company GROUP BY s.risk_level")
List<Object[]> countRisksByLevelAndCompany(@Param("company") String company);
@Query("SELECT s.risk_level, COUNT(s) FROM safetyrisk s WHERE s.new_department = :department GROUP BY s.risk_level")
List<Object[]> countRisksByLevelAndDepartment(@Param("department") String department);
@Query("SELECT s.risk_level, COUNT(s) FROM safetyrisk s WHERE s.work_area = :workArea GROUP BY s.risk_level")
List<Object[]> countRisksByLevelAndWorkArea(@Param("workArea") String workArea);
@Query("SELECT s.risk_level, COUNT(s) FROM safetyrisk s WHERE s.team = :team GROUP BY s.risk_level")
List<Object[]> countRisksByLevelAndTeam(@Param("team") String team);
}
浙公网安备 33010602011771号