Get from VIEW in Spring boot

user_6214375 0
Viewed 56

I am beginner with Spring Boot and trying to improve my skills to get new job, so I hope you help me even if the question maybe easy for you as I search a lot and gain nothing.

I need to get by id, but return data is duplicated with only one record, I will show you what I do and the result for more explanation.

In DB side: I have VW_Prices view in DB and it's data as shown below: enter image description here

In Spring Boot side:

VW_Prices class is :


import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Immutable;

@Entity
@Table(name = "VW_PRICES")
public class VW_Prices implements Serializable {

    private long dealId;
    private Long quotationId;
    private Long productPriceForEjada;
    private Long productPriceForClient;
    private Long productId;
    private Long productQuantity;
    private String productName;

    @Id
    @Column(name = "ID")
    public long getDealId() {
        return dealId;
    }

    public void setDealId(long dealId) {
        this.dealId = dealId;
    }
    
    
    @Column(name = "PRODUCT_QUANTITY")
    public Long getProductQuantity() {
        return productQuantity;
    }

    public void setProductQuantity(Long productQuantity) {
        this.productQuantity = productQuantity;
    }
    
    @Column(name = "PRODUCT_NAME")
    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    @Column(name = "PRODUCT_PRICE_FOR_EJADA")
    public Long getProductPriceForEjada() {
        return productPriceForEjada;
    }

    public void setProductPriceForEjada(Long productPriceForEjada) {
        this.productPriceForEjada = productPriceForEjada;
    }
    @Column(name = "PRODUCT_PRICE_FOR_CLIENT")
    public Long getProductPriceForClient() {
        return productPriceForClient;
    }

    public void setProductPriceForClient(Long productPriceForClient) {
        this.productPriceForClient = productPriceForClient;
    }
    @Column(name = "PRODUCT_ID")
    public Long getProductId() {
        return productId;
    }

    public void setProductId(Long productId) {
        this.productId = productId;
    }
    
    @Column(name = "QUOTATION_ID")
    public Long getQuotationId() {
        return quotationId;
    }

    public void setQuotationId(Long quotationId) {
        
        this.quotationId = quotationId;
    }

}

and I create VW_PricesRepository 


import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import springboot.deals_tracker_system.models.VW_Prices;
import springboot.deals_tracker_system.models.VW_Prices_interface;

public interface VW_PricesRepository extends JpaRepository<VW_Prices, Long> {
   
   
   
   @Query( nativeQuery = true,value = "SELECT distinct * from VW_Prices v where v.id = :dealID "  )
   List<VW_Prices> findByDealId( @Param("dealID") Long id);
}

and my in my Service 

public List<VW_Prices> findByDealId(Long dealId) {
        System.out.println("we are in service");
        
        List<VW_Prices> variableForDebug = VW_pricesRepository.findByDealId(dealId);
        for (VW_Prices vw_Prices : variableForDebug) {
            System.out.println(vw_Prices.getDealId() + " " + vw_Prices.getProductName());
        }
        return variableForDebug;
        //return VW_pricesRepository.findByDealId(dealId);
    }

When I pass dealId = 39 the result comes duplicated and not correct as in below:

enter image description here

how can I get correct data??

The view is made for Quotation Product Table to get product name. enter image description here

spring-boot hibernate
2 Answers

i think the problem is the id annotation you must add GeneratedValue

fro the class:

@Entity
@Table(name = "VW_PRICES")
public class VW_Prices implements Serializable {

@Id
@GeneratedValue (strategy = GenerationType.IDENTITY)
private long dealId;
private Long quotationId;
private Long productPriceForEjada;
private Long productPriceForClient;
private Long productId;
private Long productQuantity;
private String productName;


//code..

}

You dont have to use JPQL for this type of queries it's already exist in jpa: VW_PricesRepository:

public interface VW_PricesRepository extends JpaRepository<VW_Prices, Long> {

}

to get data by id use findById like that:

public VW_Prices findByDealId(Long dealId) {
    System.out.println("we are in service");
    
    VW_Prices vw_Prices = VW_pricesRepository.findById(dealId);
   
        System.out.println(vw_Prices.getDealId() + " " + 
   vw_Prices.getProductName());
    }
    return vw_Prices;

}

All data should be deleted from VW_Prices table because ids are not unique, try to insert new data with unique id then try the above code

user_129724670

I detect the problem, The view has main table Quotation and I didn't select it's ID and I used ID of the secondary table as the main ID for the View

I just write it if any one Google for such problem

user_62143750
Related