JPQL issue updating records

Trying to update a record in a database but getting this error:

Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.7.4.v20190115-ad5b7c6b2a): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [UPDATE DATABASE.PROPOSAL p SET p.proposal_for = 10 WHERE p.proposal_id = 1]. 
[29, 43] The state field cannot be resolved.
[55, 68] The state field path 'p.proposal_id' cannot be resolved to a valid type.

Code below is responsible for inserting it

public int updateFor(int forCount, Long propId) {
        int newCount = forCount + 1;
        int propIdNew = propId.intValue();
        Query q = em.createQuery("UPDATE DATABASE.PROPOSAL p SET p.proposal_for = " + newCount + " WHERE p.proposal_id = "+ propIdNew );
        return q.executeUpdate();
    }

Im not sure if there is a type issue with propId being a Long or if i have written the SQL paths wrong or both.

Answer

You’re trying to execute an SQL query as JPQL.
There are a couple options to avoid the error in this situation:

#1 (easiest).
Use the createNativeQuery method

Query q = em.createNativeQuery("UPDATE DATABASE.PROPOSAL p SET p.proposal_for = " + newCount + " WHERE p.proposal_id = "+ propIdNew );

---

#2.
Make your query a JPQL. It’s the first steps to the bright side Spring partnership.

1. Create a Proposal class describing the PROPOSAL table and it’s fields
2. Create a ProposalRepository class
3. Make a small changes in your SQL query in order to adapt it to JPQL syntax
4. Set the changed query in @Query annotation on top of your new method in ProposalRepository
5. Call the method via autowired ProposalRepository instance

The result should be something like this:

@Modifying
@Query("update Proposal p set p.proposal_for = :newCount where p.proposal_id = :propIdNew ")
void update(@Param("newCount") int newCount, @Param("proposal_id") int propIdNew)

---

#3 (preferable).
Use the full power of the bright side Spring.
Update the entity via the org.springframework.data.repository.CrudRepository#save method.
In that case you still have to make steps 1 and 2 from option #2 – but end up with no manual JPQL solution required.

1. Create a Proposal class describing the PROPOSAL table and it’s fields
2. Create a ProposalRepository class, which extends JpaRepository or CrudRepository
3. Get the record you want to change by getting it through ProposalRepository. The common way of getting one is by using a findById method
4. Change it however you like
5. Use save method via ProposalRepository instance, set the changed Proposal object as argument
6. Let the Spring do it’s thing

    @Autowired
    private ProposalRepository proposalRepository;

    private void example(Long recordId) {
        Proposal proposal = proposalRepository.findById(recordId);
        proposal.setProposalFor(10);
        proposalRepository.save(proposal);
    }