Execute database session initialization SQL in Spring JDBC

Question

I am using Spring JdbcTemplate/SimpleJdbcTemplate in combination with an Oracle datasource (oracle.jdbc.pool.OracleDataSource) via JNDI lookup. This application is running on Oracle Application Server (OAS) 10.1.3 connecting to an Oracle 11g database.

The database connection that I am making is to an account that has a couple of non-default database roles. I need to enable these roles (probably via the Oracle "set role" command) when the physical connection to the database is established. I don't see any way to configure the Oracle datasource to issue a SQL command to do this. Because my Java code is using the Spring JDBC classes, it doesn't deal with opening and closing connections. Does Spring offer some way to run some initialization SQL upon getting a connection?

Datasource definition:

<?xml version = '1.0' encoding = 'windows-1252'?>
<data-sources xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:noNamespaceSchemaLocation="http://xmlns.oracle.com/oracleas/schema/data-sources-10_1.xsd">
 <native-data-source data-source-class="oracle.jdbc.pool.OracleDataSource"
                      jndi-name="jdbc/xxx" name="xxx"
                      url="jdbc:oracle:thin:@hostxxx:1541:xxx"
                      password="zzzzz" user="username"/>
</data-sources>

Spring bean definition:

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/xxx" />

axtavt axtavt · Accepted Answer · 2011-04-25T19:22:53

You can always create a proxy for the DateSource and override its getConnection().

Perhaps even using AOP:

@Aspect
public class DataSourceAspect {
    @AfterReturning(value = "execution(* javax.sql.DataSource.getConnection(..))", 
        returning = "c")
    public void afterGetConnection(Connection c) {
        ...
    }
}

Execute database session initialization SQL in Spring JDBC

1 Answers