Where should I run custom SQL to create enum type in database in spring boot?

Question

I am new to Spring boot, and I have a model like this:

package com.example.resource.type.req;

import java.util.Objects;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Embeddable;
import javax.validation.constraints.NotBlank;
import javax.persistence.Column;

import com.example.resource.annotation.EnumNamePattern;

@Embeddable
public class TodoItem {
    public enum Status {
        completed,
        incomplete,
        removed;
    }
    @NotBlank(message = "Name required")
    public String name;
    
    @NotBlank(message = "Description required")
    public String description;
    
    @Column(columnDefinition = "status")
    @EnumNamePattern(regexp = "completed|incomplete|removed")
    public Status status;
}

And I want to to run the following statement to create a enum type called status in the database:

CREATE TYPE status as ENUM ('completed', 'incomplete', 'removed');

Should I use Session.createNativeQuery() to run this, and where should I place? I am not sure as I only want this SQL to run once. Is there a better alternative?

Otakoucht Mohamed · Accepted Answer · 2021-08-12 08:07:48Z

2

u should use @Query in your repository like this

@Query(value="CREATE TYPE status as ENUM ('completed', 'incomplete', 'removed')")
void createEnum();

and execute this function in your service

answered Aug 12, 2021 at 8:07

Otakoucht Mohamed

1242 silver badges19 bronze badges

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

3 Comments

John Winston Over a year ago

But this type is related with my database schema, shouldn't it be living with the model, not the service?

Davide Lorenzo MARINO Over a year ago

@JohnWinston it is a bad idea to insert in your code something that is not related to the behaviour of that code. So it is better to place it outside the project using a tool dedicated to this task. If you would like to have it in java creates a new project or at least dedicated classes in the existing project to handle the database setup.

Otakoucht Mohamed Over a year ago

@JohnWinston you can excute it in the constructor so each time you initialize this object this function will be executed automatically

Davide Lorenzo MARINO · Accepted Answer · 2021-08-12 08:36:00Z

1

To init / update the database it is better to use a database migration tool than manually run an init procedure written in java.

Spring boot can be for example configured to use FlyWay that is described in the section 85.5 of this link.

Here is how FlyWay (and generally database migration tools) works:

At startup it looks at a schema history table
If that table is not present it will create it
Then it looks at the last migration applied to the database
If any additional migrations need to be applied it execute those migrations
Update the schema history table if needed

The migrations are saved in standard sql files that will be runned by FlyWay when needed. In a spring boot project there are standards so place those files in a directory db/migration under the classpath (generally <PROJECT>/src/main/resources/db/migration). The files should be named V<number>__<anything>.sql, for example V1__initDb.sql or V2__addColumnToUsers.sql

There are many properties that you can use in your application.properties to handle flyway behaviour. THose properties are listed here.

answered Aug 12, 2021 at 8:36

Davide Lorenzo MARINO

27k4 gold badges45 silver badges58 bronze badges

2 Comments

John Winston Over a year ago

If I am already using an ORM like hibernate, should I create a mapping type instead? It seems like hibernate can do what Flyway can do already?

Davide Lorenzo MARINO Over a year ago

Hibernate can do a lot less than any db migration tool. At least there are tables not mapped as entities in java. There are also views functions or procedures that can be easily defined with flyway. You can also prepopulate a table. You can define special strategies to add or remove record or populate columns. Hibernate can do very basic migrations. It is an orm, not a db migration tool and every tool should be user for its job.

Collectives™ on Stack Overflow

Where should I run custom SQL to create enum type in database in spring boot?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related