Using play with existing database ( models )

Play Framework
1

Hello,

Can i use play framework linked to an existing database (Example SAP DBB) to only display :

  • Dashboards (Queries)
  • Charts (Queries)

I developped an authentication page, suddenly, i did not tought about how to extract data from an existing database without declaring models!

What is the good way to only extract with many queries data and display it on views scala (Play framework JAVA) ?

Thank you so much

3

Can you give us more details please!
Thank you in advance

4

why do you need it to be without declaring models?

5

Because the database already exists and works on SAP ERP.
I want to use playframework as a “web interface” to show some custom :

  • Reports ( by JasperReports )
  • Charts ( by Queries )
  • Dashboards ( by Queries )

That’s why i can’t déclare models, first models have a multitude of tables/columns ( so many! )
Seconde, i juste would like to show data by executing queries without ( Create, Update or delete )

Thank you in advance

6

you can obtain JDBC Connection instance and execute raw SQL queries (read docs here https://www.playframework.com/documentation/2.8.x/AccessingAnSQLDatabase)

7

Thank you for your return, but i don’t think it is necessary to obtain a JDBC connection, this is what i did :

1- Repository which group all my complexe queries

package repository;

import io.ebean.*;
import models.DataI;
import models.DataII;
import play.db.ebean.EbeanConfig;
import javax.inject.Inject;
import java.util.Optional;
import java.util.List;
import java.util.ArrayList;
import java.util.concurrent.CompletionStage;

import static java.util.concurrent.CompletableFuture.supplyAsync;

public class CustomDataRepository {

    private final EbeanServer ebeanServer;
    private final DatabaseExecutionContext executionContext;

    @Inject
    public CustomDataRepository(EbeanConfig ebeanConfig, DatabaseExecutionContext executionContext) {
        this.ebeanServer = Ebean.getServer(ebeanConfig.defaultServer());
        this.executionContext = executionContext;
    }

	
	
  public CompletionStage<List<DataI>> QueryI() {
    return supplyAsync(() -> {
        							
				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<DataI> query = Ebean.find(DataI.class);  
                query.setRawSql(rawSql);
                List<DataI> list = query.findList();  
				
				return list;				              
				
		}, executionContext);
  } 
  

  public CompletionStage<List<DataII>> QueryII() {
    return supplyAsync(() -> {
        							
				final String sql =  "SELECT sysuser.name, sysuser.active, department.description "
				                   +"from sysuser "
								   +"left join department on department.id = sysuser.department_id "
								   +"where sysuser.id = '1' ";
				final RawSql rawSql = RawSqlBuilder.parse(sql).create();				
				Query<DataII> query = Ebean.find(DataII.class);  
                query.setRawSql(rawSql);
                List<DataII> list = query.findList();  
				
				return list;				              
				
		}, executionContext);
  }  
  
  
		

	
}

2- Custom models

package models;

import play.data.validation.Constraints;
import javax.persistence.*;
import io.ebean.*;

@Entity
public class DataI extends Model {

    @Column(name = "sysuser_id")
    private Long sysuserId;
	
    @Column(name = "role_id")
    private Long roleId;
	
	
		
	
    public Long getUserID()
    {return sysuserId;}	

    public Long getRoleID()
    {return roleId;}		

}


package models;

import play.data.validation.Constraints;
import javax.persistence.*;
import io.ebean.*;

@Entity
public class DataII extends Model {

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

    @Column(name = "description")
    private String description;	
	
		
	
    public String getName()
    {return name;}	

    public Boolean getActive()
    {return active;}		

    public String getDescription()
    {return description;}	

}

3- Controller which can combine more completionstage ( more queries )

package controllers;

import models.Sysuser;
import models.DataI;
import models.DataII;
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 io.ebean.*;
import play.Logger;
import java.util.List;
import java.util.ArrayList;
import repository.CustomDataRepository;

@Security.Authenticated(Secured.class)
public class SiteMap extends Controller {
	
	private final CustomDataRepository customDataRepository;
	private final HttpExecutionContext httpExecutionContext;
	private static final Logger.ALogger logger = Logger.of(SiteMap.class);

    @Inject
    public SiteMap(HttpExecutionContext httpExecutionContext,
	               CustomDataRepository customDataRepository) {
        this.httpExecutionContext = httpExecutionContext;
		this.customDataRepository = customDataRepository;
    }	


	
  public CompletionStage<Result> index() {

   return customDataRepository.QueryI().thenApplyAsync((List<DataI> L1) -> {                   
   //return customDataRepository.QueryII().thenApplyAsync((List<DataII> L2) -> {                   
    
    	return ok(views.html.sitemap.index.render(  Sysuser.findByUserName(request().username()), L1)  );
            }, httpExecutionContext.current());
  }	
	
	

  
		

	
}

do you think i’m on the right path please ?
Thank you so much