Lab 6

JDBC
Write an application that allows to connect to a relational database by using JDBC, submit SQL statements and display the results.

The main specifications of the application are:

Compulsory (1p)

• Create a relational database using any RDBMS (Oracle, Postgres, MySql, Java DB, etc.).
• Write an SQL script that will create the following tables:
  • movies: id, title, release_date, duration, score;
  • genres: Action, Drama, etc. (assume that a movie has only a genre)
  • actors;
  • the necessary associative (junction) tables.
• Update pom.xml, in order to add the database driver to the project libraries.
• Create a singleton class in order to manage a connection to the database.
• Create a DAO class that offers methods for creating genres, and finding them by their ids and names;
• Implement a simple test using your classes.

Homework (2p)

• Use a connection pool in order to manage database connections, such as C3PO, HikariCP or Apache Commons DBCP.
• Create an object-oriented model of the data managed by the Java application.
• Implement all the DAO classes.
• Generate an HTML report containing all the movies in the database. The structure of the report must be defined using a template, and the data must be read from a view (or function) stored in the database.

Advanced (2p)

• Create a tool to import data from a real dataset, such as MovieLens, or The Movies Dataset or other. You may use opencsv or any other CSV parser.
• Extend the model in order to create movie lists. A movie list has a name, a creation timestamp and some movies.
  Use FlyWay or other DB migration tool in order to evolve your database easily.
• Two movies are related if they have a common actor.
  Create an algorithm that partitions the collection of movies into lists that contain only unrelated movies.
  The number of lists should be "as small as possible" and any two lists should have the same size or differ by at most one.

Resources

• Slides
• JDBC
• Oracle Database JDBC Developer's Guide and Reference
• JDBC Tutorial - The ULTIMATE Guide

Objectives

• Understand terms and concepts related to relational databases: DBMS, SQL, table, query, stored procedure, cursor, etc.
• Connect to a relational database by using a JDBC driver
• Submit queries and get results from the database
• Specify JDBC driver information externally
• Perform CRUD operations by using the JDBC API