Custom generated column value with persistent sequence for mySQL in Java Springboot

Question

Given a table in a mySQL database looks like this with the following columns:

| id  | name | reference|

id is a big-int auto-generated when persisting
name is a varchar
reference is a varchar

I am currently using spring-boot with spring-jpa.

Is there a way when I am persisting this entity to have the reference column be auto-generated in a custom way only when it is null. For example:

INSERT INTO my_table(name,reference) VALUES('John', 'MREF-System-0940');

Will produce a result as follows

| id  | name | reference        |
| 1   | John | MREF-System-0940 |

But when I execute the following

INSERT INTO my_table(name) VALUES('John');

Will produce a result as follows (Where reference is based as 'REF-{name}-{sequence}' where sequence could be the id or a predefined persistent sequence)

| id  | name | reference  |
| 2   | John | REF-John-2 |

It should auto-generate the reference column reference when it detects that column is null when persisting the entity.

If possible could we append the generated id column at the end? If not, is there a feasible way of auto-generating a reference as such.

Meaning if we do

INSERT INTO my_table(name) VALUES('Bob');

It might produce a result as follows

| id  | name | reference   |
| 3   | Bob  | REF-Bob-001 |

@RafaelPizao for the id yes I am using database to auto increment. For the reference I am open to using java or database. I prefer java based as I can configure in future without redeployment — Leo
– Leo, Commented Nov 6, 2020 at 14:50

Oleksii Valuiskyi · Accepted Answer · 2020-11-06 21:11:13Z

1

To make JPA generate field value you should use myEntityRepository#save instead of the native modifying query.

@Entity
public class MyEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    String name;
    String reference;

    public MyEntity(String name) {
        this.name = name;
    }

    //getters, setters, toString, hashcode, equals

    @PostPersist
    public void setReferencePostPersist(){
        if(id != null) {
            reference = "REF-" + name + "-" + id;
        }
    }
}

then

@Autowired
MyEntityRepository repository;

public MyEntity saveNewMyEntity(String name) {
    MyEntity myEntity = new MyEntity(name);   
    return repository.save(myEntity);
}

edited Nov 6, 2020 at 21:11

answered Nov 6, 2020 at 14:54

Oleksii Valuiskyi

2,8711 gold badge10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Leo Over a year ago

Thanks this works perfectly. Just add a null check in the postPersist method and all good to go.

Collectives™ on Stack Overflow

Custom generated column value with persistent sequence for mySQL in Java Springboot

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related