Map an entity to an Native SQL Query

Question

Is this possible to map a SQL Native query (instead of a table) with an Entity without annotations (using XML configuration)??

I know that i can make a View in the database and map this view as a workaround to this, but I try to avoid this solution because the Schema is automatically generated from the Entities model, and I would have to create the view manually afterwards.

I know also that with annotations there exist something like @SqlResultSetMapping, but I am not allowed to use annotations.

Adriaan Koster · Accepted Answer · 2011-09-07 21:06:05Z

For mapping legacy tables on an entity using Hibernate you could use a DAO method like this one:

public List<Audit> getAudits(Integer branchId, Integer locationId, Date fromDate, Date toDate, int first, int count, String sortProperty, boolean ascending) {

    // columns renamed to match sort properties and hibernate mapping
    StringBuilder query = new StringBuilder(
        "SELECT AUD_Number AS number,"
        + " AUD_Number_REL AS relationNumber,"
        + " AUD_Name_REL AS relationName,"
        + " AUD_Date AS date,"
        + " FROM audit WHERE 1");
        if (branchId!= null) {
            query.append(String.format(" AND AUD_Number_BRN = %s", branchId));
        }
        if (locationId!= null) {
            query.append(String.format(" AND AUD_Nummer_LOC = %s", locationId));
        }
        if (fromDate != null) {
            query.append(String.format(" AND AUD_Date >= %s", DateConverter.dateToSql(fromDate)));
        }
        if (toDate != null) {
            query.append(String.format(" AND AUD_Date <= %s", DateConverter.dateToSql(toDate)));
        }
        query.append(String.format(" ORDER BY %s %s", sortProperty, ascending ? "ASC" : "DESC"));
        query.append(String.format(" LIMIT %s, %s", first, count));
        return (List<Audit>) getSession().createSQLQuery(query.toString()).addEntity(Audit.class).list();
}

Meanwhile in mappings.hbm.xml:

<class name="Audit">
    <id name="number" type="integer" />
    <property name="relationNumber" type="integer" />
    <property name="relationName" type="string" />
    <property name="date" type="datetime" />
</class>
etc...

Omitting spring context for brevity.

Does this help?

sleske · Accepted Answer · 2011-09-07 07:29:57Z

1

A view is the proper way. If you want to somehow store an SQL query in the database, and refer to its results regularly (as if it were a table), then that is precisely the description of a view.

So just create a view and map it.

If that does not solve your problem, please edit your answer to explain why a solution using a view is not practical. Maybe we can help you then.

answered Sep 7, 2011 at 7:29

sleske

84k40 gold badges196 silver badges239 bronze badges

3 Comments

Anantha Sharma Over a year ago

i agree, even though Hibernate supports this feature (to map a query to an entity) its best to use a SQL view in its place.. unless you don't have access to modify schema (to add the view).

Adriaan Koster Over a year ago

Whether or not using a view is a good solution depends on the type of RDBMS, the level of controle you have over it and your decision to allow an extra layer of mapping logic (and thus maintenance complexity) to live in your database. I would consider using Hibernate's support for native SQL queries, will post an answer about that shortly.

Adriaan Koster Over a year ago

I'll have to post a full answer tonight, I don't have access to the example code I want to use at work. In the meantime see this: docs.jboss.org/hibernate/core/3.3/reference/en/html/…

Collectives™ on Stack Overflow

Map an entity to an Native SQL Query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related