Hi all,

I've got a query (that work) but that I found ugly because not the "hibernate" way. It's a mix between hql and Oracle functions ....

Can somebody show me the way to write it the proper way ? (Criteria or full hql, I prefer criterias ;) ).

I didn't manage to do it an other way.

here's the query:

List dates = null;

String query = "select distinct (to_char(cdc.id.datepoint,'MM')-1), (to_char(cdc.id.datepoint,'YYYY')) from "
+ CdcNettePdfPointSr.class.getName() + " as cdc "
+ " where cdc.id.conPdf.id = :idPdf "
+ "and to_char(cdc.id.datepoint,'HH24') not in (1,23)";

dates = getSession().createQuery(query).setInteger("idPdf",i_idPdf.intValue()).list();

What I'm doing is : I've got a table with dates in a column and a fk (the id).
I need to get a distinct list of month and year of these dates.

difficult no ?

Service workflowService = ServiceRegistry.getInstance().getService( Workflowdefinition.class );
List defList = workflowService.list( Expression.ilike( "name", query ));

Iterator defIter = defList.iterator();
while( defIter.hasNext() ) {
criterion = Expression.or( criterion, Expression.eq( "workflowdefinition", defIter.next() ));
}

.
.
.
.
This code ends up passing the criterion into the following method, in this case the Order obj would be null.


public List find( final Criterion criterion, final Order order ) {
long startTime = System.currentTimeMillis();
List result = null;
result = getHibernateTemplate().executeFind( new HibernateCallback() {

public Object doInHibernate(Session session) throws HibernateException {
Criteria criteria = session.createCriteria( getClazz() );
if( criterion != null ) {
criteria.add( criterion );
}
if( order != null ) {
criteria.addOrder( order );
}
return criteria.list();
}
}
);

logger.debug( "find( criterion, order ) for class ["+getClazz().getName()+"] took "+(System.currentTimeMillis() - startTime)+"ms.");
return result;
}


Hope this helps