PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Spring boot sql table relationships

I’m developing a Spring Boot application with a PostgreSQL database. Within this application, I’ve implemented the functionality to create bots. Here’s a glimpse of my Bot entity:

@Data
@Entity
@Table(name = "bots")
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Bot {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "currentMoney", precision = 19, scale = 4)
    private BigDecimal currentMoney;

    @Column(name = "moneyLeftToInvest")
    private BigDecimal moneyLeftToInvest;

    @OneToMany(mappedBy = "bot", cascade = CascadeType.ALL)
    @Column(name = "stocks")
    private List<Stock> stocks = new ArrayList<>();

    @ElementCollection
    @CollectionTable(name = "bot_algorithms", joinColumns = @JoinColumn(name = "bot_id"))
    @Column(name = "algorithm_name")
    private List<Algorithms> algorithms;

    @Column(name = "percentageChange")
    private double percentageChange;
}

Then there ist another entity named Stock which looks like this:

@Data
@Entity
@Table(name = "stocks")
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Stock {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "bot_id")
    private Long botId;

    @Column(name = "name")
    private StockType stockType;

    @Column(name = "amount")
    private BigDecimal amount;
}

My objective is to ensure that each time I retrieve information about a bot, the stocks field within the Bot entity automatically queries the Stock Table for the stockId associated with the current bot. Unfortunately, I’m struggling to figure out how to make this functionality work seamlessly. Any guidance or insights would be greatly appreciated.

Tags:

2

Answers


  1. 0

    Your "one" side should have defined relationship to "many" like this

    @OneToMany(mappedBy = "bot", fetch = FetchType.EAGER,
               cascade = {CascadeType.PERSIST, CascadeType.MERGE,
                          CascadeType.DETACH, CascadeType.REFRESH})
private List<Stock> stocks;

    Your "many" side should have a defined relationship to "one" like this

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE,
                          CascadeType.DETACH, CascadeType.REFRESH})
@JoinColumn(name = "bot_id")
private Bot bot;

    Where I assume that your table stock in your database has a field bot_id that is a reference to the id of your bot id primary key field

    Also instead of using FetchType.EAGER, you can use .LAZY instead that is a cheaper operation, but you can still simulate EAGER behavior where you fetch all stocks that are related to the bot entity

    It would look something like this

    @Repository
public class AppDAOImpl {
// define field for entity manager
    private EntityManager entityManager;

    // inject entity manager using constructor injection
    @Autowired
    public AppDAOImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public Bot findBotByIdJoinFetch(int theId) {

        // create query
        TypedQuery<Bot> query = entityManager.createQuery(
                                                "select b from Bot b "
                                                    + "JOIN FETCH b.stocks "
                                                    + "where b.id = :data", Bot.class);
        query.setParameter("data", theId);

        // execute query
        Bot bot = query.getSingleResult();

        return bot;
    }

    After that everything will be loaded in your bot entity and you can then decide what you want to call to get a result, either only bot to be in a variable, or bot and stocks, but with this join fetch bot and stocks will always be loaded.

    Bot would be

    Bot bot = appDAO.findBotByIdJoinFetch(theId);

    and from that bot you can simply call

    bot.getStocks();

    or

    List<Stocks> stocks = bot.getStocks();

    Regardless of what you choose the stocks field in the bot entity will always have data that is related to that specific bot.

    Login or Signup to reply.
  2. 0

    In the @OneToMany relationship, the default fetch type is LAZY. So, when you retrieve the Bot entity, you won’t get the list of Stock data.

    To get the List of Stock when you fetch the Bot entity,
    Add fetch = FetchType.EAGER in @OneToMany annotation.
    Like @OneToMany(mappedBy = "bot", cascade = CascadeType.ALL, fetch = FetchType.EAGER)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search