FAQ
Cayenne has very nice functionality for paging large lists of objects, so that the list is mostly hollow objects which are retrieved in a lazy fashion as they are accessed.

This works well to fetch 100,000 contacts in a list and only draw the ones visible to the user as they scroll. What works less well is the common use case of a list view where you might only want to show several attributes of a large record.

So a list of contacts might show just:

* firstName
* lastName
* totalSales

To fetch this data requires a query on contact with a prefetch to invoices. Lots of data being loaded from two or more tables and potentially a bit slow.

Some options:

1. Use DataRows. This is simple, but you lose some of the nice modelling/entity features from Cayenne. You have to type all the columns yourself.

2. Create a view in the database with just three columns (plus a PK) and create a separate read-only Cayenne model which maps to that view. But now you are hardcoding your application to a specific database and changing the representation is hard.

I want something half way between the two. SQLtemplate to fetch only the columns or aggregates I need, but mapped to some sort of read-only lightweight Cayenne entity.


Has anyone tried something like this?


Ari



--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A

Search Discussions

  • Dollj at Apr 15, 2015 at 6:44 pm
    Hi Ari

    Not sure if this is what you are looking for, but this is what I did once:

    1. Create a dbEntity in the modeler that has all the columns you are
    interested in from the various tables. (So this dbEntity is more like a view
    as it doesn't represent a real table.)
    2. Create a read only ObjEntity in the modeler based on the 'fake'
    dbEntity.
    3. Create a SQLTemplate query that will retrieve the data from the various
    tables.
    4. Perform the query as usual (no datarows)

    For example I did something like:

    SQLTemplate reportTemplateQry = new SQLTemplate
    (
         ReportTemplateLine.class,
         "select A.NAME, B.ORDER from RPT_GROUPS as B, PRT_TEMPLATE as A "
         +"where .... "
    );

    List<ReportTemplateLine> lines = ....performQuery( reportTemplateQry );


    Regards
    Jurgen




    -----Original Message-----
    From: Aristedes Maniatis
    Sent: Wednesday, April 15, 2015 11:20 AM
    To: user@cayenne.apache.org
    Subject: lightweight object fetching

    Cayenne has very nice functionality for paging large lists of objects, so
    that the list is mostly hollow objects which are retrieved in a lazy fashion
    as they are accessed.

    This works well to fetch 100,000 contacts in a list and only draw the ones
    visible to the user as they scroll. What works less well is the common use
    case of a list view where you might only want to show several attributes of
    a large record.

    So a list of contacts might show just:

    * firstName
    * lastName
    * totalSales

    To fetch this data requires a query on contact with a prefetch to invoices.
    Lots of data being loaded from two or more tables and potentially a bit
    slow.

    Some options:

    1. Use DataRows. This is simple, but you lose some of the nice
    modelling/entity features from Cayenne. You have to type all the columns
    yourself.

    2. Create a view in the database with just three columns (plus a PK) and
    create a separate read-only Cayenne model which maps to that view. But now
    you are hardcoding your application to a specific database and changing the
    representation is hard.

    I want something half way between the two. SQLtemplate to fetch only the
    columns or aggregates I need, but mapped to some sort of read-only
    lightweight Cayenne entity.


    Has anyone tried something like this?


    Ari



    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Michael Gentry at Apr 16, 2015 at 4:31 pm
    Hi Ari,

    We chose option 2 and created a view with a read-only modeler mapping.
    This was especially more useful for us since we had quite a few joins we
    were flattening out for putting into an overview table and we kept all that
    activity in the database instead of round-trips between our server and the
    database.

    I was going to mention creating "subset" entities, too, but Jurgen already
    suggested that.

    mrg

    On Wed, Apr 15, 2015 at 5:20 AM, Aristedes Maniatis wrote:

    Cayenne has very nice functionality for paging large lists of objects, so
    that the list is mostly hollow objects which are retrieved in a lazy
    fashion as they are accessed.

    This works well to fetch 100,000 contacts in a list and only draw the ones
    visible to the user as they scroll. What works less well is the common use
    case of a list view where you might only want to show several attributes of
    a large record.

    So a list of contacts might show just:

    * firstName
    * lastName
    * totalSales

    To fetch this data requires a query on contact with a prefetch to
    invoices. Lots of data being loaded from two or more tables and potentially
    a bit slow.

    Some options:

    1. Use DataRows. This is simple, but you lose some of the nice
    modelling/entity features from Cayenne. You have to type all the columns
    yourself.

    2. Create a view in the database with just three columns (plus a PK) and
    create a separate read-only Cayenne model which maps to that view. But now
    you are hardcoding your application to a specific database and changing the
    representation is hard.

    I want something half way between the two. SQLtemplate to fetch only the
    columns or aggregates I need, but mapped to some sort of read-only
    lightweight Cayenne entity.


    Has anyone tried something like this?


    Ari



    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Andrus Adamchik at Apr 16, 2015 at 4:40 pm
    Use EJBQLQuery. It supports fetching individual columns (and does not require SQL).

    We are working to support this in ObjectSelect as well.

    Andrus
    On Apr 15, 2015, at 12:20 PM, Aristedes Maniatis wrote:

    Cayenne has very nice functionality for paging large lists of objects, so that the list is mostly hollow objects which are retrieved in a lazy fashion as they are accessed.

    This works well to fetch 100,000 contacts in a list and only draw the ones visible to the user as they scroll. What works less well is the common use case of a list view where you might only want to show several attributes of a large record.

    So a list of contacts might show just:

    * firstName
    * lastName
    * totalSales

    To fetch this data requires a query on contact with a prefetch to invoices. Lots of data being loaded from two or more tables and potentially a bit slow.

    Some options:

    1. Use DataRows. This is simple, but you lose some of the nice modelling/entity features from Cayenne. You have to type all the columns yourself.

    2. Create a view in the database with just three columns (plus a PK) and create a separate read-only Cayenne model which maps to that view. But now you are hardcoding your application to a specific database and changing the representation is hard.

    I want something half way between the two. SQLtemplate to fetch only the columns or aggregates I need, but mapped to some sort of read-only lightweight Cayenne entity.


    Has anyone tried something like this?


    Ari



    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Andrus Adamchik at Apr 16, 2015 at 5:35 pm
    Use EJBQLQuery. It is old style and requires String concatenation, but supports fetching individual columns and does not require SQL:

    EJBQLQuery q =
        new EJBQLQuery("SELECT p.firstName, p.lastName, sum(s.sales) FROM Person p INNER JOIN p.sale s");

    We are working to support this in ObjectSelect as well.

    Andrus
    On Apr 15, 2015, at 12:20 PM, Aristedes Maniatis wrote:

    Cayenne has very nice functionality for paging large lists of objects, so that the list is mostly hollow objects which are retrieved in a lazy fashion as they are accessed.

    This works well to fetch 100,000 contacts in a list and only draw the ones visible to the user as they scroll. What works less well is the common use case of a list view where you might only want to show several attributes of a large record.

    So a list of contacts might show just:

    * firstName
    * lastName
    * totalSales

    To fetch this data requires a query on contact with a prefetch to invoices. Lots of data being loaded from two or more tables and potentially a bit slow.

    Some options:

    1. Use DataRows. This is simple, but you lose some of the nice modelling/entity features from Cayenne. You have to type all the columns yourself.

    2. Create a view in the database with just three columns (plus a PK) and create a separate read-only Cayenne model which maps to that view. But now you are hardcoding your application to a specific database and changing the representation is hard.

    I want something half way between the two. SQLtemplate to fetch only the columns or aggregates I need, but mapped to some sort of read-only lightweight Cayenne entity.


    Has anyone tried something like this?


    Ari



    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedApr 15, '15 at 9:22a
activeApr 16, '15 at 5:35p
posts5
users4
websitecayenne.apache.org

People

Translate

site design / logo © 2021 Grokbase