This is really nice and something that we wanted to have in Cayenne for years!
It plugs a huge hole in the query capabilities and will be a major 4.0 feature.
I am looking forward to upgrading a few of my projects and rewriting a bunch of
queries using the new API.
Andrus
> On Jan 6, 2017, at 4:08 PM, Nikita Timofeev <ntimof...@objectstyle.com> wrote:
>
> Hi all,
>
> I would like to present new Cayenne feature in development to you: API
> for SQL functions (both aggregate and non aggregate).
>
> When it will be completed it will be possible to use functions in
> select queries with pure API calls without writing any custom SQL or
> EJBQL queries.
> You'll be able to do something like this (though it may be not a final
> version of API):
>
> long totalCount = ObjectSelect.query(Artist.class)
> .column(Artist.ARTIST_COUNT)
> .selectOne(context);
>
> Or make even more complex queries with HAVING clause:
>
> List<Object[]> result = ObjectSelect.query(Artist.class)
> .columns(artistCount,
> minSalary, namePart)
>
> .where(Artist.DATE_OF_BIRTH.lt(new Date()))
> .or(...) // additional
> condition in WHERE clause
> .having(namePart.like("P%"))
> .or(...) // additional
> condition in HAVING clause
> .select(context);
>
> for(Object[] r : result) {
> long count = (long)r[0];
> int min = (int)r[1];
> String name = (String)r[2];
> }
>
> All this queries will rely on custom properties that can be created
> ad-hoc for specific query:
>
> Expression substrExp =
> FunctionExpressionFactory.substringExp(Artist.ARTIST_NAME.path(), 10,
> 15);
> Property<String> namePart = Property.create("namePart", substrExp,
> String.class);
>
> or be defined directly in a model class:
>
> public static final Property<Long> ARTIST_COUNT =
> Property.create("artistCount", FunctionExpressionFactory.countExp(),
> Long.class);
>
> You can find additional information and track progress in Apache JIRA:
> https://issues.apache.org/jira/browse/CAY-2187
>
> And of course any feedback will be really appreciated!
>
> --
> Best regards,
> Nikita Timofeev
