Liferay can be configured to use many different databases. For this example we will change the default database (Hypersonic) of the Liferay-Tomcat bundle to point to a MySQL database

Configuration

Liferay v5.2 and later

Liferay v5.2 unifies the configuration of the database in a single file for all application servers. As most other configuration options you can set this up in the portal-ext.properties file as follows (example for MySQL):

jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.default.username=
jdbc.default.password=

Since 5.2.2 one can again use the connection pools provided by the application server by specifying the JNDI name to be used in portal-ext.properties using the following property:

jdbc.default.jndi.name=jdbc/LiferayPool

This will re-establish the way connections are handled to the behaviour of version 5.1.x

If you do not have an existing portal-ext.properties file, you can create one and save it to:

{liferay-home}\{tomcat}\webapps\ROOT\WEB-INF\classes

for Tomcat. (To find out where to put portal-ext.properties for other app servers, please refer to the Portal Administrator's Guide.)

The portal.properties file has examples for all the main databases supported. However, if you are using the bundled / download version of Liferay (instead of building from source), you will not be able to find portal.properties in your installation. (Refer to page 127 of the Portal Administrator's Guide Second Edition for an explanation why this is the case.) Instead, you can find a copy of portal.properties here for reference:

http://svn.liferay.com/repos/public/portal/branches/5.2.x/portal-impl/src/portal.properties

Again, once you've referenced portal.properties to understand what settings are available, make the appropriate additions to your portal-ext.properties file.

Note that this system uses the apache commons pool for pooling connections to the database. If you prefer to use the pooling mechanism provided by the portal application server you can still do that through spring. In particular add this to the ext-spring.xml file:

<bean id="liferayDataSource"
class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
       <property name="targetDataSource">
               <bean
class="com.liferay.portal.spring.jndi.JndiObjectFactoryBean">
                       <property name="jndiName" value="jdbc/LiferayPool" />
               </bean>
       </property>
</bean>

This file can be found in your extension environment. If you are not using this environment just create an Spring configuration file with that name and add it to the META-INF dir of your installation.

For MySql, make sure you create the database first;

create database lportal character set utf8;

Liferay v5.1 and previous

In Liferay v5.1 and previos versions the configuration was specific for each application server. This section explains how to do the configuration for tomcat by modifying the ROOT.xml file. For other application servers please check their documentation.

ROOT.xml can be found in tomcat\conf\Catalina\localhost\ROOT.xml. for reference, at the end of this article I have included an excerpt of ROOT.xml from Liferay 4.3.x

Notice there are sample entries in this file for many different databases. By default, Liferay uses Hypersonic (lightweight db used for development purposes). Notice that all entries are commented out except for the Hypersonic entry. Since we want to change Liferay to point to MySQL, we will simply

1) comment out the Hypersonic entry,

	<!-- Hypersonic -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.hsqldb.jdbcDriver"
		url="jdbc:hsqldb:lportal"
		username="sa"
		password=""
		maxActive="20"
	/>-->

2) and uncomment the MySQL entry (or which ever entry you need for your database).

	<!-- MySQL -->

	<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/lportal?useUnicode=true&amp;characterEncoding=UTF-8"
		username=""
		password=""
		maxActive="20"
	/>

make sure that you change the value for url to point to your MySQL database url, and also fill in a username and password.

Including the JDBC driver

The next step is to make sure that tomcat has the appropriate JDBC driver so that it can 'talk' to this database. You can find these drivers online, however, for your convenience, we have included the MySQL driver (along with many others) in the portal code at the following path:

portal\lib\development\mysql.jar

For Liferay 5.1.1, if you are using Oracle 9i, then there are some issues with ojdbc14.jar. Instead of that use other jars like ojdbc14_g.jar for connecting to database.

If we are..

• developing in the EXT environment, we need to copy the JDBC driver to this path:
  • ext\ext-lib\global\mysql.jar

• developing directly from Portal Core (not recommended), copy the driver here:
  • portal\lib\global\mysql.jar

• only using the Tomcat bundle, copy the driver here:
  • tomcat\common\lib\ext\mysql.jar

Creating the database and tables

You will first need to create the database, make sure that this matches the value in the url of your ROOT.xml.

For most versions of Liferay, you will have 2 sets of sql scripts, one which will create the Liferay tables with sample data (the Liferay public website) or with just the bare minimal data. You can find both sets of scripts in the portal code:

portal\sql\portal\portal-mysql.sql

and

portal\sql\portal-minimal\portal-minimal-mysql.sql

If you..

• dont have the portal source code,
  • you can download all the scripts online here: http://www.liferay.com/web/guest/downloads/additional or http://sourceforge.net/projects/lportal and download the portal SQL scripts.
• dont see the scripts in source,
  • you will have to run "ant start" from your portal source folder which will generate the appropriate files.

Manually creating the table

Run the appropriate script against your database.

Automatically creating the tables

in portal.properties, there is a property that you can set which will determine if the portal will create a minimal version of Liferay or a version which is pre-populated with sample data.

##
## Schema
##

    #
    # Set this to true to automatically create tables and populate with default
    # data if the database is empty.
    #
    schema.run.enabled=true

    #
    # Set this to to true to populate with the minimal amount of data. Set this
    # to false to populate with a larger amount of sample data.
    #
    schema.run.minimal=false

If you wanted Liferay to automatically create only the necessary Liferay tables for you, then add the following values in your portal-ext.properties (tomcat\webapps\ROOT\WEB-INF\classes\portal-ext.properties)

    schema.run.enabled=true
    schema.run.minimal=true

Reference

excerpt of ROOT.xml in 4.3.x

<Context path="" crossContext="true">

	<!-- DB2 -->

	<!--<Resource
		name="jdbc/LiferayPool"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="com.ibm.db2.jcc.DB2Driver"
        url="jdbc:db2:lportal"
        username="db2admin"
        password="lportal"
        maxActive="20"
	/>-->

	<!-- Derby -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
		url="jdbc:derby:lportal"
		username=""
		password=""
		maxActive="20"
	/>-->

	<!-- Hypersonic -->

	<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.hsqldb.jdbcDriver"
		url="jdbc:hsqldb:lportal"
		username="sa"
		password=""
		maxActive="20"
	/>

	<!-- MySQL -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/lportal?useUnicode=true&amp;characterEncoding=UTF-8"
		username=""
		password=""
		maxActive="20"
	/>-->

	<!-- Oracle -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="oracle.jdbc.driver.OracleDriver"
		url="jdbc:oracle:thin:@localhost:1521:orcl"
		username="lportal"
		password="lportal"
		maxActive="20"
	/>-->

	<!-- P6Spy -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.p6spy.engine.spy.P6SpyDriver"
		url="jdbc:mysql://localhost/lportal?useUnicode=true&amp;characterEncoding=UTF-8"
		username=""
		password=""
		maxActive="20"
	/>-->

	<!-- PostgreSQL -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.postgresql.Driver"
		url="jdbc:postgresql://localhost:5432/lportal"
		username="sa"
		password=""
		maxActive="20"
	/>-->

	<!-- SQL Server -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="net.sourceforge.jtds.jdbc.Driver"
		url="jdbc:jtds:sqlserver://localhost/lportal"
		username="sa"
		password=""
		maxActive="20"
	/>-->

	<!-- Sybase -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="net.sourceforge.jtds.jdbc.Driver"
		url="jdbc:jtds:sybase://localhost:5000/lportal"
		username="sa"
		password=""
		maxActive="20"
	/>-->

Related Articles

Tomcat with SQL Server 2008

Building and Installing Liferay Source Code on Glassfish v2 and MySQL

Sybase configuration for Liferay

DB2 Express-C 9.5 on CentOS 5.2

Derby with Glassfish

PostgreSQL with Glassfish

Derby Java DB RDBMS