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 15:37:10 GMT
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