程序与课程映射表内连接重复问题及Hibernate优化咨询

针对你提出的两个疑问，分别给出落地解决方案：

1. 不使用DISTINCT实现无重复查询 #

内连接导致程序数据重复的核心原因是：一个程序对应多个符合条件的课程，连接后会生成多条重复的程序记录。不用DISTINCT的话，可以通过**存在性判断（EXISTS子查询）**替代内连接，只保留主表的唯一数据：

SQL实现 #

SELECT SeqNo, pgm_id, start_date, end_date, pgm_description, location, timings
FROM PGM_DETAILS T1
WHERE T1.pgm_id = 110
AND EXISTS (
    SELECT 1 FROM COURSE_DETAILS T2
    WHERE T2.pgm_id = T1.pgm_id
    AND T2.course_name IN ('C basics','Java Basics')
);

这种方式仅检查是否存在符合条件的课程，不会因为多课程返回重复的程序数据，性能通常优于DISTINCT（尤其是数据量较大时）。

Hibernate/JPA实现 #

用JPQL编写查询的对应写法：

String jpql = "SELECT p FROM PGM_DETAILS p WHERE p.pgmId = :pgmId AND EXISTS " +
              "(SELECT c FROM COURSE_DETAILS c WHERE c.pgmId = p.pgmId AND c.courseName IN :courseNames)";
Query query = entityManager.createQuery(jpql);
query.setParameter("pgmId", 110);
query.setParameter("courseNames", Arrays.asList("C basics", "Java Basics"));
List<PGM_DETAILS> result = query.getResultList();

也可以调整实体映射的抓取策略，比如在@OneToMany注解中设置fetch = FetchType.SELECT或@Fetch(FetchMode.SUBSELECT)，避免Hibernate自动生成内连接导致的重复。

2. 单条查询返回分号分隔的课程，从根源避免重复 #

利用数据库的字符串聚合函数，将符合条件的课程拼接成单个字符串，同时通过分组保证程序数据唯一，完全避免重复。不同数据库的聚合函数不同，以下是主流数据库的实现方式：

MySQL（GROUP_CONCAT） #

SELECT 
    T1.SeqNo, T1.pgm_id, T1.start_date, T1.end_date, 
    T1.pgm_description, T1.location, T1.timings,
    GROUP_CONCAT(T2.course_name SEPARATOR ';') AS courses
FROM PGM_DETAILS T1
INNER JOIN COURSE_DETAILS T2
ON T1.pgm_id = T2.pgm_id
WHERE T1.pgm_id = 110 AND T2.course_name IN ('C basics','Java Basics')
GROUP BY T1.SeqNo, T1.pgm_id, T1.start_date, T1.end_date, T1.pgm_description, T1.location, T1.timings;

Oracle（LISTAGG） #

SELECT 
    T1.SeqNo, T1.pgm_id, T1.start_date, T1.end_date, 
    T1.pgm_description, T1.location, T1.timings,
    LISTAGG(T2.course_name, ';') WITHIN GROUP (ORDER BY T2.course_name) AS courses
FROM PGM_DETAILS T1
INNER JOIN COURSE_DETAILS T2
ON T1.pgm_id = T2.pgm_id
WHERE T1.pgm_id = 110 AND T2.course_name IN ('C basics','Java Basics')
GROUP BY T1.SeqNo, T1.pgm_id, T1.start_date, T1.end_date, T1.pgm_description, T1.location, T1.timings;

SQL Server（STRING_AGG） #

SELECT 
    T1.SeqNo, T1.pgm_id, T1.start_date, T1.end_date, 
    T1.pgm_description, T1.location, T1.timings,
    STRING_AGG(T2.course_name, ';') AS courses
FROM PGM_DETAILS T1
INNER JOIN COURSE_DETAILS T2
ON T1.pgm_id = T2.pgm_id
WHERE T1.pgm_id = 110 AND T2.course_name IN ('C basics','Java Basics')
GROUP BY T1.SeqNo, T1.pgm_id, T1.start_date, T1.end_date, T1.pgm_description, T1.location, T1.timings;

Hibernate/JPA实现 #

由于JPQL没有标准的字符串聚合函数，推荐使用原生SQL查询：

String sql = "SELECT T1.SeqNo, T1.pgm_id, T1.start_date, T1.end_date, " +
             "T1.pgm_description, T1.location, T1.timings, " +
             "GROUP_CONCAT(T2.course_name SEPARATOR ';') AS courses " +
             "FROM PGM_DETAILS T1 " +
             "INNER JOIN COURSE_DETAILS T2 ON T1.pgm_id = T2.pgm_id " +
             "WHERE T1.pgm_id = ?1 AND T2.course_name IN (?2, ?3) " +
             "GROUP BY T1.SeqNo, T1.pgm_id, T1.start_date, T1.end_date, T1.pgm_description, T1.location, T1.timings";
Query query = entityManager.createNativeQuery(sql);
query.setParameter(1, 110);
query.setParameter(2, "C basics");
query.setParameter(3, "Java Basics");
List<Object[]> result = query.getResultList();

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