CS436/636 - Database Application Development, Fall 2017

This class will cover database applications using SQL and Java as the main programming languages. We will start with simple Java line-oriented user interaction for database applications, and later cover web-based GUIs, working up to web-enabled applications involving database back ends. You need proficient Java programming skills and CS430/630 for SQL, or an equivalent introductory database course covering SQL, E-R diagrams, and client programming (in C, Java or PL/SQL) including elementary transactions. It is good to know basic HTML, including forms, and basic XML, but these topics will be covered in homework. There are plenty of tutorials for HTML and XML on the Web.

Class meets MW 4:00-5:15 in W-1-0068
Professor: Betty O’Neil (eoneil at cs.umb.edu)
Office hours: MW 3:30-3:45, 5:30-6:45 in S-3-169
Prerequisites: CS210, CS630 (these are important), and officially, CS615 (less important, can be replaced by programming experience)

Teaching Assistant: Adit Shah
Office hours: Mon 1:00-2:00 in S-3-169

Note that I also teach CS437/637 Database-Backed Web Sites and Web Services, another web applications course, usually in the spring. You may take both classes if you wish. Both classes use databases for holding persistent data and use HTML5 and MVC for web UI, so there is some overlap. If you want to take both classes in different terms, please consider taking cs637 first, since it is more elementary. Here is a quick comparison of these classes:

CS436/636 CS437/637
uses Java for business logic uses PHP for business logic
requires Java expertise (cs210 or equivalent) requires intermediate Java (cs110 or equivalent)
focus on large or enterprise sites focus on smaller sites
basic HTML5, minimal CSS more HTML5, CSS
layered architecture (presentation/service/DAO) simpler sites, less layering emphasis
use MVC (Model/View/Controller) pattern for web UI use MVC (Model/View/Controller) pattern for web UI
use Oracle or mysql interchangeably use mysql
default to using eclipse IDE default to using NetBeans IDE
run your own tomcat application server run XAMPP, your own Apache webserver, mysql, PHP site
only discuss web services study and implement web services

If you are interested in preparing to use .NET, you should probably take CS436/636, since .NET is an enterprise platform, like JEE.


Syllabus 

Student Information sheet, with list of essential skills needed for this course
 
Text: print edition required for open-book exam use
Java Servlets and JSP , third edition, by Joel Murach and Andrea Steelman, Murach & Assoc, 2014, ISBN 978-1-890774-44-8, available for example at Amazonor the bookstore. This book cover basic web technology (HTTP, HTML), servlets, JSP, using the MySQL database, JDBC and tomcat. Follow the book link for free sample chapters and code.

Getting Ready:
Check out your development PC: A Windows 7 system with at least 2GB of memory works well with these tools. Windows 8 or 10 is also OK. Alternatively, a Linux system or recent MacOSX system will work well. Setup directions are provided for Windows, Linux, and MacOSX.

First week: Get a UNIX/Linux account for cs636 by running apply, even if you already have a UNIX account here. This should be done end of the first week to list your username for Oracle and mysql accounts. Follow the Java/ant setup instructions for UNIX/Linux and your own home system.

Class Notes (final versions posted after class)

Wed., Sept. 6 notes Intro, concepts of client-server apps vs. web apps
Fri., Sept. 8 3pm: Oracle and mysql accounts have been set up for those with class accounts already. See DatabaseSetup for more info.
Mon, Sept. 11 notes Software layers, intro to our projects and databases, JDBC.
Tues., Sept. 12: 1:30pm Created more Oracle and mysql accounts
Wed., Sept. 13 notes JDBC, XML, ant and its build.xml
Mon., Sept. 18 notes ant, pizza1's layers and the service API
Wed., Sept. 20 notes pizza1's database and domain objects
Mon., Sept.25 notes pizza1's DAO API, looking at calls down the layers 
Wed., Sept. 27 notes Setting up the infrastructure for pizza1, intro to music1 music1 schema
Mon., Oct. 2 notes Software architecture of layered web apps
Wed., Oct. 4 notes The music1 project
Wed., Oct. 11 notes More on the project, intro to JUnit
Mon., Oct. 16 notes More on packages, intro to transactions
Wed., Oct. 18 notes Transaction rollback on error, JDBC for transactions, Project notes, HTTP Chap 18 slides (6pp)
Mon., Oct. 23 notes HTTP, intro to tomcat, JPA Chap. 13 slides (6pp)
Wed., Oct. 26 notes Debugging with eclipse, more on  JPA, including pizza2 slides (6pp)
Mon., Oct. 30 notes Pizza2, the JPA version of pizza1
Wed., Nov. 1 notes Intro to Servlets (Chap. 2) slides (6pp)
Mon., Nov. 6 notes Midterm Review
Wed. Nov. 8 Midterm Exam (open print books, notes, posted materials, closed electronic devices)
Midterm Review on Layers
Practice midterm exam (Solution) Makeup Midterm Solution
Mon., Nov. 13 notes Servlets (Chap. 5) slides (6pp)
Wed., Nov. 15 notes More on servlets, request variables, intro to session variables (Chap. 7) slides (6pp
Mon., Nov. 20 notes Pizza3 web app, DataSource Connection Pooling, JNDI UsingDBfromWebApp
Wed., Nov. 22 no class
Mon., Nov. 27 notes The MVC servlets in pizza3
Wed., Nov. 29 notes Webapp concurrency and correctness, webapp initialization, SQL injection attacks
Mon., Dec. 4 notes Scaling up, Microservice architecture
Wed., Dec. 6 notes More on Servlet URLs, Caching, Internationalization
Mon., Dec. 11 notes REST Web Services in Java, JAX-RS

Wed., Dec. 20, 3:00-6:00pm in W-2-200 Final Exam Practice Final Exam

Homework and Programming Assignments

Homework 1 Due Monday, Sept. 18, at start of class, on paper. Review, Setup, and Intro to JDBC Solution
Homework 2 due Wednesday, Sept. 27, in class, on paper Java Packages, XML, Ant, HTML Forms, Pizza Project Solution
pizza1(zip) Pizza Project, provided example project, client-server version
pizza2(zip) Pizza Project, JPA client-server version (updated 10/30 to remove unneeded code in findOrdersByRoom)
Homework 3 due Monday, Oct. 16 (slipped to Wed. Oct. 18 because of network downtime) in class, on paper (problems 1-5) Music DB, Page Flow for pizza, API Design, Java Exceptions Solution (including #6)
Homework 3 problem 6 (separately graded, 10 points) A start on PA1, due Friday, Oct. 20 by midnight in your cs636/pa1a directory
music1-setup(zip) Updated Oct. 11 to fix end-of-lines in run*.sh (more info) and provide Java-7-compatible hsqldb.jar
Programming Assignment 1 (music1 project) due Sunday Oct. 29 by midnight in your cs636/pa1b directory
music1 (zip) Solution to pa1b
Homework 4 due Wed, Nov. 29 in class, on paper, HTTP, JPA, Intro Servlets, tomcat (updated 11/16) Solution
Programming Assignment 2 (music3 project) due Sun., Dec. 10 in your cs636/pa2 directory
pizza3 (zip) Web app version of the pizza project
music3-setup (zip) Web app music3 project setup
Homework 5 due Wed., Dec. 6 in class, on paper. MVC webapps, debugging, pizza3, optionally using eclipse tomcat Solution

Resources

Piazza Class Site

tomcat-8.5.zip Linux tomcat setup instructions Your port assignment

Notes, etc. from Fall, 2016

Access to cs.umb.edu systems from offsite Logging in and transferring files.

Student Information Form (in case you missed the first class) 

Development Setup Installing Java and ant (if necessary) on your home system. Installing eclipse. A few things needed on cs.umb.edu hosts as well.

Database Setup Setting up direct SQL access for Oracle, setting up the needed tunnels to our database servers, using eclipse to access databases.

Java We are using version 7 or 8. JDK API Java classpath explained. javac tool doc java tool doc

eclipse : We are using version Oxygen, of the Eclipse IDE for Java EE Developers Advanced Java debugging tips

ant: We are using version 1.8 or 1.9. Apache Ant documentation site. For the tutorial, select the topic "Developing with Ant", and within that, "Hello World with Ant".

SQL Try out SQL queries at sqlzoo.net, Also see product SQL manuals below under Oracle, Mysql, HSQLDB. We will write portable SQL, so one script will work on all three databases, as much as possible.

JDBC: jdbc directory for JdbcCheckup and drivers tutorial Note that the JDBC API is part of the standard Java library. Chap. 12 of Murach has useful coverage.

HTML5: Intro at www.w3schools.com forms tutorial tag reference, with HTML 4/5 info  HTML5 validator

Living without Javascript: CSS can do more than you might think: photo carosel (wait a bit for this to start) dropdown in menu bar (also www.cs.umb.edu

Javascript book for no-credit reading course: Learning Web App Development, by Semmy Purewal (best to get a print copy, for example $26 at amazon.com) More links.

XML: XML tutorial at www.w3schools.com. We need basic XML for ant from the start, and for JPA configuration later

JUnit4: We are using version 4.12. JUnit4 in eclipse tutorial Calculator example project (zip)

Oracle: We are using version 12c. Sign up (free) for Oracle Technical Network. Oracle 12c documentation (SQL) Oracle error messages info.

MySQL MySql Setup We are using version 5.6 on topcat.cs.umb.edu. docs (SQL)

HSQLDB : We are using version 2.3.4. HSQLDB is an open-source in-memory DB with JDBC, great for unit testing. docs (SQL)

HTTP tutorial
curl HTTP tool (do HTTP GETs, POSTs using command line, or from build.xml) Windows install instructions (usually available on Mac/Linux)

JEE, Java Enterprise Edition (API) We are using version 7, but only certain of its technologies: servlets, JSP, EL, JSTL, and JPA2, so all we need are certain jar files in addition to the Java JDK, plus tomcat to run the servlets.

Apache tomcat, our servlet container, i.e., web server with servlet capability: We are using version 8

REST: firstRest (zip) REST tutorial project from vogella.com, with build.xml similar to our other projects.