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. As in previous labs you should keep your Bitbucket issue tracker up to date.

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.

Gradle tasks:

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

Demo application data model:

Homework (10p)

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

Use Hibernate ORM to store POS data objects to the database. Hibernate is a library for mapping Java objects to SQL (Object-Relational Mapping).
Use HSQLDB database for storing the data. This is a very tiny SQL database that doesn't need to be installed separately (gradle can download and start it for you, see the demo application above).
Extend gradle build script by adding tasks:
- gradlew hsql - starts the HSQLDB database server
Add db support in classes of the package ee.ut.math.tvt.salessystem.dataobjects:
- 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
Add a new implementation of SalesSystemDAO:
- Add the class HibernateSalesSystemDAO (keep your existing InMemorySalesSystemDAO)
- Make SalesSystemUI and ConsoleUI use HibernateSalesSystemDAO instead of InMemorySalesSystemDAO

Adding Relational Database Support

Save the following file as persistence.xml under root project's src / main / resources / META-INF /

    <persistence 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"
             version="2.1">

        <persistence-unit name="pos">
            <!-- list all the classes that have @Entity annotation -->
            <class>ee.ut.math.tvt.salessystem.dataobjects.SoldItem</class>
            <class>ee.ut.math.tvt.salessystem.dataobjects.StockItem</class>

            <properties>
                <!-- configure hibernate to use the HSQL database running on the local machine -->
                <!-- make sure the HSQL database is started before starting the application -->
                <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"/>
                <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:hsql://localhost/pos"/>

                <!-- configure hibernate to print out all the SQL commands that it is executing -->
                <property name="hibernate.show_sql" value="true"/>

                <!-- drop-and-create tells hibernate to create database tables based on the @Entity classes -->
                <!-- the tables are created at application startup. all existing tables and data are deleted -->
                <!-- feel free to set it to 'none' to keep existing tables and data -->
                <!-- https://docs.oracle.com/javaee/7/tutorial/persistence-intro005.htm -->
                <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
            </properties>
        </persistence-unit>
    </persistence>

This file lists the database entities (currently SoldItem and StockItem), sets which database to use and what to do with the database on program startup. Currently the database is dropped and recreated. Later if you have added annotations and you want the data to persist after closing the application you have to change drop-and-create into none or create. Bear in mind that if you deliver your application using none, it might create problems if it has not been run with another setting beforehand. In that case, using create is more appropriate since it will create the tables in the database only if they do not exist.

Until now the POS application used InMemorySalesSystemDAO. This was an in-memory implementation of the data access object. Now we wish to save data in the database. Therefore we need a new data access object that works with the database. Add the skeleton for the new implementation of the SalesSystemDAO to the project.

    package ee.ut.math.tvt.salessystem.dao;

    import javax.persistence.EntityManager;
    import javax.persistence.EntityManagerFactory;
    import javax.persistence.Persistence;

    public class HibernateSalesSystemDAO implements SalesSystemDAO {

        private final EntityManagerFactory emf;
        private final EntityManager em;

        public HibernateSalesSystemDAO() {
            // if you get ConnectException/JDBCConnectionException then you
            // probably forgot to start the database before starting the application
            emf = Persistence.createEntityManagerFactory("pos");
            em = emf.createEntityManager();
        }

        // TODO implement missing methods

        public void close() {
            em.close();
            emf.close();
        }

        @Override
        public void beginTransaction() {
            em.getTransaction().begin();
        }

        @Override
        public void rollbackTransaction() {
            em.getTransaction().rollback();
        }

        @Override
        public void commitTransaction() {
            em.getTransaction().commit();
        }
    }

The implementation is not complete so you have to implement the missing methods. 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. If you look into the provided HibernateSalesSystemDAO class you see that it already knows how to begin, commit and close transactions. You now have to tell it how to find and persist objects.

You also need to make ConsoleUI and SalesSystemUI use the new DAO instead of the in-memory one.

In build.gradle file in the root folder add:

   repositories {
      jcenter()
   }

   dependencies {
      compile('org.hsqldb:hsqldb:2.3.4')
      compile('org.hibernate:hibernate-entitymanager:5.2.1.Final')
      compile('org.hibernate:hibernate-java8:5.2.1.Final')
      implementation "javax.xml.bind:jaxb-api:2.2.11"
   }

   task hsql(type:JavaExec){
      main = "org.hsqldb.Server"
      classpath = sourceSets.main.runtimeClasspath
      args = ["-database.0", "${projectDir}/data/pos", "-dbname.0", "pos"]
   }

Now you should be able to run the start the database with gradlew hsql.

After starting the application for the first time with Hibernate configured, the data/POSdb.script file should contain SQL statements for the following tables:

STOCKITEM
SOLDITEM

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. Annotate the relevant classes under ee/ut/math/tvt/salessystem/dataobjects.
Make ConsoleUI and SalesSystemUI use the new DAO class.

Now you can load and manipulate the data.

In short: Your application should fully support the database - load, manipulate and save the data using the database. Loading last 10 sales and sales between the dates should be done using Hibernate's JPQL queries.

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 something did not work and you were penalized for it during the previous lab evaluation you should fix it for this lab evaluation or you could be penalized again for the code design.

Your results must be submitted using your Bitbucket repository and the Bitbucket wiki. Create a new tag named "homework-5" and add a link to the labs wiki page.

NOTE: In the assessment/consultation lab you must be able to show partial solutions.

Tarkvaratehnika 2019/20 sügis

Lab 5 - Development, Phase II

Demo Application

Homework (10p)

Adding Relational Database Support

Submission of the Results

Links