Spring Boot中UNION ALL查询异常求助：跨同结构表取数失效

Hey there, I’ve run into this exact scenario before—your SQL works perfectly in psql or pgAdmin but fails to return both expected records in Spring Boot. Let’s break down the most common causes and straightforward fixes:

1. Primary Key Conflict in JPA Entity Mapping #

The biggest culprit here is JPA’s default behavior with primary keys. Since EMPID is marked as @Id in your entity class, the EntityManager assumes it’s a unique identifier. When it gets two rows with the same EMPID from the UNION ALL result, it’ll silently merge them into a single record without throwing an error.

Fix: #

Use a DTO (Data Transfer Object) without a primary key annotation to receive the merged results instead of your existing JPA entity. Here’s how to implement this:

• Create a simple DTO that matches your table’s column structure:

public class MergedEmployeeDTO {
    private Long empId;
    private String name;
    // Add all other columns present in your table1 and table2
    // Generate standard getters and setters for all fields
}

• Update your repository with a native query that returns this DTO:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    @Query(value = "SELECT * FROM table1 t1 WHERE t1.empid = 400 UNION ALL SELECT * FROM table2 t2 WHERE t2.empid = 400", 
           nativeQuery = true)
    List<MergedEmployeeDTO> getMergedEmployees();
}

2. Incorrect Result Handling with JdbcTemplate #

If you’re using JdbcTemplate instead of JPA, double-check your RowMapper logic. Make sure you’re not accidentally overwriting records or applying unintended deduplication.

Correct RowMapper Example: #

List<MergedEmployeeDTO> employees = jdbcTemplate.query(
    "SELECT * FROM table1 t1 WHERE t1.empid = 400 UNION ALL SELECT * FROM table2 t2 WHERE t2.empid = 400",
    (rs, rowNum) -> {
        MergedEmployeeDTO dto = new MergedEmployeeDTO();
        dto.setEmpId(rs.getLong("empid"));
        dto.setName(rs.getString("name"));
        // Map all other columns from the result set to the DTO
        return dto;
    }
);

Ensure each row from the result set is converted to a separate DTO instance—no logic that skips or merges rows based on EMPID.

3. Verify Parameter Binding (If Using Dynamic Values) #

If you’re passing 400 as a parameter instead of hardcoding it, confirm the parameter is being bound correctly. For example:

@Query(value = "SELECT * FROM table1 t1 WHERE t1.empid = ?1 UNION ALL SELECT * FROM table2 t2 WHERE t2.empid = ?1", 
       nativeQuery = true)
List<MergedEmployeeDTO> getMergedEmployees(Long empId);

Make sure you’re passing a Long (matching your PostgreSQL column type) and not an Integer—while PostgreSQL often handles type mismatches gracefully, this can sometimes cause unexpected results.

4. Check for Query Caching #

If you’ve enabled @Cacheable on your repository method, you might be getting cached results that only include one record. Temporarily disable caching or clear the cache to test if this is the issue.

Quick Validation Steps #

1. Enable SQL logging in Spring Boot to confirm the exact SQL being executed matches what you ran in the PostgreSQL client.
2. Test the query directly with JdbcTemplate first to rule out JPA-related issues.
3. Check the size of the returned List—if it’s 1, the primary key deduplication in JPA is almost certainly the problem.

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