Database access - fidransky/kiv-pia-labs GitHub Wiki

TOTD:

• learn about Java database access basics
• implement repositories using plain JDBC and Spring's JdbcTemplate
• introduce database migration engine to the project

There are generally two ways of accessing data in a relational database from any app:

1. plain database access
2. object-relational mapping (ORM)

Plain database access in Java is usually covered by JDBC (Java DataBase Connectivity) which is an ancient but still valid Java standard for accessing relational databases.

With JDBC, you can run queries using standard SQL and retrieve data as collections of objects. There is some basic support for data conversions between DB columns and their corresponding Java types.

It is good to know about JDBC because ORM frameworks usually use it as their base layer.

ORM is in the Java world represented by the Jakarta Persistence API (JPA) standard, and provides developers with a way of persisting entire Java objects and/or collections. It provides powerful tools for manipulating and querying the stored data.

As many other Java standards, JPA is just an API specification. There are a few implementations of which the most widely known open source implementation is probably Hibernate. Except for JDBC drivers for specific databases, Hibernate introduces dialects which are used as a bridge between the varying SQL implementations and the unified Jakarta Persistence Query Language (JPQL).

To access a database engine using JDBC, you need an appropriate driver. Most of the currently available databases provide such drivers and there are even some pretty obscure "databases" accessible this way like MS Excel, DBF and many other.

Before starting to play with DB access, we need to make our services run their read/write queries in transactions. This is especially important for operations altering data in multiple DB tables - when one query fails, all queries fail.

Spring behaves so that when an exception is thrown from a transactional method, Spring automatically rollbacks the DB transaction (configurable). Spring does so with the help of bean proxies as we've seen in a previous lab.

Typically, as application evolves, its database schema (i.e. database tables, their structure and relations) evolves with it. To make schema changes easier to manage, we usually introduce a database migration engine to our apps.

Generally, database migration engines store all applied migration scripts somewhere (e.g. in a database table). When the migration engine is started, it first looks into its store to decide which migration scripts to run, executes and saves them back to its store for future reference. The engine can be started either at the start of the app or before that.

In Java, two migrations engines are commonly used:

• Liquibase
• Flyway

The chat rooms app only stores its data in memory. Today, we're going to swap all repository implementations so that relational database (here, MariaDB) is used instead.

Use the official Docker image for MariaDB to run MariaDB in Docker:

docker run --detach --name mariadb -p 3306:3306 -e MARIADB_ROOT_PASSWORD=root -e MARIADB_DATABASE=pia_labs -e MARIADB_USER=mariadb -e MARIADB_PASSWORD=mariadb mariadb:latest

Connect to the container's terminal:

docker exec -it mariadb bash

Open MariaDB client using the credentials above and see the DB contents:

mariadb --user mariadb --password --database pia_labs

As a rule of thumb:

1. whole service class should be annotated with @Transactional(readOnly = true)
2. read methods inherit the annotation from the class
3. write methods should be annotated with @Transactional - i.e. shorthand for @Transactional(readOnly = false)

Update DefaultDamageService to run methods in transactions.

Create pia-labs-repository-jdbctemplate submodule.

Make it depend on pia-labs-core so that it gains access to repository interfaces.

Add org.springframework.boot:spring-boot-starter-jdbc dependency for JDBC access.

Configure following JDBC connection properties in selected app's application.properties configuration file:

• spring.datasource.url
• spring.datasource.username
• spring.datasource.password

Explore init.sql file and download it to pia-labs-core submodule src/main/resources/ directory.

Load the file as a classpath Resource in JdbcConfiguration:

@Value("classpath:init.sql")
private Resource initializerScript;

Define org.springframework.jdbc.datasource.init.DataSourceInitializer bean in JdbcConfiguration:

var databaseInitializer = new ResourceDatabasePopulator(initializerScript);
databaseInitializer.setContinueOnError(true);

var initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
initializer.setDatabasePopulator(databaseInitializer);
return initializer;

Run the app, then open MariaDB client and see the DB contents again.

Create a new implementation of DamageRepository interface in the newly created JDBC module, using plain JDBC's DataSource and PreparedStatement for DB access. Don't forget to annotate it as @Repository.

Implement Spring's org.springframework.jdbc.core.RowMapper to map DB rows into Java objects.

Create another DamageRepository implementation in the JDBC module, using Spring's JdbcTemplate as a lightweight abstraction layer on top of plain JDBC. Re-use the original mapper.

Add pia-labs-repository-jdbctemplate as a dependency of the selected app.

Add org.mariadb.jdbc:mariadb-java-client dependency for MySQL Connector/J.

When you run the app now, Spring will complain that there are multiple beans implementing the DamageRepository interface available - the original in-memory ones and JDBC ones. Make JDBC @Repository implementations @Primary and run the app again.

Add org.flywaydb:flyway-core and org.flywaydb:flyway-mysql dependencies to the pia-labs-core submodule.

Use @DependsOn annotation to make database initializer beans defined in JdbcConfiguration to only run after Flyway migrations are finished.

Run the application to verify that everything is set up correctly. The app starts up as usual but in the database, a new table flyway_schema_history is created. Since we haven't created any database migration scripts yet, the table is empty.

Create a new database migration script called V1__Base.sql in pia-labs-core submodule's src/main/resources/db/migration/ folder.

Move CREATE TABLE statements from init.sql script to the newly created migration script.

Build and run the app again. Now, the flyway_schema_history table contains one row corresponding with the migration script.

• https://jakarta.ee/learn/docs/jakartaee-tutorial/current/intro/overview/overview.html
• https://www.marcobehler.com/guides/spring-transaction-management-transactional-in-depth
• https://www.baeldung.com/spring-jdbc-jdbctemplate
• https://docs.spring.io/spring-boot/how-to/data-initialization.html#howto.data-initialization.migration-tool.flyway