First Midterm Exam Solutions

A system to register people for events

In the lecture on Cline I had Cline build a simple system to manage registrations for events. In this exam you will write parts of the code for a very similar system.

The system sits on top of a database with three tables: a table of people, a table of events, and a table of registrations for events.

Suppose that your Spring Boot application has the following three Java classes:

public class Person {
  private int id;
  private String firstname;
  private String lastname;
  
  public Person() {}

  // Get and Set methods for the member variables appear here.
}

public class Event {
  private int id;
  private String title;
  private String description;
  private LocalDate day;
  
  public Event() {}

  // Get and Set methods for the member variables appear here.
}

public class Registration {
  private int person;
  private int event;
  
  public Registration() {}

  // Get and Set methods for the member variables appear here.
}

In all of the questions below you may assume that you have also written row mapper classes for each of these classes.

Problem One

Now suppose that you wanted to write the code for a method in a repository

public List<Person> getPeopleForEvent(int id)

That returns a list of Person objects for people who have registered for an event given the id of that event.

One way to do this is to do one query where you get a list of ids of people who are registered for an event from the registrations table and then do separate queries to fetch the people one by one from the table of people.

Write the code for this method. You may assume that your repository class has a member variable

JdbcTemplate jdbcTemplate;

Solution

public List<Person> getPeopleForEvent(int id) {
  String sql = "select person from registrations where event=?";
  List<Integer> ids = jdbcTemplate.queryForList(sql,Integer.class,id);
  List<Person> results = new ArrayList<Person>();
  for(int personid : ids) {
    String personSQL = "select * from people where id=?";
    RowMapper<Person> personRowMapper = new PersonRowMapper();
    try {
      Person p = jdbcTemplate.queryForObject(personSQL,personRowMapper,personid);
      results.add(p);
    } catch(Exception ex) {
        
    }
  }
  return results;
}

Problem Two

Another strategy to write this method is to do a single select that uses a join to merge the people and registrations tables and then just returns a list of Person objects directly. Write the code for that method.

Solution

public List<Person> getPeopleForEventAlternativeOne(int id) {
  String sql = "select id, firstname, lastname from people join registrations on people.id = registrations.person where event=?";
  RowMapper<Person> personRowMapper = new PersonRowMapper();
  List<Person> results = jdbcTemplate.query(sql,personRowMapper,id);
  return results;
}

Problem Three

Assuming that the method you just wrote appears in a repository class EventDAO, write the code for a method in a controller class that would allow the user to fetch this list of Person objects by making a GET request with a URL of /events/<id>/people where <id> is the id of an event they are interested in. You may assume that your controller class has a member variable

private EventDAO dao;

Solution

@GetMapping("/{id}/people")
  public ResponseEntity<List<Person>> findPeopleForEvent(@PathVariable("id") int id) {
  List<Person> people = dao.getPeopleForEvent(id);
  return ResponseEntity.ok().body(people);
}

Problem Four

Write the code for a repository method that can save a new Registration to the database.

Solution

  public void saveRegistration(Registration registration) {
    String sql = "insert into registrations(person,event) values(?,?)";
    jdbcTemplate.update(sql,registration.getPerson(),registration.getEvent());
}