Deleting a database row using an entity with an embedded ID

I am working on an app that is utilizing JPA, Rest, Hibernate, Spring Boot, and running Java for the back end.

I am calling to this method from another method, to blow away the record if someone deletes the Items associated with the parameter. I have logs that let me know the parameter ID is being passed in to the query, so the problem seems to stem from the query itself. When I test my code is Swagger, I just keep getting a "Data Was Not Found" message.

This is my first time attempting to delete a record that has an Embedded Id, and I just can't seem to get the query to work. What am I doing wrong?

Here is the delete method where I am my query in:

 @Override
 @Transactional
  public void deleteParameterById(Long parameterId) {
 
    Query query = entityManager
     .createQuery("DELETE FROM ParameterEntity pa WHERE pa.ParameterEntityId.parameterId = ?1");
     query.setParameter(1, parameterId)
     .executeUpdate();

 }

Here is the Embedded ID (ParameterEntityId.java):

@Embeddable
public class ParameterEntityId implements Serializable {

  @Column(name = "PARAMETER_ID")
  private Long parameterId;

  @Column(name = "PARAMETER_ATTRIBUTE_TYPE_ID")
  private Long parameterAttributeTypeId;

Here is My Entity File (ParameterEntity.java):

@Entity
@Table(name = "parameter")
public class ParameterEntity {

  @EmbeddedId
  private ParameterEntityId parameterEntityId;

  @Column(name = "VALUE")
  private String value;

I find a lot of examples using JPA repositories, but that it not how this app is configured, we are using entities.

I initially just tried to write a native query and call straight to the DB table Parameters and select the parameter_cd directly so I could bypass this embedded key, (the DB uses a composite key of parameter_id and parameter_attribute_type_id) but when I tested it in Swagger, I just got a "processing" message and the query would never finish running.

I have also ensured the app has the proper permissions to edit and delete from the table.

here is my original native query:

@Override
@Transactional
public void deleteParameterById(Long parameterId) {

      entityManager.createNativeQuery("DELETE FROM *dbname(redacted)*.parameter WHERE parameter_id = ?1")
      .setParameter(1, parameterId).executeUpdate();
  
  }

Solution

As already writtent in the comments by Chris, the query has to be fixed using the right case for the parameterEntityId attribute, so that you have:

Query query =
    entityManager.createQuery(
        "DELETE FROM ParameterEntity pa WHERE pa.parameterEntityId.parameterId = ?1");

Using your original query, this exception is thrown:

Caused by: org.hibernate.query.sqm.UnknownPathException: Could not resolve attribute 'ParameterEntityId' of 'it.so.entity.ParameterEntity' [DELETE FROM ParameterEntity pa WHERE pa.ParameterEntityId.parameterId = ?1]
    at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:88)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:145)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:132)
    at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:802)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:852)
    ... 49 more
Caused by: org.hibernate.query.sqm.PathElementException: Could not resolve attribute 'ParameterEntityId' of 'it.so.entity.ParameterEntity'

Using the right query, Hibernate translates the jpql into this sql:

delete from parameter pe1_0 where pe1_0.parameter_id=?

that matches your native one, which in a unit test execution runs immediately with no errors.

Since, as you said, this query runs smoothly in SQLDeveloper, I suspect that when this runs in the application there is something that may lock the record on the db that occurrs during the rest endpoint call, between the controller and the deleteParameterById method. I suggest to check if there is something that runs before this delete method that may create this lock.