© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2023
K. S. P. Reddy, S. UpadhyayulaBeginning Spring Boot 3https://doi.org/10.1007/978-1-4842-8792-7_5

5. Working with JDBC

Siva Prasad Reddy Katamreddy1   and Sai Subramanyam Upadhyayula2
(1)
Hyderabad, India
(2)
Rotterdam, The Netherlands
 

Data persistence is a crucial part of software systems. Most software applications use relational databases as datastores, but recently NoSQL data stores like MongoDB, Redis, and Cassandra are getting popular. Java provides the JDBC API to talk to the database, but it is a low-level API that requires lots of boilerplate coding. The JavaEE platform provides the Java Persistence API (JPA) specification, an object-relational mapping (ORM) framework. Hibernate and EclipseLink are the most popular JPA implementations. Other popular persistence frameworks, such as MyBatis and JOOQ, are more SQL-focused.

Spring provides a nice abstraction on top of the JDBC API, using JdbcTemplate, and offers excellent transaction management capabilities using an annotation-based approach. Spring Data is an umbrella project that supports integration with the most widespread data access technologies , such as JPA, MongoDB, Redis, Cassandra, Solr, and ElasticSearch. Spring Boot makes working with these persistence technologies easier by automatically configuring the required beans based on various criteria.

This chapter looks at how you can add database support for your Spring blog application, first by using JDBC support of Spring without Spring Boot, and then you will learn how Spring Boot makes it easy to use JDBC without much coding or configuration. You will also learn about performing database migration using Flyway. If you are unaware of JDBC, you can go through this documentation to get a quick overview of it: https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html .

Introduction to JdbcTemplate

Before the Spring Framework, application developers used to write a lot of boilerplate code for trivial database logic in applications using JDBC. Let’s take the following code as an example:
public Optional<Post> findById(String id) {
   Connection connection = null;
   PreparedStatement statement = null;
   ResultSet resultSet = null;
   try {
      connection = dataSource.getConnection();
      statement = connection.prepareStatement(
            "select id, title, description, body, slug, post_status, created_on, updated_on from posts where id=?");
      statement.setString(1,id);
      resultSet = statement.executeQuery();
      Post post = null;
      if(resultSet.next()) {
         post = new Post(
               resultSet.getInt("id"),
               resultSet.getString("title"),
               resultSet.getString("description"),
               resultSet.getString("body"),
               resultSet.getString("slug"),
               PostStatus.valueOf(resultSet.getString("post_status")),
               convertToLocalDate(resultSet.getDate("created_on")),
               convertToLocalDate(resultSet.getDate("updated_on"))
                   );
      }
      return Optional.of(post);
   } catch (SQLException e) {
   } finally {
      if (resultSet != null) {
         try {
            resultSet.close();
         } catch (SQLException e) {}
      }
      if (statement != null) {
         try {
            statement.close();
         } catch (SQLException e) {}
      }
      if (connection != null) {
         try {
            connection.close();
         } catch (SQLException e) {}
      }
   }
   return Optional.empty();
}

As the method name suggests, this code is used to find a post by its id. Even though the logic is straightforward, it has a lot of boilerplate code to handle exceptions, opening, and closing database connections, and such.

Spring Framework introduced a class called as JdbcTemplate, which implements the famous Template Design Pattern to abstract away all the boilerplate code for us and perform all the error handling and database connection management in the background. So let’s go ahead and learn how to implement this using Spring Framework without using the Spring Boot starter for JDBC.

Using JdbcTemplate Without Spring Boot

First, let's take a quick look at how you can generally use Spring's JdbcTemplate (without Spring Boot) by registering the DataSource, TransactionManager, and JdbcTemplate beans. The DataSource is the interface that contains the blueprint to interact with the database of your choice. Each database driver has an implementation of this DataSource interface , and it is responsible for creating the database connection.

The TransactionManager bean is used to manage transactions. No prizes for guessing that this means creating, committing, and rolling back the database transactions.

To get started, add the following dependencies to the pom.xml file of the Spring blog application:
<dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
</dependency>
<dependency>
   <groupId>org.apache.commons</groupId>
   <artifactId>commons-dbcp2</artifactId>
   <version>2.9.0</version>
</dependency>

These dependencies add the JDBC support from Spring Framework to your project, along with the MySQL database driver, followed by the commons-dbcp2 database connection pooling library.

Now create a class called AppConfig.java inside the config folder with the following code:
@Configuration
@ComponentScan
@EnableTransactionManagement
@PropertySource(value = { "classpath:application.properties" })
public class AppConfig {
    @Autowired
    private Environment env;
    @Bean
    public static PropertySourcesPlaceholderConfigurer placeHolderConfigurer()
    {
        return new PropertySourcesPlaceholderConfigurer();
    }
    @Value("${init-db:false}")
    private String initDatabase;
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource)
    {
        return new JdbcTemplate(dataSource);
    }
    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource)
    {
        return new DataSourceTransactionManager(dataSource);
    }
    @Bean
    public DataSource dataSource()
    {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(env.getProperty("jdbc.driverClassName"));
        dataSource.setUrl(env.getProperty("jdbc.url"));
        dataSource.setUsername(env.getProperty("jdbc.username"));
        dataSource.setPassword(env.getProperty("jdbc.password"));
        return dataSource;
    }
    @Bean
    public DataSourceInitializer dataSourceInitializer(DataSource dataSource)
    {
        DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
        dataSourceInitializer.setDataSource(dataSource);
        ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
        databasePopulator.addScript(new ClassPathResource("data.sql"));
        dataSourceInitializer.setDatabasePopulator(databasePopulator);
        dataSourceInitializer.setEnabled(Boolean.parseBoolean(initDatabase));
        return dataSourceInitializer;
    }
}

This code first enables transaction management using the @EnableTransactionManagement annotation and then it defines a bean for JdbcTemplate, a bean for DataSource with a connection pool, a bean for TransactionManager, and a bean that initializes the database at the startup of the application, based on the property init-db. If this property is true, you can initialize the database at startup using a database script called data.sql .

You should configure the JDBC connection parameters in src/main/resources/application.properties as follows:
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/springblog
jdbc.username=root
jdbc.password=admin
init-db=true
You can then create a database setup script called data.sql in src/main/resources as follows:
DROP TABLE IF EXISTS COMMENTS;
DROP TABLE IF EXISTS POSTS;
CREATE TABLE POSTS
(
   ID int NOT NULL AUTO_INCREMENT,
   TITLE varchar(50) NOT NULL,
   DESCRIPTION varchar(500) NOT NULL,
   BODY LONGTEXT DEFAULT NULL,
   SLUG varchar(60) DEFAULT NULL,
   POST_STATUS ENUM ('DRAFT','PUBLISHED'),
   CREATED_ON datetime DEFAULT NULL,
   UPDATED_ON datetime DEFAULT NULL,
   PRIMARY KEY (ID)
);
CREATE TABLE COMMENTS
(
   ID int NOT NULL AUTO_INCREMENT,
   POST_ID int NOT NULL,
   TITLE varchar(200) NOT NULL,
   AUTHOR_NAME varchar(200) NOT NULL,
   BODY LONGTEXT DEFAULT NULL,
   CREATED_ON datetime DEFAULT NULL,
   UPDATED_ON datetime DEFAULT NULL,
   PRIMARY KEY (ID),
   FOREIGN KEY (POST_ID) REFERENCES POSTS(ID)
);
If you prefer to keep the schema generation script and seed the data insertion script separately, you can put them in separate files and add them as follows :
databasePopulator.addScripts(new ClassPathResource("schema.sql"),
                             new ClassPathResource("seed-data.sql") );
With this configuration in place, let’s make some small changes to your domain model . Let’s change the type of createdOn and updatedOn from LocalDateTime to LocalDate because the default jakarta.sql.Date class does not store the time component, only the date.
       private LocalDate createdOn;
       private LocalDate updatedOn;
Now let’s create another class called JdbcPostRepository.java , which contains the actual code to interact with the database using the JdbcTemplate class.
package com.apress.demo.springblog.repository;
import com.apress.demo.springblog.domain.Post;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Set;
import java.util.stream.Collectors;
@Repository
@RequiredArgsConstructor
public class JdbcPostRepository {
   private final JdbcTemplate jdbcTemplate;
   public Set<Post> findAllPosts() {
       return jdbcTemplate.queryForStream("select id, title, description, body, slug, post_status, created_on, updated_on from posts", new PostMapper())
               .collect(Collectors.toSet());
   }
   public void addPost(Post post) {
       final String sql = "insert into posts(title, description, body, slug, post_status, created_on, updated_on) " +               "values (?,?,?,?,?,?,?)";
     jdbcTemplate.update(con -> {
           PreparedStatement preparedStatement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
           preparedStatement.setString(1, post.getTitle());
           preparedStatement.setString(2, post.getDescription());
           preparedStatement.setString(3, post.getBody());
           preparedStatement.setString(4, post.getSlug());
           preparedStatement.setObject(5, post.getPostStatus());
           preparedStatement.setDate(6, java.sql.Date.valueOf(post.getCreatedOn()));
           preparedStatement.setDate(7, java.sql.Date.valueOf(post.getUpdatedOn()));
           return preparedStatement;
       });
   }
}

  • The @Repository annotation will mark your JdbcRepository as a Spring Repository bean.

  • Using the constructor injection, you inject the JdbcTemplate class into the JdbcRepository class. The constructors will be generated at compile time by the @RequiredArgsConstructor.

  • The findAllPosts() method uses the queryForStream() method of the JdbcTemplate object to execute the select statement and returns a stream of Post objects, which are collected into a Set.

  • Lastly, the addPost() method uses the update() method of jdbcTemplate to execute the insert statement. You are using the PreparedStatement class to map the data to the query because PreparedStatement is, in general, faster than the Statement class and also it helps mitigate SQL injection attacks.

You must also create a class to map the data from the ResultSet, while querying for the posts, inside the findAllPosts() method into the Post object. For that, you must create the following PostMapper class, which implements an interface called RowMapper :
public class PostMapper implements RowMapper<Post> {
   @Override
   public Post mapRow(ResultSet rs, int rowNum) throws SQLException {
       Post post = new Post();
       post.setId(rs.getInt("id"));
       post.setTitle(rs.getString("title"));
       post.setDescription(rs.getString("description"));
       post.setBody(rs.getString("body"));
       post.setPostStatus((PostStatus) rs.getObject("post_status"));
       post.setCreatedOn(convertToLocalDateTime(rs.getDate("created_on")));
       post.setUpdatedOn(convertToLocalDateTime(rs.getDate("updated_on")));
       return post;
   }
   private LocalDate convertToLocalDateTime(Date date) {
       if (date == null) {
           return null;
       } else {
           return date.toLocalDate();
       }
   }
}

The mapRow() method goes through all the fields inside the Post object and maps each field from ResultSet. Notice that for mapping the createdOn and updatedOn fields, you must create custom mapping logic because you defined these fields as type LocalDate instead of jakarta.sql.Date.

Before testing your changes, you need to make a few more changes inside the PostService class : replace the usage of PostRepository with the JdbcPostRepository class, and also adapt the logic inside the addPost() method to set the createdOn and updatedOn fields, before saving the Post object to the database.
package com.apress.demo.springblog.service;
import com.apress.demo.springblog.domain.Post;
       import com.apress.demo.springblog.repository.JdbcPostRepository;
       import lombok.RequiredArgsConstructor;
       import org.springframework.stereotype.Service;
       import java.time.LocalDate;
       import java.util.Set;
       @Service
       @RequiredArgsConstructor
       public class PostService {
   private final JdbcPostRepository postRepository;
   public void addPost(Post post) {
       post.setCreatedOn(LocalDate.now());
       post.setUpdatedOn(LocalDate.now());
       postRepository.addPost(post);
   }
   public Set<Post> findAllPosts() {
       return postRepository.findAllPosts();
   }
   public boolean postExistsWithTitle(String title) {
       return postRepository.findAllPosts().stream()
               .anyMatch(post -> post.getTitle().equals(title));
   }
}

Now, let’s see how to use JdbcTemplate without manually configuring all these beans inside the AppConfig.java class by using Spring Boot.

Using JdbcTemplate with Spring Boot

Using Spring Boot’s autoconfiguration feature, you don’t have to configure beans manually. To get started, replace the spring-jdbc dependency in the pom.xml file with the spring-boot-starter-jdbc module.
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
By adding the spring-boot-starter-jdbc module , you get the following autoconfiguration features:

  • The spring-boot-starter-jdbc module transitively pulls the com.zaxxer.HikariCP dependency, which is used to configure the DataSource bean.

  • If you have not defined a DataSource bean explicitly and if you have any embedded database drivers in the classpath, such as H2, HSQL, or Derby, then Spring Boot will automatically register the DataSource bean using the in-memory database settings.

  • If you haven’t registered any of the following beans, Spring Boot registers them automatically:

    • PlatformTransactionManager (DataSourceTransactionManager)

    • JdbcTemplate

    • NamedParameterJdbcTemplate

  • You can have the schema.sql and data.sql files in the root classpath, which Spring Boot will automatically use to initialize the database.

Initializing the Database

Spring Boot uses the spring.sql.init.mode property value to determine whether to initialize the database. If the spring.sql.init.mode property is set to always, Spring Boot will use the schema.sql and data.sql files in the root classpath to initialize the database.

In addition to schema.sql and data.sql, Spring Boot will load the schema-${platform}.sql and data-${platform}.sql files if they are available in the root classpath. Here, the platform value is the value of the spring.sql.init.platform property, which can be hsqldb, h2, oracle, mysql, postgresql, and so on.

You can customize the default names of the scripts using the following properties:
spring.datasource.schema=create-db.sql
spring.datasource.data=seed-data.sql

If you want to turn off the database initialization, you can set spring.sql.init.initialize=never. If there are script execution errors, the application will fail to start. If you want to continue , you can set spring.sql.init.continue-on-error=true.

Testing JDBC Code

Let’s write tests to make sure that your JDBC code is working. For this, you can use h2 as in the in-memory test database. To add the h2 database driver to the pom.xml file, you use the following code:
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>
Now you can create a JUnit test class to test the JdbcPostRepository methods, as shown in Listing 5-1.
package com.apress.demo.springblog;
import com.apress.demo.springblog.domain.Post;
import com.apress.demo.springblog.repository.JdbcPostRepository;
import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.context.annotation.Import;
import java.time.LocalDate;
@JdbcTest
@Import(JdbcPostRepository.class)
class JdbcRepositoryTest {
   @Autowired
   private JdbcPostRepository postRepository;
   @Test
   void testFindAllPosts() {
      Post post = new Post();
      post.setTitle("sample blog post");
      post.setDescription("sample blog post");
      post.setBody("sample blog post");
      post.setSlug("sample-blog-post");
      post.setUpdatedOn(LocalDate.now());
      post.setCreatedOn(LocalDate.now());
      postRepository.addPost(post);
      Assertions.assertThat(postRepository.findAllPosts()).hasSize(1);
   }
}
Listing 5-1

JdbcPostRepositoryTest.java

In this test class , you use an annotation called @JdbcTest, which is a sliced annotation provided by Spring Boot to test the JDBC-related functionality in your application. This annotation will create all the required beans needed to create an application context, run the JDBC code, and auto-configure the test database, transaction management features, and all the necessary beans required to run the JDBC code. Normally, many developers use another annotation called @SpringBootTest, which does all of the above and beyond. This annotation will load the complete Spring Boot application context, including beans like Controllers, Services, and other Config classes defined in your code.

Since your main goal is to test the JDBC-related code in isolation, it doesn’t make sense to load the whole application context for your JDBC-related unit tests. It makes perfect sense to use the @SpringBootTest annotation when you want to test multiple classes together or when you want to create an integration test. We will discuss testing-related concepts in much more detail in a dedicated chapter.

Note

By default, the Spring Boot features, such as external properties, and logging, are available in the ApplicationContext only if you use SpringApplication. So, Spring Boot provides the @SpringBootTest annotation to configure the ApplicationContext for tests that use SpringApplication behind the scenes.

Using Other Connection Pooling Libraries

Spring Boot, by default, pulls in the com.zaxxer.hikari.HikariCP jar and uses com.zaxxer.hikari.HikariDataSource to configure the DataSource bean.

Spring Boot checks the availability of the following classes and uses the first one available in the classpath:

  • com.zaxxer.hikari.HikariDataSource

  • org.apache.tomcat.jdbc.pool.DataSource

  • org.apache.commons.dbcp2.BasicDataSource

If you want to use Tomcat’s DataSource, you can exclude HikariCP and add the tomcat-jdbc dependency as follows:
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <exclusions>
        <exclusion>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-jdbc</artifactId>
</dependency>
You can configure specific settings for the connection pool library as follows:
spring.datasource.tomcat.*= # Tomcat Datasource specific settings
spring.datasource.hikari.*= # Hikari DataSource specific settings
spring.datasource.dbcp2.*= # Commons DBCP2 specific settings
For example, you can set the HikariCP connection pool settings as follows:
spring.datasource.hikari.allow-pool-suspension=true
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.transaction-isolation=TRANSACTION_READ_COMMITTED
spring.datasource.hikari.connection-timeout=45000

Using Spring Data JDBC

You can observe that using Spring Boot JDBC support is also not that easy to implement, as you must write the JdbcTemplate logic to query and update the database and then create an instance of RowMapper to map the query result set to your domain objects. This will become a laborious task if you have many entities to manage in your application. Luckily, Spring Team introduced a simpler way to work with JDBC using the Spring Data JDBC framework.

Spring Data JDBC comes under the umbrella of the Spring Data project, which abstracts the logic to interact with different databases without worrying about the implementation details.

For example, you can delete your PostMapper.java class and JdbcPostRepository.java classes and replace them with the following PostJdbcRepository.java interface:
public interface PostJdbcDataRepository extends CrudRepository<Post, Integer> {
   @Query("select * from posts where title= :title")
   Optional<Object> findByTitle(@Param("title") String title);
}

Shocking, isn’t it ? All that boilerplate code is replaced by hardly four lines of code. Spring Data JDBC acts like an object relational mapping (ORM) framework under the hood and provides a mapping between your Java classes and the database tables.

You will learn about Spring Data in much detail in Chapter 8 when we discuss Spring Data JPA, another project under the Spring Data umbrella that helps us work with JPA.

The CrudRepository interface provides all the functionality to save, update, read, and delete (CRUD operations) on the database. The Spring Data project provides the implementations dynamically for these operations based on the underlying framework.

Similar to the CrudRepository interface, the PagingAndSortingRepository interface automatically provides pagination and sorting support.

You can also write custom SQL queries by using the @Query annotation. The :title placeholder will be replaced with the title String variable at the time of query execution.

Database Migration with Flyway

Having a proper database migration plan for enterprise applications is crucial. The new versions of the application may be released with new features or enhancements that involve changes to the database schema. It is strongly recommended to version the database scripts to track which changes were introduced in which release and restore the database to a particular version if required.

This section looks at one popular database migration tool, Flyway, which Spring Boot supports out of the box.

You can create database migration scripts as .sql files following a specific naming pattern so that Flyway can run these scripts in order based on the current schema version. Flyway creates a database table called schema_version that keeps track of the current schema version so that it will run any pending migration scripts while performing the migration operation (Figure 5-1).

A table has ten columns and three rows. The headers are as follows, installed rank, version, description, type, script, checksum, installed by, installed on, executi ellipsis, and success, and there are three row entries.

Figure 5-1

Flyway schema_version table

You should follow this naming convention when naming the migration scripts to run in the correct order:
V{Version}__{Description}.sql

Here, {Version} is the version number and it can contain dots (.) and underscores (_). The {Description} can contain text describing the migration changes. A separator should separate {Version} and {Description}; the default is two consecutive underscores (__), but this option is configurable.

Examples:
V1__Init.sql
V1.1_CreatePostTable.sql
V1_2__UpdatePostTable.sql
To add Flyway migration support to a Spring Boot application, you just need to add the flyway-core dependency and place the migration scripts in the db/migration directory in the classpath.
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>
Create the two migration scripts shown in Listings 5-2 and 5-3 in the src/main/resources/db/migration directory.
DROP TABLE IF EXISTS COMMENTS;
DROP TABLE IF EXISTS POSTS;
CREATE TABLE POSTS
(
   ID int NOT NULL AUTO_INCREMENT,
   TITLE varchar(50) NOT NULL,
   DESCRIPTION varchar(500) NOT NULL,
   BODY LONGTEXT DEFAULT NULL,
   SLUG varchar(60) DEFAULT NULL,
   POST_STATUS ENUM ('DRAFT','PUBLISHED'),
   CREATED_ON datetime DEFAULT NULL,
   UPDATED_ON datetime DEFAULT NULL,
   PRIMARY KEY (ID)
);
CREATE TABLE COMMENTS
(
   ID int NOT NULL AUTO_INCREMENT,
   POST_ID int NOT NULL,
   TITLE varchar(200) NOT NULL,
   AUTHOR_NAME varchar(200) NOT NULL,
   BODY LONGTEXT DEFAULT NULL,
   CREATED_ON datetime DEFAULT NULL,
   UPDATED_ON datetime DEFAULT NULL,
   PRIMARY KEY (ID),
   FOREIGN KEY (POST_ID) REFERENCES POSTS(ID)
);
Listing 5-2

V1.0__Init.sql

ALTER TABLE COMMENTS ADD COLUMN DESCRIPTION varchar(50) DEFAULT NULL;
Listing 5-3

V1.1__Add_Description_Column.sql

Now, when you start the application, it will check the current schema version number (maximum of the schema_version.version column value) and run if there are any pending migration scripts with a higher version value.

If you have an existing database with tables, you can take the dump of the current database structure and make it the baseline script so that you can clean the database and start from scratch. If you don't want to dump and rerun the initial script (common in production environments), you can set spring.flyway.baseline-on-migrate=true, which will insert a baseline record in the schema_version table with the version set to 1. Now you can add migration scripts with version numbers higher than 1—say 1.1, 1_2, etc.

For more information about Flyway migrations, see https://flywaydb.org/ .

Summary

In this chapter, you learned how to use JdbcTemplate easily in Spring Boot and connect to databases like h2 and MySQL. The chapter also used SQL scripts and various connection pooling libraries to initialize a database. The next chapter explains how to use MyBatis with Spring Boot.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.143.228.40