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" <[EMAIL PROTECTED]>
To: <[email protected]>
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 <[EMAIL PROTECTED]> 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");
}
}
