Java multiple join query

Question

I am trying to create a REST service in Java with Spring Boot.

I have 2 tables Topic and Course and i want to retrieve NAME and DESCRIPTION from Topic and PRICE from Course. The connection between these 2 tables is made with TOPIC_ID from Course.

Result MUST be a JSON.

[
{
"id": "course1",
"name": "name course1",
"description": "course1"
},
{
"id": "course2",
"name": "course2 name",
"description": "course2"
},
{
"id": "course3",
"name": "course3 name",
"description": "course3"
}
]

The query is below. I know it's possible with DTO and JPA but I need to write a lot of code for a simple database query.

Thank you.

package com.example.course;
import com.example.topic.Topic;
import javax.persistence.*;
@Entity
@Table(name = "topic", schema = "topic")
public class Topic {
    @Id
    private String id;
    private String name;
    private String description;

    public Topic() {

    }

    public Topic(String id, String name, String description) {
        this.id = id;
        this.name = name;
        this.description = description;
    }

    public String getDescription() {
        return description;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

package com.example.topic;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "course", schema = "topic")
public class Course {
    @Id
    @Column(name = "ID")
    private String id;
    @Column(name = "NAME")
    private String name;
    @Column(name = "DESCRIPTION")
    private String description;
    @Column(name="PRICE")
    private Integer price;

    @ManyToOne
    @JoinColumn(name="TOPIC_ID", nullable=false)
    private Topic topic;

    public Course() {
    }

    public Course(String id, String name, String description, String topicId) {
        this.id = id;
        this.name = name;
        this.description = description;
        this.topic = new Topic(topicId, "", "");
    }

    public String getDescription() {
        return description;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Topic getTopic() {
        return topic;
    }

    public void setTopic(Topic topic) {
        this.topic = topic;
    }

    public void setPrice(Integer price) { this.price = price; }

    public Integer getPrice() { return price; }
}

package com.example.dto;
public class TopicDescDTO {
    private String id;
    private String name;
    private String description;

    public TopicDescDTO(String id, String name, String description) {
        this.id = id;
        this.name = name;
        this.description = description;
    }

    public TopicDescDTO() {
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

select t.name, t.description, c.price as course_price
from topic.course c 
inner join topic.topic t on t.id = c.topic_id

Maciej Kowalski · Accepted Answer · 2019-08-09 07:27:43Z

1

Take advantage of the projection interface offered by Spring Data JPA:

public interface TopicDescDTO{

    Integer getPrice();
    String getName();
    String getDescription();

}

and then just land a proper method in one of your repositories:

@Query(select t.name, t.description, c.price
       from Course c 
         inner join c.topic t)
List<TopicDescDTO> getTopicDescs();

Spring will do the mapping for you.

answered Aug 9, 2019 at 7:27

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nipuro Over a year ago

Thank you, this is better, but i'm curious if there is a simpler way to do this. Without a new interface and with native SQL.

Maciej Kowalski Over a year ago

Why would you need native query for such a simple task. Use JPA where possible and be database independent

nipuro Over a year ago

This is just a simple example with only 2 tables. In my project i have much more table joins.

Collectives™ on Stack Overflow

Java multiple join query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related