Skip to content

Practical work 05: JDBC

Intro

This time, we will connect to a Database for the first time.

Installation of the project

The first step is to download the starting project code.zip and to extract it inside your IDE workspace.

Import the project inside your IDE, then you can update your Maven repository by triggering a maven update project dependencies in your IDE.

Installation of the database

Info

Since we will use SQLite, we don't need any database setup, as all the data will be created in a file by SQLite itself. Obviously, if you want to change SQLite by another database engine, you should set it up accordingly!

You can see that we import the SQLite driver in the pom.xml file:

1
2
3
4
5
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.30.1</version>
</dependency>

Instructions

Implementing the two DAO

The Java project contains two DAO classes in the fr.isen.java2.db.daos package: MovieDao and GenreDao. (One DAO for each entity and for each SQL table)

Both DAO has several methods that you have to write using JDBC like explained in the lesson. Two TestCase classes have been created in the src/test/java directory to test your implementations.

GenreDao

The simpler of the two DAO is the GenreDao. You have to implement the three methods. Since this is not a SQL lesson, here are the different SQL queries to execute for each method (or something similar):

method SQL Query
listGenres SELECT * FROM genre
getGenre SELECT * FROM genre WHERE name = 'Comedy'
addGenre INSERT INTO genre(name) VALUES('Horror')

The GenreDaoTestCase class is here to help you. Its tests are already written and you can run them to see if your methods are correct.

Some tips

  • The DataSourceFactory must be used to get a DataSource object for which you can open a connection.
  • Don't forget to use a prepared statement when needed.
  • Use the Genre(int id, String name) constructor to create new Genre objects.

When every tests are green in the test case, you can continue.

MovieDAO

The MovieDao interface is similar to the GenreDao one. Its implementation is a little more difficult to write because the Movie entity is little more complex than the Genre entity.

Once again, here are the SQL queries to use:

method SQL Query
listMovies SELECT * FROM movie JOIN genre ON movie.genre_id = genre.idgenre
listMoviesByGenre SELECT * FROM movie JOIN genre ON movie.genre_id = genre.idgenre WHERE genre.name = 'Comedy'
addMovie INSERT INTO movie(title,release_date,genre_id,duration,director,summary) VALUES(?,?,?,?,?,?)

You will see that the addMovie method has a Movie object as a parameter and as a return type. The parameter will have all the information needed to create the movie in the database but no id! The object returned by the method must have the same information with the id that has been generated by the database.

Use the MovieDaoTestCase class to test your methods. This time, you have to write the tests yourself.

Bonus stage

DataSourceFactory uses an explicit dependency to the SQLite driver. What if we want to use PostgreSQL or MySQL? As for now we have to change our code and recompile. Not very clever, isn't it?

Conveniently, JDBC provides a class called DriverManager that can infer the driver to use according to the connection URL provided, and the drivers loaded in the classpath.

Refactor your code to make use of this DriverManager class, instead of relying on the SQLite DataSource class.

To validate that your code is dependency-free, comment the dependency to SQLite connector in pom.xml. Your code should still compile (but not run, obviously). What would be the right scope for this dependency ?

Evaluated Work

Warning

Remember that this Practical Work is being evaluated.

Analysis of your work

The quality of your code, the clarity of your implementation will be taken into account for your grade.

  • Your project must be standalone (regular maven project, no hidden dependencies)
  • Your code must compile
  • Every opened resource has to be closed.
  • The code must implement the requirements of the PW.
  • The tests must run and pass
  • Use SQL functions where needed. In particular, use the Database functions where applicable.
  • Document your code; use clear, readable names
  • Leave no warning behind
  • Even if the whole project does not work, an analysis will be made on your whole project, your maven configuration and on the ability to import your project inside an IDE.

Hint

Don’t be afraid to ask your teacher if you are stuck in your implementation.

Deadline

Reminder

You have until Sunday, February, the 11th at 23:59:59.999 to send me your work.

You have two ways to send your work:

  • Send me the link to a GitHub or Gitlab project (last commit before the deadline)

  • Send me a zip file (max 10 mo) by email. Change the extension, or send a 7zip file, to avoid having your file filtered by anti-spam software... NO RAR FILES

  • Your code / email have to clearly state who you are.

  • It is your responsibility to ensure that I received the file. If I received your code I will get back to you individually to confirm that I received your work no later than February, the 20th at 23:59.

  • Provide all the information you think is necessary to understand and rate your project.

  • Your project should at least contain:

    • a pom.xml file
    • your java classes
    • the test classes used to validate your code