Database Applications

This page explains how to execute SQL queries programmatically, and how to organize a program that accesses a database.

Reading

Chapter 10 in Fundamentals of Database Systems gives a good overview of different approaches to programmatic database access. That chapter is however quite brief, and does not give an in-depth coverage of any technology. Also, some of the technologies that are covered are a bit outdated. The recommendation is therefore to read chapter 10 as an introduction to this topic, but to focus on the videos below. As a side note, that chapter mentions SQLJ as an example of embedded SQL in Java, while a more up-to-date alternative is jOOQ, https://www.jooq.org/ Links to an external site.. However, none of those are part of this course.
Chapter 9 in Database System Concepts covers programmatic database access, but it's only section 9.6 that overlaps with what is covered here. The rest of chapter 9 covers things outside this course.

Introduction to JDBC

This video is an introduction the JDBC (Java Database Connectivity) API, which is used to execute SQL statements from a Java program. The video was originally created for another course, with focus on communication rather than on databases. Thus the database that is used is not very well designed, and there are some remarks in the video on that. Please just ignore that, it is still a good explanation of how to call a database from a Java program.

Introduction to JDBC Links to an external site.
Content

Here is a GitHub repository with (almost) the program that is created in the video above, https://github.com/KTH-IV1351/jdbc-intro Links to an external site.. There are some small differences between this program and the one in the video, mainly names of classes, database, etc. Also, the program in the video uses Apache Derby, but this one uses PostgreSQL or MySQL. Please do not use Derby, it is a simple DBMS intended only for testing, not for production.

The GitHub repository does in fact contain a Visual Studio Code project, that's why there are some extra files, like .classpath and .settings. You can simply ignore those.

Here is a video explaining the JDBC URL, it's the application below that's used as an example.
A JDBC URL Links to an external site.

Architecture and Design of a Database Application

In order to write code that handles database access in a good way, we need a basic understanding of code organization as such. Therefore, this video repeats basic architectural concepts. The architecture introduced here is used in the video below, on architecture of a database application. If you wish, you can also read about this architecture in section 5.3 of A First Course in Object-Oriented Development. This video was originally recorded for another course, please ignore everything that is said about 'the course'. You must use a well-designed layered architecture if you solve the higher grade project task on database applications, but you are not required to use exactly the layers presented here. There are no requirements on code organization for the mandatory project task on database applications.

Introduction to layered architecture Links to an external site.
Content

00:00 Links to an external site. Goals of software design
01:05 Links to an external site. High cohesion
01:42 Links to an external site. Low coupling
02:29 Links to an external site. The MVC pattern
06:51 Links to an external site. The Layer pattern

The video below shows how to design an application that includes a database. This is the level of architecture and design your application must have if you solve the higher grade project task on database applications, but you are not required to use exactly the layers presented here.

There are two important things that shall be emphasized about the video below:

Methods in a DAO must absolutely not contain any business logic. Public methods in a DAO are only allowed to do CRUD (Create, Read, Update or Delete) operations. A proven practice is that names of all public methods in DAOs shall start with either create, find, update or delete. That's a help to ensure nothing but CRUD operations are performed, since a method must of course do what it's name says it does.
The prepared statement findAccountByAcctNoStmtLockingForUpdate, created on line 284-288 in BankDAO executes a SELECT FOR UPDATE sql statement. The purpose of this kind of select is to place exclusive locks on the rows that are selected. Only one transaction at a time can hold such locks. This is needed in operations that read a value from the database, calculates an update of the value, and stores the updated value in the database. That is done by the deposit and withdraw operations in the bank program. Without such locks, it would be possible for two (or more) transactions to read the value and then update it at the same time, overwriting each other's updates.

Architecture and design of a database application Links to an external site.
Content

Here is a GitHub repository with the program that is described in the video above, https://github.com/KTH-IV1351/jdbc-bank.git Links to an external site..

The GitHub repository does in fact contain a Visual Studio Code project, that's why there are some extra files, like .classpath and .settings. You can simply ignore those.