Joining Two Entities in Spring Data JPA

Question

I have two tables in a Mysql database: Department and Contact. I connected with my application in the apllication.properties file.

This is my Database:

pom.xml is as Follows:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo1</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo1</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

This is my contact class:

@Entity
@Table(name="contact")
public class Contact {

    @Id
    @Column(name="contact_id")
    private int Contact_id;

    @Column(name="emp_name")
    private String Emp_name;

    @Column(name="mobile")
    private String Mobile;


    @Column(name="landline_office")
    private String Landline_office;

    @Column(name="landline_res")
    private String Landline_res;

    @Column(name="fax")
    private String Fax;

    @Column(name="email")
    private String Email;

    @ManyToOne(cascade= {CascadeType.PERSIST,CascadeType.MERGE,
            CascadeType.DETACH,CascadeType.REFRESH})
    @JoinColumn(name="department_dept_id")
    private Department department;

... constructors and getters and setters

This is my department class:

@Entity
@Table(name="department")
public class Department {

    @Id
    @Column(name="dept_id")
    private int Dept_id;

    @Column(name="dept_name")
    private String Dept_name;

    @Column(name="order")
    private String Order;

    @Column(name="home")
    private int Home;

    @OneToMany(mappedBy="department",
            cascade= {CascadeType.PERSIST,CascadeType.MERGE,
                    CascadeType.DETACH,CascadeType.REFRESH})
    private List<Contact> contacts;

    public Department() {

    }

...getters and setters and constructors

I can display the first entity: Department in table using thymeleaf:

What I want to do is: Dynamically display all employees belonging to ICT when i click View button in row 1 and so for PWD.

I have uploaded the project in github: https://github.com/sammizodev/Jpa_two_tables

Answer 1

Here is a code review of the code you post:

Naming conventions: You should take a look at java naming conventions, class attributes should follow camel case syntax, use of underscore is disregard.

Above does not need to impact your database schema, as you can use @Column to do the mapping between your table field and class attribute, for instance:

@Id
@Column(name="dept_id")
private int id;

@Column(name="dept_name")
private String name;

@Column(name="dept_order")
private String Order;

Notice, order is a keyword in many database, so you may need to change it.

Restful Conventions: I would suggest you to take a look at restful API design, then you can understand how to structure your application to access certain resources.

According to conventions, you have one resource(department) and you would need these URI:

• URI: GET /department - /department/list.html - Render table of department
• URI: GET /department/{id} - /department/show.html - Render a department with details(contact table).

For instance, you have GET /departments_list to render your department list, you would need instead to change it to GET /departments and your template should be named list.html.

@GetMapping("/departments")
public String listDepartments(Model model) {
    List<Department> departments = departmentService.findAll();
    model.addAttribute("departments",departments);
    return "/departments/list"; // Your current thymeleaf template
}

Then you would need a GET /departments/{id} in order to render the department details including the list of contacts.

So on your department list template you should build the link like:

<a th:href="@{/home/contact/{departmentId}(departmentId=${tempDepartment.dept_id})}"
                class="btn btn-info btn-sm">View</a>

Notice, you need to provide the url like /home/contact/{departmentId} so tymeleaf can replace the id property, otherwise you will received as a parameter.

On your controller you need to update the mapping to the contacts to include the id as a path variable:

@GetMapping("/departments/{id}")
public String listContacts(@PathVariable("id") int theId, Model theModel) {
        Department department = departmentService.findById(theId);
        theModel.addAttribute("department",department);
        return "/departments/show";
}

If your Department class loads contacts eager, you could access the list in your front-end in a show.html template.

<tr th:each="contact : ${department.contacts}">     
    <td th:text="${contact.contact_id}" />  
    <td th:text="${contact.emp_name}" />    
    <td th:text="${contact.mobile}" />  
    <td th:text="${contact.landline_office}" />         
</tr>

Also, remember to wire the ContactService at your DemoController.

public DemoController(DepartmentService departmentService, ContactService contactService) {
        this.departmentService = departmentService;
        this.contactService = contactService;
    }