Hibernate: How To Alias a Column for a Non-Entity Sql Query?

Question

All,

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

I'm attempting to use the SqlQuery Hibernate class to create a List of non-entity POJO's, but am running into trouble trying to alias the columns in the SQL query. When I attempt to put aliases in my sql (e.g SELECT o.id as orderId, ps.code as prescriptionStatus...), hibernate complains that it can't find column "x", where column "x" is the non-aliased column name (e.g "id" instead of "orderId").

If I don't alias my returned columns, all is well, but my POJO is then forced to have properties with the non-aliased field names, or I have to manage mappings within the POJO (getters with nice names returning the non-aliased field names).

Here's 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;

Thank you!

Answer 1

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

For example :

 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;

The transformer will then look for a class called RelatedPrescriptionOrderLine with the setter called setPrescriptionId() , setPrescriptionStatus() etc , and populate the result to its instance via these setters.