how to configure ibatis to use an existing datasource?

[maxvar]

Currently I use camel-sql component in a camel route, but want to move to camel-ibatis for ease of mapping and onConsume-processing.

My datasource for camel-sql is defined as a bean with url/user/password properties got via osgi cm-properties. Then the bean is referred by dataSourceRef option in the sql component URL.

The question is how to reuse this existing datasource or use cm-properties in ibatis SqlMapConfig.xml transactionManager/dataSource.

Googled for an answer, but found nothing.

[maxvar]

I'm further trying to use jndi lookup in SqlMapConfig to access a DataSource published as a service in OSGI. Is there an example for this?

[ffang]

Hi,

I believe this discussion thread[1] would help.

http://servicemix.396122.n5.nabble.com/JNDI-JDBC-DataSource-for-ServiceMix-4-td3371885.html

Freeman

[maxvar]

Gone through this yesterday:

• installed oracle jdbc driver bundle (wrapped jar) into smx

• built and installed into smx a bundle with beans.xml:

• built and installed bundle with a route using ibatis with SqlMapConfig.xml:

• the route:

from("timer://foo?period=60000").to("ibatis:GetUnprocessesUpdates?statementType=QueryForList")

Now when I start the bundle with the route I get the error:

19:30:57,477 | ERROR | foo              | DefaultErrorHandler              | 68 - org.apache.camel.camel-core - 2.6.0.fuse-01-09 | Failed delivery for exchangeId: ID-1-2272-60983-1304601704780-9-2. Exhausted after delivery attempt: 1 caught: java.lang.RuntimeException: Error occurred.  Cause: com.ibatis.common.xml.NodeletException: Error parsing XML.  Cause: java.lang.RuntimeException: Error parsing XPath '/sqlMapConfig/transactionManager/dataSource/end()'.  Cause: com.ibatis.sqlmap.client.SqlMapException: There was an error configuring JndiDataSourceTransactionPool. Cause: javax.naming.NameNotFoundException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS)) Root exception is javax.naming.ServiceUnavailableException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS))

java.lang.RuntimeException: Error occurred.  Cause: com.ibatis.common.xml.NodeletException: Error parsing XML.  Cause: java.lang.RuntimeException: Error parsing XPath '/sqlMapConfig/transactionManager/dataSource/end()'.  Cause: com.ibatis.sqlmap.client.SqlMapException: There was an error configuring JndiDataSourceTransactionPool. Cause: javax.naming.NameNotFoundException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS)) Root exception is javax.naming.ServiceUnavailableException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS))

     at com.ibatis.sqlmap.engine.builder.xml.SqlMapConfigParser.parse(SqlMapConfigParser.java:49)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

     at com.ibatis.sqlmap.client.SqlMapClientBuilder.buildSqlMapClient(SqlMapClientBuilder.java:63)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

...

Caused by: com.ibatis.common.xml.NodeletException: Error parsing XML.  Cause: java.lang.RuntimeException: Error parsing XPath '/sqlMapConfig/transactionManager/dataSource/end()'.  Cause: com.ibatis.sqlmap.client.SqlMapException: There was an error configuring JndiDataSourceTransactionPool. Cause: javax.naming.NameNotFoundException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS)) Root exception is javax.naming.ServiceUnavailableException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS))

     at com.ibatis.common.xml.NodeletParser.parse(NodeletParser.java:53)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

     at com.ibatis.sqlmap.engine.builder.xml.SqlMapConfigParser.parse(SqlMapConfigParser.java:46)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

...

Caused by: java.lang.RuntimeException: Error parsing XPath '/sqlMapConfig/transactionManager/dataSource/end()'.  Cause: com.ibatis.sqlmap.client.SqlMapException: There was an error configuring JndiDataSourceTransactionPool. Cause: javax.naming.NameNotFoundException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS)) Root exception is javax.naming.ServiceUnavailableException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS))

     at com.ibatis.common.xml.NodeletParser.processNodelet(NodeletParser.java:123)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

     at com.ibatis.common.xml.NodeletParser.process(NodeletParser.java:105)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

...

Caused by: com.ibatis.sqlmap.client.SqlMapException: There was an error configuring JndiDataSourceTransactionPool. Cause: javax.naming.NameNotFoundException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS)) Root exception is javax.naming.ServiceUnavailableException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS))

     at com.ibatis.sqlmap.engine.datasource.JndiDataSourceFactory.initialize(JndiDataSourceFactory.java:60)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

     at com.ibatis.sqlmap.engine.builder.xml.SqlMapConfigParser$9.process(SqlMapConfigParser.java:220)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

     at com.ibatis.common.xml.NodeletParser.processNodelet(NodeletParser.java:121)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

     ... 43 more

Caused by: javax.naming.NameNotFoundException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS)) Root exception is javax.naming.ServiceUnavailableException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS))

     at org.apache.servicemix.naming.OSGiServicesContext.lookup(OSGiServicesContext.java:67)[77:org.apache.servicemix.naming:1.4.0.fuse-01-09]

     at org.apache.servicemix.naming.OSGiContext.lookup(OSGiContext.java:61)[77:org.apache.servicemix.naming:1.4.0.fuse-01-09]

     at org.apache.servicemix.naming.InitialContextWrapper.lookup(InitialContextWrapper.java:173)[77:org.apache.servicemix.naming:1.4.0.fuse-01-09]

     at javax.naming.InitialContext.lookup(Unknown Source)[:1.6.0_24]

     at com.ibatis.sqlmap.engine.datasource.JndiDataSourceFactory.initialize(JndiDataSourceFactory.java:48)[222:org.apache.servicemix.bundles.ibatis-sqlmap:2.3.4.726_3]

     ... 45 more

Caused by: javax.naming.ServiceUnavailableException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS))

     at org.apache.servicemix.naming.ProxyInvocationHandler.getTarget(ProxyInvocationHandler.java:59)[77:org.apache.servicemix.naming:1.4.0.fuse-01-09]

     at org.apache.servicemix.naming.OSGiServicesContext.lookup(OSGiServicesContext.java:63)[77:org.apache.servicemix.naming:1.4.0.fuse-01-09]

     ... 49 more

In karaf console i see that the pool bundle is started and (ls) provides:

org.springframework.osgi.bean.name = dsPARUS

Bundle-SymbolicName = db-parus

Bundle-Version = 1.0.0.SNAPSHOT

osgi.jndi.service.name = jndiPARUS

objectClass = javax.sql.DataSource, oracle.jdbc.pool.OracleDataSource

service.id = 255

Where should i look next?

[ffang]

Hi,

This is interesting

Caused by: javax.naming.ServiceUnavailableException: (&(objectClass=javax.sql.DataSource)(osgi.jndi.service.name=jndiPARUS))

but from osgi:ls

osgi.jndi.service.name = jndiPARUS

objectClass = javax.sql.DataSource, oracle.jdbc.pool.OracleDataSource

Further investigation found that oracle.jdbc.pool.OracleDataSource isn't a javax.sql.DataSource, so you expose the OSGi service isn't correct, it should be

Hope this helps

Freeman

[maxvar]

I actually lacked the import of javax.naming and javax.sql in the datasource bundle. So the error handling and diagnosis is a bit shorthanded here =)

What finally worked was a combo of

(1) db-parus bundle with

-

---

-

---

in beans.xml

(2) import of javax.naming, javax.sql, oracle.jdbc.pool in db-parus bundle

(3) sap-psu bundle with SqlMapConfig with

-

---

-

---

Now it works sharing the DB pool with another JBI BC+SE combo bundle.

[chandraprabha]

=

Edited by: chandraprabha on Sep 1, 2011 6:07 PM