Database.java

package icecream;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.HashMap;

public class Database {
    Connection con;
    Statement st;

    public Database(Connection con) throws SQLException {
        this.con = con;
        st = con.createStatement();

        // st.execute("INSERT INTO toppings VALUES (1,'Chocolate Syup',2.5);");
        // st.execute("INSERT INTO flavors VALUES (1,'Vanilla',3.5);");
        // st.execute("INSERT INTO orders VALUES (1,1, 'N','P','Cone');");
        // st.execute("INSERT INTO ordertoppings VALUES (1,1);");
    }

    public void makeTables() throws SQLException {
        try {
            st.executeQuery("SELECT * FROM flavors WHERE 1=0;");
        } catch (SQLException e) {
            st.executeUpdate("CREATE TABLE flavors (id INT PRIMARY KEY, flavor VARCHAR(64), price MONEY);");
        }

        try {
            st.executeQuery("SELECT * FROM toppings WHERE 1=0;");
        } catch (SQLException e) {
            st.executeUpdate("CREATE TABLE toppings (id INT PRIMARY KEY, topping VARCHAR(64), price MONEY);");
        }

        try {
            //st.execute("INSERT INTO orders VALUES ('Nicholas','Pipitone',1,1,'Cone',GETDATE())");
            st.executeQuery("SELECT * FROM orders WHERE 1=0;");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            st.executeUpdate(
                    "CREATE TABLE orders (id INT IDENTITY(1,1) PRIMARY KEY, fname VARCHAR(64), lname VARCHAR(64), flavorid INT FOREIGN KEY REFERENCES flavors(id), toppingid INT FOREIGN KEY REFERENCES toppings(id), container VARCHAR(64), date DATE);");
        }
    }

    final String[] cols = new String[] { "First name", "Last name", "Flavor", "Topping", "Container", "Price" };

    public String[][] getorders() throws SQLException {
        ResultSet rs;

        rs = st.executeQuery(
                "SELECT *, toppings.price AS toppingPrice, flavors.price AS flavorPrice FROM orders INNER JOIN flavors ON orders.flavorid = flavors.id INNER JOIN toppings ON orders.toppingid = toppings.id;");
        HashMap<Integer, String[]> rows = new HashMap<Integer, String[]>();

        while (rs.next()) {
            int key = rs.getInt("id");
            DecimalFormat df = new DecimalFormat("$0.00");
            rows.put(key,
                    new String[] { rs.getString("fname"), rs.getString("lname"), rs.getString("flavor"),
                            rs.getString("topping"), rs.getString("container"),
                            df.format(rs.getFloat("flavorPrice") + rs.getFloat("flavorPrice")) });
        }

        if (rows.size() != 0) {
            String[][] output = new String[rows.size()][];

            int index = 0;

            for (Integer key : rows.keySet()) {
                output[index] = rows.get(key);
                index++;
            }

            return output;
        } else {
            return null;
        }
    }
}