Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (herse.apache.org: domain of blacknext@gmail.com designates
 64.233.166.176 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=uYy8yz3iajw1k0wDjihuDySQIftHZzf8SRYHiOtvTEoBH5oxQXuKgkHgEpljg28uMNYCS6p0AO1jCx4fFJFWn2Ngp998nMY+z5iu17676RMP32Uw7tVtXxqy0gaorEJ0wn/Nim4hk86CJR6i1jM2k/pGR5kG/PkdyR2x/JgwGQM=
Message-ID: <f65b8ae30702020553p1a4025fl26ca6ff44f6c01b6@mail.gmail.com>
Date: Fri, 2 Feb 2007 08:53:24 -0500
From: "Michael Gentry" <blacknext@gmail.com>
To: user@cayenne.apache.org
Subject: Re: how to call a custom query
In-Reply-To: 
 <49D23F7404DC5B4086D7352312597E0FD26687@FNHH-SVMEXDB002.Freenet-AG.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: 
 <49D23F7404DC5B4086D7352312597E0FD26687@FNHH-SVMEXDB002.Freenet-AG.de>

I do agree with everyone that it is best to not use raw SQL if you can
avoid it.  Have you looked at any of the Cayenne examples?  I wrote
this one a while back, which might be helpful (even if you just kind
of trace through it without having downloading/installing the code
into Eclipse):

http://cwiki.apache.org/CAY/cayenne-in-motion.html

It shows how to do relationships/etc, too.  There are several others,
plus the modeling guide, etc.

Thanks,

/dev/mrg


On 2/2/07, Peter Schr=F6der <Peter.Schroeder@freenet-ag.de> wrote:
> hi frank,
>
> you may use the object-graph to execute the queries you want. there is no=
 joining with cayenne-syntax. cayenne will translate your query into an sql=
-statement with any joins that will be nessessary.
>
> so
>
> query +=3D " FROM schedules";
>
> query +=3D " INNER JOIN procedures ON schedules.procedure_id =3D
> procedures.procedure_id";
>
> query +=3D " INNER JOIN providers ON schedules.provider_id =3D
> providers.provider_id";
>
> may go as something like
>
> "where schedules.procedureId =3D XYZ and schedules.proviederId =3D ABC"
>
> cayenne sees the relation and creates a join or what ever it thinks that =
should be done.
>
>
>
> -----Urspr=FCngliche Nachricht-----
> Von: Frank [mailto:farocco@hotmail.com]
> Gesendet: Freitag, 2. Februar 2007 04:42
> An: user@cayenne.apache.org
> Betreff: Re: how to call a custom query
>
> >>I'm not sure why do you want to execute just a SQL. After doing query
> like you are proposing no data could be edited and saved back to db
> easily.
> Cayenne is a ORM, so why not getting a list of schedules ordered by
> procedure name:
> Expression exp =3D ExpressionFactory.matchExpr(Schedule.DATE_PROPERTY,
> new Date());
> <<
> I do not know how to join tables in the Modeler, that is why I opted for =
the
> rawSelect.
> If I just use the Schedule.class, there is no procedure_name field, just
> procedure_id
>
> Frank
> ----- Original Message -----
> From: "Marcin Skladaniec" <marcin@ish.com.au>
> To: <user@cayenne.apache.org>
> Sent: Thursday, February 01, 2007 10:09 PM
> Subject: Re: how to call a custom query
>
>
> > Hi
> > schedule_date =3D CURDATE():
> > what is the schedule_date data type ? Check if you are not comparing
> > TIMESTAMP with DATE.
> >
> >
> > I would use the functionality of NamedQuery. All you do you define a
> > Query in Modeller, example :
> >
> > SELECT #result('count(*)' 'int' 'C') FROM $entityName $whereClause
> >
> > and in the code you do something like :
> >
> > HashMap map =3D new HashMap();
> > map.put("entityName", entity);
> > map.put("whereClause", where);
> >
> > NamedQuery query =3D new NamedQuery("SpecialCount", map);
> > Map row =3D (Map) getContext().performQuery(query).get(0);
> > return ((Number) row.get("C")).intValue();
> >
> >
> > I'm not sure why do you want to execute just a SQL. After doing query
> > like you are proposing no data could be edited and saved back to db
> > easily.
> > Cayenne is a ORM, so why not getting a list of schedules ordered by
> > procedure name:
> > Expression exp =3D ExpressionFactory.matchExpr(Schedule.DATE_PROPERTY, =
 new
> > Date());
> > Ordering o =3D new Ordering(Schedule.PROCEDURE_PROPERTY
> > +"."+Procedure.NAME_PROPERTY);
> > SelectQuery sq =3D new SelectQuery(Schedule.class, exp);
> > sq.addOrdering(o);
> >
> > List schedules =3D context.performQuery(sq);
> > so you have the schedules
> > now just access the values you are want to display.
> >
> > Marcin
> >
> >
> > On 02/02/2007, at 12:41 PM, Frank wrote:
> >
> >> Here is my code.
> >> My table show no data. I have three records for this query.
> >>
> >> I am trying to display procedure_name, provider_name, office_phone,
> >> alt_phone
> >> The _Schedules class does not have these fields.
> >> What do I need to do?
> >>
> >> Thanks
> >> Frank
> >> public List getRecords() {
> >>
> >> String query =3D "SELECT ALL
> >> schedules.schedule_id,schedules.schedule_date,";
> >>
> >> query +=3D " procedures.procedure_name,providers.provider_name,";
> >>
> >> query +=3D " providers.office_phone,providers.alt_phone";
> >>
> >> query +=3D " FROM schedules";
> >>
> >> query +=3D " INNER JOIN procedures ON schedules.procedure_id =3D
> >> procedures.procedure_id";
> >>
> >> query +=3D " INNER JOIN providers ON schedules.provider_id =3D
> >> providers.provider_id";
> >>
> >> query +=3D " WHERE schedule_date =3D CURDATE()";
> >>
> >> query +=3D " ORDER BY procedure_name";
> >>
> >> DataContext context =3D DataContext.getThreadDataContext();
> >>
> >> SQLTemplate rawSelect =3D new SQLTemplate(Schedules.class, query);
> >>
> >> List records =3D context.performQuery(rawSelect);
> >>
> >> return records;
> >>
> >> }
> >>
> >> ----- Original Message ----- From: "Christian Mittendorf"
> >> <christian.mittendorf@freenet.de>
> >> To: <user@cayenne.apache.org>
> >> Sent: Thursday, February 01, 2007 7:16 PM
> >> Subject: Re: how to call a custom query
> >>
> >>
> >>> Please check out the documentation at the great new Cayenne homepage:
> >>>
> >>> http://cayenne.apache.org/doc20/queries.html
> >>>
> >>> ...especially the part on SQLTemplates
> >>>
> >>> Christian
> >>>
> >>> P.S. The link to the API  (http://cayenne.apache.org/1_2/api/ cayenne=
/
> >>> org/objectstyle/cayenne/query/package-summary.html) from  the 1.2
> >>> documentation returns a 404 error....
> >>>
> >>>
> >>> Am 02.02.2007 um 01:02 schrieb Frank:
> >>>
> >>>> Can anyone help me with the a short example on how to do this?
> >>>>
> >>>> Thanks
> >>>>
> >>>> Frank
> >>>> ----- Original Message ----- From: "Frank" <farocco@hotmail.com>
> >>>> To: <user@cayenne.apache.org>
> >>>> Sent: Thursday, February 01, 2007 11:33 AM
> >>>> Subject: how to call a custom query
> >>>>
> >>>>
> >>>>> Hello,
> >>>>>
> >>>>> I have the following raw query defined.
> >>>>> SELECT BSYDTAA.BSYPEMP.EMHSP#, BSYDTAA.BSYPEMP.EMYLNM,
> >>>>> BSYDTAA.BSYPEMP.EMYFNM, BSYDTAC.BPRPPCP.PCDEP4,
> >>>>> BSYDTAC.BPRPPCP.PCLDDS, BSYDTAA.BSYPEMP.EMEMP# AS EMEMP,
> >>>>> BSYDTAA.BSYPEMP.EMWPH# AS WPHONE FROM BSYDTAA.BSYPEMP,
> >>>>> BSYDTAC.BPRPPCP WHERE BSYDTAA.BSYPEMP.EMHSP# =3D  BSYDTAC.BPRPPCP.P=
CHSP#
> >>>>> AND BSYDTAA.BSYPEMP.EMDEP4 =3D  BSYDTAC.BPRPPCP.PCDEP4 AND
> >>>>> (BSYDTAA.BSYPEMP.EMHSP# IN (1,5)) AND  (BSYDTAA.BSYPEMP.EMDOT=3D0)
> >>>>> AND EMEMP# =3D ?
> >>>>>
> >>>>> How do I call this from code using the SelectQuery and passing  in
> >>>>> the value?
> >>>>>
> >>>>> Thanks
> >>>>>
> >>>>> Frank
> >>>>>
> >>>>
> >>>
> >>
> >
> > Marcin
> >
> >
> >
> >
>
>