这个hql/sql查询可能吗?java,Spring Boot

m3eecexj  于 2021-08-09  发布在  Java
关注(0)|答案(0)|浏览(262)

我有多个实体Map到mysql数据库,我可以使用spring的crudrepository将这些实体Map到相关对象。这三个实体是:快照、市场和合同
每个快照都包含一个唯一的id、一个时间戳和一个市场对象列表
每个市场都包含一个唯一的主键id、一个非唯一的MarketId、名称、url……和合同对象列表。
每个合同都包含有关定价信息的字段。
unonquemarketid是我从中获取数据的api提供的id。它只在每个快照的市场对象列表中出现一次
我想知道是否有可能从一个特定的快照市场列表中得到一个特定市场的合同列表。也就是说,如果我有一个快照的时间戳和一个ununiquemarketid,是否可以编写一个查询来查看数据库中的所有快照,获取具有给定时间戳的快照,然后查看该快照的市场列表,要么获取给定ununiquemarketid的市场,要么获取其中的合同列表字段?
更一般地说,我知道我想要什么样的快照,我想要什么样的市场,有没有可能在这两个参数下得到那个特定的市场?
快照.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 +
                '}';
    }
}

市场.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}";
    }
}

合同.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 + '\'' +
                '}';
    }
}

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题