Webアプリ作成　Spring Boot
④DBからデータ取得（DAOパターン）

CREATE TABLE IF NOT EXISTS diary(
  id serial,
  category varchar(2),
  title varchar(50) NOT NULL,
  content text,
  date date NOT NULL,
  update_datetime timestamp,
  PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS category_code(
  id serial,
  group_cd varchar(2),
  cd varchar(2),
  name varchar(20),
  PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS users(
  id serial,
  user_id varchar(10) NOT NULL,
  password varchar(60) NOT NULL,
  username varchar(50),
  PRIMARY KEY(id)
)

spring.datasource.initialization-mode=always

./psql -d diary -U appuser

\dt;

package diary.entity;

import java.sql.Timestamp;

public class Diary {
	
	private int id;
	private String category;
	private String title;
	private String content;
	private String date;
	private Timestamp update_datetime;
	private String name;

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getDate() {
		return date;
	}
	public void setDate(String date) {
		this.date = date;
	}
	public Timestamp getUpdate_datetime() {
		return update_datetime;
	}
	public void setUpdate_datetime(Timestamp update_datetime) {
		this.update_datetime = update_datetime;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

package diary.repository;

import java.util.List;
import diary.entity.Diary;

public interface IDiaryDao {
	// 登録されている日記を取得
	List<Diary> findList();
}

package diary.repository;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import diary.entity.Diary;

@Repository
public class DiaryDao implements IDiaryDao {
	
	private final NamedParameterJdbcTemplate jdbcTemplate;

	@Autowired
	public DiaryDao(NamedParameterJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public List<Diary> findList() {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("SELECT d.id, d.category, d.title, d.content, TO_CHAR(d.date, 'YYYY/MM/DD') AS date, d.update_datetime, c.name "
				+ "FROM diary AS d INNER JOIN category_code AS c ON d.category = c.cd "
				+ "WHERE c.group_cd = '01'");

		String sql = sqlBuilder.toString();

		// パラメータ設定用Map
		Map<String, String> param = new HashMap<>();
		//タスク一覧をMapのListで取得
		List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql, param);
		//return用の空のListを用意
		List<Diary> list = new ArrayList<Diary>();

		//データをDiaryにまとめる
		for(Map<String, Object> result : resultList) {
			Diary diary = new Diary();
			diary.setId((int)result.get("id"));
			diary.setCategory((String)result.get("category"));
			diary.setTitle((String)result.get("title"));
			diary.setContent((String)result.get("content"));
			diary.setDate((String)result.get("date"));
			diary.setUpdate_datetime((Timestamp)result.get("update_datetime"));
			diary.setName((String)result.get("name"));
			list.add(diary);
		}
		return list;
	}
}

package diary.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import diary.repository.IDiaryDao;
import diary.entity.Diary;

@Service
@Transactional
public class DiaryService {

	private final IDiaryDao dao;

	@Autowired
	public DiaryService(IDiaryDao dao) {
		this.dao = dao;
	}

	public List<Diary> findList() {
		return dao.findList();
	}
}