Provide a way to use a native JDBC extractor for metadata processing in SimpleJdbcInsert [SPR-7611]

[spring-projects-issues]

Thomas Risberg opened SPR-7611 and commented

To access metadata from synonyms in Oracle we need access to the native Oracle connection - so we need a way to set a native JDBC extractor.

---

Affects: 3.0.4

Issue Links:

• Refactor SimpleJdbcInsert/Call to make it easier to provide custom meta data providers [SPR-7634] #12290 Refactor SimpleJdbcInsert/Call to make it easier to provide custom meta data providers

Referenced from: commits 0f92482, 63b8ae0

1 votes, 1 watchers

[spring-projects-issues]

Mike Youngstrom commented

Hello Thomas, I believe this issue may have been created as a result of my support request.

A quick thought. Is there a particular reason you would consider solving this problem by using a JDBC extractor instead of just allowing us to provide a custom TableMetaDataProvider? I suppose that that allowing me to provide a metadata extractor would work it may be easier if I could just provide a custom MetaDataProvider then in my custom MetaDataProvider I could use Websphere's com.ibm.websphere.rsadapter.WSCallHelper.jdbcCall() method to invoke get/setIncludeSynonyms.

If you do ultimately decide to use a NativeJDBCExtractor then that would still work for me, however, IBM does not support how WebSphereNativeJdbcExtractor acquires a native connection. Luckily I'm using Websphere 7 so if you do go the NativeJdbcExtractor route I can create a custom NativeJdbcExtractor that uses JDBC's unwrap() method to return a Connection that implements OracleConnection. This approach would be supported by IBM.

Anyway, either way you go it would work. It just seems to me allowing to provide a custom TableMetadataProvider would be the more powerful and extensible solution to this problem.

Thanks,
Mike

[spring-projects-issues]

Juergen Hoeller commented

A side note: Such a WebSphere 7 compliant NativeJdbcExtractor already exists - see Jdbc4NativeJdbcExtractor. We did not make it a default even when running on Java 6 since such extraction won't work unless the entire setup (not only the JDBC driver but also the connection pool) is fully JDBC 4 enabled. Also, we don't generally know whether native JDBC extraction is necessary in the first place, so I guess it'll never actually be a default...

Juergen

[spring-projects-issues]

Mike Youngstrom commented

Ah, I didn't know you had a Jdbc4NativeJdbcExtractor. However, in this case I don't believe that would work for Websphere 7 either. When you invoke unwrap() I believe Websphere's conneciton pool creates a proxy of the interface passed in that they use the block dangerous methods. So I don't think we would be able to invoke get/setIncludeSynonyms on the resulting proxy of type Connection.class. However, if you enhanced Jdbc4NativeJdbcExtractor to allow us to specify the native interface to get when invoking unwrap then that would work.

Mike

[spring-projects-issues]

Mike Youngstrom commented

Issue to add support for specifying unwrap interface types. #12269

[spring-projects-issues]

Thomas Risberg commented

The underlying problem is that we need access to the Oracle connection to set the switch to include synonyms. That seems to be the case for several connection pools I have tried. In your case you might want to use an improved Jdbc4NativeJdbcExtractor or rely on your own custom TableMetaDataProvider. I'm not against making it easier to provide your own TableMetaDataProvider. It might require a bit of refactoring so I'm not sure it will make 3.0.5. Adding the NativeJdbcExtractor is straightforward and I'm committing these changes soon.

Thomas

[spring-projects-issues]

Mike Youngstrom commented

Sounds good. The nativejdbcextractor will work for my case.

[spring-projects-issues]

Thomas Risberg commented

Another way of solving this would be to provide a connection property of "includeSynonyms" set to true. Not sure how easy is to provide custom connection properties for the connection pool you are using.

Thomas

[spring-projects-issues]

Mike Youngstrom commented

Actually for my situation setting "includeSynonyms" globally would probably be the best solution. However, I don't believe Websphere allows you to set custom connection properties. I'm asking them now.

[spring-projects-issues]

Mike Youngstrom commented

Unfortunately, this doesn't completely solve my problem. When I use the OracleNativeJdbc4Extractor Websphere returns a proxy object. The class for this proxy object does not start with "oracle" which is what the OracleTablemetaDataProvider checks for.

It seams that that instead of checking to see if the connection's class starts with "oracle" it would make more sense to check to see if it is an instanceof "oracle.jdbc.OracleConnection".

Another comment. It would be nice if SimpleJdbcInsert could automatically use the nativeJdbcExtractor set on the jdbcTemplate I pass into SimpleJdbcInsert.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
      jdcbTemplate.setNativeJdbcExtractor(new OracleJdbc4NativeJdbcExtractor());
SimpleJdbcInsertOperations exampleModelJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
          .withTableName("BLANK_MODEL")
          .includeSynonymsForTableColumnMetaData();

[spring-projects-issues]

Juergen Hoeller commented

Added autodetection of the NativeJdbcExtractor from the given JdbcTemplate. Will revisit the "oracle" detection as well.

Juergen

[spring-projects-issues]

Juergen Hoeller commented

Committed - this will be in the next 3.0.5 snapshot, coming up in about two hours.

Mike, would be great if you could give it a try, in particular with respect to includeSynonyms support which has been quite heavily rearranged...

Thomas, could you please quickly review OracleTableMetaDataProvider's latest version - just to be on the safe side?

Juergen

[spring-projects-issues]

Mike Youngstrom commented

Works perfectly. Tested with Websphere+Oracle and Tomcat+UCP+Oracle.

Thanks tons.

[spring-projects-issues]

Thomas Risberg commented

Looks good to me. Didn't find any regressions.

-Thomas