Hibernation: how is a column alias for a non-Entity related Sql query?

Question

All,

Does anyone know how I can direct the SqlQuery class to use aliases for columns returned in a Non Managed Entity query?

I'm trying to use the SqlQuery Hibernate class to create a list of POJO non-entities, but I'm having trouble trying column aliases in an SQL query. When I try to put aliases in my sql (e.g. SELECT o.id as orderId, ps.code as prescriptionStatus... ), hibernate complains that it cannot find column "x", where column "x" is the name of the smoothed column (for example, "id" instead of "orderId").

If I don't have the aliases of my returned columns, everything is fine, but my POJO is then forced to have properties with names with a field alias, or I need to manage the mappings inside the POJOs (recipients with good names that return names without anti-aliasing).

Here is my code

 //TODO: change builder to a name query --jg StringBuilder sql = new StringBuilder(); sql.append("SELECT o.id,\n"); sql.append(" pet.name,\n"); sql.append(" o.order_date,\n"); sql.append(" rx_view.prescription_id,\n"); sql.append(" rx_view.code\n"); sql.append("FROM order_line_item oli\n"); sql.append(" JOIN order_detail o\n"); sql.append(" ON o.id = oli.order_id\n"); sql.append(" JOIN order_line_item_pet olip\n"); sql.append(" ON olip.order_line_item_id = oli.id\n"); sql.append(" JOIN pet\n"); sql.append(" ON pet.id = olip.pet_id\n"); sql.append(" LEFT JOIN (SELECT olip.order_line_item_id order_line_item_id,\n"); sql.append(" olip.prescription_id,\n"); sql.append(" ps.code\n"); sql.append(" FROM prescription_order_line_item olip\n"); sql.append(" JOIN prescription p\n"); sql.append(" ON olip.prescription_id = p.id\n"); sql.append(" JOIN prescription_status ps\n"); sql.append(" ON p.status_id = ps.id) rx_view\n"); sql.append(" ON rx_view.order_line_item_id = oli.id\n"); sql.append("WHERE oli.order_id IN (SELECT o.id\n"); sql.append(" FROM order_detail o\n"); sql.append(" JOIN order_line_item oli\n"); sql.append(" ON o.id = oli.order_id\n"); sql.append(" JOIN prescription_order_line_item poli\n"); sql.append(" ON oli.id = poli.order_line_item_id\n"); sql.append(" JOIN prescription rx\n"); sql.append(" ON rx.id = poli.prescription_id\n"); sql.append(" WHERE rx.id = :prescriptionId)\n"); SQLQuery query = baseDao.getSession().createSQLQuery(sql.toString()); query.setLong("prescriptionId", prescriptionId); query.setResultTransformer(Transformers.aliasToBean(RelatedPrescriptionOrderLine.class)); List results = query.list(); return results; 

Thanks!

Answer 1

You can use addScalar (String columnAlias, Type Type) to explicitly alias the columns of your own SQL:

For instance:

  SQLQuery query = baseDao.getSession().createSQLQuery("SELECT o.id as orderId, ps.code as prescriptionStatus..."); query.setLong("prescriptionId", prescriptionId); query.addScalar("orderId",StandardBasicTypes.INTEGER ) query.addScalar("prescriptionStatus",StandardBasicTypes.STRING ) query.setResultTransformer(Transformers.aliasToBean(RelatedPrescriptionOrderLine.class)) List results = query.list(); return results; 

Then the transformer will look for a class called RelatedPrescriptionOrderLine with an installer called setPrescriptionId() , setPrescriptionStatus() , etc., and populate the result with an instance through these setters.