Query from screen controller

Support
#1

Hi,

Is it possible to query a datasource table from the screen controller? I’ve tried following the instructions on Running SQL Queries - CUBA Platform. Developer’s Manual, but I can’t get it to work. persistence doesn’t have a method ‘getEntityManager()’.

@Inject
    private Persistence persistence;

    @Subscribe
    protected void onInit(InitEvent event) {

        Query query = persistence.getEntityManager().createNativeQuery(
                "select * from TABLE");
        List list = query.getResultList();
        for (Iterator it = list.iterator(); it.hasNext(); ) {
            Object[] row = (Object[]) it.next();
            String name = (String) row[1];
            log.info(name);
        }
    }
}

Is this possible or is there another way to query a datasource and manipulate the results in the screen controller?

#2

You can use DataManager from a screen controller:

@Inject
private DataManager dataManager;

private Book loadBookById(UUID bookId) {
    return dataManager.load(Book.class).id(bookId).view("book.edit").one();
}

private List<BookPublication> loadBookPublications(UUID bookId) {
    return dataManager.load(BookPublication.class)
        .query("select p from library_BookPublication p where p.book.id = :bookId")
        .parameter("bookId", bookId)
        .view("bookPublication.full")
        .list();
}

https://doc.cuba-platform.com/manual-7.1/dataManager.html

Low level Persistence is available only from core module due to distributed 3-tier architecture of the platform.

1 Like
#3

Thanks, that helps me a step forward. Suppose I want to load entities from a KeyValueDatasource. What can I best do to query the datasource and then iterate the results? I’m not sure what method listed on https://doc.cuba-platform.com/manual-7.1/dataManager.html is suitable to do that. I suspect ‘loadValues(String query)’.

I think this would work:

List<KeyValueEntity> list = dataManager.loadValues(
                "SELECT kdoi.kdo_datum, kdoi.score_meetbaar " +
                        "FROM kdodatabase_Opdracht opdr " +
                        "LEFT JOIN opdr.kdoinitiatie kdoi")
                .properties("kdo_datum", "score_meetbaar")
                .list();

        for (Iterator it = list.iterator(); it.hasNext(); ) {
            log.info(it.next());
        }

But how to get a specific field from each KeyValueEntity (e.g. “score_meetbaar”)?

#4

Each entity class has getValue(String propertyName) method, you can try to use it with KeyValueEntity too.