Stored Procedure Conundrum

[shadowrunner52]

Environment:
Database - Oracle 10i
App Server - OC4J 10.1.3.5.0
JDK - 1.5.0_09

I am required by design to interact with the database solely through stored procedures/functions and user-defined types. To this end I have created a collection of classes that extend org.springframework.jdbc.object.StoredProcedure. I use JNDI to lookup the datasource and so I have also created a class that extends org.springframework.jndi.JndiObjectFactoryBean and I have overridden getObject() as such:

public class CustomJndiObjectFactoryBean extends JndiObjectFactoryBean {

	@Override
	public DataSource getObject() {
		System.out.println("-- GETTING DATASOURCE --");
		DataSource ds = (DataSource)super.getObject();
		try {
			ds.getConnection().setTypeMap(MGFEMappings.TYPE_MAP);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ds;
	}
}

My stored procedure class looks like this:

public class FnGetUserByName extends StoredProcedure {
	
	private static final String SF_NAME = "pkg_user.sf_get_user";
	
	public FnGetUserByName(DataSource dataSource) {
		super(dataSource, SF_NAME);
		try {
			System.out.println("--Creating FnGetUserByName--" + this.getJdbcTemplate().getDataSource().getConnection().getTypeMap().get(User.SQLTypeName));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		setFunction(true);
		declareParameter(new SqlOutParameter("user", Types.STRUCT, User.SQLTypeName));
		declareParameter(new SqlParameter("userName", Types.VARCHAR));
		compile();
	}
	
	public User execute(String userName) {
		try {
			System.out.println("--Executing FnGetUserByName--" + this.getJdbcTemplate().getDataSource().getConnection().getTypeMap().get(User.SQLTypeName));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Map<String, Object> inParams = new HashMap<String, Object>(1);
		inParams.put("userName", userName);
		Map<String, Object> outParams = execute(inParams);
		User user = null;
		if(outParams.size() > 0) {
			try {
				user = (User) outParams.get("user");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return user;
	}
}

Both my dataSource and my stored procedure object are configured in the applicationContext.xml as:

<bean id="dataSource" class="editor.util.CustomJndiObjectFactoryBean">
	<property name="jndiName" value="jdbc/MyDS" />
</bean>

<bean id="fnGetUserByName" class="editor.hibernate.dao.function.FnGetUserByName">
	<constructor-arg><ref bean="dataSource"/></constructor-arg>
</bean>

However when I run my app, the debug code shows:

11/01/28 13:13:01 --Creating FnGetUserByName--class editor.hibernate.model.User
11/01/28 13:13:02 --Executing FnGetUserByName--null

and then of course I get the error:

11/01/28 13:13:03 java.lang.ClassCastException: oracle.sql.STRUCT

So what happens between the time the class is instantiated and the time the execute method is called that the connection type map is getting clobbered?
Is there a better way to set the type map than by overriding getObject()?

I've been banging my head on this one for a while now so any help would be appreciated.

[amiladomingo]

Hey,

Output param is a Struct right, then how do you do this?

user = (User) outParams.get("user");

[amiladomingo]

I think the problem is StoredProcedure.execute() method always gets a new connection and your following code won't make any affect.

ds.getConnection().setTypeMap(MGFEMappings.TYPE_MAP);

There must be a way to set it to all the connections (something like getUnderlyingConnection())

[shadowrunner52]

Hey,

Output param is a Struct right, then how do you do this?

user = (User) outParams.get("user");

User implements java.sql.SQLData.

[shadowrunner52]

I think the problem is StoredProcedure.execute() method always gets a new connection and your following code won't make any affect.

ds.getConnection().setTypeMap(MGFEMappings.TYPE_MAP);

There must be a way to set it to all the connections (something like getUnderlyingConnection())

Ah! You are absolutely correct. After looking through the Spring source code I see that in JdbcTemplate.execute has the line:

Connection con = DataSourceUtils.getConnection(getDataSource());

I'm not sure what my options are at this point. Any ideas?

Thanks!

[shadowrunner52]

Okay, I think I've found a solution. Since my data source connection is retrieved through JNDI I created a custom class that extends JndiObjectFactoryBean and implements DataSource. Then I override getObject to return the custom class, and in getConnection I set the type map. Here is the relevant code:

public class CustomDataSource extends JndiObjectFactoryBean implements DataSource {
	
	private DataSource dataSource;

	@Override
	public Object getObject() {
		this.dataSource = (DataSource) super.getObject();
		return this;
	}

	public Connection getConnection() throws SQLException {
		Connection conn = dataSource.getConnection();
		conn.setTypeMap(Mappings.TYPE_MAP);
		return conn;
	}
}

Thanks for the help!

[shadowrunner52]

Alternately, since I'm using the 'session per request' model, I have instead extended ConnectionHolder and overrode the method getConnection as such:

public class CustomConnectionHolder extends ConnectionHolder {

	public CustomConnectionHolder(Connection connection) {
		super(connection);
	}

	@Override
	public Connection getConnection() {
		Connection conn = super.getConnection();
		try {
			conn.setTypeMap(Mappings.TYPE_MAP);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
}

Then in my interceptor I just add the data source to the TransactionSynchronizationManager. It turns out that DataSourceUtils.doGetConnection will check to see if the transaction is being synchronized and if so, will get the data source from the TransactionSynchronizationManager.