CODEWITHSUNDEEP
javahibernatecriteria
Pascal
Pascal

Reputation: 2154

Hibernate sql query to criteria

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)

Upvotes: 1

Views: 3384

Answers (1)

shokulei
shokulei

Reputation: 105

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.

Upvotes: 1

Related Questions