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

user_14026062 0
Viewed 281

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?

spring-boot java
2 Answers

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

user_91235580

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.

user_18038530
Related