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] [Updated] (CAY-2187) Support for the scalar and aggregate SQL functions in ObjectSelect API
Date Thu, 05 Jan 2017 06:53:58 GMT

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

Nikita Timofeev updated CAY-2187:
---------------------------------
    Description: 
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:
        Expression paintingCountExp = FunctionExpressionFactory.countExp(Artist.PAINTING_ARRAY.path());
        Property<Long> paintingCountProperty = Property.create("paintingCount", paintingCountExp,
Long.class);
        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}

  was:
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_NAME.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:
        Expression paintingCountExp = FunctionExpressionFactory.countExp(Artist.PAINTING_ARRAY.path());
        Property<Long> paintingCountProperty = Property.create("paintingCount", paintingCountExp,
Long.class);
        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}


> 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
>
> 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:
>         Expression paintingCountExp = FunctionExpressionFactory.countExp(Artist.PAINTING_ARRAY.path());
>         Property<Long> paintingCountProperty = Property.create("paintingCount",
paintingCountExp, Long.class);
>         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