Update query spring data JPA modifies all columns

Question

I'm trying to update table based on Id that user passes from UI.

Problem statement example : when user sends Id as 1 then table should be updated only one row with other few parameters like lastModifiedBy and deleted where as my code updates all rows in table,

orm.xml

<entity class="com.example.Domain" name="Domain">
    <table name="t_table"/>
    <attributes>
        <id name="id">
            <column name="ID" nullable="false"/>
            <generated-value strategy="SEQUENCE" generator="S_GEN"/>
            <sequence-generator name="S_GEN" sequence-name="S_GEN" allocation-size="1"/>
        </id>
        <basic name="deleted">
            <column name="DELETED" nullable="false" length="5"/>
        </basic>
        <basic name="lastModifiedBy">
            <column name="LAST_MODIFIED_BY" nullable="false" length="100"/>
        </basic>
    </attributes>

Entity class

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Domain {
    private Long id;
    private boolean deleted;
    private String lastModifiedBy;
}

JPA Repository:

    public interface JpaDomainRepository extends Repository<Domain, Long> {
        @Modifying
        @Query(value = "update Domain set DELETED = true, LAST_MODIFIED_BY = lastModifiedBy where id = id")
        int update(Domain domain);
}

Service Class

public void update(UpdateCommand command) {
    Domain domain = repository.findByIdAndDeletedFalse(command.getID());
    domain.setLastModifiedOn(command.getLastModifiedOn());
    domain.setLastModifiedBy(command.getLastModifiedBy());
    repository.update(domain);
}

If table is having multiple entries already and I'm trying to update first row then rest all rows are getting modified,

In-short notation my update query is always true for where clause like below,

Example for input 1, only one column where id = 1 should get modify but query is modifying rows 2, 3 and so on as well,

update Domain set DELETED = true, LAST_MODIFIED_BY = lastModifiedBy where 1= 1

Why is it behaving like so and what could be solution?

PS: I don't want to pass ID explicitly from service class since I id is already available in domain object.

*I have already having id in domain object.* 1. What is the point of passing a domain object that is not used: why not simply pass the ID? 2. Why are you writing a query to do the update rather than using standard ORM funtionality and here available to you via the inherited `save(entity)` method you would have available if you extended `CrudRepository` rather tahn `Repository`. — Alan Hay, Dec 04 '19 at 09:43
@AlanHay 1. point of having domain object is https://stackoverflow.com/questions/39741102/how-to-beautifully-update-a-jpa-entity-in-spring-data 2. I'm writing query since I'm using spring-data-jpa. — Praveen Kumar Mekala, Dec 04 '19 at 09:51
1. You are **not** writing a native query: that is a JPQL query. 2. *point of having domain object is .......* - I think you need to review some of the basic concepts of JPA. — Alan Hay, Dec 04 '19 at 09:52
@AlanHay isn't it good way of passing complete domain object instead of passing multiple parameters? I hope you understood my problem statement — Praveen Kumar Mekala, Dec 04 '19 at 10:01
It would be **if you actually used it in your update method**. But you don't.... ;( — Alan Hay, Dec 04 '19 at 10:03
You are not really understanding the point i am making: `int update(Domain domain); //irrelevant argument as not used delete it and your code works(fails) as before....` — Alan Hay, Dec 04 '19 at 10:14
where 1=1 smells fishy to me. Maybe you could try "update Domain d set DELETED = true, LAST_MODIFIED_BY = lastModifiedBy where d.id = id" — C. Weber, Dec 04 '19 at 10:31
Maybe this question helps you further https://stackoverflow.com/q/10802798/7634201 — C. Weber, Dec 04 '19 at 10:50
@C.Weber highly appreciated for the link you're provided, this is what exactly I was looking for — Praveen Kumar Mekala, Dec 04 '19 at 11:00

James Gawron · Answer 1 · 2019-12-04T22:51:04.400

0

where id = id will always be true, if that is the exact syntax you are using ... did you mean something else ?

Shouldn't parameters in the @Query be preceded by ? for indexed parameters and : for named parameters?

   @Query(value = "update Domain set DELETED = true, LAST_MODIFIED_BY = :lastModifiedBy where id = :id")

edited Dec 04 '19 at 22:51

answered Dec 04 '19 at 22:33

James Gawron

871
10
27

I was looking for SpEL solution and found in this URL https://stackoverflow.com/questions/10802798/spring-data-jpa-query-with-parameter-properties – Praveen Kumar Mekala Dec 05 '19 at 07:27

Update query spring data JPA modifies all columns

1 Answers1