Announcement Announcement Module
No announcement yet.
Selecting schema with JdbcTemplate Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Selecting schema with JdbcTemplate


    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?

    Amit Khanna

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