레이어드 아키텍처 (4) 방명록 실습 Repository Layer 구현

Minkyeong Kim·2021년 12월 5일

[boostcourse] Web-Backend

목록 보기

46/55

위 그림에서 Repository Layer를 작성한다고 보면 된다.

1) MYSQL에서 DB 테이블 생성

guestbook table

CREATE TABLE guestbook(
    -> id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    -> name varchar(255) NOT NULL,
    -> content text,
    -> regdate datetime,
    -> PRIMARY KEY (id)
    -> );

+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255)        | NO   |     | NULL    |                |
| content | text                | YES  |     | NULL    |                |
| regdate | datetime            | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

log table

CREATE TABLE log(
    -> id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    -> ip varchar(255) NOT NULL,
    -> method varchar(10) NOT NULL,
    -> regdate datetime,
    -> PRIMARY KEY (id)
    -> );

+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| ip      | varchar(255)        | NO   |     | NULL    |                |
| method  | varchar(10)         | NO   |     | NULL    |                |
| regdate | datetime            | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

2) DTO 클래스 생성

kr.or.connect.guestbook.dto 패키지를 먼저 생성 후 MYSQL DB에 있는 테이블대로 DTO 클래스 생성

Guestbook.java

package kr.or.connect.guestbook.dto;

import java.util.Date;

public class Guestbook {
	private Long id;
	private String name;
	private String content;
	private Date regdate;
    //getter, setter 추가
    // toString 추가
    //(길이 상 생략함)
}

Log.java

package kr.or.connect.guestbook.dto;

import java.util.Date;

public class Log {
	private Long id;
	private String ip;
	private String method;
	private Date regdate;
    //getter, setter 추가
    // toString 추가
    //(길이 상 생략함)
}

3) DAO 클래스 생성

kr.or.connect.guestbook.dao 패키지 생성 후 클래스들 생성

LogDao.java

package kr.or.connect.guestbook.dao;

import javax.sql.DataSource;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import kr.or.connect.guestbook.dto.Log;

@Repository
public class LogDao {
	private NamedParameterJdbcTemplate jdbc;
	private SimpleJdbcInsert insertAction;
	
	public LogDao(DataSource dataSource) {
		this.jdbc=new NamedParameterJdbcTemplate(dataSource);
		this.insertAction=new SimpleJdbcInsert(dataSource)
				.withTableName("log")
				.usingGeneratedKeyColumns("id"); // id가 자동으로 입력되도록 함		
	}
	
	public Long insert(Log log) {
		//SqlParameterSource를 사용해 Log 객체를 SQL 형식으로 변환
		SqlParameterSource params = new BeanPropertySqlParameterSource(log);
		
		// insert문은 내부적으로 생성해 실행, 자동으로 생성된 id값 반환
		return insertAction.executeAndReturnKey(params).longValue();
	}
}

GuestbookDaoSqls.java

SELECT * FROM guestbook LIMIT start, limit; 문법으로 SELECT 범위 제한 가능

package kr.or.connect.guestbook.dao;

public class GuestbookDaoSqls {
	public static final String SELECT_PAGING = "SELECT id, name, content, regdate FROM guestbook ORDER BY id DESC limit :start, :limit";
	public static final String DELETE_BY_ID = "DELETE FROM guestbook WHERE id = :id";
	public static final String SELECT_COUNT = "SELECT count(*) FROM guestbook";
}

GuestbookDao.java

🚩 import static으로 GuestbookDaoSqls 메소드 가져오기

package kr.or.connect.guestbook.dao;

import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import kr.or.connect.guestbook.dto.Guestbook;
import static kr.or.connect.guestbook.dao.GuestbookDaoSqls.*;
@Repository
public class GuestbookDao {
	private NamedParameterJdbcTemplate jdbc;
	private SimpleJdbcInsert insertAction;
	private RowMapper<Guestbook> rowMapper= BeanPropertyRowMapper.newInstance(Guestbook.class);
	
	public GuestbookDao(DataSource dataSource) {
		this.jdbc=new NamedParameterJdbcTemplate(dataSource);
		this.insertAction = new SimpleJdbcInsert(dataSource)
				.withTableName("guestbook")
				.usingGeneratedKeyColumns("id");
	}
	
	public List<Guestbook> selectAll(Integer start, Integer limit){
		Map<String, Integer> params=new HashMap<>();
		params.put("start", start);
		params.put("limit", limit);
		return jdbc.query(SELECT_PAGING, params, rowMapper);
	}
	
	public Long insert(Guestbook guestbook) {
		SqlParameterSource params = new BeanPropertySqlParameterSource(guestbook);
		return insertAction.executeAndReturnKey(params).longValue();
	}
	
	public int deleteById(Long id) {
		Map<String, ?> params = Collections.singletonMap("id", id);
		return jdbc.update(DELETE_BY_ID, params);
	}
	
	public int selectCount() {
		return jdbc.queryForObject(SELECT_COUNT, Collections.emptyMap(), Integer.class);
	}
}

4) Java Application에서 테스트 클래스 실행

main 메소드에서 실행하는 것이 아님
성공 시 id값을 출력
일단은 dao 클래스 내에서 테스트

GuestbookDaoTest.java

package kr.or.connect.guestbook.dao;

import java.util.Date;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import kr.or.connect.guestbook.config.ApplicationConfig;
import kr.or.connect.guestbook.dto.Guestbook;
import kr.or.connect.guestbook.dto.Log;

public class GuestbookDaoTest {

	public static void main(String[] args) {
		ApplicationContext ac = new AnnotationConfigApplicationContext(ApplicationConfig.class);
		GuestbookDao guestbookDao = ac.getBean(GuestbookDao.class);
		
        	//GuestbookDao 테스트
		Guestbook guestbook = new Guestbook();
		guestbook.setName("unknown");
		guestbook.setContent("hi there.");
		guestbook.setRegdate(new Date());
		Long id = guestbookDao.insert(guestbook);
		System.out.println("id: "+id);
		
        	//LogDao 테스트
		LogDao logDao=ac.getBean(LogDao.class);
		Log log = new Log();
		log.setIp("127.0.0.1");
		log.setMethod("insert");
		log.setRegdate(new Date());
		Long logId = logDao.insert(log);
		System.out.println("id: "+logId);

	}

}