SE 352/452 Object Oriented Enterprise Computing
▪ Administrative
▪ Keys from last meeting
▪ Version Control (Git) ▪ Environment Setup
▪ Java SQL
▪ Before next class
▪ Changes in teams
▪ Those impacted should have received an email
▪ Course Overview ▪ Java Basic
▪ 12 Factor App
▪ Environment Setup
▪ DePaul-SE452/concepts
▪ IDE – Visual Code ▪ Java IDE Extension
▪ https://12factor.net/codebase
▪ One codebase tracked in revision control, many deploys
Working or Stage area
Local Repository
push Remote Repository
Pull create
• create • merge • delete
12 Factor App Concept (https://12factor.net/dependencies)
▪ Java/Spring
▪ Ant or Maven or Gradle
▪ Declarative dependencies
▪ Setup the major project components to reduce setup time ▪ https://start.spring.io
▪ From VS Code, select View → Command Palette and select Spring Initializer
▪ Lombok (https://projectlombok.org/)
▪ Library to reduce the template code ▪ Constructors
▪ Getters and Setters
▪ toString
▪ Code annotation)
▪ Course has multiple students
To avoid property to be included in string
Validation in each layer Common validation
▪ Define library dependency
Jakarta Bean Validation specification
▪ Use the library
Database Management System
▪ What is it? Ways to store data in permanent way such that it can be retrieved later
▪ Flavors of DBMS: Structured Query Language (SQL) and NoSQL
▪ SQL is based on Relations between Tables based on rows and columns (think Excel sheets) and so referred to as Relational DataBase Management System (RDBMS)
▪ Different database vendors
▪ Oracle, SQLServer, DB2, MySQL, Postgres, H2
▪ NoSQL is based on anything but SQL… but more on that next class
▪ Protocol – Requests and renders content from servers ▪ http/https, tcp, smtp…
▪ Port – Communication tunnel ▪ 80/443, 25, 465…
▪ Content – Format ▪ html, plain text…
▪ Server runs as a process on a machine
▪ Client connects to the server using ▪ Client software
1. Server specific client
2. Generic client
3. Application code
▪ Connection information ▪ Server
▪ Protocol mechanism ▪ User id
▪ password
Structured Query Language
Tables, rows, columns, etc.
• A relational database consists of one or more tables that consist of rows (records) and columns (fields).
• The primary key in a table is the one that uniquely identifies each of the rows in the table.
• A foreign key is used to relate the rows in one table to the rows in another table.
• When you create a table, you define each of its columns and you identify its primary key.
• To define a column, you must supply the name and the data type, whether it’s automatically generated for new rows, and so on.
• On Unix systems, the table and column names are case-sensitive.
Murach’s Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 22
An INSERT statement that inserts multiple rows
INSERT INTO User
(FirstName, LastName, Email)
(‘John’, ‘Smith’,
(‘Andrea’, ‘Steelman’,
(‘Joel’, ‘Murach’,
How to insert multiple rows into a table
• The INSERT statement lets you insert one or more rows into one table of a database. When you code it, you need to include data for all columns that aren’t defined with default values or aren’t automatically generated.
• On a Unix system, table and column names are case-sensitive.
Murach’s Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 23
A SELECT statement that gets all columns Syntax
FROM table-1
[WHERE selection-criteria]
[ORDER BY column-1 [ASC|DESC] [, column-2 [ASC|DESC] …]]
A statement that selects all rows and columns
SELECT * FROM User
Result set
Murach’s Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 24
A SELECT statement that gets selected columns Syntax
SELECT column-1 [,column-2] …
FROM table-1
[WHERE selection-criteria]
[ORDER BY column-1 [ASC|DESC] [,column-2 [ASC|DESC] …]]
A statement that selects two rows and two columns
SELECT FirstName, LastName
WHERE UserID < 3
ORDER BY LastName ASC
Result set
Murach's Java Servlets/JSP (3rd Ed.), C11
© 2014, & Associates, Inc.
How to select data from a single table
• A SELECT statement is a SQL DML statement that returns a result set (or result table) that consists of the specified rows and columns.
• To specify the columns, use the SELECT clause.
• To specify the rows, use the WHERE clause.
• To specify the table that the data should be retrieved from, use the FROM clause.
• To specify how the result set should be sorted, use the ORDER BY clause.
Murach's Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 26
A SELECT statement that joins two tables Syntax
SELECT column-1 [,column-2] ...
FROM table-1
{INNER | LEFT OUTER | RIGHT OUTER} JOIN table-2
ON table-1. column-1 {=|<|>|<=|>=|<>} table-2.column-2 [WHERE selection-criteria]
[ORDER BY column-1 [ASC|DESC] [,column-2 [ASC|DESC] …]]
Murach’s Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 27
A SELECT statement that joins two tables (cont.) A statement that joins the User and Download tables
SELECT Email, DownloadFilename, DownloadDate FROM User
INNER JOIN Download
ON User.UserID = Download.UserID
WHERE DownloadDate > ‘2014-01-01’
ORDER BY Email ASC
Result set
Murach’s Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 28
How to select data from multiple tables
• To return a result set that contains data from two tables, join the tables. To do that, use a JOIN clause. Most of the time, you’ll want to code an inner join so that rows are only included when the key of a row in the first table matches the key of a row in the second table.
• In a left outer join, the data for all of the rows in the first table (the one on the left) are included in the table, but only the data for matching rows in the second table are included. In a right outer join, the reverse is true.
• An inner join is the default type of join. As a result, it’s common to omit the INNER keyword from a SELECT statement for an inner join.
Murach’s Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 29
The INSERT statement Syntax
INSERT INTO table-name [(column-list)]
VALUES (value-list)
A statement that adds one row to the Download table
INSERT INTO Download
(UserID, DownloadDate, DownloadFilename, ProductCode)
(1, ‘2014-05-01’, ‘jr01_so_long.mp3’, ‘jr01’)
A statement that uses MySQL’s NOW function
to get the current date
INSERT INTO Download
(UserID, DownloadDate, DownloadFilename, ProductCode)
(1, NOW(), ‘jr01_filter.mp3’, ‘jr01’)
Murach’s Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 30
The UPDATE statement Syntax
UPDATE table-name
SET expression-1 [, expression-2] …
WHERE selection-criteria
A statement that updates the FirstName column
in one row
UPDATE User
SET FirstName = ‘Jack’
WHERE Email =
A statement that updates the ProductPrice column
in selected rows
UPDATE Product
SET ProductPrice = 36.95
WHERE ProductPrice = 36.50
Murach’s Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 31
The DELETE statement Syntax
DELETE FROM table-name
WHERE selection-criteria
A statement that deletes one row
from the User table
DELETE FROM User WHERE Email =
A statement that deletes selected rows
from the Downloads table
DELETE FROM Download WHERE DownloadDate < '2014-06-01'
Murach's Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 32
How to insert, update, and delete data
• INSERT, UPDATE, and DELETE statements modify the data that’s stored in a database, but they don’t return a result set. Instead, they return the number of rows that were affected by the query.
• These statements are sometimes referred to as action queries.
Murach's Java Servlets/JSP (3rd Ed.), C11 © 2014, & Associates, Inc. Slide 33
In Memory database – H2
▪ Server runs as a process on a machine
▪ Client connects to the server using ▪ Client software
1. Server specific client = h2-console
2. Generic client
3. Application code
▪ Connection information
▪ Server = localhost
▪ Port = 8081
▪ Protocol mechanism = jdbc:h2:xxx ▪ User id
▪ password
localhost:8081
▪ Update build.gradle to include database driver to use
▪ Open Browser
▪ http://localhost:8081/h2-console
▪ 12 Factor App Concept
▪Config (https://12factor.net/config)
▪ Spring Profile
1. Config – Properties
Schema.sql Data.sql
▪ Defines initial table ▪ Defines initial data
▪ Database library dependency (h2)
▪ Language database library access dependent (Java Persistence API)
▪ Define Connection information (jdbc url, username, password...) in configuration ▪ Define relationship between Class to Table
▪ Add in Java Persistence API (JPA) to dependencies
▪ Java Persistence API (JPA) ▪ @Entity
▪ @GeneratedValue ▪ @Column
https://javaee.github.io/tutorial/persistence-intro.html
▪ EntityManager invokes framework to handle SQL definitions for you
▪ Defines Finders using Spring Repository construct ▪ CrudRepository
▪ JPARepository
https://docs.spring.io/spring-data/data-commons/docs/current/reference/html/#repositories.definition
▪ Use Finder and then update object and then save the update
▪ Showing SQL may come handy for debugging
Course Student
Course_students
Origination
Destination
Origination
Destination
▪ JPA - https://docs.oracle.com/javaee/7/tutorial/partpersist.htm ▪ Chapter 37, 38, 43, and 44
▪SpringBootwithJPA (https://spring.io/guides/gs/accessing-data-jpa/)
▪ Run sample code
▪ Look at Milestone 1
▪ Turn in similar document to Airline Reservation
▪ Update prior documentation as necessary
▪ Overview, Section 1, Appendix A, Appendix B populated
▪ Be prepared for 5-10 min presentation about your project ▪ It can be PPT or walking through the document
▪ Live or recorded
▪ There may be questions on the presentation so be prepared