Assignment:SubstituteTeacherScheduler

From Juneday education
Jump to: navigation, search

This assignment is used in the course Systemintegration-Yrgo-2018 and 2019 but can be used in other courses as well.

The idea behind the assignment, is to create a system for a company providing substitute teachers to various schools. The schedule for the assignments of the teachers is stored in an SQLite3 database. The database is provided by the teachers. (TODO: Link the database). The database is pretty simple and described below.

In order to publish the assignments for the substitute teachers and the administrative personnel, you have been tasked with creating a web API which can be queried for the assignments. The web API should be able to respond to the following queries:

  • All assignments
  • All assignments at given date (YYYY-mm-dd)
  • All assignments for a given teacher (substitute_id - the id of a substitute teacher)
  • All assignments for a given substitute_id at a given date

Furthermore, there's a requirement of the web API to be able to respond with either JSON or XML formatted data.

You will use Java and Servlets to write the web API and org.json for creating the JSON. You may choose to use another API if you prefer, but the code examples will be using this. For the XML creation, suggest that you look at our examples. For the database access, we will use plain JDBC in our examples, but you may use some other framework if you prefer. Please note: We will perhaps not be able to help you if you choose a framework or third party API for database access, JSON, XML or anything else. We encourage you to experiment, but can not promise that you will get equal help if you do so.

Grading

G

For G, you should have a working web API which produces correct JSON and XML and implements the listed query parameter combinations.

You must include instructions for how to build and run.

You cannot rely on any IDE, such as eclipse, intellij, netbeans etc - the code must run and be able to be built and run in a terminal (e.g. running bash).

VG

For VG: you should have a working web API which

  • produces correct JSON and XML implements the listed query parameter combinations
  • responds with the correct content-type header (e.g. application/json)
  • handles incorrect input like missing parameters in a documented way (e.g. responds with some status code indicating failure)
    • Example: for a date or substitute_id which cannot be found in the database, the web API could respond with "404 Not Found"
  • doesn't have any database or JSON or XML code in the servlet class, but rather use some kind of object (preferably accessed via an interface type)
  • is well coded (consistent indentation and coding style)
  • well commented
  • Comes with instructions to the teacher for how to build and run
  • a good and logical directory/package structure (not everything in the same directory)
  • includes some tests, e.g. some examples on how to test the system without a browser, e.g. using curl or wget etc.

You cannot rely on any IDE, such as eclipse, intellij, netbeans etc - the code must run and be able to be built and run in a terminal (e.g. running bash).

GET paramters

The following GET parameters exist in this web API:

  • day - the date of the schedule data - A String with format YYYY-mm-dd
  • substitute_id - The substitute teachers employee number (an integer)
  • format - the format for the response - one of "xml" or "json"

Leaving out day will list all dates of the schedule.

Leaving out substitute_id, will list all schedules for all teachers

Leaving out both day and substitute_id will list all schedules at all dates for all teachers.

JSON output format

The result should be a JSON array (if the result renders no data, it is OK if your web API responds with [], which is an empty JSON array).

Every assignment (schedule point) should be a JSON object with the following key-value pairs:

  • date - string
  • school - a json object:
    • address - string
    • school_name - string
  • substitute - a json object:
    • name - string

For information about the JSON syntax, please see:

Example empty result

[]

Example an array of one item

[
  {
    "date": "2018-01-17 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Rikard"}
  }
]

Example an array of two items

[
  {
    "date": "2018-01-17 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Rikard"}
  },
  {
    "date": "2018-01-17 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Henrik"}
  }
]

Example of one array element (one single assignment - which should occur in the array)

  {
    "date": "2018-01-17 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Rikard"}
  }

Example JSON response

The GET parameters format=json&day=2018-01-17 should, for instance generate the following response:

[
  {
    "date": "2018-01-17 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Rikard"}
  },
  {
    "date": "2018-01-17 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Henrik"}
  },
  {
    "date": "2018-01-17 08:00:00",
    "school": {
      "address": "Grepgatan 9 424 65 Angered",
      "school_name": "Angeredsgymnasiet"
    },
    "substitute": {"name": "Anders"}
  }
]

Example XML output

If the format parameter is provided and has the value xml, then the output from the web api will be XML.

Here's an example XML output:

<?xml version="1.0" encoding="utf-8"?>
<schedules>
  <schedule date="2018-01-17 08:00:00">
    <school>
      <address>Lärdomsgatan 3 402 72 GÖTEBORG</address>
      <school_name>Yrgo</school_name>
    </school>
    <substitute>
       <name>Rikard</name>
    </substitute>
  </schedule>
  <schedule date="2018-01-17 08:00:00">
    <school>
      <address>Lärdomsgatan 3 402 72 GÖTEBORG</address>
      <school_name>Yrgo</school_name>
    </school>
    <substitute>
       <name>Henrik</name>
    </substitute>
  </schedule>
  <schedule date="2018-01-17 08:00:00">
    <school>
      <address>Grepgatan 9 424 65 Angered</address>
      <school_name>Angeredsgymnasiet</school_name>
    </school>
    <substitute>
       <name>Anders</name>
    </substitute>
  </schedule>
</schedules>

The database

Schema for the tables:

CREATE TABLE substitute(substitute_id integer primary key, name text);
CREATE TABLE schedule(day datetime, substitute_id integer, school_id integer);
CREATE TABLE "school"(school_id integer primary key, school_name text, address text);

The school:

CREATE TABLE "school"(school_id integer primary key, school_name text, address text);
sqlite> select * from school;
1|Yrgo|Lärdomsgatan 3 402 72 GÖTEBORG
2|ITHS|Ebbe Lieberathsgatan 18 c 412 65 Göteborg
3|Jensen|Kruthusgatan 17 411 04 Göteborg
4|Angeredsgymnasiet|Grepgatan 9 424 65 Angered
5|Aniaragymnasiet|Kyrkogatan 46 411 15 Göteborg
6|Bernadottegymnasiet|Skånegatan 18 411 40 Göteborg
7|Bräckegymnasiet|Uppfinnaregatan 1 417 56 Göteborg
8|Burgårdens utbildningscentrum|Skånegatan 20 402 29 Göteborg

The table substitute (Swedish: vikarie):

CREATE TABLE substitute(substitute_id integer primary key, name text);
sqlite> select * from substitute;
1|Rikard
2|Henrik
3|Anders
4|Nahid
5|Conny
6|Svante
7|Elisabeth
8|Eva
9|Kristina
10|Bengt

The table schedule (the connection between date - substitute - school):

CREATE TABLE schedule(day datetime, substitute_id integer, school_id integer);
sqlite>
select * from schedule;
2018-01-15 08:00:00|1|1
2018-01-16 08:00:00|1|1
2018-01-17 08:00:00|1|1
2018-01-18 08:00:00|1|2
2018-01-16 08:00:00|2|1
2018-01-17 08:00:00|2|1
2018-01-18 08:00:00|2|3
2018-01-16 08:00:00|3|2
2018-01-17 08:00:00|3|4
2018-01-18 08:00:00|3|5
2018-01-16 08:00:00|4|8

Example query for all substitute (teachers) at the date 2018-01-17:

select day, name, school_name,address
 from schedule join substitute
 on schedule.substitute_id=substitute.substitute_id
 join school
 on schedule.school_id = school.school_id
 where day='2018-01-17 08:00:00';

2018-01-17 08:00:00|Rikard|Yrgo|Lärdomsgatan 3 402 72 GÖTEBORG
2018-01-17 08:00:00|Henrik|Yrgo|Lärdomsgatan 3 402 72 GÖTEBORG
2018-01-17 08:00:00|Anders|Angeredsgymnasiet|Grepgatan 9 424 65 Angered

Here's a dump of the above database, which you can import into SQLite3:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE substitute(substitute_id integer primary key, name text);
INSERT INTO "substitute" VALUES(1,'Rikard');
INSERT INTO "substitute" VALUES(2,'Henrik');
INSERT INTO "substitute" VALUES(3,'Anders');
INSERT INTO "substitute" VALUES(4,'Nahid');
INSERT INTO "substitute" VALUES(5,'Conny');
INSERT INTO "substitute" VALUES(6,'Svante');
INSERT INTO "substitute" VALUES(7,'Elisabeth');
INSERT INTO "substitute" VALUES(8,'Eva');
INSERT INTO "substitute" VALUES(9,'Kristina');
INSERT INTO "substitute" VALUES(10,'Bengt');
CREATE TABLE schedule(day datetime, substitute_id integer, school_id integer);
INSERT INTO "schedule" VALUES('2018-01-15 08:00:00',1,1);
INSERT INTO "schedule" VALUES('2018-01-16 08:00:00',1,1);
INSERT INTO "schedule" VALUES('2018-01-17 08:00:00',1,1);
INSERT INTO "schedule" VALUES('2018-01-18 08:00:00',1,2);
INSERT INTO "schedule" VALUES('2018-01-16 08:00:00',2,1);
INSERT INTO "schedule" VALUES('2018-01-17 08:00:00',2,1);
INSERT INTO "schedule" VALUES('2018-01-18 08:00:00',2,3);
INSERT INTO "schedule" VALUES('2018-01-16 08:00:00',3,2);
INSERT INTO "schedule" VALUES('2018-01-17 08:00:00',3,4);
INSERT INTO "schedule" VALUES('2018-01-18 08:00:00',3,5);
INSERT INTO "schedule" VALUES('2018-01-16 08:00:00',4,8);
CREATE TABLE "school"(school_id integer primary key, school_name text, address text);
INSERT INTO "school" VALUES(1,'Yrgo','Lärdomsgatan 3 402 72 GÖTEBORG');
INSERT INTO "school" VALUES(2,'ITHS','Ebbe Lieberathsgatan 18 c 412 65 Göteborg');
INSERT INTO "school" VALUES(3,'Jensen','Kruthusgatan 17 411 04 Göteborg');
INSERT INTO "school" VALUES(4,'Angeredsgymnasiet','Grepgatan 9 424 65 Angered');
INSERT INTO "school" VALUES(5,'Aniaragymnasiet','Kyrkogatan 46 411 15 Göteborg');
INSERT INTO "school" VALUES(6,'Bernadottegymnasiet','Skånegatan 18 411 40 Göteborg');
INSERT INTO "school" VALUES(7,'Bräckegymnasiet','Uppfinnaregatan 1 417 56 Göteborg');
INSERT INTO "school" VALUES(8,'Burgårdens utbildningscentrum','Skånegatan 20 402 29 Göteborg');
COMMIT;

Import the database like this (if you want the database to end up in www/WEB-INF/resources/vikarie.db for instance):

  1. Create a text file with the contents of the dump
    1. name it database-dump.sql
  2. Create an SQLite3 database from the textfile:
$ sqlite3 www/WEB-INF/resources/vikarie.db < database-dump.sql

Getting started with the assignment

Version 1. If you start with the servlet from the workshop

Clone this repository and copy everything from web-misc/nahid-sysint/ to a directory on your computer, where you will do all work.

$ git clone https://github.com/progund/web-misc.git
$ mkdir my-lab
$ cp -a web-misc/nahid-sysint/* my-lab/
$ cd my-lab
$ tree
.
├── clean.sh
├── compile_servlet_and_start_winstone.sh
├── download_sqlite_driver.sh
├── download_winstone.sh
├── winstone.jar
└── www
    └── WEB-INF
        ├── classes
        │   └── se
        │       └── yrgo
        │           └── schedule
        │               ├── AccessException.java
        │               ├── Assignment.java
        │               ├── AssignmentsFactory.java
        │               ├── Assignments.java
        │               ├── DatabaseAssignments.java
        │               ├── DBHelper.java
        │               ├── FormatterFactory.java
        │               ├── Formatter.java
        │               ├── HtmlFormatter.java
        │               ├── ParamParser.java
        │               └── ScheduleServlet.java
        ├── lib
        │   └── sqlite-jdbc.jar
        ├── resources
        │   └── vikarie.db
        └── web.xml

8 directories, 20 files

You are now in my-lab and this is where you will be working. The directory tree you see above, is the directory from the workshop where we showed you a servlet which could generate substitute teacher schedules as HTML. We will modify and extend this system, so that it can produce XML and JSON instead of HTML.

Some things to think about:

  • The current system only produces HTML
    • we need to check the parameter format and, if it's xml, produce XML, and if it's json, produce JSON.
  • The model for a schedule in the example, is very simplified as the class Assignment
    • We need to create a richer model, which can represent an Assignment as composed with
      • A Substitute (new class representing the substitute teacher with a name)
      • A School (new class representing the school of the assignment with a name and an address of the school)
      • A date for the assingment (we'll use a String for the date for convenience)
    • The new model is needed, since the JSON/XML should contain information which is not included in the simpler model in the workshop example, like the school's address, for instance
  • The example code does have an HTML formatter class, we can copy that code to a new class JsonFormatter and write a formatter for JSON
    • Later you can make one more copy to XmlFormatter and write a formatte for XML too

Task 1. Write the JsonFormatter

Copy HtmlFormatter.java to JsonFormatter.java, change the class name in the source code to JsonFormatter and also change the javadoc comment to reflect the responsibility of this class (formatting to JSON)

$ cp www/WEB-INF/classes/se/yrgo/schedule/HtmlFormatter.java www/WEB-INF/classes/se/yrgo/schedule/JsonFormatter.java

The code for you to write is inside the format(List<Assignment> assignments) method. You should loop through every assignment and create JSON from the list.

Now, before we can do that, we need to write the richer model with the new version of the Assignment class and use composition so that each Assignment has an instance variable of type School, and an instance variable of type Substitute and a String with the date. Make a note of this!

For now, we'll settle with simply having our method format(List<Assignment> assignments) returning a fake hard coded JSON String, e.g. [ { "some-key": "some-value" } ], and see if we can get the Servlet to react on the format parameter and a json value, call our formatter and write the fake JSON response.

This is an example of the fake version of the format method:

  public String format(List<Assignment> assignments) {
    return "[ { \"some-key\": \"some-value\" } ]";
  }

Compile the JsonFormatter.java to see that this fake version works.

You need to include the www/WEB-INF/classes path in your class path, so that javac can find the Assignment class (part of the argument to the method), because they are both in the package se.yrgo.schedule!

$ javac -cp www/WEB-INF/classes/ www/WEB-INF/classes/se/yrgo/schedule/JsonFormatter.java

Task 2. Make the Servlet care about the format parameter and call the formatter for json

Open the source code for the servlet (in www/WEB-INF/classes/se/yrgo/schedule/ScheduleServlet.java) for editing.

Look at the line in doGet which gets a Formatter. It gets the "correct" formatter to use in two steps:

  • Ask the parser for the format
  • Send the format to the FormatterFactory and get a Formatter back

We'll start by looking at the parser, to see that it will return the correct type if format=json is part of the request string. So, open the ParamParser class and the parseValues() method. Will it detect format=json? Yes it will. But, what about the content-type? Look at the parseContentType() method. It seems to only set the content-type if format is null. Now, that won't work if format is in fact "json" (or xml)!

Let's change the parseContentType() method so that it "knows about" json and xml!

This is an example of how that could be done:

  private void parseContentType() {
    if (format != null && format.equalsIgnoreCase("json")) {
      contentType = "application/json;charset=" + UTF_8.name();
    } else if (format != null && format.equalsIgnoreCase("xml")) {
      contentType = "application/xml;charset=" + UTF_8.name();
    } // Default to text/html
    else { // format param missing or illegal format!
      contentType = "text/html;charset=" + UTF_8.name();
    }
  }

We now have to change also some details in the FormatterFactory.

Look at the class FormatterFactory. The variable private static Formatter JSON_FORMATTER; is null. Change it to be a new JsonFormatter instead. You will later do the same for the XmlFormatter, but we haven't written that class yet, so make a note of that! Also, change the if statement of the factory to use equalsIgnoreCase() rather than contains() when figuring out what the format parameter is!

Compile the parser and formatter factory, and start winstone:

$ javac -cp winstone.jar www/WEB-INF/classes/se/yrgo/schedule/*.java && java -jar winstone.jar --webroot=www

Now, test that the servlet (the parser used by the servlet) detects the format=json parameter and sends the correct JSON (the fake) and the correct content-type by opening a new terminal while winstone is running and do the following:

$ lwp-request -m GET -e 'http://localhost:8080/v1?format=json' 
200 OK
Connection: Close
Date: Sun, 11 Feb 2018 12:21:59 GMT
Server: Winstone Servlet Engine v0.9.10
Content-Type: application/json;charset=UTF-8
Client-Date: Sun, 11 Feb 2018 12:21:59 GMT
Client-Peer: 127.0.0.1:8080
Client-Response-Num: 1
X-Powered-By: Servlet/2.5 (Winstone/0.9.10)

[ { "some-key": "some-value" } ]

Verify that you see the correct content-type HTTP header, and also the JSON string (the fake one).

Now test it without the format parameter, and with a faulty format as the parameter. Verify that the servlet detects the problem.

$ lwp-request -m GET -e 'http://localhost:8080/v1?'
200 OK
Connection: Close
Date: Sun, 11 Feb 2018 12:29:11 GMT
Server: Winstone Servlet Engine v0.9.10
Content-Type: text/html;charset=UTF-8
Client-Date: Sun, 11 Feb 2018 12:29:11 GMT
Client-Peer: 127.0.0.1:8080
Client-Response-Num: 1
Title: Substitutes R Us - Schedule API
X-Powered-By: Servlet/2.5 (Winstone/0.9.10)

<!DOCTYPE html>
<html lang="en">
<head><title>Substitutes R Us - Schedule API</title></head>
<body>
<h1>Schedule</h1>
<table border="1">
 <tr>
  <th>Teacher</th>
  <th>date</th>
  <th>school</th>
 </tr>
 <tr>
  <td>Rikard</td>
  <td>2018-01-15 08:00:00</td>
  <td>Yrgo</td>
 </tr>
 <tr>
  <td>Rikard</td>
  <td>2018-01-16 08:00:00</td>
  <td>Yrgo</td>
 </tr>
 <tr>
  <td>Rikard</td>
  <td>2018-01-17 08:00:00</td>
  <td>Yrgo</td>
 </tr>
 <tr>
  <td>Rikard</td>
  <td>2018-01-18 08:00:00</td>
  <td>ITHS</td>
 </tr>
 <tr>
  <td>Henrik</td>
  <td>2018-01-16 08:00:00</td>
  <td>Yrgo</td>
 </tr>
 <tr>
  <td>Henrik</td>
  <td>2018-01-17 08:00:00</td>
  <td>Yrgo</td>
 </tr>
 <tr>
  <td>Henrik</td>
  <td>2018-01-18 08:00:00</td>
  <td>Jensen</td>
 </tr>
 <tr>
  <td>Anders</td>
  <td>2018-01-16 08:00:00</td>
  <td>ITHS</td>
 </tr>
 <tr>
  <td>Anders</td>
  <td>2018-01-17 08:00:00</td>
  <td>Angeredsgymnasiet</td>
 </tr>
 <tr>
  <td>Anders</td>
  <td>2018-01-18 08:00:00</td>
  <td>Aniaragymnasiet</td>
 </tr>
 <tr>
  <td>Nahid</td>
  <td>2018-01-16 08:00:00</td>
  <td>Burgårdens utbildningscentrum</td>
 </tr>
</table>
</body>
</html>

Correct content-type (defaults to HTML) but wrong content. Let's remove the HtmlFormatter completely, and remove it from the FormatterFactory as a valid formatter and throw an IllegalArgumentException for anything else than xml or json:

  public static Formatter getFormatter(String contentType) {
    if (contentType.equalsIgnoreCase("xml")) {
      return XML_FORMATTER;
    } else if (contentType.equalsIgnoreCase("json")) {
      return JSON_FORMATTER;
    } else {
      throw new IllegalArgumentException("Format not supported");
    }
  }

Make the servlet catch illegal argument exception when getting the formatter and write an error response as HTML:

      // Get a formatter, by asking the parser for the format (defaults to HTML)
      try {
        Formatter formatter = FormatterFactory.getFormatter(parser.format());
        // Format the result to the format according to the parser:
        String result = formatter.format(assignments);
        // Print the result and close the PrintWriter
        out.println(result);
      } catch (IllegalArgumentException e) {
        out.println("<html><head><title>Format error</title></head>");
        out.println("<body>Format missing or not supported");
        out.println(" - We support xml and json</body>");
        out.println("</html>");
      }
      out.close();

Test it again:

$ lwp-request -m GET -e 'http://localhost:8080/v1?format=jsong'
200 OK
Connection: Close
Date: Sun, 11 Feb 2018 12:38:40 GMT
Server: Winstone Servlet Engine v0.9.10
Content-Type: text/html;charset=UTF-8
Client-Date: Sun, 11 Feb 2018 12:38:40 GMT
Client-Peer: 127.0.0.1:8080
Client-Response-Num: 1
Title: Format error
X-Powered-By: Servlet/2.5 (Winstone/0.9.10)

<html><head><title>Format error</title></head>
<body>Format missing or not supported
 - We support xml and json</body>
</html>

You may, and should, of course also test various requests using a browser, but start with the command line.

Task 3. Create the model classes

Now we know that our system can detect and use the format parameter (at least for JSON), so we should now implement the JsonFormatter class. But before we can do that, we need to fix our model! The current model, which we "borrowed" from the HTML schedule workshop code, isn't complete enough for holding all the data we need for the JSON output. Let's revisit example JSON for one assignment:

  {
    "date": "2018-01-17 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Rikard"}
  }

From the above, we see that each assignment (of point in a schedule) needs three things from the database:

  • A date (let's use a String for this for simplicity)
  • A school, which in turn is an object with
    • address (String)
    • school_name (String)
  • A substitute (teacher), which in turn is an object with
    • name (String)

So, let's start by creating two new classes for our model:

  • School
  • Substitute

These will be plain old Java objects with constructor, instance variables and accessor methods for the values.

Writing the School class

Write a class School which has

  • Instance variable name : String
  • Instance variable address : String
  • Constructor (String name, String address) - sets the instance variables
  • Accessor method name() : String
  • Accessor method address() : String

Make it part of the same package as everything else. Compile it.

Writing the Substitute class

Write a class Substitute which has

  • Instance variable name : String
  • Constructor (String name) - sets the instance variable
  • Accessor method name() : String

Make it part of the same package as everything else. Compile it.

Re-factor the Assignment class to use the new classes

Change the Assignment class so that it has:

  • Instance variable teacher : Substitute
  • Instance variable date : String
  • Instance variable school : School
  • Constructor (Substitute teacher, String date, School school) - sets the instance variables
  • Accessor method teacher() : Substitute
  • Accessor method date() : String
  • Accessor method school : School
  • toString() - returns a string representation of this assignment (use teacher.name(), school.name(), school.address() etc)

Now compile the Assignment class (you will need to put www/WEB-INF/classes on your class path, since Assignment is using School and Substitute from the same package as itself:

$ javac -cp www/WEB-INF/classes/ www/WEB-INF/classes/se/yrgo/schedule/Assignment.java

Verify that also Substitute and School were re-compiled (there is a clean.sh script for deleting all class files to start from scratch!).

Task 4. Finish the database class for creating assignments

Change the class DatabaseAssignments, so that it creates lists of Assignments using your new model classes for assignments, substitute and school.

Change all four methods (all(), forTeacher(), at() and forTeacherAt()) so that they create Assignments using the constructor in Assignment which now takes the following argument types: Substitute, String, School

Here's what the method all() could look like. Do similar changes to the other three methods:

  public List<Assignment> all() throws AccessException {
    List<Assignment> result = new ArrayList<>();
    try {
      ResultSet rs = db.fetch(SELECT_ALL);
      while (rs.next()) {
        result
          .add(new Assignment(new Substitute(rs.getString("name")),
                              rs.getString("day"),
                              new School(rs.getString("school_name"),
                                         rs.getString("address"))));
      }
      return result;
    } catch (SQLException sqle) {
      throw new AccessException("Problem fetching all assignments", sqle);
    }
  }

Task 5. Implement the JSON formatter

Now it is finally time to get back to our good old JsonFormatter class and remove the fake JSON and instead, using org.json, parse the list of assigments to some real JSON.

Start by importing org.json.* in the JsonFormatter class. Next, in the format method, check if the list of assigments in the argument is empty and if so, return a String representing an empty JSON array:

    if (assignments.size() == 0) {
      return "[]";
    }

If not, it is time to create the actual JSON objects and add them to a new JSONArray. We'll use a helper method which can convert one single Assignment to one JSONObject when we loop over each assignment in the argument list assignments:

    if (assignments.size() == 0) {
      return "[]";
    } else {
      JSONArray JSON = new JSONArray();
      for (Assignment assignment : assignments) {
        JSON.put(JSONAssignment(assignment));        
      }
      return JSON.toString(2);
    }

You will have to figure out the code for the helper method yourself. Here's the rough layout for you so that you can get started:

  /* Creates one JSON object from one assignment */
  private JSONObject JSONAssignment(Assignment assignment) {
    // Create a new empty JSONObject called JSONAsssignment
    // Put the key "date" in that object, and the value from the assignment's date
    // Create a new empty JSONObject called JSONSubstitute
    // Put the key "name" in that object, and the value from the assignment's teacher's name
    // Put the JSONSubstitute object in the JSONAssignment object with the key "substitute"
    // Create a new JSONObject called JSONSchool
    // Put the key "school_name" in that object and the value from the assignment's school's name
    // Put the key "address" in that object and the value from the assignment's school's address
    // Put the JSONSchool in the JSONAssignment object with the key "school"
    return JSONAssignment;
  }

Compile everything and call the servlet again with the argument format=json&day=2018-01-16 and verify your response against the database.

You need to have both winstone.jar and org.json.jar and www/WEB-INF/classesin your classpath when you compile.

In order to download org.json.jar, we suggest the following:

$ wget --no-check-certificate 'https://search.maven.org/remotecontent?filepath=org/json/json/20171018/json-20171018.jar' -O www/WEB-INF/lib/org.json.jar

To compile everything (MacOS/Unix/GNU/Linux):

$ javac -cp www/WEB-INF/lib/org.json.jar:winstone.jar:www/WEB-INF/classes www/WEB-INF/classes/se/yrgo/schedule/*.java

Windows with Cygwin:

$ javac -cp "www/WEB-INF/lib/org.json.jar;winstone.jar;www/WEB-INF/classes" www/WEB-INF/classes/se/yrgo/schedule/*.java

The query we will use will be http://localhost:8080/v1?format=json&day=2018-01-16 and that means the following should happen:

  • 1. The servlet parses the GET query string, using the ParamParser
    • 1.1 The parser detects format=json and will give the servlet the correct format and content type when asked
    • 1.2 The parser detects and saves the day parameter
    • 1.3 The parser detects the type of query by looking at all parameters and decides this is the type DAY since we only have day as the parameter and no substitute_id - so all scheduels for all teachers at the specified day!
  • 2. The servlet uses the type DAY to get all scheduels for all teachers at this day from the database, by calling the db.at() method, and gets a List<Assignments> back from the db object.
  • 3. The servlet gets a Formatter from the FormatterFactory, by giving the parser's format value as an argument
  • 4. The servlet asks the Formatter to format its list of assignments and writes them to the response.

Here's how you can test the above from the command line:

$ lwp-request -m GET -e 'http://localhost:8080/v1?format=json&day=2018-01-16'
200 OK
Connection: Close
Date: Sun, 11 Feb 2018 14:05:12 GMT
Server: Winstone Servlet Engine v0.9.10
Content-Type: application/json;charset=UTF-8
Client-Date: Sun, 11 Feb 2018 14:05:12 GMT
Client-Peer: 127.0.0.1:8080
Client-Response-Num: 1
X-Powered-By: Servlet/2.5 (Winstone/0.9.10)

[
  {
    "date": "2018-01-16 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Rikard"}
  },
  {
    "date": "2018-01-16 08:00:00",
    "school": {
      "address": "Lärdomsgatan 3 402 72 GÖTEBORG",
      "school_name": "Yrgo"
    },
    "substitute": {"name": "Henrik"}
  },
  {
    "date": "2018-01-16 08:00:00",
    "school": {
      "address": "Ebbe Lieberathsgatan 18 c 412 65 Göteborg",
      "school_name": "ITHS"
    },
    "substitute": {"name": "Anders"}
  },
  {
    "date": "2018-01-16 08:00:00",
    "school": {
      "address": "Skånegatan 20 402 29 Göteborg",
      "school_name": "Burgårdens utbildningscentrum"
    },
    "substitute": {"name": "Nahid"}
  }
]

And, indeed, it seems to have worked! The only thing left for you in order to pass the assignment at level G is to add a formatter for XML (remember all your notes that you have taken above when working with the JSON formatter etc).

Task 6. Some hints for implementing the XML formatter

You should now do the same with the XML formatter as you did with your JSON formatter. Do you rember the steps? We told you to make notes previously for these steps. Here they are in summary:

  • Copy the old HtmlFormatter to a new name XmlFormatter and change the class name and javadoc comment (you may also copy your JsonFormatter to the new name XmlFormatter if you have deleted your HtmlFormatter)
  • Add import statements to XmlFormatter so that you can use javax.xml and all the rest needed for creating XML
  • Finish the format() method so that it loops through the list of Assignments given as the argument, and creates an XML document from each assignment in the list.
  • Look at the documentation above, in order to see what the XML document should look like:
    • The root element should be called <schedules>
    • Each schedule element should be called schedule and have the date as an attribute:
    • <schedule date="2018-01-17 08:00:00">
    • Each schedule element has two sub-elements, <school> and <substitute>
    • Look at the documentation XML example in order to see what the whole thing should look like stucturally
  • Build the XML document and turn it into a String, using a StringWriter, and return the String (code example follows)
  • Don't forget to instantiate the XmlFormatter variable in the FormatterFactory!

Here's a rough outline of the code for the xml version of format:

    if (assignments.size() == 0) {
      return new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n")
        .append("<schedules></schedules>\n")
        .toString();
    } else {
      try {
        DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
        Document doc = docBuilder.newDocument();
        Element rootElement = doc.createElement("schedules");
        doc.appendChild(rootElement);
        for (Assignment assignment : assignments) {
          // Create an Element schedule using doc.createElement("schedule")
          // set the attribute "date" on this schedule (use this assignment's date)
          // Create an Element school the same way
          // Create an Element schoolName the same way
          // add a text node to schoolName and use the assignment's school's name 
          //    to create a textNode, use doc.createTextNode(the text as a string);
          // add schoolName as a child to school
          // Create an Element address the same way as above
          // append a textNode to address with the assignment's school's address 
          // append address as a child to school
          // Append the whole school as a child to schedule
          // Create an Element substitute
          // Create an element Element name
          // Append a text node to name with the assignment's teacher's name 
          // Append name as a child to substitute
          // Append the whole substitute as a child to schedule
          // Append the whold schedule as a child to rootElement
        }
        StringWriter xml = new StringWriter();
        TransformerFactory transformerFactory =
          TransformerFactory.newInstance();
        Transformer transformer = transformerFactory.newTransformer();
        transformer.setOutputProperty(OutputKeys.INDENT, "yes");
        transformer
          .setOutputProperty("{http://xml.apache.org/xslt}indent-amount",
                             "2");
        DOMSource source = new DOMSource(doc);
        StreamResult result = new StreamResult(xml);
        transformer.transform(source, result);
        return xml.toString();
      } catch (ParserConfigurationException|TransformerException e) {
        return "XML Error";
      }
    }

Here's a test from the command line:

$ lwp-request -m GET -e 'http://localhost:8080/v1?format=xml&day=2018-01-16'
200 OK
Connection: Close
Date: Sun, 11 Feb 2018 14:35:07 GMT
Server: Winstone Servlet Engine v0.9.10
Content-Type: text/html;charset=UTF-8
Client-Date: Sun, 11 Feb 2018 14:35:07 GMT
Client-Peer: 127.0.0.1:8080
Client-Response-Num: 1
X-Powered-By: Servlet/2.5 (Winstone/0.9.10)

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<schedules>
  <schedule date="2018-01-16 08:00:00">
    <school>
      <school_name>Yrgo</school_name>
      <address>Lärdomsgatan 3 402 72 GÖTEBORG</address>
    </school>
    <substitute>
      <name>Rikard</name>
    </substitute>
  </schedule>
  <schedule date="2018-01-16 08:00:00">
    <school>
      <school_name>Yrgo</school_name>
      <address>Lärdomsgatan 3 402 72 GÖTEBORG</address>
    </school>
    <substitute>
      <name>Henrik</name>
    </substitute>
  </schedule>
  <schedule date="2018-01-16 08:00:00">
    <school>
      <school_name>ITHS</school_name>
      <address>Ebbe Lieberathsgatan 18 c 412 65 Göteborg</address>
    </school>
    <substitute>
      <name>Anders</name>
    </substitute>
  </schedule>
  <schedule date="2018-01-16 08:00:00">
    <school>
      <school_name>Burgårdens utbildningscentrum</school_name>
      <address>Skånegatan 20 402 29 Göteborg</address>
    </school>
    <substitute>
      <name>Nahid</name>
    </substitute>
  </schedule>
</schedules>

For comparison, we might check that this is the correct data by using SQL directly in the sqlite database interactively:

 sqlite3 www/WEB-INF/resources/vikarie.db 
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite>

You may use the following SQL:

SELECT day, name, school_name, address
FROM schedule
 NATURAL JOIN substitute
 NATURAL JOIN school
WHERE day LIKE '2018-01-16%';

The result from the database should look like this:

2018-01-16 08:00:00|Rikard|Yrgo|Lärdomsgatan 3 402 72 GÖTEBORG
2018-01-16 08:00:00|Henrik|Yrgo|Lärdomsgatan 3 402 72 GÖTEBORG
2018-01-16 08:00:00|Anders|ITHS|Ebbe Lieberathsgatan 18 c 412 65 Göteborg
2018-01-16 08:00:00|Nahid|Burgårdens utbildningscentrum|Skånegatan 20 402 29 Göteborg

Version 2. If you write everything from scratch

If you don't want to start with the code from the HTML servlet workshop with the substitute schedule as HTML example, you should write everything from scratch. We suggest that you start with the directory layout:

.
├── winstone.jar
└── www
    └── WEB-INF
        ├── classes
        │   └── se
        │       └── yrgo
        │           └── schedule
        │               └── Java files here!
        ├── lib
        │   ├── org.json.jar - make sure you have org.json.jar and sqlite-jdbc.jar
        │   └── sqlite-jdbc.jar
        ├── resources
        │   └── vikarie.db - take the database from the workshop or create it using the instructions above
        └── web.xml - This one must be according to the standard - see the workshop for inspiration

After this, make a list of what classes you need and what to name them, what they should do etc. You will need one servlet and a lot of helper classes. Here's a suggestion:

  • a parser for the query,
  • a factory for the formatters,
  • a factory for the database access (we called it AssignmentsFactory in the workshop),
  • SQL/JDBC helper class,
  • and then the domain classes
    • Assignment,
    • School,
    • Substitute

Think carefully about the classpath when compiling:

  • Classes with stuff from the javax.servlet package need to have winstone.jar in the class path in order to compile
  • Classes with stuff from the org.json package need to have org.json.jar in the class path
  • Classes which reference other classes in the same package need to have the root package in the class path
    • e.g. In order to compile se.yrgo.schedule.Assignment (if it uses se.yrgo.schedule.School), you need to put www/WEB-INF/classes in your classpath, if you are compiling from the directory with www (which we recommend)

Think about how to test your development in various steps - compile often and run tests often.

If you decide to write everything from scratch, it is even more important to comment your code, write a build and run instruction for the teacher so that the teacher knows how to compile and run the system (and how to interact with the web api). Otherwise it will be impossible to correct your assignment - if we are not easily able to even compile your code, it means that we will probably not accept it or grade it.

Good luck!

Hints for VG grade tasks

Staus codes

Your servlet should send HTTP headers for when it can't find any schedule data according to the parameters. It is enough if you check if the list of assignments from the database is empty, and then set the status code 404 Not Found. There are lectures on how to set status codes available, e.g. Servlet_-_HTTP_response_codes and Exercises_-_Servlet_-_HTTP_response_codes.

Your servlet should also detect illegal or malformed requests. It is enough if your servlet detects that the format parameter is either missing or has a value for an unsupported type, in which case you should set the status code BAD_REQUEST and some text in the response body explaining what was bad with the request (e.g. "The format parameter is mandatory" or "That format is not supported"). You may use HTML as the content-type for error messages, or plain text or whatever you want. But all handling of these types of errors must be documented a report.

Note: If you send the error message in HTML, think about what happens with the content-type header. There is no clear standard for what content type to use for error messages. Some APIs use JSON also for the error message (or XML if that content type is used for data). But what format should you use for an error message about the format parameter, e.g. "format not supported"? It is up to the client to check both status code and content type, but this should also be documented in the API's online documentation. In this case, you should give the teacher instructions for how to compile and run the server-side, and also how the API handles errors.

Note 2: Due to a bug in Rikard's code, we should change the way we create the PrintWriter in the Servlet. Change to either:

PrintWriter out =
  new PrintWriter(new OutputStreamWriter(response.getOutputStream(),
                                         UTF_8),
                  false);

Or:

response.setCharacterEncoding(UTF_8.name());
PrintWriter out = response.getWriter();

The reason was a very subtle bug due to the way the OutputStreamWriter was created before. The boolean parameter was true which caused the printwriter to commit every output, which caused the servlet to behave weird when the content-type and status code was changed. You don't need to understand this. Just change to one of the above, and the bug will disappear.

Hint: Create a boolean method in the parser which can tell if the request was valid or not, use a boolean variable inside the parser and set it to false if you detect e.g. an illegal format or that something is missing. This makes it cleaner to check for errors from the servlet - it can now ask the parser if the request was valid and if not, set some error status codes etc.

Hints if you write everything from scratch

If you write everything from scratch, rather than building upon the html scheduler workshop code, it is a criteria for VG that your servlet doesn't contain any SQL/JDBC code as well as no code for JSON or XML formatting. The servlet must be "clean" and use other classes and objects for doing the:

  • Parsing of the arguments (understanding the query string)
  • Fetching data from the database
  • Converting the data to the proper format (JSON or XML)
    • Hint: Use interfaces like "Formatter", "Assignments" and factories for selecting the concrete implementations

Directory layout refactoring for VG

The way the code is organized in the workshop code isn't very well structured. It has every class in the same package (in order to make the workshop easier to understand). This is not how you typically organize your code.

Hint: Create more than one package for your various classes. Suggested package names (you may come up with your own, as long as you document them so that the teachers have a chance to quickly understand your code)

  • se.yrgo.schedule.servlet - For the servlet and parser and possibly the AccessException (thrown from the database stuff)
  • se.yrgo.schedule.domain - For the Assignment, School and Substitute classes
  • se.yrgo.schedule.format - For the FormatFactory, JsonFormatter and XmlFormatter
  • se.yrgo.schedule.data - For the AssignmentsFactory (which could create a DatabaseAssignments class) and the SQL/JDBC related classes

Now the code would be more logically structured with code more closely related to each other in the same packages, and other code will be imported when needed.

Remember that refactoring everything into new packages is a lot of work (but something you do very often in the real world), but it is not that hard if you focus. All classes using stuff from one package, need to import that package (or those classes in that package). So, the servlet, for instance could import se.yrgo.schedule.domain.Assignment for instance, because it handles a List<Assignment>. And the servlet would also need to import se.yrgo.schedule.format.Formatter and se.yrgo.schedule.format.FormatterFactory, because it is using those classes as well. The database realated code in se.yrgo.schedule.data will probably need to import se.yrgo.schedule.domain.* etc.

So here's a plan:

  • make a sketch of your new package layout
  • then create the corresponding directories
  • move the classes to their respective new directory/package
  • add package declarations reflecting the new locations/packages
  • compile often and when you see class not found exceptions, you have probably forgotten to import classes from other packages

Good luck!

Voluntary non-mandatory challenge for the ambitious student

If you feel that this assignment was too little, too easy, then you can as an optional task (will not affect the grade) add support for more formats to the web api. We suggest CSV (comma separated values) and why not HTML. We have lectures for this on video if you want to see how that could be done. Here are some pointers:

Don't start with this until you are finished and ready to hand in your files - so that you don't get stuck here and miss the assignment because of this challenge.

Make this challenge in a copy of your files, so that you still can hand in a working copy of the actual assignment.

Another thing you can optionally do as a challenge, is to switch to using ISO-8601 dates. You can format a date from SQLlite3 using strfrtime:

SELECT strftime('%Y-%m-%dT%H:%M:%S+01:00',day) FROM schedule;

2018-01-15T08:00:00+01:00
2018-01-16T08:00:00+01:00
2018-01-17T08:00:00+01:00
2018-01-18T08:00:00+01:00
2018-01-16T08:00:00+01:00
2018-01-17T08:00:00+01:00
2018-01-18T08:00:00+01:00
2018-01-16T08:00:00+01:00
2018-01-17T08:00:00+01:00
2018-01-18T08:00:00+01:00
2018-01-16T08:00:00+01:00

Note that when on Swedish summer time (daylight saving), you should add the String +02:00 to the date string. If you save your dates as UTC dates, then you can use Z instead of +01:00 or +02:00, never mind the summer time or winter time. UTC is always the same and doesn't change with the seasons.

Hints:

Yet another challenge could be to replace the GET parameters with crafted URLs, e.g. http://localhost:8080/v1/date/all/substitute/2 or http://localhost:8080/v1/date/2018-01-16/substitute/3. You would have to parse the "pathInfo" instead of the GET parameters. Calling request.getPathInfo() would return the part matching the * in the URL pattern of your web.xml. So if your servlet has the URL pattern /v1/* then pathInfo() would return /date/all/substitute/2 for the URL http://localhost:8080/v1/date/all/substitute/2.