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/ManagemetDS"
    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; loclahost: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.)

Table of Contents