How to Insert data in @OnetoOne Mapping in SpringBoot

Question

I am trying to learn SpringBoot and trying to make an API.

I have multiple entities as follows:

@Entity
public class Superhero {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;

  private String name;
}

And then another Entity is Superhero_stats - where I want to add additional fields and map it with heroId from SuperHero Entity.

    @Entity
    @Table(name = "superhero_stats")
    public class SuperheroStats {
    
      @Id
      @GeneratedValue
      private int stats_id;
    
      @OneToOne(mappedBy = "superhero", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
      Superhero superhero;
    
      private int intelligence;
}

My question is how to write the sql queries in data.sql script in resources. I have found many examples which are trying to do the same thing in main Application class, but I also want to know if this is possible through data.sql file.

Here is the sample data.sql that I am trying

insert into superhero(name) values ('Spiderman')
insert into superhero(name) values ('Superman')
insert into superhero(name) values ('Batman')


insert into superhero_stats(stats_id, superhero, intelligence) values (1,1,100)
insert into superhero_stats(stats_id, superhero, intelligence) values (2,2,200)
insert into superhero_stats(stats_id, superhero, intelligence) values (3,3,300)

Edit : I am trying this at start of my application. NOT for testing.

Not obvious from your question, but I assume you are using h2 in-memory database and/or a unit test to test this? See this answer, if so: https://stackoverflow.com/questions/60333899/insert-data-in-h2-database-through-data-sql-file-before-performing-unit-testing. Otherwise, just install and use a local database, mysql or mariadb, for example. — Geyser14, May 10 '21 at 18:10
@samoussa.usa Yes I am using h2. And this is not for unit test. It’s what I need at initial start of my application. — kukroid, May 10 '21 at 19:12

score 0 · Answer 1 · answered May 10 '21 at 19:45

0

To connect to the db and run your commands using the shell:

java -cp h2*.jar org.h2.tools.Shell

and copy your sql statements into the sql> prompt.

To run your script as is,

java -cp h2*.jar org.h2.tools.RunScript -url jdbc:h2:~/test -script data.sql

See the tutorial here: http://www.h2database.com/html/tutorial.html and this answer: https://stackoverflow.com/a/10679673/2735802.

answered May 10 '21 at 19:45

Geyser14

1,385
3
14
32

My question is more on lines of: What would be my 'data.sql' script. How can I insert superhero id superhero_stats entity when superhero id is autogenerated and 2nd point I am referring `Superhero Object` in superhero_stats, how can I insert that? – kukroid May 10 '21 at 20:12

score 0 · Answer 2 · answered May 13 '21 at 01:06

I had to change the way I defined my Entities. I changed as follows

Added this instance in my Superhero Entity instead of SuperheroStats entity

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
  @JoinColumn(name = "superhero_id")
  private List<SuperheroStats> superheroStats = new ArrayList<>();

and added superhero_id in SuperheroStats Entity

private int superhero_id;

and now my data.sql looks like this:

insert into superhero(name) values ('Spiderman')
insert into superhero(name) values ('Superman')
insert into superhero(name) values ('Batman')


insert into superhero_stats(stats_id, superhero_id, intelligence) values (1,1,100)
insert into superhero_stats(stats_id, superhero_id, intelligence) values (2,2,200)
insert into superhero_stats(stats_id, superhero_id, intelligence) values (3,3,300)

How to Insert data in @OnetoOne Mapping in SpringBoot

2 Answers2