此HQL / SQL查询可以吗? Java,春季启动

我有多个实体映射到我的MySQL数据库,并且可以使用Spring的CrudRepository将这些实体映射到相关对象中。这三个实体是:快照,市场和合同

每个快照都包含一个唯一的ID,一个时间戳和一个Market对象列表

每个市场均包含唯一的主键ID,nonUniquemarketId,名称,URL,...和“合同列表”对象。

每个合同都包含有关定价信息的字段。

nonUniqueMarketId是我从中获取数据的API给出的ID。在每个快照的市场对象列表中仅发生一次

我想知道是否有可能从特定快照的市场列表中获取特定市场的合同列表。也就是说,如果我有快照的时间戳和nonUniqueMarketId,是否可以编写查询以查看数据库中的所有快照,获取具有给定时间戳的快照,然后查看该快照的市场列表,或者得到给定其nonUniqueMarketId的市场或其中的“合约列表”字段?

更笼统地说,我知道我想要什么快照和我想要什么市场,考虑到这两个参数,是否有可能获得那个特定市场?

快照.java

package com.axlor.predictionassistantanalyzer.model;

import com.fasterxml.jackson.annotation.JsonProperty;

import javax.persistence.*;
import java.util.List;

@Entity
public class Snapshot{

    @Id
    private Integer hashId;

    @JsonProperty("markets")
    @ElementCollection(fetch=FetchType.EAGER)
    @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER) //ALL, not persist
    private List<Market> markets;

    private long timestamp;

    public Snapshot(List<Market> markets, long timestamp, Integer hashId) {
        this.markets = markets;
        this.timestamp = timestamp;
        this.hashId = hashId;
    }

    public Snapshot() {
    }

    public void setMarkets(List<Market> markets){
        this.markets = markets;
    }

    public List<Market> getMarkets(){
        return markets;
    }

    public long getTimestamp() {
        return timestamp;
    }

    public void setTimestamp(long timestamp) {
        this.timestamp = timestamp;
    }

    public Integer getHashId() {
        return hashId;
    }

    public void setHashId(Integer hashId) {
        this.hashId = hashId;
    }

    @Override
    public String toString() {
        return "Snapshot{" +
                "hashId=" + hashId +
                ", markets=" + markets +
                ", timestamp=" + timestamp +
                '}';
    }
}

Market.java

package com.axlor.predictionassistantanalyzer.model;

import com.fasterxml.jackson.annotation.JsonProperty;

import javax.persistence.*;
import java.util.List;

@Entity
public class Market {

    @Id
    @GeneratedValue
    private Integer marketUniqueID;

    @JsonProperty("timeStamp")
    private String timeStamp;

    @Transient
    @JsonProperty("image")
    private String image;

    @JsonProperty("name")
    private String name;

    @JsonProperty("id")
    private int id;

    @Transient
    @JsonProperty("shortName")
    private String shortName;

    @ElementCollection(fetch=FetchType.EAGER)
    @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER) //ALL, not persist
    @JsonProperty("contracts")
    private List<Contract> contracts;

    @JsonProperty("url")
    private String url;

    @JsonProperty("status")
    private String status;

    public Market(String timeStamp, String image, String name, int id, String shortName, List<Contract> contracts, String url, String status) {
        this.timeStamp = timeStamp;
        this.image = image;
        this.name = name;
        this.id = id;
        this.shortName = shortName;
        this.contracts = contracts;
        this.url = url;
        this.status = status;
    }

    public Market() {
    }

    public Integer getMarketUniqueID() {
        return marketUniqueID;
    }

    public void setMarketUniqueID(Integer marketUniqueID) {
        this.marketUniqueID = marketUniqueID;
    }

    public String getTimeStamp() {
        return timeStamp;
    }

    public void setTimeStamp(String timeStamp) {
        this.timeStamp = timeStamp;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public String getName() {
        return name;
    }

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

    public int getId() {
        return id;
    }

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

    public String getShortName() {
        return shortName;
    }

    public void setShortName(String shortName) {
        this.shortName = shortName;
    }

    public List<Contract> getContracts() {
        return contracts;
    }

    public void setContracts(List<Contract> contracts) {
        this.contracts = contracts;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "\n\nMarket{" +
                "marketUniqueID='" + marketUniqueID + '\'' +
                ", timeStamp='" + timeStamp + '\'' +
                ", image='" + image + '\'' +
                ", name='" + name + '\'' +
                ", id=" + id +
                ", shortName='" + shortName + '\'' +
                ", contracts=" + contracts +
                ", url='" + url + '\'' +
                ", status='" + status + '\'' +
                "\n}";
    }
}

Contract.java

package com.axlor.predictionassistantanalyzer.model;

import com.fasterxml.jackson.annotation.JsonProperty;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Transient;

@Entity
public class Contract {

    /*
Why do we need contractUniqueID?:
    See explanation in Market.java
    Basically, the JsonProperty 'id' field cannot be used as a primary key in the database. We need to create one, so we generate it here.
 */
    @Id
    @GeneratedValue
    private Integer contractUniqueID;

    @Transient
    @JsonProperty("image")
    private String image;

    @JsonProperty("lastClosePrice")
    private double lastClosePrice;

    @JsonProperty("bestBuyNoCost")
    private double bestBuyNoCost;

    @JsonProperty("bestSellNoCost")
    private double bestSellNoCost;

    @Transient
    @JsonProperty("displayOrder")
    private int displayOrder; //not sure what this even is supposed to do lol

    @JsonProperty("dateEnd")
    private String dateEnd;

    @JsonProperty("bestSellYesCost")
    private double bestSellYesCost;

    @JsonProperty("bestBuyYesCost")
    private double bestBuyYesCost;

    @JsonProperty("lastTradePrice")
    private double lastTradePrice;

    @JsonProperty("name")
    private String name;

    @JsonProperty("id")
    private int id;

    @Transient
    @JsonProperty("shortName")
    private String shortName;

    @JsonProperty("status")
    private String status;

    public Contract(String image, double lastClosePrice, double bestBuyNoCost, double bestSellNoCost, int displayOrder, String dateEnd, double bestSellYesCost, double bestBuyYesCost, double lastTradePrice, String name, int id, String shortName, String status) {
        this.image = image;
        this.lastClosePrice = lastClosePrice;
        this.bestBuyNoCost = bestBuyNoCost;
        this.bestSellNoCost = bestSellNoCost;
        this.displayOrder = displayOrder;
        this.dateEnd = dateEnd;
        this.bestSellYesCost = bestSellYesCost;
        this.bestBuyYesCost = bestBuyYesCost;
        this.lastTradePrice = lastTradePrice;
        this.name = name;
        this.id = id;
        this.shortName = shortName;
        this.status = status;
    }

    public Contract() {
    }

    public Integer getContractUniqueID() {
        return contractUniqueID;
    }

    public void setContractUniqueID(Integer contractUniqueID) {
        this.contractUniqueID = contractUniqueID;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public double getLastClosePrice() {
        return lastClosePrice;
    }

    public void setLastClosePrice(double lastClosePrice) {
        this.lastClosePrice = lastClosePrice;
    }

    public double getBestBuyNoCost() {
        return bestBuyNoCost;
    }

    public void setBestBuyNoCost(double bestBuyNoCost) {
        this.bestBuyNoCost = bestBuyNoCost;
    }

    public double getBestSellNoCost() {
        return bestSellNoCost;
    }

    public void setBestSellNoCost(double bestSellNoCost) {
        this.bestSellNoCost = bestSellNoCost;
    }

    public int getDisplayOrder() {
        return displayOrder;
    }

    public void setDisplayOrder(int displayOrder) {
        this.displayOrder = displayOrder;
    }

    public String getDateEnd() {
        return dateEnd;
    }

    public void setDateEnd(String dateEnd) {
        this.dateEnd = dateEnd;
    }

    public double getBestSellYesCost() {
        return bestSellYesCost;
    }

    public void setBestSellYesCost(double bestSellYesCost) {
        this.bestSellYesCost = bestSellYesCost;
    }

    public double getBestBuyYesCost() {
        return bestBuyYesCost;
    }

    public void setBestBuyYesCost(double bestBuyYesCost) {
        this.bestBuyYesCost = bestBuyYesCost;
    }

    public double getLastTradePrice() {
        return lastTradePrice;
    }

    public void setLastTradePrice(double lastTradePrice) {
        this.lastTradePrice = lastTradePrice;
    }

    public String getName() {
        return name;
    }

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

    public int getId() {
        return id;
    }

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

    public String getShortName() {
        return shortName;
    }

    public void setShortName(String shortName) {
        this.shortName = shortName;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Contract{" +
                "contractUniqueID='" + contractUniqueID + '\'' +
                ", image='" + image + '\'' +
                ", lastClosePrice=" + lastClosePrice +
                ", bestBuyNoCost=" + bestBuyNoCost +
                ", bestSellNoCost=" + bestSellNoCost +
                ", displayOrder=" + displayOrder +
                ", dateEnd='" + dateEnd + '\'' +
                ", bestSellYesCost=" + bestSellYesCost +
                ", bestBuyYesCost=" + bestBuyYesCost +
                ", lastTradePrice=" + lastTradePrice +
                ", name='" + name + '\'' +
                ", id=" + id +
                ", shortName='" + shortName + '\'' +
                ", status='" + status + '\'' +
                '}';
    }
}
评论