Spring JPA Many to Many with extra column not updating

Question

I have a many to many relationship between proposals and companies. That relationship is stored in the table proposal_companies along with an ID for the function of the company as it relates to the proposal.

Here are the (what I consider) relevant parts of the entities:

Proposal

@Entity
@Table(name="sales.proposals")
public class Proposal implements Serializable {
    private static final long serialVersionUID = 1L;
    
    private int proposalID;
    private List<ProposalCompany> companies = new ArrayList<>();
    
    public Proposal() {}
    
    @Id
    @Column(name="pk_proposalid")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @JsonView({View.ProposalView.class,View.ProposalRevisionView.class})
    public int getProposalID() {
        return proposalID;
    }

    public void setProposalID(int proposalID) {
        this.proposalID = proposalID;
    }

    @OneToMany(mappedBy="proposal")
    @JsonView(View.ProposalView.class)
    public List<ProposalCompany> getCompanies() {
        return companies;
    }
    
    public void setCompanies(List<ProposalCompany> companies) {
        this.companies = companies;
    }
    
    public void addCompany(Company company) {
        ProposalCompany proposalCompany = new ProposalCompany(this, company);
        this.companies.add(proposalCompany);
        company.getProposals().add(proposalCompany);
    }
    
    public void removeCompany(Company company) {
        for (Iterator<ProposalCompany> iterator = companies.iterator(); iterator.hasNext(); ) {
            ProposalCompany proposalCompany = iterator.next();
 
            if (proposalCompany.getProposal().equals(this) &&
                    proposalCompany.getCompany().equals(company)) {
                iterator.remove();
                proposalCompany.getCompany().getProposals().remove(proposalCompany);
                proposalCompany.setProposal(null);
                proposalCompany.setCompany(null);
            }
        }
    }
}

Company

@Entity
@Table(name="companies.companies")
public class Company implements Serializable {
    private static final long serialVersionUID = 1L;
    
    private int companyID;
    private List<ProposalCompany> proposals = new ArrayList<>();
    
    public Company() {}
    
    @Id
    @Column(name="pk_companyid")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @JsonView(View.AllCompaniesView.class)
    public int getCompanyID() {
        return companyID;
    }

    public void setCompanyID(int companyID) {
        this.companyID = companyID;
    }

    @OneToMany(mappedBy="company")
    @JsonView(View.SingleCompanyView.class)
    public List<ProposalCompany> getProposals() {
        return proposals;
    }

    public void setProposals(List<ProposalCompany> proposals) {
        this.proposals = proposals;
    }
}

ProposalCompany

@Entity
@Table(name="sales.proposal_companies")
public class ProposalCompany implements Serializable {
    private static final long serialVersionUID = 1L;
    
    private Proposal proposal;
    private Company company;
    private JobFunction jobFunction;

    public ProposalCompany() {}
    
    public ProposalCompany(Proposal proposal, Company company) {
        this.proposal = proposal;
        this.company = company;
    }

    @Id
    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="fk_proposalid")
    @JsonView(View.SingleCompanyView.class)
    public Proposal getProposal() {
        return proposal;
    }

    public void setProposal(Proposal proposal) {
        this.proposal = proposal;
    }

    @Id
    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="fk_companyid")
    @JsonView(View.ProposalView.class)
    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    @ManyToOne
    @JoinColumn(name="fk_jobfunctionid")
    @JsonView(View.ProposalView.class)
    public JobFunction getJobFunction() {
        return jobFunction;
    }

    public void setJobFunction(JobFunction jobFunction) {
        this.jobFunction = jobFunction;
    }

    @Override
    public String toString() {
        return "Proposal: "+(proposal==null ? "NULL" : proposal.toString())+" / Company: "+(company==null ? "NULL" : company.toString())+" / Function: "+(jobFunction==null ? "NULL": jobFunction.toString());
    }
}

This works fine for existing entries in the database. The problem comes when I add a company from the UI, or even try to change the function for an existing company attached to a proposal. (I am using Angular 5, and it passes a Proposal object in JSON format to Spring.)

When I add a new company, this is the error:

2018-01-30 09:02:34,187 https-jsse-nio-8493-exec-8 ERROR [/].[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; 
nested exception is org.springframework.orm.jpa.JpaObjectRetrievalFailureException: Unable to find com.couplingcorp.sales.domain.proposals.ProposalCompany with id Proposal: Proposal ID: 2923 / Company: 10 - Company Name / Function: NULL; 
nested exception is javax.persistence.EntityNotFoundException: Unable to find com.couplingcorp.sales.domain.proposals.ProposalCompany with id Proposal: Proposal ID: 2923 / Company: 10 - Company Name / Function: NULL] with root cause

even though this is the JSON coming in:

companies   […]
    0   {…}
        company {…}
            companyID:      10
            companyName:    Company Name
            companyAbbr:
        jobFunction {…}
            jobFunctionID:  4
            jobFunctionName:    Sole Handler
            jobFunctionDefinition:  Company who purchases and uses the product

Here is how the JSON is handled in Spring on the service layer:

@Override
public Proposal update(Proposal proposal) throws EntityNotFoundException {
    Optional<Proposal> prop = repository.findById(proposal.getProposalID());
    if (!prop.isPresent()) {
        throw new EntityNotFoundException();
    }

    // in cases where a ProposalCompany has been added or updated, we need to set up the objects appropriately
    proposal.getCompanies().stream()
        .filter((pc) -> pc.getProposal()==null)
        .forEachOrdered((pc) -> {
            LOGGER.debug("The proposal is not set for "+pc.toString());
            pc.setProposal(proposal);
            LOGGER.debug("The proposal is now set for "+pc.toString());
        });
    
    return repository.save(proposal);
}

I can see in the logs that at the point where the proposal is being saved, it looks like the ProposalCompany object is OK:

2018-01-30 09:02:34,112 https-jsse-nio-8493-exec-8 DEBUG proposals.ProposalServiceImpl - The proposal is not set for Proposal: NULL / Company: 10 - Company Name / Function: 4: Sole Handler
2018-01-30 09:02:34,112 https-jsse-nio-8493-exec-8 DEBUG proposals.ProposalServiceImpl - The proposal is now set for Proposal: Proposal ID: 2923 / Company: 10 - Company Name / Function: 4: Sole Handler

What am I missing that would cause it to not find the entity, even when it clearly has all the information it needs?

I am using Spring Boot 2.0.0.M7 with Hibernate 5.2.12.

Here is the link to the full log from the point where save is called (I can't put it here because it is too long): https://pastebin.com/2AjVA2zp

EDIT: I also tried two other approaches.

The second was as described here. That seems promising, but I get the error Unknown column 'companies0_.proposal_pk_proposalid' in 'field list'. Somehow with the embedded ID it is not forming the SQL correctly. If I ignored the companies portion of the JSON, then everything worked. But I need to know the companies, so that is not a valid option.

The third approach was similar to the second, except that I used the actual Proposal and Company objects in the @Embeddable PropoasalCompanyID class, but there I couldn't figure out how to map the ManyToMany aspect.

Answer 1

That's easy. You forgot to cascade properly.

First, you should never cascade from child entities to parent one. So instead of:

@ManyToOne(cascade=CascadeType.ALL)

you should have:

@ManyToOne

Second, you should use cascade on the parent side:

@OneToMany(mappedBy="company", cascade = CascadeType.ALL, orphanRemoval = true)

and

@OneToMany(mappedBy="proposal", cascade = CascadeType.ALL, orphanRemoval = true)

Third, you should use FetchType.LAZY for the @ManyToOne associations as they are EAGER by default and that's bad for performance.

So, use this instead:

@ManyToOne(fetch = FetchType.LAZY)