Creating a new object instance in hibernate using a sql query

Question

I am attempting to create an object in hibernate using a query, which will then be saved back to the table representing the class.

Excerpt from hbm.xml file:

<class name="MyClass" table="MY_TABLE">
<id column="ID" name="ID">
   <generator class="sequence">
    <param name="sequence">MY_SEQ</param>
   </generator>
</id>
  <property column="VAL" name="val" type="string"/>
</class>

<sql-query name="myQuery">
  <return class="MyClass"/>
  SELECT MY_SEQ.nextval ID, 'something' VAL from DUAL
</sql-query>

Code snippet from test case:

MyClass myClass = (MyClass) session.getNamedQuery("myQuery").list().get(0);
Transaction t = session.beginTransaction();
session.save(myClass);
t.commit();

My aim is that there should now be a new record in table MY_TABLE, but the insert does not occur, I assume that this is due the fact that Hibernate does not know that the instance has not been persisted in the db.

I have tried changing the query to read:

SELECT NULL ID, 'something' VAL from DUAL

But this results in Hibernate not instantiating an object.

So how can i create a new object instance from a query that is not associated with a persisted instance of the class and use this to create a persisted instance?

Pascal Thivent · Accepted Answer · 2010-07-17 15:08:24Z

5

Update: I tested the approach suggested below and I couldn't get it working for this particular scenario, Hibernate expects you to select columns for all attributes while we definitely don't want the id. However, using a ResultTransformer did work:

16.1.5. Returning non-managed entities

It is possible to apply a ResultTransformer to native SQL queries, allowing it to return non-managed entities.
sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
        .setResultTransformer(Transformers.aliasToBean(CatDTO.class))
This query specified:

the SQL query string

a result transformer

The above query will return a list of CatDTO which has been instantiated and injected the values of NAME and BIRTHNAME into its corresponding properties or fields.

The documentation mentions returning non-managed entities but it also work with an entity (there is no reason it wouldn't work) and I could persist the transient entity successfully.

16.1.2. Entity queries

The above queries were all about returning scalar values, basically returning the "raw" values from the resultset. The following shows how to get entity objects from a native sql query via addEntity().

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

This query specified:

the SQL query string

the entity returned by the query

Assuming that Cat is mapped as a class with the columns ID, NAME and BIRTHDATE the above queries will both return a List where each element is a Cat entity.

If the entity is mapped with a many-to-one to another entity it is required to also return this when performing the native query, otherwise a database specific "column not found" error will occur. The additional columns will automatically be returned when using the * notation, but we prefer to be explicit as in the following example for a many-to-one to a Dog:

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

This will allow cat.getDog() to function properly.

~~But I don't think you should set the ID if you want to save it and want Hibernate to perform an insert.~~

edited Jul 17, 2010 at 15:08

answered Jul 16, 2010 at 17:58

Pascal Thivent

572k140 gold badges1.1k silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

stjohnroe Over a year ago

Pascal, the system that i am working on is generating financial transactions based upon services rendered. So what i am doing is scanning the DB for transactions that need to be generated. My plan was that the select would identify and in effect create the required transaction objects, which would then be processed (complex operations) and then stored as appropriate to the DB. In effect I am trying to get hibernate to create the transient objects for me and then later persist them as necessary. A secondary objective is to keep all the SQL statement config in the mapping file.

Pascal Thivent Over a year ago

@stjohnroe: OK, I see. I did some testing and couldn't get the initial suggestion to work as I wanted. But I've updated my answer with an alternative approach which seems to work.

Collectives™ on Stack Overflow

Creating a new object instance in hibernate using a sql query

1 Answer 1

16.1.5. Returning non-managed entities

See also

16.1.2. Entity queries

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

See also

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related