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 Mon, 26 Feb 2007 16:32:32 GMT
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