Class architecture

Runner

starts the program - contains main()

examine the start() method

sets up Jetty server on port 9000

maps urls .../index.html and .../add to (a) PersonServlet (object)

PersonServlet

provides the (http) responses to requests to the above urls

for 2 types of request:

http get request (responded to via doGet())

http post request (responded to via doPost())

Person (in package .../model)

a typical POJO (a plain old Java object)

models the domain object "person"

described by first name, last name and email address

H2Person (in .../db)

data access object, an object that implements the database operation for a particular domain object, such as adding a person to the database or retrieving a person from a database

MustacheRenderer (in .../util/mustache)

compiles the specified template (first argument) with the data from the specified object (second argument)

index.mustache (in resources/templates)

the template that is rendered

This page has static components and a small dynamic component.

Activity: Which contents are generated dynamically?

Where does the database come from?

The database is H2

There is an H2 dependency declared in the pom.xml file

That loads the H2 jar file when the program starts

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

Accessing the Data Store

Principle: to separate the domain logic (the classes that represent the objects in our application) from the objects needed to access the data store for our objects (here: the database).

dbdemo has one domain object: Person.

The H2Person class implements the operations on the database:

adding a person (inserting a new row into the person table)

finding all persons (retrieving all rows in the

The method getConnection() sets up a connection to the database.

The driver is specified in the H2 manual (see below)

Here the database runs in memory, see in-memory URL (MEMORY) is used

The constructor sets up the connection and the tables (see file person.sql).

Where do the tables come from?

The SQL script that sets up the table is in person.sql

It is run when the H2Person object is constructed.

CREATE TABLE IF NOT EXISTS person (
  id int AUTO_INCREMENT PRIMARY KEY,
  first VARCHAR(255),
  last VARCHAR(255),
  email VARCHAR(255)
);

Connecting to the database

static Connection getConnection(String db) throws SQLException, ClassNotFoundException {
   Class.forName("org.h2.Driver");
   return DriverManager.getConnection(db, user: "sa", password: "");
}

The getConnection() method returns a connection to the database

This method requires a database URL, here the constant MEMORY

Class.forName requires an object of type java.sql.Driver, see documentation for the relevant database for the correct driver

DriverManager.getConnection uses the default login for H2

The Prepared Statement

public void addPerson(Person person) {
    final String ADD_PERSON_QUERY = "INSERT INTO person (first, last, email) VALUES (?, ?, ?)";
    try (PreparedStatement ps = connection.PreparedStatement(ADD_PERSON_QUERY)) {
        ps.SetString( parameterIndex: 1, person.getFirst());
        ps.setString( parameterIndex: 2, person.getlast());
        ps.setString( parameterIndex: 3, person.getEmail());
        ps.execute();
    } catch (SQLException e) {
        throw new RunTimeException(e);
    }
}

Create the SQL statement with "?" instead of input parameters, i.e.

Create the SQL query with the input parameters

Then create the PreparedStatment with that query String

and set the replacement values by using getter and setter methods of the domain object, e.g. Person person.getFirst(),

Close the Connection

Once a connection to the database has been set up but is no longer needed you need to release any resources held by this connection.

Otherwise the database keeps the connection open but continues to acquire new connections, most of which will be un-used.

This will slow the database down.

If single threaded, no other process can connect.

If an connection exists, call the close() method.

Set the connection object to null.

Handling Requests

There are 2 types of request:

http GET via doGet()

http POST via doPost()

doGet()

Obtain a list of Person objects by querying the database, see findPersons() method in the H2Person class

Call the MustacheRenderer with the template index.mustache (in resources/templates) and inner class Result as object providing the data (an int indicating the size of the persons list).

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    List<Person> persons = h2Person.findPersons();
    String html = mustache.render( templateName: "index.mustache", new Result(persons,size()));
    response.setContentType("text/html");
    response.setStatus(200);
    response.getOutputStream().write(html.getBytes(Charset.forName("utf-8")));
}

doPost()

Instantiate a Person object with the values taken from the request parameters.

Call the addPerson() method of the H2Person class, providing the new Person object as argument

Create another GET request to index.html -> see previous slide for the handling of the GET request

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String first = request.getParameter( s: "first");
    String last = request.getParameter( s: "last");
    String email = request.getParameter( s: "email");
    Person person = new Person(first, last, email);
    h2Person.addPerson(person);
    response.sendRedirect( s: "/index.html");
}