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.
DaoGenerico dao= new DaoGenerico(“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.
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.
That’s been the approach I’ve taken replacing SQLContainer.
It takes me between 40 minutes to multiple days to plan, convert and test one grid, with the average being about 2-3 hours. The Vaadin 8 grids (8.0.4) take about twice as long as the Vaadin 7 grids to render in Chrome, which is much better than it had been as Henri Sara’s recent work has sped things up a lot. However the new grids handle large data sets much better. Scroll down 10,000 rows and the fresh data appears instantly.
Overall I find the Vaadin 8 grids require more skill and lower level attention to detail compared to Vaadin 7 grids. The conveniences of SQLContainers especially in filtering and sorting are missed. But the big data handling capabilities of the new pattern and the lighter touches on the database probabably constitute an improvement and a brighter future.
For now, I’m spending hundreds of hours on this conversion hoping not to introduce too many new bugs to stable code, still finding a few blockers (websocket issues at the moment), and expect the end result to be mostly the same as Vaadin 7 but a bit slower.
Ready for the future…
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.
RowSetDynaClass 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);
I’m interested on this, also.
Have you find a better solution, Konstantin?
I tend to create a class for every SQL I run; then I use Sql2o to map the result onto that class and show it in the Grid. It is annoying to define new class per SQL in Java but it’s just one-liner to do in Kotlin. Of course this solution is not applicable if the columns are not known upfront; but if they are, you can just use this SQLDataProvider:
SQLDataProvider.kt
Thanks a lot for your reply, Martin. I’ll take it into account when I resume my porting attempt to v8; I had to switch back to v7 because of more critical tasks that need to be accomplished on my project. Also, this lack of “direct SQL-to-component filling” feature kind of make me aprehensive to upgrading.
For some reason it seems I also skipped Ivan’s last message, which gives some very useful info that deserves a try, too.
Ivan Pasquale:
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.
IvanList<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);
ivan, the solution worked perfectly, thank you very much.
Could you give me information on how to implement the filters? Thanks
I’ve created a full-blown implementation of the SQLDataProvider which supports all features: paging, sorting and filtering. The example project also shows a generated filter bar, making it a full-blown replacement of the FilterTable extension. You can find the example project here: https://github.com/mvysny/vaadin8-sqldataprovider-example
This way of coding helped me a lot to build the source code for the site [apple customer service]
(https://applesupportnumber.net/apple-customer-service/) and reduce the lines of coding.