You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by David Marko <dm...@tiscali.cz> on 2007/10/09 13:11:04 UTC
SelectQuery and getting where clause
Hello, I need to create a select query that have some parameters and I need to
get SQL where statement from it containing replaced parameters. (see snippet
below) . Is there any way how to accomplish this?
[Code snippet]
SelectQuery sq = new
SelectQuery(Role.class,Expression.fromString(this.whereClause));
return sq.queryWithParameters(params);
Re: SelectQuery and getting where clause
Posted by Andrus Adamchik <an...@objectstyle.org>.
BTW, you may also look at the new EJBQLQuery in Cayenne 3.0. M1
supports only the simplest SELECT queries, while the upcoming M2
supports 99% of the JPA mandated syntax. While this is somewhat of a
bleeding edge, it should simplify custom aggregate queries
significantly.
http://cayenne.apache.org/doc/ejbqlquery.html
Andrus
On Oct 9, 2007, at 5:21 PM, David Marko wrote:
> Thanks, its really what I needed.
>
> The reason why I asked is as follow. The EntityManager for Cayenne
> (my previous
> post) offers aggregate functions (count, sum, average, max). To
> accomblish this
> I created SelecteQuery e.g. q="SELECT COUNT(*) as count, $name as
> name FROM
> ROLE GROUP BY $name"; for queries with 'where' constrains I use
> special Query
> syntax(wrapper for cayenne expression) and I need the 'where'
> clause to be put
> into manualy created 'select count(*) .... ' query. Not easy to
> explain without
> detailed code though.
>
> // and this is how I call it: em is EntityManager
> List<DataRow> li= em.count(Item.class, Item.NAME_PROPERTY,
> Query.where("year >
> $year").addParam("year", 2003));
>
> // or sum
> List<DataRow> li= em.sum(Item.class, Item.PROFIT_PROPERTY,
> Item.PRODUCT_NAME_PROPERTY, Query.where("year > $year").addParam
> ("year", 2003));
>
>
> David
>
> Michael Gentry wrote:
>> Will:
>>
>> Expression exp = sq.getQualifier();
>> String clause = exp.toString();
>>
>> do what you are wanting?
>>
>>
>> On 10/9/07, David Marko <dm...@tiscali.cz> wrote:
>>> Hello, I need to create a select query that have some parameters
>>> and I need to
>>> get SQL where statement from it containing replaced parameters.
>>> (see snippet
>>> below) . Is there any way how to accomplish this?
>>>
>>>
>>> [Code snippet]
>>> SelectQuery sq = new
>>> SelectQuery(Role.class,Expression.fromString(this.whereClause));
>>> return sq.queryWithParameters(params);
>>>
>>
>
>
Re: CayenneObjectManager - alternative API for using Apache Cayenne
Posted by David Marko <dm...@tiscali.cz>.
Just for note, you can look to this URL, maybe we can learn something or at
least grab some idea.
http://www.thimbleware.com/projects/jrel
Re: CayenneObjectManager - alternative API for using Apache Cayenne
Posted by David Marko <dm...@tiscali.cz>.
I just read both links. I think that having some concept, that avoids using DAO
schema, would be very good. For most web applications DAO is just overhead and
require to write large infrastructure. Would be good to have flexibility of
ActiveRecord or Django ORM for many solutions where just 5 tables are involved
and developer need to design application in quick way. My implementation came
from https://activeobjects.dev.java.net/ . Author here creates entire ORM from
scratch, I rather prefer ot use existing ORM(like Cayenne) and just create
alternative API above.
BTW: I think this is why people enjoy using GORM in Grails. GORM is just higher
layer over Hibernate implemented in Groovy. People ussualy cry when using
Hibernate in JAVA but praise GORM which stays over Hibernate. So good API matters.
David
Re: CayenneObjectManager - alternative API for using Apache Cayenne
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi Ari,
thanks for pointing to that dev discussion thread. I was going to do
the same. In fact my own interest in this approach started as a result
of David posting his first example:
https://issues.apache.org/cayenne/browse/CAY-877
And I hope to reuse his work if possible (and at the minimum use some
of the ideas).
Andrus
On Feb 24, 2008, at 12:57 PM, Aristedes Maniatis wrote:
>
> On 24/02/2008, at 9:13 PM, David Marko wrote:
>
>> // find first item by property and given condition
>> co_manager
>> .find(Class).byProperty("property").BETWEEN(value1,value2).first()
>> co_manager.find(Class).byProperty("property").LIKE(value).first()
>> co_manager.find(Class).byProperty("property").IN(range).first()
>
> This looks a lot like the Fluent interface [1] Andrus has proposed
> as a possible way to generify the query API. Have a read of the
> relevant thread [2] for further info there. It is an interesting
> idea but quite hard to write documentation for (such as javadocs)
> and possibly hard for newcomers. On the other hand it is very
> compact and readable.
>
>
> Ari Maniatis
>
>
>
> [1] http://martinfowler.com/bliki/FluentInterface.html
> [2] http://markmail.org/message/jqfjtsogixpeijsn
>
>
> -------------------------->
> ish
> http://www.ish.com.au
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001 fax +61 2 9550 4001
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
>
>
Re: CayenneObjectManager - alternative API for using Apache Cayenne
Posted by David Marko <dm...@tiscali.cz>.
Haven't read it yet. Will look at it, thanks very much! I would really
appreciate some Cayenne standardized way, if there will be some.
David Marko
Aristedes Maniatis wrote:
>
> On 24/02/2008, at 9:13 PM, David Marko wrote:
>
>> // find first item by property and given condition
>> co_manager
>> .find(Class).byProperty("property").BETWEEN(value1,value2).first()
>> co_manager.find(Class).byProperty("property").LIKE(value).first()
>> co_manager.find(Class).byProperty("property").IN(range).first()
>
> This looks a lot like the Fluent interface [1] Andrus has proposed as a
> possible way to generify the query API. Have a read of the relevant
> thread [2] for further info there. It is an interesting idea but quite
> hard to write documentation for (such as javadocs) and possibly hard for
> newcomers. On the other hand it is very compact and readable.
>
>
> Ari Maniatis
>
>
>
> [1] http://martinfowler.com/bliki/FluentInterface.html
> [2] http://markmail.org/message/jqfjtsogixpeijsn
>
>
> -------------------------->
> ish
> http://www.ish.com.au
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001 fax +61 2 9550 4001
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
>
>
Re: CayenneObjectManager - alternative API for using Apache Cayenne
Posted by Aristedes Maniatis <ar...@ish.com.au>.
On 24/02/2008, at 9:13 PM, David Marko wrote:
> // find first item by property and given condition
> co_manager
> .find(Class).byProperty("property").BETWEEN(value1,value2).first()
> co_manager.find(Class).byProperty("property").LIKE(value).first()
> co_manager.find(Class).byProperty("property").IN(range).first()
This looks a lot like the Fluent interface [1] Andrus has proposed as
a possible way to generify the query API. Have a read of the relevant
thread [2] for further info there. It is an interesting idea but quite
hard to write documentation for (such as javadocs) and possibly hard
for newcomers. On the other hand it is very compact and readable.
Ari Maniatis
[1] http://martinfowler.com/bliki/FluentInterface.html
[2] http://markmail.org/message/jqfjtsogixpeijsn
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
CayenneObjectManager - alternative API for using Apache Cayenne
Posted by David Marko <dm...@tiscali.cz>.
Hello,
a few months ago a wrote about creating alternative approach for using Apache
Cayenne. Actualy I use Cayenne with Click Framework and following scenario came
from practice on several projects. The main idea is to use Cayenne as very
stable background(engine) but alter API to something closer to ActiveRecord like
approach. The result is CayenneObjectManager that implements alternative API
that, as result, generates standard Cayenne queries.
CayenneObjectManager API assumes Java5 as generics are used extensively and is
tested with latest Apache Cayenne M3. Entire package is rather small and is
intended to be extended by other methods. I'm not very experienced JAVA
developer so one thing I would like to ask is, if someone can review the concept
and correct some potencial mistakes or contribute with some ideas etc. I'm open
for correction.
Thanks,
David
See attached file with source at https://issues.apache.org/cayenne/browse/CAY-988
For quick example just image you have a simple table with Role names and imagine
how simply you can work with this:
// Load role object by its name
COManager manager=new COManagerImpl();
// init is based on usage scenario. This can be used in standalone app. for e.g.
web app usage, the context is inititalized automaticly from
DataContext.getThreadDataContext();
co_manager.setContext(DataContext.createDataContext());
Role
role_admin=co_manager.find(Role.class).byProperty("name").EQUALS("admin").first();
// all roles from allowed list as key-pair for checkbox field
Map<String,String>
roles=co_manager.find(Role.class).byProperty(Role.NAME_PROPERTY).IN(allowed_roles).to_pairs("name",
"id");
CayenneObjectsManager - alternative API for working with Apache Cayenne subsystem
// Available use cases
// find one item by ID
co_manager.find(Class).byId(id)
// find all items by property and given condition
co_manager.find(Class).byProperty("property").EQUALS(value)
// find first item by property and given condition
co_manager.find(Class).byProperty("property").BETWEEN(value1,value2).first()
co_manager.find(Class).byProperty("property").LIKE(value).first()
co_manager.find(Class).byProperty("property").IN(range).first()
// find all items
co_manager.find(Class).all()
// find all items by based on CayenneObjectsQuery
co_manager.find(Class).allByQuery(COQuery query)
// find all firts item by based on CayenneObjectsQuery
co_manager.find(Class).allByQuery(COQuery query).firts()
//find all selected items as key-pairs available e.g. for checkbox field
co_manager.find(Class).byProperty("property").EQUAL(value1).to_pairs(key,value)
// aggregate functions
co_manager.aggregate(Class).count("property")
co_manager.aggregate(Class).count("property", COQuery query)
co_manager.aggregate(Class).sum("property")
co_manager.aggregate(Class).sum("property", COQuery query)
co_manager.aggregate(Class).avg("property")
co_manager.aggregate(Class).avg("property", COQuery query)
co_manager.aggregate(Class).count("property",
Query.where("year>$year").addParam("year", 2003));
// common actions
co_manager.create(Class)
co_manager.save()
Re: SelectQuery and getting where clause
Posted by David Marko <dm...@tiscali.cz>.
Thanks, its really what I needed.
The reason why I asked is as follow. The EntityManager for Cayenne(my previous
post) offers aggregate functions (count, sum, average, max). To accomblish this
I created SelecteQuery e.g. q="SELECT COUNT(*) as count, $name as name FROM
ROLE GROUP BY $name"; for queries with 'where' constrains I use special Query
syntax(wrapper for cayenne expression) and I need the 'where' clause to be put
into manualy created 'select count(*) .... ' query. Not easy to explain without
detailed code though.
// and this is how I call it: em is EntityManager
List<DataRow> li= em.count(Item.class, Item.NAME_PROPERTY, Query.where("year >
$year").addParam("year", 2003));
// or sum
List<DataRow> li= em.sum(Item.class, Item.PROFIT_PROPERTY,
Item.PRODUCT_NAME_PROPERTY, Query.where("year > $year").addParam("year", 2003));
David
Michael Gentry wrote:
> Will:
>
> Expression exp = sq.getQualifier();
> String clause = exp.toString();
>
> do what you are wanting?
>
>
> On 10/9/07, David Marko <dm...@tiscali.cz> wrote:
>> Hello, I need to create a select query that have some parameters and I need to
>> get SQL where statement from it containing replaced parameters. (see snippet
>> below) . Is there any way how to accomplish this?
>>
>>
>> [Code snippet]
>> SelectQuery sq = new
>> SelectQuery(Role.class,Expression.fromString(this.whereClause));
>> return sq.queryWithParameters(params);
>>
>
Re: SelectQuery and getting where clause
Posted by Michael Gentry <bl...@gmail.com>.
Will:
Expression exp = sq.getQualifier();
String clause = exp.toString();
do what you are wanting?
On 10/9/07, David Marko <dm...@tiscali.cz> wrote:
> Hello, I need to create a select query that have some parameters and I need to
> get SQL where statement from it containing replaced parameters. (see snippet
> below) . Is there any way how to accomplish this?
>
>
> [Code snippet]
> SelectQuery sq = new
> SelectQuery(Role.class,Expression.fromString(this.whereClause));
> return sq.queryWithParameters(params);
>
Re: SelectQuery and getting where clause
Posted by Andrus Adamchik <an...@objectstyle.org>.
Could you elaborate on what are you trying to do? Do you want to
capture generated SQL without executing it?
Note that Cayenne-generated SQL uses PreparedStatement syntax with
"?" placeholders for most parameters, letting JDBC driver to bind
them correctly.
Andrus
On Oct 9, 2007, at 2:11 PM, David Marko wrote:
> Hello, I need to create a select query that have some parameters
> and I need to
> get SQL where statement from it containing replaced parameters.
> (see snippet
> below) . Is there any way how to accomplish this?
>
>
> [Code snippet]
> SelectQuery sq = new
> SelectQuery(Role.class,Expression.fromString(this.whereClause));
> return sq.queryWithParameters(params);
>
Re: Cayenne ActiveRecord like interface
Posted by Robert Zeigler <ro...@puregumption.com>.
Hi David, I did something very similar (although your api looks
cleaner than mine; I particularly liked the Query portion...) in a
recent project combining tapestry 5 with cayenne.
I was able to have a single "add/edit' page and a single "view" page
for all of about 15 tables in the application, which would
automatically pick up new properties, etc.
There are certainly places in the application where a more detailed
knowledge of the schema is necessary (there are only 15 tables, but
some of them will store literally hundreds of thousands of records,
and the app is required to generate fairly complicated reports; for
performance purposes, using simple object queries, etc. just wouldn't
always cut it...). At some point, I will very likely adapt my code
for more general use outside of this particular project.
Robert
On Oct 9, 2007, at 10/97:21 AM , David Marko wrote:
> Hello,
> I like a ROR ActiveRecord a lot and I decided to create a special
> interface for
> Cayenne to let me work easier in most cases. I borrowed some ideas
> and created a
> wrapper for Apache Cayenne. Instead a common DAO like scenario of
> using services
> for each model, I can now use one EntityManager that serves for all
> models.
>
> The solution requires Java 5 as generics and some other features
> are beeing
> used. Here are just a few code snippets of how it can be used.
>
> Syntax examples:
> EntityManager em;
> // create and save object
> Contact contact=em.create(Contact.class);
> em.saveChanges();
>
> // find object by id
> Contact contact=em.findById(Contact.class, id);
>
> // find object by property
> Contact contact=em.findFirstByProperty(Contact.class, "username",
> form.getFieldValue("username"));
>
> // find many objects by property
> List contacts=em.findAllByProperty(Contact.class, "subdomain.name",
> "subdomain");
>
> // find objects based on user defined query
> List contacts=em.findAllByQuery(Contact.class,
> Query.select().where("subdomain = $subdomain and age > $age")
> .param("subdomain", "agh")
> .param("age", 18)
> .order("lastname",true)
> .offset(200)
> .limit(10)
> .include(Contact.ROLE_ARRAY_PROPERTY));
>
> // count items per invoice
> List counts=em.count(Item.class, "invoice_id")
>
> Its just a special API interface that let you work with Apache
> Cayenne in very
> easy way. Of course there is still a place for DAO for more
> advanced situations.
> Is anyone else working on some similar approach? I would like to share
> experiences/ideas.
>
> David
>
Re: Cayenne ActiveRecord like interface
Posted by Mike Kienenberger <mk...@gmail.com>.
Significant contributions require a CLA.
What significant means has often been debated, but I suspect adding
new functionality (as opposed to fixing a bug) is considered
significant.
Note that the CLA only gives ASF non-exclusive license to use the
code, so other than the bureaucratic hoop jumping required (filling
out and sending in the paperwork, then waiting for it be processed),
it's almost never an issue.
-Mike
On 10/9/07, Michael Gentry <bl...@gmail.com> wrote:
> I thought if he added it into Jira and selected "Grant license to ASF
> for inclusion in ASF works (as per the Apache License §5)" as the
> option for "Attachment License" then it would be OK? Not that I'm
> trying to backdoor the CLA ... :-)
>
> 5. Submission of Contributions. Unless You explicitly state otherwise,
> any Contribution intentionally submitted for inclusion in the Work
> by You to the Licensor shall be under the terms and conditions of
> this License, without any additional terms or conditions.
> Notwithstanding the above, nothing herein shall supersede or modify
> the terms of any separate license agreement you may have executed
> with Licensor regarding such Contributions.
>
> /dev/mrg
>
>
> On 10/9/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> > Cool. This can be done via Jira.
> >
> > Let me also mention in this context that you will need to sign a CLA
> > for us to be able to include your code in Cayenne, which is hopefully
> > not a big deal:
> >
> > http://apache.org/licenses/
> >
> > Andrus
> >
> >
> > On Oct 9, 2007, at 5:29 PM, David Marko wrote:
> > > Yes, I can provide you with code. Its suprisingly simple code. I
> > > will prepare it
> > > with some description and examples of usage.
> > >
> > > David
> > >
> > > Andrus Adamchik wrote:
> > >>
> > >> On Oct 9, 2007, at 4:56 PM, Andrus Adamchik wrote:
> > >>
> > >>> dotae
> > >>
> > >> I meant "donate" of course :-)
> > >>
> > >> A.
> > >>
> > >>
> > >
> > >
> >
> >
>
Re: Cayenne ActiveRecord like interface
Posted by Michael Gentry <bl...@gmail.com>.
I thought if he added it into Jira and selected "Grant license to ASF
for inclusion in ASF works (as per the Apache License §5)" as the
option for "Attachment License" then it would be OK? Not that I'm
trying to backdoor the CLA ... :-)
5. Submission of Contributions. Unless You explicitly state otherwise,
any Contribution intentionally submitted for inclusion in the Work
by You to the Licensor shall be under the terms and conditions of
this License, without any additional terms or conditions.
Notwithstanding the above, nothing herein shall supersede or modify
the terms of any separate license agreement you may have executed
with Licensor regarding such Contributions.
/dev/mrg
On 10/9/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> Cool. This can be done via Jira.
>
> Let me also mention in this context that you will need to sign a CLA
> for us to be able to include your code in Cayenne, which is hopefully
> not a big deal:
>
> http://apache.org/licenses/
>
> Andrus
>
>
> On Oct 9, 2007, at 5:29 PM, David Marko wrote:
> > Yes, I can provide you with code. Its suprisingly simple code. I
> > will prepare it
> > with some description and examples of usage.
> >
> > David
> >
> > Andrus Adamchik wrote:
> >>
> >> On Oct 9, 2007, at 4:56 PM, Andrus Adamchik wrote:
> >>
> >>> dotae
> >>
> >> I meant "donate" of course :-)
> >>
> >> A.
> >>
> >>
> >
> >
>
>
Re: Cayenne ActiveRecord like interface
Posted by Andrus Adamchik <an...@objectstyle.org>.
Cool. This can be done via Jira.
Let me also mention in this context that you will need to sign a CLA
for us to be able to include your code in Cayenne, which is hopefully
not a big deal:
http://apache.org/licenses/
Andrus
On Oct 9, 2007, at 5:29 PM, David Marko wrote:
> Yes, I can provide you with code. Its suprisingly simple code. I
> will prepare it
> with some description and examples of usage.
>
> David
>
> Andrus Adamchik wrote:
>>
>> On Oct 9, 2007, at 4:56 PM, Andrus Adamchik wrote:
>>
>>> dotae
>>
>> I meant "donate" of course :-)
>>
>> A.
>>
>>
>
>
Re: Cayenne ActiveRecord like interface
Posted by David Marko <dm...@tiscali.cz>.
Yes, I can provide you with code. Its suprisingly simple code. I will prepare it
with some description and examples of usage.
David
Andrus Adamchik wrote:
>
> On Oct 9, 2007, at 4:56 PM, Andrus Adamchik wrote:
>
>> dotae
>
> I meant "donate" of course :-)
>
> A.
>
>
Re: Cayenne ActiveRecord like interface
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Oct 9, 2007, at 4:56 PM, Andrus Adamchik wrote:
> dotae
I meant "donate" of course :-)
A.
Re: Cayenne ActiveRecord like interface
Posted by Andrus Adamchik <an...@objectstyle.org>.
Looks nice. Any chance you'd like to dotae your work to Cayenne and
work with Cayenne developers to package it in a form suitable for
inclusion in the project?
Thanks
Andrus
On Oct 9, 2007, at 3:21 PM, David Marko wrote:
> Hello,
> I like a ROR ActiveRecord a lot and I decided to create a special
> interface for
> Cayenne to let me work easier in most cases. I borrowed some ideas
> and created a
> wrapper for Apache Cayenne. Instead a common DAO like scenario of
> using services
> for each model, I can now use one EntityManager that serves for all
> models.
>
> The solution requires Java 5 as generics and some other features
> are beeing
> used. Here are just a few code snippets of how it can be used.
>
> Syntax examples:
> EntityManager em;
> // create and save object
> Contact contact=em.create(Contact.class);
> em.saveChanges();
>
> // find object by id
> Contact contact=em.findById(Contact.class, id);
>
> // find object by property
> Contact contact=em.findFirstByProperty(Contact.class, "username",
> form.getFieldValue("username"));
>
> // find many objects by property
> List contacts=em.findAllByProperty(Contact.class, "subdomain.name",
> "subdomain");
>
> // find objects based on user defined query
> List contacts=em.findAllByQuery(Contact.class,
> Query.select().where("subdomain = $subdomain and age > $age")
> .param("subdomain", "agh")
> .param("age", 18)
> .order("lastname",true)
> .offset(200)
> .limit(10)
> .include(Contact.ROLE_ARRAY_PROPERTY));
>
> // count items per invoice
> List counts=em.count(Item.class, "invoice_id")
>
> Its just a special API interface that let you work with Apache
> Cayenne in very
> easy way. Of course there is still a place for DAO for more
> advanced situations.
> Is anyone else working on some similar approach? I would like to share
> experiences/ideas.
>
> David
>
>
Cayenne ActiveRecord like interface
Posted by David Marko <dm...@tiscali.cz>.
Hello,
I like a ROR ActiveRecord a lot and I decided to create a special interface for
Cayenne to let me work easier in most cases. I borrowed some ideas and created a
wrapper for Apache Cayenne. Instead a common DAO like scenario of using services
for each model, I can now use one EntityManager that serves for all models.
The solution requires Java 5 as generics and some other features are beeing
used. Here are just a few code snippets of how it can be used.
Syntax examples:
EntityManager em;
// create and save object
Contact contact=em.create(Contact.class);
em.saveChanges();
// find object by id
Contact contact=em.findById(Contact.class, id);
// find object by property
Contact contact=em.findFirstByProperty(Contact.class, "username",
form.getFieldValue("username"));
// find many objects by property
List contacts=em.findAllByProperty(Contact.class, "subdomain.name", "subdomain");
// find objects based on user defined query
List contacts=em.findAllByQuery(Contact.class,
Query.select().where("subdomain = $subdomain and age > $age")
.param("subdomain", "agh")
.param("age", 18)
.order("lastname",true)
.offset(200)
.limit(10)
.include(Contact.ROLE_ARRAY_PROPERTY));
// count items per invoice
List counts=em.count(Item.class, "invoice_id")
Its just a special API interface that let you work with Apache Cayenne in very
easy way. Of course there is still a place for DAO for more advanced situations.
Is anyone else working on some similar approach? I would like to share
experiences/ideas.
David