Changing Default Database in OFBiz
Apache OFBiz is a open source ERP solution which can be used to automate any business processes. It has in-built modules like CRM (Customer Realtionship Management), ERP (Enterprise Resource Planning), E-Commerce, MRP (Manufacturing Resource Planning), SCM (Supply Chain Management), HRMS (Human Resource Management System), POS etc.
To learn more about Apache OFBiz please go through my earlier post and to learn more on OFBiz developement please refer this post.
By default OFBiz is integrated with the Apache Derby database system While Derby is sufficient to handle OFBiz during software development, evaluation and functional testing. It is not recommended for environments that experience high transaction volumes. In particular, it is not recommended for use in production environments.
So, in this post we will learn in detail how to change this default Derby database with more robust, production ready MySQL, PostgreSQL and Oracle database.
Before configuring an external database
Before configuring an external database, the following few steps have to be ensured:
- Before changing the OFBiz Entity Engine configuration to use a remote data source, you must first create the remote database. The remote database must exist.
- Add a user/owner for the remote database. OFBiz will access the database as this user. Make sure the user has all necessary privileges to create and remove database tables.
- Add a user/owner password (if desired or necessary) to the remote database.
- Ensure that the IP port the database is listening on for remote connections is open and clear of any firewall obstructions (for example, by default, PostgreSQL listens for connections on port 5432).
- Add the appropriate database driver to the ~framework/entity/lib/jdbc directory. For example, if you are using PostgreSQL version 8.3, download the postgresql-8.3-605.jdbc2.jar driver from the PostgreSQL website (http://jdbc.postgresql.org/download.html).
Configuring External Database
To configure another external database, follow these few steps:
- Open the Entity Engine’s configuration file located at: ~framework/entity/config/entityengine.xml
- Within the entityengine.xml file, configure the remote database’s usage settings. A suggested method for doing this is to take an existing datasource element entry and modify that to reflect the necessary settings for a remote database. There are examples provided for most of the commonly used databases. For example, to configure a remote PostgreSQL database with the name of myofbiz_db, with a username ofbiz and password of ofbiz, edit the localpostnew configuration entry as shown here:
<datasource name="localpostnew"
helper-class=
"org.ofbiz.entity.datasource.GenericHelperDAO"
schema-name="public"
field-type-name="postnew"
check-on-start="true"
add-missing-on-start="true"
use-fk-initially-deferred="false"
alias-view-columns="false"
join-style="ansi"
result-fetch-size="50"
use-binary-type-for-blob="true">
<read-data reader-name="seed"/>
<read-data reader-name="seed-initial"/>
<read-data reader-name="demo"/>
<read-data reader-name="ext"/>
<inline-jdbc jdbc-driver="org.postgresql.Driver"
jdbc-uri="jdbc:postgresql://127.0.0.1/myofbiz_db"
jdbc-username="ofbiz"
jdbc-password="ofbiz"
isolation-level="ReadCommitted"
pool-minsize="2"
pool-maxsize="250"/>
</datasource>3. Configure the default delegator for this data source:
<delegator name="default" entity-model-reader="main"
entity-group-reader="main" entity-eca-reader="main"
distributed-cache-clear-enabled="false">
<group-map group-name="org.ofbiz"
datasource-name="localpostnew"/>
<group-map group-name="org.ofbiz.olap"
datasource-name="localderbyolap"/>
</delegator>4. Save and close the entityengine.xml file.
5. From the OFBiz install directory, rebuild OFBiz by running the ant run-install command.
6. Start OFBiz.
7. Test by observing that the database was created and populated. You may use the WebTools entity reference page (https://localhost:8443/webtools/ control/entityref) to search for your newly created entities, or a third-party tool designed to work with your specific database.
Configure MySQL as default database with OFBiz
Before proceeding please make sure you have MySQL database installed on your system. Now follow below commands to create required databases in MySQL, setting up user and granting access.
Step – I
C:\mysql-5.5.23-winx64\bin>mysql -u root
mysql>create database ofbiz;
mysql>create database ofbizolap;
mysql>create database ofbiztenant;
mysql>use mysql;
mysql>select database();
mysql>create user ofbiz@localhost;
mysql>create user ofbizolap@localhost;
mysql>create user ofbiztenant@localhost;
mysql>update user set password=PASSWORD("ofbiz") where User='ofbiz';
mysql>update user set password=PASSWORD("ofbizolap") where User='ofbizolap';
mysql>update user set password=PASSWORD("ofbiztenant") where User='ofbiztenant';
mysql>grant all privileges on *.* to 'ofbiz'@localhost identified by 'ofbiz';
mysql>grant all privileges on *.* to 'ofbizolap'@localhost identified by 'ofbizolap';
mysql>grant all privileges on *.* to 'ofbiztenant'@localhost identified by 'ofbiztenant';Download latest MySQL database driver and copy it in the ~framework/entity/lib/jdbc directory
Step – II
Use webtools to export all data to XML:
- Start OfBiz
- Navigate to http://localhost:8080/webtools
- Go to section ‘Entity XML Tools’ and click the link ‘XML Data Export All’ – export the data to a directory of your choice
Step – III
1. Stop OfBiz server 2. Create a backup of <ofbiz-dir>/framework/entity/config/entityengine.xml 3. Edit entityengine.xml as follows: a. Update the datasources to use below jdbc driver
<inline-jdbc
jdbc-driver="com.mysql.jdbc.Driver"
jdbc-uri="jdbc:mysql://127.0.0.1/ofbiz?autoReconnect=true&characterEncoding=UTF-8"
jdbc-username="ofbiz"
jdbc-password="ofbiz"
isolation-level="ReadCommitted"
pool-minsize="2"
pool-maxsize="250"
time-between-eviction-runs-millis="600000"/>b. Replace derby with mysql in default, default-no-eca and test delegators as follows:
<delegator name="default" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" distributed-cache-clear-enabled="false">
<group-map group-name="org.apache.ofbiz" datasource-name="localmysql"/>
<group-map group-name="org.apache.ofbiz.olap" datasource-name="localmysqlolap"/>
<group-map group-name="org.apache.ofbiz.tenant" datasource-name="localmysqltenant"/>
</delegator>
<delegator name="default-no-eca" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" entity-eca-enabled="false" distributed-cache-clear-enabled="false">
<group-map group-name="org.apache.ofbiz" datasource-name="localmysql"/>
<group-map group-name="org.apache.ofbiz.olap" datasource-name="localmysqlolap"/>
<group-map group-name="org.apache.ofbiz.tenant" datasource-name="localmysqltenant"/>
</delegator>
<delegator name="test" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main">
<group-map group-name="org.apache.ofbiz" datasource-name="localmysql"/>
<group-map group-name="org.apache.ofbiz.olap" datasource-name="localmysqlolap"/>
<group-map group-name="org.apache.ofbiz.tenant" datasource-name="localmysqltenant"/>
</delegator>c. Save this file
Step – IV
Create the ofbiz plugin
./gradlew createPlugin -PpluginId=MySQLDemo
4. And add gradle dependency of MySQL in MySQLDemo plugin.
dependencies {
pluginLibsRuntime 'mysql:mysql-connector-java:5.1.6'
}5. Load the deafult data
./gradlew loadDefault
Configure PostgreSQL as default database with OFBiz
Configuring PostgreSQL is similar to MySQL database, just you need to Download latest PostgreSQL database driver and copy it in the ~framework/entity/lib/jdbc directory and change jdbc connection URL as mentioned below
In Step – III above use below connection URL
<inline-jdbc
jdbc-driver="org.postgresql.Driver"
jdbc-uri="jdbc:postgresql://127.0.0.1:5432/ofbiz"
jdbc-username="ofbiz"
jdbc-password="ofbiz"
isolation-level="ReadCommitted"
pool-minsize="2"
pool-maxsize="250"
time-between-eviction-runs-millis="600000"/>Change in Step – IV
Create the ofbiz plugin
./gradlew createPlugin -PpluginId=PostgreSQLDemo
4. And add gradle dependency of PostgreSQL in PostgreSQLDemo plugin.
dependencies {
pluginLibsRuntime 'postgresql:postgresql:9.0-801.jdbc4'
}Configure Oracle as default database with OFBiz
Configuring Oracle is also similar to MySQL database, just you need to Download latest Oracle database driver and copy it in the ~framework/entity/lib/jdbc directory andchange jdbc connection URL as mentioned below
In Step – III above use below connection URL
<inline-jdbc
jdbc-driver="oracle.jdbc.driver.OracleDriver"
jdbc-uri="jdbc:oracle:thin:@localhost:1521:ofbiz"
jdbc-username="ofbiz"
jdbc-password="ofbiz"
isolation-level="ReadCommitted"
pool-minsize="2"
pool-maxsize="250"
time-between-eviction-runs-millis="600000"/>Change in Step – IV
Create the ofbiz plugin
./gradlew createPlugin -PpluginId=OracleDemo
And add gradle dependency of Oracle in OracleDemo plugin.
dependencies {
pluginLibsRuntime 'com.oracle:ojdbc14:10.2.0.4.0'
}How it works
The Entity Engine is configured using the entityengine.xml file. Whenever OFBiz is restarted, the Entity Engine initializes itself by first referencing this file, and then building and testing all the designated database connections. In this way, an unlimited number of data source connections, database types, and even low-level driver combinations may be applied at runtime without affecting the higher-level database access logic.
By abstracting the connection using one or more delegators, OFBiz further offloads low level database connection management from the developer and handles all connection maintenance, data mappings, and the default transaction configuration for an unlimited number of target databases.
Conclusion
We learned how to chagne the default database i.e Derby databse in Apache OFBiz. The detailed steps on using MySQL and PostgreSQL database is also covered.
Please feel free to post your queries, doubts in comments section.
References
ofbiz.org