Configuring OpenXava with MySQL Database

Configuring OpenXava with MySQL Database:
------------------------------------------------
Install
1) MySQL database (ex. MySQL 5.5)
2) MySQL connector J (eg. mysql-connector-java-5.1.22-bin.jar)
3) OpenXava 4.9.1

Steps to follow :

1) Create a new project (ex. Academy) according to the steps mentioned in the OpenXava reference guide. (http://openxava.wikispaces.com/reference_en)

2) Go to MySQL DB Console and create a new database schema such as eg. academydb
also create a table using the following scrript
CREATE  TABLE IF NOT EXISTS `ACADEMYDB`.`ACADEMY` (
  `academyId` INT(3) NOT NULL ,
  `academyName` VARCHAR(25) NOT NULL,
  `city` VARCHAR(25),
  `state` VARCHAR(25),
  PRIMARY KEY (`academyId`) ,
  UNIQUE INDEX `ACADEMY_NAME_UNIQUE` (`academyName` ASC) );

3. Place MySQL connector jar into
{OpenXavaBase}/tomcat/lib
{OpenXavaBase}/workspace/OpenXavaTest/lib

4. Open the context.xml file in
{OpenXavaBase}/tomcat/conf folder.
Insert the following lines into the file.
<Resource name="jdbc/AcademyDS" auth="Container" type="javax.sql.DataSource"
                                maxActive="20" maxIdle="5" maxWait="10000"
                                username="root" password="root@123" driverClassName="com.mysql.jdbc.Driver"
                                url="jdbc:mysql://localhost:3306/academydb"/>

5. Go to the project folder’s build.xml (Academy/build.xml) and change updateSchema target as follows. You have to change the value of the schema.path to point to the MySQL connector jar which is available in OpenXavaTest/lib folder.
<target name="updateSchema">
                                <ant antfile="../OpenXava/build.xml" target="updateSchemaJPA">                                    
                                                <property name="persistence.unit" value="junit"/>
                                                <property name="schema.path" value="../OpenXavaTest/lib/mysql-connector-java-5.1.22-bin.jar"/>
                                </ant>
                </target>          

6.Go to persistence.xml (Academy/persistence/META-INF/persistence.xml)
Change the default persistence unit as follows
 <!-- Tomcat + MySQL-->
    <persistence-unit name="default">
                <provider>org.hibernate.ejb.HibernatePersistence</provider>
                <non-jta-data-source>java:comp/env/jdbc/AcademyDS</non-jta-data-source>
                <class>org.openxava.session.GalleryImage</class>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
        </properties>
    </persistence-unit>

<!-- JUnit MySQL-->
    <persistence-unit name="junit">
                <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
                                <property name="hibernate.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://localhost:3306/academydb"/>
        </properties>
    </persistence-unit>  

7.Go to hibernate.cfg.xml (Academy/persistence/hibernate.cfg.xml) and change the hibernate.dialect to org.hibernate.dialect.MySQLDialect as follows
<!-- Tomcat + MySQL-->
                                <property name="hibernate.connection.datasource">java:comp/env/jdbc/AcademyDS</property>
                                <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
                                <property name="hibernate.jdbc.use_get_generated_keys">false</property>                            
                                <property name="hibernate.show_sql">false</property>

& now you run updateSchema target, it will create war & unzip it under webapps folder in
{OpenXavaBase}\tomcat\webapps\Academy, that will access the MySQL to retrieve and save data.