File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Projection from inner join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Projection from inner join" Watch "Projection from inner join" New topic
Author

Projection from inner join

Siby George
Greenhorn

Joined: Mar 22, 2011
Posts: 14
Hi All,

I am trying to project fields from 2 tables which are joined and also I need to order based on 2 columns, both from different tables.

The example SQL query is:

SELECT table1.col1, table2.col1, table2.col3 FROM table1 JOIN table2 ON table1.colm = table2.coln
WHERE table1.colm IN (1,2,3) ORDER BY table1.col1, table2.col1

I am trying this:

Example:

Criteria c = session.createCriteria(table2.class);
c.createCriteria(table2.PROP_table1).add(Property.forName(table1.colm).in(1,2,3));
ProjectionList proList = Projections.projectionList();
proList.add(Projections.property(table2.col1));
proList.add(Projections.property(table2.col2));
// proList.add(Projections.property(table1.col1));
c.setProjection(proList);
c.addOrder(Order.asc(table1.col1));
c.addOrder(Order.asc(table2.col1));
List result = c.list();


Any example showing projection from two tables (joined) would do.

Thanks
Siby
Siby George
Greenhorn

Joined: Mar 22, 2011
Posts: 14
This can be achieved by using alias names for inner table like

criteria.createAlias(table1.table2, "table2");

and then using projection as,
.add(Projections.property("table2."+table2.col1))
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Projection from inner join