SQL Datasource and Spring transaction manager

[mpretorian]

Hi,

I'd like to know whether it is possible to configure an SQL Datasource to perform its fetch operation using the same database connection/transaction as a Service and a Dao that are configured using Spring transaction manager.
Below is a pseudo code that describes what I tried but it didn't work.
The Dao has to insert some rows into a temporary table (without committing them) and those rows should be visible by the SQL Datasource fetch query.

<context:annotation-config />
	<tx:annotation-driven transaction-manager="transactionManager" />
<context:component-scan base-package="xxx" />
	
package xxx;	
public class MyDatasourceDMI { 
	private MyDao dao;	
    @Transactional(propagation = Propagation.REQUIRED)    
	public DSResponse fetch(DSRequest dsRequest) throws Exception {
		dao.insertDataInTempTable(params);
		return dsRequest.execute();
	}
}	
		
<DataSource ID="myDatasource" serverType="sql" dbName="db" tableName="db-table">
	<fields>field1...	</fields>
  <operationBindings>
    <operationBinding operationType="fetch" serverMethod="fetch" autoJoinTransactions="true">
      <serverObject lookupStyle="spring" bean="MyDatasourceDMI"/>
      <tableClause>..</tableClause>
      <selectClause>...</selectClause>
      <whereClause>...and exists (select 1 from temp_table where field1=col1)</whereClause>	  
    </operationBinding>
  </operationBindings>
</DataSource>

I'm using Spring 3 and SmartGWT 2.5.2011-05-14-EVAL.

[Isomorphic]

At the moment there doesn't appear to be a way to do this unless you did something exotic like setup a JNDI connection on the fly. However, in the latest SDK, you'll see a sample of doing the opposite: getting access to the SQLConnection to add your own operations to the transaction (under Server Examples -> Transactions -> Transactional User Operations). Would this solve your problem as well?

[mpretorian]

Do you think that would be possible in the future to configure an SQL Datasource with an external transaction manager? Is it already planned such a feature?

[Isomorphic]

It could be implemented, it's not currently planned, but it would be a valid Feature Sponsorship.

[mpretorian]

There is a class in spring library called DataSourceUtils that makes all the work.
Couldn’t be used that class to obtain a connection from a datasource taken from JNDI in SmartWGT server framework?
Have a look to the implementation of this method: org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSource).
It returns a managed connection if exists or falls back to the connection from the jndi datasource.
So it doesn't break the existing functionality but adds a lot of possibilities to how a datasource can be implemented as a combination between SQLDatasource and custom access to database.

[mpretorian]

Is this feature in the meantime planned to be implemented or already implemented?

[Isomorphic]

As stated above - it could be implemented, it's not currently planned, but it would be a valid Feature Sponsorship.

[pwcolinx]

SQL Datasource and Spring transaction manager

Was this feature ever implemented?

[Isomorphic]

As it happens, this was sponsored and checkins for the feature have just started. Within a few days you'll see new docs for it in 5.0d builds.

[ssequeira]

I am using smartgwt power 5.0 18.01.2017
I read the http://www.smartclient.com/smartgwte...tegration.html because I would like smartGWT to participate in a Spring managed transaction.
I cannot make it to work.
This is what I have so far:

@Component
public class TransactionSupportBeanImpl extends JdbcDaoSupport implements TransactionSupportBean {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
    public void startTx() {
        jdbcTemplate.queryForInt("select 1 from dual");
    }

}

@Component
public class IDACall<RT> extends com.isomorphic.servlet.IDACall {

@Autowired
    private TransactionSupportBean transactionSupportBean;

    @Override
    public void processRPCTransaction(RPCManager arg0, RequestContext arg1) throws Exception {
        transactionSupportBean.startTx();
        super.processRPCTransaction(arg0, arg1);
    }

}

This does not work.
I tried to follow the documentation, but it isn't clear what to do.
The code which is there is also wrong. You have a method declaration inside a method declaration...

 @Transactional(isolation=Isolation.READ_COMMITTED, propagation=Propagation.REQUIRED)
     public class MyServiceBean {
  
         public void processQueue(HttpServletRequest req, HttpServletResponse resp) 
         throws Exception 
         {
             // invoke Smart GWT server standard request processing
             public void processQueue(RPCManager rpc) throws Exception {
                 rpc.processRPCTransaction();
             }
         }
     }

[Isomorphic]

We will correct that obvious error in the documentation, and also review it to see how we can make things clearer, but to solve your immediate problem, here is how we set things up for the internal automated tests of this feature:

IDACall replacement

public class SpringTransactionIDACall extends IDACall {

    @Override
    public void processRPCTransaction(RPCManager rpc, RequestContext context) throws Exception {
        ApplicationContext ac = WebApplicationContextUtils.getWebApplicationContext(getServletContext());
        QueueServiceBean queueBean = (QueueServiceBean)ac.getBean("queueServiceBean");
        queueBean.processQueue(rpc);
   }
}

Transactional Spring bean

@Transactional(isolation=Isolation.READ_COMMITTED, propagation=Propagation.REQUIRED)
public class QueueServiceBean {
 
    public void processQueue(RPCManager rpc) throws Exception {
        rpc.processRPCTransaction();
    }
}

So, the SpringTransactionIDACall.processRPCTransaction() override will be invoked by the normal SmartGWT client-server flow. It creates the Spring bean and calls its processQueue() method, which causes Spring to start a transaction, and the processQueue() method then calls back into normal SmartGWT processing; now, the SmartGWT queue processing is wrapped inside that transactional method. As long as the DSRequests are for DataSources or OperationBindings that have been marked useSpringTransaction="true", and SmartGWT's normal transaction processing is in force (see autoJoinTransactions), all DSRequests in the queue will use the Spring-managed transaction, and Spring will deal with committing or rolling back the transaction when the queue is complete, and thus the QueueServiceBean.processQueue() method ends.

Note, the client-side automated tests in question call RPCManager.setActionURL() to redirect DSRequest calls to the URL where this special SpringTransactionIDACall is listening, but if you want this approach globally, just map the normal IDACall address to that servlet in your web.xml file.

Regards,
Isomorphic Software Support

[ssequeira]

Hello.
A couple of questions about this: does it work only for RPCQueue? Does it also work for single DSRequests?
Related to the code you posted, I have tried this as well, but there is something else which seems to be missing.
I was first getting an error related to the framework not being able to get the Spring Application Context.
After researching a bit I found that I potentially need to define a bean

     <bean id="springApplicationContextProvider"
 class="com.isomorphic.spring.SpringApplicationContextProvider" />

After I have done it, that error is gone, but now I am getting
"java.lang.Exception: No value for property sql.spring.jdbcDataSourceBean found in server.properties"

I could not find anywhere on the spring integration guide (nor by following the links on the spring integration guide page) which could help me figure out the full configuration needed.
Can you please point me in the right direction?

[ssequeira]

Ping

[Isomorphic]

On your first question: requests that come from a client are always queues, even if they only contain one DSRequest (a queue with a single request in it is a very common case).

You should be able to use Spring's getWebApplicationContext() method, like the example code does, but you can call the SmartClient utility SpringApplicationContextProvider.getApplicationContext() if you prefer. That does require you to register the bean, as you say.

The error you now have is happening because SmartClient needs a way to lookup the JNDI connection being used by Spring, and this needs to be configured. This does not appear to be documented, so apologies for that, we will correct it. You need to register a bean like this in your applicationContext.xml files:

  <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <!-- Set this to the JNDI name Spring is using -->
    <property name="jndiName" value="isomorphic/jdbc/defaultDatabase"/>
  </bean>

and then add a line like this to your server.properties:

# Set this property to match the "id" of the JndiObjectFactoryBean registered in Spring
sql.spring.jdbcDataSourceBean: dataSource

[ksenia_korenkova]

Hi Isomorphic,
We are now almost done with this migration from using Isomorhic transaction to use spring transaction.
But currently we faced with the issue that we can't use one Spring Transaction when the queue request was send through the REST.
So we have our own overridden RestHandler which extends com.isomorphic.servlet.RESTHandler and there is only method which we could use to override and start spring transaction and decide if we want to commit or rollback:
public DSResponse handleDSRequest(DSRequest dsRequest, RPCManager rpcManager, RequestContext context) throws Exception{
TransactionStatus status = txManager.getTransaction(def);
try {
DSResponse response = getResponse(dsRequest, rpcManager, context);
if (response.getStatus() >= 0) {
txManager.commit(status);
} else {
txManager.rollback(status);
}
return response;
}
But this will work for every request in the queue, so it means every time the transaction will be started for every request from the queue.
But this is not what we want because it contradict to the original behavior. (1 queue - 1 transaction)
There is one more method:
public void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
But this one doesn't contain any useful information to decide if we need to commit or not (at least rpcManager parameter will be useful).
Currently we use SmartClient Version: v10.0p_2018-06-25/PowerEdition Deployment (built 2018-06-25)
We found out that in java doc for 12 version you introduce this new method:
public void processRestTransaction(RPCManager rpcManager, RequestContext context) throws java.lang.Exception https://www.smartclient.com/smartgwt...STHandler.html

It is not possible for us now to update from 10 to 12 version but we need to use this feature with using Spring Transaction.
Could you provide us the way how we can do it in Rest Handler for 10 version?

Best Regards, Ksenia