By default, eXo Platform uses HSQL and you do not need any configuration to make it work. Follow this chapter only when you need to use another RDBMS.
Assuredly you can use MySQL, PostgreSQL, MSSQL, Oracle, Sybase, DB2. Optimistically any SQL database that provides a JDBC compliant driver can be used.
In this chapter:
See also

4.1. Creating databases

eXo Platform uses two datasources:
  • One for Identity Management (IDM) service.
  • And the other for Java Content Repository (JCR) service.
  1. Create 2 databases - one for IDM, and one for JCR - or you can use the same database for both. Leave the databases empty, the tables will be created automatically in Platform first startup.
    • If you do not need to support specific characters, it is recommended to use the character set latin1 and the collation latin1_general_cs (as eXo JCR is case sensitive). For example, in MySQL: CREATE DATABASE plf_jcr DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_general_cs;.
    • If you need to support specific characters, it is recommended to use the character set utf8 and the collation utf8_bin (as eXo JCR is case sensitive). For example, in MySQL: CREATE DATABASE plf_jcr DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
  2. Grant a user the right to access the databases. The user should be allowed to access remotely from where eXo Platform is hosted.
    For example, in MySQL: 
    grant all on ${dbname}.* to '$username'@'$IP' identified by '$password';
    • $IP is your app server host name that accepts wildcard (for example, 192.168.1.% = all IPs on 192.168.1.x network).
    • $username and $password are credentials that eXo Platform will use to connect to the databases.     

4.2. Configuring eXo Platform

eXo Platform relies on the application server (Tomcat/JBoss) to access databases. It uses two JNDI datasources:
  • IDM uses exo-idm_portal.
  • JCR uses exo-jcr_portal.
eXo provides the ready-made and fine-tuned configuration so typically you just need to choose a sample and modify the connection url and the credentials.

4.2.1 For Tomcat
  1. Configure the datasources.
    • i. Edit conf/server.xml to remove the default HSQL configuration: 
      <!-- eXo IDM Datasource for portal -->

<Resource name="exo-idm_portal" ...

username="sa" password="" driverClassName="org.hsqldb.jdbcDriver" .../>

<!-- eXo JCR Datasource for portal -->

<Resource name="exo-jcr_portal" ...

username="sa" password="" driverClassName="org.hsqldb.jdbcDriver" .../>
    • ii. Add a new one. For MySQL as an example, you will just need to copy the sample in conf/server-mysql.xml: 
      <!-- eXo IDM Datasource for portal -->

<Resource name="exo-idm_portal" auth="Container" type="javax.sql.DataSource"

...

username="plf" password="plf" driverClassName="com.mysql.jdbc.Driver" 
url="jdbc:mysql://localhost:3306/plf?autoReconnect=true" />

<!-- eXo JCR Datasource for portal -->

<Resource name="exo-jcr_portal" auth="Container" type="javax.sql.DataSource"

...

username="plf" password="plf" driverClassName="com.mysql.jdbc.Driver" 
url="jdbc:mysql://localhost:3306/plf?autoReconnect=true" />
    • iii. Edit username, password, url (host, port and database name). Besides MySQL, if you are using Enterprise Edition, you will find the samples for other RDBMSs in conf/server-*.xml.
    • iv. Append this character encoding to the url in case your database character set is utf8. For example, in MySQL (this is different between RDBMSs): 
      url="jdbc:mysql://localhost:3306/plf?autoReconnect=true&amp;characterEncoding=utf8"
  2. Set the SQL Dialect if necessary. This step is not mandatory because the dialect is auto-detected in most cases. You only need to take care of it for some particular RDBMSs:
    • i. For JCR, only when you are using MySQL and database character set utf8, you need to edit gatein/conf/exo.properties file to have: 
      exo.jcr.datasource.dialect=MySQL-UTF8
    • ii. For IDM, eXo Platform detects automatically the dialect for RDBMSs listed here. Only when your RDBMS is not in the list, for example Postgres Plus Advanced Server 9.2, you will need to edit gatein/conf/exo.properties file to have: 
      hibernate.dialect=org.hibernate.dialect.PostgresPlusDialect
    If you have not created exo.properties yet, see Configuration overview.
  3. Download the JDBC driver for Java and install it to $PLATFORM_TOMCAT_HOME/lib.

    Tip

    Normally you can find out an appropriate driver for your JDK from your database vendor website. For example, for MySQL: http://dev.mysql.com/downloads/connector/j/, and for Oracle: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.

4.2.2 For JBoss
  1. Configure the datasources.
    • i. Edit standalone/configuration/standalone-exo.xml to remove the default HSQL configuration: 
      <!-- eXo IDM Datasource for PLF -->

<datasource enabled="true" jndi-name="java:/comp/env/exo-idm_portal" 
jta="false" pool-name="exo-idm_portal" spy="false" use-ccm="true" 
use-java-context="true">

    <!-- HSQLDB -->

    <driver>hsqldb-driver.jar</driver>

    <driver-class>org.hsqldb.jdbcDriver</driver-class>

    <connection-url>jdbc:hsqldb:file:${exo.data.dir}/hsql/exo-plf;
shutdown=true;hsqldb.write_delay=false;</connection-url>

...

<!-- eXo JCR Datasource for PLF -->

<datasource enabled="true" jndi-name="java:/comp/env/exo-jcr_portal" 
jta="false" pool-name="exo-jcr_portal" spy="false" use-ccm="true" 
use-java-context="true">

<!-- HSQLDB -->

    <driver>hsqldb-driver.jar</driver>

    <driver-class>org.hsqldb.jdbcDriver</driver-class>

    <connection-url>jdbc:hsqldb:file:${exo.data.dir}/hsql/exo-plf;
shutdown=true;hsqldb.write_delay=false;</connection-url>

    ...
    • ii. For MySQL as an example, need to uncomment some lines in the file, edit driver, username, password, url: 
      <!-- MySQL -->

<driver>mysql-connector-java-5.1.6-bin.jar</driver>

<driver-class>com.mysql.jdbc.Driver</driver-class>

<connection-url>jdbc:mysql://localhost:3306/plf?
autoReconnect=true</connection-url>

...

<security>

    <user-name>root</username>

    <password>exoplf</password>

</security>

<validation>

    ...

    <!-- MySQL -->

    <exception-sorter class-name=
"org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" />

    <valid-connection-checker 
class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.
MySQLValidConnectionChecker" />
    • iii. Append this character encoding to the url in case your database character set is utf8. For example, in MySQL (this is different between RDBMSs): 
      <connection-url>jdbc:mysql://localhost:3306/plf?autoReconnect=true&amp;
characterEncoding=utf8</connection-url>
  2. Set the SQL Dialect if necessary.
    This step is not mandatory because the dialect is auto-detected in most cases. You only need to take care of it for some particular RDBMSs:
    • i. For JCR, only when you are using MySQL and database character set utf8, you need to edit standalone/configuration/gatein/exo.properties file to have: 
      exo.jcr.datasource.dialect=MySQL-UTF8
    • ii. For IDM, eXo Platform detects automatically the dialect for RDBMSs listed here. Only when your RDBMS is not in the list, for example Postgres Plus Advanced Server 9.2, you will need to edit standalone/configuration/gatein/exo.properties file to have: 
      hibernate.dialect=org.hibernate.dialect.PostgresPlusDialect
    If you have not created exo.properties yet, see Configuration overview.
  3. Download the JDBC driver for Java and install it to $PLATFORM_JBOSS_HOME/standalone/deployments.

    Tip

    Normally you can find out an appropriate driver for your JDK from your database vendor website. For example, for MySQL: http://dev.mysql.com/downloads/connector/j/, and for Oracle: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.

    Note

    Particularly to MySQL, this fast install method might not work for some couples of MySQL server and driver versions. If it happens to you, solve it by installing the driver as a module. See details below.

4.2.3 Installing JDBC driver as a JBoss module
This alternative method is applied to solve a deployment problem with some couples of MySQL server and driver versions. There is no statement that indicates exactly the versions, but the problem likely happens with some most recent versions, such as MySQL server 5.6.19 and mysql-connector-java-5.1.35-bin.jar.
  • When it happens, you will get a JBAS014775 message like the following, and Platform JBoss does not start successfully: 
    JBAS014775:    New missing/unsatisfied dependencies:
      service jboss.jdbc-driver.mysql-connector-java-5_1_35-bin_jar (missing) 
dependents: 
      [service jboss.data-source.java:/comp/env/exo-idm_portal, 
      service jboss.driver-demander.java:/comp/env/exo-jcr_portal, 
      service jboss.driver-demander.java:/comp/env/exo-idm_portal, 
JBAS014799: ... and 3 more ]
Then you should remove the jar from $PLATFORM_JBOSS_HOME/standalone/deployments/ and install it to JBoss modules as follows:
  1. Create a new folder: $PLATFORM_JBOSS_HOME/modules/com/mysql/main/.
  2. Place the driver (.jar) in the created folder.
  3. Create a module.xml file in the created folder, with the following content: 
    <?xml version="1.0" encoding="UTF-8"?>

<module xmlns="urn:jboss:module:1.0" name="com.mysql">  

    <resources>  

        <resource-root path="mysql-connector-java-5.1.35-bin.jar"/>  
<!--replace this with your jar file-->

    </resources>  

    <dependencies>  

        <module name="javax.api"/>

    </dependencies>  

</module>
  4. Modify the datasources configuration in standalone-exo.xml to declare a driver in drivers tag and reference to it in datasource tag: 
    <subsystem xmlns="urn:jboss:domain:datasources:1.1">

    <datasources>

        <!-- eXo IDM Datasource for PLF -->

        <datasource enabled="true" jndi-name="java:/comp/env/exo-idm_portal" 
jta="false" pool-name="exo-idm_portal" spy="false" use-ccm="true" 
use-java-context="true">



            <driver>com.mysql</driver>

            <connection-url>jdbc:mysql://localhost:3306/plf?autoReconnect=true
</connection-url>



            <!-- note: don't put driver-class tag here-->

            ...

        </datasource>

        <!-- similar to other datasources, JCR (and Quartz in cluster) -->



        <drivers>

            <driver name="com.mysql" module="com.mysql">

                <xa-datasource-class>
com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                <driver-class>com.mysql.jdbc.Driver</driver-class>

            </driver>

        </drivers>

    </datasources>

</subsystem>

4.3. Datasource JNDI name

As said previously, eXo Platform uses two datasources, exo-idm_portal and exo-jcr_portal. If for any reason you change those names in datasource configuration (xml file), you need to match them in some other files.

Note

The properties file (exo.properties) will not take "_portal" suffix as it is appended automatically by eXo, as detailed below.
There is a constraint that the suffix of datasource JNDI names must be "_portal". Take JCR as example, it uses the following property: 
exo.jcr.datasource.name=java:/comp/env/exo-jcr
to look up a datasource for the portal. Because the core of eXo Platform is designed for supporting multi-portal, there are theoretically different datasources for different portals. Consequently this property is treated as datasource name's prefix, and the portal name (knowing that it is "portal" by default) is appended to complete the name in JNDI lookup.
So if you change the JDNI names exo-idm_portal and exo-jcr_portal, you need to edit the following properties: 
# JNDI Name of the IDM datasource
exo.idm.datasource.name=java:/comp/env/exo-idm
...
# name of the datasource that will be used by eXo JCR
exo.jcr.datasource.name=java:/comp/env/exo-jcr
in gatein/conf/exo.properties (Tomcat), or standalone/configuration/gatein/exo.properties (JBoss).
If you have not created exo.properties yet, see Configuration overview.
Particularly in Tomcat, you also need to edit conf/Catalina/localhost/context.xml.default file: 
<ResourceLink name="exo-jcr_portal" global="exo-jcr_portal" 
type="javax.sql.DataSource"/>

<ResourceLink name="exo-idm_portal" global="exo-idm_portal" 
type="javax.sql.DataSource"/>

4.4. Frequently asked questions

Q1. Any note for Microsoft SQL?
Q2. How to remove the idle MySQL connections?
Q3. How to enable managed connection?
Q1.Any note for Microsoft SQL?
Particularly to Microsoft SQL, you need to add sendStringParametersAsUnicode=false
 to the connection url as in the example below: 
<Resource name="exo-idm_portal" auth="Container" type="javax.sql.DataSource"

initialSize="5" maxActive="20" minIdle="5" maxIdle="15" maxWait="10000"

validationQuery="SELECT 1" validationQueryTimeout="5"

testWhileIdle="true" testOnBorrow="true" testOnReturn="false"

timeBetweenEvictionRunsMillis="30000" minEvictableIdleTimeMillis="60000"

removeAbandoned="true" removeAbandonedTimeout="300" logAbandoned="false"

poolPreparedStatements="true"

username="plf" password="plf" 
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" 

url="jdbc:sqlserver://localhost:1433;databaseName=plf;
sendStringParametersAsUnicode=false" />
This parameter is necessary to avoid any possible performance problem.
 MS SQL Server differentiates Unicode data types (such as nchar) from ASCII data types
(such as char). By default (without this parameter), all the JDBC drivers send strings in
Unicode format to SQL Server. When, for example, doing a comparison on a non-Unicode column,
 SQL Server tries to convert data in the table into Unicode first. This conversion might cause a
serious performance issue.
The parameter is a bit different between JDBC Drivers. See more details here.
Q2.How to remove the idle MySQL connections?
Some RDBMSs, like MySQL, close the idle connections after a period (8 hours on MySQL
by default). Thus, a connection from the pool will be invalid and any application SQL command
will fail, resulting in errors as below: 
org.hibernate.SessionException: Session is closed!
at org.hibernate.impl.AbstractSessionImpl.errorIfClosed
(AbstractSessionImpl.java:72)
at org.hibernate.impl.SessionImpl.getTransaction(SessionImpl.java:1342)
To avoid this, you can use DBCP to monitor the idle connections and drop them when they are
invalid, with the testWhileIdle, timeBetweenEvictionRunsMillis, and validationQuery
parameters.
The validation query is specific to your RDBMS. For example, on MySQL, you would use: 
testWhileIdle="true" timeBetweenEvictionRunsMillis="300000" 
validationQuery="SELECT 1"
  • testWhileIdle activates the idle connections monitoring.
  • timeBetweenEvictionRunsMillis defines the time interval between two checks in milliseconds (5 minutes in the example).
  • validationQuery provides a simple SQL command to validate connection to the RDBMS.
For more details, refer to the following:
Q3.How to enable managed connection?
This question is specific to the JCR datasource in Platform JBoss package.
When you want to use managed connections, set "true" for the gatein.jcr.datasource.managed property in the $PLATFORM_JBOSS_HOME/standalone/configuration/gatein/exo.properties file. See Configuration overview if you have not created this file yet. 
gatein.jcr.datasource.managed=true
To be clear, this property needs to be "true" in two cases:
  • You use a datasource with JTA enabled: 
    <datasource jndi-name="java:/comp/env/exo-jcr_portal" jta="true" .../>
  • You use an xa-datasource: 
    <xa-datasource  jndi-name="java:/comp/env/exo-jcr_portal" .../>
Using managed connections has pros and cons, so only do it if you know what you are doing. By default eXo Platform JBoss uses datasource jta="false".