JpaRepository findBy query (SELECT) is updating records in the DB

Viewed 624

Recently we came across an issue where records were getting updated in the DB where we were doing only read operation via java api. Below is the code snippet of our springboot application

public List<ResultDto> getSomethingByColumnA(String arg1, String arg2, String arg3)
   return myRepo.findSomethingByColumnA(param1, param2, param3, ResultDto.class)
                    .stream().map(v -> {
                String varErrRec = v.getErrorRecByerl();
                varErrRec = (varErrRec == null) ? "" : varErrRec;
                String erlTotalRec = v.getTotalRecByerl();
                erlTotalRec = (erlTotalRec == null) ? "" : erlTotalRec;
                String varErrRecByRule = v.getErrCountByerlRule();
                varErrRecByRule = (varErrRecByRule == null) ? "" : varErrRecByRule;
                Pattern glPattern = Pattern.compile(glName + ":(.*?),");
                Pattern glerlRulePattern = Pattern.compile(glName + "\\|(.*?)\\$");
                Matcher erlErrMatcher = glPattern.matcher(varErrRec);
                Matcher erlTotalMatcher = glPattern.matcher(erlTotalRec);
                Matcher errByRuleMatcher = glerlRulePattern.matcher(varErrRecByRule + "$");
                v.setTotalRec(0L);
                v.setErrorRec(0L);
                v.setErrByRule(null);
                while (erlErrMatcher.find()) {
                    v.setErrorRec(Long.valueOf(erlErrMatcher.group(1)));
                    break;
                }
                while (erlTotalMatcher.find()) {
                    v.setTotalRec(Long.valueOf(erlTotalMatcher.group(1)));
                    break;
                }
                while (errByRuleMatcher.find()) {
                    System.out.println(errByRuleMatcher.group(1));
                    v.setErrByRule(errByRuleMatcher.group(1));
                    break;
                }

                return v;
            }).collect(Collectors.toList());
}

myRepo code snippet for the findBy method

@Query("select s from tableA s, tableB t  where someCondition order by aColumn ASC")
    <T> Collection<T> findSomethingByColumnA(@Param("param1") String param1, @Param("param2")Date param2, @Param("param3")Date param3, Class<T> ctype);


@Projection(name="proj1", types= MyTable.class)
public interface ResultDto{
    
    Long getRunId();
    
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern ="dd MMM yyyy")
    Date getValidateDt();
    
    ..some more getters and setters
}

@Entity
@Table(name = "MY_TABLE_NAME")
@Data
public class MyTable implements Serializable {

@Id
    @Column(name="ID")
    private long id;
    
    .. some more fields
}

I added below properties to enable logs for debugging

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

log snippet below :

    2021-01-19 10:22:39.455 DEBUG 57901 --- [nio-9000-exec-4] org.hibernate.SQL                        : select ..query
    2021-01-19 10:22:39.455 TRACE 57901 --- [nio-9000-exec-4] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [CLOB] - [%textA%]
    2021-01-19 10:22:39.456 TRACE 57901 --- [nio-9000-exec-4] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [TIMESTAMP] - [Wed Jan 01 00:00:00 EST 2020]
    2021-01-19 10:22:39.457 TRACE 57901 --- [nio-9000-exec-4] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [TIMESTAMP] - [Tue Jan 19 00:00:00 EST 2021]
    2021-01-19 10:22:39.552 DEBUG 57901 --- [nio-9000-exec-4] org.hibernate.SQL                        : update MY_TABLE_NAME set ...query
    2021-01-19 10:22:39.553 TRACE 57901 --- [nio-9000-exec-4] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [0]
    2021-01-19 10:22:39.553 TRACE 57901 --- [nio-9000-exec-4] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [6901]
.
.
binding parameter [16]

As you can see UPDATE statements are getting executed right after the SELECT statement ! Strange thing is It's happening on the servers (dev,stage and prod). However when I took the same code in STS (eclipse) pointed it to dev/stage/prod properties (spring profiles) and tested in my sytem this issue is not happening ! What might be causing execution of update statements ?

UPDATE : SELECT statemnt fetches 16 columns of MY_TABLE_NAME and all those columns are getting updated with UPDATE query

UPDATE query will execute as many times as result of SELECT query. Say SELECT query returns 10 rows, UPDATE Query will get executed 10 times to update all those 10 rows !

Is it because entity is being modified in .stream().map(v) ?

v.setTotalRec(Long.valueOf(0));
v.setErrorRec(Long.valueOf(0));
v.setErrByRule(null);
1 Answers

Is it because entity is being modified in .stream().map(v)?

Yes, this is arguably the defining feature of JPA: You load entities, you modify them and at the end of the transaction (actually the session but that's mostly the same) JPA gathers all modifications from managed entities and updates their states in the database.

Related