You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Arnaud Garcia <ar...@imagemed-87.com> on 2010/04/18 18:15:36 UTC
count(*) with parameter
Hello,
I am not sure how to create a select count(*) in the modeler when I need to
pass a parameter ?
I downloaded the petstore example, and it seems that I have to create a "Raw
SQL" Query, and directly write my select count(*)...
=>but how to I had my parameter in my SQL ?
... then I suppose I can do ctxt.performQuery("myCount",params,true) ?
thanks for help for this beginner question...
Arnaud
Re: count(*) with parameter
Posted by "Florin T.PATRASCU" <fl...@rogers.com>.
or something like this:
...
String sql = "select count(*) as total_clicks from user_clicks " +
" where user_id = #bind( $userId, 'VARCHAR')";
SQLTemplate sqlTemplate = new SQLTemplate( UserClick.class, sql);
sqlTemplate.setParameters(params); //params defined elsewhere
sqlTemplate.setFetchingDataRows(true);
List res = BaseContext.getThreadObjectContext().performQuery( sqlTemplate);
return ((Integer) ((Map) res.get(0)).get("total_clicks")).intValue();
}
it works :)
-florin
On 18-Apr-10, at 12:43 , Joe Baldwin wrote:
> Arnaud,
>
> If I understand you, I think you want to do this in the code and not the modeler?
>
> Here is a simple code fragment for a like-ignore-case qualifier:
>
> String substring = "Martin";
> ObjectContext oc = BaseContext.getThreadObjectContext();
> Expression exp = ExpressionFactory.likeIgnoreCaseExp(Product.NAME_PROPERTY, ("%" + substring + "%"));
> SelectQuery query = new SelectQuery(Product.class, exp);
> List list = oc.performQuery(query);
> System.out.println("\ncount: " + list.size());
>
> This is pretty efficient and to my knowledge does not trigger any DataObject faults. There are a few other ways to do this depending on your design. The ExpresionFactory has a lot of powerful methods to help you out.
>
> Joe
>
>
>
>
> On Apr 18, 2010, at 12:15 PM, Arnaud Garcia wrote:
>
>> Hello,
>>
>> I am not sure how to create a select count(*) in the modeler when I need to
>> pass a parameter ?
>> I downloaded the petstore example, and it seems that I have to create a "Raw
>> SQL" Query, and directly write my select count(*)...
>>
>> =>but how to I had my parameter in my SQL ?
>>
>> ... then I suppose I can do ctxt.performQuery("myCount",params,true) ?
>>
>> thanks for help for this beginner question...
>>
>> Arnaud
>
Re: count(*) with parameter
Posted by Arnaud Garcia <ar...@imagemed-87.com>.
thanks all
arnaud
2010/4/19 Joe Baldwin <jf...@earthlink.net>
> Arnaud,
>
> For example: I think you can add stored procedures via the CayenneModeler
> (CM). I have not used this feature yet, and so I do not know if this is
> even an optimum solution. (Maybe the Cayenne team knows the answer to
> this.)
>
> You could try this out and then do some performance testing to decide what
> is best. (I would be interested in the result.)
>
> My design was to create a custom Persistence Factory class and add
> convenience methods as I need them. I also located a lot of
> CayenneDataObject-related methods in the CayenneDataObject's subclass. As I
> learned more about Cayenne features, I was able to easily update this kind
> of design for performance enhancements and bug fixes. (This is just my
> opinion.)
>
> Your decision might come down to performance or simply whether your design
> concepts are better handled fromm the DBMS or OO perspective.
>
> Joe
>
> On Apr 19, 2010, at 12:28 PM, Arnaud Garcia wrote:
>
> > Hi and thanks,
> >
> > Well both, modeler and code (just for my cayenne understanding...)
> > And in the modeler ?
> >
> > Arnaud
> >
> > 2010/4/18 Joe Baldwin <jf...@earthlink.net>
> >
> >> Arnaud,
> >>
> >> If I understand you, I think you want to do this in the code and not the
> >> modeler?
> >>
> >> Here is a simple code fragment for a like-ignore-case qualifier:
> >>
> >> String substring = "Martin";
> >> ObjectContext oc = BaseContext.getThreadObjectContext();
> >> Expression exp =
> >> ExpressionFactory.likeIgnoreCaseExp(Product.NAME_PROPERTY, ("%" +
> substring
> >> + "%"));
> >> SelectQuery query = new SelectQuery(Product.class, exp);
> >> List list = oc.performQuery(query);
> >> System.out.println("\ncount: " + list.size());
> >>
> >> This is pretty efficient and to my knowledge does not trigger any
> >> DataObject faults. There are a few other ways to do this depending on
> your
> >> design. The ExpresionFactory has a lot of powerful methods to help you
> out.
> >>
> >> Joe
> >>
> >>
> >>
> >>
> >> On Apr 18, 2010, at 12:15 PM, Arnaud Garcia wrote:
> >>
> >>> Hello,
> >>>
> >>> I am not sure how to create a select count(*) in the modeler when I
> need
> >> to
> >>> pass a parameter ?
> >>> I downloaded the petstore example, and it seems that I have to create a
> >> "Raw
> >>> SQL" Query, and directly write my select count(*)...
> >>>
> >>> =>but how to I had my parameter in my SQL ?
> >>>
> >>> ... then I suppose I can do ctxt.performQuery("myCount",params,true) ?
> >>>
> >>> thanks for help for this beginner question...
> >>>
> >>> Arnaud
> >>
> >>
>
>
Re: count(*) with parameter
Posted by Joe Baldwin <jf...@earthlink.net>.
Arnaud,
For example: I think you can add stored procedures via the CayenneModeler (CM). I have not used this feature yet, and so I do not know if this is even an optimum solution. (Maybe the Cayenne team knows the answer to this.)
You could try this out and then do some performance testing to decide what is best. (I would be interested in the result.)
My design was to create a custom Persistence Factory class and add convenience methods as I need them. I also located a lot of CayenneDataObject-related methods in the CayenneDataObject's subclass. As I learned more about Cayenne features, I was able to easily update this kind of design for performance enhancements and bug fixes. (This is just my opinion.)
Your decision might come down to performance or simply whether your design concepts are better handled fromm the DBMS or OO perspective.
Joe
On Apr 19, 2010, at 12:28 PM, Arnaud Garcia wrote:
> Hi and thanks,
>
> Well both, modeler and code (just for my cayenne understanding...)
> And in the modeler ?
>
> Arnaud
>
> 2010/4/18 Joe Baldwin <jf...@earthlink.net>
>
>> Arnaud,
>>
>> If I understand you, I think you want to do this in the code and not the
>> modeler?
>>
>> Here is a simple code fragment for a like-ignore-case qualifier:
>>
>> String substring = "Martin";
>> ObjectContext oc = BaseContext.getThreadObjectContext();
>> Expression exp =
>> ExpressionFactory.likeIgnoreCaseExp(Product.NAME_PROPERTY, ("%" + substring
>> + "%"));
>> SelectQuery query = new SelectQuery(Product.class, exp);
>> List list = oc.performQuery(query);
>> System.out.println("\ncount: " + list.size());
>>
>> This is pretty efficient and to my knowledge does not trigger any
>> DataObject faults. There are a few other ways to do this depending on your
>> design. The ExpresionFactory has a lot of powerful methods to help you out.
>>
>> Joe
>>
>>
>>
>>
>> On Apr 18, 2010, at 12:15 PM, Arnaud Garcia wrote:
>>
>>> Hello,
>>>
>>> I am not sure how to create a select count(*) in the modeler when I need
>> to
>>> pass a parameter ?
>>> I downloaded the petstore example, and it seems that I have to create a
>> "Raw
>>> SQL" Query, and directly write my select count(*)...
>>>
>>> =>but how to I had my parameter in my SQL ?
>>>
>>> ... then I suppose I can do ctxt.performQuery("myCount",params,true) ?
>>>
>>> thanks for help for this beginner question...
>>>
>>> Arnaud
>>
>>
Re: count(*) with parameter
Posted by Arnaud Garcia <ar...@imagemed-87.com>.
Hi and thanks,
Well both, modeler and code (just for my cayenne understanding...)
And in the modeler ?
Arnaud
2010/4/18 Joe Baldwin <jf...@earthlink.net>
> Arnaud,
>
> If I understand you, I think you want to do this in the code and not the
> modeler?
>
> Here is a simple code fragment for a like-ignore-case qualifier:
>
> String substring = "Martin";
> ObjectContext oc = BaseContext.getThreadObjectContext();
> Expression exp =
> ExpressionFactory.likeIgnoreCaseExp(Product.NAME_PROPERTY, ("%" + substring
> + "%"));
> SelectQuery query = new SelectQuery(Product.class, exp);
> List list = oc.performQuery(query);
> System.out.println("\ncount: " + list.size());
>
> This is pretty efficient and to my knowledge does not trigger any
> DataObject faults. There are a few other ways to do this depending on your
> design. The ExpresionFactory has a lot of powerful methods to help you out.
>
> Joe
>
>
>
>
> On Apr 18, 2010, at 12:15 PM, Arnaud Garcia wrote:
>
> > Hello,
> >
> > I am not sure how to create a select count(*) in the modeler when I need
> to
> > pass a parameter ?
> > I downloaded the petstore example, and it seems that I have to create a
> "Raw
> > SQL" Query, and directly write my select count(*)...
> >
> > =>but how to I had my parameter in my SQL ?
> >
> > ... then I suppose I can do ctxt.performQuery("myCount",params,true) ?
> >
> > thanks for help for this beginner question...
> >
> > Arnaud
>
>
Re: count(*) with parameter
Posted by Joe Baldwin <jf...@earthlink.net>.
Arnaud,
If I understand you, I think you want to do this in the code and not the modeler?
Here is a simple code fragment for a like-ignore-case qualifier:
String substring = "Martin";
ObjectContext oc = BaseContext.getThreadObjectContext();
Expression exp = ExpressionFactory.likeIgnoreCaseExp(Product.NAME_PROPERTY, ("%" + substring + "%"));
SelectQuery query = new SelectQuery(Product.class, exp);
List list = oc.performQuery(query);
System.out.println("\ncount: " + list.size());
This is pretty efficient and to my knowledge does not trigger any DataObject faults. There are a few other ways to do this depending on your design. The ExpresionFactory has a lot of powerful methods to help you out.
Joe
On Apr 18, 2010, at 12:15 PM, Arnaud Garcia wrote:
> Hello,
>
> I am not sure how to create a select count(*) in the modeler when I need to
> pass a parameter ?
> I downloaded the petstore example, and it seems that I have to create a "Raw
> SQL" Query, and directly write my select count(*)...
>
> =>but how to I had my parameter in my SQL ?
>
> ... then I suppose I can do ctxt.performQuery("myCount",params,true) ?
>
> thanks for help for this beginner question...
>
> Arnaud