基于SpringBoot2.1与Java8的原生SQL关联查询报错求助

Let's break down what's causing this error and how to fix it step by step.

Root Cause #

Your error stems from two key issues with how you're trying to map the native SQL query results to the Assignment entity:

1. Missing Primary Key Column: Hibernate needs the primary key of the Assignment entity to map query results to it. Your SQL query doesn't select the id_assignment column (the primary key of the assignment table, based on the error), so Hibernate can't find it and throws the No such column: id_assignment exception.
2. Mismatched Data Structure: You're selecting fields from joined tables (project.PROJECT_NAME, contributor.FIRST_NAME) that don't belong to the Assignment entity itself. Trying to map these directly to an Assignment object will cause mapping failures even if you fix the primary key issue.

Solutions #

Option 1: Use Entity Associations (Recommended) #

Instead of writing raw native SQL for joins, leverage JPA's entity associations to let Hibernate handle the joins automatically. This is cleaner and aligns with JPA best practices.

Step 1: Update the Assignment Entity

Add @ManyToOne associations to link Assignment with Contributor and Project:

@Entity
@Table(name = "assignment")
public class Assignment {
    @Id
    @Column(name = "id_assignment") // Match your actual DB primary key column name
    private Integer id;

    @Column(name = "START_DATE")
    private LocalDate startDate;

    @Column(name = "END_DATE")
    private LocalDate endDate;

    @ManyToOne
    @JoinColumn(name = "CONTRIBUTOR_id") // Matches the foreign key in assignment table
    private Contributor contributor;

    @ManyToOne
    @JoinColumn(name = "PROJECT_ID_PROJECT") // Matches the foreign key in assignment table
    private Project project;

    // Getters and Setters
}

Make sure you have corresponding Contributor and Project entities mapped to their respective tables.

Step 2: Update the DAO with JPQL Query

Replace your native SQL query with a JPQL query that uses the entity associations:

@Repository
public interface AssignmentDao extends JpaRepository<Assignment, Integer> {
    @Query("SELECT a FROM Assignment a JOIN a.contributor c JOIN a.project p")
    List<Assignment> fetchAssignmentDataInnerJoin();
}

Hibernate will generate the correct SQL join under the hood, and you'll get full Assignment objects with their associated Contributor and Project data. You can access assignment.getProject().getProjectName() and assignment.getContributor().getFirstName() in your service/controller.

Option 2: Use a DTO for Custom Query Results #

If you only need the specific fields you're selecting (and don't want full entity objects), create a Data Transfer Object (DTO) to map the query results.

Step 1: Create an AssignmentDTO Class

public class AssignmentDTO {
    private String projectName;
    private String firstName;
    private LocalDate startDate;
    private LocalDate endDate;

    // Constructor that matches the order of fields in your SQL query
    public AssignmentDTO(String projectName, String firstName, LocalDate startDate, LocalDate endDate) {
        this.projectName = projectName;
        this.firstName = firstName;
        this.startDate = startDate;
        this.endDate = endDate;
    }

    // Getters (no setters needed for constructor-based mapping)
    public String getProjectName() { return projectName; }
    public String getFirstName() { return firstName; }
    public LocalDate getStartDate() { return startDate; }
    public LocalDate getEndDate() { return endDate; }
}

Step 2: Update the DAO to Return the DTO

Modify your repository method to return List<AssignmentDTO> instead of List<Assignment>:

@Repository
public interface AssignmentDao extends JpaRepository<Assignment, Integer> {
    @Query(value = "SELECT project.PROJECT_NAME, contributor.FIRST_NAME, assignment.START_DATE, assignment.END_DATE " +
                   "FROM assignment " +
                   "JOIN contributor ON assignment.CONTRIBUTOR_id = contributor.id " +
                   "JOIN project ON assignment.PROJECT_ID_PROJECT = project.ID_PROJECT",
           nativeQuery = true)
    List<AssignmentDTO> fetchAssignmentDataInnerJoin();
}

Step 3: Update Service and Controller

Adjust your service and controller to use AssignmentDTO instead of Assignment:

// Service
public List<AssignmentDTO> fetchAssignmentDataInnerJoin(){
    return assignmentDao.fetchAssignmentDataInnerJoin();
}

// Controller
@GetMapping({"/listAssignments"})
public String listAssignment(ModelMap model) {
    List<AssignmentDTO> assignments = assignmentService.fetchAssignmentDataInnerJoin();
    model.addAttribute("assignments", assignments);
    System.out.println("Liste des affectations : " + assignments);
    return "allAssignments";
}

Quick Fix (Not Recommended) #

If you insist on using native SQL and returning Assignment entities (not ideal), you need to include the primary key column in your query and ensure all selected fields map to Assignment properties. However, since PROJECT_NAME and FIRST_NAME don't belong to Assignment, this approach won't give you that data in the entity. Example fixed SQL:

SELECT assignment.id_assignment, assignment.START_DATE, assignment.END_DATE 
FROM assignment 
JOIN contributor ON assignment.CONTRIBUTOR_id = contributor.id 
JOIN project ON assignment.PROJECT_ID_PROJECT = project.ID_PROJECT

But again, this won't include the project or contributor names in the Assignment object, so using associations or DTOs is better.

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