Spring JPA - how to set data.sql to add data if not exist, ignore if exists

Question

Hi i am creaeting rest api spring boot jpa & mysql. i want this to be automated as possible so i drop the database and schema and want spring to create all for me. i am adding data into mysql using data.sql. When i start spring boot, i 1 2 it to add into the table (which spring jpa help to create).

Tis is from my application.properties

spring.jpa.show-sql=true
spring.h2.console.enabled=true
spring.datasource.url=jdbc:mysql://localhost:3306/mockashop
spring.datasource.username=root
spring.datasource.password=root
spring.data.jpa.repositories.bootstrap-mode=default
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.properties.hibernate.format.sql=true
spring.jpa.hibernate.ddl-auto=update
spring.sql.init.mode=always

here is my data.sql:

INSERT INTO `mockashop`.`user` (`user_id`, `user_role`, `user_contact`, `user_email`, `user_name`, `user_pswd`, `user_wallet`) VALUES ('20003', 'ROLE_USER', '+6592212152', 'shah3@gmail.com', 'shah3', 'shahshah', '777.55');

model:

@Entity
@Data
@SequenceGenerator(name="seq2", initialValue=2000, allocationSize=1)
public class User  { 

  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq2")
  @Id
  private int user_id;
 
  @NotNull(message = "Name cannot be blank")
  @Size(min=5, message = "Name must contain atleast 5 character")
  private String user_name;

  @NotNull(message = "Email cannot be blank")
  @Email(message = "Enter valid email")
  @Size(min=5, message = "Email must contain atleast 5 character")
  private String user_email;
 
  @Size(min=5, message = "Password must contain atleast 8 character")
  @NotNull(message = "Password cannot be blank")
  private String user_pswd;

  @Size(min=5, message = "Contact must contain atleast 8 character")
  @NotNull(message = "Password cannot be blank")
  private String user_contact;

  @NotNull(message = "Wallet cannot be blank")
  private BigDecimal user_wallet;

  private String USER_ROLE;

}

1ST PROB

spring hibernate will create the table for me if i create the schema first else it will give me error:

Caused by: java.sql.SQLSyntaxErrorException: Unknown database 'mockashop'

How do i resolve this?

2ND PROB

so i create the schema, then start spring, which give me this error:

Caused by: java.sql.SQLSyntaxErrorException: Table 'mockashop.user' doesn't exist

it will go away until i remove data.sql. now spring boot is finally running.

3RD PROB

mysql has the table created for me but no data. so i add back the data.sql. restart spring. now data is added.

4TH PROB

i do other changes and restart spring it gives me this error:

Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '20001' for key 'user.PRIMARY'

i have to remove data.sql then the error is gone.

is there a way to resolve all this ?

Answer 1

1st

While h2 will create the schema for you automatically, MySQL won't automatically create the database for you. You have to do it manually.

Cant create a mysql database using Spring Boot

2nd and 3rd

If you are just adding just one line in mockashop.user, you can try adding the data using @EventListner

You can then remove the data.sql file

@Component
public class Data {
    @Autowired
    UserRepository userRepository;

    @EventListener
    public void appReady(ApplicationReadyEvent event){
        rolesRepository.save(new User('20003', 'ROLE_USER', '+6592212152', 'shah3@gmail.com', 'shah3', 'shahshah', '777.55'));
    }
}

4th

That is because every time you run this application the spring tries to add the same value over and over again and the user_id would have a UNIQUE constraint. That's why you are getting that error.

I believe the solution in the 2nd and 3rd point would also solve this problem as the save method of JPA would update the values in the database if the primary key value that is being passed is already present in the database.