Table of Contents

Configuring OpenXava 5.4.x for PostgreSQL
Get the latest JDBC driver
Configure PostgreSQL
Configure your OpenXava Project
Testing
Configurating OpenXava 2.X for Postgres
Verify Postgres is running
Configure the DB (changes from chapter 2.2 OX Reference)
Editing the file “build.xml”
Making Postgres JDBC Jars available for Tomcat
Create the file “tomcat-postgres.properties”
Edit the file “hibernate/hibernate-junit.cfg.xml”
Moving to the section 2.3 of the OX Reference guide

Configuring OpenXava 5.4.x for PostgreSQL

This guide is for later (5.4...) versions of OpenXava. This setup uses version 9.3 of PostgreSQL, but should work with any version. Also, these steps are for a Linux environment. If you're using Windows, well.....

Get the latest JDBC driver

Go here (https://jdbc.postgresql.org/download.html)and download the latest .jar file. In my case recently, it was
postgresql-9.4.1207.jre6.jar Make note of the location where you save it, of course, as you'll need to copy/move it to a couple of new locations later.

Configure PostgreSQL

I will presume that you have a working PostgreSQL installation, and have CREATE DATABASE permissions, either via the postgres user or a previously configured user with appropriate permissions. If not, stop right now and make sure that you are ready. Everything following will fail otherwise!

Create the database to be used by your OpenXava application. We will assume for the purposes of this example that your application is named "Invoice", and you want to use the database named "oxtest". (NOTE: I have found no difference - if correctly configured - between using upper or lower case names for the database. I have not tried mixed-case, but if you're feeling adventurous give it a try, once you have a successful test!) Make note, obviously of the DB user and password of the DB owner as you'll need that info. You can use either pgAminIII or the psql command line approach to create the database. In my case, I called "su" to become root, called "su - postgres" to become the postgres user, then ran "psql". At the psql prompt, just run "CREATE DATABASE oxtest OWNER gerry; " (Again, for this example, I'm using "gerry" as the DB user and "abcdef" as the DB password.) Presuming you are rewarded with a DATABASE CREATED message, we're ready for the next steps. If not, go back and start over or it will never work.

Also, before proceeding, stop the existing tomcat/OpenXava server either via the Server tab or the command line (./shutdown.sh). We'll want to monitor the server startup logs after reconfiguring, as well as making sure there's no conflict with any running processes.

Configure your OpenXava Project

Each OpenXava project stores its database configuration information spread amongst several files. (There are good architectural reasons for this, so no whining about wanting one config file! :-) These files are:

../openxava-5.4.1/tomcat/conf/context.xml
{ProjectFolder} -> persistence -> META-INF -> persistence.xml
{ProjectFolder} -> build.xml

Let's make the changes to each one....

../openxava-5.4.1/tomcat/conf/context.xml
For this file, note that each project has its own stanza. Ours would need to look like this:
<Resource name="jdbc/InvoiceDS" auth="Container" type="javax.sql.DataSource"
    maxActive="20" maxIdle="5" maxWait="10000"
    username="gerry" password="abcdef"
    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://localhost:5432/oxtest"/>
Next,
{ProjectFolder} -> persistence -> META-INF -> persistence.xml
Near the top of the file, in the "Tomcat + Hypersonic" section, find the "hibernate.dialect" property name and replace it with this line:
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
Towards the bottom of the same file, you'll find the <persistence-unit name="junit"> section. This handles schema creation and update so is very important! Here's my example of this section:
<persistence-unit name="junit">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <properties>
        <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
        <property name="hibernate.connection.release_mode" value="after_transaction"/>
        <property name="hibernate.connection.username" value="gerry"/>
        <property name="hibernate.connection.password" value="abcdef"/>
        <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/oxtest"/>
    </properties>**
</persistence-unit>**
The last file to modify is {ProjectFolder} -> build.xml
At the bottom of the file, you'll find the "updateSchema" target. Replace the "schema.path" property with the entry below:
<property name="schema.path" value="../OpenXava/lib/postgresql-9.4.1207.jre6.jar"/>
Now, we need to copy the previously downloaded .jar to the required location. Referencing the above line, the actual full path to which to copy the .jar file is: ~/openxava-5.4.1/workspace/OpenXava/lib
After copying the file here, verify that its permissions are correct. All the jar files here have the permissions of 770 so make sure to chmod the newly installed jar file, and verify owner and group.
NOTE: This location is used for OpenXava to build the tables, etc. If you are using the default Tomcat server that comes with OpenXava, you MUST also
copy the .jar file to: ../openxava-5.4.1./tomcat/lib and again confirm the 770 permissions in order for your application to run.

Testing

Alright! At this point, we are ready to test. Before running your application (I presume you have some app in some degree of development), we'll need to do a couple more steps. First, restart Tomcat/OpenXava server, again either via the Server start mechanism within OpenXava or command line via "./startup.sh". Next, we need to update the schema as part of rebuilding the application. As this is the first run, the schema update should create the necessary tables and columns. Monitor the startup messages closely, and correct any errors flagged. If you've followed these directions diligently, you should be up and ready to go using OpenXava with PostgreSQL!

Configurating OpenXava 2.X for Postgres

In this little guide we will only modify the files we have got after generating a new project as references in section 2.1 from OX Reference guide. We also assume that OpenXava is installed in the folder openxava2.X

Verify Postgres is running

You should verify that Postgres is running. Use the utility pgadminIII (available from the Postgres Web) to create a database called “MYDB” (in upper case letters) and a schema called “management” (in lower case).

Configure the DB (changes from chapter 2.2 OX Reference)

The file context.xml from the folder openxava2.x/portal/conf should be edited as follows:
<Resource name="jdbc/ManagementDS"
auth="Container"
type="javax.sql.DataSource"
maxActive="20"
maxIdle="5"
maxWait="10000"
username="postgres"
password="my_postgres_password"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql:localhost:5432/MYBD"/>
my_postgres_password is the password for user postgres in the Postgres database.
url="jdbc:postgresql:localhost:5432/MYDB" defines how to access the Database. (MYDB is the database we have created; localhost:5432 is the address and port of the server executing the Postgres database service.

Remarks:
We should distinguish the Tomcat from inside Eclipse and the one outside Eclipse. In the first case the file context.xml is in the folder “openxava2.X/workspace/ Servers/Tomcat v5.5 Server @ localhost-config “ and in the second case in the fólder “openxava2.X/portal/conf”. See OpenXava with Eclipse WTP.

Editing the file “build.xml”

We look for a line containing tomcat-hypersonic and comment it and add a new line as shows:
<!-- Comment
<property name="configuration" value="tomcat-hypersonic"/>
-->
<property name="configuration" value="tomcat-postgres"/>
 
In addition, we look for the task updateSchema and give the variable schema.path the absolute path for the JDBC JAR driver (in my particular case in Windows XP "C:\POSTGRES\postgresql-8.3dev-601.jdbc3.jar". You can verify if there is a newer JDBC driver in the WEB, and you should use it!
<!-- Comment
<property name="schema.path" value="[PUT HERE THE PATH OF YOUR JDBC DRIVER]"/>
-->
<property name="schema.path" value="C:/POSTGRES/postgresql-8.3dev-601.jdbc3.jar"/>
 
And save changes.

Making Postgres JDBC Jars available for Tomcat

I have copied my particular file C:\POSTGRES\postgresql-8.3dev-601.jdbc3.jar (or a newer one) into the folder openxava-2.X/portal/common/lib

Create the file “tomcat-postgres.properties”


Let’s copy the file tomcat-hypersonic.properties to a new one called tomcat-postgres.properties. Let’s change separator to “.” And hibernate.dialect to “org.hibernate.dialect.PostgreSQLDialect
separator=.
organization_es=Gestión cuatrocientos, s.l.
organization_ca=Gestió quatre-cents, s.l.
organization_en=Management four hundred, limited
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
datasource.prefix=java:comp/env/jdbc
 

Edit the file “hibernate/hibernate-junit.cfg.xml”

We should comment all about HSQLDB and create new entries for Postgres:
<session-factory>
<!-- Hypersonic commented
<property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
<property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
<property name="hibernate.connection.url">jdbc:hsqldb:hsql:localhost:1666</property>
-->
<!-- Postgres-->
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.connection.url">jdbc:postgresql:localhost:5432/MYBD</property>
 
Where localhost:5432 references the address and port of our server running the Postgres service and MYDB is the database we are using for this example.
In addition to these, we have to change all about users and passwords in the DB. We should change the user to postgres and introduce the correct password. We shall comment the lines referencing HSQLDB
<!-- Hypersonic Access commented
<property name="hibernate.connection.username">sa</property>
<property name="hibernate.connection.password"></property>
<property name="hibernate.show_sql">false</property>
-->
<!-- Postgres Access-->
<property name="hibernate.connection.username">postgres</property>
<property name="hibernate.connection.password">my_postgres_password</property>
<property name="hibernate.show_sql">false</property>
And save changes.

Moving to the section 2.3 of the OX Reference guide

Our component “Warehouse.xml” that has been mapped to the table WAREHOUSES from the schema “MANAGENENT” (which was previously created as “management” in lower case letters) remains unchanged as in section 2.3 as follows:
<?xml version="1.0" encoding="ISO-8859-1"?>
 
<!DOCTYPE component SYSTEM "dtds/component.dtd">
 
<component name="Warehouse">
<entity>
<property name="zoneNumber" key="true"
size="3" required="true" type="int"/>
<property name="number" key="true"
size="3" required="true" type="int"/>
<property name="name" type="String"
size="40" required="true"/>
</entity>
 
<entity-mapping table=MANAGEMENT@separator@WAREHOUSES>
<property-mapping
property="zoneNumber" column="ZONE"/>
<property-mapping
property="number" column="NUMBER"/>
<property-mapping
property="name" column="NAME"/>
</entity-mapping>
</component>
 
Let’s execute the Ant tasks generateCode and rebuild and finally updateSchema and the tables should be created in our database.
After that, follow the instructions from the guide.

(improvements to this page and error reporting are wellcome. Eduard.)