Joing two tables in JPA repository

Question

Joing two tables in JPA repository

I am going throw spring boot tutorial and got this requriment

    @Entity
    @Table(name = "transiction")
    public class Transictions {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long Id;
        
        @Column(name = "userId")
        private Long userId;
        
        @Column(name = "productName")
        private String productName;
        
        @Column(name = "quantity")
        private int quantity;
        
        @Column(name = "price")
        private double price;
        
        @Column(name = "transictionDate")
        private Date transictionDate;
    
// Getter and Setter method with constructor.

My Second Model class

@Entity
@Table(name = "product")
public class product {
    
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    @Column(name = "product")
    private String productName;
    
    @Column(name = "ltp")
    private float LTP;
    
    @Column(name = "exchange")
    private String exchange;

In the same way I have created different controller and two differnt JPA repository to fetch the data from the each table and working fine.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import com.springboot.Ole.Model.Transictions;

public interface TransictionRepository extends JpaRepository<Transictions, Long>{
    
    
}

Model class for product

package com.springboot.Ole.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.springboot.Ole.Model.Product;

@Repository
public interface ProducttRepository extends JpaRepository<Instrument, Long>{

}

Now I got the secanrio where I need to join these two tables.

MySql command

SELECT transiction.user_id, transiction.quantity,transiction.product_name, transiction.Price,product.LTP             
FROM product
INNER JOIN transiction
ON product.product=transiction.product_name;

I can see propper data in sql. But I dont know how to write this query in Java code.

I am going throw this tutorial https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections but not getting much idea.

this is what I am trying in my repositry

package com.springboot.Ole.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import com.springboot.Ole.Model.Transictions;

public interface TransictionRepository extends JpaRepository<Transictions, Long>{
    
    @Query(value = "SELECT transiction.user_id, transiction.quantity,transiction.product_name, transiction.Price,product.LTP"
            + "FROM product"
            + "INNER JOIN transiction"
            + "ON product.product=transiction.product_name")
            
}

but no luck because i m not storing this into some list or maybe something else not sure.

I have question

1. Do I need to write service class or something like that.

Can anybody help me here.

Answer 1

If you want to use table join in spring jpa you have to use the relationship models that spring offers, which are the well-known one-to-one, one-to-many, and many-to-many. In spring data rest relationships explains the different kinds of joints that you can use in your project .

For example if you want a one-to-one relationship your code would be as follows:

@Entity
@Table(name = "transiction")
public class Transictions {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long Id;
    
    @Column(name = "userId")
    private Long userId;
    
    @OneToOne(mappedBy = "transiction")
    private Product product;
    
    @Column(name = "quantity")
    private int quantity;
    
    @Column(name = "price")
    private double price;
    
    @Column(name = "transictionDate")
    private Date transictionDate;
    
@Entity
@Table(name = "product")
public class Product {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    @Column(name = "product")
    private String productName;
    
    @Column(name = "ltp")
    private float LTP;
    
    @Column(name = "exchange")
    private String exchange;
    
    @OneToOne
    @JoinColumn(name = "transiction_id")
    @RestResource(path = "product-transiction", rel="transiction")
    private Transictions transiction;

Answer 2

So you are querying native queries, so you need to pass nativeQuery = true to query argument. Also, you need to add @Repository annotation to TransictionRepository interface. That is nothing but your Dao layer.

package com.overflow.overflow.service;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.overflow.overflow.models.Transictions;

@Repository
public interface TransictionRepository extends JpaRepository<Transictions, Long> {
    @Query(nativeQuery = true,
            value = "SELECT transiction.user_id, transiction.quantity,transiction.instrument_name, transiction.Price,instrument.LTP"
            + "FROM instrument"
            + "INNER JOIN transiction"
            + "ON instrument.instrument=transiction.instrument_name")
    public List<Object[]> getTransictionsAndInstruments();
}

Answer 3

To expand upon Mr_Thorynque's comment, if there is a FK relationship between the two entities, then you can define that in the entities, themselves.

Here's an example.

@Entity
    @Table(name = "transiction")
    public class Transiction {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long Id;
        
        @Column(name = "userId")
        private Long userId;
        
        @Column(name = "productName")
        private String productName;
        
        @Column(name = "quantity")
        private int quantity;
        
        @Column(name = "price")
        private double price;
        
        @Column(name = "transictionDate")
        private Date transictionDate;

        @ManyToOne(fetch = FetchType.EAGER)
        @JoinColumn(name = "product_id")
        private Product product;
    
...
}

@Entity
@Table(name = "product")
public class product {
    
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    @Column(name = "product")
    private String productName;
    
    @Column(name = "ltp")
    private float LTP;
    
    @Column(name = "exchange")
    private String exchange;

    @OneToMany(mappedBy = "product", fetch = FetchType.LAZY)
    private List<Transiction> transictions

...
}

I am making some assumptions here.

• a product has multiple transictions associated with it
• a transiction has a single product associated with it
• there is a strict PK/FK relationship between transiction and product

Anyway, given the above, if you fetch a Transiction then it will return a Transiction with its accompanying Product object populated.

BTW, if you like this answer, please also give an upvote to Mr_Thorynque's comment.