SpringBootでH2Databaseを使って一覧取得API作成

# 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データベース接続の参考
https://it-jog.com/java/springdatajpa/starth2

## 一覧取得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作成の参考
https://medium-company.com/spring-boot-thymeleaf%E3%81%A7%E4%B8%80%E8%A6%A7%E7%94%BB%E9%9D%A2%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B/

https://qiita.com/tseno/items/98f60c8064c369b32087