Selecting schema with JdbcTemplate

[amitkhanna]

Hi

I am using PostgreSQL and i need to select schema dynamically for each query based on the user.

Foe example, if user is abc, I have to run:
set SEARCH_PATH to abc;
select * from mytable;

(assuming that every user has mytable in his/her schema)

when i was using JDBC it was simple I used to create a connection and i was then able to run the 2 queries on the same connection.

Now I have switched to Spring+JDBC and i'm using JdbcTemplate to run the queries as i found it simple and easy to use. But JdbcTemplate creates a new connection every time i execute a query using the execute() method. Is there any way i can run multiple queries in on the same connection.

I think it will be better if i can select the schema at beginning of each transaction using @Transactional annotation

Can anyone suggest how to do this?

Thanks
Amit Khanna

[Marten Deinum]

For starters use proper tx management, that way you have the same connection for the whole transaction.