【食事内容管理Webアプリ】データベースから値を取得する

create table dishes (
  dish_code CHAR(7) not null
  , dish_name VARCHAR(90) not null
  , primary key (dish_code)
)

create table meal_types (
  meal_type_code CHAR(1) not null
  , meal_type_name VARCHAR(30) not null
  , color VARCHAR(12) not null
  , primary key (meal_type_code)
)

create table menu (
  menu_id BIGINT not null auto_increment
  , eating_date DATE not null
  , meal_type_code CHAR(1) not null
  , dish_code CHAR(7) not null
  , primary key (menu_id)
  , constraint fk_dish_code foreign key (dish_code) references dishes(dish_code)
  , constraint fk_meal_type_code foreign key (meal_type_code) references meal_types(meal_type_code)
)

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jdbc' // 追加
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	compileOnly 'org.projectlombok:lombok'
	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	runtimeOnly 'com.mysql:mysql-connector-j' // 追加
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

# データベースのURL(db_foodappスキーマに接続する)
spring.datasource.url=jdbc:mysql://localhost:3306/db_foodapp
# ユーザ名
spring.datasource.username=root
# パスワード
spring.datasource.password=*******
# ドライバ
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 文字コード
spring.sql.init.encoding=utf-8

@Controller
@RequestMapping("/foodapp")
public class FoodAppController {
	
	@Autowired
	CalendarManagementService calendarManagementService;
	
	@GetMapping("calendar")
	public String showCalendar(HttpSession session, Model model) {
		
		Date date = new Date(session.getCreationTime());
		String year = new SimpleDateFormat("yyyy").format(date);
		String month = new SimpleDateFormat("MM").format(date);
		
		List<EventBean> events = calendarManagementService.getEventsOfMonth(year, month);
		model.addAttribute("events", events);
		
		return "calendar";
	}
	
}

@Service
class CalendarManagementServiceImpl implements CalendarManagementService {

	@Autowired
	CalendarRepository calendarRepository;
	
	@Override
	public List<EventBean> getEventsOfMonth(String year, String month) {
		return calendarRepository.getEvents(year, month);
	}
	
}

@Repository
public class CalendarRepositoryImpl implements CalendarRepository {
	
	@Autowired
	JdbcTemplate jdbcTemplate;

	@Override
	public List<EventBean> getEvents(String year, String month) {
		String date = year + month + "01";
		
		List<EventBean> events = jdbcTemplate.query(
				SqlConst.getQueryFromXml(SqlConst.MENU_FOR_EACH_MONTH),
				(rs, rowNum) -> new EventBean()
										.start(rs.getString("start"))
										.title(rs.getString("title"))
										.backgroundColor(rs.getString("backgroundColor")),
				date,
				date);
		
		return events;
	}

}

public class SqlConst {
	
	private static final String XML_FOLDER_PATH = System.getProperty("user.dir")
			+ "\\src\\main\\resources\\sql\\";
	
	public static final String MENU_FOR_EACH_MONTH = "menu.forEachMonth";
	
	public static String getQueryFromXml(String queryId) {

		String sql = null;
		
		// 引数からテーブル名とIDを取得
		String tableName = queryId.split("\\.")[0];
		String id = queryId.split("\\.")[1];
		
		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		
		try {
			DocumentBuilder builder = factory.newDocumentBuilder();
			
			// 引数よりxmlファイルを特定
			Document doc = builder.parse(new File(XML_FOLDER_PATH + tableName + ".xml"));

			NodeList sqlList = doc.getElementsByTagName("query");

			// idが同じノードの値を取得する
			for (int i = 0; i < sqlList.getLength(); i++) {
				Node node = sqlList.item(i);
				Node attr = node.getAttributes().getNamedItem("id");

				if (attr.getNodeValue().equals(id)) {
					sql = node.getTextContent();
					break;
				}
			}

		} catch (ParserConfigurationException | SAXException | IOException e) {
			// 今回、例外処理は割愛
			e.printStackTrace();
		}

		return Objects.nonNull(sql) ? sql.replaceAll("^\t", "") : null;
	}
}

<?xml version="1.0" encoding="UTF-8"?>
<sql table="menu">
	<query id="forEachMonth" comment="各月のメニューを取得">
		<![CDATA[
			select
				menu.eating_date start
				, dishes.dish_name title
				, menu.meal_type_code code
				, meal_types.color backgroundColor
			from
				menu
			inner join
				dishes
			on
				menu.dish_code = dishes.dish_code
			inner join
				meal_types
			on
				menu.meal_type_code = meal_types.meal_type_code
			where
				menu.eating_date between str_to_date(?, '%Y%m%d') and last_day(str_to_date(?, '%Y%m%d'))
			order by
				start
				, code
		]]>
	</query>
</sql>

@Data
public class EventBean {
  // 食べた日付
	private String start;
	// 食べた料理名
	private String title;
	// 朝食：red、昼食：orange、夕食：blue
	private String backgroundColor;
	// 画面に表示する際の境界線の色
	private String borderColor = "white";
	// startフィールドを更新して自身の参照値を返す
	public EventBean start(String start) {
		this.start = start;
		return this;
	}
	// titleフィールドを更新して自身の参照値を返す
	public EventBean title(String title) {
		this.title = title;
		return this;
	}
	// backgroundColorフィールドを更新して自身の参照値を返す
	public EventBean backgroundColor(String backgroundColor) {
		this.backgroundColor = backgroundColor;
		return this;
	}
}