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.
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.
Next: our first JDBC program, JdbcCheckup.java. It's available in $cs636/jdbc, and linked to the class web page.JdbcCheckup.java is available at $cs636/jdbc/JdbcCheckup.java
The driver jar files are also in $cs636/jdbc.
To start, try out Jdbc on our UNIX/Linux systems, to avoid hassle of firewall:
Ex. Login on users or topcat, run JdbcCheckup to Oracle on dbs2
Ex. Login on users or topcat, run JdbcCheckup to MySql on topcat
Note: we are not trying to run Java on dbs2, because its Java software is out of date.
See examples at the end of the JdbcCheckup handout.
At home (using Window, Linux, or MacOSX): run JdbcCheckup to MySql on topcat: first use a tunnel as shown in DatabaseSetup
We need to use “tunnel” to get to Oracle or mysql from home, as shown in that document.
Last time: The idea of the TCP stream connection as a two-way datapipe across the Internet, or between processes on one system.
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 dbs2.cs.umb.edu)
You can login to this Solaris UNIX server and then run Oracle’s sqlplus program to access your account.
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, as shown in the handout code of JdbcCheckup.java.
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: Our installation of mysql on topcat is set to use caseless identifiers.
Pg. 383 alternative ways:
select userid from user where email = ‘firstname.lastname@example.org’;
Select Userid from User whERE eMAIL = ‘email@example.com’;
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 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: