cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hans Poo" <h...@welinux.cl>
Subject Re: joins
Date Thu, 22 May 2008 22:00:30 GMT
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)


Mime
View raw message