i realize this is a little long, i've tried to reduce it to the simplist form...
say i have tables
Authors (Id, Name, ...)
Books(AuthorId, LanguageId, ...)
Languages(Id, LanguageName, Direction, ...)

i want to create the following sql:
select b.* from Authors a, Books b, Languages L
where (a.Id = b.AuthorId) AND (b.LanguageId = L.Id) AND l.Direction = "left-to-right"

using HSQL BUT instead of retrieving a bunch of columns i want the result to be a set of Book instances.

I have tried the following (based on gavin's response to ollie a few weeks ago) -
select elements(a.books) from Author a join a.books b, Languages L
where (a.id = :id) AND (b.language = L.id) AND (L.Direction = :dir)

where :id is set to some id value and :dir is set to, e.g., "left-to-right"

but the resulting set is the just the join of Authors and Books for the specified Author Id (:id) ... the resulting sql looks ok except that the 'elements(a.books) gets mapped to an additional entry in the where clause. I tried using 'select elements(b) ...' but that was rejected (cant find properties for b)

If i explicitly list the fields of b, e.g.,
select b.language, ... from ...
then i get the right set - but i'm trying to get hibernate to keep track of all the necessary columns.

How can i refer to 'b' in the select clause as a 'class' reference?

What am i missing ???



so the appropriate hibernate xdoclet/java classes are something like
( i've tried to give the minimum stuff) :
/**
* @hibernate.class table="Authors"
*/

public class Author
{
private int id;
private Set books;

/**
* @hibernate.id generator-class="xxx" type="int" column="ID" unsaved-value="0"
*/
public int getId( return id);

/**
* @hibernate.set name="books" table="Books"
* inverse="true"
* cascade="none"
* lazy="true"
* @hibernate.collection-key column="AuthorId"
* @hibernate.collection-composite-element class="Book"
*/
public Set getBooks()
{ return books; }
}
-----------------------------------

public class Book
{
private int langId;

/**
* @hibernate.property name="Language" column="LanguageId"
*/
public int getLanguageId( return langId);
}

--------------------------------------
/**
* @hibernate.class table="Languages"
*/
public class Language
{
private int id;
private String name;
private String direction;

/**
* @hibernate.id generator-class="xxx" type="int" column="ID" unsaved-value="0"
*/
public int getId() {return id };

/**
* @hibernate.property name="Direction" column="Direction"
*/
public getDirection() { return direction; }
}

Justin

thanks for the reply, unfortunately things are a little more complicated. The 'Book' class is not a 'top-level' table - it can only be accessed indirectly from the 'Author' class. The Book class models a legacy table which does not have an 'id' column - it has a multi-column unique key - which i don't know how to do using xdoclet tags.

But Book is really in a one-to-many relationship with Author - so i don't want to model it as a separate table.

If making 'Book' a top-level table is the only way to do this then i will either re-think the model or just explictly fetch each of the column values and construct the Book object as i iterate thru the results.

btw - i have not successfully used the 'select new Book(...)' syntax - hibernate runtime can't find the necessary constructor .

What about:


Justin