POJOs for our simple invoice database

This is a code example of iText PDF, discover more.

30th August 2015
admin-marketing

Switch code for this example

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.



Invoice.java
/*
 * 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 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 getItems() {
        return items;
    }

    public void setItems(List 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();
    }
}
Customer.java
/*
 * 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();
    }
}
Item.java
/*
 * 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();
    }
}
Product.java
/*
 * 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();
    }
}
PojoFactory.java
/*
 * 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 customerCache = new HashMap();
    protected HashMap productCache = new HashMap();
    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 getInvoices() throws SQLException {
        List invoices = new ArrayList();
        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 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 getItems(int invoiceid) throws SQLException {
        List items = new ArrayList();
        getItems.setInt(1, invoiceid);
        ResultSet rs = getItems.executeQuery();
        while (rs.next()) {
            items.add(getItem(rs));
        }
        return items;
    }
}

Resources

invoices.script

images

https://itextpdf.com/sites/default/files/C3F1_0.png
Contact

Still have questions? 

We're happy to answer your questions. Reach out to us and we'll get back to you shortly.

Contact us
Stay updated

Join 11,000+ subscribers and become an iText PDF expert by staying up to date with our new products, updates, tips, technical solutions and happenings.

Subscribe Now