Filter Grid with SQLContainer doesn't work

When I use a SQLConatainer instead a JPAContainer the filter doesn’t work.

Could anyone help me?

import java.sql.SQLException;

import com.vaadin.data.util.BeanItem;
import com.vaadin.data.util.filter.SimpleStringFilter;
import com.vaadin.data.util.sqlcontainer.SQLContainer;
import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.FreeformQuery;
import com.vaadin.ui.Grid;
import com.vaadin.ui.Grid.HeaderCell;
import com.vaadin.ui.Grid.HeaderRow;
import com.vaadin.ui.TextField;
import com.vaadin.ui.UI;
import com.vaadin.ui.VerticalLayout;
import com.vaadin.ui.Window;

import.Config;
import .entity.Firma;

public class SucheStoff extends Window
{

private final VerticalLayout form = new VerticalLayout();
Grid mainGrid = new Grid();

//JPAContainer firmaContainer = JPAContainerFactory.makeReadOnly(Firma.class, Config.PERSISTENCE_UNIT);

SQLContainer container;

public SucheStoff()
{
init();

SimpleJDBCConnectionPool connPool;
connPool = null;
try
{
connPool = new SimpleJDBCConnectionPool(Config.driverName, Config.connectionUri, Config.userName, Config.password);
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}

try
{
container = new SQLContainer(new FreeformQuery(“Select firma.FIRMA, werk.BEZEICHNUNG, werk.KUERZEL From firma Inner Join werk On werk.FIRMAID_ID = firma.ID”,connPool));

} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}

mainGrid.setContainerDataSource(container);
mainGrid.setEditorEnabled(false);

// Create a header row to hold column filters
HeaderRow filterRow = mainGrid.appendHeaderRow();

// Set up a filter for all columns
for (Object pid : mainGrid.getContainerDataSource().getContainerPropertyIds())
{
HeaderCell cell = filterRow.getCell(pid);

// Have an input field to use for filter
TextField filterField = new TextField();
filterField.setColumns(8);

// Update filter When the filter input is changed
filterField.addTextChangeListener(change →
{
// filterField.addTextChangeListener(FieldEvents.TextChangeListener
// listener() {
// Can’t modify filters so need to replace
container.removeContainerFilters(pid);

// (Re)create the filter if necessary
if (!change.getText().isEmpty())
container.addContainerFilter(new SimpleStringFilter(pid, change.getText(), true, false));
});
cell.setComponent(filterField);
}
}

public void init()
{

setModal(true);
setClosable(true);
setContent(form);
setResizable(false);
setCaption(“Suche nach Stoffen”);

form.setSizeUndefined();
form.setMargin(false);
form.setSpacing(true);

form.addComponent(mainGrid);

UI.getCurrent().addWindow(this);
}
}

It’s not the SQLContainer, it’s the FreeformQuery causing problems. By default the FreeformQuery has no logic for Filtering and Sorting. You can nevertheless implement this yourself with a FreeformQueryDelegate (there are some examples for this on this forum and one in the Book I think).

You can also do the Filtering and Sorting custom outside of the Query by modifying/recreating the FreeformQuery each time you filter/sort/… and set the new one as the SQLContainer’s Query delegate

Another way would be to use a TableQuery instead though as you’re using a Join. Maybe you could create a View on the DB side and then use the slightly modified TableQuery which was posted on this forum a while ago to access it.

If that’s not an option maybe creating two SqlContainers and linking them with setReferenceItem (or something along those lines) might be good enough for you

I wouldn’t create for all Queries an extra view.

Does another container exist, where I could add an SQL statement and have the option to filter?

No there probably isn’t. Mainly because the actual Filtering and Sorting is, as i said done by the Query Delegate and as it stands the current ones are TableQuery and FreeformQuery when it comes to SQL.
You have to think about it this way: the standard Query has no idea how to create the Filter or Sort SQL required for your Join. TableQuery only knows those things for basic Tables (and Views with some slight modifications) as there you don’t have to differ between which Table the property comes from.

Your best bet would probably be a custom FreeformStatementDelegate ( I think it’s called)

Okay, thank you, then I think the best way is to create a view.

I have create someone in my mysql database, but when I use them with tablequery I get the following error:

java.lang.IllegalArgumentException: Primary key constraints have not been defined for the table “vFirma”. Use FreeFormQuery to access this table.

An at view I could not add a primary key.

Have anyone an idea to solve this problem?


container = new SQLContainer(new TableQuery(“vFirma”, connPool));

Like I said. You need to use a modified version of the TableQuery.
What you have to change can be found here:
https://vaadin.com/forum#!/thread/10244484

Thank you very much Marius, you are the best, now the errors with the TableQuery are solved.

When I call the view in MYSQL I get some data, when I call it in Java I only see the header names but no data.

Is this a problem from the cast or what is my mistake?
See attachment, I have called the TableQuery → ViewQuery
21023.java (2.95 KB)

Sorry the problem is solved, I have connected to the empty database.

Thank you very much Marius, you help me so much!

I think there is a bug, because the data that are visible in the grid and on other tools are not the same.

Have a look at the attachment and there at the column sprache, in the vaadin grid are two lines with
en
but there should one with
en
and one with
de


CREATE VIEW vSucheStoff2 AS
Select
matnr.MatNr,
stoff.Handelsname,
dokumententyp.Typ,
sprache.Sprache,
dokument.Datei,
stoff.ID
From
stoff Inner Join
dokument On dokument.StoffID = stoff.ID Inner Join
dokumententyp On dokumententyp.ID = dokument.TypID Inner Join
sprache On sprache.ID = dokument.SpracheID Inner Join
matnr On stoff.ID = matnr.StoffID
Where
(dokumententyp.Typ = ‘Technische Dokumentation’ And
stoff.ID = 269) Or
(dokumententyp.Typ = ‘Sicherheitsdatenblatt’ And
stoff.ID = 269)
Order By
stoff.Handelsname

21034.png

I don’t know whether this has to do with your data, your view or maybe some weird display-thing in the Grid but it seems like it shows the en data-entry two times (seeing as the pdf Link also is the same while the de one doesn’t have an e at the end.
You could check if there are some similar data-entries in your DB though it looks like you already did that. Other than that…i don’t really know…haven’t encountered this issue.
You could iterate through your container after creating it and log the Values to the Console to see if it already happens in the Container or if it’s a Grid “bug”.