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 <farocc..otmail.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" <mkienen..mail.com>
> To: <use..ayenne.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 <farocc..otmail.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" <mkienen..mail.com>
> >> To: <use..ayenne.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 <farocc..otmail.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");
> >> >> }
> >> >>
> >> >>
> >> >> }
> >> >>
> >> >>
> >> >
> >>
> >>
> >
>
>
This archive was generated by hypermail 2.0.0 : Tue Feb 27 2007 - 10:45:19 EST