*sigh of relief b/c he was directed to new forum*
*copies and pastes his post from the old forum*

Gavin,
There is a feature you are planning on putting into Hibernate 2.2 (was 2.1 but got pushed). "named native SQL queries defined in mapping file" My team and I are very interested in this feature and I was just wondering if you were planning to do with 2.2 the same that you did with 2.1, being release 2.2 beta 1 at the same time as the official release of 2.1.

Also, I noticed that in the query example ...

<sql-query name="mySqlQuery">
<return alias="person" class="eg.Person"/>
SELECT {person}.NAME AS {person.name}, {person}.AGE AS {person. age}, {person}.SEX AS {person.sex}
FROM PERSON {person} WHERE {person}.NAME LIKE 'Hiber%'
</sql-query>

you prefix all of the columns with the object as opposed to the table name then "as" them to the object.property. I'm wondering what exactly is NEEDED in these situations. Do we have to "as" them like that and is it possible to use table.column as opposed to {object}.column in some places?

Lastly, when you use the ":replacementValue" format... is there anything planned for future development where we can pass in a map of all the replacements and there would be a mechanism that goes in, interates the map and does the proper "set{Type}" on the query for each name-value pair in the map? I'm wondering this mainly b/c if you are going to be building it soon (or already have and I don't know about it), I won't bother building one.

Thanx

We have not added named sql queries yet to hibernate - any patches for doing it is welcome ;)


[qoute]
Also, I noticed that in the query example ...

<sql-query name="mySqlQuery">
<return alias="person" class="eg.Person"/>
SELECT {person}.NAME AS {person.name}, {person}.AGE AS {person. age}, {person}.SEX AS {person.sex}
FROM PERSON {person} WHERE {person}.NAME LIKE 'Hiber%'
</sql-query>

you prefix all of the columns with the object as opposed to the table name then "as" them to the object.property. I'm wondering what exactly is NEEDED in these situations. Do we have to "as" them like that and is it possible to use table.column as opposed to {object}.column in some places?
[/quote]

createSQLQuery() utilizes the exact same code HQL queries uses to get the data from an resultset - thus it is a requirement that the column names is aliased exactly the same way as if it was retreived from the original mapped table. Furthermore having the {} syntax makes it quite easy to avoid having hardcoded sql column/table names.

So, you most likely need to "as" them, but you are totally free to do table.column instead of {class}.column



I think we would accept such a patch. You would like something like Query.setPropertiesFromMap(Map map);, right ?

Have you taken a look at Query.setProperties(Object); which does what you mention via get/setters on an Object ?

(I reckon using this as inspiration (look at AbstractQuery.setProperties(Object)) will make it easy for you to do the above request ;)

/max

Max,
Thanks for the info. Is the named sql query from file still something you guys are planning to do and if so, when do you see that happening.

Also in reference to ...



Actually we are planning on doing something more like...

call:
ArrayList results = (ArrayList) executeQuery( sqlString, parmMap );

method:
public List executeQuery( String sql, HashMap parms );

I will talk to my team about writing something that you guys could implement in Hibernate. It shouldn't be a problem.

Another question comes up from your post...

Query.setProperties( Object );
So I just pass in one of my persistant objects for this and it will map the items to the correct spot?

Does this imply I need to use the {class}.column in my SQL query, or possibly even have to use {class.property}?

We want it to happen - not just having the time right now ,)



First - I hope you change those HashMap and ArrayList to Map and List ;)
Secondly - I makes much more sense to have the method on Query instead of having it standalone (or on Session which is where you would put this - right ?)



If you have
"select .... from .... where something > :from"
and a object with a getFrom() method then
q.setProperties(x);

will take the value from x.getFrom() and set correctly as the value of the :from parameter.



I don't follow that question ;)

The ONLY requirement for the sql statement is that the resulting resulset's column contains the same column names as Hibernate would expect if performing it with HQL...and with HQL all these column names get aliased (to eg. from_0) and you could just write that literally - OR use the usefull and helpfull {class.property} syntax.

Why don't you take a look at the unit test - and simply just try to execute the queries ? There are NO black-magic behind this ;)

OK cool. Well if we look into creating something like this, I will let you know.



If we do make something like this, then we would put the method on the Query Object and it would take a "Map." Of course I didn't see the setProperties( Object ) method in the api the first time through. Must have skipped over it. After seeing that, I'm not sure how much value sending in a map would be. We will evaluate and see what happens.



That's exactly what I needed to know. Thank you.



That's ok, you answered it above! :)



I will do that, thanks again.