====== Connection Pooling with JPA and Apache Karaf ======
This has been tested with Apache Karaf 3.0.3 and by installing the following features:

  feature:install scr jpa/2.0.0 openjpa/2.2.2 transaction jndi jdbc

===== JDBC Driver =====
PostgreSQL is used in this example. The driver can be retrieved as a bundle from the Apache Servicemix project. Look in the [[http://search.maven.org | Maven Repository]] for ''org.apache.servicemix.bundles.postgresql''.

Just drop the bundle into the ''deploy'' folder and set the bundle start level to 25.

<note>I think the low bundle level is necessary because it the driver need to be started before the Aries JPA stuff so that it can pick up any Drivers. But I might be wrong with this one.</note>

===== DataSource =====
The ''javax.sql.DataSource'' instances will be created via Blueprint. Create a ''OSGI-INF/blueprint/blueprint.xml'' file in your project which looks like this:

<sxh xml>
<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0" xmlns:jpa="http://aries.apache.org/xmlns/jpa/v1.0.0"
	xmlns:tx="http://aries.apache.org/xmlns/transactions/v1.0.0" 
	xmlns:cm="http://aries.apache.org/blueprint/xmlns/blueprint-cm/v1.1.0">

    <reference id="transactionManager" interface="javax.transaction.TransactionManager" />
    
    <!-- Configuration Admin suppport -->
    <cm:property-placeholder persistent-id="sdm.persistence.datasource" update-strategy="reload" />

    <bean class="org.postgresql.ds.PGSimpleDataSource" id="sdsDatasource">
        <property name="serverName" value="${host}" />
        <property name="databaseName" value="${database}" />
        <property name="user" value="${user}" />
        <property name="password" value="${password}" />
    </bean>

    <bean class="org.postgresql.xa.PGXADataSource" id="xaDatasource">
        <property name="serverName" value="${host}" />
        <property name="databaseName" value="${database}" />
        <property name="user" value="${user}" />
        <property name="password" value="${password}" />
    </bean>
    
    <bean class="org.apache.commons.dbcp2.managed.BasicManagedDataSource" id="xaSdsDatasource" destroy-method="close">
        <property name="xaDataSourceInstance" ref="xaDatasource" />
        <property name="transactionManager" ref="transactionManager" />
        <property name="maxTotal" value="25" />
        <property name="initialSize" value="3" />
    </bean>
    
    <service id="sdsDS" interface="javax.sql.DataSource" ref="sdsDatasource">
        <service-properties>
             <entry key="osgi.jndi.service.name" value="jdbc/sds" />
        </service-properties>
    </service>

    <service id="xaSdsDS" interface="javax.sql.DataSource" ref="xaSdsDatasource">
        <service-properties>
             <entry key="osgi.jndi.service.name" value="jdbc/xasds" />
        </service-properties>
    </service>
	
</blueprint>
</sxh>

If you have any questions about this file, look at the OSGi Blueprint specs.

<note tip>''BasicManagedDataSource'' is packaged in the ''commons-dbcp2'' project (which has a dependency to ''commons-pool2''). Both projects are already packaged as bundles and can be fetched from the maven repository.</note>

The placeholders are filled by the ConfigAdmin service. It uses the configuration file //sdm.persistence.datasource// as stated in the attribute ''persistent-id'' which resides in the ''etc'' folder of Karaf. It may look something like this:

<code>
service.pid=sdm.persistence.datasource
host=server
database=my_db
user=sgbs
password=sgbs
</code>

The DataSource instances can be checked via

  service:list DataSource

===== JPA =====
Now the JPA needs to use our DataSource instances for getting a connection. This can be done in the ''persistence.xml'' file.

It may look something like this:

<sxh xml>
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
	xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

	<persistence-unit name="sdm" transaction-type="JTA">
		<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>

		<jta-data-source>osgi:service/jdbc/xasds</jta-data-source>
		<non-jta-data-source>osgi:service/jdbc/sds</non-jta-data-source>

		<class>my.jpa.EntityClass</class>

		<properties>
			<property name="openjpa.Log" value="File=/tmp/org.apache.openjpa.log, DefaultLevel=TRACE" />
			<property name="openjpa.RuntimeUnenhancedClasses" value="supported" />
		</properties>

	</persistence-unit>

</persistence>
</sxh>


<note important>Transaction testing still needs to be done!</note>


===== Other Solutions =====

==== Pax JDBC ====
The project [[https://ops4j1.jira.com/wiki/display/PAXJDBC | Pax JDBC]] offers a nice solution to for creating DataSource objects via ConfigAdmin and also supports connection pooling.