Multiple queries in the same view

I would like to run multiple queries then show results in a page such as : https://adminlte.io/themes/v3/index.html

I create a first controller query :

package controllers;

import models.Sysuser;
import play.mvc.Controller;
import play.mvc.Result;
import play.mvc.Security;
import views.html.sitemap.index;

import javax.inject.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CompletionStage;
import play.libs.concurrent.HttpExecutionContext;
import static java.util.concurrent.CompletableFuture.supplyAsync;

import play.db.*;
import io.ebean.*;
import play.Logger;
import java.util.List;
import java.util.ArrayList;
import models.LocationExtractedData;

@Security.Authenticated(Secured.class)
public class SiteMap extends Controller {

    private Database db;
    private final HttpExecutionContext httpExecutionContext;
    private static final Logger.ALogger logger = Logger.of(SiteMap.class);

    @Inject
    public SiteMap(Database db,
                   HttpExecutionContext httpExecutionContext) {
        this.db = db;
        this.httpExecutionContext = httpExecutionContext;
    }   


    public CompletionStage<Result> index() {
    return SearchSomething().thenApplyAsync((List<LocationExtractedData> infos) -> {                   
          return ok(views.html.sitemap.index.render(  Sysuser.findByUserName(request().username()), infos)  );
            }, httpExecutionContext.current());
  } 


  public CompletionStage<List<LocationExtractedData>> SearchSomething() {
    return CompletableFuture.supplyAsync(() -> {

        return db.withConnection(
              connection -> {

                // Imagines this is a complexe QUERY (Later in the future...)
                final String sql =  "SELECT sysuser_id, role_id "
                                   +"from sysuser_role "
                                   +"where sysuser_id = '1' "
                                   +"and role_id in ('1','2','3','4','5') ";
                final RawSql rawSql = RawSqlBuilder.parse(sql).create();                
                Query<LocationExtractedData> query = Ebean.find(LocationExtractedData.class);  
                query.setRawSql(rawSql);
                List<LocationExtractedData> list = query.findList();  

                return list;                              

              });

        }, httpExecutionContext.current());
  } 




}

Can you telling me how to run multiple and optimized queries in the same time for my page full of dashboards, charts and tables!

If i create multiple list of ebeanLists ( queries ), does this will affect the loading of my page ? IF not, then, what should i do ?

Thank you in advance,

Answer

Typically, in an application similar to the link you have provided, you create reusable APIs following the MVC design pattern. Querying the database from the controller is very much against that pattern.

Each API should be atomic, creating a single API to run 1 query to fetch all of the data for that page is not the correct approach.

If you are looking for performance for your API you should get familiar with asynchronous programming. Running your APIs async will allow your back end to process multiple front end requests at the same time, greatly improving performance.