cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Frank" <faro...@hotmail.com>
Subject Re: How do I query a joined table in cayenne?
Date Tue, 27 Feb 2007 16:08:15 GMT
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