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.
The room name and equipment name are output on the screen.
| Column name | Data type | |
|---|---|---|
| room number | room_id | INT |
| Room name | room_name | VARCHAR |
| 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