Lab 5 - Development, Phase II

Now your team has developed the base functionality for your POS solution. Users already can add real products to the warehouse, see the order history, accept orders and payments. However, after closing the application it does not save the data.

During this lab you will add a database support to your application and continue to extend the functionality.

Demo Application

Lets start from a small training with the following demo application: hibernateDemo. This task is not for submission. However, we strongly recommend to complete this task before you start working on your homework. You need to extend the given demo application by adding the correct JPA annotations instead of the @Transient. You need to select the JPA annotations that will describe the mapping between Java classes and the database tables using the data model below. If your solution will be correct then your application will print this text to the console. You should not change anything else except the JPA annotations.

ANT targets:

ant startdb - starting the database (should be done before the application start)
ant run - starting the application. Should print the database data to the console.

Demo application data model:

Homework

You need to extend your previous homework solution. Here is an outline of the required POS application modifications:

Add the HSQLDB support. This is a relational database, more info is available at: http://hsqldb.org/
Add the Hibernate. Its an object-relational mapping library, more info at: http://www.hibernate.org/
Extending the ANT build script by adding targets:
- ant startdb - starts the HSQLDB database server
- ant dbmanager - starts the GUI database client
Add db support in classes of the package ee.ut.math.tvt.salessystem.domain.data:
- Sale/Purchase/... - your application can be developed differently, so the name of this object(s) can be different or be missing.
- SoldItem - Product selected for selling or sold product
- StockItem - the product in the warehouse
Also extend the interface ee.ut.math.tvt.salessystem.domain.controller.impl.SalesDomainController by adding:
- public void endSession();
In addition you need to modify classes:
- ee.ut.math.tvt.salessystem.domain.controller.impl.SalesDomainControllerImpl
- ee.ut.math.tvt.salessystem.ui.ConsoleUI
- ee.ut.math.tvt.salessystem.ui.SalesSystemUI

Task 1. Adding Relational Database Support

You should download additional code from here which includes:

HSQLDB:
- ../lib/hsqldb.jar
Hibernate:
- ../lib/hibernate
XML managing tools:
- Apache Xerces
- XML API
Database script ../data/POSdb.script that creates two tables:
- STOCKITEM
- SOLDITEM

Provided JAR files should be added to the project class path, otherwise Eclipse will not understand that project has a database support.

Extending the build.xml script

Adding new database specific variables

<property name="lib.dir" value="${basedir}/lib"/>

<!-- database variables -->
<property name="data.dir"	value="${basedir}/data"/>
<property name="db.file"	value="${data.dir}/POSdb"/>
<property name="db.alias"	value="POS"/>
<property name="db.url" 	value="jdbc:hsqldb:hsql://localhost/POS"/>
<property name="lib.hibernate.dir"   value="${lib.dir}/hibernate"/>

We also need to extend the classpath, otherwise ANT will not find new Hibernate JAR files.

<path id="run.classpath">
        <pathelement location="${build.classes.dir}"/>
        <!-- configuration -->
        <pathelement location="${conf.dir}"/>
        <fileset dir="${lib.dir}">
            <include name="*.jar"/>
        </fileset>

        <fileset dir="${lib.hibernate.dir}">
             <include name="*.jar"/>
        </fileset>
</path>

Next, extending the JAVA compilation classpath compile.classpath, by adding the location of the JAR files.

<path id="compile.classpath">
        <fileset dir="${lib.dir}">
            <include name="*.jar"/>
        </fileset>

<fileset dir="${lib.hibernate.dir}"> <include name="*.jar"/> </fileset>

</path>

Adding two additional targets dbmanager and startdb:

<target name="dbmanager" description="Start the HSQLDB manager">
        <java
            classname="org.hsqldb.util.DatabaseManagerSwing"
            classpathref="run.classpath"
            fork="yes"
        >
            <arg line="--user sa --url ${db.url}" />
        </java>
    </target>

    <target name="startdb" description="Start the HSQLDB with the default params">
        <java
            classname="org.hsqldb.Server"
            classpathref="run.classpath"
            fork="yes"
        >
            <arg line="-database.0 '${db.file}' -dbname.0 '${db.alias}'" /> 
        </java>
</target>

After the modifications provided above, the database support should be implemented. Try to start the database with the startdb target.

Check if the STOCKITEM and SOLDITEM were created using the dbmanager target.

In order to add the Hibernate support we need to perform two steps:

Add JPA annotations in the code. Usually one class corresponds to one target table.

Modifying Classes

Modify the interface ee.ut.math.tvt.salessystem.domain.controller.impl.SalesDomainController add the following function:

public void endSession();

Modify the class ee.ut.math.tvt.salessystem.domain.controller.impl.SalesDomainControllerImpl add the following function:

public void endSession() {
    HibernateUtil.closeSession();
}

Modify the class ee.ut.math.tvt.salessystem.ui.ConsoleUI. When user is pressing the "q"button, your application should end the database session before the exit (using SalesDomainController#endSession)
Modify the class ee.ut.math.tvt.salessystem.ui.SalesSystemUI. When user is closing the main application window, your application should end the database session before the exit (using SalesDomainController#endSession)
Modify classes in the package ee.ut.math.tvt.salessystem.domain.data add all required JPA annotations.

Now you can load and manipulate the data. However, in order to save the data you need to get familiar with the transaction technique and understand the difference between detached and managed objects. Here are some material with few brief examples. In our case the transaction would be created using the HibernateUtil session.

In short: Your application should fully support the database - load, manipulate and save the data using the database.

Task 2. Example Data

Create the startup database script that will create a database initial state:

Warehouse - add at least 20 different products
Purchase/Sale/... - create at least 5 records in the order history table (history tab)

The database startup script should be executed automatically on HSQLDB start.

Task 3. SQL queries.

Create and deliver the SQL queries that will return:

Query that returns the most popular product name, total quantity, unit price and unit price multiplied by the total quantity sold (most popular one is the one that gave highest income - last column max number)
Query that returns the date with the highest income (should return only one row)

We encourage you to fill the database with different data to perform a more reliable testing of the queries. It would be extremely difficult to guarantee the correctness if you have 1-2 clients or if all sales were created during one day.

Submission of the Results

Note: If you had some problems in the previous labs, you need to fix them during this lab. For example if your ANT did not work and you were penalized for it during the previous lab evaluation you should fix ANT script for this lab evaluation or you could be penalized again for the code design.

Create a new tag with name homework_5 where you commit the final solution, comment it with "Homework 5". As a result your solution on the GitHub should have the link:
- https://github.com/[account name]/[repo name]/tree/homework_5
SQL queries produced in the task 3 should be delivered using the GitHub wiki

Tarkvaratehnika 2013/14 sügis