cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nikita Timofeev (JIRA)" <j...@apache.org>
Subject [jira] [Closed] (CAY-2187) Support for the scalar and aggregate SQL functions in ObjectSelect API
Date Thu, 26 Jan 2017 12:07:24 GMT

     [ https://issues.apache.org/jira/browse/CAY-2187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Nikita Timofeev closed CAY-2187.
--------------------------------
    Resolution: Fixed

> Support for the scalar and aggregate SQL functions in ObjectSelect API
> ----------------------------------------------------------------------
>
>                 Key: CAY-2187
>                 URL: https://issues.apache.org/jira/browse/CAY-2187
>             Project: Cayenne
>          Issue Type: Task
>          Components: Core Library
>    Affects Versions: 4.0.M5
>            Reporter: Nikita Timofeev
>            Assignee: Nikita Timofeev
>             Fix For: 4.0.M5
>
>
> For now only options to use SQL functions (including aggregate) are:
>  # EJBQL
>  # plain SQL
> Both of it lack any type-checking or support from the API side as user needs to wright
and debug queries with plain text.
> So Cayenne needs API for that functionality in order to have more control.
> It is suggested to add following features and new API in ObjectSelect query:
> # New expressions for function calls and new factory as convenient method to create them:
{code} 
>         Expression substrExp = FunctionExpressionFactory.substringExp(Artist.ARTIST_NAME.path(),
10, 15);     
>         Expression modExp = FunctionExpressionFactory.modExp(Artist.ARTIST_SALARY.path(),
10);
> {code}
> # Enhanced Property class with Expression support and explicit type,
> they can be declared in persistent object like normal Cayenne properties or adhoc for
specific query: {code}
>         Property<String> namePart = Property.create("namePart", substrExp, String.class);
>         Property<Long> artistCount = Property.create("artistCount", countExp, Long.class);
>         Property<Integer> minSalary = Property.create("minSalary", minExp, Integer.class);
> {code}
> # New methods in ObjectSelect for specifying return columns for the query: {code}   
       
>         // Single column
>         long totalCount = ObjectSelect.query(Artist.class)
>                             .column(Artist.ARTIST_COUNT)
>                             .selectOne(context);
>                             
>         // Several columns
>         List<Object[]> result = ObjectSelect.query(Artist.class)
>                                     .columns(artistCount, minSalary, namePart)
>                                     .select(context);
>         for(Object[] r : result) {
>             long count = (long)r[0];
>             int min = (int)r[1];
>             String namePart = (String)r[2];
>         }
> {code}
> # New having() method in ObjectSelect: {code}
>         // Full query example:
>         List<Object[]> result = ObjectSelect.query(Artist.class)
>                 // result
>                 .columns(artistCount, minSalary, namePart)
>                 // WHERE clause
>                 .where(Artist.DATE_OF_BIRTH.lt(new Date())) 
>                 // additional condition in WHERE clause
>                 .or(...)                      
>                 // HAVING clause
>                 .having(namePart.like("P%"))                
>                 // additional condition in HAVING clause
>                 .or(...)                                    
>                 .select(context);
>         for(Object[] r : result) {
>             long count = (long)r[0];
>             int min = (int)r[1];
>             String name = (String)r[2];
>         }
>         // Aggregate on toMany relationship:
>         Property<Long> paintingCountProperty = Artist.PAINTING_ARRAY.count();
>         List<Object[]> result2 = ObjectSelect.query(Artist.class)
>                 .columns(Artist.ARTIST_NAME, paintingCountProperty)
>                 .having(paintingCountProperty.gt(10L))
>                 .select(context);
>         for(Object[] r : result2) {
>             String artistName = (String)r[0];
>             long paintingCount = (long)r[1];
>         }
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message