Hibernate entities without underlying tables

Question

I have a really ugly legacy database system that I need to integrate with. Essentially I'm doing some read only reporting on the system, and I don't want to set up a thousand entities representing each of the tables that I'm working on. Instead, I'd like to just define an Entity for each of the report-types that I generate (essentially a union of a bunch of columns from different tables), and then let hibernate map from the nasty (many joined, many unioned) sql query to a list of such entities.

The question is: can I create an entity that doesn't have an underlying table, and use a sql statement to populate a list of said entities?

Thanks!

score 8 · Accepted Answer · edited Mar 21 '14 at 17:13

We do that sort of thing all the time - and here is how we do it:

Define a simple bean-like object to represent each row of output in your report:

public class CityStateRevenueReport {

    private String mId;
    private String mState;
    private String mCity;
    private Double mRevenue;

    public String getId() { return mId; }
    public void setId(String i) { mId = i; }
    public String getState() { return mState; }
    public void setState(String s) { mState = s; }
    public String getCity() { return mCity; }
    public void setCity(String c) { mCity = c; }
    public Double getReveneue() { return mRevenue; }
    public void setRevneue(Double d) { mRevenue = d; }
}

Define a hibernate mapping file, CityStateRevneueReport.hbm.xml:

<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd&quot;>
<hibernate-mapping>
    <class entity-name="CityStateRevenueReport">
        <id name="Id" type="java.lang.String" column="report_id">
            <generator class="sequence" />
        </id>
        <property name="city" type="string" column="city" />
        <property name="state" type="string" column="state" />
        <property name="revenue" type="double" column="revenue" />
    </class>
    <sql-query name="runReport">
        <![CDATA[ 
        SELECT {r.*} FROM 
            (select some_id_value as report_id, 
                    state_abbreviation as state, 
                    city_name as city, 
                    dollar_amount as revenue 
               from -- tables, joins, other SQL insanity 
             ) r
        ]]>
        <return alias="r" class="CityStateRevenueReport" />
    </sql-query>
</hibernate-mapping>

Then run the query and populate instances:

public List<CityStateRevenueReport> runReport() {

    List<CityStateRevenueReport> reports = 
                             new ArrayList<CityStateRevenueReport>();
    List<HashMap> maps = session.getNamedQuery("runReport").list()
    for ( HashMap map : results ) {
        CityStateRevenueReport report = new CityStateRevenueReport();
        report.setState(map.get("state"));
        report.setCity(map.get("city"));
        report.setRevenue(Double.parseDouble(map.get("revenue"));
        reports.add(report);
    }
    return reports;
}

I changed to this answer because it uses Hibernate and answers the original question better. — idbentley, Apr 03 '14 at 15:46
In the hibernate mapping class, you should use "name" instead of "entity-name" and hibernate will be able to create the result objects on it's own, without the boilerplate code to manually create them. It would be nice if this could be done only with annotations, but I couldn't find a way. — Alex G, Aug 04 '15 at 17:30

Scott A · Answer 2 · 2011-11-03T23:18:24.973

Use an Entity query:

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#d0e13696

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);

Or create a view in the database and map against that.

Here's a longer tutorial using addEntity:

http://timezra.blogspot.com/2009/05/mapping-hibernate-entities-to-views.html

private Collection<AuthorAggregate> findByFirstName() {
    return sessionFactory.getCurrentSession() //
            .createSQLQuery(AUTHORS_BY_FIRST_NAME) // 
            .addEntity(AuthorAggregate.class) //
            .list();
}

score 2 · Answer 3 · answered Nov 04 '11 at 00:02

2

If you're free to choose hibernate or not, I suggest you take a look at Spring JDBC. it's lighter than hibernate and does the job right. With your requirements it will suit nicely into the picture.

answered Nov 04 '11 at 00:02

WeMakeSoftware

9,039
5
34
52

thanks a lot - this seems like a much better solution for my needs. – idbentley Nov 04 '11 at 18:02

Hibernate entities without underlying tables

3 Answers3

Linked