Geek asks and answers

Java gateway dataprovider for large tables

I am developing a gate interface for an EJB3 Java application with a MySQL database. A website basically presents forms and shows tables. For these tables, I prefer to create IDataProviders that work very well with AjaxPagingNavigation. But I have some tables that are quite large, and the IDataProvider size function becomes a little complicated to implement. I usually use a counter for a query, and this is done in most cases, but I have some large tables where the counter takes too much time (3s +). Now there are several options that I can use, but I'm not very happy with using any of them, so why am I asking anyone know an elegant solution to implement the size () function for a large table?

The first option I found on Google is to use a subquery with limited results to execute score a. But since I mostly use named queries and EJB3, it seems hacky, and it can be difficult to maintain if something changes.

The second option is to use a ListDataView and just make a limited query in the table and omit the need for a large query for counting. This solution is still my favorite, but not the most pleasant, since it (almost) always gets the maximum number of records. I also have to choose between storing this List object or re-querying the database between queries.

The last parameter I found was a fake size option. I did not implement this, but I could force the IDataProvider size () function to return something like pagesize + 1. This leads to several other problems, such as the presence of pages with page records. There are checks to catch them, but this is also messy.

Does anyone know an elegant solution to use IDataProvider at the gate to display paged tables of large database tables?

Thanks a lot, Martin

+3
source share
3 answers

I implemented a grid with a data provider that only needs an iterator, without size information - its called IterableGridView

Here is the code: https://github.com/maciejmiklas/cyclop/tree/master/cyclop-wicket-components

Iterable Grid View Wicket GridView, IDataProvider,   IterableDataProvider. javas- - , .

final List<String> myGridData = new ArrayList<>();
myGridData.add("value 1");
myGridData.add("value 2");

IterableDataProvider<String> iterableDataProvider = new IterableDataProvider<String>(10) {
    @Override
    protected Iterator<String> iterator() {
        return myGridData.iterator();
    }

    @Override
    public IModel<String> model(String s) {
        return Model.of(s);
    }

    @Override
    public void detach() {
    }
};

IterableGridView<String> myGrid = new IterableGridView<String>("myGrid", iterableDataProvider) {
    @Override
    protected void populateEmptyItem(Item<String> item) {
        item.add(new Label("myValue"));
    }

    @Override
    protected void populateItem(Item<String> item) {
        item.add(new Label("myValue", item.getModelObject()));
    }
};

add(myGrid);

myGrid.setItemsPerPage(10);

// you have to use custom pager and not AjaxPagingNavigator
IterablePagingNavigator pager = new IterablePagingNavigator("rowNamesListPager", rowNamesList);
resultTable.add(pager);
+1

quickview? size , .

0

"" IDataProvider, , - JDBC, :

  • to execute select [skip columns] count (*) automatically where ... under the size () wicket method

  • to execute a prepared (prepared in a human sense) MSSQL query, which server does not have LIMIT / OFSET (in older versions) under an iterator (), I use a common trick with ROW_NUMBER ()

The second is not important in your case, because MySQL has LIMIT / OFFSET

The code is ugly, specific to my integration goals, etc., but it works, therefore it is a closed source;) Querying is a kind of "criterion" with string substitutions, the same criteria are used twice.

0
source

More articles:


All Articles