How to bind SQL resultset to vaadin grid

Flow
1

I have declared a vaading grid
private Grid gridReq = new Grid();

and the below query returns me a resultset named rs
ResultSet rs=stmt.executeQuery(“select distinct ReqID from Requirement”);

How do I bind the resultset to the grid?

2

Are you using Vaadin 8.x? In that case it is very simple. I assume that your ResultSet is already collection of Beans you want to show in your Grid. So you you simply use Grid.setItems(ResultSet). Grid needs to be typed Requitement in your case (assuming ResultSet is Collection of Requirement).

3

I have little bit different situation and I use Vaadin flow 16. I have a dynamic resultset which means every query has another table header.
How can I display this result with Grid or any suitable vaadin component?

This is how I get resultset:

public List<Map<String,Object>> retrieveRows(String queryString) throws SQLException{
		List<Map<String, Object>> rows = new LinkedList<Map<String, Object>>();
		
		Connection conn = null;
		PreparedStatement s = null;	
		ResultSet rs = null;
		try 
		{
			conn = mydb.getConnection();
			s = conn.prepareStatement(queryString);
			
			int timeout = s.getQueryTimeout();
			if(timeout != 0)
				s.setQueryTimeout(0);
			
			rs = s.executeQuery();
			
			
			List<String> columns = new LinkedList<>();
			ResultSetMetaData resultSetMetaData = rs.getMetaData();
			int colCount = resultSetMetaData.getColumnCount();
			for(int i= 1 ; i < colCount+1 ; i++) {
				columns.add(resultSetMetaData.getColumnLabel(i));
			}
			
			while (rs.next()) {
				Map<String, Object> row  = new LinkedHashMap<String, Object>();
				for(String col : columns) {
					int colIndex = columns.indexOf(col)+1;
					String object = rs.getObject(colIndex)== null ? "" : String.valueOf(rs.getObject(colIndex));
					row.put(col, object);
				}
				
				rows.add(row);
			}
		} catch (SQLException | IllegalArgumentException  | SecurityException e) {
            throw new OwalException("Error during query: "+ queryString, e);
		}
		finally {
			Utils.close(rs, s, conn, logger);
		}
		
		return rows;
	}
4

You can do this:


        // Create the grid and set its items
        Grid<HashMap<String, Object>> grid2 = new Grid<>();
        grid2.setItems(rows); // rows is the result of retrieveRows

        // Add the columns based on the first row
        HashMap<String, Object> s = rows.get(0);
        for (Map.Entry<String, Object> entry : s.entrySet()) {
            grid2.addColumn(h -> h.get(entry.getKey().toString())).setHeader(entry.getKey());
        }

        // Add the grid to the page
        layout.add(grid2);

You have to deal with the conversion of your object (in my code it’s done with toString() but I’m not checking if it’s null or if it’s a date …).

Here the original solution for Vaadin 8: https://vaadin.com/forum/thread/16038356/grid-8-without-bean-class

5

Thanks for your reply.

Because I add grid dynamically to the view by clicking a button, it adds every times I click a button a new grid to the end of view.

I want just replace the old grid with the new or remove the old one and add new one.

How can I achieve this?

btnSearch.addClickListener(event -> {
            String query = textArea.getValue();
            updateGridContent(query);
        });

    private void updateContent(String query) {
        List<Map<String, Object>> rows = null;
        try {
            rows = queryViewService.retrieveRows(query);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        // Create the grid and set its items
        Grid<Map<String, Object>> grid2 = new Grid<>();
        grid2.setItems(rows); // rows is the result of retrieveRows

        // Add the columns based on the first row
        Map<String, Object> s = rows.get(0);
        for (Map.Entry<String, Object> entry : s.entrySet()) {
            grid2.addColumn(h -> h.get(entry.getKey())).setHeader(entry.getKey());
        }

        // Add the grid to the page
        add(grid2);
    }
6

Something like this should work.

  private Grid<Map<String, Object>> grid2 ;
  
   private void updateContent(String query) {
        List<Map<String, Object>> rows = null;
        try {
            rows = queryViewService.retrieveRows(query);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        // Create the grid and set its items
		if (grid2 != null) {
		  remove(grid2);
		}
        grid2 = new Grid<>();
        grid2.setItems(rows); // rows is the result of retrieveRows

        // Add the columns based on the first row
        Map<String, Object> s = rows.get(0);
        for (Map.Entry<String, Object> entry : s.entrySet()) {
            grid2.addColumn(h -> h.get(entry.getKey())).setHeader(entry.getKey());
        }

        // Add the grid to the page
        add(grid2);
    }

If the columns are always the same you can also just run grid2.setItems(rows)