Loading...
Important Notice - Forums is archived

To simplify things and help our users to be more productive, we have archived the current forum and focus our efforts on helping developers on Stack Overflow. You can post new questions on Stack Overflow or join our Discord channel.

Product icon
TUTORIAL

Vaadin lets you build secure, UX-first PWAs entirely in Java.
Free ebook & tutorial.

Vaadin 8: populate Grid from sql query

Konstantin Cherepennikov
5 years ago Feb 09, 2017 6:34am

In application I have to take sql query from user and display results in a Grid.
In vaadin7 I used to get jdbc ResultSet metadata and create/fill vaadin Container.

How can I do this in vaadin 8? I have no class for Grid template and even don't know how many columns will be un query.

juan carlos montes
5 years ago Mar 17, 2017 5:16pm

DaoGenerico<Object> dao= new DaoGenerico<Object>("data000",Objects.class); 
        List<Map<String, Object>> sql = dao.buscarPorSqlQueryTransformers("SELECT cliente,nombre,dirección FROM clientes where baja ='N'");
        ListDataProvider<Map<String, Object>> lista= new ListDataProvider<Map<String,Object>>(sql);
        Grid<Map<String, Object>> grid= new Grid<Map<String,Object>>();
        grid.setDataProvider(lista);
        grid.addColumn((v)-> v.get("cliente")).setCaption("Cliente");
        grid.addColumn((v)-> v.get("nombre")).setCaption("Nombre");
        grid.addColumn((v)-> v.get("dirección")).setCaption("Dirección");
        layout.addComponents(grid);

Peter Hansson
5 years ago Mar 29, 2017 5:43am

juanka: DaoGenerico<Object> dao= new DaoGenerico<Object>("data000",Objects.class); 
        List<Map<String, Object>> sql = dao.buscarPorSqlQueryTransformers("SELECT cliente,nombre,dirección FROM clientes where baja ='N'");
        ListDataProvider<Map<String, Object>> lista= new ListDataProvider<Map<String,Object>>(sql);
        Grid<Map<String, Object>> grid= new Grid<Map<String,Object>>();
        grid.setDataProvider(lista);
        grid.addColumn((v)-> v.get("cliente")).setCaption("Cliente");
        grid.addColumn((v)-> v.get("nombre")).setCaption("Nombre");
        grid.addColumn((v)-> v.get("dirección")).setCaption("Dirección");
        layout.addComponents(grid);

@junka: The OP was asking for column-dynamic SQL, i.e. one where it is not known beforehand what the columns are. I think you missed that.

In Vaadin7, I was using so-called free-form queries from SQLContainer (or Enhanced SQLContainer) for this purpose, but I have yet to figure out how to do it in Vaadin 8. An added bonus of the SQLContainer was that it made my application somewhat DB agnostic, so I did not have to force my customers to use a specific DB.

Vaadin, and in particular Vaadin8, makes it dead-easy for you to display collections of objects in a Grid. But you don't have an object. Hence the problem.

Also - from my experience - this discussion is very hard to have because people will tell you that this and that solution can do "dynamic SQL", so why don't you just use that?   Well, the problem is that the term "dynamic SQL" normally refers to a situation where the WHERE claus changes at runtime. Frankly, that is pretty easy. Your situation - and mine - is one where the columns change at runtime, hence for a lack of better word I refer to it as column-dynamic queries. Try doing column-dynamic queries in JPA for instance (good luck on that one!).

Let me know if you figure something out.

 

Konstantin Cherepennikov
5 years ago Mar 29, 2017 6:31am

I meant something like this - user can write sql query and see result in Grid. For example - "select blah1, blah2 from blah_table1" and later "select * from blah_table2".
In Vaadin 7 I used jdbc ResultSet to get data and ResultSetMetaData to get information about columns (name, data type). And filled IndexedContainer with this data.
But there no this way in Vaadin 8.

As junka wrote I can populate Map<String, Object> from ResultSet and ResultSetMetaData, fill the grid using setDataProvider and add columns to Grid using Map keys. But I suppose that IndexedContainer in Vaadin7 was more convinient and easy, for example, I can set container property data type using ResultSetMetaData and automatically get correct renderer in Grid.

Steve Demy
5 years ago Mar 29, 2017 8:27am
Ivan Pasquale
4 years ago Sep 25, 2017 9:20am

Hi, I have found a simple solution that fits my needs.
Tha main idea is to use org.apache.commons.beanutils.DynaBean and RowSetDynaClass .
Its' simple and fast because the entire resultset is loaded into RowSetDynaClass.
First, you have to execute your query and get the results.

owSetDynaClass rsdc = null;
 try (ResultSet myResult = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).executeQuery(SourceQuery)) {
                    rsdc = new RowSetDynaClass(myResult, false);
                    myResult.getStatement().close();
                    myResult.close();
                }

Once you have filled your dynaclass you can use it to add your column, using lambda expression.Try it and tell me if it can help.
Ivan

List<DynaBean> BeanList =  .getRows();
        Grid<DynaBean> grid = new Grid("my grid");
        if (BeanList.size() > 0) {
            DynaProperty propertys =  BeanList.get(0).getDynaClass().getDynaProperties();
            for(DynaProperty property : propertys)
            {
                String col = property.getName();
                grid.addColumn((source) -> {
                    Object Value = (Object) source.get(col);
                    if(Value == null)
                        return "";
                    else
                        return Value;                  
                }).setCaption(col);
            }
        }
        grid.setItems(BeanList);
 
Pere Pasqual
4 years ago Nov 07, 2017 12:25pm
Martin Vyšný
4 years ago Nov 07, 2017 2:55pm
Pere Pasqual
4 years ago Nov 09, 2017 10:47am
wilson ramos
3 years ago Jul 01, 2018 2:36pm
Martin Vyšný
3 years ago Sep 10, 2018 3:25pm
Matt Smith
3 years ago Sep 22, 2018 8:44pm