HW1 is due next Monday: questions?
Homework 1 is linked from the class web page www.cs.umb.edu/cs636, also known as $cs636.
If you are logged in on a UNIX or Linux system at cs.umb.edu, you can see this directory at /data/htdocs/cs636, so $cs636 can be thought of as an abbreviation for www.cs.umb.edu/cs636 or /data/htdocs/cs636.
Software architecture of all apps for this course: three layers--
This important picture corresponds to the figure on p. 17 of Murach, Figure 1-7. The presentation code calls the service layer code, the service layer code calls the data access code. The data access code uses JDBC to talk to the database. Then data is returned by the calls up the layers.
Domain Objects. Data used by the application is held in "domain objects", for example PizzaOrder objects, PizzaTopping objects, etc. One PizzaOrder object needs access to multiple PizzaTopping objects to model the idea that pizzas can have multiple toppings. This is done by have a field of PizzaOrder that holds a Set<PizzaTopping> or List<PizzaTopping>, and the actual objects for this set or list itself contains multiple references to the associated PizzaTopping objects. See PizzaOrder.java and PizzaTopping.java in the available pizza1 project.Note that Figure 1-7 does not make clear that the domain objects can move between layers, so it’s a good idea to add the up-and-down arrow as in the above picture to Figure 1-7 to emphasize this important fact. In fact, we don't want all the domain objects to reach into the presentation layer, as we will discuss.
Idea of impedance mismatch: Java objects vs. database tables
In Java, we use graphs of objects. In the database, we use tables, a different way to hold data. How can we hold object data in the database?Simple case: no problem. For Employee objects and employees table, each Java object has one row in the table.
More complicated case: a Pizza object has a Set<PizzaTopping> to describe its toppings. So one pizza can have many toppings. Also, one topping can be used in several different pizzas. What database tables are needed?Database modeling for pizza orders: since one pizza can have multiple toppings, and a topping can be used in multiple pizzas, this could be handled is an N-N relationship, and that way would need a relationship table in the database as well.
However, the toppings are managed on their own in this app. You can add a topping so that future pizzas can use that new topping, and delete old toppings due to lack of supplies or demand.
There are two concepts of "topping" here: a topping on the menu, which can be deleted, and a topping already in use for a pizza order, and no longer deletable.
So we will have PizzaToppings and MenuToppings. This allows a PizzaOrder to own its PizzaToppings for its own lifetime, and avoids the N-N relationship in the model. There is a N-1 relationship from PizzaOrder to its owned PizzaToppings.
For this situation, the DAO layer is expected to be able to query the database for all the information needed to construct a certain PizzaOrder object. It has to query the database tables. In the other direction, the DAO is passed a PizzaOrder object and told to save it in the database. The DAO has to do inserts on the three tables. We’ll look at the details later.
We see that managing the Java to/from DB data flow is a little more complex than just matching up a db row to a particular object.
If the app Java classes have an inheritance hierarchy, it’s a harder case. There is no single way to express inheritance relationships in database tables, but rather several competing ways. Designing done in database terms tends to avoid using inheritance. Designing nice inheritance structures in the object world means harder work doing the database design. It’s good to have the help of a system like JPA/Hibernate or JPA/Eclipselink to help with this. We will avoid using inheritance among domain objects in this class.
The Pizza Project, our running worked-out example:
pizza1: client-server/JDBC <---now available, linked to class web page!
pizza2: client-server with transactions and JPA
pizza3: web app with JSP and dispatcher servlet to provide MVC
Our Project: Music Website for a Band: music1, music2, music3
The band has multiple CDs to sell, so a CD is a Product.
Each CD has multiple Tracks, each with a song (or other content)
Website visitors can be registered by name and email (their unique id), with passwords
To track visitors, user registration is required before track samples can be played (downloaded) for free.
CDs can be bought, and this is handled by the usual idea of a “shoppng cart”, Cart for short, which can hold one or more CDs.
A visitor does not need to be registered to have a Cart, only to “check out”, that is, actually buy the CDs, or download samples.
A visitor can get the Cart displayed, and remove individual CDs from the cart if desired.
How does a user add a CD to the Cart? There is one page describing a certain CD and another page for working with the Cart.
Answer: there’s only one cart, so it’s much easier to have “add to Cart” on the Product page than “add a certain CD to cart” on the cart page. The Cart page has an option to “Browse catalog”, allowing the user to find specific CDs to buy.
SQL92 Entry Level is claimed by all major DB’s
- Standard covers
tables, view, select, insert, delete, etc.
- But not users, catalog tables
We will use Oracle, MySQL, and HSQLDB (memory-only database)
Compared to PHP: PHP has a portable API, but no good implementation of it for Oracle
One database at a site (on dbs3.cs.umb.edu), accessed from anywhere on the Internet.
Each user gets a schema (a container/namespace that holds tables, etc.), separate from other users.
User eoneil can have an orders table
eoneil.orders long-form table name including schema name ^^^^^^ ^^^^^ schema table
and also user joe can have an orders table
User eoneil can refer to this table as simply “orders”, and similarly joe can too, and they are each accessing different tables.
Running HSQLDB: no “real db”, just memory structures (the way we use it anyway)
First run the HSQLDB server in one window, then run JdbcCheckup in another window, as detailed in header comment of JdbcCheckup.java.
The client and server communicate using TCP/IP, so the server uses a certain TCP port. If you or someone else tries to start another instance of the server on the same system, it will fail because the first one has the port in use. But this is unlikely to happen in practice, because we will be developing on our individual home PCs.
Note the discussion on pg. 349 of how version 5.5 of MySQL has overcome previous deficiencies to become a “real” database, offering relational integrity (FKs that work) and transactions by default. We are using version 5.6 on topcat.
MySQL still has some problems with case-sensitive identifiers (table names, column names, etc.). By the SQL standard, all identifiers are caseless, but by default on Linux and other UNIX (but not MacOSX) systems, MySQL table and database names are case-sensitive. I have overridden this bad behavior on topcat by setting lower_case_names = 1 in /etc/mysql/my.cnf. If you have a Linux development system with your own mysql installed (not required for this course), set this in mysql/bin/my.ini.
Section on “Two ways to interact”:
p. 362 How to create a database—I’ll do this for you. UNIX user joe will get database joedb.
p. 364: How to create a table: this shows useful SQL examples. Continue to the end of the chapter looking at SQL examples.
Chap 12 JDBC read this to p. 387 for now.
Database drivers: I’ve downloaded the MySQL driver. It and the Oracle and HSQLDB drivers are in $cs636/jdbc, where $cs636 stands for www.cs.umb.edu/cs636, the class’s web home page.
p. 381: how to connect. We will use the newer method
p. 383: how to do a SQL select statement, queries fill a ResultSet object with rows.
Note that from these examples it looks like mysql is case-sensitive for table and column names, whereas standard SQL has caseless identifiers. However, with the proper setup, you can use any case with mysql: Our installation of mysql on topcat is set to use caseless identifiers.
Pg. 383 alternative ways:
select userid from user where email = ‘email@example.com’;
Select Userid from User whERE eMAIL = ‘firstname.lastname@example.org’;
The table data is always case sensitive.
p. 385: how to get values out of rows in the ResultSet
Note: This coverage ignores the exception handling needed to actually code with JDBC. Need full examples, such as JdbcCheckup.java.
Also uses this code uses double for money variable, not great.
JDBC API is past of standard Java library or “JDK”, so no special imports or jar files are needed to use it.
But it needs a “driver” for the DB
- we need an Oracle Driver
ojdbc6.jar for Java 5/6+ (There is an ojdbc7.jar now, but it has the same code, just compiled with Java 7, no advantage, and a definite disadvantage if you are stuck with Java 6 yourself.)
- also one for mysql longname.jar, and hsqldb.jar
Both of these work on UNIX/Linux, Mac, and Windows (Java portability!)
In reading the Java tutorial—
To start, read sections on “Setting Up Tables” and “Retrieving and Modifying Values from ResultSets”
This also uses float/double for price, although apologizes for it. We'll use BigDecimal.
JDBC features we don’t need, at least for now: