Using named query parameter in string

HUMPPA · **Joined:** Tue Sep 08, 2009 9:49 am **Posts:** 23

Hi Hibernat0rs,

I'm new here... great forum, awesome framework!

Database: Postgresql 8.4 + Postgis 1.4.0
Using Hibernate Spatial Release 1.0-M2

How can I use a named query parameter in an SQL string?
Example SQL-query:

Code:

SELECT {c.*} 
FROM country c 
WHERE 
c.boundingbox && ST_GeomFromText('MULTIPOINT(:swlng :swlat,:nelng :nelat)',4326) 

Now if I try to set the named parameters in my code, Hibernate trows an exception saying that it can't find the query parameters in the sql command.
I found out that it can't find the parameters because they are between the single quotes (part of the string).

Independent from my example, is there a way to escape the query parameters located between single quotes, so hibernate can find the parameters?

Regards
humppa!

Cameron McKenzie · **Posted:** Tue Sep 08, 2009 10:44 am

Well, you can always do something like this:

Code:

String loginName;
/*obtain the loginName from user somehow!!!*/ 
String sqlQuery = 
          "select * from user where loginName = ?";
PreparedStatement ps = con.prepareStatement(sqlQuery);
/*replace the first ? mark 
with the value held by the variable*/
ps.setString(1, loginName);
ResultSet rs = ps.executeQuery();

http://www.hiberbook.com/HiberBookWeb/l ... medqueries

HUMPPA · **Joined:** Tue Sep 08, 2009 9:49 am **Posts:** 23

Cameron, thank you for your reply.

Unfortunately it does not work using the question mark either.

Quote:

SELECT {c.*}
FROM country c
WHERE
c.boundingbox && ST_GeomFromText('MULTIPOINT(? ?,? ?)',4326)

It then throws an index out of bounds exception.

When I remove the single quotes ' for testing, hibernate replaces the ? marks by the vars I set with setParameter(). So I would say that you can't use named parameter nor question mark parameters.

HUMPPA · **Joined:** Tue Sep 08, 2009 9:49 am **Posts:** 23

And the solution is (works on postgresql):

Code:

SELECT {c.*}
FROM country c
WHERE
c.boundingbox && ST_GeomFromText('MULTIPOINT('||:swlng||' '||:swlat||','||:nelng||' '||:nelat||')',4326) 

cheers
Humppa