SpringBoot × H2DataBase × MyBatis
H2データベースとの接続
SpringBootプロジェクトを作成
下記のライブラリを使用する
- Spring Boot Devtools
- Lombok
- Spring Data JPA
- H2 Database
- Spring Web
テーブルと初期データを作成
src/main/resourcesにファイル作成
schema.sql
drop table if exists sheets;
create table sheets (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
parent_id INTEGER,
PRIMARY KEY(id),
constraint parent_id_constraint foreign key (parent_id) references sheets (id)
);
data.sql
insert into sheets (name) values ('シート1');
insert into sheets (name, parent_id) values ('aaa', 1);
insert into sheets (name, parent_id) values ('bbb', 1);
insert into sheets (name) values ('シート2');
insert into sheets (name, parent_id) values ('ccc', 4);
insert into sheets (name, parent_id) values ('ddd', 1);
application.propertiesを入力
application.properties
#H2Database
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:hogeDB
spring.datasource.username=sa
spring.datasource.password=pass
# MyBatis
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.type-aliases-package=jp.co.sample.persistence.entity
SpringBootApplicationを実行してH2Consoleにアクセス
http://localhost:8080/h2-console
application.propertiesに入力した内容を入力してログインすると、
コンソール画面が開く
H2データベース接続の参考
一覧取得APIの作成
pom.xmlに追記
pom.xml
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
</dependencies>
エンティティクラス
Sheet.java
package jp.co.sample.entity;
import lombok.Data;
import lombok.NoArgsConstructor;
@NoArgsConstructor
@Data
public class Sheet {
private Integer id;
private String name;
private Integer parentId;
public Sheet(String name, Integer parentId) {
this.name = name;
this.parentId = parentId;
}
}
マッパークラス
SheetMapper.java
package jp.co.sample.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import jp.co.sample.entity.Sheet;
@Mapper
public interface SheetMapper {
@Select("SELECT id, name, parent_id FROM Sheets")
List<Sheet> findAll();
@Insert("INSERT INTO (name, parent_id) VALUES (#{name}, #{parentId})")
@Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
}
マッパークラス(XMLの場合)
SheetMapper.java
package jp.co.sample.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import jp.co.sample.entity.Sheet;
@Mapper
public interface SheetMapper {
List<Sheet> findAll();
int insert(Sheet sheet);
}
SheetMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="jp.co.sample.mapper.SheetMapper">
<select id="findAll" resultType="jp.co.sample.entity.Sheet">
SELECT id, name, parent_id FROM Sheets
</select>
<insert id="insert" parameterType="jp.co.sample.entity.Sheet" useGeneratedKeys="true"
keyColumn="id" keyProperty="id">
INSERT INTO sheets (name, parent_id)
VALUES (#{name}, #{parentId})
</insert>
</mapper>
レスポンスクラス
SheetResponse.java
package jp.co.sample.reponse;
import jp.co.sample.entity.Sheet;
import lombok.Value;
@Value
public class SheetResponse {
private Integer id;
private String name;
private Integer parentId;
public SheetResponse(Sheet sheet) {
this.id = sheet.getId();
this.name = sheet.getName();
this.parentId = sheet.getParentId();
}
}
リクエストクラス
SheetRequest.java
package jp.co.sample.request;
import com.sun.istack.NotNull;
import jp.co.sample.entity.Sheet;
import lombok.AllArgsConstructor;
import lombok.ToString;
@AllArgsConstructor
@ToString
public class SheetRequest {
@NotNull
private String name;
private Integer parentId;
public Sheet convertToEntity() {
return new Sheet(name, parentId);
}
}
コントローラークラス
SheetController.java
package jp.co.sample.rest;
import java.util.List;
import java.util.stream.Collectors;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import jp.co.sample.entity.Sheet;
import jp.co.sample.reponse.SheetResponse;
import jp.co.sample.service.SheetService;
import lombok.RequiredArgsConstructor;
@RestController
@RequestMapping("/sheets")
@RequiredArgsConstructor
public class SheetController {
private final SheetService sheetService;
/**
* 一覧取得 curl -v -X GET http://localhost:8080/sheets
*/
@GetMapping
public List<SheetResponse> findAll(){
List<Sheet> sheetList = sheetService.findAll();
return sheetList.stream().map(x -> new SheetResponse(x)).collect(Collectors.toList());
}
/**
* ■追加 curl -v -X POST -H "Content-Type:application/json" -d "{\"name\":\"test\", \"parentId\":\"1\"}" http://localhost:8080/sheets
*
*/
@PostMapping
public ResponseEntity<?> insert(@RequestBody @Validated SheetRequest sheetRequest) {
Sheet sheet = sheetRequest.convertToEntity();
sheetService.insert(sheet);
URI uri = ServletUriComponentsBuilder.fromCurrentRequest()
.pathSegment(sheet.getId().toString()).build().encode().toUri();
return ResponseEntity.created(uri).build();
}
}
サービスクラス
SheetService.java
package jp.co.sample.service;
import java.util.List;
import jp.co.sample.entity.Sheet;
public interface SheetService {
List<Sheet> findAll();
int insert(Sheet sheet);
}
SheetServiceImpl.java
package jp.co.sample.service.impl;
import java.util.List;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import jp.co.sample.entity.Sheet;
import jp.co.sample.mapper.SheetMapper;
import jp.co.sample.service.SheetService;
import lombok.RequiredArgsConstructor;
@Service
@RequiredArgsConstructor
public class SheetServiceImpl implements SheetService {
private final SheetMapper sheetMapper;
@Override
@Transactional(readOnly = true)
public List<Sheet> findAll() {
return sheetMapper.findAll();
}
@Override
@Transactional(readOnly = false)
public int insert(Sheet sheet) {
return sheetMapper.insert(sheet);
}
}
疏通テスト
curlを実行して想定通りの結果が返ってくればOK