Derrick Petzold

The other day I wrote about how to do a IN and GROUP BY query using Java's de facto ORM, Hibernate. I thought it would be interesting to see how other ORMs handled the same query. This is the query I want to generate:

SELECT COUNT(*),state FROM download_request WHERE id IN (<id list>) GROUP BY state;

Below is the code, output and SQL generated for the three ORMs.

Hibernate

class HibernateDAO implements ApplicationDAO {
public Map getStateCounts(final Collection ids) {
  HibernateSession hibernateSession = new HibernateSession();
  Session session = hibernateSession.getSession();
  Criteria criteria = session.createCriteria(DownloadRequestEntity.class)
	.add(Restrictions.in("id", ids));
  ProjectionList projectionList = Projections.projectionList();
  projectionList.add(Projections.groupProperty("state"));
  projectionList.add(Projections.rowCount());
  criteria.setProjection(projectionList);
  List results = criteria.list();
  Map stateMap = new HashMap();
  for(Object[] obj: results) {
      	DownloadState downloadState = (DownloadState)obj[0];
       	stateMap.put(downloadState.getDescription().toLowerCase(), (Integer)obj[1]);
  }
  hibernateSession.closeSession();
  return stateMap;
}
public static void main(String args[]) {
    HibernateDAO downloadRequestDAO = new HibernateDAO();	
    Collection ids = new ArrayList();
    for (int i = 1000;  i < 1010; i++ )
        ids.add(i);
    Map stateCounts =  downloadRequestDAO.getStateCounts(ids);
    for (String state: stateCounts.keySet()) {
        System.out.println(state + ": " + stateCounts.get(state));
    }
}
}

Output

failed: 5
downloaded: 1
completed: 4

SQL

select this_.state as y0_, count(*) as y1_ from download_request this_ 
where this_.id in (1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009) 
group by this_.state

Django

counts = models.DownloadRequest.objects.filter(
    id__in=range(1000, 1010),
).values('state').annotate(Count('state'))
for count in counts:
    print count

Output

{'state': u'FAILED', 'state__count': 5}
{'state': u'COMPLETED', 'state__count': 4}
{'state': u'DOWNLOADED', 'state__count': 1}

SQL

SELECT `download_request`.`state`, COUNT(`download_request`.`state`) 
AS `state__count` FROM `download_request` 
WHERE `download_request`.`id` IN (1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009) 
GROUP BY `download_request`.`state` ORDER BY NULL

SQLAlchmey

query = session.query(
    func.count(DownloadRequest.state), DownloadRequest.state,
).filter(
    DownloadRequest.id.in_(range(1000,1010)),
).group_by(DownloadRequest.state)
for count in query.all():
    print count

Output

(4L, 'COMPLETED')
(1L, 'DOWNLOADED')
(5L, 'FAILED')

SQL

SELECT count(download_request.state) AS count_1, download_request.state 
AS download_request_state FROM download_request 
WHERE download_request.id IN (1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009) 
GROUP BY download_request.state

As you can see SQLAlchemy is the most similar to SQL, django's is the briefest and Hibernate (obviously) is the most Java-like. Of the three I'd say I like SQLAlchemy the best as it is the most similar to SQL and me being from an SQL background it is the most natural. However all three get the job done and it is always great to have options.