Re: How do I query a joined table in cayenne?

From: Mike Kienenberger (mkienen..mail.com)
Date: Tue Feb 27 2007 - 10:29:32 EST

  • Next message: Frank: "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:30:50 EST