3. A simple invoice database

Usually, all the data about your products, customers, sales, pricing, and so on, will be stored in a Customer Relationship Management (CRM) system. As this is a tutorial with examples that anyone should be able to download and execute without depending on a specific CRM, we've created a very basic invoice database that can be accessed using Hyper SQL Database (hsqldb).

Database diagram

Figure 3.1 shows the minimal set of tables and fields that we'll use for the examples in the next couple of chapters.

Figure 3.1: Simple invoice database schema
Figure 3.1: Simple invoice database schema

We'll work with only four tables:

  • INVOICE: every record contains an id and an invoice date. The customer id refers to the CUSTOMER table.

  • CUSTOMER: every record contains an id, a first name, last name, street, postal code, city and country id.

  • ITEM: an invoice will involve one or more items. The invoice id refers to the INVOICE table; item is a sequential number for each separate invoice line. Product id refers to the PRODUCT table; the quantity field tells you how many products are being purchased.

  • PRODUCT: every record contains an id, a name, a price and a VAT percentage.

In the real world, CRM databases contain much more information, but for the sake of this tutorial, we want to keep the complexity as low as possible.

  • We won't create a country table,

  • We'll assume that the customers are individuals without a company id,

  • We won't store the address of the seller in our database,

  • And so on.

This is not a tutorial on how to build a CRM system. We just need some data for our examples.

We will access our database using JDBC, and to make things simple, we'll work with a series of Plain Old Java Objects (POJO).

Creating database POJOs

We start by creating the classes Invoice, Customer, Item, and Product. These are our POJOs: each class corresponds with a table in our database and consists of a series of member-variables (one for each field) and the corresponding getters and setters.

For instance: the body of the Product class looks like this:

  1. // member-variables
  2. protected int id;
  3. protected String name;
  4. protected double price;
  5. protected double vat;
  6. // getters and setters
  7. public int getId() {
  8. return id;
  9. }
  10. public void setId(int id) {
  11. this.id = id;
  12. }
  13. public String getName() {
  14. return name;
  15. }
  16. public void setName(String name) {
  17. this.name = name;
  18. }
  19. public double getPrice() {
  20. return price;
  21. }
  22. public void setPrice(double price) {
  23. this.price = price;
  24. }
  25. public double getVat() {
  26. return vat;
  27. }
  28. public void setVat(double vat) {
  29. this.vat = vat;
  30. }

We'll also add a toString() method that renders this content as a String value:

  1. public String toString() {
  2. StringBuilder sb = new StringBuilder();
  3. sb.append("\t(").append(id).append(")\t").append(name).append("\t")
  4. .append(price).append("\u20ac\tvat ").append(vat).append("%");
  5. return sb.toString();
  6. }

Once we have created such a class for every table, we create a series of queries that will return Invoice objects. An Invoice object contains a Customer object and a list of Item objects, each of which refers to a Product object.

Creating a POJO factory

When working with hsqldb, all the data is stored in a .script file. In our case, the file is named invoices.script. We'll write a PojoFactory class that connects to this database and that initializes a series of prepared statements:

  1. protected Connection connection;
  2. protected HashMap<Integer, Customer> customerCache
  3. = new HashMap<Integer, Customer>();
  4. protected HashMap<Integer, Product> productCache
  5. = new HashMap<Integer, Product>();
  6. protected PreparedStatement getCustomer;
  7. protected PreparedStatement getProduct;
  8. protected PreparedStatement getItems;
  9. // constructor
  10. private PojoFactory() throws ClassNotFoundException, SQLException {
  11. Class.forName("org.hsqldb.jdbcDriver");
  12. connection = DriverManager.getConnection(
  13. "jdbc:hsqldb:resources/zugferd/db/invoices", "SA", "");
  14. getCustomer = connection.prepareStatement(
  15. "SELECT * FROM Customer WHERE id = ?");
  16. getProduct = connection.prepareStatement(
  17. "SELECT * FROM Product WHERE id = ?");
  18. getItems = connection.prepareStatement(
  19. "SELECT * FROM Item WHERE invoiceid = ?");
  20. }

Let's take a closer look at the member-variables in the PojoFactory class:

  • In line 1, we have a connection object that is an object of type java.sql.Connection. We load the hsqldb database driver in line 11 and create the connection in lines 12 and 13. In our case, the invoices.script is stored in the folder db, which is a subdirectory of the folder resources which is in turn a subdirectory of the working directory of our Java Virtual Machine (JVM). The username and password of that database are "SA" and "".

  • In lines 2-3 and 4-5, we create a cache for Customer objects and a cache for Product objects by storing an integer (the id of a record) and the object containing the data of a record in HashMap objects. That way, we won't always have to execute a query on the database when a Customer or a Product is needed more than once; we can just fetch it from the HashMap that caches these objects. Obviously, this will only work if we never change any record in our database. Please keep in mind that this is just a demo database and some demo code. In the real world, your database and database access may be completely different.

  • In lines 6, 7, and 8, we have three prepared statements. These are defined in lines 14-15, 16-17, and 18-19. As you can see, these are simple SELECT statements that return all the fields from the records in the tables CUSTOMER, PRODUCT, and ITEM based in an id.

You can get all the invoices at once, using the getInvoices() method. This method selects all the fields from all the records in the INVOICE table:

  1. public List<Invoice> getInvoices() throws SQLException {
  2. List<Invoice> invoices = new ArrayList<Invoice>();
  3. Statement stm = connection.createStatement();
  4. ResultSet rs = stm.executeQuery("SELECT * FROM Invoice");
  5. while (rs.next()) {
  6. invoices.add(getInvoice(rs));
  7. }
  8. stm.close();
  9. return invoices;
  10. }

The getInvoice() method will perform several sub-queries:

  1. public Invoice getInvoice(ResultSet rs) throws SQLException {
  2. Invoice invoice = new Invoice();
  3. invoice.setId(rs.getInt("id"));
  4. invoice.setCustomer(getCustomer(rs.getInt("customerid")));
  5. List<Item> items = getItems(rs.getInt("id"));
  6. invoice.setItems(items);
  7. double total = 0;
  8. for (Item item : items)
  9. total += item.getCost();
  10. invoice.setTotal(total);
  11. invoice.setInvoiceDate(rs.getDate("invoicedate"));
  12. return invoice;
  13. }

You see that we even calculate the total sum of the invoice. This is a value that you'd usually store in your database redundantly, to avoid that the total invoice price of an old invoice changes when you introduce new prices for products mentioned on that old invoice. Once again: we have kept our database as minimal as possible.

The Customer object that corresponds with this invoice, is obtained with the getCustomer() method:

  1. public Customer getCustomer(int id) throws SQLException {
  2. if (customerCache.containsKey(id))
  3. return customerCache.get(id);
  4. getCustomer.setInt(1, id);
  5. ResultSet rs = getCustomer.executeQuery();
  6. if (rs.next()) {
  7. Customer customer = new Customer();
  8. customer.setId(id);
  9. customer.setFirstName(rs.getString("FirstName"));
  10. customer.setLastName(rs.getString("LastName"));
  11. customer.setStreet(rs.getString("Street"));
  12. customer.setPostalcode(rs.getString("Postalcode"));
  13. customer.setCity(rs.getString("City"));
  14. customer.setCountryId(rs.getString("CountryID"));
  15. customerCache.put(id, customer);
  16. return customer;
  17. }
  18. return null;
  19. }

If the id is found in the cache, we return the corresponding Customer object. If not, we perform a query using one of our prepared statements, and we store the resulting Customer instance in the cache before returning it.

When populating the Invoice object, we get a List of Item objects using the getItems() method:

  1. public List<Item> getItems(int invoiceid) throws SQLException {
  2. List items = new ArrayList<Item>();
  3. getItems.setInt(1, invoiceid);
  4. ResultSet rs = getItems.executeQuery();
  5. while (rs.next()) {
  6. items.add(getItem(rs));
  7. }
  8. return items;
  9. }

This method calls the getItem() method for every invoice line that belongs to a specific Invoice:

  1. public Item getItem(ResultSet rs) throws SQLException {
  2. Item item = new Item();
  3. item.setItem(rs.getInt("Item"));
  4. Product product = getProduct(rs.getInt("ProductId"));
  5. item.setProduct(product);
  6. item.setQuantity(rs.getInt("Quantity"));
  7. item.setCost(item.getQuantity() * product.getPrice());
  8. return item;
  9. }

The getItem() method also fetches the corresponding Product:

  1. public Product getProduct(int id) throws SQLException {
  2. if (productCache.containsKey(id))
  3. return productCache.get(id);
  4. getProduct.setInt(1, id);
  5. ResultSet rs = getProduct.executeQuery();
  6. if (rs.next()) {
  7. Product product = new Product();
  8. product.setId(id);
  9. product.setName(rs.getString("Name"));
  10. product.setPrice(rs.getDouble("Price"));
  11. product.setVat(rs.getDouble("Vat"));
  12. productCache.put(id, product);
  13. return product;
  14. }
  15. return null;
  16. }

Now we have everything we need to retrieve all the invoice data that is stored in our very simple invoice database.

Testing the database

Before we use this database to create ZUGFeRD XMLs and invoices, let's run the DatabaseTest example to test the database:

  1. public static void main(String[] args) throws SQLException {
  2. PojoFactory factory = PojoFactory.getInstance();
  3. List<Invoice> invoices = factory.getInvoices();
  4. for (Invoice invoice : invoices)
  5. System.out.println(invoice.toString());
  6. factory.close();
  7. }

We use the PojoFactory to get a List of Invoice objects and we write the content of such an object to the System.out. The result will be a sequence of text snippets (one for each invoice) that look like this:

Invoice id: 4 Date: 2015-04-01 Total cost: 1507.0€
Customer: 30
    First Name: Bill
    Last Name: Sommer
    Street: 362 - 20th Ave.
    City: BE 9000 Ghent
  #0  (28)   Running jersey      8.0€   vat 21.0%  Quantity: 9  Cost: 72.0€
  #1  (35)   Golf polo           8.0€   vat 21.0%  Quantity: 1  Cost: 8.0€
  #2  (41)   Threadmill          600.0€ vat 21.0%  Quantity: 2  Cost: 1200.0€
  #3  (23)   Pro steel dartboard 25.0€  vat 21.0%  Quantity: 2  Cost: 50.0€
  #4  (9)    My First Cookbook   17.0€  vat 6.0%   Quantity: 1  Cost: 17.0€
  #5  (37)   Golf kit            80.0€  vat 21.0%  Quantity: 2  Cost: 160.0€

This already looks more or less like an invoice, doesn't it? Now let's find out how all this data fits into the ZUGFeRD data model.