Eric,
Have you tried with SQLTemplate ? i've found it extermely useful, in particular
when expressions
aren't enough (or my knowledge of them is not enough).
This example is for running code and a join with two tables: familia and
visita. Returns a list
of familias.
public List getPersonas(int ano) {
String sql = "select f.* from persona f, visita v "
+ " where f.id_comuna = #bind($id_comuna) "
+ " and v.id_persona = f.id_persona"
+ " and v.id_formulario = 1 "
+ " and extract(year from v.fec_visita) =
#bind($ano)";
Map parameters = new HashMap();
parameters.put("id_comuna", this.getId());
parameters.put("ano", ano);
SQLTemplate query = new SQLTemplate(Persona.class, sql);
query.setParameters(parameters);
System.err.println(query.toString());
return DataContext.getThreadDataContext().performQuery(query);
}
Good luck
Hans
On Thu, 22 May 2008 17:23:25 -0400, Eric Polino wrote
> Given three tables (This is not accurate SQL syntax...but you get the point).
>
> Reservation {
> ...
> siteId int foreign key with Site,
> ...
> }
>
> Site {
> ...
> siteId int primary key,
> typeId int foreign key with SiteType,
> ...
> }
>
> SiteType {
> ...
> siteTypeId int primary key,
> name varchar(32),
> ...
> }
>
> How can I do the following SQL statement using Expression,
> ExpressionFactory, SelectQuery, etc?
>
> select * from Reservation r, Site s, SiteType st where r.siteId =
> s.siteId and s.typeId = st.siteTypeId and st.name = 'foo'
>
> TIA.
>
> --
> Eric Polino
> Campground Automated Systems
--
Open WebMail Project (http://openwebmail.org)
