Hibernate sql query to criteria

Question

I have problem converting a raw sql query into a criteria.

My raw query looks like this:

select id,signalid,signalname from signals where (select count(*) from error_signal where signalid_gen=id) == 0;

The Error_Signal (ManyToMany JoinTable with extra field) has a composite PK where the error is mapped via primaryKey.error and the signal via primaryKey.signal

This is my criteria for getting all Signals

ProjectionList pList = Projections.projectionList(); pList.add(Projections.property(SignalEntity.ID).as(SignalEntity.ID)); pList.add(Projections.property(SignalEntity.SIGNAL_ID).as(SignalEntity.SIGNAL_ID)); pList.add(Projections.property(SignalEntity.SIGNALNAME).as(SignalEntity.SIGNALNAME)); Session session = DatabaseManager.getCurrentSession(); Criteria criteria = session.createCriteria(SignalEntity.class, "signal"); criteria.add(Restrictions.like(SignalEntity.SIGNALNAME, tmpSearch)); criteria.setProjection(pList);

Now I need to apply that Restriction. I have tried it with a DetachedCriteria without luck.

DetachedCriteria countSubquery = DetachedCriteria.forClass(ErrorSignalEntity.class); countSubquery.createCriteria(ErrorSignalEntity.SIGNAL_PK).add(Restrictions.eqProperty(SignalEntity.ID, "signal." + SignalEntity.ID)); countSubquery.setProjection(Projections.rowCount()); criteria.add(Subqueries.eq(new Integer(0), countSubquery));

The error I get is

Caused by: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: signalenti1_.id)

yes SignalEntity.ID is marked with Id and ErrorSignalEntity.primaryKey is marked with EmbeddedId because it is of the type ErrorSignalPK (composite PK) — Pascal
– Pascal, Commented May 28, 2015 at 12:17
At least show us your entities and the generated SQL which does not work. The corresponding DDL would also be helpful. — JimmyB
– JimmyB, Commented May 28, 2015 at 12:54

shokulei · Accepted Answer · 2017-04-14 16:30:37Z

Even this is an old question, but I ran into same requirement. I ended up using the Restrictions.sqlRestriction() function for it.

ProjectionList pList = Projections.projectionList(); pList.add(Projections.property(SignalEntity.ID).as(SignalEntity.ID)); pList.add(Projections.property(SignalEntity.SIGNAL_ID).as(SignalEntity.SIGNAL_ID)); pList.add(Projections.property(SignalEntity.SIGNALNAME).as(SignalEntity.SIGNALNAME)); Session session = DatabaseManager.getCurrentSession(); Criteria criteria = session.createCriteria(SignalEntity.class, "signal"); criteria.add(Restrictions.like(SignalEntity.SIGNALNAME, tmpSearch)); // *** Solution: add the special count check here **** criteria.add(Restrictions.sqlRestriction("(select count(*) from error_signal where signalid_gen={alias}.id) == 0")); criteria.setProjection(pList);

Hope this will help someone who ran into the same need.

Collectives™ on Stack Overflow

Hibernate sql query to criteria

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related