hibernate detached criteria - How to add a native sql statement

Question

Hi I have very big problem. I have a DetachedCriteria and I named it dc. I declared it this way DetachedCriteria dc = getDetachedCriteria(). I want to add a collation statement before the order by. The purpose of collation is to handle ñ. The statement I want to add is COLLATE utf8_spanish_ci. I did it this way dc.add(Restrictions.sqlRestriction(" COLLATE utf8_spanish_ci ")). Of course I got an error because this is wrong. I don't know to do it. Please help.

Answer 1

You can execute native SQL queries in order to take advantage of your particular database features, this is how is done in hibernate using detached criteria...

List<YourEntity> list = (List<YourEntity>) yourEntityDAO.getHibernateTemplate().execute(
    new HibernateCallback() {
    @Override
    public Object doInHibernate(Session session) throws HibernateException {
        SQLQuery sq = session.createSQLQuery("SELECT * FROM MY_TABLE");
        return sq.addEntity(YourEntity.class).list();
    }
});