Is it better to store the results of the query in RAM, or each time to re-do the query to the database?

Question

Java

Good afternoon!
Due to the lack of experience, I ran into the following problem: I don’t know how to work with data from the database. Leave them in temporary memory, or release and re-query the database when the need arises for this data?
I am writing a term paper on the database. I use Java, JDBC, PostgreSQL + JavaFX for GUI.
There is an entity "Supplier", which has its own "Catalog" of supplied goods (link 1:M).
At the moment, it's done like this:
When the program needs to display a list of suppliers, the singleton class method of the following form is called:

public List<Supplier> getSupplierList() {
        if (null == supplierList) {
                supplierList = SupplierTransaction.loadAllSuppliers();
        }
        return supplierList;
    }

After that, data about suppliers is stored until the end of the program.
When it is necessary to display the supplier's catalog, the following function of the Supplier("Supplier") class is used:

public List<Catalog> getCatalog() {
        if (null == catalog) {
            phoneList = SupplierTransaction.getSupplierCatalog(id);
        }
        return catalog;
    }

Thus, data is loaded from the database only once. On the one hand, this is good: when we re-access, we get data faster, but on the other hand, the amount of memory that the program occupies grows each time.
I gave only two examples, a similar implementation is present in other parts of the program.
How to do better? Do you need to re-query each time? Leave as is? Or how to combine these two implementations?
Thank you!

Answer 1

Depends on many conditions. However, please note that:
1. A query to the database is more expensive in terms of resources than its own memory. The memory you have on your host is usually gigabytes, plus local SWAP. Frequent requests to the database create a load on it and compete with other requests.
2. Access to your own memory is many, many, many times faster than access over the network.
Hence the conclusion - caching is necessary and you are doing everything right.
In order not to eat up ALL memory, you need to limit yourself to a certain (configurable!) volume, when it goes beyond it, the oldest data (which has not been accessed for a long time) is deleted from the cache.
In order not to get a situation when the database has changed, and you don’t know anything about it, use pg_notify

Answer 2

For coursework, it doesn't matter.
For real solutions, of course, everything depends on the specifics, but as a moment: if the database is something distributed, and the application lives in the form of many instances, then with intermediate storage / caching of data in the memory of an application instance, it can be a source of problems.
For in other instances - this data will not yet be.
Well, taking into account that the database mechanisms one way or another but cache - all of the above may be meaningful only for very specific cases.

Answer 3

A little from another area (Java EE), but suddenly come in handy

public static List<Order> getOrders() throws SQLException, ... {
  List<Order> result = new ArrayList<>();  
  InitialContext initContext = new InitialContext();
  DataSource ds = (DataSource) initContext.lookup("jdbc/ИМЯ_РЕСУРСА");
  try (Connection conn = ds.getConnection()) {
      Statement stm = conn.createStatement();
      ResultSet res = stm.executeQuery(" SELECT * FROM  `orders` бла-бла-бла ;");
      //кешируем запрос
      CachedRowSet crs = new com.sun.rowset.CachedRowSetImpl();
      crs.populate(res);
      //работаем с ним
      if (crs.size() > 0) {
          crs.first();
          do {
                String id = crs.getString("id");
                String number = crs.getString("number");
                //формирование результ.Объекта
                result.add(new Order(id, number));
          } while (crs.next());
          countRows = result.size();  //сохраним колво
      }
   } //try
  return result;
}

После ресурсы подключения к БД автоматически закрываются.