This is the exercise repository for the database systems I course for IT systems engineering bachelor's degree @ Hasso Plattner Institute Potsdam.
The exercise will cover how to programmatically connect to a DBMS and query data. In this case, we want you to use the Java Database Connectivity (JDBC) which is a stable and often used database API for JVM-based programming languages (exists since 1997). We chose JVM (Java Virtual Machine) languages as a base for all implementations in this course for ease of development and quick setup, and to give you the chance to test out a new popular programming language in your studies if you haven't developed in Java or Kotlin yet.
Your assignment is to implement a simple application that connects to your PostgreSQL instance and queries movies and actors / actresses with a title / name matching the given keyword from the IMDB dataset. For that, you can program in either Java or Kotlin.
The build process is using Gradle and uses the long-term release Java 17 / Kotlin 1.8.22. Don't worry, our configuration should make setup and running easy for you so that you don't have to figure out how this works!
This repository contains a Gradle project, which can be used standalone with a command line and a text editor, or preferably with an IDE like IntelliJ Idea (free student license) or VSCode (or Netbeans / Eclipse) - IDEs with Gradle integration make running build tasks easier and have some nice development shortcuts. We suggest that you use IntelliJ Idea if this is your first time working with Java projects or if you want easy plug-and-play with the Gradle build tasks. Of course, you can ask us on Moodle if you need help with your setup, but we might not be prepared if you try out other IDEs.
Please configure your database connection details in the database.properties file. You could also provide these as command-line arguments or environment variables, but this should be the easiest way.
Next, you can decide in which language you want to implement the exercise. We provided a single file in which you have to write your solution.
- for Java
/src/main/java/JDBCExerciseJavaImplementation.java. - for Kotlin
/src/main/kotlin/JDBCExerciseKotlinImplementation.kt.
Once you decide on your used language, check the top of the class where you find a @ChosenImplementation(false) annotation - replace false with true. Only the class with this annotation set to true will be executed by the application and tests.
Simply open this folder in your IDE, and it should automatically get picked up as a Gradle project (or via open / import project), which should then index all containing files and start the Gradle build daemon.
The most important build tasks we want to use are:
gradle run --args="<search keyword>"to start your program with the given keyword.gradle testto start the provided test suite to test your solutions.
If you have a Gradle plugin in your IDE (icon with an elephant), you should find these tasks there. You can also provide run arguments on these, which are needed for gradle run.
You can also use the Gradle build process without an IDE by running each required build task using the provided ./gradlew script.
Run all described build tasks from the other tutorial as command line arguments, e.g. ./gradlew test.
See the Gradle Wrapper documentation for more details.
You have your chosen implementation file open? No matter which language you picked, both contain a class that implements the JDBCExercise interface. Check it out to see what each method is supposed to do!
You will see that there are 3 tasks to do, which get harder with each time:
- create a connection to the database
- query movies matching the keyword
- query actors matching the keyword
The last two also require entity objects which will contain relational results from your queries. How you query the database is up to you, but at the end, you have to return lists of the required objects from the methods.
You can read details about JDBC in the PostgreSQL JDBC Documentation, though a lot of them are not needed for this exercise. All you need to do is use the API to connect to your database, create PreparedStatement objects for queries, and read the ResultSet to get the query results.
We provide some JUnit tests for testing the basic functionality of each method. You can start working on your solution and cross-check with each test if your code does the correct things. They should generate helpful insights into which data is missing or what sort order was wrong. Re-read the documentation details in JDBCExercise when some parts of the result are incorrect. You can see what the results should be by looking here Movies / Actors. Once all tests are green, you are probably ready to submit.
Feel free to write tests for edge cases that you found while implementing your exercise solutions (not mandatory). We will test your submissions with a bigger test set and different input data to check for correctness!
You might have noticed that your implementation results in a simple app that can be run using gradle run --args="<search keyword>". This prints the results for the found movies and actors on your console. You can play around with these while working on your implementation if you prefer this to the tests. You can also use the provided logger, or println from your chosen language, or use debug tools to check the execution of your code.
Under the hood, Gradle runs the main(args) method to start an instance of the JDBCExerciseApp. The app class handles command-line argument processing and then uses your provided implementation to connect to the database, execute the two queries, and print their results. Don't worry about Kotlin - both languages are inter-compatible, but we wrote the most relevant parts for you in Java; the Kotlin side contains mostly utilities for the execution of the application and tests.