Module 2.2

The Data has Value -- Let's store it!

The web applications that we built in the previous part of this course handled and stored data within the application. This means that when the web server or the application is restarted, the data is lost.

Almost every web application has the need for storing and retrieving data. The data can be stored into the file system as files by the application, or the responsibility of storing the data can be given out to a separate application such as a database management system. Database management systems have been built with the explicit purpose of storing and retrieving data in a robust and efficient manner, and they can reside both on the same server as the web application or somewhere elsewhere on the internet.

Although there are vast differences between the term database management system and database, we will use the term 'database' to cover these both. Similarly, although there are many types of database systems, we will mostly focus on relational databases.

Java Database Connectivity API

We will use the H2 Database Engine for getting started with databases. H2 Database Engine can be added to a Maven project by adding the following dependency to the pom.xml file.

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.193</version>
</dependency>

The previously added dependency provides H2-specific support for interacting with the database.

A program that uses a database needs to (1) create a database connection, (2) execute a query to the database, (3) do something with the query results, and (4) close the connection. When using Java and JDBC, a program that does the previously mentioned steps could be as follows — we assume, that there exists a database table called "Book" with the columns "id" and "name".

// Open connection
Connection connection = DriverManager.getConnection("jdbc:h2:file:./database", "sa", "");

// Execute query and retrieve the query results
ResultSet resultSet = connection.createStatement().executeQuery("SELECT * FROM Book");

// Do something with the results -- here, we print the books
while (resultSet.next()) {
    String id = resultSet.getString("id");
    String name = resultSet.getString("name");

    System.out.println(id + "\t" + name);
}

// Close the connection
resultSet.close();
connection.close();

Perhaps the most important part here is the class ResultSet, which provides an access to the query results. The method next moves to the next row in the result table, and the method getString("column name") retrieves the value for column "column name" for that row as a String.

The data in a database is typically organized so that it represents the problem domain and follows a specific structure. This structure, i.e. schema, defines the database table names, the columns in each table, and the datatypes for each column. In addition to the schema, a database contains data.

H2 Database Engine provides support for loading schemas and data using the RunScript class. In the example below, the content of database-schema.sql and database-import.sql is inserted to the database after the database connection has been made.

// Open connection to database
Connection connection = DriverManager.getConnection("jdbc:h2:file:./database", "sa", "");

try {
    // If database has not yet been created, create it
    RunScript.execute(connection, new FileReader("database-schema.sql"));
    RunScript.execute(connection, new FileReader("database-import.sql"));
} catch (Throwable t) {
    System.out.println(t.getMessage());
}
// ...
Loading
Loading

Objects and Databases

When working with classes, objects and databases, the need for transforming database query results into objects arises (see Object-relational mapping, ORM). As this is a very typical task, an abundance of tools have been created for the task.

ORM tools offer — among other things — the functionality needed to transform existing classes into database schemas and to build queries using objects. This has created a situation where large parts of the typical database interaction is no longer implemented by the developer, but by a framework. The developer effectively only needs to implement the database interaction in those parts, where the existing approaches are not sufficient.

One standard for ORM technique in Java is the Java Persistence Api (JPA), which has been implemented by a set of frameworks. When using JPA and an implementation of the standard such as Hibernate, developing basic database queries becomes quite straightforward.

Classes and Entities

The JPA standard states that each class that represents a database table should be defined as an entity; this can be done with the annotation @Entity. Moreover, each class that represents a table should have an identifier that can be used to identify a specific instance of that class. Such an identifier is typically an object variable which is annotated using the @Id annotation. Finally, the class should implement the Serializable-interface.

For example, the following class that represents a person would be transformed into a database table on the fly, and instances of it could be stored to that database table.

// package

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Person implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;

    // getters and setters
}

If the programmer wishes to, the column names and the database table name can be included using the annotations @Column and @Table.

// package

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "Person")
public class Person implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;
    @Column(name = "name")
    private String name;

    // getters and setters
}

The above configuration defines a database table called "Person" that has the columns "id" and "name". The column types are inferred from the variable types (but can be also defined through the @Column annotation).

The above examples follow the JPA specification. The Spring project called Spring Data JPA provides a superclass AbstractPersistable that can be inherited. It provides functionality that makes the previous definitions a bit more straightforward.

// package

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

@Entity
@Table(name = "Person")
public class Person extends AbstractPersistable<Long> {

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

    // getters and setters
}

Now, creating the queries that alter the data in table "Person" is rather straightforward. We need to implement an interface that extends the interface JpaRepository. This provides us all the basic functionality needed for altering the database contents.

// package

import org.springframework.data.jpa.repository.JpaRepository;

public interface PersonRepository extends JpaRepository<Person, Long> {
}

Note that we only created an interface, but not the actual implementation. The Spring framework takes care of the rest for us, given that we tell it to autowire — i.e. include — the implementation of the interface to our application. This is done using an annotation called @Autowired.

The database functionality can be included to a controller as follows:

// package and imports

@Controller
public class PersonController {

    @Autowired
    private PersonRepository personRepository;

    // when a request is made to the address "/persons"
    @RequestMapping("/persons")
    public String listAll(Model model) {

        // find all persons from the database and add them to the model
        model.addAttribute("persons", personRepository.findAll());

        // then create a view from a file called "persons.html" and
        // send it as a response to the request
        return "persons";
    }

    // etc ...
}
Loading

Database Transactions

Transactions are used to verify that all the database operations in a group are executed, or that none of them are. Database management systems offer support for implementing transactions, but, as we often work outside the database, additional steps are needed.

Spring provides transaction support on both class- and method-level through the @Transactional annotation. If a method has been annotated using the @Transactional annotation, then all the database functionality within that method will be performed within a single transaction. If the annotation is on the class level (i.e. before the class definition), then all the methods in that class are transactional.

Perhaps the most classic transaction example is shown below. If the execution of the method fails (e.g. an exception is thrown) after money has been withdrawn from one account and the money has not yet been added to another, then the original withdraw will be also canceled. Without the annotation @Transactional, the money would disappear.

@Transactional
public void bankTransfer(Long fromAccount, Long toAccount, Integer amount) {
    Account from = accountRepository.findOne(fromAccount);
    Account to = accountRepository.findOne(toAccount);

    from.setBalance(from.getBalance() - amount);
    to.setBalance(to.getBalance() + amount);
}

The annotation @Transactional also indicates that the entities are managed within the method. That is, the entities that have been loaded from the database are tracked, and the changes that are made to them are written to the database at the end of the method.

If the method would not have been annotated with the @Transactional annotation, the accounts would have to be separately saved if we want to commit the changes to the database.

@Transactional
public void bankTransfer(Long fromAccount, Long toAccount, Integer amount) {
    Account from = accountRepository.findOne(fromAccount);
    Account to = accountRepository.findOne(toAccount);

    from.setBalance(from.getBalance() - amount);
    to.setBalance(to.getBalance() + amount);
}
Loading

Handling object relationships

When working with databases, information in one table can refer to information in another table. A customer — for example — can have multiple orders, and each order points to a specific customer. In Java, the references for such a case would be written as follows.

// package and imports

public class Customer {
    // variables

    private List<Order> orders = new ArrayList<>();

    // getters and setters
}
// package and imports

public class Order {
    // variables

    private Customer customer;

    // getters and setters
}

When working with JPA and databases, the programmer needs to define the relationships with annotations. These relationship types are @OneToMany, @ManyToOne and @ManyToMany. The above classes would be transformed into the following entities.

// package and imports

@Entity
public class Customer extends AbstractPersistable<Long> {
    // variables

    // the field customer in Order points here
    @OneToMany(mappedBy = "customer")
    private List<Order> orders = new ArrayList<>();

    // getters and setters
}
// package and imports

@Entity
public class Order extends AbstractPersistable<Long> {
    // variables

    @ManyToOne
    private Customer customer;

    // getters and setters
}
Loading
:
Loading interface...
:
Loading interface...

Login to view the exercise

You have reached the end of this section! Continue to the next section:

Remember to check your points from the ball on the bottom-right corner of the material!