Project Archive

The completed auction system

These notes will walk you through the design and construction of the completed auction system.

This system is fairly large, so I am not going to cover every single detail of the system's construction in these notes. Instead, I am going to be giving a high level overview of the most important system details.

Package layout

The full auction system makes use of some 27 different classes. Given the large number of classes involved, I used a system of packages to help organize everything:

Working with JdbcTemplate

All of the database interactions in this application go through JdbcTemplate objects. The JdbcTemplate class offers several methods to do database interactions. In the notes below I will show examples of how I used each of the following methods:

The first three methods are all used in combination with SQL select statements, while the fourth method gets used for all other kinds of statements such as insert, update, and delete.

Here is an example we have already seen in an earlier set of lecture notes. This is the code for the UserDAO's method for storing a new user in the database:

public String save(User user) {
    // First make sure this is not a duplicate
    String sql = "SELECT * FROM users WHERE name=?";
    RowMapper<User> rowMapper = new UserRowMapper();
    User old = null;
    try {
        old = jdbcTemplate.queryForObject(sql, rowMapper, user.getName());
    } catch(Exception ex) {

    }
    if(old != null)
        return "Duplicate";

    // Have MySQL generate a unique id
    String idSQL = "select uuid()";
    String key = null;
    try {
        key = jdbcTemplate.queryForObject(idSQL, String.class);
    } catch(Exception ex) {
        key = "Error";
    }
    if(key.equals("Error"))
        return key;

    String hash = passwordService.hashPassword(user.getPassword());
    String insertSQL = "insert into users(userid,name,password) values (?, ?, ?)";
    jdbcTemplate.update(insertSQL,key,user.getName(),hash);
    return key;
}

This example shows two slightly different applications of the queryForObject() method. The first application is more typical: we execute an SQL select statement that is designed to return 0 or 1 User objects from the database. In that version of queryForObject() the second parameter is a RowMapper designed to generate the kind of object we are expecting to get back from the query. The second application of queryForObject() works with a select statement that is designed to return a string. For that version of queryForObject() we simply use the second parameter to indicate that we are expecting the query to return a Java String object.

This example also shows a typical application of the update() method. Here we use update() to execute a SQL insert statement.

Some of the queries we will do will be designed to return multiple objects. For those types of queries we use the query() method. Here is a typical example: the code below is for a method from the AuctionDAO that fetches a list of all currently open auctions:

public List<Auction> findActive() {
  String sql = "select * from auctions where opens<=curdate() and closes>=curdate()";
  List<Auction> results = null;
  RowMapper<Auction> rowMapper = new AuctionRowMapper();
  try {
    results = jdbcTemplate.query(sql, rowMapper);
  } catch(Exception ex) {
        results = new ArrayList<Auction>();
    }
  return results;
}

Finally, there are a few cases where will need to read lists of things from the database that don't require the use of a RowMapper. In those situations we use queryForList() in place of query(). We can see an example of this in the code for reading all of the Auctions that have been posted by a particular user. The Auction class I defined has a member variable that stores a list of tags for the auction. Sometimes when I read an Auction object from the database I also will go to the tags table in the database to fetch the list of tags associated with that auction. Since the tags are simple strings, I will use queryForList() to read them. Here is the method from the AuctionDAO class that reads all of the Auctions posted by a particular user:

public List<Auction> findByUser(String userid) {
  String sql = "select * from auctions where seller=?";
  List<Auction> results = null;
  RowMapper<Auction> rowMapper = new AuctionRowMapper();
  try {
    results = jdbcTemplate.query(sql, rowMapper, userid);
  } catch (Exception ex) {
    results = new ArrayList<Auction>();
  }
  for (Auction a : results) {
    String tagSQL = "select tag from tags where auction=?";
    List<String> tags = jdbcTemplate.queryForList(tagSQL, String.class, a.getAuctionid());
    a.setTags(tags);
  }
  return results;
}

Controller methods

The final version of the auction application supports a wide range of API requests. There are 23 different interactions in the API spread across three separate controller classes. We have already seen a couple of basic examples of POST and GET interactions in a previous set of lecture notes. In these notes I am going to be discussing some examples of API methods we have not seen previously.

A lot of the methods in the auction API make use of path parameters. Here is a typical example: to fetch a list of all of the Auction objects uploaded by a particular user we use the HTTP combination

GET /users/<id>/auctions

where <id> is replaced by the user's id. Here is the method in the UserController that responds to this request:

@GetMapping("/{id}/auctions")
public ResponseEntity<List<Auction>> getAuctions(@PathVariable String id) {
  List<Auction> results = auctionDAO.findByUser(id);
  return ResponseEntity.ok().body(results);
}

Two things are important here. First of all, in the @GetMapping annotation we use the special notation {id} to indicate the presence of a path parameter in the URL. Secondly, we add a parameter to the method that is annotated with @PathVariable to receive the value of that path parameter.

Another feature that I use only rarely is query parameters. As I explained in the API design guidelines notes, query parameters are used only rarely as part of a filtering mechanism. There is one example of this in the auction API, a GET request to the AuctionsController that fetches all of the Auctions with a particular tag:

@GetMapping(params= {"tag"})
public ResponseEntity<List<Auction>> findActiveAuctionsByTag(@RequestParam(value = "tag") String tag) {
  List<Auction> auctions = dao.findActiveByTag(tag);
  return ResponseEntity.ok().body(auctions);
}

The query parameter gets passed as a parameter to the method that is annotated with the @RequestParam annotation.

DTO classes

In addition to the core classes defined in the auction.core package, I also found the need to create seven additional classes to meet specific needs of the API. These are all classes that will either be posted to the server, most often to implement remote procedure calls, or returned from the server in response to various GET requests.

Here are a couple of typical examples.

The first example becomes important when users have just won an auction. To fetch a list of objects that describe the auctions they have won users will do a GET with the URL /users/<id>/offers. Here is the method in the UserController that responds to that request:

 @GetMapping("/{id}/offers")
public ResponseEntity<List<Offer>> getOffers(@PathVariable String id) {
  List<Offer> results = dao.findOffers(id);
  return ResponseEntity.ok().body(results);
}

The Offer objects returned by this request have the following structure:

public class Offer {
  private String purchaseid;
  private String auctionid;
  private int bidid;
  private int amount;
}

The data in these Offer objects is a subset of the data found in the purchase_detail view in the database. Here is the code for the DAO method that pulls this data from the database:

public List<Offer> findOffers(String userId) {
  String sql = "select * from purchase_detail where bidder=? and status='Won_bid'";
  List<Purchase> purchases;
  RowMapper<Purchase> rowMapper = new PurchaseRowMapper();
  try {
    purchases = jdbcTemplate.query(sql, rowMapper,userId);
  } catch(Exception ex) {
    purchases = new ArrayList<Purchase>();
  }
  ArrayList<Offer> result = new ArrayList<Offer>();
  for(Purchase p : purchases) {
    Offer o = new Offer(p);
    result.add(o);
  }
  return result;
}

Note that the select statement we use here initially pulls a list of Purchase objects from the database. The fields that go into the Offer objects are a subset of those fields. To turn the Purchase objects into Offer objects I used a special constructor in the Offer class that builds an Offer from a Purchase:

public Offer(Purchase base) {
  purchaseid = base.getPurchaseid();
  auctionid = base.getAuction();
  bidid = base.getBidid();
  amount = base.getBid();
}

I also use this same technique with the Sale and Bill classes. All three of these classes implement slices of the Purchase class to deliver more focussed information to clients.

Another set of DTO classes are used to implement parameters for remote procedure calls. You can find many examples of these remote procedure calls in the PurchaseController class. That class contains a series of methods meant to implement key steps in the purchasing process.

Here is a typical example. When a user does the GET request shown above to fetch a list of Offers for auctions they have won, they will be expected to either accept or reject each offer. They will do this via a remote procedure call that involves posting an OfferResponse object to the URL /purchases/offerresponse. Here is the structure of the OfferResponse class:

public class OfferResponse {
  private String purchaseid;
  private String userid;
  private boolean accept;
  private int shippingid;
}

Here is the method in the PurchaseController to implement this remote procedure call:

@PostMapping("/offerresponse")
public ResponseEntity<String> offerresponse(@RequestBody OfferResponse or) {
  if(dao.saveOfferResponse(or))
    return ResponseEntity.status(HttpStatus.CREATED).body("Offer accepted");
  else {
    dao.concludeAuctions();
    return ResponseEntity.status(HttpStatus.ACCEPTED).body("Offer cancelled");
  }
}

The method in the PurchaseDAO class that does the processing here is also interesting. It contains logic to handle both the case where the user accepts the offer and the case where they reject it.

public boolean saveOfferResponse(OfferResponse or) {
  if(or.getAccept()) {
    String sql = "update purchases set shippingid=?, status='Confirmed' where purchaseid=?";
    jdbcTemplate.update(sql,or.getShippingid(),or.getPurchaseid());
    return true;
  } else {
    String searchPurchase = "select * from purchases where purchaseid=?";
    RowMapper<Purchase> rowMapper = new PurchaseRowMapper();
    Purchase result;
    try {
      result = jdbcTemplate.queryForObject(searchPurchase, rowMapper,or.getPurchaseid());
    } catch(Exception ex) {
      result = null;
    }
    if(result != null) {
      String deletePurchaseSQL = "delete from purchases where purchaseid=?";
      jdbcTemplate.update(deletePurchaseSQL);
      String deleteBidSQL = "delete from bids where bidid=?";
      jdbcTemplate.update(deleteBidSQL,result.getBidid());
    }
    return false;
  }
}

Rejecting an offer requires more elaborate processing. In this case we have to look up the details of the purchase, delete that purchase from the table of purchases, and also find the winning bid that lead to this purchase being generated and remove that from the table of bids as well.