Spring Boot + Spring Data JPA About multiple table joins

I had a hard time reading reference books such as INNER JOIN and LEFT JOIN using Spring, so I will write it as a memorandum.

What I want to achieve this time

The room name and equipment name are output on the screen.

Room table

Column name Data type
room number room_id INT
Room name room_name VARCHAR

Equipment table

Column name Data type
Equipment number equipmentId_id INT
Equipment name equipment_name VARCHAR
room number room_id INT

Create Entity (Room and Equipment). import is omitted.

Room.java


@Entity
public class Room implements Serializable {

	@Id
	@GeneratedValue
	private Integer roomId;

	private String roomName;

	@OneToMany(mappedBy="room", cascade=CascadeType.ALL)
	private List<Equipment> equipments;

	public Integer getRoomId() {
		return roomId;
	}

	public void setRoomId(Integer roomId) {
		this.roomId = roomId;
	}

	public String getRoomName() {
		return roomName;
	}

	public void setRoomName(String roomName) {
		this.roomName = roomName;
	}

	public List<Equipment> getEquipments() {
		return equipments;
	}

	public void setEquipments(List<Equipment> equipments) {
		this.equipments = equipments;
	}
}

Since there is a one-to-many relationship with Equipment, use @OneToMany.

@OneToMany(mappedBy="room", cascade=CascadeType.ALL)
private List<Equipment> equipments;

Equipment.java


@Entity public class Equipment implements Serializable {

	@Id
	@GeneratedValue
	private Integer equipmentId;

	private String equipmentName;


	@ManyToOne
	@JoinColumn(name="room_id")
	private Room room;

	public Integer getEquipmentId() {
		return equipmentId;
	}

	public void setEquipmentId(Integer equipmentId) {
		this.equipmentId = equipmentId;
	}

	public String getEquipmentName() {
		return equipmentName;
	}

	public void setEquipmentName(String equipmentName) {
		this.equipmentName = equipmentName;
	}

	public Room getRoom() {
		return room;
	}

	public void setRoom(Room room) {
		this.room = room;
	}
}

Use @ManyToOne because it has a many-to-one relationship with the Room. Use room_id in @JoinColumn as a foreign key.

@ManyToOne
@JoinColumn(name="room_id")
private Room room;

Create a query using @Query in the Repository. You can set the value dynamically with @Param ("roomId").

EquipmentRepository.java



public interface EquipmentRepository extends JpaRepository<Equipment, Integer> {

	@Query("SELECT DISTINCT e FROM Equipment e INNER JOIN e.room WHERE e.room.roomId = :roomId ORDER BY e.equipmentId")
	List<Equipment> find(@Param("roomId") Integer roomId);
}

Inject Equipment Repository with @Autowired. Annotation to get the value of the path variable in the URL with @PathVariable ("id").

TestController.java


@Controller
public class TestController {


	@Autowired
	EquipmentRepository equipmentRepository;

	@GetMapping("/{id}")
	public String index(@PathVariable("id")Integer id, Model model) {

		List<Equipment> list2 =equipmentRepository.find(id);
		model.addAttribute("lists2", list2);


		return "index";
	}
}

HTML elements are repeatedly output for the number of array values specified by th: each.

index.html


<!DOCTYPE html>
<html  xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<table>
		<tr>
			<th>room</th>
			<th>Equipment</th>
		</tr>
		<tr th:each="list2:${lists2}">
			<td th:text="${list2.room.roomName}">
			<td th:text="${list2.equipmentName}">

		</tr>
	</table>
</body>
</html>

With the above, the room name and equipment name can be output. In the future, I would like to increase the number of tables and challenge complicated processing.

Recommended Posts

Spring Boot + Spring Data JPA About multiple table joins
[spring] Let's use Spring Data JPA
Flow until output table data to view with Spring Boot
[How to install Spring Data Jpa]
Spring Data JPA SQL log output
See the behavior of entity update with Spring Boot + Spring Data JPA
OR search with Spring Data Jpa Specification
About Spring ③
Exists using Specification in Spring Data JPA
Implementation method for multi-data source with Spring boot (Mybatis and Spring Data JPA)
Sample code for DB control by declarative transaction in Spring Boot + Spring Data JPA
About designing Spring Boot and unit test environment
Summary of what I learned about Spring Boot
Spring Data JPA save select-insert is only insert
Various correspondence table of Spring Framework and Spring Boot
Spring Data JPA Entity cross-reference and its notes
An introduction to Spring Boot + in-memory data grid
Sort by Spring Data JPA (with compound key sort)
Creating a common repository with Spring Data JPA
How to define multiple orm.xml in Spring4, JPA2.1
[Spring Batch] Output table data to CSV file
Challenge Spring Boot
About spring AOP
Spring Boot Form
Spring Boot Memorandum
gae + spring boot
Check the behavior of getOne, findById, and query methods in Spring Boot + Spring Data JPA
Testing JPA entities and repositories using Spring Boot @DataJpaTest
I tried to get started with Spring Data JPA
[Spring boot] I thought about testable code by DI
Until the use of Spring Data and JPA Part 2
Try Dependency Inversion Principle with Multiple Spring Boot Projects
Until the use of Spring Data and JPA Part 1
Make the where clause variable in Spring Data JPA
A memorandum about table data types and commands (Rails)
How to use the same Mapper class in multiple data sources with Spring Boot + MyBatis