[도트타이머] 2. 스프링부트에 MySQL 연동

1. MySQL 연동

MySQL에서 스키마 만든 후, 아래 파일 중 하나에 코드를 추가한다.

 

1) applicaion.properties

• key-value형식

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver // com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/스키마이름r?serverTimezone=UTC&characterEncoding=UTF-8
spring.datasource.username=계정명
spring.datasource.password=비밀번호

spring.jpa.show-sql=true // Runtime때 콘솔에 SQL 쿼리문 출력
spring.jpa.properties.hibernate.format_sql=true // Runtime 때 SQL 커리문 예쁘게 출력
spring.jpa.hibernate.ddl-auto=create-drop // 테이블 자동 생성, 자동 삭제

spring.sql.init.mode=always // schema.sql, data.sql로 스키마 및 데이터 생성

2) application.yml

• 계층적 구조
• 반복되는 접두사 없음
• 구분자 이용하여 파일 분기 가능

spring:
	datasource:com.mysql.cj.jdbc.Driver
    	driver-class-name:
        url: jdbc:mysql://localhost:3306/스키마이름r?serverTimezone=UTC&characterEncoding=UTF-8
        username: 계정명
        password: 비밀번호

둘 중 하나 입력 후, main함수가 있는 파일에서 Run해서 http://localhost:포트/spring가 에러없이 실행되면 접속 성공이다.

* 에러

아래 에러는 DB에 해당 스키마 이름이 없을 때 생기는 에러이다.

Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]

아래 에러는 Intellij의 File > Invalid Caches에서 Invalidatd and Run을 실행해야 한다.

execution failed for task ':=application.main()'. > process 'command 'c:/program files/java/jdk-17.0.5/bin/java.exe'' finished with non-zero exit value 1

2. 스키마 및 데이터 생성

main - resources 폴더 아래에 schema.sql과 data.sql을 추가하여 직접 스키마와 데이터를 생성했다.

schema.sql

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS USER CASCADE;
DROP TABLE IF EXISTS USER_ROLES CASCADE;
DROP TABLE IF EXISTS USER_SEQ CASCADE;
DROP TABLE IF EXISTS STUDY_GROUP CASCADE;
DROP TABLE IF EXISTS GROUP_JOIN CASCADE;
DROP TABLE IF EXISTS PLAN CASCADE;
DROP TABLE IF EXISTS PLAN_INFO CASCADE;
DROP TABLE IF EXISTS REVIEW CASCADE;
DROP TABLE IF EXISTS REVIEW_LIKE CASCADE;
DROP TABLE IF EXISTS FOLLOW CASCADE;
DROP TABLE IF EXISTS COIN CASCADE;
DROP TABLE IF EXISTS DONATE CASCADE;
SET foreign_key_checks = 1;

CREATE TABLE USER (
	-- Field Type Null Default
	id int not null auto_increment,
	coin_count int not null default 0,
	email varchar(255) not null unique,
	introduction varchar(255),
	name varchar(255),
	opened bit(1) not null default b'0',
	password varchar(300) not null,
	premium bit(1) not null default b'0',
	img varchar(255),
	refresh_token varchar(255),

	-- Key
	primary key(id)
);

CREATE TABLE USER_ROLES (
    user_id int	not null,
    roles varchar(255),

    constraint fk_user_roles_to_user foreign key(user_id) references user(id) on delete restrict on update restrict
);

CREATE TABLE USER_SEQ (
    next_val bigint
);

CREATE TABLE FOLLOW (
    id int not null auto_increment,
    follower_id int not null,
    following_id int not null,

    primary key(id),
    constraint fk_follower_to_user foreign key(follower_id) references user(id) on delete restrict on update restrict,
    constraint fk_following_to_user foreign key(following_id) references user(id) on delete restrict on update restrict
);

CREATE TABLE COIN (
    id int not null auto_increment,
    coin_count int not null,
    studied_at date,
    user_id int not null,

    primary key(id),
    constraint fk_coin_to_user foreign key(user_id) references user(id) on delete restrict on update restrict
);

CREATE TABLE DONATE (
    id int not null auto_increment,
    coin_count int not null,
    donated_at date,
    user_id int not null,

    primary key(id),
    constraint fk_donate_to_user foreign key(user_id) references user(id) on delete restrict on update restrict
);

CREATE TABLE STUDY_GROUP (
    id int not null auto_increment,
    category varchar(255),
    created_at date,
    details varchar(255),
    join_count int not null,
    name varchar(255),
    password varchar(255),
    theme varchar(255),
    user_id int not null,

    primary key(id),
    constraint fk_study_group_to_user foreign key(user_id) references user(id) on delete restrict on update restrict
);

CREATE TABLE GROUP_JOIN (
    id int not null auto_increment,
    joined_at date,
    study_group_id int not null,
    user_id int not null,

    primary key(id),
    constraint fk_group_join_to_study_group foreign key(study_group_id) references study_group(id) on delete restrict on update restrict,
    constraint fk_group_join_to_user foreign key(user_id) references user(id) on delete restrict on update restrict
);

CREATE TABLE PLAN_INFO (
    id int not null auto_increment,
    category varchar(255),
    color varchar(255),
    completed_at date,
    repeat_day varchar(255),
    title varchar(255),

    primary key(id)
);

CREATE TABLE PLAN (
    id int not null auto_increment,
    end_time time,
    recorded bit(1) not null,
    start_time time,
    coin_id int not null,
    plan_info_id int not null,

    primary key(id),
    constraint fk_plan_to_coin foreign key(coin_id) references coin(id) on delete restrict on update restrict,
    constraint fk_plan_to_plan_info foreign key(plan_info_id) references plan_info(id) on delete restrict on update restrict
);

CREATE TABLE REVIEW (
    id int not null auto_increment,
    bad varchar(255),
    good varchar(255),
    plan varchar(255),
    reviewed_at	date,
    user_id int not null,

    primary key(id),
    constraint fk_review_to_user foreign key(user_id) references user(id) on delete restrict on update restrict
);

CREATE TABLE REVIEW_LIKE (
    id int not null auto_increment,
    review_id int not null,
    user_id int not null,

    primary key(id),
    constraint fk_review_like_to_review foreign key(review_id) references review(id) on delete restrict on update restrict,
    constraint fk_review_like_to_user foreign key(user_id) references user(id) on delete restrict on update restrict
);