I think the example in the unit tests is just not too representative of how
this will be used in real life. If all I need is "SELECT * FROM ARTIST", I'd
use SelectQuery, not raw SQL. Usually SQLTemplate (and now SQLSelect) is a
query of a last resort.
An example from my customer apps... Occasionally I'd have a situation when in
addition to the main table ARTIST, I have a set of views that fetch data
structures compatible with ARTIST, but do it in some really twisted way (with
unions, subqueries, etc.). So 90% of the time I'd do "new
SelectQuery(Artist.class)", and in the remaining 10% it will be "new
SQLTemplate(Artist.class, "SELECT * FROM ARTIST_VIEW1")".
If others are mostly using SQLTemplate to get a count or another aggregate
function, we should just build utilities around SelectQuery to make aggregates
possible / easy (kind of like you did already).
So I'd like to hear from everyone what are the most typical use cases for
SQLTemplate now?
Andrus
On May 26, 2013, at 9:13 PM, Michael Gentry <mgen...@masslight.net> wrote:
> That's cool, but it still seems like there is too much duplication for SQL
> queries.
>
> The main reason to pass in Artist.class, I think, is to know which objects
> to create coming back, which means you shouldn't do:
>
> "SELECT NAME FROM ARTIST"
>
> if you are returning Artist.class (you want to fetch all the columns).
> That type of query is only valid for data rows, right?
>
> Also, I still don't like having to specify the artist twice. So if doing a
> data row query, maybe:
>
> SQLSelect.dataRowsOn(Artist.class).columns(List<String> or
> String...).where("...").fetch(context);
>
> In this example, Cayenne can look up the correct table name for
> Artist.class and automatically build it into the "SELECT ... FROM ARTIST"
> for you. And no need to write "select" three times, either -- I've
> replaced your select(context) above with fetch(context). Also, have two
> methods for columns(), one which takes a List<String> and one that takes a
> varargs parameter. If you omit the columns, it can default to "*"
> automatically. I think this would provide more type safety, such as:
>
> SQLSelect.dataRowsOn(Artist.class).columns(ARTIST.NAME_PROPERTY).where("...").select(context);
>
> Of course, there should also be a where() method accepts an Expression, I
> think.
>
> Thoughts?
>
> Thanks,
>
> mrg
>
>
>
>
>
> On Sun, May 26, 2013 at 12:58 PM, Andrus Adamchik
> <and...@objectstyle.org>wrote:
>
>> Absolutely. I was planning a model-based SQL building as the next step for
>> SQLSelect. The current version (that only took me maybe an hour to write)
>> streamlines casting the result to something that you need, binding
>> parameters, etc. I haven't looked at the SQL "payload" part of it yet. My
>> note below about "other methods for building SQL chunks based on Cayenne
>> mapping, such as "allColumns()" is essentially about doing something like
>> you suggest.
>>
>> In general designing this fluent API requires a bit different mindset
>> compared to designing "canonical" API that we have. Will need to better
>> wrap my head around it.
>>
>> A.
>>
>>
>> On May 26, 2013, at 3:19 PM, Michael Gentry <mgen...@masslight.net> wrote:
>>> Hi Andrus,
>>>
>>> I may be missing something, but it looks like you'd have to do something
>>> such as:
>>>
>>> SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ...");
>>>
>>> In most cases, you are always going to select "*" I think and artist is
>>> duplicated. Why not something more along the lines of:
>>>
>>> SQLSelect.on(Artist.class).where("...");
>>>
>>> Thanks,
>>>
>>> mrg
>>>
>>>
>>>
>>> On Fri, May 24, 2013 at 11:45 AM, Andrus Adamchik <
>> and...@objectstyle.org>wrote:
>>>
>>>> https://issues.apache.org/jira/browse/CAY-1828
>>>>
>>>>
>> http://svn.apache.org/repos/asf/cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/SQLSelect.java
>>>>
>>>> So SQLTemplate annoyed me enough to wrap it as a quick experiment with
>>>> fluent APIs. I guess this is the direction where the rest of the queries
>>>> should be going. Aside from chaining query configuration parameters,
>> there
>>>> are "select" and "selectOne" methods that allow to take the chain to the
>>>> logical conclusion - the resulting objects.
>>>>
>>>> I can think of other methods for building SQL chunks based on Cayenne
>>>> mapping, such as "allColumns()", etc.
>>>>
>>>> What do you think? Also method naming criticism is accepted. E.g. I am
>> not
>>>> sure that changing "setPageSize()" to "pageSize()" was such a great
>> idea.
>>>>
>>>> Andrus
>>>>
>>>>
>>>>
>>
>>
