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.
The points when accessing the DB with JdbcTemplate are shown below.
For basic CRUD, I think the content shown here is sufficient.
JdbcTemplate that defines the parameter part with? And NamedParameterJdbcTemplate that defines the parameter part with : parameter name.NamedParameterJdbcTemplate@Repository class annotation is added.NamedParameterJdbcTemplate into the repository class with @ Autowired etc.@Value: parameter nameBeanPropertySqlParameterSource.MapSqlParameterSource., DELETE, ʻUPDATE uses ʻupdate method of jdbcTemplate`SELECT SQL statement, use RowMapper to map the result to a specific classSELECT to get one row uses queryForObject method of jdbcTemplateSELECT to get multiple rows uses query method of jdbcTemplateIn 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
);
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
}
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;
}
};
}
}
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