My most common use-cases for SQLTemplate are either
a) To express an idiom that doesn't exist via other API's
b) For performance purposes.
One example of b: Several years ago (long before EJBQL support), I essentially
had to build a pivot table, and to do it through the object api wound up being
incredibly expensive. So I wrote some highly tuned and optimized queries and
used SQLTemplate to fetch the data as data rows.
Outside of a and b, I typically stick with the object-based APIs.
Robert
On May 26, 2013, at 5/262:41 PM , Andrus Adamchik <and...@objectstyle.org>
wrote:
> 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
>>>>>
>>>>>
>>>>>
>>>
>>>
>
