How to get updated values from Spring JPA save with a table that has a trigger

Question

The issue I'm having is when I use Spring JPA to persist to a table with a trigger. The trigger populates a field with a default value if that field is NULL on INSERT.

CREATE TABLE Example (
    ID INTEGER IDENTITY PRIMARY KEY,
    col int
);

CREATE TRIGGER PUBLIC.TR_INSERT BEFORE INSERT ON PUBLIC.Example
    REFERENCING new ROW AS newrow FOR EACH ROW
BEGIN ATOMIC
  IF newrow.col is null THEN
  SET newrow.col = 1;
  END IF;
END

my test, which fails because the value in the entity is still NULL and not the updated value from the trigger. I'm guessing the save doesn't expect any values to be changing besides the ID.

@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
@Entity
@Table(name = "example")
public class Example {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Column(name = "col")
  private Integer col;
}

public interface ExampleRepository extends JpaRepository<Example, Long> {
}

@Transactional
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
class ExampleRepositoryIntTest {
  @Autowired
  ExampleRepository uut;

  @Test
  public void testNullIsChangedToDefault() {
    Example ex = Example.builder().col(null).build();
    Example saved_ex = uut.save(ex);
    assertEquals("1", saved.getCol()); // <-- Fails...returned value is "null"
  }
}

I've been able to persist the data correctly using the JdbcTemplate but that seems like a step backwards.

I'm using SpringBoot 2.4.5 libs and HSqlDB 2.6

score 0 · Answer 1 · answered Aug 05 '21 at 00:51

found the answer to my issue

Refresh and fetch an entity after save (JPA/Spring Data/Hibernate)

Short answer is, once you save, you need to get the updated values from the DB. JPA caches the select request so it doesn't make it to the DB. Calling the entityManager.refresh() does the trick.

How to get updated values from Spring JPA save with a table that has a trigger

1 Answers1