Re: Spring integration and related changes
Lukas Eder
I'll CC this to the user group as well, as this is probably of general interest
> Your code is great, very good impression.
Thank you
> I am trying integrate JooQ with Spring.
> Let me explain a bit problems that I faced (very briefly, night).
>
> 1. JooQ Factory is not thread safe (because Connection is not thread safe)
> - created project jOOQ-spring that contains thread safe Factory proxy.
This would be a valuable extension, that might be useful to other
users as well. I checked upon the proxy. I'm not too familiar with
Spring for managing data sources. I imagine, that transactions are
somehow maintained outside of this proxy? What would some sample
service code look like?
I'll add Spring integration as a feature request:
https://sourceforge.net/apps/trac/jooq/ticket/897
> - added interface FactoryOperations in jOOQ project, that declares
> non-static public operations in Factory class
> - added setters to SchemaMapping class (to simplify injecting configuration
> parameters in spring configuration)
That makes sense.
> 2. some JooQ Factory methods return SQLException (this is checked exception)
> - SQLException removed from method signature, operations return runtime
> DataAccessException instead of checked SQLException
> This exception is the same as DataAccessException in Spring
> http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/dao/DataAccessException.html
> 3. no abstraction in SQLException handling (different databases have
> different errorCodes)
> - added SQLExceptionTranslator
> The idea is to map vendor-specific error codes to DataAccessException
> descendants
> http://www.koders.com/xml/fidE70F96602F7F254B935C711E60F1D141C60CD1DB.aspx
> Implementation is very simple (just stub) at this moment. (todo)
That's a nice concept. I wasn't aware of this. This will be feature
request #898:
https://sourceforge.net/apps/trac/jooq/ticket/898
> 4. jars can not be deployed into OSGI environment (jar's are not bundles)
> - added configuration of maven-bundle-plugin to pom.xml
Is that what org.apache.felix does? Thanks for adding this. It will be
included with
https://sourceforge.net/apps/trac/jooq/ticket/899
> Preliminary version of these changes in attachment. I am not sure that all
> these changes are correct.
> Suppose that these changes are important.
Release 2.0 is the right time for such changes. Thanks a lot for your help!
Cheers
Lukas
2011/11/1 Sergey Epik <serge...@gmail.com>:
> Hi Lukas,
>
> Your code is great, very good impression.
> I am trying integrate JooQ with Spring.
> Let me explain a bit problems that I faced (very briefly, night).
>
> 1. JooQ Factory is not thread safe (because Connection is not thread safe)
> - created project jOOQ-spring that contains thread safe Factory proxy.
> - added interface FactoryOperations in jOOQ project, that declares
> non-static public operations in Factory class
> - added setters to SchemaMapping class (to simplify injecting configuration
> parameters in spring configuration)
> Here is example of spring configuration:
>
>
> <bean id="jooQFactoryProxy" class="org.jooq.spring.JooQFactoryProxy">
> <property name="dataSource" ref="dataSource"/>
> <property name="schemaMapping">
> <bean class="org.jooq.SchemaMapping">
> <property name="defaultSchema" value="PROJ_${app.schema}"/>
> <property name="schemaMapping">
> <map>
> <entry key="PROJ_AA" value="PROJ_AA_${app.schema}"/>
> </map>
> </property>
> </bean>
> </property>
> <property name="dialect">
> <value type="org.jooq.SQLDialect">ORACLE</value>
> </property>
> </bean>
>
> inject it into the service:
>
> <bean id="fooService" class="com.example.FooServiceImpl">
> <property name="jooQ" ref="jooQFactoryProxy"/>
> </bean>
>
> 2. some JooQ Factory methods return SQLException (this is checked exception)
> - SQLException removed from method signature, operations return runtime
> DataAccessException instead of checked SQLException
> This exception is the same as DataAccessException in Spring
> http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/dao/DataAccessException.html
> 3. no abstraction in SQLException handling (different databases have
> different errorCodes)
> - added SQLExceptionTranslator
> The idea is to map vendor-specific error codes to DataAccessException
> descendants
> http://www.koders.com/xml/fidE70F96602F7F254B935C711E60F1D141C60CD1DB.aspx
> Implementation is very simple (just stub) at this moment. (todo)
> 4. jars can not be deployed into OSGI environment (jar's are not bundles)
> - added configuration of maven-bundle-plugin to pom.xml
>
> Preliminary version of these changes in attachment. I am not sure that all
> these changes are correct.
> Suppose that these changes are important.
>
> Best regards,
> Sergey
>
Lukas Eder
I have merged your suggestions for
- https://sourceforge.net/apps/trac/jooq/ticket/898 (unchecked exceptions)
- https://sourceforge.net/apps/trac/jooq/ticket/899 (OSGI bundling)
The changes are being uploaded to SVN and GitHub
And I'll be doing this one in the next days
- https://sourceforge.net/apps/trac/jooq/ticket/897 (Spring integration)
I have slightly modified your suggestions:
- The unchecked DataAccessException is now always formally declared in
method signatures, and documented in Javadocs. I think that's cleaner.
But I really do like the fact of it being unchecked.
- I moved the relevant Factory Javadoc up to FactoryOperations (not
sure about the name of that interface yet)
- Factory.fetch(ResultSet) is now also declared in FactoryOperations
(might've been forgotten)
- I removed the exception translator type for now, replacing it by a
static package-private method in JooqUtil, as this is incomplete. The
final API might differ, so I prefer not to change the API for now.
Maybe there is no need to expose that API.
- I changed all relevant methods in your Spring FactoryProxy (class
renamed) to being final. I tend to prefer that.
And I have some questions:
- How can I keep OSGI export/import instructions in synch with the
rest of the pom.xml's maven dependencies?
- What is the NativeJdbcExtractor useful for? Can you show me concrete examples?
Cheers
Lukas
2011/11/1 Lukas Eder <lukas...@gmail.com>:
Lukas Eder
> Regarding exception translator. It may be useful to set custom translator,
> additionally to dialect-specific default translator.
> For example, to handle exceptions from PL/SQL package (to handle
> application-specific SQLException.vendorCode values).
> It was assumed that DataAccessException will be abstract and exception
> translator will be necessary to instantiate proper successor.
I'll add the feature on the roadmap. For some release after 2.0, I'm
planning to add more runtime configuration for various reasons. This
will be treated with
https://sourceforge.net/apps/trac/jooq/ticket/910
> If exception translator will not be available through the API, vendorCode
> must be accessible from outside (through the nested SQLException, for
> example).
That'll do for now.
> It would be great to see JooQFactoryProxy as a temporary workaround.
> Is it possible to make Factory connectionless, i.e. replace getConnection()
> in Configuration/Factory with getDataSource()?
I generally don't want jOOQ to get involved with transaction handling
and container-specific stuff, that is why jOOQ operates on
connections, not data sources... jOOQ cannot decide for the user, when
to commit / rollback transactions or close connections that were
obtained from a data source. Maybe jOOQ-spring could, but I don't have
a good feeling for that yet.
> I mean getting connection at the beginning of try/catch block (places, where
> JooqUtil.translate is now called) and closing in finally block.
> Data source usually transaction-manager aware, so we will receive the same
> connection during transaction (transaction manager binds connection to
> thread).
> For simple cases single-connection datasource can be used (just wrapper over
> single connection). SingleConnectionDataSource is a good example.
You might want to have a look at the org.jooq.ConfigurationRegistry.
That is one way for you to inject connections to queries. This feature
is not thoroughly documented yet.
>> - What is the NativeJdbcExtractor useful for? Can you show me concrete
>> examples?
>
> If JDBC connection is received from data source (pooled), it almost always
> is a proxy. NativeJdbcExtractor tries to extract native jdbc connection from
> proxy.
> I guess that org.jooq.util.oracle.OracleUtils requres native oracle
> connection.
I'm sure that oracle JDBC driver classes can deal with a managed
(proxied) connection. At least, in a J2EE container, I made good
experience with this design. Is this really needed? When would you
want to closely couple your application logic to a JDBC
implementation?
> JdbcTemplate, for example, always tries to resolve native connection and
> execute query using this connection.
> There are several implementations of this interface (one per data source
> type):
> http://static.springsource.org/spring/docs/3.0.6.RELEASE/javadoc-api/org/springframework/jdbc/support/nativejdbc/NativeJdbcExtractor.html
Wow, that class features methods like
isNativeConnectionNecessaryForNativeCallableStatements()... :-)
Sergey, I feel that this is going in a wrong direction. jOOQ shouldn't
resolve issues on a service-layer level. Maybe I'm getting this wrong,
though?
Cheers
Lukas
Lukas Eder
Do you think the idea of a session would help you more? I imagine that
when the concept of a session is introduced in jOOQ, it'll be easier
to abstract transaction handling and dealing with data sources.
I could imagine that the Factory will continue to operate on
connections directly, whereas a Session could also provide connections
to executed queries using the existing Attachable API. If that is
done, then a Session could manage the connection it gets from a data
source. Read more in this thread:
https://groups.google.com/forum/#!topic/jooq-user/s6Bqgz_81Yw
Cheers
Lukas
2011/11/5 Sergey Epik <serge...@gmail.com>:
> Hello Lukas,
>
>
> On Fri, Nov 4, 2011 at 6:00 PM, Lukas Eder <lukas...@gmail.com> wrote:
>
>> - The unchecked DataAccessException is now always formally declared in
>> method signatures, and documented in Javadocs. I think that's cleaner.
>> But I really do like the fact of it being unchecked.
>> - I moved the relevant Factory Javadoc up to FactoryOperations (not
>> sure about the name of that interface yet)
>> - Factory.fetch(ResultSet) is now also declared in FactoryOperations
>> (might've been forgotten)
>> - I removed the exception translator type for now, replacing it by a
>> static package-private method in JooqUtil, as this is incomplete. The
>> final API might differ, so I prefer not to change the API for now.
>> Maybe there is no need to expose that API.
>> - I changed all relevant methods in your Spring FactoryProxy (class
>> renamed) to being final. I tend to prefer that.
>
> Thank you!
>
> Regarding exception translator. It may be useful to set custom translator,
> additionally to dialect-specific default translator.
> For example, to handle exceptions from PL/SQL package (to handle
> application-specific SQLException.vendorCode values).
> It was assumed that DataAccessException will be abstract and exception
> translator will be necessary to instantiate proper successor.
> Anyway this feature is optional.
> If exception translator will not be available through the API, vendorCode
> must be accessible from outside (through the nested SQLException, for
> example).
>
> It would be great to see JooQFactoryProxy as a temporary workaround.
> Is it possible to make Factory connectionless, i.e. replace getConnection()
> in Configuration/Factory with getDataSource()?
> I mean getting connection at the beginning of try/catch block (places, where
> JooqUtil.translate is now called) and closing in finally block.
> Data source usually transaction-manager aware, so we will receive the same
> connection during transaction (transaction manager binds connection to
> thread).
> For simple cases single-connection datasource can be used (just wrapper over
> single connection). SingleConnectionDataSource is a good example.
>
> Regarding FactoryOperations and DataAccessException, I am also not sure that
> these names are good.
>
>>
>> And I have some questions:
>>
>> - How can I keep OSGI export/import instructions in synch with the
>> rest of the pom.xml's maven dependencies?
>
> I suppose plugin cares about dependencies.
>
>>
>> - What is the NativeJdbcExtractor useful for? Can you show me concrete
>> examples?
>
> If JDBC connection is received from data source (pooled), it almost always
> is a proxy. NativeJdbcExtractor tries to extract native jdbc connection from
> proxy.
> I guess that org.jooq.util.oracle.OracleUtils requres native oracle
> connection.
> JdbcTemplate, for example, always tries to resolve native connection and
> execute query using this connection.
> There are several implementations of this interface (one per data source
> type):
> http://static.springsource.org/spring/docs/3.0.6.RELEASE/javadoc-api/org/springframework/jdbc/support/nativejdbc/NativeJdbcExtractor.html
>
> Best regards,
> Sergey
>
Lukas Eder
> In this case it can be used from different threads concurrently (be a
> singleton).
Let me ask you this question:
What is the intent of a factory being thread-safe, after all? Why
wouldn't you want to create new factories?
2011/11/7 Sergey Epik <serge...@gmail.com>:
> Hi Lukas,
>
> I've found that Oracle JDBC driver supports statement pooling. And mysql
> jdbc driver also supports.
> c3p0 data source ( http://sourceforge.net/projects/c3p0/) supports prepared
> statement pooling.
> So I am not sure that's a jook concern.
>
> I proposed to configure Factory with DataSource instance instead of
> Connection instance.
> Or, may be, better introduce separate class that implements
> FactoryOperations and is
> configured with DataSource, SQLDialect and SchemaMapping (without ant spring
> dependencies).
> In this case it can be used from different threads concurrently (be a
> singleton).
> It's not related directly to transaction management, because transaction
> manager is behind DataSource.
>
> --
> Best regards,
Lukas Eder
> a singleton-scope service (and inject factory as a dependency).
> Let's look to the similar solutions:
> - org.springframework.data.jdbc.query.QueryDslJdbcTemplate
> (http://static.springsource.org/spring-data/data-jdbc/docs/1.0.0.RC1/reference/html/#core.querydsl
> )
> - org.mybatis.spring.SqlSessionTemplate (from mybatis-spring integration)
> The purpose of these classes are very close to JooQFactoryProxy.
I see, thanks. This makes sense to me, now. I'll also check out the
QueryDSL / Spring integration, as I'm vaguely acquainted with
QueryDSL. Many use cases are very similar between jOOQ and QueryDSL.
In general, the jOOQ Factory doesn't need to be touched, but the
wrapper may provide different behaviour according to requirements or
suggestions by Spring.
Cheers
Lukas
PS: I'm always CC'ing this to the user group as well. I think these
are very important questions to be adressed and communicated to the
community.