POJOs for our simple invoice database

Tags: ZUGFeRDpublish databaseiText 5

In the context of the book ZUGFeRD: The Future of Invoicing, we need a simple invoice database. This database has the following four tables:

We'll write a POJO for each of these four tables and we'll create a POJO factory to access the database.

Files: 
/*
 * Part of a set of classes based on a sample database.
 */
package zugferd.pojo;
 
import java.util.Date;
import java.util.List;
 
/**
 * Plain Old Java Object containing info about an Invoice.
 * @author Bruno Lowagie (iText Software)
 */
public class Invoice {
    protected int id;
    protected Customer customer;
    protected double total;
    protected List<Item> items;
    protected Date invoiceDate;
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public Customer getCustomer() {
        return customer;
    }
 
    public void setCustomer(Customer customer) {
        this.customer = customer;
    }
 
    public double getTotal() {
        return total;
    }
 
    public void setTotal(double total) {
        this.total = total;
    }
 
    public List<Item> getItems() {
        return items;
    }
 
    public void setItems(List<Item> items) {
        this.items = items;
    }
 
    public Date getInvoiceDate() {
        return invoiceDate;
    }
 
    public void setInvoiceDate(Date invoiceDate) {
        this.invoiceDate = invoiceDate;
    }
 
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append("Invoice id: ").append(id).append(" Date: ").append(invoiceDate).append(" Total cost: ").append(total).append("\u20ac\n");
        sb.append("Customer: ").append(customer.toString()).append("\n");
        for (Item item : items) {
            sb.append(item.toString()).append("\n");
        }
        return sb.toString();
    }
}
/*
 * Part of a set of classes based on a sample database.
 */
package zugferd.pojo;
 
/**
 * Plain Old Java Object containing info about a Customer.
 * @author Bruno Lowagie (iText Software)
 */
public class Customer {
    protected int id;
    protected String firstName;
    protected String lastName;
    protected String street;
    protected String postalcode;
    protected String city;
    protected String countryId;
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getFirstName() {
        return firstName;
    }
 
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
 
    public String getLastName() {
        return lastName;
    }
 
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
 
    public String getStreet() {
        return street;
    }
 
    public void setStreet(String street) {
        this.street = street;
    }
 
    public String getCity() {
        return city;
    }
 
    public void setCity(String city) {
        this.city = city;
    }
 
    public String getPostalcode() {
        return postalcode;
    }
 
    public void setPostalcode(String postalcode) {
        this.postalcode = postalcode;
    }
 
    public String getCountryId() {
        return countryId;
    }
 
    public void setCountryId(String countryId) {
        this.countryId = countryId;
    }
 
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(id).append("\n");
        sb.append("    First Name: ").append(firstName).append("\n");
        sb.append("    Last Name: ").append(lastName).append("\n");
        sb.append("    Street: ").append(street).append("\n");
        sb.append("    City: ").append(countryId).append(" ").append(postalcode).append(" ").append(city);
        return sb.toString();
    }
}
/*
 * Part of a set of classes based on a sample database.
 */
package zugferd.pojo;
 
/**
 * Plain Old Java Object containing info about an Item.
 * @author Bruno Lowagie (iText Software)
 */
public class Item {
    protected int item;
    protected Product product;
    protected int quantity;
    protected double cost;
 
    public int getItem() {
        return item;
    }
 
    public void setItem(int item) {
        this.item = item;
    }
 
    public Product getProduct() {
        return product;
    }
 
    public void setProduct(Product product) {
        this.product = product;
    }
 
    public int getQuantity() {
        return quantity;
    }
 
    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }
 
    public double getCost() {
        return cost;
    }
 
    public void setCost(double cost) {
        this.cost = cost;
    }
 
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append("  #").append(item);
        sb.append(product.toString());
        sb.append("\tQuantity: ").append(quantity);
        sb.append("\tCost: ").append(cost).append("\u20ac");
        return sb.toString();
    }
}
/*
 * Part of a set of classes based on a sample database.
 */
package zugferd.pojo;
 
/**
 * Plain Old Java Object containing info about a Product.
 * @author Bruno Lowagie (iText Software)
 */
public class Product {
    protected int id;
    protected String name;
    protected double price;
    protected double vat;
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public double getPrice() {
        return price;
    }
 
    public void setPrice(double price) {
        this.price = price;
    }
 
    public double getVat() {
        return vat;
    }
 
    public void setVat(double vat) {
        this.vat = vat;
    }
 
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append("\t(").append(id).append(")\t").append(name).append("\t").append(price).append("\u20ac\tvat ").append(vat).append("%");
        return sb.toString();
    }
}
/*
 * Part of a set of classes based on a sample database.
 */
package zugferd.pojo;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
 
/**
 * Factory that creates Invoice, Customer, Product, and Item classes.
 * @author Bruno Lowagie (iText Software)
 */
public class PojoFactory {
 
    protected static PojoFactory factory = null;
    protected Connection connection;
    protected HashMap<Integer, Customer> customerCache = new HashMap<Integer, Customer>();
    protected HashMap<Integer, Product> productCache = new HashMap<Integer, Product>();
    protected PreparedStatement getCustomer;
    protected PreparedStatement getProduct;
    protected PreparedStatement getItems;
 
    private PojoFactory() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbcDriver");
        connection = DriverManager.getConnection(
            "jdbc:hsqldb:resources/zugferd/db/invoices", "SA", "");
        getCustomer = connection.prepareStatement("SELECT * FROM Customer WHERE id = ?");
        getProduct = connection.prepareStatement("SELECT * FROM Product WHERE id = ?");
        getItems = connection.prepareStatement("SELECT * FROM Item WHERE invoiceid = ?");
    }
 
    public static PojoFactory getInstance() throws SQLException {
        if (factory == null || factory.connection.isClosed()) {
            try {
                factory = new PojoFactory();
            } catch (ClassNotFoundException cnfe) {
                throw new SQLException(cnfe.getMessage());
            }
        }
        return factory;
    }
 
    public void close() throws SQLException {
        connection.close();
    }
 
    public List<Invoice> getInvoices() throws SQLException {
        List<Invoice> invoices = new ArrayList<Invoice>();
        Statement stm = connection.createStatement();
        ResultSet rs = stm.executeQuery("SELECT * FROM Invoice");
        while (rs.next()) {
            invoices.add(getInvoice(rs));
        }
        stm.close();
        return invoices;
    }
 
    public Invoice getInvoice(ResultSet rs) throws SQLException {
        Invoice invoice = new Invoice();
        invoice.setId(rs.getInt("id"));
        invoice.setCustomer(getCustomer(rs.getInt("customerid")));
        List<Item> items = getItems(rs.getInt("id"));
        invoice.setItems(items);
        double total = 0;
        for (Item item : items)
            total += item.getCost();
        invoice.setTotal(total);
        invoice.setInvoiceDate(rs.getDate("invoicedate"));
        return invoice;
    }
 
    public Item getItem(ResultSet rs) throws SQLException {
        Item item = new Item();
        item.setItem(rs.getInt("Item"));
        Product product = getProduct(rs.getInt("ProductId"));
        item.setProduct(product);
        item.setQuantity(rs.getInt("Quantity"));
        item.setCost(item.getQuantity() * product.getPrice());
        return item;
    }
 
    public Customer getCustomer(int id) throws SQLException {
        if (customerCache.containsKey(id))
            return customerCache.get(id);
        getCustomer.setInt(1, id);
        ResultSet rs = getCustomer.executeQuery();
        if (rs.next()) {
            Customer customer = new Customer();
            customer.setId(id);
            customer.setFirstName(rs.getString("FirstName"));
            customer.setLastName(rs.getString("LastName"));
            customer.setStreet(rs.getString("Street"));
            customer.setPostalcode(rs.getString("Postalcode"));
            customer.setCity(rs.getString("City"));
            customer.setCountryId(rs.getString("CountryID"));
            customerCache.put(id, customer);
            return customer;
        }
        return null;
    }
 
    public Product getProduct(int id) throws SQLException {
        if (productCache.containsKey(id))
            return productCache.get(id);
        getProduct.setInt(1, id);
        ResultSet rs = getProduct.executeQuery();
        if (rs.next()) {
            Product product = new Product();
            product.setId(id);
            product.setName(rs.getString("Name"));
            product.setPrice(rs.getDouble("Price"));
            product.setVat(rs.getDouble("Vat"));
            productCache.put(id, product);
            return product;
        }
        return null;
    }
 
    public List<Item> getItems(int invoiceid) throws SQLException {
        List items = new ArrayList<Item>();
        getItems.setInt(1, invoiceid);
        ResultSet rs = getItems.executeQuery();
        while (rs.next()) {
            items.add(getItem(rs));
        }
        return items;
    }
}
File nameRaw URLUpdated
Invoice.javaInvoice.java2015-08-30 10:36 am
Customer.javaCustomer.java2015-08-30 10:36 am
Item.javaItem.java2015-08-30 10:36 am
Product.javaProduct.java2015-08-30 10:36 am
PojoFactory.javaPojoFactory.java2015-08-30 10:36 am
Resources: 
File nameRaw URLUpdated
invoices.scriptinvoices.script2015-08-30 10:37 am