Connection pooling?

kleinmat · ‎2014-08-28

Hi there,
I have created a simple, synchronous Web Service which reads records out of a database, processes them and returns some values.
For the data fetching part, I used a tOracleInput component.
But I have the feeling that every request leads to the web service opening a new connection to the DB, fetching rows, and closing the DB connection. Is that correct?
If so: how can I avoid such overhead and use a connection pool (which automatically restores a DB connection if it has been lost)?
Thanks
Matt

Anonymous · ‎2014-08-28

Hi Matt,
In your tOracleInput Component tab, take a look at the bottom box that says 'Data Source'. There you can check 'Specify data source alias' and name your data source. For instance, I call my sandbox pool 'mysql-ds'. As the note says, this will only apply in Karaf.
Before you deploy the service to Karaf you'll need to deploy a connection pool. To do this, create a Springframework configuration file that defines the data source. You will have to make sure any required JDBC drivers or other dependencies are installed as well.
Just drop the Spring file into the deploy/ directory. Then you will have connection pooling. You just have to make sure the value you use in osgi:service-properties matches with the one you used in your component data source alias.
Here is a sample file:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:osgi="http://www.springframework.org/schema/osgi"
  xmlns:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   http://www.springframework.org/schema/osgi http://www.springframework.org/schema/osgi/spring-osgi.xsd
   http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
  
	<!-- The Connection Pool -->
	<bean id="mysql-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://localhost/demo"/>
		<property name="username" value="talend"/>
		<property name="password" value="talend"/>
		<property name="initialSize" value="4"/>
		<property name="maxActive"  value="30"/>
		<property name="maxIdle" value="10"/>
		<property name="maxWait" value="3000"/>
		<property name="validationQuery" value="SELECT 1"/>
	</bean>
	<!-- Publish the pool as an OSGi and JNDI Service -->
	<osgi:service interface="javax.sql.DataSource" ref="mysql-ds">
		<osgi:service-properties>
			<entry key="osgi.jndi.service.name" value="mysql-ds"/>
		</osgi:service-properties>
	</osgi:service>
</beans>

If you want to see the datasource in JMX (like you can with Tomcat), then just add this bean with appropriate reference/naming adjustments:

	<!-- Export key DataSource Pool properties into JMX for Management and Monitoring -->
	<bean id="exporter" class="org.springframework.jmx.export.MBeanExporter" lazy-init="false">
		<property name="beans">
			<map>
				<entry key="org.talend.DataSource:name=MySQL" value-ref="mysql-ds"/>
			</map>
		</property>
		<property name="assembler">
			<bean class="org.springframework.jmx.export.assembler.MethodNameBasedMBeanInfoAssembler">
				<property name="managedMethods">
					<value>
						isClosed,
						getDefaultAutoCommit,
						setDefaultAutoCommit,
						getDefaultTransactionIsolation,
						setDefaultTransactionIsolation,
						getDriverClassName,
						getInitialSize,
						getMaxActive,
						setMaxActive,
						getMaxIdle,
						setMaxIdle,
						getMaxOpenPreparedStatements,
						setMaxOpenPreparedStatements,
						getMaxWait,
						setMaxWait,
						getMinEvictableIdleTimeMillis,
						setMinEvictableIdleTimeMillis,
						getMinIdle,
						getNumActive,
						getNumIdle,
						getNumTestsPerEvictionRun,
						setNumTestsPerEvictionRun,
						isPoolPreparedStatements,
						setPoolPreparedStatements,
						getRemoveAbandoned,
						setRemoveAbandoned,
						getRemoveAbandonedTimeout,
						setRemoveAbandonedTimeout,
						getTestOnBorrow,
						setTestOnBorrow,
						getTestOnReturn,
						setTestOnReturn,
						getTestWhileIdle,
						setTestWhileIdle,
						getTimeBetweenEvictionRunsMillis,
						setTimeBetweenEvictionRunsMillis,
						getUrl,
						getValidationQuery,
						setValidationQuery,
						getValidationQueryTimeout,
						setValidationQueryTimeout
					</value>
				</property>
			</bean>
		</property>
	</bean>

HTH,
Ben

kleinmat · ‎2014-08-28

Wow, that is really complicated. Is this actually the only way? If so: would you mind passing this on to your product management to make connection pooling available "at the click of a button"?

Anonymous · ‎2017-03-17

I am following your steps. I got the following error in the Studio. Do you know what is missing?
Configuration problem: Unable to locate Spring NamespaceHandler for XML schema namespace []
Offending resource: class path resource
org.springframework.beans.factory.parsing.BeanDefinitionParsingException: Configuration problem: Unable to locate Spring NamespaceHandler for XML schema namespace []
Offending resource: class path resource
at org.springframework.beans.factory.parsing.FailFastProblemReporter.error(FailFastProblemReporter.java:68)
at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:
at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:80)
at org.springframework.beans.factory.xml.BeanDefinitionParserDelegate.error(BeanDefinitionParserDelegate.java:315)
at org.springframework.beans.factory.xml.BeanDefinitionParserDelegate.parseCustomElement(BeanDefinitionParserDelegate.java:1429)
at org.springframework.beans.factory.xml.BeanDefinitionParserDelegate.parseCustomElement(BeanDefinitionParserDelegate.java:1422)
at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.parseBeanDefinitions(DefaultBeanDefinitionDocumentReader.java:187)
at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.doRegisterBeanDefinitions(DefaultBeanDefinitionDocumentReader.java:147)
at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.registerBeanDefinitions(DefaultBeanDefinitionDocumentReader.java:101)
at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.registerBeanDefinitions(XmlBeanDefinitionReader.java:495)
at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.doLoadBeanDefinitions(XmlBeanDefinitionReader.java:391)
at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:335)
at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:303)
at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:174)
at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:209)
at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:180)
at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:243)
at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:127)
at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:93)
at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:130)
at org.springframework.context.support.AbstractApplicationContext.obtainFreshBeanFactory(AbstractApplicationContext.java:539)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:451)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
at demo_project.demodatasource_0_1.DemoDatasource$2.createContext(DemoDatasource.java:186)
at org.apache.camel.main.Main.getCamelContextMap(Main.java:148)
at org.apache.camel.main.MainSupport.postProcessContext(MainSupport.java:386)
at org.apache.camel.main.Main.doStart(Main.java:124)
at org.apache.camel.support.ServiceSupport.start(ServiceSupport.java:61)
at org.apache.camel.main.MainSupport.run(MainSupport.java:124)
at demo_project.demodatasource_0_1.DemoDatasource.run(DemoDatasource.java:252)
at demo_project.demodatasource_0_1.DemoDatasource.runJobInTOS(DemoDatasource.java:314)
at demo_project.demodatasource_0_1.DemoDatasource.main(DemoDatasource.java:284)
Job DemoDatasource ended at 14:08 17/03/2017.
Thanks,
email3888

Anonymous · ‎2017-03-17

Look into documentation step-by-step example: https://help.talend.com/search/all?query=Configuring+datasource+in+Talend+Runtime&content-lang=en

Anonymous · ‎2017-03-18

Thanks archenroot! I am able to create the datasource. Now, I have issue to use camel-sql in TOS 6.21
After I updated the Spring file with the datasource info, I have the following error. Do you have experience with camel-sql
Configuration problem: Unable to locate Spring NamespaceHandler for XML schema namespace []
Thanks,
email3888

Other

v6.x