Use filter with custom mysql

Support
#1

Hi,
I create a custom datasource with my custom mysql

select os, sex from tblUser;

but i dont know how to use this custom datasource with filter
pls send me demo or guide step by step
Currently I add this cutom datasource to a table and set filter for this table, but filter not working
Thanks for your support,

1

2

#2

You cannot directly use the generic Filter component with a custom datasource. Filter is designed to add conditions to a JPQL of a standard collection datasource.

#3

So I how to paging table with a custom datasource.
Thanks for your support,

#4

Paging with a custom datasource can be implemented as follows.

Suppose you want to display data from this table directly, bypassing CUBA ORM:


create table FOO (
    NUM integer not null primary key,
    NAME varchar(100)
)

Create a non-persistent entity Foo:


@MetaClass(name = "customdatasource$Foo")
public class Foo extends AbstractNotPersistentEntity {
    @MetaProperty
    protected Integer num;

    @MetaProperty
    protected String name;
...
}

Create a service returning a list and a count of Foo:


@Service(FooService.NAME)
public class FooServiceBean implements FooService {
    @Inject
    private Persistence persistence;

    @Override
    public List<Foo> getFooList(int maxResults, int firstResult) {
        String sql = String.format("select NUM, NAME from FOO order by NUM limit %d offset %d", maxResults, firstResult);

        QueryRunner runner = new QueryRunner(persistence.getDataSource());
        try {
            return runner.query(sql, new ResultSetHandler<List<Foo>>() {
                @Override
                public List<Foo> handle(ResultSet rs) throws SQLException {
                    ArrayList<Foo> list = new ArrayList<>();
                    while (rs.next()) {
                        Foo foo = new Foo();
                        foo.setNum(rs.getInt("NUM"));
                        foo.setName(rs.getString("NAME"));
                        list.add(foo);
                    }
                    return list;
                }
            });
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int getFooCount() {
        QueryRunner runner = new QueryRunner(persistence.getDataSource());
        try {
            Object[] row = runner.query("select count(*) as _count from FOO", new ArrayHandler());
            return ((Number) row[0]).intValue();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

Create a custom datasource, implement getEntities and override getCount():


public class FooCollectionDatasource extends CustomCollectionDatasource<Foo, UUID> {

    private FooService fooService = AppBeans.get(FooService.class);

    @Override
    protected Collection<Foo> getEntities(Map<String, Object> params) {
        return fooService.getFooList(maxResults, firstResult);
    }

    @Override
    public int getCount() {
        return fooService.getFooCount();
    }
}

In a screen, define the datasource and add element to the table:


<window xmlns="http://schemas.haulmont.com/cuba/window.xsd"
        caption="Custom datasource with paging"
        class="com.company.customdatasource.web.screens.FooScreen"
        messagesPack="com.company.customdatasource.web.screens">
    <dsContext>
        <collectionDatasource id="foosDs"
                              allowCommit="false"
                              class="com.company.customdatasource.entity.Foo"
                              datasourceClass="com.company.customdatasource.web.screens.customdatasources.FooCollectionDatasource"/>
    </dsContext>
    <layout>
        <table height="100%"
               width="100%">
            <columns>
                <column id="num"/>
                <column id="name"/>
            </columns>
            <rows datasource="foosDs"/>
            <rowsCount/>
        </table>
    </layout>
</window>

In the screen controller, initialize the datasource with a desired page size:


public class FooScreen extends AbstractWindow {

    @Inject
    private FooCollectionDatasource foosDs;

    @Override
    public void init(Map<String, Object> params) {
        foosDs.setMaxResults(50);
    }
}

See the full example project here: GitHub - knstvk/custom-datasource: This example illustrates how to create and use custom collection datasource in the CUBA Platform

1 Like