Tuesday, November 11, 2008

Use Projection.sqlProjection For MySQL Specific Functions

While at work, I had a problem and I need to project my query to look like this:

CONCAT(CONCAT(YEAR(documentDate), "_"), MONTH(documentDate))

Since hibernate doesnt support mysql or database specific queries, I needed to use Project.sqlProjection to customize my projection list. Anyway the code looks like this:

final Criteria c = session.createCriteria(JobServiceBill.class);
              
final ProjectionList projectionList = Projections.projectionList();
                
final StringBuilder sb = new StringBuilder();
sb.append("CONCAT(CONCAT(YEAR(documentDate), \"_\"), MONTH(documentDate)) AS yearMonth");
                
final Projection distinctYearMonthProj = Projections.distinct(Projections.sqlProjection(sb.toString(),
                        new String[] {"yearMonth"}, new Type[] {Hibernate.STRING}));
               
projectionList.add(distinctYearMonthProj);
                
c.setProjection(projectionList);
                
 return c.list();