Output of “tree pizza1” command on Windows:
C:\CS\CS636\PIZZA1
├───.settings
├───build
│
└───classes
│
└───cs636
│
└───pizza
│
├───config
│
├───dao
│
├───domain
│
├───presentation
│
└───service
├───database
├───lib
└───src
└───cs636
└───pizza
├───config
├───dao
├───domain
├───presentation
└───service
C:\cs\cs636>cd piz*1/src/c*/p*/se*
See the Service API plus a few other lines—
C:\cs\cs636\pizza1\src\cs636\pizza\service>find "public" *Service.java
---------- ADMINSERVICE.JAVA
public class AdminService {
public AdminService(DbDAO db, AdminDAO admDAO, PizzaOrderDAO poDAO) {
public void initializeDb() throws ServiceException {
public void addTopping(String name) throws ServiceException {
public void markNextOrderReady() throws ServiceException {
public void advanceDay() throws ServiceException {
public void addPizzaSize(String name) throws ServiceException {
public void removeTopping(String topping) throws ServiceException {
public Set<PizzaOrder> getDailyReport() throws ServiceException {
public void removeSize(String size) throws ServiceException {
public Set<PizzaOrder> getAdminReport() throws ServiceException
public int getCurrentDate() throws ServiceException
public Set<PizzaSize> getPizzaSizes()throws ServiceException
public Set<Topping> getToppings()throws ServiceException
---------- STUDENTSERVICE.JAVA
public class StudentService {
public StudentService(PizzaOrderDAO pizzaDAO, AdminDAO admDAO) {
public int getNextOrderID() throws ServiceException {
public void makeOrder(int roomNum, PizzaSize size, Set<Topping> toppings)
public Set<PizzaOrder> getOrderStatus(int roomNumber) throws ServiceException {
C:\cs\cs636\pizza1\src\cs636\pizza\service>
First concentrate on the service API calls in bold.
Note how the domain objects PizzaSize, Topping, PizzaOrder pass through the service API, back and forth between the presentation and service layers.
The service layer does any needed changing
or creation of domain objects, based on actions specified by the service API
calls. For example the makeOrder code, in the
service layer, creates a PizzaOrder object.
Database load script database/createdb.sql: Works for Oracle, MySql, HSQLDB:
create table pizza_orders(
id integer,
room_number integer,
size_id integer,
day integer,
status integer,
primary key(id));
create table pizza_size(
id integer,
size_name varchar(30),
primary key (id),
unique (size_name));
create table toppings(
id integer,
topping_name varchar(30),
primary key(id),
unique (topping_name));
create table order_topping (
order_id integer,
topping_id integer,
primary key (order_id, topping_id),
foreign key (order_id) references pizza_orders (id),
foreign key (topping_id) references toppings(id));
create table pizza_sys_tab (
current_day integer,
last_order_id integer,
last_report integer);
insert into pizza_sys_tab values (1, 1, 1);
Using
HSQLDB: the in-memory database:
Use “ant start-hsqldb” using pizza1/database/build.xml in an eclipse ant View (or at the command line). This starts the server, and you can use it from anywhere on the system (it’s a TCP server, like any database today)
Using
Oracle
1.
Try
it out directly by logging in to dbs2.cs.umb.edu, using “module load oracle/10g”
(and put this in .cshrc), and using sqlplus.
2.
Use
JdbcCheckup.java to reach it by JDBC from UNIX (any system) and your PC. Read
the comments at the top of JdbcCheckup.java for more info.
3.
For
pizza1 and other projects, set up environment variables ORACLE_USER and
ORACLE_PW as user env vars
on your PC and by adding to your UNIX .cshrc
setenv ORACLE_USER xxx
setenv ORACLE_PW yyy
To
load the pizza1 tables into Oracle and HSQLDB:
Use “ant load-oradb” or “ant load-hsqldb” with build.xml in pizza1/database.
Also
see targets show-oradb, drop-oradb,
etc.
Sample DAO (Data Access Object) Code
Using field connection: JDBC Connection object
public void insertOrder(PizzaOrder order) throws SQLException {
Statement stmt = connection.createStatement();
try {
String sqlString = "insert
into "
+ ORDER_TABLE + " values ("
+ order.getId() + ", " + order.getRoomNumber() + ",
"
+ order.getSize().getId() + ",
" + order.getDay() + ", "
+ order.getStatus() + ") ";
stmt.execute(sqlString);
Set<Topping>
toppings = order.getToppings();
for (Topping t: toppings) {
sqlString = "insert
into " + TOPPING_ORDER_TABLE
+ " values ("
+ order.getId() + ", " + t.getId()+ ") ";;
stmt.execute(sqlString);
}
} finally {
stmt.close();
}
}
// find first order with specified status
public int findFirstOrder(int status) throws SQLException
{
int ordNo;
Statement stmt = connection.createStatement();
try {
ResultSet
set = null;
set
= stmt.executeQuery("select * from "
+ ORDER_TABLE +
" where status = " + status
+ " order
by id");
set.next();
ordNo = set.getInt("id");
} finally {
stmt.close();
}
return ordNo;
}
Harder
case: the following four-way join provides us with a table that has one row for
each order-topping combo. We collect all the toppings up for each pizza order
with the help of a Map from pizza order id to PizzaOrder.
Hibernate will make this a lot easier.
// Get orders, including toppings for a certain day and room
number
//TODO: This doesn't handle topless pizzas correctly
public Set<PizzaOrder> findOrdersByRoom(int roomNumber, int day) throws SQLException
{
//Don't use "to" as a table alias, it's a reserved
word
String sqlString =
// cols 1 2 3 4 5 6
"select o.id, o.size_id,
s.size_name, ot.topping_id,
t.topping_name, o.status
"
+ "from " + ORDER_TABLE + " o, " + PIZZA_SIZE_TABLE + " s, "
+ TOPPING_ORDER_TABLE
+ " ot, " + TOPPING_TABLE + " t "
+ " where o.id = ot.order_id and ot.topping_id = t.id and o.size_id
= s.id"
+ " and o.room_number = " + roomNumber + " and o.day = " + day;
Map<Integer, PizzaOrder> statusMap = new TreeMap<Integer,
PizzaOrder>();
Statement stmt = connection.createStatement();
try {
ResultSet
table = stmt.executeQuery(sqlString);
while (table.next()) {
int ordNo = table.getInt(1);
PizzaOrder
order = null;
int toppingId = table.getInt(4);
String toppingName = table.getString(5);
Topping topping = new Topping(toppingId,
toppingName);
if ((order =
(PizzaOrder) statusMap.get(new Integer(ordNo))) != null) {
order.addTopping(topping);
} else {
Set<Topping>
toppings = new TreeSet<Topping>();
toppings.add(topping);
PizzaSize size = new PizzaSize(table.getInt(2), table.getString(3));
int status = table.getInt(6);
order = new PizzaOrder(ordNo,
roomNumber, size, toppings, day, status);
statusMap.put(ordNo, order);
}
}
} finally {
stmt.close();
}
return new TreeSet<PizzaOrder>(statusMap.values());
// Collection really was a Set
}