FLOW - Grid with lazy loading

Flow
21

Issues · vaadin/flow · GitHub is the perfect place for bug reports with a reproducible example

22

Ready, in a single class.

see attached video

CREATE SEQUENCE public.seq_usuario
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1
NO CYCLE;

CREATE TABLE public.usuario (
id int8 NOT NULL,
nome text NOT NULL,
CONSTRAINT pk_usuario PRIMARY KEY (id)
);

package com.bm.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

import com.vaadin.flow.component.grid.Grid;
import com.vaadin.flow.component.grid.Grid.SelectionMode;
import com.vaadin.flow.component.grid.GridVariant;
import com.vaadin.flow.component.orderedlayout.VerticalLayout;
import com.vaadin.flow.router.Route;

@Route(“”)
public class CaveView extends VerticalLayout {
private static final long serialVersionUID = 2520211698297094249L;
private static Connection conexao;

public CaveView() {
	Grid<Usuario> grid = new Grid<>(Usuario.class, false);
	grid.setHeight("300px");
	grid.addThemeVariants(GridVariant.LUMO_COMPACT);
	grid.setSelectionMode(SelectionMode.SINGLE);
	grid.addColumn(Usuario::getId).setHeader("ID");
	grid.addColumn(Usuario::getNome).setHeader("Name");
	grid.setPageSize(50);
	grid.setItems(query -> getUsuarios(query.getOffset(), query.getLimit()).stream());
	add(grid);
}

public List<Usuario> getUsuarios(int offset, int limit) {
	String sql = "select * from usuario offset " + offset + " limit " + limit;
	List<Usuario> us = new ArrayList<Usuario>();
	try (PreparedStatement st = getConexao().prepareStatement(sql)) {
		System.out.println(st.toString());
		try (ResultSet rs = st.executeQuery()) {
			while (rs.next()) {
				Usuario o = new Usuario();
				o.setId(rs.getLong("id"));
				o.setNome(rs.getString("nome"));
				us.add(o);
			}
			return us;
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return us;
}

public static Connection getConexao() {
	try {
		if (Objects.nonNull(conexao) && !conexao.isValid(20)) {
			conexao = null;
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	if (Objects.isNull(conexao))
		try {
			Class.forName("org.postgresql.Driver");
			conexao = DriverManager
					.getConnection("jdbc:postgresql://localhost:5432/bmapp?user=postgres&password=mussi99");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	return conexao;
}

}

23

I’m really sorry, but I still do not understand where’s the problem here.
Is it the different limit you get on a query (18 instead of 50)?
If so, this is expected because once you scroll down and get all the records, the data communicator can adjust its assumed size to the total number of rows retrieved, and just ask for the missing ones.
For example, if you are at the end and scroll up a bit (not to the top) and then down to the end, the data communicator might require rows from the visible offset (e.g. 100) to the known size (e.g. 118), so setting the limit to 18 because remaining data is less that the page size.
However, If you quickly scroll up and down to the bottom of the grid, the communicator will then need to fetch more rows than the page size (e.g. from 50 to 118), so it enqueues two requests with the same limit equal to the page size (50).

24

Hi Marco,

As you can see in the video, just scrolling up triggers a new query, as shown at 30 seconds into the video.

I really can’t understand why scrolling up triggers a new query, since I’m browsing through records already loaded in the grid.

Also note that the SQL executed is select * from user offset 0 limit 50, because that’s what I’m doing if I already have 118 records loaded in the grid.

Why did the offset go back to zero?

I believe that the query for the backend should only be triggered when scrolling down.

25

Only a portion of data is retained in memory with lazy loading, so when you scroll up the grid might or not do a new request.
Here’s the link to the documentation about lazy loading: Binding Items to Components | Data Binding | Flow | Vaadin Docs

26

As Marco said; the Grid won’t cache values forever and clears them quite regularly to ensure smooth client side Handling - so scrolling up can result in queries exactly as scrolling down.

2 Likes