cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Kienenberger" <mkien...@gmail.com>
Subject Re: How do I query a joined table in cayenne?
Date Tue, 27 Feb 2007 16:39:26 GMT
Sorry, you'll have to ask on the Click mailing list for click-specific issues.

The code I sent you will work in a generic Cayenne environment.  Try
it out and see.
It's only a couple more lines of code to perform query once you have
the expression built.

On 2/27/07, Frank <farocco@hotmail.com> wrote:
> In Click, this should map to the department name.
> departments.pcldds
>
> I would load the table column like this
> column = new Column("departments.pcldds")
> I think Click would translate this to
> getDepartments().getPcldds()
>
> I tried various combinations and nothing seems to work.
> My query select does not show a join
> here is the output:
>
> INFO  QueryLogger: SELECT t0.EMEMP#, t0.EMWPH#, t0.EMYFNM, t0.EMYLNM,
> t0.EMHSP#, t0.EMDEP4 FROM BSYDTAA.BSYPEMP t0 WHERE (t0.EMHSP# IN (?, ?)) AND
> (UPPER(RTRIM(t0.EMYLNM)) LIKE UPPER(CAST (? AS VARCHAR(25)))) AND
> (UPPER(RTRIM(t0.EMYFNM)) LIKE UPPER(CAST (? AS VARCHAR(15)))) [bind: 1, 5,
> '%', '%'] - prepared in 31 ms.
>
> Frank
>
> ----- Original Message -----
> From: "Mike Kienenberger" <mkienenb@gmail.com>
> To: <user@cayenne.apache.org>
> Sent: Tuesday, February 27, 2007 10:58 AM
> Subject: Re: How do I query a joined table in cayenne?
>
>
> >I don't understand what
> >
> > column = new Column("toDepartments().getPcldds()")
> >
> > is supposed to do.
> >
> > Also, my examples may not exactly match your model.  It looks like
> > your "toDepartments()" relationship is actually "departments()".
> > Adjust accordingly.
> >
> > public static final String DEPARTMENTS_PROPERTY = "departments";
> >
> >
> > On 2/27/07, Frank <farocco@hotmail.com> wrote:
> >> I still get an error
> >> Is this right?
> >>
> >> No matching getter method found for property toDepartments() on class
> >> stemc.cayenne.as400.Bsypemp
> >> column = new Column("toDepartments().getPcldds()");
> >>
> >> Frank
> >> ----- Original Message -----
> >> From: "Mike Kienenberger" <mkienenb@gmail.com>
> >> To: <user@cayenne.apache.org>
> >> Sent: Tuesday, February 27, 2007 10:44 AM
> >> Subject: Re: How do I query a joined table in cayenne?
> >>
> >>
> >> > You'd show in the table:
> >> >
> >> > getFirstName()
> >> > getLastName()
> >> > toDepartment().getDeptName()
> >> >
> >> > You would search on:
> >> >
> >> > andExp() of the following:
> >> >
> >> > ExpressionFactory.matchExp(Bsypemp.FIRST_NAME_PROPERTY, firstName)
> >> >
> >> > ExpressionFactory.matchExp(Bsypemp.LAST_NAME_PROPERTY, lastName)
> >> >
> >> > ExpressionFactory.matchExp(Bsypemp.TO_DEPARTMENT_PROPERTY + "." +
> >> > bprppcp.DEPT_NAME_PROPERTY, deptName)
> >> >
> >> >
> >> > On 2/27/07, Frank <farocco@hotmail.com> wrote:
> >> >> If I have two files joined:
> >> >> bsypemp
> >> >>     firstName
> >> >>     LastName
> >> >>     deptno
> >> >> bprppcp
> >> >>     deptno
> >> >>     deptName
> >> >>
> >> >> I want to list in my table:
> >> >>     firstName
> >> >>     LastName
> >> >>     deptName
> >> >>
> >> >> I want to search on
> >> >>     firstName
> >> >>     LastName
> >> >>     deptName
> >> >>
> >> >> as long as I do not reference the deptName, my table loads fine.
> >> >> What am I doing wrong?
> >> >>
> >> >> Regards,
> >> >> Frank
> >> >> ----- Original Message -----
> >> >> From: "Mike Kienenberger" <mkienenb@gmail.com>
> >> >> To: <user@cayenne.apache.org>
> >> >> Sent: Tuesday, February 27, 2007 10:29 AM
> >> >> Subject: Re: How do I query a joined table in cayenne?
> >> >>
> >> >>
> >> >> > Are you just trying to find an object by the property on a related
> >> >> > object?
> >> >> >
> >> >> > ExpressionFactory.matchExp(Contact.PRIMARY_CONTACT_NAME_PROPERTY
+
> >> >> > "."
> >> >> > + ContactName.FIRST_NAME_PROPERTY, firstName)
> >> >> >
> >> >> > For example, if you're trying to find an employee by department
> >> >> > name,
> >> >> >
> >> >> > ExpressionFactory.matchExp(Employee.DEPARTMENT_PROPERTY + "."
+
> >> >> > Department.NAME_PROPERTY, targetDepartmentName)
> >> >> >
> >> >> > You can chain together as many relationships as you like, but
be
> >> >> > aware
> >> >> > of potential outer join issues if there's any chance that one
of the
> >> >> > relationships may be null.
> >> >> >
> >> >> > For example, here's a fun one that operates on two different kinds
> >> >> > of
> >> >> > entities, with parts of the expression path being built
> >> >> > conditionally:
> >> >> >
> >> >> > ===========================================================
> >> >> >        String pathToFeePrefix;
> >> >> >        String pathToFeePaymentHistoryPrefix;
> >> >> >        Class searchEntityClass;
> >> >> >        if (searchByFeeInsteadOfFeePaymentReceipt)
> >> >> >        {
> >> >> >        pathToFeePrefix = "";
> >> >> >            pathToFeePaymentHistoryPrefix =
> >> >> > Fee.FEE_PAYMENT_RECEIPT_LIST_PROPERTY + ".";
> >> >> >            searchEntityClass =
> >> >> > com.gvea.eng_work_mgmt.entity.cayenne.Fee.class;
> >> >> >        }
> >> >> >        else
> >> >> >        {
> >> >> >        pathToFeePrefix = FeePaymentReceipt.FEE_PROPERTY + ".";
> >> >> >            pathToFeePaymentHistoryPrefix = "";
> >> >> >            searchEntityClass =
> >> >> > com.gvea.eng_work_mgmt.entity.cayenne.FeePaymentReceipt.class;
> >> >> >        }
> >> >> >
> >> >> >
> >> >> > [... one example usage...]
> >> >> >
> >> >> >
> >> >> > oredExpressionList.add(ExpressionFactory.matchExp(pathToFeePrefix
+
> >> >> > Fee.FEE_CYCLE_PROPERTY + "."
> >> >> >                + FeeCycle.INITIAL_AUTHORIZATION_DOCUMENT_PROPERTY
+
> >> >> > "."
> >> >> >                +
> >> >> > AuthorizationDocument.DEPENDENT_PERMIT_DOCUMENT_PROPERTY + "."
> >> >> >                + PermitDocument.AGENCY_PROPERTY, agency));
> >> >> > ===========================================================
> >> >> >
> >> >> >
> >> >> > On 2/26/07, Frank <farocco@hotmail.com> wrote:
> >> >> >> Hi Mike,
> >> >> >>
> >> >> >> Here is my orginal jdbc version of select
> >> >> >>
> >> >> >> String sql = "SELECT BSYDTAA.BSYPEMP.EMHSP# AS HSP,
> >> >> >> BSYDTAA.BSYPEMP.EMYLNM,
> >> >> >> BSYDTAA.BSYPEMP.EMYFNM, BSYDTAC.BPRPPCP.PCDEP4,
> >> >> >> BSYDTAC.BPRPPCP.PCLDDS,
> >> >> >> BSYDTAA.BSYPEMP.EMEMP# AS EMEMP, BSYDTAA.BSYPEMP.EMWPH# AS
WPHONE,
> >> >> >> BSYDTAA.BSYPEMP.EMSCH, BSYDTAA.BSYPEMP.EMOTSH FROM BSYDTAA.BSYPEMP,
> >> >> >> BSYDTAC.BPRPPCP WHERE BSYDTAA.BSYPEMP.EMHSP# =
> >> >> >> BSYDTAC.BPRPPCP.PCHSP#
> >> >> >> AND
> >> >> >> BSYDTAA.BSYPEMP.EMDEP4 = BSYDTAC.BPRPPCP.PCDEP4 AND
> >> >> >> (BSYDTAA.BSYPEMP.EMHSP#
> >> >> >> IN (1,5)) AND (BSYDTAA.BSYPEMP.EMDOT=0)";
> >> >> >>             sql += " AND EMYLNM like ?";
> >> >> >>             sql += " AND EMYFNM like ?";
> >> >> >>             sql += " AND PCLDDS like ?";
> >> >> >>             sql += " AND PCDEP4 like ?";
> >> >> >>             sql += " ORDER BY EMYLNM";
> >> >> >>
> >> >> >> I can just do this:
> >> >> >> rs = pstmt.executeQuery();
> >> >> >> rs.getString("PCLDDS");
> >> >> >> to get the department name.
> >> >> >>
> >> >> >> In Cayenne I do not understand how to limit records by department
> >> >> >> name
> >> >> >>    Integer[] hspArray = {new Integer(1), new Integer(5)};
> >> >> >>      Expression e =
> >> >> >> ExpressionFactory.inDbExp(Bsypemp.EMHSPPOUND_PK_COLUMN,
> >> >> >> hspArray);
> >> >> >>         e =
> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.EMYLNM_PROPERTY,
> >> >> >> lname
> >> >> >>         +"%"));
> >> >> >>         e =
> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.EMYFNM_PROPERTY,
> >> >> >> fname
> >> >> >>                 +"%"));
> >> >> >>        // I'm lost here e =
> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.class.DEPARTMENTS_PROPERTY,
> >> >> >> pcldds
> >> >> >>        //         +"%"));
> >> >> >>
> >> >> >>      SelectQuery query = new SelectQuery(Bsypemp.class,e);
> >> >> >>      List records = context.performQuery(query);
> >> >> >>
> >> >> >> Thanks
> >> >> >> Frank
> >> >> >> ----- Original Message -----
> >> >> >> From: "Mike Kienenberger" <mkienenb@gmail.com>
> >> >> >> To: <user@cayenne.apache.org>
> >> >> >> Sent: Friday, February 23, 2007 10:02 PM
> >> >> >> Subject: Re: How do I query a joined table in cayenne?
> >> >> >>
> >> >> >>
> >> >> >> >I don't follow what you're trying to do.
> >> >> >> > Can you give an example of what you expect the sql to
look like
> >> >> >> > when
> >> >> >> > you're done, and give some example data so I can understand
what
> >> >> >> > you're trying to do?
> >> >> >> >
> >> >> >> > On 2/23/07, Frank <farocco@hotmail.com> wrote:
> >> >> >> >> Hello,
> >> >> >> >>
> >> >> >> >> I cannot figure out how to query the pcldds, pcdep4
fields
> >> >> >> >> What am I doing wrong?
> >> >> >> >>
> >> >> >> >> Thanks
> >> >> >> >>
> >> >> >> >> Frank
> >> >> >> >>
> >> >> >> >> ** Java Code Start
> >> >> >> >>  DataContext context = DataContext.getThreadDataContext();
> >> >> >> >>      Integer[] hspArray = {new Integer(1), new Integer(5)};
> >> >> >> >>      Expression e =
> >> >> >> >> ExpressionFactory.inDbExp(Bsypemp.EMHSPPOUND_PK_COLUMN,
> >> >> >> >> hspArray);
> >> >> >> >>         e =
> >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.EMYLNM_PROPERTY,
> >> >> >> >> lname
> >> >> >> >>         +"%"));
> >> >> >> >>         e =
> >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.EMYFNM_PROPERTY,
> >> >> >> >> fname
> >> >> >> >>                 +"%"));
> >> >> >> >>        // I'm lost here e =
> >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.class.DEPARTMENTS_PROPERTY,
> >> >> >> >> pcldds
> >> >> >> >>        //         +"%"));
> >> >> >> >>
> >> >> >> >>      SelectQuery query = new SelectQuery(Bsypemp.class,e);
> >> >> >> >>      List records = context.performQuery(query);
> >> >> >> >> ** Java Code End
> >> >> >> >>
> >> >> >> >> Here is my first class
> >> >> >> >> package stemc.cayenne.as400.auto;
> >> >> >> >>
> >> >> >> >> /** Class _Bsypemp was generated by Cayenne.
> >> >> >> >>   * It is probably a good idea to avoid changing
this class
> >> >> >> >> manually,
> >> >> >> >>   * since it may be overwritten next time code is
regenerated.
> >> >> >> >>   * If you need to make any customizations, please
use subclass.
> >> >> >> >>   */
> >> >> >> >> public class _Bsypemp extends
> >> >> >> >> org.objectstyle.cayenne.CayenneDataObject {
> >> >> >> >>
> >> >> >> >>     public static final String E_MEMPPOUND_PROPERTY
=
> >> >> >> >> "eMEMPpound";
> >> >> >> >>     public static final String E_MWPHPOUND_PROPERTY
=
> >> >> >> >> "eMWPHpound";
> >> >> >> >>     public static final String EMOTSH_PROPERTY =
"emotsh";
> >> >> >> >>     public static final String EMSCH_PROPERTY = "emsch";
> >> >> >> >>     public static final String EMYFNM_PROPERTY =
"emyfnm";
> >> >> >> >>     public static final String EMYLNM_PROPERTY =
"emylnm";
> >> >> >> >>     public static final String DEPARTMENTS_PROPERTY
=
> >> >> >> >> "departments";
> >> >> >> >>
> >> >> >> >>     public static final String EMEMPPOUND_PK_COLUMN
= "EMEMP#";
> >> >> >> >>     public static final String EMHSPPOUND_PK_COLUMN
= "EMHSP#";
> >> >> >> >>
> >> >> >> >>     public void setEMEMPpound(java.math.BigDecimal
eMEMPpound) {
> >> >> >> >>         writeProperty("eMEMPpound", eMEMPpound);
> >> >> >> >>     }
> >> >> >> >>     public java.math.BigDecimal getEMEMPpound() {
> >> >> >> >>         return (java.math.BigDecimal)readProperty("eMEMPpound");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setEMWPHpound(java.math.BigDecimal
eMWPHpound) {
> >> >> >> >>         writeProperty("eMWPHpound", eMWPHpound);
> >> >> >> >>     }
> >> >> >> >>     public java.math.BigDecimal getEMWPHpound() {
> >> >> >> >>         return (java.math.BigDecimal)readProperty("eMWPHpound");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setEmotsh(String emotsh) {
> >> >> >> >>         writeProperty("emotsh", emotsh);
> >> >> >> >>     }
> >> >> >> >>     public String getEmotsh() {
> >> >> >> >>         return (String)readProperty("emotsh");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setEmsch(String emsch) {
> >> >> >> >>         writeProperty("emsch", emsch);
> >> >> >> >>     }
> >> >> >> >>     public String getEmsch() {
> >> >> >> >>         return (String)readProperty("emsch");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setEmyfnm(String emyfnm) {
> >> >> >> >>         writeProperty("emyfnm", emyfnm);
> >> >> >> >>     }
> >> >> >> >>     public String getEmyfnm() {
> >> >> >> >>         return (String)readProperty("emyfnm");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setEmylnm(String emylnm) {
> >> >> >> >>         writeProperty("emylnm", emylnm);
> >> >> >> >>     }
> >> >> >> >>     public String getEmylnm() {
> >> >> >> >>         return (String)readProperty("emylnm");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setDepartments(stemc.cayenne.as400.Bprppcp
> >> >> >> >> departments) {
> >> >> >> >>         setToOneTarget("departments", departments,
true);
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>     public stemc.cayenne.as400.Bprppcp getDepartments()
{
> >> >> >> >>         return
> >> >> >> >> (stemc.cayenne.as400.Bprppcp)readProperty("departments");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> Here is my second class
> >> >> >> >> package stemc.cayenne.as400.auto;
> >> >> >> >>
> >> >> >> >> /** Class _Bprppcp was generated by Cayenne.
> >> >> >> >>   * It is probably a good idea to avoid changing
this class
> >> >> >> >> manually,
> >> >> >> >>   * since it may be overwritten next time code is
regenerated.
> >> >> >> >>   * If you need to make any customizations, please
use subclass.
> >> >> >> >>   */
> >> >> >> >> public class _Bprppcp extends
> >> >> >> >> org.objectstyle.cayenne.CayenneDataObject {
> >> >> >> >>
> >> >> >> >>     public static final String PCDEP4_PROPERTY =
"pcdep4";
> >> >> >> >>     public static final String PCINA_PROPERTY = "pcina";
> >> >> >> >>     public static final String PCLDDS_PROPERTY =
"pcldds";
> >> >> >> >>     public static final String PCSDDS_PROPERTY =
"pcsdds";
> >> >> >> >>
> >> >> >> >>     public static final String PCDEP4_PK_COLUMN =
"PCDEP4";
> >> >> >> >>     public static final String PCHSPPOUND_PK_COLUMN
= "PCHSP#";
> >> >> >> >>
> >> >> >> >>     public void setPcdep4(java.math.BigDecimal pcdep4)
{
> >> >> >> >>         writeProperty("pcdep4", pcdep4);
> >> >> >> >>     }
> >> >> >> >>     public java.math.BigDecimal getPcdep4() {
> >> >> >> >>         return (java.math.BigDecimal)readProperty("pcdep4");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setPcina(String pcina) {
> >> >> >> >>         writeProperty("pcina", pcina);
> >> >> >> >>     }
> >> >> >> >>     public String getPcina() {
> >> >> >> >>         return (String)readProperty("pcina");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setPcldds(String pcldds) {
> >> >> >> >>         writeProperty("pcldds", pcldds);
> >> >> >> >>     }
> >> >> >> >>     public String getPcldds() {
> >> >> >> >>         return (String)readProperty("pcldds");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>     public void setPcsdds(String pcsdds) {
> >> >> >> >>         writeProperty("pcsdds", pcsdds);
> >> >> >> >>     }
> >> >> >> >>     public String getPcsdds() {
> >> >> >> >>         return (String)readProperty("pcsdds");
> >> >> >> >>     }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> }
> >> >> >> >>
> >> >> >> >>
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >>
> >
>
>

Mime
View raw message