AEM CQ with JPA (Hibernate)

Question

I'm working with Adobe Experience Manager (AEM) 6.4 with Service Pack 1, and the Forms Package. I have a lot of extended Properties/attributes, so I made a Database diagram. I don't want to save all the additional stuff in crx I want to save it in an Oracle database.

The Database diagram is complex, so I want to USE JPA (Hibernate), at minimum. If Spring would help to make it easier to use, than that would be fine for me.

I read a lot that the OSGI is working with blueprint instead of Spring, but you can combine it.

I'm really searching for an good example how to make things work with JPA and a Oracle db.

Adobe is no Help at all, they can't show an example how to work with AEM and JPA/Hibernate/Spring/blueprint.

Can anybody help me to make things working? with AEM and JPA?

I think what I need is:

1. persistence.xml (RESOURCE_LOCAL or JTA?)
2. Entity classes with Annotation @Entity and other JPA annotations
3. Service classes with Transaction control and maybe manager classes to submit to work with the entity classes and get result from queries, maybe I could put it all in the service classes
4. hibernate-osgi (dependency)
5. ojdbc7 for connection (dependency)
6. org.apache.aries.jpa.api (dependency)

But how do I have to make things happen? Nothing works. I even don't know if this is the right way.

should I use blueprint or Spring or both?

I found this Aries thing from apache. http://aries.apache.org And weard different samples that I don't really understand how they work. https://github.com/apache/aries-jpa/tree/master/examples

And some OSGI sample, they look very incomplete for my situation. https://enroute.osgi.org/tutorial/032-tutorial_microservice-jpa.html

So is there anybody with some experience with AEM and JPA?

Answer 1

let me describe how it is implemented on our project. We are using AEM 6.3 with SP2. We have next dependencies in the root pom.xml:

    <!-- JPA -->
    <dependency>
        <groupId>org.hibernate.javax.persistence</groupId>
        <artifactId>hibernate-jpa-2.1-api</artifactId>
        <version>1.0.0.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.1.10.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>5.1.10.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate.common</groupId>
        <artifactId>hibernate-commons-annotations</artifactId>
        <version>5.0.1.Final</version>
    </dependency>
    <dependency>
        <groupId>com.ibm.db2.jcc</groupId>
        <artifactId>db2jcc4</artifactId>
        <version>11.1</version>
        <scope>system</scope>
        <systemPath>${project.root.path}/lib/db2jcc4.jar</systemPath>
    </dependency>
    <dependency>
        <groupId>org.jboss.logging</groupId>
        <artifactId>jboss-logging</artifactId>
        <version>3.3.2.Final</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml</groupId>
        <artifactId>classmate</artifactId>
        <version>1.3.0</version>
    </dependency>
    <dependency>
        <groupId>antlr</groupId>
        <artifactId>antlr</artifactId>
        <version>2.7.7</version>
    </dependency>
    <!-- local development database -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.194</version>
    </dependency>
    <!-- /JPA-->

Then we have the bundle to provide JPA dependencies and the OSGI service that allows to get hibernate Session. The bundle pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>com.myproject</groupId>
    <artifactId>myproject-bundles</artifactId>
    <version>1.0-SNAPSHOT</version>
  </parent>
  <artifactId>com.myproject.db</artifactId>
  <packaging>bundle</packaging>
  <name>myproject - DB bundle</name>
  <description>OSGI bundle to work with a database</description>

  <properties>
    <bundle.export>
      com.myproject.db.*,
      javax.persistence,
      org.hibernate,
      org.hibernate.cfg,
      org.hibernate.proxy,
      org.hibernate.boot.registry,
      org.hibernate.annotations,
      org.hibernate.service,
      org.hibernate.criterion,
      org.hibernate.transform
    </bundle.export>
    <bundle.import>*;resolution:=optional</bundle.import>
    <!-- Import JDBC driver dynamically -->
    <bundle.dynamic.import>com.ibm.*,javassist.util.*</bundle.dynamic.import>
    <bundle.embed>
      hibernate-jpa-2.1-api,hibernate-core,hibernate-entitymanager,hibernate-commons-annotations,jboss-logging,antlr
    </bundle.embed>
    <project.root.path>${project.basedir}/../..</project.root.path>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.hibernate.javax.persistence</groupId>
      <artifactId>hibernate-jpa-2.1-api</artifactId>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>
    </dependency>
    <dependency>
      <groupId>org.hibernate.common</groupId>
      <artifactId>hibernate-commons-annotations</artifactId>
    </dependency>
    <dependency>
      <groupId>org.jboss.logging</groupId>
      <artifactId>jboss-logging</artifactId>
    </dependency>
    <dependency>
      <groupId>com.ibm.db2.jcc</groupId>
      <artifactId>db2jcc4</artifactId>
    </dependency>
    <dependency>
      <groupId>antlr</groupId>
      <artifactId>antlr</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
    </dependency>
      <dependency>
          <groupId>com.fasterxml.jackson.core</groupId>
          <artifactId>jackson-annotations</artifactId>
      </dependency>
      <dependency>
          <groupId>com.fasterxml.jackson.core</groupId>
          <artifactId>jackson-databind</artifactId>
      </dependency>
      <dependency>
          <groupId>com.myproject</groupId>
          <artifactId>com.myproject.common</artifactId>
          <version>1.0-SNAPSHOT</version>
          <scope>compile</scope>
      </dependency>
  </dependencies>
</project>

In the bundle we have next service:

package com.myproject.db;

import org.hibernate.Session;

public interface JPASessionFactory {
    Session openSession();

    void closeSession(Session session);
}

implementation:

package com.myproject.db.impl;

import com.myproject.db.JPASessionFactory;
import org.apache.felix.scr.annotations.*;
import org.apache.sling.commons.osgi.PropertiesUtil;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;

import javax.sql.DataSource;
import java.util.Map;

@Service
@Component(metatype = true, policy = ConfigurationPolicy.REQUIRE)
@Properties({
        @Property(label = "Hibernate SQL dialect", name = Environment.DIALECT),
        @Property(label = "Show SQL", name = Environment.SHOW_SQL, boolValue = false),
        @Property(label = "Bulk ID Strategy", name = Environment.HQL_BULK_ID_STRATEGY)
})
public class JPASessionFactoryImpl implements JPASessionFactory {

    @Reference(target = "(datasource.name=myproject)")
    private DataSource dataSource;

    private SessionFactory sessionFactory;

    @Activate
    protected void activate(Map<String, Object> properties) {
        StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
                .configure()
                .applySetting(Environment.DIALECT, PropertiesUtil.toString(properties.get(Environment.DIALECT), ""))
                .applySetting(Environment.SHOW_SQL, PropertiesUtil.toBoolean(properties.get(Environment.SHOW_SQL), false))
                .applySetting(Environment.DATASOURCE, dataSource);

        String bulkIdStrategy = PropertiesUtil.toString(properties.get(Environment.HQL_BULK_ID_STRATEGY), "");
        if (!bulkIdStrategy.isEmpty()) {
            builder.applySetting(Environment.HQL_BULK_ID_STRATEGY, bulkIdStrategy);
        }
        sessionFactory = new Configuration().buildSessionFactory(builder.build());
    }

    @Deactivate
    protected void deactivate() {
        if (sessionFactory != null) {
            sessionFactory.close();
        }
    }

    @Override
    public Session openSession() {
        return sessionFactory.openSession();
    }

    @Override
    public void closeSession(Session session) {
        if (session != null && session.isOpen()) {
            session.close();
        }
    }
}

osgi config for the service:

<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:sling="http://sling.apache.org/jcr/sling/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0"
          jcr:primaryType="sling:OsgiConfig"
          hibernate.dialect="org.hibernate.dialect.H2Dialect"
          hibernate.show_sql="{Boolean}true"/>

Configuration for DataSourceFactory apps/myproject-forms/configuration/config.local/org.apache.sling.datasource.DataSourceFactory-localh2.xml:

<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:sling="http://sling.apache.org/jcr/sling/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0"
          jcr:primaryType="sling:OsgiConfig"
          datasource.name="myproject"
          driverClassName="org.h2.Driver"
          url="jdbc:h2:./myprojectlocal;AUTO_SERVER=TRUE"
          username="sa"
          password=""
          testOnBorrow="{Boolean}true"
          testOnReturn="{Boolean}true"
          testWhileIdle="{Boolean}true"
          validationQuery="SELECT 1"/>

Also we have the Hibernate config file hibernate.cfg.xml in "resources" folder of the bundle

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hbm2ddl.auto">update</property>
    <property name="current_session_context_class">thread</property>
    <mapping class="com.myproject.db.Entity1"/>
    <mapping class="com.myproject.db.Entity2"/>
  </session-factory>
</hibernate-configuration>

Answer 2

I've found the solution and made a post here on the site.

https://forums.adobe.com/message/10640295#10640295

I feel like I'm the First Guy on earth which combined AEM with JPA/Hibernate. Now I could check if it's a benefit to work with spring, to operate with the Transactions.

And those two who voted the question down, I'm sure you are from adobe --> thanks, now I feel more expert than you on your own cms, because before my post there was no solution for this problem.

Update: Maven Dependencies. If you have problems with the dependencies you have to Embed them and add the property "Embed-Transitive to true"

 <plugin>
            <groupId>org.apache.felix</groupId>
            <artifactId>maven-bundle-plugin</artifactId>
            <extensions>true</extensions>
            <inherited>true</inherited>
            <configuration>
                <instructions>
                    <Embed-Dependency>
                        *;scope=compile|runtime
                    </Embed-Dependency>
                    <Embed-Transitive>true</Embed-Transitive>
                    <Export-Package>
                        <!--TODO export packages -->

                    </Export-Package>
                    <Private-Package>todo</Private-Package>
                    <Import-Package>
                        <!-- Attention this is really sensitive!!. -->
                        *;resolution:="optional"
                    </Import-Package>
                    <Bundle-Activator>path.to.Activator</Bundle-Activator>
                </instructions>
            </configuration>
        </plugin>