Configure Result Persistence

You can set up social comments, bookmarks, and saved searches so that they persist to a database.

When using the Appkit Social module, the Appkit application has been designed to save comments, bookmarks, and saved searches against logged in users. By default, the application uses an in memory database, the contents of which is lost on application restart.

To persist comments, bookmarks, and searches, we can set this up to work with any relational JDBC-compliant database (such as Oracle or MySQL).

Requirements

  • Any relational JDBC-compliant database (such as Oracle or MySQL).

  • Network access from the Appkit application to the relational database.

This database doesn’t have to be empty because we prefix all of our tables with a twigkit identifier, but for simplicity we would prefer having our own schema since you probably won’t need to join these tables with other schemas.

Database specific setup instructions

Which database is used for storing the metadata collected using the Social module is controlled via POM dependencies. By default a lightweight, in-memory Derby database is used, as per the following pom.xml dependency:

For version 4.2 and later:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.provider.db.derby.memory</artifactId>
    <version>${project.parent.version}</version>
</dependency>
  • For version 4.1 and earlier:*

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.db.derby.memory</artifactId>
    <version>${project.parent.version}</version>
</dependency>

To use a disk-based version of the same (persisting social metadata between restarts) change the above to:

For version 4.2 and later:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.db.provider.derby</artifactId>
    <version>${project.parent.version}</version>
</dependency>

For version 4.1 and earlier:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.db.derby</artifactId>
    <version>${project.parent.version}</version>
</dependency>

You can specify the Derby system directory, which is the directory that will contain the social database, by adding the following flag to your Java options:

-Dderby.system.home=/my/derby/directory

If the system directory that you specify with derby.system.home doesn’t exist at startup, Derby creates the directory automatically.

You’ll need to configure a persistence.xml file with the appropriate connection strings etc. Click here for Derby-memory and Derby examples.

Other pre-configured versions are also available, including:

db.mysql
db.oracle
db.sqlserver

Click here for MySQL, Oracle, and SQL Server examples of persistence.xml files.

This file should be created at:
src/main/resources/META-INF/persistence.xml

ID generator mappings in the persistence.xml

Regardless of which persistence.xml you use, please ensure the following property is added to the persistence.xml file:

<property name="hibernate.id.new_generator_mappings" value="false" />

This will ensure both pre- and post-Hibernate 5 IDs continue to be generated using the same mapping.

Troubleshooting database configuration

Connection issues

Many databases require the application to manage the connections, refreshing them and maintaining enough open connections to service the load on the database. In some cases the default connection management provided is inadequate for production setups.

If you notice bad performance, connections going stale, and associated errors (usually intermittently) the default connection pooling is probably inadequate for your environment.

To remedy this situation you can use a third-party connection pooling technology. We recommend 'C3P0', which can be configured with the following simple steps:

  1. Add the dependency for Hibernate c3p0 to the pom.xml:

    <!-- Hibernate c3p0 connection pooling -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-c3p0</artifactId>
        <version>X.X.X.Final</version>
    </dependency>

    The version of Hibernate c3p0 you should use depends on the version of Appkit you are using. To begin with try version 4.1.7.Final (legacy) or 5.2.2.Final.

  2. Add the configuration for C3P0 to the persistence.xml:

    <!-- c3p0 connection pool settings -->
    <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />
    <property name="hibernate.c3p0.max_size" value="100" />
    <property name="hibernate.c3p0.min_size" value="0" />
    <property name="hibernate.c3p0.acquire_increment" value="1" />
    <property name="hibernate.c3p0.idle_test_period" value="300" />
    <property name="hibernate.c3p0.max_statements" value="0" />
    <property name="hibernate.c3p0.timeout" value="100" />

The settings above should be adequate for a standard Appkit application using the Social module, but they can be adjusted as desired.