How To Approach Java, Databases, and SQL [Video]

We want to save our thumbnail data to a database so that we can render our pictures to a nice HTML gallery page and finish the proof of concept for our Google Photos clone! 

Which database should we use and why? Which Java database API? What database tools will make our lives easier along the way? Find out in this episode of Marco Codes!

What’s in the Video

00:00 Intro 

We'll cover what the plan is for this episode: to add database capabilities to our Google Photos clone, which currently only works with files, but doesn't store their metadata in a database table.

00:52 Where We Left Off

Before jumping straight into implementing database and ORM features, we will do a quick code recap of the previous episodes, to remind ourselves how the image scanning and conversion process currently works.

01:46 Setup 

Whenever we want to do something with databases and Java, we need a couple of (in this case) Maven dependencies. More specifically we want to make sure to add the H2 database to our project, which we will use for production, not just for testing! We'll also add the HikariCP connection pool to it - something I do by default in every project and which is usually done automatically by frameworks like Spring Boot.

04:38 Writing a Database Schema 

Here, I present my current approach when doing Java database work: making sure the database schema is hand-written, thinking through table names, column names, types, etc. Hence, we'll start writing a schema.sql file for our new "media" table during this section.

10:08 Creating a DataSource

Having created the schema, we'll need to create a DataSource next. As we're using HikariCP, we'll follow its documentation pages to set up the DataSource. We'll also make sure the schema.sql file written earlier gets automatically executed whenever we run our application.

12:46 Saving Thumbnail Data 

It's finally time to not just render thumbnail files on disk, but also save information about the generated thumbnails and original images in our brand-new database table! We'll use plain JDBC to do that and talk about its advantages and disadvantages.

14:00 Refactoring Maneuver 

Sometimes you just need to _see_ certain things that are very hard to explain in words. To clean up our program, we will have to change a couple of method signatures and move parameters up and down throughout the file.

16:21 Extracting Image Creation Dates 

At the moment, we don't properly detect the image creation date from its metadata. We'll talk about how to implement this in the future and why we'll stick with the file creation date for now.

17:10 Avoiding Duplication 

We'll also need to handle duplicates. If we re-run our program several times, we don't want to store the image metadata multiple times in our tables. Let's fix this here.

19:04 Inspecting H2 File DBs 

In case you don't know how to access H2 file databases, we will spend some time showing you how to do that from inside IntelliJ IDEA and its database tool window.

21:23 Rendering HTML Output 

Last but not least, we'll need to render all the information from our database to a nice, little HTML page, so we can actually browse our thumbnails! As a bonus point, this will be the simplest and probably dirtiest implementation of such an HTML page you've seen for a while - but it works!

30:30 What’s Next?

Did you like what you saw? Which feature should we implement next? Let me know!

Video