alter session using JDBCTemplate

[vivash]

I've to run the following query before I run any other sql in my application,

String sessionSql = "ALTER SESSION SET nls_date_format = 'YYYY-MM-dd:HH24:MI:ss'"

I tried using JDBCTemplate.execute(sessionSql), but that doesn't do anything. Is there a way to alter session using JDBCTemplate?

The alternative to using JDBCTemplate would be directly using the JDBC Statement, statement.execute(sessionSql);

I was hoping I can use JDBCTemplate so I don't have to deal with connection close, sql exceptions etc.

Any help?

thanks,
-vivek

[karldmoore]

The execution should work fine via JdbcTemplate. Are you using transactions? If not isn't it going to grab a new connection each time and thus your setting wouldn't work?

[vivash]

Thanks for the response. I am not using transaction, do we have any example how to use it with JDBCTemplate?

Does every method call (like query, execute) in JDBCTemplate gets a new connection? I am injecting a connection pool datasource to the JDBCTemplate, would it grab a new connection and use a new session everytime?

Once I set a session and since I am using connection pool would that session setting remain with that connection forever? Basically, do I need to reset connection settings?

[karldmoore]

The reference manual is the best place for examples. Ok there is a connection pool, but aren't you potentially getting a different connection from it everytime. Will those settings you've applied be applied to all connections?
http://www.springframework.org/docs/reference/transaction.html#transaction-declarative

[vvaradarajan]

You can try the method:
JdbcTemplate.execute(StatementCallback SC) -- The StatementCallback is an interface with a method doInStatement(Statement stmt). You can code this function to execute all the 'alter' statements and finally follow them up with the statement you want to execute. All of these will execute within the same connection.

Note that the 'alter' statements are executed each time this method is called.