|
7 years ago | |
---|---|---|
.mvn/wrapper | 7 years ago | |
src | 7 years ago | |
.gitignore | 7 years ago | |
H2-Worksheet.md | 7 years ago | |
README.md | 7 years ago | |
data-h2.sql | 7 years ago | |
mvnw | 7 years ago | |
mvnw.cmd | 7 years ago | |
pom.xml | 7 years ago |
The following lab is to be completed using the H2 Console. Once you have figured out the correct queries for each step, save a copy in a file called src/main/resources/script.sql
. This will be how you submit this assignment. If at any time you need to reset the database, you can restart your Spring Boot server.
Add the following movies to the movies
table using an insert statement:
Title | Runtime | Genre | IMDB Score | Rating |
---|---|---|---|---|
Howard the Duck | 110 | Sci-Fi | 4.6 | PG |
Lavalantula | 83 | Horror | 4.7 | TV-14 |
Starship Troopers | 129 | Sci-Fi | 7.2 | PG-13 |
Waltz With Bashir | 90 | Documentary | 8.0 | R |
Spaceballs | 96 | Comedy | 7.1 | PG |
Monsters Inc. | 92 | Animation | 8.1 | G |
Add a few more movies of your choosing.
Create a query to find all movies in the Sci-Fi genre.
Create a query to find all films that scored at least a 6.5 on IMDB
For parents who have young kids, but who don't want to sit through long children's movies, create a query to find all of the movies rated G or PG that are less than 100 minutes long.
Create a query to show the average runtimes of movies rated below a 7.5, grouped by their respective genres.
There's been a data entry mistake; Starship Troopers is actually rated R, not PG-13. Create a query that finds the movie by its title and changes its rating to R.
Show the ID number and rating of all of the Horror and Documentary movies in the database. Do this in only one query.
This time let's find the average, maximum, and minimum IMDB score for movies of each rating.
That last query isn't very informative for ratings that only have 1 entry. use a HAVING COUNT(*) > 1
clause to only show ratings with multiple movies showing.
Let's make our movie list more child-friendly. Delete all entries that have a rating of R. Remember to record your query in script.sql
.
Create a PersonService
class. These will be used to manipulate the contents of the database given program requirements. Remember to use a JdbcTemplate
to provide access to your database. All database access logic and sorting/filtering should be handled by your PersonService
and the SQL queries it issues to the database.
You can provide your PersonService
to any controller that needs it by marking it as a @Service
class and autowiring it into the appropriate controller. Remember that you will also need a corresponding Person
class to hold the data in transit. You do not need to use any of the JPA annotations (@ID
, @Entity
etc.) in your Person class.
Support the following operations:
Person
to the databasePerson
in the databaseCreate the following REST endpoints to interact with the application. You can use postman to confirm your program's behavior.
POST
/people
-- create a personPUT
/people/{id}
-- update person with id
. 404 error if that person doesn't exist yetGET
/people/{id}
-- get the person with the specified IDDELETE
/people/{id}
-- Delete the person with the specified IDGET
/people
-- get all people in the databaseGET
/people/reverselookup/{mobileNumber}
-- find all people with the specified mobile numberGET
/people/surname/{lastName}
-- Find all people with a particular last nameGET
/people/surname
-- Get the result of the surname report aboveGET
/people/firstname/stats
-- Get the report of first name frequenciesThere is another table in the data base named home that consist of the following fields:
ID | ADDRESS | HOMENUMBER |
---|
People in the 'Person' table are able to have a home from the 'Home' table associated with them. People are mapped to homes through their 'HOME_ID' value which is a foreign key that references the id column in the 'HOME' table
Add the following homes to the 'HOME' table
Address | Home Number |
---|---|
36 E. Bayberry Rd.Savannah, GA 31404 | 565-6895 |
11 Essex Dr.Farmingdale, NY 11735 | 454-4544 |
920 Arlington Street Clifton, NJ 07011 | 985-4515 |
234 High Street, PA 19159 | 267-3940 |
You may use the query in the 'data-h2.sql' file to populate the 'PERSON' and 'HOME' table
Support the following operations:
List all of the homes that have more that one person that live there. Group people by the home that they live in
List all of the people that live a the address 11 Essex Dr.Farmingdale, NY 11735
Create a query to update a person's home
Create a query to update a person's 'HOMENUMBER"
Find the home number of John Smith
In this section we're going to re-create our service class using JPA.
Start by breaking your PersonService
class into an interface and implementation eg: interface PersonService
and class JdbcPersonServiceImpl
. Place the @Service
annotation on the implementation class.
Now create a new class that implements the PersonService, this time with JPA. Once you annotate this class with @Service you will get an error when you start spring -- you can fix this by annotation the service you want to use with @Primary
-- This tells Spring that if there are multiple beans competing for autowiring, the one marked with @Primary
should be used (this won't work if multiple competing beans are marked @Primary
In your JdbcPersonServiceImpl
you used a JdbcTemplate
or NamedParameterJdbcTemplate
to access the database. In your JpaPersonServiceImpl
you will use a PersonRepository
instead. You will have to define the interface for this repository, but the Spring Framework will provide the implementation automatically.
Implement all of the methods found in your PersonService
interface in this new JPA-based service. You won't have to change your controller at all if you are autowiring a reference to a PersonService
- Spring will automatically inject the @Primary
-annotated implementation of PersonService
Remember that you won't be using the Jdbc service or any JdbcTemplate
objects in this part of the lab.