You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Emerson Castañeda <em...@gmail.com> on 2012/03/14 21:22:58 UTC
Howto Include Time/Date Functions in Query Expressions
HI everyone
I have a table with a timestamp field, so I'm thinking about how to write
the next query without define a store procedure, but directly from my java
code:
select *
from table1
where
id = 1 and
extract(day from timestampField)='04' and
extract(month from timestampField)='04' and
extract(year from timestampField)='2009'
I'd like to know if that is possible using cayenne, maybe some thing like
this?
SelectQuery query = new SelectQuery(Table1.class);
query.andQualifier(<??EXPRESSION???>)
OR
SelectQuery query = new SelectQuery(Table1.class);
query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
Table1.timestampproperty, "%"));
Thank you for your time
EMERSON
Re: Howto Include Time/Date Functions in Query Expressions
Posted by Mike Kienenberger <mk...@gmail.com>.
I am not an expert in this area. I suspect it depends on your
database and environment.
My guess is that a view that only added those three columns would have
very little impact on performance. The primary difference is that
you've moved the conversion of those items from the application to the
database side. But converting a timestamp to a day, time, year is a
pretty basic database function.
You'll be transferring 3 additional columns over the network on
queries, but again it's pretty negligible.
So, yes, it might have a minor performance impact, or it might have
none. An Oracle View would likely have no impact. Can't speak for
views on other databases.
The real question is whether your code maintenance is going to
increase by having to use SQLTemplates instead of native Cayenne
objects.
2012/3/15 Emerson Castañeda <em...@gmail.com>:
> Hi Mike
>
> My context: I am handling a table with 50 millions of rows and its size is
> increasing up every day some thousands. So, my question goes around
> performance of the view's solution that you propose.
>
> Do you think view performance will be better than using SQLTemplate
> queries?
>
> Thanks
>
> EMERSON
>
> On Thu, Mar 15, 2012 at 3:07 PM, Mike Kienenberger <mk...@gmail.com>wrote:
>
>> Depending on your database and usage, another option is to set up a
>> view and let the database create derived columns for these, then you
>> can treat them as regular Cayenne data object fields.
>>
>> 2012/3/14 Emerson Castañeda <em...@gmail.com>:
>> > HI everyone
>> >
>> > I have a table with a timestamp field, so I'm thinking about how to
>> write
>> > the next query without define a store procedure, but directly from my
>> java
>> > code:
>> >
>> > select *
>> > from table1
>> > where
>> > id = 1 and
>> > extract(day from timestampField)='04' and
>> > extract(month from timestampField)='04' and
>> > extract(year from timestampField)='2009'
>> >
>> > I'd like to know if that is possible using cayenne, maybe some thing like
>> > this?
>> >
>> >
>> > SelectQuery query = new SelectQuery(Table1.class);
>> > query.andQualifier(<??EXPRESSION???>)
>> >
>> > OR
>> >
>> > SelectQuery query = new SelectQuery(Table1.class);
>> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
>> > Table1.timestampproperty, "%"));
>> >
>> >
>> > Thank you for your time
>> >
>> > EMERSON
>>
Re: Howto Include Time/Date Functions in Query Expressions
Posted by Michael Gentry <mg...@masslight.net>.
If the view is backing to 50m records, then it would still have to do
a full table scan without indexes.
On Thu, Mar 15, 2012 at 12:42 PM, Mike Kienenberger <mk...@gmail.com> wrote:
> In this particular case, since the view is just doing data conversion,
> I don't think you'd even need indexes, would you?
>
> On Thu, Mar 15, 2012 at 12:35 PM, Michael Gentry <mg...@masslight.net> wrote:
>> Hi Emerson,
>>
>> I'm pretty sure a view will make no difference. The best way to
>> improve performance is to have proper indexes (which a view could also
>> utilize). We have a similar-sized database and query times went from
>> over 1 minute to a few milliseconds once we added an appropriate
>> index.
>>
>> mrg
>>
>>
>> On Thu, Mar 15, 2012 at 11:29 AM, Emerson Castañeda <em...@gmail.com> wrote:
>>> Hi Mike
>>>
>>> My context: I am handling a table with 50 millions of rows and its size is
>>> increasing up every day some thousands. So, my question goes around
>>> performance of the view's solution that you propose.
>>>
>>> Do you think view performance will be better than using SQLTemplate
>>> queries?
>>>
>>> Thanks
>>>
>>> EMERSON
>>>
>>> On Thu, Mar 15, 2012 at 3:07 PM, Mike Kienenberger <mk...@gmail.com>wrote:
>>>
>>>> Depending on your database and usage, another option is to set up a
>>>> view and let the database create derived columns for these, then you
>>>> can treat them as regular Cayenne data object fields.
>>>>
>>>> 2012/3/14 Emerson Castañeda <em...@gmail.com>:
>>>> > HI everyone
>>>> >
>>>> > I have a table with a timestamp field, so I'm thinking about how to
>>>> write
>>>> > the next query without define a store procedure, but directly from my
>>>> java
>>>> > code:
>>>> >
>>>> > select *
>>>> > from table1
>>>> > where
>>>> > id = 1 and
>>>> > extract(day from timestampField)='04' and
>>>> > extract(month from timestampField)='04' and
>>>> > extract(year from timestampField)='2009'
>>>> >
>>>> > I'd like to know if that is possible using cayenne, maybe some thing like
>>>> > this?
>>>> >
>>>> >
>>>> > SelectQuery query = new SelectQuery(Table1.class);
>>>> > query.andQualifier(<??EXPRESSION???>)
>>>> >
>>>> > OR
>>>> >
>>>> > SelectQuery query = new SelectQuery(Table1.class);
>>>> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
>>>> > Table1.timestampproperty, "%"));
>>>> >
>>>> >
>>>> > Thank you for your time
>>>> >
>>>> > EMERSON
>>>>
Re: Howto Include Time/Date Functions in Query Expressions
Posted by Mike Kienenberger <mk...@gmail.com>.
In this particular case, since the view is just doing data conversion,
I don't think you'd even need indexes, would you?
On Thu, Mar 15, 2012 at 12:35 PM, Michael Gentry <mg...@masslight.net> wrote:
> Hi Emerson,
>
> I'm pretty sure a view will make no difference. The best way to
> improve performance is to have proper indexes (which a view could also
> utilize). We have a similar-sized database and query times went from
> over 1 minute to a few milliseconds once we added an appropriate
> index.
>
> mrg
>
>
> On Thu, Mar 15, 2012 at 11:29 AM, Emerson Castañeda <em...@gmail.com> wrote:
>> Hi Mike
>>
>> My context: I am handling a table with 50 millions of rows and its size is
>> increasing up every day some thousands. So, my question goes around
>> performance of the view's solution that you propose.
>>
>> Do you think view performance will be better than using SQLTemplate
>> queries?
>>
>> Thanks
>>
>> EMERSON
>>
>> On Thu, Mar 15, 2012 at 3:07 PM, Mike Kienenberger <mk...@gmail.com>wrote:
>>
>>> Depending on your database and usage, another option is to set up a
>>> view and let the database create derived columns for these, then you
>>> can treat them as regular Cayenne data object fields.
>>>
>>> 2012/3/14 Emerson Castañeda <em...@gmail.com>:
>>> > HI everyone
>>> >
>>> > I have a table with a timestamp field, so I'm thinking about how to
>>> write
>>> > the next query without define a store procedure, but directly from my
>>> java
>>> > code:
>>> >
>>> > select *
>>> > from table1
>>> > where
>>> > id = 1 and
>>> > extract(day from timestampField)='04' and
>>> > extract(month from timestampField)='04' and
>>> > extract(year from timestampField)='2009'
>>> >
>>> > I'd like to know if that is possible using cayenne, maybe some thing like
>>> > this?
>>> >
>>> >
>>> > SelectQuery query = new SelectQuery(Table1.class);
>>> > query.andQualifier(<??EXPRESSION???>)
>>> >
>>> > OR
>>> >
>>> > SelectQuery query = new SelectQuery(Table1.class);
>>> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
>>> > Table1.timestampproperty, "%"));
>>> >
>>> >
>>> > Thank you for your time
>>> >
>>> > EMERSON
>>>
Re: Howto Include Time/Date Functions in Query Expressions
Posted by Michael Gentry <mg...@masslight.net>.
Hi Emerson,
I'm pretty sure a view will make no difference. The best way to
improve performance is to have proper indexes (which a view could also
utilize). We have a similar-sized database and query times went from
over 1 minute to a few milliseconds once we added an appropriate
index.
mrg
On Thu, Mar 15, 2012 at 11:29 AM, Emerson Castañeda <em...@gmail.com> wrote:
> Hi Mike
>
> My context: I am handling a table with 50 millions of rows and its size is
> increasing up every day some thousands. So, my question goes around
> performance of the view's solution that you propose.
>
> Do you think view performance will be better than using SQLTemplate
> queries?
>
> Thanks
>
> EMERSON
>
> On Thu, Mar 15, 2012 at 3:07 PM, Mike Kienenberger <mk...@gmail.com>wrote:
>
>> Depending on your database and usage, another option is to set up a
>> view and let the database create derived columns for these, then you
>> can treat them as regular Cayenne data object fields.
>>
>> 2012/3/14 Emerson Castañeda <em...@gmail.com>:
>> > HI everyone
>> >
>> > I have a table with a timestamp field, so I'm thinking about how to
>> write
>> > the next query without define a store procedure, but directly from my
>> java
>> > code:
>> >
>> > select *
>> > from table1
>> > where
>> > id = 1 and
>> > extract(day from timestampField)='04' and
>> > extract(month from timestampField)='04' and
>> > extract(year from timestampField)='2009'
>> >
>> > I'd like to know if that is possible using cayenne, maybe some thing like
>> > this?
>> >
>> >
>> > SelectQuery query = new SelectQuery(Table1.class);
>> > query.andQualifier(<??EXPRESSION???>)
>> >
>> > OR
>> >
>> > SelectQuery query = new SelectQuery(Table1.class);
>> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
>> > Table1.timestampproperty, "%"));
>> >
>> >
>> > Thank you for your time
>> >
>> > EMERSON
>>
Re: Howto Include Time/Date Functions in Query Expressions
Posted by Emerson Castañeda <em...@gmail.com>.
Hi Mike
My context: I am handling a table with 50 millions of rows and its size is
increasing up every day some thousands. So, my question goes around
performance of the view's solution that you propose.
Do you think view performance will be better than using SQLTemplate
queries?
Thanks
EMERSON
On Thu, Mar 15, 2012 at 3:07 PM, Mike Kienenberger <mk...@gmail.com>wrote:
> Depending on your database and usage, another option is to set up a
> view and let the database create derived columns for these, then you
> can treat them as regular Cayenne data object fields.
>
> 2012/3/14 Emerson Castañeda <em...@gmail.com>:
> > HI everyone
> >
> > I have a table with a timestamp field, so I'm thinking about how to
> write
> > the next query without define a store procedure, but directly from my
> java
> > code:
> >
> > select *
> > from table1
> > where
> > id = 1 and
> > extract(day from timestampField)='04' and
> > extract(month from timestampField)='04' and
> > extract(year from timestampField)='2009'
> >
> > I'd like to know if that is possible using cayenne, maybe some thing like
> > this?
> >
> >
> > SelectQuery query = new SelectQuery(Table1.class);
> > query.andQualifier(<??EXPRESSION???>)
> >
> > OR
> >
> > SelectQuery query = new SelectQuery(Table1.class);
> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
> > Table1.timestampproperty, "%"));
> >
> >
> > Thank you for your time
> >
> > EMERSON
>
Re: Howto Include Time/Date Functions in Query Expressions
Posted by Mike Kienenberger <mk...@gmail.com>.
Depending on your database and usage, another option is to set up a
view and let the database create derived columns for these, then you
can treat them as regular Cayenne data object fields.
2012/3/14 Emerson Castañeda <em...@gmail.com>:
> HI everyone
>
> I have a table with a timestamp field, so I'm thinking about how to write
> the next query without define a store procedure, but directly from my java
> code:
>
> select *
> from table1
> where
> id = 1 and
> extract(day from timestampField)='04' and
> extract(month from timestampField)='04' and
> extract(year from timestampField)='2009'
>
> I'd like to know if that is possible using cayenne, maybe some thing like
> this?
>
>
> SelectQuery query = new SelectQuery(Table1.class);
> query.andQualifier(<??EXPRESSION???>)
>
> OR
>
> SelectQuery query = new SelectQuery(Table1.class);
> query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
> Table1.timestampproperty, "%"));
>
>
> Thank you for your time
>
> EMERSON
Re: Howto Include Time/Date Functions in Query Expressions
Posted by Emerson Castañeda <em...@gmail.com>.
Hi, Thank you so much Andrus
I have implemented a solution using SQLTemplate, I think as you said, It's
the best bet.
A great solution, even better once I have been discovering stuff as: the
#bind and #result directives, and the possibility of DataRow treatment in
order to build structure flexible queries
I'm including a single example about the solution in case it results
usefull for someone or for comments
public List getRowsByDateAndID(int d, int m, int y, int id){
//String pgTemplate = "SELECT *"
String pgTemplate = "SELECT #result('TIME' 'java.util.Date')"
+ " FROM Table1"
+ " WHERE id = #bind($id) and"
+ " extract(day from timestampfield) = #bind($d) and"
+ " extract(month from timestampfield) = #bind($m) and"
+ " extract(year from timestampfield)= #bind($y)";
SQLTemplate query = new SQLTemplate(Table1.class, pgTemplate);
query.setFetchingDataRows(true);
//query.setTemplate(PostgresAdapter.class.getName(), pgTemplate);
Map parameters = new HashMap();
parameters.put("d", d);
parameters.put("m", m);
parameters.put("a", y);
parameters.put("id", new Integer(id));
query.setParameters(parameters);
System.out.println(query.toString());
System.out.println(query.getDefaultTemplate());
List list = performQuery(query);
return list;
}
Using and Handling results:
List list = getRowsByDateAndID(4,4,2009,4551);
Iterator itr = list.iterator();
while(itr.hasNext()) {
DataRow d = (DataRow)itr.next();
System.out.println("A-"+ d);
Map row = (Map)d;
System.out.println("field:"+row.get("TIME"));
//System.out.println("field:"+row.get("time"));//for select * case
}
Cayenne Rocks
EMERSON
On Thu, Mar 15, 2012 at 1:44 AM, Andrus Adamchik <an...@objectstyle.org>wrote:
> Your best bet is using SQLTemplate in this case. Not as OO as SelectQuery,
> but certainly not a stored procedure either :)
>
> http://cayenne.apache.org/doc30/sqltemplate-query.html
>
> Cheers,
> Andrus
>
> On Mar 14, 2012, at 4:22 PM, Emerson Castañeda wrote:
>
> > HI everyone
> >
> > I have a table with a timestamp field, so I'm thinking about how to
> write
> > the next query without define a store procedure, but directly from my
> java
> > code:
> >
> > select *
> > from table1
> > where
> > id = 1 and
> > extract(day from timestampField)='04' and
> > extract(month from timestampField)='04' and
> > extract(year from timestampField)='2009'
> >
> > I'd like to know if that is possible using cayenne, maybe some thing like
> > this?
> >
> >
> > SelectQuery query = new SelectQuery(Table1.class);
> > query.andQualifier(<??EXPRESSION???>)
> >
> > OR
> >
> > SelectQuery query = new SelectQuery(Table1.class);
> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
> > Table1.timestampproperty, "%"));
> >
> >
> > Thank you for your time
> >
> > EMERSON
>
>
Re: Howto Include Time/Date Functions in Query Expressions
Posted by Andrus Adamchik <an...@objectstyle.org>.
Your best bet is using SQLTemplate in this case. Not as OO as SelectQuery, but certainly not a stored procedure either :)
http://cayenne.apache.org/doc30/sqltemplate-query.html
Cheers,
Andrus
On Mar 14, 2012, at 4:22 PM, Emerson Castañeda wrote:
> HI everyone
>
> I have a table with a timestamp field, so I'm thinking about how to write
> the next query without define a store procedure, but directly from my java
> code:
>
> select *
> from table1
> where
> id = 1 and
> extract(day from timestampField)='04' and
> extract(month from timestampField)='04' and
> extract(year from timestampField)='2009'
>
> I'd like to know if that is possible using cayenne, maybe some thing like
> this?
>
>
> SelectQuery query = new SelectQuery(Table1.class);
> query.andQualifier(<??EXPRESSION???>)
>
> OR
>
> SelectQuery query = new SelectQuery(Table1.class);
> query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
> Table1.timestampproperty, "%"));
>
>
> Thank you for your time
>
> EMERSON