위 그림에서 Repository Layer를 작성한다고 보면 된다.
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 | |
+---------+---------------------+------+-----+---------+----------------+
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 | |
+---------+---------------------+------+-----+---------+----------------+
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 추가
//(길이 상 생략함)
}
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 추가
//(길이 상 생략함)
}
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();
}
}
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";
}
🚩 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);
}
}
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);
}
}