Return-Path: Delivered-To: apmail-cayenne-user-archive@www.apache.org Received: (qmail 22108 invoked from network); 27 Feb 2007 16:43:34 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 27 Feb 2007 16:43:34 -0000 Received: (qmail 6466 invoked by uid 500); 27 Feb 2007 16:43:38 -0000 Delivered-To: apmail-cayenne-user-archive@cayenne.apache.org Received: (qmail 6457 invoked by uid 500); 27 Feb 2007 16:43:37 -0000 Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cayenne.apache.org Delivered-To: mailing list user@cayenne.apache.org Received: (qmail 6434 invoked by uid 99); 27 Feb 2007 16:43:37 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 27 Feb 2007 08:43:37 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=MSGID_FROM_MTA_HEADER,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of farocco@hotmail.com designates 65.54.246.138 as permitted sender) Received: from [65.54.246.138] (HELO bay0-omc2-s2.bay0.hotmail.com) (65.54.246.138) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 27 Feb 2007 08:43:25 -0800 Received: from hotmail.com ([65.54.175.81]) by bay0-omc2-s2.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Tue, 27 Feb 2007 08:43:04 -0800 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 27 Feb 2007 08:43:04 -0800 Message-ID: Received: from 209.4.252.100 by BAY104-DAV9.phx.gbl with DAV; Tue, 27 Feb 2007 16:43:03 +0000 X-Originating-IP: [209.4.252.100] X-Originating-Email: [farocco@hotmail.com] X-Sender: farocco@hotmail.com From: "Frank" To: References: <45DC441B.2080800@opensource.lk> <8f985b960702231902o71680e6bled446c27177debf0@mail.gmail.com> <8f985b960702270729w583a1ea0me97934381a3b8c47@mail.gmail.com> <8f985b960702270744w6e312fbaq55b8cae63da08f@mail.gmail.com> <8f985b960702270758y7b72c14es98a841be01bea0a7@mail.gmail.com> <8f985b960702270839y157f733dq6c00311f5b0c472e@mail.gmail.com> Subject: Re: How do I query a joined table in cayenne? Date: Tue, 27 Feb 2007 11:42:58 -0500 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-OriginalArrivalTime: 27 Feb 2007 16:43:04.0629 (UTC) FILETIME=[5A115650:01C75A8E] X-Virus-Checked: Checked by ClamAV on apache.org ok, Thanks Mike... Frank ----- Original Message ----- From: "Mike Kienenberger" To: Sent: Tuesday, February 27, 2007 11:39 AM Subject: Re: How do I query a joined table in cayenne? > 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 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" >> To: >> 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 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" >> >> To: >> >> 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 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" >> >> >> To: >> >> >> 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 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" >> >> >> >> To: >> >> >> >> 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 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"); >> >> >> >> >> } >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> } >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> > >> >> >> >> >> >> >> >> > >> >> >> >> >> > >> >> >