Programming Laboratory

ORM

ORM is a short name for "object-relational mapping". Therefore, it is about exchanging data in a tabular form (relation in a database) to objects or in the other direction. It is a modern approach to the problem of cooperation with a database, using the object oriented programming philosophy.

Dependencies

To use Hibernate we will need a hibernate-core package and a database driver.

<!-- Hibernate resources -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>5.2.1.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.2.0.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>5.2.0.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate.common</groupId>
    <artifactId>hibernate-commons-annotations</artifactId>
    <version>4.0.2.Final</version>
    <classifier>tests</classifier>
</dependency>
<dependency>
    <groupId>org.hibernate.javax.persistence</groupId>
    <artifactId>hibernate-jpa-2.0-api</artifactId>
    <version>1.0.1.Final</version>
</dependency>
<dependency>
    <groupId>com.sun.xml.bind</groupId>
    <artifactId>jaxb-impl</artifactId>
    <version>2.3.0</version>
</dependency>

<!-- Database driver-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.1.1</version>
</dependency>

In our project, we will use the HSQL (in-memory) database, but you should try to run PostgreSQL verson at home.

Excercise 1

Add dependencies to the pom.xml file.

Hibernate configuration

We save the Hibernate configurations in the file named hibernate.cfg.xml, hibernate.properties or persistence.xml.

Attention! Depending on what elements we use, the library will automatically search for specific files in the project path to find the configuration.

We must specify:

access details of connection to the database, address, password, user, location,

the language of the sql queries we want to use,

possibly a list of classes mapped to database tables

dialect specifies how hibernate is going to communicate with the database

show_sql - true will show us SQL queries created by hibernate (mainly for debugging).

hbm2dll specifies the behavior of the database schema create means that we create the schema from scratch every time. Once we create it, I recommend changing the value to validate or update - but the update is not recommended because it can cause strange errors like "table not found".

Sample database access settings for the PostgreSQL database:

<?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="hibernate-dynamic" transaction-type="RESOURCE_LOCAL">
        <properties>
            <!-- Configuring JDBC properties -->
            <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/postgres"/>
            <property name="javax.persistence.jdbc.user" value="postgres"/>
            <property name="javax.persistence.jdbc.password" value="postgres"/>
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />

            <!-- Hibernate properties -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
            <property name="hibernate.hbm2ddl.auto" value="create"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.show_sql" value="true"/>

        </properties>
    </persistence-unit>
</persistence>

Note! In order to run the program with the above settings at home you should have PostgreSQL server installed or change the server address in the settings. You should easily find information about PostgreSQL installation on the internet. To edit data in the postgresql database, I recommend using program called pgAdmin.

Configuration using the university PostgreSQL database:

<?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="hibernate-dynamic" transaction-type="RESOURCE_LOCAL">
        <properties>
            <!-- Configuring JDBC properties -->
            <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://psql.wmi.amu.edu.pl:5432/mw?ssl=true&amp;sslfactory=org.postgresql.ssl.NonValidatingFactory"/>
            <property name="javax.persistence.jdbc.user" value="mw"/>
            <property name="javax.persistence.jdbc.password" value="riustsdardswify"/>
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />

            <!-- Hibernate properties -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
            <property name="hibernate.hbm2ddl.auto" value="create"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.show_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

During the classes we will use the HSQL database run in the computer's memory:

<?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="hibernate-dynamic" transaction-type="RESOURCE_LOCAL">
        <properties>
            <!-- Configuring JDBC properties -->
            <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:PRA"/>
            <property name="javax.persistence.jdbc.user" value="sa"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver" />

            <!-- Hibernate properties -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
            <property name="hibernate.hbm2ddl.auto" value="create"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.show_sql" value="true"/>

        </properties>
    </persistence-unit>
</persistence>

Exercise 2

Add to the directory resources subdirectory META-INF and in it file persistence.xml paste the above content into it.

Mapping - annotations

The mapping between the database tables and the class is carried out using the annotation mechanism.

@Entity annotation used to specify that the given class should be mapped to the database entity.

@Table specifies the table properties (e.g. name, definition of restrictions, etc.), if this annotation is not the table name will be the name of the class

@Id denote the main key of the table

@Column specifies the properties of the table column (eg, the name, whether it must be unique or empty), if it is not defined, the column will have the same name as the class field.

@GeneratedValue means that the value of this field will be automatically generated

Example:

package hibernate.model;

import javax.persistence.*;

@Entity
@Table(name = "EMPLOYEE", uniqueConstraints = {
        @UniqueConstraint(columnNames = {"first_name","last_name"})})
public class Employee {

    @Id @GeneratedValue
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "salary")
    private int salary;

    @Column(name = "PESEL", nullable = false, unique = true)
    private int pesel;

    public Employee() {}

    public int getId() {
        return id;
    }

    public void setId( int id ) {
            this.id = id;
    }

    public String getFirstName() {
         return firstName;
    }

    public void setFirstName( String first_name ) {
        this.firstName = first_name;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName( String last_name ) {
        this.lastName = last_name;
    }

    public int getSalary() {
        return salary;
    }

        public void setSalary( int salary ) {
        this.salary = salary;
    }

    public int getPesel() {
        return pesel;
    }

    public void setPesel(int pesel) {
        this.pesel = pesel;
    }
}

Exercise 3

Add the hibernate package and package model in it. Create the above class.

Entity states

Apart from object-relational mapping itself, one of the problems that Hibernate was intended to solve is the problem of managing entities during runtime. The notion of “persistence context” is Hibernate’s solution to this problem. Persistence context can be thought of as a container or a first-level cache for all the objects that you loaded or saved to a database during a session.

Any entity instance in your application appears in one of the three main states in relation to the Session persistence context:

* transient — this instance is not, and never was, attached to a Session; this instance has no corresponding rows in the database; it’s usually just a new object that you have created to save to the database;

* persistent (managed) — this instance is associated with a unique Session object; upon flushing the Session to the database, this entity is guaranteed to have a corresponding consistent record in the database;

* detached — this instance was once attached to a Session (in a persistent state), but now it’s not; an instance enters this state if you evict it from the context, clear or close the Session, or put the instance through serialization/deserialization process.

It is important to understand from the beginning that all of the methods (persist, save, update, merge, saveOrUpdate) do not immediately result in the corresponding SQL UPDATE or INSERT statements. The actual saving of data to the database occurs on committing the transaction or flushing the Session.

EntityManager vs SessionManager

In Hibernate, the persistence context is represented by org.hibernate.Session instance. For JPA, it is the javax.persistence.EntityManager. When we use Hibernate as a JPA provider and operate via EntityManager interface, the implementation of this interface basically wraps the underlying Session object.

We will be using Entity Manager in our examples.

If during the work with the code you need the Session object you can get it using:

Session session = entityManager.unwrap(Session.class);

Working with Entity Managerem

entityManager.find(Employee.class, 1L);

Employee emp = new Employee();
entityManager.persist(emp);

Employee emp = new Employee();
entityManager.merge(emp);

entityManager.remove(emp);

Example

A class that writes and reads an element from a database:

package hibernate;

import hibernate.model.Employee;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import java.util.List;


class Manager {

    public static void main(String[] args) {

            System.out.println("Start");

            EntityManager entityManager = null;

            EntityManagerFactory entityManagerFactory = null;

        try {
            //taka nazwa jak w persistence.xml
            entityManagerFactory = Persistence.createEntityManagerFactory("hibernate-dynamic");
            //utworz entityManagera
            entityManager = entityManagerFactory.createEntityManager();

            //rozpocznij transakcje
            entityManager.getTransaction().begin();

            Employee emp = new Employee();
            emp.setFirstName("Jan");
            emp.setLastName("Polak");
            emp.setSalary(100);
            emp.setPesel(100);

            entityManager.persist(emp);

            Employee employee = entityManager.find(Employee.class, emp.getId());

            entityManager.remove(emp);

            System.out.println("Employee " + employee.getId() + " " + employee.getFirstName() +     employee.getLastName());

            //zakoncz transakcje
            entityManager.getTransaction().commit();

            System.out.println("Done");

            entityManager.close();

        } catch (Throwable ex) {
            System.err.println("Initial SessionFactory creation failed." + ex);
        } finally {
            entityManagerFactory.close();
        }

    }

}

User-defined inquiries

To execute the query, we must first create it using the entityManager.createQuery method and then retrieve the result. To download a list of employees we need to do the following code:

Query query = entityManager.createQuery("SELECT k FROM Employee k");
List<Employee> employees = query.getResultList();

If our query returns only one element, we can use the query.getSingleResult() method.

Attention! If the query to the database does not return results (the result is empty), then:

the getResultList() method will return null!

the getSingleResult() method throws an exception (NoResultException)! (if we have more than one result, we also get an exception - NonUniqueResultException)

The objects returned in the query are persisted that is, all changes made to them will be automatically saved to the database (if they are included in the transaction - objects outside the transaction will not be saved).

Exercise 4

Add the getThemAll() method to the class that retrieves all employees.

TypedQuery<Employee> q2 =
     em.createQuery("SELECT k FROM Employee k", Employee.class);

TypedQuery<Employee> query = entityManager.createQuery(
                "SELECT c FROM Employee c WHERE c.lastName LIKE :name", Employee.class);
return query.setParameter("name", name).getResultList();

Execution

log4j.logger.org.hibernate=info

OneToOne dependencies

There are dependencies such as 1-1, 1-, *- between the database tables.

Goal: Define the relationship between objects - let one object be the field of the other.

Sometimes our model requires that in addition to simple types, the given entity should contain a reference to another entity. For example, we want to connect an employee with his address.

Exercise 7

Define the address class having the city, street, nr, houseNumber, postcode fields. So that all except houseNumber could not be empty and the code had at most 5 characters (all fields should be of the String type). Add the autogenerated master key.

@Entity
@Table(name = "ADDRESSES")
public class Address {

    @Id
    @GeneratedValue(generator = "gen")
    @SequenceGenerator(name = "gen", sequenceName = "author_seq")
    @Column(name = "id")
    private int id;

    @Column(nullable = false)
    String street;

    @Column(nullable = false)
    String city;

    @Column(length = 5, nullable = false)
    String nr;

    @Column(length = 5)
    String housenr;

    @Column(length = 5, nullable = false)
    String postcode;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getStreet() {
        return street;
    }

    public void setStreet(String street) {
        this.street = street;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getNr() {
        return nr;
    }

    public void setNr(String nr) {
        this.nr = nr;
    }

    public String getHousenr() {
        return housenr;
    }

    public void setHousenr(String housenr) {
        this.housenr = housenr;
    }

    public String getPostcode() {
        return postcode;
    }

    public void setPostcode(String postcode) {
        this.postcode = postcode;
    }
}

Add a field in the Employee class, and define in the Manager address for the employee Jan.

@OneToOne(cascade = CascadeType.PERSIST)
@JoinColumn(name="Address_ID", referencedColumnName = "id")
Address address;

Join column defines the name of our column and we can define the name of the column with which our element connects (this column must be unique). By default, we connect to the table's primary key.

In OneToOne we can define additional options, optional define whether the field is required, CASCADE define whether changes in the person's address field will propagate to the Address table (available options MERGE, PERSIST, REFRESH, REMOVE, ALL).

Examples:

http://www.coderpanda.com/jpa-one-to-one-mapping/

http://www.logicbig.com/tutorials/java-ee-tutorial/jpa/one-to-one/

Remember to add the Address to the main code to the employee and assign it to him. Attention! If we want id generated for addresses to be different than those for employees then between @Id and @Column we can enter:

@GeneratedValue(generator = "gen")
@SequenceGenerator(name="gen", sequenceName = "author_seq")

If we need a back-to-back mapping between entities, we must use the @MappedBy annotation.

Exercise 8

See what happens if you do not define a value for one of the mandatory fields.

What happens when an object in a persisted state performs:

emp.getAddress().setStreet(null);

OneToMany dependencies

In case when we want both classes to have a link to each other (eg. a child to a parent and a parent to a child), in one class we define @ManyToOne annotations and in the other we refer to this mapping through the "mappedBy" parameter).

@Entity
public class Parent {
// ...

    @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY)
    private Set<Child> children = new HashSet<Child>();

// ...
}

@Entity
public class Child {
    // ...

    @ManyToOne(fetch = FetchType.LAZY)
    private Parent parent;

    // ...
}

FetchType determines whether in the case of downloading one of the tables we have to poll the database about the object connected to it, if we choose FetchType.EAGER if not and the query is to be performed only when we refer to a specific value, select FetchType.LAZY.

ManyToMany's dependencies

Goal: Let one object have a connection not to one and to many objects at once (in both directions - that is, the manager has many subordinates but subordinates may also have many managers).

Exercise 9

Add a list of employees to subordinates to each employee (it may be empty).

Add in Employee:

@ManyToMany(mappedBy = "subworkers", cascade = CascadeType.ALL)
private List<Employee> managers = new ArrayList<Employee>();

@ManyToMany(cascade = CascadeType.ALL)
private List<Employee>  subworkers = new ArrayList<>();

Note that the list is an object, so for it to be not null we need to initialize it. See in the database what dependencies were created (a new base should be created connecting the two tables - in our case connecting the employee with the employee).

We can also define how the table connecting two tables should look using the @JoinTable annotation

@JoinTable(name="joinTwoTables",
            joinColumns={@JoinColumn(name="id")},
            inverseJoinColumns={@JoinColumn(name="id")})

Pagination

Purpose: Define the query that return paged data.

It often happens that having many records we do not want to download all of the database every time but we would like to divide them into smaller parts (pages).

The simplest paging software method is the query query configuration

Query query = entityManager.createQuery("From EMPLOYEE");
int pageNumber = 1;
int pageSize = 10;
query.setFirstResult((pageNumber-1) * pageSize);
query.setMaxResults(pageSize);
List <Foo> empList = query.getResultList();

We use the methods:

setFirstResult(int): sets the position from which we want to download data

setMaxResults(int): sets the number of records we want to retrieve from the database

Knowledge about the number of pages to which data is shared is also useful

Query queryTotal = entityManager.createQuery ("Select count(e.id) from EMPLOYEE e");
long countResult = (long)queryTotal.getSingleResult();
int pageSize = 10;
int pageNumber = (int) ((countResult / pageSize) + 1);

Exercise 10

Add a query to the code that downloads all employees broken down into pages.

public List<Employee> getAllEmployeeByPage(int pagenr) {
    //calculate total number
    Query queryTotal = entityManager.createQuery
            ("Select count(e) from Employee e");
    long countResult = (long)queryTotal.getSingleResult();

    //create query
    Query query = entityManager.createQuery("Select e FROM Employee e");
    //set pageSize
    int pageSize = 10;
    //calculate number of pages
    int pageNumber = (int) ((countResult / pageSize) + 1);

    if (pagenr > pageNumber) pagenr = pageNumber;
    query.setFirstResult((pagenr-1) * pageSize);
    query.setMaxResults(pageSize);

    return query.getResultList();
}

Create multiple employees using

for (int i = 1; i < 100; i++) {
    entityManager.persist(Employee.copyEmployee(emp));
}

Call the code in the main class.

PROJECT

Project in the field of JSON + XML + HIBERNATE

Create a program that will save and read a specific data model.

Each person has to think about what type of data he wants to store in the final application (information about people, movies, cars, footballers, computers, etc.). You should model this data by defining the tables, fields and the relationships between them.

The data must contain at least 5 tables, two OneToOne relationships and two OneToMany (or ManyToMany) relationships.

Exemplary data requests should be created for the data (preferably useful in the target project). Requests must be at least 5, each query should be a separate method in the code. One of the queries should be paginated.

Please use the date type field in the project - preferably ZonedDateTime or JodaTime (as this may cause a problem), if you do not use it the maximal number of points you would get from project is 85%.

The target design should read and write modeled data from and into XML and JSON type files (both types!) And write / read them from the database. It must also use a database not stored in memory, eg. PostgreQSL, MyQSL, ORACLE etc.

You do not need to create any menu for the project, execution can be done by defining the appropriate tests method (in test classes!) For scenarios: reading data from XML / JSON file and writing to the database, reading data from the database and writing to XML / JSON files. You must create the appropriate XML / JSON files for the above mentioned tests.

Attention! read and write should execute / fail in case of providing partial / incorrect data (some empty fields).

I suggest you add the code to your repository in branch master.

Deadline for completion: 13.XII.2018 after this date, the maximum number of points to be obtained for the project is 50%.

Extra

For IntelliJ Ultimate users

https://www.youtube.com/watch?v=bGl4u44WRiI

*

References used to create this materials:

https://kobietydokodu.pl/13-2-baza-danych-z-jpa-cz-2/

http://math.uni.lodz.pl/~kowalcr/Bazy/Temat8.pdf

https://docs.jboss.org/hibernate/orm/3.6/quickstart/en-US/html/index.html

http://www.baeldung.com/hibernate-save-persist-update-merge-saveorupdate

http://www.mkyong.com/hibernate/hibernate-one-to-one-relationship-example-annotation/

https://www.javaworld.com/article/2077819/java-se/understanding-jpa-part-2-relationships-the-jpa-way.html

http://www.baeldung.com/jpa-pagination