SpringBoot(SpringMVC) RestAPI CRUD + DB(PostgreSQL)

1. 시작 전

1.1 테이블 생성

CREATE TABLE tb_user(
user_id varchar(30) NOT NULL PRIMARY KEY,
user_nm varchar(20),
email varchar(50),
reg_dt date,
updt_dt date
)

1.2 데이터 등록

INSERT INTO tb_user (user_id, user_nm, email, reg_dt, updt_dt) 
VALUES ('github_name', 'name', 'github_name@github.com', now(), now());

2. Start

2.1 application.properties 수정

# Server 포트 지정
server.port=8080
# database (postgres) 정보입력
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://(IP주소):(포트)/(DB명)
spring.datasource.username=DB 유저명
spring.datasource.password=DB 패스워드
# XML location (mapper 파일 위치 지정)
mybatis.mapper-locations=classpath:mapper/*.xml
# Swagger 설정
spring.mvc.pathmatch.matching-strategy=ant-path-matcher
# Exception Handler 설정
spring.mvc.throw-exception-if-no-handler-found=true
spring.web.resources.add-mappings=false
# Encoding UTF-8
server.servlet.encoding.charset=UTF-8
server.servlet.encoding.force=true

2.2 패키지 구조 확인

com
 ᄂ example
 ᄂ restapi
 ᄂ common
 | ᄂ ExceptionController.java
 ᄂ config
 | ᄂ SwaggerConfig.java
 | ᄂ WebConfig.java
 ᄂ controller
 | ᄂ RestApiController.java
 ᄂ mapper
 | ᄂ RestApiMapper.java
 ᄂ service
 | ᄂ RestApiService.java
 | ᄂ RestApiServiceImpl.java
 ᄂ vo
 ᄂ UserVO.java

3. 기타(etc)

3.1 VO 생성 쿼리(PostgreSQL)

SELECT
	'private ' ||
	CASE
		WHEN DATA_TYPE = 'character varying' THEN 'String'
		WHEN DATA_TYPE = 'numeric' THEN 'float'
		WHEN DATA_TYPE = 'integer' THEN 'int'
	END 
	|| ' ' || LOWER(COLUMN_NAME) || ';'
FROM
	INFORMATION_SCHEMA.COLUMNS
WHERE
	TABLE_CATALOG = 'DB명'
	AND TABLE_NAME = '테이블명'
	AND table_schema = '스키마명'
ORDER BY
	ORDINAL_POSITION;