CS436/636 - Database Application Development, Fall 2016

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 5:30-6:45 in W-01-0019 W-2-200 starting Sept. 26
Professor: Betty O’Neil (eoneil at cs.umb.edu)
Office hours (in-term): MW 3:30-4:45 in S-3-169
Prerequisites: CS310, CS630 (these are important), and officially, CS615 (less important, can be replaced by programming experience)

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
use Java for business logic use PHP for business logic
requires Java expertise (cs310 or equivalent) requires intermediate Java (cs210 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.


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.

Recommended book (not required):

Pro JPA2 , second edition, Mastering the Java Persistence API, by Mike Keith and Merrick Schincariol, APress, 2013, ISBN 978-1-4302-4926-9, available for example at Amazon. JPA2 is the API we will be using for object-relational mapping, i.e., automating the relationship between application data in Java objects and the same data in database table rows. Follow the book link for errata, 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 by Fri., Sept. 9 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. 7 notes Intro, concepts of client-server apps vs. web apps

Fri., Sep. 9: Oracle and mysql accounts will be created for students with cs636 class accounts already. Done at 4:15pm. Account name is your UNIX username, as is the password. See Database Setup linked below for more information.

Mon, Sept. 12 notes Oracle and mysql,
Wed., Sept.. 14 notes JdbcCheckup, XML, build.xml
Mon., Sept. 19 notes build.xml, jar files, Intro to the Pizza Project
Wed., Sept. 21 notes Demo on Pizza Project, discussion of its database, its objects
Mon., Sept. 26 notes Pizza Project layers, APIs
Wed., Sept. 28 notes Pizza Project configuration
Mon., Oct. 3 notes Stateless Service layer and other architectural principles
Wed., Oct 5 notes Intro to the Music Project
Wed., Oct 12 notes JUnit testing
Mon., Oct. 17 notes Transactions
Wed., Oct. 19 notes Project 1, HTTP
Mon., Oct. 24 notes slides (6 per page) Intro tomcat, intro JPA
Wed., Oct. 26 notes intro JPA, demo on debugging, intro to pizza2
Mon., Oct. 31 notes pizza2, JPA
Wed., Nov. 2 notes pizza2 service methods, intro to servlets
Mon., Nov. 7 notes Midterm Review Layers Summary (QsAndAs)
Midterm Exam Wed., Nov. 9 Practice Midterm Exam (solution)
Mon., Nov. 14 notes Servlets Chapter5slides (6pp) ch05email (zip)
Wed., Nov. 16 notes Servlets, Session variables chapter7slides (6pp) ch07cart (zip) ch07download (zip)
Mon., Nov. 21 notes Moving our system into the web container, Connection pooling, JNDI
Wed., Nov. 23 no class
Mon., Nov. 28 notes The MVC servlets in pizza3
Wed., Nov. 30 notes Web app initialization, scaling up
Mon., Dec. 5 notes Debugging demo, SQL injection, scaling way up
Wed., Dec. 7 notes Using the shared cache, Internationalization
Mon., Dec. 12 notes REST web services in Java
Wed., Dec. 14 notes Review

Mon., Dec. 19 Final Exam 6:30-9:30 in W-2-200 (we'll try to start at 6:15) Practice Final (Solution) (revised Sat. 12/17)

Homework and Programming Assignments

Homework 1 due Monday Sept. 19 in class, on paper Solution
Homework 2 due Wed., Sept. 28 in class, on paper Solution
pizza1 (zip) Pizza Project 1 (doc)
Homework 3, due Monday, Oct. 17 in class, on paper, except for #5, delivered online. Solution
music1-setup (zip) Project 1  Solution: music1 (zip)
pizza2 (zip) Pizza Project 2: JPA, transactions
Homework 4, due Monday, Nov. 28 in class, on paper. JPA, tomcat, servlets Assigned ports for tomcat on topcat Solution
tomcat-8.5 (zip) setup instructions for topcat setup instructions for home
Project 2   pizza3 (zip) music2 (zip), Murach's  musicStoreJPA (zipmusic3 Solution (zip)
Homework 5, due Monday, Dec. 5 in class, on paper. MVC webapps, using eclipse tomcat, pizza3 Solution


Notes, etc. from Fall, 2015

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

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 Mars, of the Eclipse IDE for Java EE Developers

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

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. You can reach it on topcat at http://topcat.cs.umb.edu:11600/firstRest/rest/hello or http://localhost:11600/firstRest/rest/hello with tunnel if my tomcat is running.