CODEWITHSUNDEEP
javaspring-data-jpacomposite-keysequence-generators
DiegoAlfonso
DiegoAlfonso

Reputation: 257

Spring Data JPA - Entities with composite key in which one column is an internal sequence related to the other

I have a table like the following in an Oracle database:

| GROUP | SEQ | OTHER_DATA |
|-------|-----|------------|
| 1     | 1   | ~~~~~~~~~~ |
| 1     | 2   | ~~~~~~~~~~ |
| 1     | 3   | ~~~~~~~~~~ |
| 2     | 1   | ~~~~~~~~~~ |
| 2     | 2   | ~~~~~~~~~~ |
| 2     | 3   | ~~~~~~~~~~ |

where GROUP is a foreign key and (GROUP, SEQ) is the primary key. I want to insert the next record for group 1, that should be (1, <(MAX SEQ FOR GROUP 1) + 1>, <OTHER DATA>) = (1, 4, <OTHER DATA>).

I'm using Spring Boot 2. So far I have the following:

public class MyEntityPK implements Serializable {
    private Long group;
    private Long seq;
    // getters and setters ...
}

@Entity
@Table(name = "MY_TABLE")
@IdClass(MyEntityPK.class)
public class MyEntity {

    @Id
    private Long group;

    @Id
    private Long seq;

    @ManyToOne
    @JoinColumn(name="GROUP", insertable=false, updatable=false)
    private Group group;

    private String otherData;
    // getters and setters ...
}

public interface MyEntityRepository extends CrudRepository<MyEntity, MyEntityPK> {}

When I explicitly set the seq field, as in the following example, everything works.

@RunWith(SpringRunner.class)
@SpringBootTest
public class MyEntityTest {

    @Autowired
    MyEntityRepository repository;

    @Test
    public void testSaveMyEntityWithExplicitParameters() {
        MyEntity entity = new MyEntity();
        Group group = new Group(1L, "Group description");
        entity.setGroup(group);
        entity.setSeq(4L);
        repository.save(entity);
    }
}

But I can't figure out how to increment the sequence in the persistence layer, without adding logic to the service that uses MyEntityRepository to get the max seq value and explicitly set it. I can't use @GeneratedValue with a sequence generator because it generates the next available value without taking in account the group.

Upvotes: 0

Views: 297

Answers (1)

Oleksandr Abasov
Oleksandr Abasov

Reputation: 311

as a suggestion you can try a trigger, please find raw example:

CREATE OR REPLACE TRIGGER seq_upd_before_insert
BEFORE INSERT
   ON MY_TABLE
   FOR EACH ROW
DECLARE
   v_seq number;
BEGIN
   -- Find max seq for performing INSERT into your table
   SELECT max(seq) INTO v_seq 
   FROM MY_TABLE
   WHERE group = :new.group
   GROUP BY group;

   -- Update new seq field to max seq + 1 before insert
   :new.seq := v_seq + 1;
END;
/

Hope it helps

Upvotes: 1

Related Questions