how to fetch data from 3 tables by using spring and hibernate?

Question

I have 3 tables which are joined by foreign key. I need to fetch data by joining all the tables. Below are my 3 entity classes.

Stock.java

@Entity
public class Stock {
    private int id;
    private Date entryDate;
    private int entryUserId;
    private BigDecimal parValue;
    private int status;
    private Timestamp statusChgDate;
    private int statusChgUserId;
    private String stockName;
    private String stockSymbol;

    @Id
    @Column(name = "Id", nullable = false)
    public int getId() {
        return id;
    }

    @Basic
    @Column(name = "EntryDate", nullable = false)
    public Date getEntryDate() {
        return entryDate;
    }

    @Basic
    @Column(name = "EntryUserID", nullable = false)
    public int getEntryUserId() {
        return entryUserId;
    }

    @Basic
    @Column(name = "ParValue", nullable = false, precision = 2)
    public BigDecimal getParValue() {
        return parValue;
    }

    @Basic
    @Column(name = "Status", nullable = false)
    public int getStatus() {
        return status;
    }

    @Basic
    @Column(name = "StatusChgDate", nullable = false)
    public Timestamp getStatusChgDate() {
        return statusChgDate;
    }

    @Basic
    @Column(name = "StatusChgUserID", nullable = false)
    public int getStatusChgUserId() {
        return statusChgUserId;
    }

    @Basic
    @Column(name = "StockName", nullable = false, length = 100)
    public String getStockName() {
        return stockName;
    }

    @Basic
    @Column(name = "StockSymbol", nullable = false, length = 15)
    public String getStockSymbol() {
        return stockSymbol;
    }

    ... setters ...
}

StockDetl.java

@Entity
public class StockPriceDetl {
    private int id;
    private BigDecimal amount;
    private BigDecimal closingPrice;
    private BigDecimal diffAmount;
    private BigDecimal maxPrice;
    private BigDecimal minPrice;
    private int numberOfTransaction;
    private BigDecimal previousClosingPrice;
    private Timestamp statusChgDate;
    private int statusChgUserId;
    private int tradedShares;
    private Stock stockByStockId;

    @Id
    @Column(name = "Id", nullable = false)
    public int getId() {
        return id;
    }

    @Basic
    @Column(name = "Amount", nullable = false, precision = 2)
    public BigDecimal getAmount() {
        return amount;
    }

    @Basic
    @Column(name = "ClosingPrice", nullable = false, precision = 2)
    public BigDecimal getClosingPrice() {
        return closingPrice;
    }

    @Basic
    @Column(name = "DiffAmount", nullable = false, precision = 2)
    public BigDecimal getDiffAmount() {
        return diffAmount;
    }

    @Basic
    @Column(name = "MaxPrice", nullable = false, precision = 2)
    public BigDecimal getMaxPrice() {
        return maxPrice;
    }

    @Basic
    @Column(name = "MinPrice", nullable = false, precision = 2)
    public BigDecimal getMinPrice() {
        return minPrice;
    }

    @Basic
    @Column(name = "NumberOfTransaction", nullable = false)
    public int getNumberOfTransaction() {
        return numberOfTransaction;
    }

    @Basic
    @Column(name = "PreviousClosingPrice", nullable = false, precision = 2)
    public BigDecimal getPreviousClosingPrice() {
        return previousClosingPrice;
    }

    @Basic
    @Column(name = "StatusChgDate", nullable = false)
    public Timestamp getStatusChgDate() {
        return statusChgDate;
    }

    @Basic
    @Column(name = "StatusChgUserID", nullable = false)
    public int getStatusChgUserId() {
        return statusChgUserId;
    }

    @Basic
    @Column(name = "TradedShares", nullable = false)
    public int getTradedShares() {
        return tradedShares;
    }

    @ManyToOne
    @JoinColumn(name = "StockId", referencedColumnName = "Id", nullable = false)
    public Stock getStockByStockId() {
        return stockByStockId;
    }

    ... setters ...
}

StockPriceMast.java

@Entity
public class StockPriceMast {
    private int id;
    private Date entryDate;
    private int entryUserId;
    private String remarks;
    private int status;
    private Timestamp statusChgDate;
    private int statusChgUserId;
    private Date tranDate;

    @Id
    @Column(name = "Id")
    public int getId() {
        return id;
    }

    @Basic
    @Column(name = "EntryDate")
    public Date getEntryDate() {
        return entryDate;
    }

    @Basic
    @Column(name = "EntryUserID")
    public int getEntryUserId() {
        return entryUserId;
    }

    @Basic
    @Column(name = "Remarks")
    public String getRemarks() {
        return remarks;
    }

    @Basic
    @Column(name = "Status")
    public int getStatus() {
        return status;
    }

    @Basic
    @Column(name = "StatusChgDate")
    public Timestamp getStatusChgDate() {
        return statusChgDate;
    }

    @Basic
    @Column(name = "StatusChgUserID")
    public int getStatusChgUserId() {
        return statusChgUserId;
    }

    @Basic
    @Column(name = "TranDate")
    public Date getTranDate() {
        return tranDate;
    }

    ... setters ...
}

Here client will send StockSymbol and two dates. i need to populate maxprice, minprice, closingprice and previousclosingPrice from certain dates like say 01/01/2001 to 01/01/2002 (Trandate on stockpricemast).

How can i do that using hibernate? SQL query i want to achieve.

Select d.MinPrice,d.MaxPrice,d.ClosingPrice, d.PreviousClosingPrice
from StockPriceDetl d
inner join Stock st on d.StockId=st.Id
inner join StockPriceMast sm on d.MastId = sm.Id
where st.StockSymbol='NABIL' and sm.TranDate 
between '2001-01-01'and'2002-01-01'

StockDetl has a foreign key named mastid and transaction dates are stored on StockPriceMast according to which i need to fetch minprice,maxprice,closingprice and previsousclosingprice from the table StockDetl. — sagar, Feb 17 '17 at 09:57
Why didn't you add the StockDetl to StockPriceMast mapping through hibernate too? — Maddy, Feb 17 '17 at 10:09
I dont see that field in the entity.. also it would be useful if you add an SQL query that you want to transpose to JPQL — Maciej Kowalski, Feb 17 '17 at 10:10
please check above sql query i want to achieve. i have edited question. — sagar, Feb 17 '17 at 10:30

score 0 · Answer 1 · answered Feb 17 '17 at 10:35

0

Have you thought about JPA-Repositories. There you can define your SQL to a method like:

public interface StockDetlRepository extends JpaRepository<StockDetl, Long>, QueryDslPredicateExecutor<StockDetl> {

    @Query("SELECT sd from StockDetl sd, Stock s, StockPriceMast spm WHERE s.id = sd.stockId AND s.madtId = spm.id AND s.stockSymbol = ?1 AND spm.trandDate BETWEEN ?2 AND ?3")
    List<StockDetl> findPricesByStockSymbolAndDate(String stockSymbol, Timestamp fromTranDate, Timestamp toTranDate);

}

answered Feb 17 '17 at 10:35

Alexander Plickat

101
1
10

But you haven't any mastId in StockDetl posted. – Alexander Plickat Feb 17 '17 at 10:41
well i didn't know about this. i want to give this a try. can you suggest me how my entities should be defined? a little confused about where should i put OnetoOne or OnetoMany or ManytoOne – sagar Feb 17 '17 at 10:47
For your question about OneToOne, OneToMany and ManyToOne - please find out this http://stackoverflow.com/questions/16119531/hibernate-jpa-manytoone-vs-onetomany and http://stackoverflow.com/questions/21789769/jpa-unidirectional-onetoone-vs-manytoone-with-hibernate-no-difference – Alexander Plickat Feb 22 '17 at 10:47

how to fetch data from 3 tables by using spring and hibernate?

1 Answers1