Step 3 Database Setup

In this sample application, we're using a database to show orders, customers and suppliers. First, you will have to create this database that will be working in the background. It will provides us with all the necessary information. This sample uses an HSQL database, but you can choose any other database type. To create the database, download the sample database . Create a new folder with the name "db" in the project root folder and unpack the downloaded file into this folder. The database is now on your computer.

The next step is to include the JDBC database connector in the project. To do this, open POM.XML in the project root folder and copy the following to the dependencies:

         <dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.5.1</version>
</dependency>
        

Once you completed this, the POM.XML will look like this:

         <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.jbstrap.example</groupId>
    <artifactId>OrderManager</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>Application</name>
    <description>Application description</description>
    <organization>
        <name>com.jbstrap.example</name>
        <url> </url>
    </organization>
 
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
 
    <repositories>
        <repository>
            <id>JBStrap</id>
            <name>JBStrap framework repository</name>
            <url>http://repo.jbstrap.com/repository/jbstrap</url>
        </repository>
    </repositories>
 
    <dependencies>
        <dependency>
            <groupId>com.jbstrap</groupId>
            <artifactId>JBStrap</artifactId>
            <version>4.0</version>
            <type>war</type>
        </dependency>
        <dependency>
            <groupId>com.jbstrap</groupId>
            <artifactId>JBStrap</artifactId>
            <version>4.0</version>
            <classifier>classes</classifier>
        </dependency>
 
        <!-- https://mvnrepository.com/artifact/org.hsqldb/hsqldb -->
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.5.1</version>
        </dependency>
    </dependencies>
 
    <build>
        <finalName>Application</finalName>
        <resources>
            <resource>
                <directory>src/main/resources/</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/persistence.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <excludes>
                    <exclude>**/*.properties</exclude>
                </excludes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>style</directory>
                <targetPath>${project.build.directory}/style</targetPath>
            </resource>
        </resources>
        <sourceDirectory>src/main/java</sourceDirectory>
        <plugins>
            <!-- Compile project -->
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <release>17</release>
                </configuration>
            </plugin>
            <!-- JBStrap style compiler -->
            <plugin>
                <groupId>com.jbstrap</groupId>
                <artifactId>style-compiler</artifactId>
                <version>1.0</version>
                <configuration>
                    <themeName>default</themeName>
                </configuration>
                <executions>
                    <execution>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <!-- comple WAR archive -->
            <plugin>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.3.2</version>
                <configuration>
                    <warSourceDirectory>WebContent</warSourceDirectory>
                    <failOnMissingWebXml>false</failOnMissingWebXml>
                    <webResources>
                        <resource>
                            <directory>WebContent</directory>
                        </resource>
                    </webResources>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>
        

As a result, the HSQL JDBC driver is included in the project as a dependency and is automatically downloaded by Maven. The next step is to specify the parameters for the database access in the persistence.xml . You can find this file as a project resource in the META-INF folder. Modify the file so that it contains the following:

Delete the non-jta-data-source tag
Place the following properties within the properties tag:
- <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbc.JDBCDriver" />
- <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:file:${basedir}/db/sbs.db;hsqldb.lock_file=false" />
- <property name="javax.persistence.jdbc.user" value="SA"/>
- <property name="javax.persistence.jdbc.password" value=""/>
- <property name="eclipselink.target-database" value="HSQL"/>

         <?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
     
    <persistence-unit name="ApplicationPU" transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
         
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <shared-cache-mode>NONE</shared-cache-mode>
         
        <properties>
            <property name="show-sql" value="true" />
            <property name="eclipselink.weaving" value="false" />
            <property name="eclipselink.logging.level" value="FINE" />
            <property name="eclipselink.logging.parameters" value="true" />
            <property name="eclipselink.orm.throw.exceptions" value="true" />
            <property name="eclipselink.logging.logger" value="DefaultLogger" />
            <property name="eclipselink.logging.level.sql" value="FINEST" />
            <property name="eclipselink.refresh.cascade" value="NoCascading" />
            <property name="eclipselink.jdbc.cache-statements" value="false" />
            <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbc.JDBCDriver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:file:${basedir}/db/omdb.db;hsqldb.lock_file=false" /> 
            <property name="javax.persistence.jdbc.user" value="SA"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="eclipselink.target-database" value="HSQL"/>
        </properties>
    </persistence-unit>
     
</persistence>
        

After this modification, your database is all set up and now you're ready to use it.