Changing OpenXava Database

OpenXava takes me back a few years to the days of modelling your application and generating out the database and application. This was the domain of the now defunct CASE tools. OpenXava uses the Java Persistence API, via Hibernate to access the persistent store. I am using the Opensource tool on a current project. Details of OpenXava are at http://www.openxava.org/. If you need an Opensource tool and deployment architecture it certainly goes to the top of my list. Easy to learn and deploy. However, the default datasource is a store called Hypersonic. Not something I have come across before and the project requirement is for a MySQL store.

One of the advantages of using Hibernate is the agnostic attitude to datastore. But you need to do a little configuration to switch from Hypersonic to another tool. Sounds easy, but cost me a lot of time, so have recorded what I found to work.

References to other discussion on moving from Hypersonic to MySQL to run an OpenXava project can be found at:

Adding my own configuration files as something that I found to work in 2017…

I am using MAMP on my MAC to provide MySQL on the usual Port 3306. database username is efd and a simple password of efd.  I also use MySQL Workbench for modelling and checking tables, columns etc.

To move from Hypersonic to MySQL, localhost, port 3306, database efd  and a datasource name of EFD_v2 you will need to change the following:

Under project / persistence / META-INF edit persistence.xml (look at the ‘efd’ changes. This is only for development work so go easy on security comments…

persistence.xml

<?xml version=“1.0” encoding=“UTF-8”?>

<persistence xmlns=“http://java.sun.com/xml/ns/persistence”

             xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”

             xsi:schemaLocation=“http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd”

             version=“1.0”>

             

    <!– Tomcat + Hypersonic –>

    

        <persistence-unit name=“default”>

    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <non-jta-data-source>java:comp/env/jdbc/EFD_v2DS</non-jta-data-source>

    <class>org.openxava.session.GalleryImage</class>

        <properties>

            <property name=“hibernate.dialect” value=“org.hibernate.dialect.MySQLDialect”/>

        </properties>

    </persistence-unit>

           

    <!– JBoss + Hypersonic

    <persistence-unit name=”default”>

    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

    <non-jta-data-source>java:/EFD_v2DS</non-jta-data-source>

    <class>org.openxava.session.GalleryImage</class>

        <properties>

            <property name=”hibernate.dialect” value=”org.hibernate.dialect.HSQLDialect”/>

            <property name=”hibernate.hbm2ddl.auto” value=”update”/>

            <property name=”hibernate.connection.release_mode” value=”after_transaction”/>

        </properties>

    </persistence-unit>    

    –>

    

    <!– WebSphere + AS/400

    <persistence-unit name=”default”>

    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

    <non-jta-data-source>jdbc/EFD_v2DS</non-jta-data-source>

    <class>org.openxava.session.GalleryImage</class>

        <properties>

            <property name=”hibernate.dialect” value=”org.hibernate.dialect.DB2400Dialect”/>

            <property name=”hibernate.hbm2ddl.auto” value=”update”/>

            <property name=”hibernate.connection.release_mode” value=”after_transaction”/>

        </properties>

    </persistence-unit>

    –>

    

    <!– JUnit Hypersonic –>

    <persistence-unit name=“junit”>

    <provider>org.hibernate.ejb.HibernatePersistence</provider>

        <properties>

<property name=“connection.driver_class” value=“com.mysql.jdbc.Driver” />

<property name=“hibernate.dialect” value=“org.hibernate.dialect.MySQLDialect”/>

<property name=“hibernate.connection.url” value=“jdbc:mysql://127.0.0.1:3306/efd”/>

<property name=“hibernate.connection.username” value=“efd”/>

<property name=“hibernate.connection.password” value=“pw”/>

        </properties>

    </persistence-unit>

    

</persistence>

Under project / persistence edit hibernate.cfg.xml

hibernate.cfg.xml

<!DOCTYPE hibernate-configuration PUBLIC

“-//Hibernate/Hibernate Configuration DTD//EN”

“http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd”>

<!–

This hibernate configuration is used only for IMAGES_GALLERY stereotype,

because this stereotype is implemented using hibernate native APIs.

If you do not use IMAGE_GALLERY you do not need this file.

The datasource configured here is the datasource in where the images will be

store, and usually match wiht the main datasource of application defined in

persistence.xml.

–>

<hibernate-configuration>

<session-factory>

<!– Tomcat + Mysql –>

<property name=”hibernate.connection.datasource”>java:comp/env/jdbc/EFD_v2DS</property>

<property name=”hibernate.dialect” value=”org.hibernate.dialect.MySQLDialect” />

<property name=”hibernate.jdbc.use_get_generated_keys”>false</property>

<property name=”hibernate.show_sql”>false</property>

<!– GalleryImage is needed only if you uses IMAGES_GALLERY/GALERIA_IMAGENES stereotype –>

<mapping resource=”GalleryImage.hbm.xml”/>

</session-factory>

</hibernate-configuration>

Edit / Servers / Tomcat / context.xml

context.xml (see last line)

<?xml version=”1.0″ encoding=”UTF-8″?>

<!–

  Licensed to the Apache Software Foundation (ASF) under one or more

  contributor license agreements.  See the NOTICE file distributed with

  this work for additional information regarding copyright ownership.

  The ASF licenses this file to You under the Apache License, Version 2.0

  (the “License”); you may not use this file except in compliance with

  the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software

  distributed under the License is distributed on an “AS IS” BASIS,

  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  See the License for the specific language governing permissions and

  limitations under the License.

–><!– The contents of this file will be loaded for each web application –><Context>

    <!– Default set of monitored resources –>

    <WatchedResource>WEB-INF/web.xml</WatchedResource>

    <!– Uncomment this to disable session persistence across Tomcat restarts –>

    <!–

    <Manager pathname=”” />

    –>

    <!– Uncomment this to enable Comet connection tacking (provides events

         on session expiration as well as webapp lifecycle) –>

    <!–

    <Valve className=”org.apache.catalina.valves.CometConnectionManagerValve” />

    –>

    

    <!–

    <Resource name=”jdbc/OpenXavaTestDS” auth=”Container” type=”javax.sql.DataSource”

      maxActive=”20″ maxIdle=”5″ maxWait=”10000″

      username=”sa” password=”” driverClassName=”org.hsqldb.jdbcDriver”

      url=”jdbc:hsqldb:file:../data/openxava-test-db“/>

    –>

    

    <Resource auth=”Container” driverClassName=”org.hsqldb.jdbcDriver” maxActive=”20″ maxIdle=”5″ maxWait=”10000″ name=”jdbc/OpenXavaTestDS” password=”” type=”javax.sql.DataSource” url=”jdbc:hsqldb:hsql://localhost:1555″ username=”sa“/>

        

    <Resource auth=”Container” driverClassName=”org.hsqldb.jdbcDriver” maxActive=”20″ maxIdle=”5″ maxWait=”10000″ name=”jdbc/MySchoolDS” password=”” type=”javax.sql.DataSource” url=”jdbc:hsqldb:file:../data/my-school-dbusername=”sa“/>

     

    <Resource auth=”Container” driverClassName=”org.hsqldb.jdbcDriver” maxActive=”20″ maxIdle=”5″ maxWait=”10000″ name=”jdbc/InvoiceDemoDS” password=”” type=”javax.sql.DataSource” url=”jdbc:hsqldb:file:../data/invoice-demo-dbusername=”sa“/>      

    

    <Resource name=”jdbc/EFD_v2DS” auth=”Container” type=”javax.sql.DataSource” driverClassName=”com.mysql.jdbc.Driver” url=”jdbc:mysql://127.0.0.1:3306/efdusername=”efd” password=”pw” validationQuery=”select 1″  maxActive=”100″  maxIdle=”4″/>

    

</Context>

also

add an appropriate mysql jdbc file to {OPENXAVA_HOME}/tomcat/lib

i.e. mysql-connector-java-5.1.42-bin.jar

You should now be able to get your OpenXava project to read and write data to your local MySQL database.