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

API作成の参考