Data SQL
Spring Boot ⭔ Material Two
SQL Relationships
SQL databases store the information of their entities in tables.
Clustering entities presented the possibility to reuse information by creating references.
When an entity references another to link their information to theirs, a relationship is
created.
Because entities are related to each others, SQL databases are called relational
databases.
Considering two entities (A and B), from A’s perspective there are four kinds of SQL
relationships:
⭔ One to one -> one single entity of A can only be related to one single entity of B
⭔ One to many -> one single entity of A can be related to several entities of B
⭔ Many to one -> many entities of A can be related to one single entity of B
⭔ Many to many -> many entities of A can be related to many entities of B
One to one
@Entity @Entity
public class Person { public class Toothbrush {
@Id @Id
@GeneratedValue @GeneratedValue
private Long id; private Long id;
private String nickname; private String color;
@OneToOne (constructors, getters & setters)
private Toothbrush toothbrush; }
(constructors, getters & setters)
}
@Entity ⭔ Write @OnetoOne over the property that
public class Person{ references another entity
⭔ When reading this class, we only focus on
@Id
the relationship from this class's
@GeneratedValue
private Long id; perspective
⭔ One Person can have only one Toothbrush
private String nickname; ⭔ We need to save the Toothbrush in the
database before linking it to the Person,
@OneToOne
because we need its database id
private Toothbrush toothbrush;
(constructors, getters & setters) ⭔ In the Person's table, only the Toothbrush id
} is saved
One to many
@Entity @Entity
public class Musician { public class Song {
@Id @Id
@GeneratedValue @GeneratedValue
private Long id; private Long id;
private String name; private String name;
@OneToMany(fetch = [Link]) (constructors, getters & setters)
private List<Song> songs; }
(constructors, getters & setters)
}
@Entity ⭔ Write @OnetoMany over the property that
public class Musician { references other entities
⭔ When reading this class, we only focus on
@Id
the relationship from this class's
@GeneratedValue
private Long id; perspective
⭔ One Musician can have many Songs
private String name; ⭔ We need to save each Song in the
database before linking it to the Musician,
@OneToMany(fetch = [Link])
because we need its database id
private List<Song> songs;
⭔ Nothing is saved in the in the Musician’s
table, instead, the musician_songs table is
(constructors, getters & setters)
} created with the connected Musician ids
and Song ids
Many to one
@Entity @Entity
public class CoWorker { public class Desk {
@Id @Id
@GeneratedValue @GeneratedValue
private Long id; private Long id;
private String name; private String name;
@ManyToOne (constructors, getters & setters)
private Desk desk; }
(constructors, getters & setters)
}
@Entity ⭔ Write @ManyToOne over the property that
public class CoWorker { references the other entity
⭔ When reading this class, we only focus on
@Id
the relationship from this class's
@GeneratedValue
private Long id; perspective
⭔ Many CoWorkers can have one Desk
private String name; ⭔ We need to save the Desk in the database
before linking it to the CoWorker, because
@ManyToOne
we need its database id
private Desk desk;
⭔ In the CoWorker’s table, only the Desk id is
saved
(constructors, getters & setters)
}
Many to many
@Entity @Entity
public class Museum { public class Visitor {
@Id @Id
@GeneratedValue @GeneratedValue
private Long id; private Long id;
private String topic; private int age;
@ManyToMany(fetch = [Link]) (constructors, getters & setters)
private List<Visitor> visitors; }
(constructors, getters & setters)
}
@Entity ⭔ Write @ManytoMany over the property
public class Museum { that references other entities
⭔ When reading this class, we only focus on
@Id
the relationship from this class's
@GeneratedValue
private Long id; perspective
⭔ Many Museums can have many Visitors
private String topic; ⭔ We need to save each Visitor in the
database before linking it to the Museum,
@ManyToMany(fetch = [Link])
because we need its database id
private List<Visitor> visitors;
⭔ Nothing is saved in the in the Museum’s
(constructors, getters & setters) table, instead, the museum_visitors table is
} created with the connected Museum ids
and Visitor ids
Method name - same as with MongoRepository
public interface PersonRepository public interface PersonRepository
extends extends JpaRepository<Person,
MongoRepository<Person, String> { Long> {
Optional<Person> findByName(String name); Optional<Person> findByName(String name);
Optional<Person> Optional<Person>
findOneByNameIgnoreCase(String name); findOneByNameIgnoreCase(String name);
Optional<Person> findByNameAndAge(String Optional<Person> findByNameAndAge(String
name, int age); name, int age);
List<Person> findByAgeIsGreaterThan(int List<Person> findByAgeIsGreaterThan(int
minAge); minAge);
Set<Person> findByAgeBetween(int start, Set<Person> findByAgeBetween(int start, int
int end); end);
Stream<Person> findByOrderByNameAsc(); Stream<Person> findByOrderByNameAsc();
} }
Examples
Example 1 | The SQL one to one relationship
[Link]
spring:
datasource:
url: jdbc:mysql://localhost/data-sql-material?useSSL=false
username: root
password: root
jpa:
hibernate:
ddl-auto: update
[Link]
package [Link];
import [Link];
import [Link];
import [Link];
import [Link];
@Entity
public class Teenager {
@Id
@GeneratedValue
private Long id;
private String name;
@OneToOne
private CellPhone cellPhone;
public Teenager() {
}
public Teenager(String name, CellPhone cellPhone) {
[Link] = name;
[Link] = cellPhone;
}
@Override
public String toString() {
return "Teenager{" +
"id=" + id +
", name='" + name + '\'' +
", cellPhone=" + cellPhone +
'}';
}
}
[Link]
package [Link];
import [Link];
import [Link];
import [Link];
@Entity
public class CellPhone {
@Id
@GeneratedValue
private Long id;
private String name;
public CellPhone() {
}
public CellPhone(String name) {
[Link] = name;
}
@Override
public String toString() {
return "CellPhone{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
'}';
}
}
[Link]
package [Link];
import [Link];
import [Link];
public interface TeenagerRepository extends JpaRepository<Teenager, Long> {
}
[Link]
package [Link];
import [Link];
import [Link];
public interface CellPhoneRepository extends JpaRepository<CellPhone, Long> {
}
[Link]
package [Link];
import [Link];
import [Link];
import
[Link]
tory;
import
[Link]
ory;
import [Link];
import [Link];
import [Link];
@Configuration
public class TeenagerRunner {
@Bean
ApplicationRunner runTeenagers(TeenagerRepository teenagerRepository,
CellPhoneRepository cellPhoneRepository) {
return args -> {
[Link]();
[Link]();
CellPhone cellPhone1 = new CellPhone("AweSomeSung x15");
[Link](cellPhone1);
Teenager teenager1 = new Teenager("Leela", cellPhone1);
[Link](teenager1);
CellPhone cellPhone2 = new CellPhone("yPhone 3");
[Link](cellPhone2);
Teenager teenager2 = new Teenager("Rob", cellPhone2);
[Link](teenager2);
// how the relationship would change if Rob and Aron are sharing the cellPhone?
Teenager teenager3 = new Teenager("Aron", cellPhone2);
[Link](teenager3);
[Link]("### Cell phones ###");
[Link]().forEach([Link]::println);
[Link]("### Teenagers ###");
[Link]().forEach([Link]::println);
};
}
}
Example 2 | The SQL one to many relationship
[Link]
spring:
datasource:
url: jdbc:mysql://localhost/data-sql-material?useSSL=false
username: root
password: root
jpa:
hibernate:
ddl-auto: update
[Link]
package [Link];
import [Link].*;
import [Link];
@Entity
public class Person {
@Id
@GeneratedValue
private Long id;
private String name;
@OneToMany(fetch = [Link])
private List<Underwear> underwears;
public Person() {
}
public Person(String name, List<Underwear> underwears) {
[Link] = name;
[Link] = underwears;
}
@Override
public String toString() {
return "Person{" +
"id=" + id +
", name='" + name + '\'' +
", underwears=" + underwears +
'}';
}
}
[Link]
package [Link];
import [Link];
import [Link];
import [Link];
@Entity
public class Underwear {
@Id
@GeneratedValue
private Long id;
private String color;
public Underwear() {
}
public Underwear(String color) {
[Link] = color;
}
@Override
public String toString() {
return "Underwear{" +
"id=" + id +
", color='" + color + '\'' +
'}';
}
}
[Link]
package [Link];
import [Link];
import [Link];
public interface PersonRepository extends JpaRepository<Person, Long> {
}
[Link]
package [Link];
import [Link];
import [Link];
public interface UnderwearRepository extends JpaRepository<Underwear, Long> {
}
[Link]
package [Link];
import [Link];
import [Link];
import
[Link]
y;
import
[Link]
tory;
import [Link];
import [Link];
import [Link];
import [Link];
@Configuration
public class PersonRunner {
@Bean
ApplicationRunner runPersons(PersonRepository personRepository,
UnderwearRepository underwearRepository) {
return args -> {
[Link]();
[Link]();
Underwear underwear1 = new Underwear("blue");
Underwear underwear2 = new Underwear("black");
Underwear underwear3 = new Underwear("white");
[Link](underwear1);
[Link](underwear2);
[Link](underwear3);
Person person1 = new Person("Samantha", [Link](underwear1, underwear2));
[Link](person1);
// Samantha and Richard cannot share the same underwear1
// because the relationship is one-to-many
// on one side, one person can have many different underwears
// but on the other side, one underwear can only belong to one person
/*
Person person2 = new Person("Richard", [Link](underwear1, underwear3));
[Link](person2);
*/
[Link]("### Underwears ###");
[Link]().forEach([Link]::println);
[Link]("### Persons ###");
[Link]().forEach([Link]::println);
};
}
}
Example 3 | The SQL many to one relationship
[Link]
spring:
datasource:
url: jdbc:mysql://localhost/data-sql-material?useSSL=false
username: root
password: root
jpa:
hibernate:
ddl-auto: update
[Link]
package [Link];
import [Link];
import [Link];
import [Link];
import [Link];
@Entity
public class Child {
@Id
@GeneratedValue
private Long id;
private String name;
@ManyToOne
private Parent parent;
public Child() {
}
public Child(String name, Parent parent) {
[Link] = name;
[Link] = parent;
}
@Override
public String toString() {
return "Child{" +
"id=" + id +
", name='" + name + '\'' +
", parent=" + parent +
'}';
}
}
[Link]
package [Link];
import [Link];
import [Link];
import [Link];
@Entity
public class Parent {
@Id
@GeneratedValue
private Long id;
private String name;
public Parent() {
}
public Parent(String name) {
[Link] = name;
}
@Override
public String toString() {
return "Parent{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
[Link]
package [Link];
import [Link];
import [Link];
public interface ChildRepository extends JpaRepository<Child, Long> {
}
[Link]
package [Link];
import [Link];
import [Link];
public interface ParentRepository extends JpaRepository<Parent, Long> {
}
[Link]
package [Link];
import [Link];
import [Link];
import
[Link]
;
import
[Link]
y;
import [Link];
import [Link];
import [Link];
@Configuration
public class ChildRunner {
@Bean
ApplicationRunner runChildren(ChildRepository childRepository,
ParentRepository parentRepository) {
return args -> {
[Link]();
[Link]();
Parent parent = new Parent("Tamara");
[Link](parent);
Child child1 = new Child("Lucia", parent);
[Link](child1);
// Lucia and Manuel share the same parent without duplicating the information
Child child2 = new Child("Manuel", parent);
[Link](child2);
[Link]("### Parents ###");
[Link]().forEach([Link]::println);
[Link]("### Children ###");
[Link]().forEach([Link]::println);
};
}
}
Example 4 | The SQL many to many relationship
[Link]
spring:
datasource:
url: jdbc:mysql://localhost/data-sql-material?useSSL=false
username: root
password: root
jpa:
hibernate:
ddl-auto: update
[Link]
package [Link];
import [Link].*;
import [Link];
@Entity
public class ComicFan {
@Id
@GeneratedValue
private Long id;
private String name;
@ManyToMany(fetch = [Link])
private List<Comic> comics;
public ComicFan() {
}
public ComicFan(String name, List<Comic> comics) {
[Link] = name;
[Link] = comics;
}
@Override
public String toString() {
return "ComicFan{" +
"id=" + id +
", name='" + name + '\'' +
", comics=" + comics +
'}';
}
}
[Link]
package [Link];
import [Link];
import [Link];
import [Link];
@Entity
public class Comic {
@Id
@GeneratedValue
private Long id;
private String name;
public Comic() {
}
public Comic(String name) {
[Link] = name;
}
@Override
public String toString() {
return "Comic{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
[Link]
package [Link];
import [Link];
import [Link];
public interface ComicFanRepository extends JpaRepository<ComicFan, Long> {
}
[Link]
package [Link];
import [Link];
import [Link];
public interface ComicRepository extends JpaRepository<Comic, Long> {
}
[Link]
package [Link];
import [Link];
import [Link];
import
[Link]
ory;
import
[Link]
;
import [Link];
import [Link];
import [Link];
import [Link];
@Configuration
public class ComicFanRunner {
@Bean
ApplicationRunner runComicFans(ComicFanRepository comicFanRepository,
ComicRepository comicRepository) {
return args -> {
[Link]();
[Link]();
Comic comic1 = new Comic("Spider-man");
Comic comic2 = new Comic("Batman");
[Link](comic1);
[Link](comic2);
ComicFan comicFan1 = new ComicFan("Seth", [Link](comic1, comic2));
[Link](comicFan1);
// Seth and Linda have both exactly the same comics without duplicating the
information
ComicFan comicFan2 = new ComicFan("Linda", [Link](comic1, comic2));
[Link](comicFan2);
[Link]("### Comics ###");
[Link]().forEach([Link]::println);
[Link]("### Comic Fans ###");
[Link]().forEach([Link]::println);
};
}
}