Access the built-in h2db of spring boot with jdbcTemplate

1.First of all

This time, I would like to explain how to access the embedded h2db of spring boot as DB by using jdbcTemplate of spring-jdbc. However, there is almost no talk about h2db, it is simply how to use jdbcTemplate. Please refer to "How to use embedded h2db with spring boot" for the preparation of embedded h2db.

If you want to access DB with other ORMapper instead of jdbcTemplate, please refer to the following article.

2. Points of DB access by JdbcTemplate

The points when accessing the DB with JdbcTemplate are shown below. For basic CRUD, I think the content shown here is sufficient.

3. Sample

3.1. Table definition

In spring boot, the schema.sql file directly under the classpath is automatically executed at startup. Describe the CREATE TABLE statement of the table to be used this time in this file. Please refer to the following official guidelines for the data type of h2db. http://www.h2database.com/html/datatypes.html

src/main/resources/schema.sql


-- file_info table
CREATE TABLE IF NOT EXISTS file_info (
    file_id varchar(100) PRIMARY KEY,
    file_type varchar(100) NOT NULL,
    file_name varchar(200) NOT NULL,
    file_path varchar(200) NOT NULL,
    content_type varchar(100) NOT NULL,
    content_length bigint NOT NULL,
    registered_date timestamp NOT NULL
);

3.2. Classes to be mapped

Define the class to be mapped corresponding to the table. However, it is an ordinary POJO class.

FileInfo.java


package com.example.demo.domain.model;

import java.io.Serializable;
import java.sql.Timestamp;

public class FileInfo implements Serializable {

    private static final long serialVersionUID = 1L;

    private String contentType;
    private long contentLength;
    private String fileName;
    private String fileType;
    private String fileId;
    private String filePath;
    private Timestamp registeredDate;

    // constructor,setter,getter omitted
}

3.3. Repository class

Define the main repository class for this article. The points are as described in "[2. DB access points by JdbcTemplate](# 2-db access points by jdbctemplate)".

** As a precaution for implementation, if there are 0 search results, ʻorg.springframework.dao.EmptyResultDataAccessExceptionwill occur. This time, like other ORMappers, if there are 0 cases,null` is returned. ** **

It is our convenience to wrap DataAccessException in DemoSystemException. It is not directly related to how to use jdbcTemplate.

FileInfoRepository.java


package com.example.demo.domain.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.example.demo.domain.common.DemoSystemException;
import com.example.demo.domain.model.FileInfo;

@Repository
public class FileInfoRepository {

    private static final Logger LOGGER = LoggerFactory.getLogger(FileInfoRepository.class);

    private static final String INSERT_SQL = "INSERT INTO file_info ("
            + "file_id, file_type, file_name, file_path, content_type, content_length, registered_date) values ("
            + ":fileId, :fileType, :fileName, :filePath, :contentType, :contentLength, :registeredDate)";

    private static final String DELETE_BY_KEY_SQL = "DELETE FROM file_info WHERE file_id = :fileId";

    private static final String UPDATE_BY_KEY_SQL = "UPDATE file_info SET file_type = :fileType, file_name = :fileName, "
            + "file_path = :filePath, content_type = :contentType, content_length = :contentLength, registered_date = :registeredDate "
            + "WHERE file_id = :fileId";

    private static final String FIND_ONE_SQL = "SELECT file_id, file_type, file_name, file_path, content_type, content_length, registered_date "
            + "FROM file_info WHERE file_id = :fileId";

    private static final String FIND_ALL_SQL = "SELECT file_id, file_type, file_name, file_path, content_type, content_length, registered_date "
            + "FROM file_info ORDER BY file_type, registered_date";

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public int insert(FileInfo fileInfo) {
        try {
            SqlParameterSource param = new BeanPropertySqlParameterSource(fileInfo);
            return jdbcTemplate.update(INSERT_SQL, param);
        } catch(DataAccessException e) {
            LOGGER.error("DataAccessError : INSERT_SQL param:{}, error:{}", fileInfo, e);
            throw new DemoSystemException("DataAccessError : FileInfoRepository INSERT_SQL", e);
        }
    }

    public int deleteByKey(String fileId) {
        try {
            SqlParameterSource param = new MapSqlParameterSource().addValue("fileId", fileId);
            return jdbcTemplate.update(DELETE_BY_KEY_SQL, param);
        } catch(DataAccessException e) {
            LOGGER.error("DataAccessError : DELETE_BY_KEY_SQL param:{}, error:{}", fileId, e);
            throw new DemoSystemException("DataAccessError : FileInfoRepository DELETE_BY_KEY_SQL", e);
        }
    }

    public int updateByKey(FileInfo fileInfo) {
        try {
            SqlParameterSource param = new BeanPropertySqlParameterSource(fileInfo);
            return jdbcTemplate.update(UPDATE_BY_KEY_SQL, param);
        } catch(DataAccessException e) {
            LOGGER.error("DataAccessError : UPDATE_BY_KEY_SQL param:{}, error:{}", fileInfo, e);
            throw new DemoSystemException("DataAccessError : FileInfoRepository UPDATE_BY_KEY_SQL", e);
        }
    }

    public FileInfo fineOne(String fileId) {
        try {
            SqlParameterSource param = new MapSqlParameterSource().addValue("fileId", fileId);
            return jdbcTemplate.queryForObject(FIND_ONE_SQL, param, fileInfoRowMapper());
        } catch(EmptyResultDataAccessException e) {
            return null;
        } catch(DataAccessException e) {
            LOGGER.error("DataAccessError : FIND_ONE_SQL param:{}, error:{}", fileId, e);
            throw new DemoSystemException("DataAccessError : FileInfoRepository FIND_ONE_SQL", e);
        }
    }

    public List<FileInfo> findAll() {
        try {
            return jdbcTemplate.query(FIND_ALL_SQL, fileInfoRowMapper());
        } catch(EmptyResultDataAccessException e) {
            return null;
        } catch(DataAccessException e) {
            LOGGER.error("DataAccessError : FIND_ALL_SQL error:{}", e);
            throw new DemoSystemException("DataAccessError : FileInfoRepository FIND_ALL_SQL", e);
        }
    }

    private RowMapper<FileInfo> fileInfoRowMapper() {

        return new RowMapper<FileInfo>() {
            @Override
            public FileInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                FileInfo fileInfo = new FileInfo();
                fileInfo.setContentLength(rs.getLong("content_length"));
                fileInfo.setContentType(rs.getString("content_type"));
                fileInfo.setFileId(rs.getString("file_id"));
                fileInfo.setFileName(rs.getString("file_name"));
                fileInfo.setFilePath(rs.getString("file_path"));
                fileInfo.setFileType(rs.getString("file_type"));
                fileInfo.setRegisteredDate(rs.getTimestamp("registered_date"));
                return fileInfo;
            }
        };
    }
}

4. Finally

This time, I explained how to access the embedded h2db of spring boot as DB by using jdbcTemplate of spring-jdbc. For simple DB access, I think jdbcTemplate is enough without installing ORMapper.

Recommended Posts

Access the built-in h2db of spring boot with jdbcTemplate
How to use built-in h2db with spring boot
A story packed with the basics of Spring Boot (solved)
Extract SQL to property file with jdbcTemplate of spring boot
See the behavior of entity update with Spring Boot + Spring Data JPA
How to access Socket directly with the TCP function of Spring Integration
The story of raising Spring Boot 1.5 series to 2.1 series
Let's check the feel of Spring Boot + Swagger 2.0
Download with Spring Boot
Organize the differences in behavior of @NotBlank, @NotEmpty, @NotNull with Spring Boot + Thymeleaf
Resource handler settings when delivering SPA with the static resource function of Spring Boot
Specify the encoding of static resources in Spring Boot
05. I tried to stub the source of Spring Boot
I tried to reduce the capacity of Spring Boot
Create Restapi with Spring Boot ((1) Until Run of App)
How to boot by environment with Spring Boot of Maven
Control the processing flow of Spring Batch with JavaConfig.
[Spring Boot] The story that the bean of the class with ConfigurationProperties annotation was not found
Generate barcode with Spring Boot
Implement GraphQL with Spring Boot
Get started with Spring boot
Hello World with Spring Boot!
Run LIFF with Spring Boot
SNS login with Spring Boot
File upload with Spring Boot
Spring Boot starting with copy
Spring Boot starting with Docker
Hello World with Spring Boot
Set cookies with Spring Boot
Use Spring JDBC with Spring Boot
Add module with Spring Boot
Getting Started with Spring Boot
Create microservices with Spring Boot
Send email with spring boot
The story of raising Spring Boot from 1.5 series to 2.1 series part2
Output embedded Tomcat access log to standard output with Spring Boot
About the function of Spring Boot due to different versions
Change the injection target for each environment with Spring Boot 2
Try hitting the zip code search API with Spring Boot
Introduction of library ff4j that realizes FeatureToggle with Spring Boot
Use Basic Authentication with Spring Boot
Memorandum of understanding when Spring Boot 1.5.10 → Spring Boot 2.0.0
gRPC on Spring Boot with grpc-spring-boot-starter
Create an app with Spring Boot 2
Hot deploy with Spring Boot development
Database linkage with doma2 (Spring boot)
Get a proxy instance of the component itself in Spring Boot
See the relative redirect behavior with the server.tomcat.use-relative-redirects setting in Spring Boot
Spring Boot programming with VS Code
Until "Hello World" with Spring Boot
Inquiry application creation with Spring Boot
Going out of message (Spring boot)
Get validation results with Spring Boot
spring boot access authorization RESTful API
(Intellij) Hello World with Spring Boot
Create an app with Spring Boot
[Spring Boot] Role of each class
Google Cloud Platform with Spring Boot 2.0.0
Check date correlation with Spring Boot
I tried GraphQL with Spring Boot
[Java] LINE integration with Spring Boot