Hibernate version: 3.1.2

I want to return a non-entity from a named query. That is, the returned objects from a named query are non-entities in the sense that I do not need to persist them. That is because, the named query does an aggregate function (e.g. select sum(), etc... )

However, I am not sure on how to go about this.
Reading the Reference Manual, I looked at:

-------------------------------------------------
1) return-scalar

http://www.hibernate.org/hib_docs/v3/re ... medqueries

The link above talks about using <return-scalar>, using the following example:



But it does not exactly say how do you use it in code. That is, what Java type is returned for a single row when using <return-scalar> ??

-------------------------------------------------
2) Custom-value types

http://www.hibernate.org/hib_docs/v3/re ... pes-custom

Looks like what I need, but ... the link above shows how to you custom-value types for __properties within a defined entity__.


-------------------------------------------------
How should I proceed about it ?

Just to show what I was trying to do, see the snippet from a mapping.
It throws an exception saying "Unknown entity: com.mig.connectivity.hibernate.UserMessageAggregate", which is right ... because it is indeed not an entity ... and I don't want it to be an entity.

1) Each entry in the returned list is an Object[] big enough to store all the scalars. So for mySqlQuery, you'd get a list of Object[2], where Object[0] is a String containing name, and Object[1] is a Long containing age.

2) If you want to return an object that isn't an entity, then you can't use a return alias. You can either return the Object[] as above, or use HQL's select new functionality. That only works if all the columns you're selecting from are mapped in entities. It's also only in v3.1, afaik. It's described in section 14.5. "The select clause", of the 3.1 ref docs.

It is not possible in 3.1 to return anything else than scalar, components and entities via native sql.

that said i have implemented support for ResultTransformer's to native sql and HQL in Hibernate 3.2 which will allow you to do this and more (but it is not yet committed to svn...but soon ;)

I currently have the very same need. I have this fairly big SQL query (roughly 110 lines) which computes various statistics using cross-tabulations and stuff like that. The result of the query is 22 different numbers. To simplify handling of this I've created a "Benchmark" object which is a very dump POJO with a bunch of getters and setters. To process the query I have to do something like this:

Benchmark benchmark = new Benchmark();
benchmark.setCountByTech(((Double) result[0]).intValue());

The second line there will have to be repeated for the 22 different numbers, with only some variance on the set method being called and the index used. It would be incredibly cool if I could define this column-to-attribute mapping in the sql query (in the mapping file), and have the named query return arbitrary objects. If I could simply say that my named SQL query returns "Benchmark", and named the result set columns the same as the Benchmark properties, then all the mapping should happen automagically. My current project involves a lot of statistical queries, and since these are not persistent entities, Hibernate seems to get in my way.

I can also imagine that I would be neat (at least for some quick-and-dirty cases) to say for example that the named sql query returns a Map, much like the queryForMap/queryForList methods in Spring work. This sort of thing is indispensable for executing arbirary queries and quickly inspecting the results, and while I don't want that sort of code in my production system, it is very nice to have in my test code where I frequently need to use simple queries to verify the results of more complex queries.

Will your ResultTransformer support somethine like this?

If not, are there plans for something like this?

i think so ;)

Here is some examples based on having a query like the following (remember this is work in progress so maybe this api will change sligtly but the concept/result will be the same):

query = s.createSQLQuery("select x as name, y as age from something");

Then



will return Map's with entries for "name" and "age"



will return instances of PersonDTO on which setName(String) and setAge(Long) have been called.

And this will work for both sql and hql (you can get Map, List and constructor beans today. ResultTransformer is a more generic way
of doing "select new xxx" in HQL)

Sweet! I hope you'll have this release really soon :) I'd love to ditch those last pieces of Spring code in favour of an all-Hibernate setup. Fewer jars, fewer ways of doing things, simpler code. Happy happy.

its targetted for hibernate 3.2

It's times like this I'd like to be able to rate in other people's topics. Someone heap praise on max for me :)

I could edit my points and give myself a heap of points from you, but somehow that doesn't sound fair ;)

Good grief max, have you been at work for over 11 hours? You should really buy a playstation or something.

i got one but it never praises me ;)

That new feature with "select new()" was very powerful.

Now what if I wanted to return non-entities from a stored procedure call as well ?

The example in the online docs:

http://www.hibernate.org/hib_docs/v3/re ... l#sp_query



... works if Employment was an entity.

What if I wanted to return a non-entity from a stored procedure call within a named query in my mapping file ? ( I want all queries externalised into the mapping files ). From the looks of the docs and the replies in here ... it looks like it is not possible at the moment ... except for perhaps using Object[]. The online docs also says: "Notice stored procedures currently only return scalars and entities".

Is there a possibility to have this in a future release ?

Thanks

the "select new" have been there for a loong time....the new thing is resultTransformer which will be in 3.2 and it will to return nonentities from stored procedures.

currently no plan for specifying it in the named queries, but you can always apply a resulttransformer in code, also on named queries.