고정 헤더 영역
상세 컨텐츠
본문
오늘은 Spring을 활용하여 웹에서 SQL DB를 출력하고 ,db를 추가하고, 페이지를 나누는 예제입니다. 이번작부터는 연작입니다. 다음 편도 여기에서 계속 누적이 될 것 같아요.
그리고 readme.md에 SQL문이 있습니다. 그걸 복사해서 MySQL에 넣어서 DB를 생성한 다음에 사용하시기 바랍니다.
파일 구조는 아래와 같이 되어 있습니다. 그리고 아래의 Github 주소에서 관련 파일 확인 가능합니다.
pack.controller 패키지
** InsertController.java **
package pack.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import pack.model.MemberDao;
@Controller
public class InsertController {
@Autowired
private MemberDao memberDao;
@ModelAttribute("command")
public MemberBean formBack() {
return new MemberBean();
}
private String formName = "insform";
@RequestMapping(value="insert", method=RequestMethod.GET)
public String form() {
return formName;
}
@RequestMapping(value="insert", method=RequestMethod.POST)
public String submit(MemberBean bean) {
//모델과 통신
memberDao.insData(bean);
return "redirect:/list"; // 추가 후 목록보기
}
}
** ListController.java **
package pack.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import pack.model.MemberDao;
import pack.model.MemberDto;
@Controller
public class ListController {
@Autowired
private MemberDao memberDao;
private int pageSize = 3; // 페이지 당 출력 행 수
@RequestMapping("list") // http://~~list?pageNum=? 으로 보내기
public ModelAndView listProcess(
@RequestParam(value="pageNum", defaultValue="1")String pageNum) {
//모델과 통신
int currentPage = Integer.parseInt(pageNum); //pagenum을 숫자로 받음
int startR = (currentPage -1) * pageSize; // 3페이지를 보여달라고 하면 6...
int endR = pageSize;
int count = memberDao.getMemberCount(); //페이지 수 얻기
List<MemberDto> list = null;
//list = memberDao.getMemberAll(); // 페이지 X
list = memberDao.getMemberAll(startR, endR); // 페이지 분할 O
ModelAndView view = new ModelAndView();
view.setViewName("list");
view.addObject("list", list);
view.addObject("currentPage", currentPage);
view.addObject("count", count);
view.addObject("pageSize", pageSize);
return view;
}
}
** MemberBean.java **
package pack.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import pack.model.MemberDao;
import pack.model.MemberDto;
@Controller
public class ListController {
@Autowired
private MemberDao memberDao;
private int pageSize = 3; // 페이지 당 출력 행 수
@RequestMapping("list") // http://~~list?pageNum=? 으로 보내기
public ModelAndView listProcess(
@RequestParam(value="pageNum", defaultValue="1")String pageNum) {
//모델과 통신
int currentPage = Integer.parseInt(pageNum); //pagenum을 숫자로 받음
int startR = (currentPage -1) * pageSize; // 3페이지를 보여달라고 하면 6...
int endR = pageSize;
int count = memberDao.getMemberCount(); //페이지 수 얻기
List<MemberDto> list = null;
//list = memberDao.getMemberAll(); // 페이지 X
list = memberDao.getMemberAll(startR, endR); // 페이지 분할 O
ModelAndView view = new ModelAndView();
view.setViewName("list");
view.addObject("list", list);
view.addObject("currentPage", currentPage);
view.addObject("count", count);
view.addObject("pageSize", pageSize);
return view;
}
}
package pack.controller;
public class MemberBean {
private String id, passwd, name, regdate;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRegdate() {
return regdate;
}
public void setRegdate(String regdate) {
this.regdate = regdate;
}
}
pack.model 패키지
** MemberDao.java **
package pack.model;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Repository;
import pack.controller.MemberBean;
@Repository
public class MemberDao extends JdbcDaoSupport{
public MemberDao(DriverManagerDataSource dataSource) {
setDataSource(dataSource);
}
//전체 자료 읽기
/*
public List<MemberDto> getMemberAll() {
String sql = "select * from member";
List<MemberDto> list = getJdbcTemplate().query(sql, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
MemberDto dto = new MemberDto();
dto.setId(rs.getString("id"));
dto.setPasswd(rs.getString("passwd"));
dto.setName(rs.getString("name"));
dto.setRegdate(rs.getString("regdate"));
return dto;
}
});
return list;
}
*/
//전체 자료 읽기 / 페이지 나누기
public List<MemberDto> getMemberAll(int startR, int endR) {
String sql = "select * from member order by id asc limit ?,?";
List<MemberDto> list = getJdbcTemplate()
.query(sql, new Object[] {startR, endR}, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
MemberDto dto = new MemberDto();
dto.setId(rs.getString("id"));
dto.setPasswd(rs.getString("passwd"));
dto.setName(rs.getString("name"));
dto.setRegdate(rs.getString("regdate"));
return dto;
}
});
return list;
}
public int getMemberCount() {//전체 레코드 건수(페이지 나누기용)
String sql = "select count(*) from member";
return getJdbcTemplate().queryForObject(sql, Integer.class);
}
// 추가 : jdbcDaoSupport
public void insData(MemberBean bean) {
String sql = "insert into member values(?,?,?,now())";
Object[] args = {bean.getId(), bean.getPasswd(), bean.getName()};
getJdbcTemplate().update(sql, args);
}
//부분 자료 읽기
public MemberDto getMember(String id) {
String sql = "select * from member where id=?";
MemberDto dto = (MemberDto)getJdbcTemplate()
.queryForObject(sql, new Object[]{id}, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
MemberDto dto = new MemberDto();
dto.setId(rs.getString("id"));
dto.setPasswd(rs.getString("passwd"));
dto.setName(rs.getString("name"));
dto.setRegdate(rs.getString("regdate"));
return dto;
}
});
return dto;
}
// 수정 : jdbcDaoSupport
public void upData(MemberBean bean) {
String sql = "update member set passwd=?, name=? where id=?";
getJdbcTemplate().update(sql, new Object[] {bean.getPasswd(), bean.getId(),bean.getRegdate()});
}
// 삭제 : jdbcDaoSupport
public void delData(String id) {
String sql = "delete from member where id=?";
getJdbcTemplate().update(sql, new Object[] {id});
}
}
** MemberDto.java **
package pack.model;
public class MemberDto {
private String id, passwd, name, regdate;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRegdate() {
return regdate;
}
public void setRegdate(String regdate) {
this.regdate = regdate;
}
}
** MyDataSource.java **
package pack.model;
import org.springframework.jdbc.datasource.DriverManagerDataSource; //BasicDataSource
import org.springframework.stereotype.Repository;
@Repository("dataSource")
public class MyDataSource extends DriverManagerDataSource {
public MyDataSource() {
setDriverClassName("org.mariadb.jdbc.Driver");
setUsername("root");
setPassword("123");
}
}
src 폴더
** index.html **
<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<html>
<head>
<meta charset="utf-8">
<title>Welcome</title>
</head>
<body>
<jsp:forward page="list"/>
<a href="list">Click to enter</a>
</body>
</html>
** pom.xml **
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.springframework.samples.service.service</groupId>
<artifactId>sweb15_jdbc_crud</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<properties>
<!-- Generic properties -->
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<!-- Web -->
<jsp.version>2.2</jsp.version>
<jstl.version>1.2</jstl.version>
<servlet.version>2.5</servlet.version>
<!-- Spring -->
<spring-framework.version>4.3.11.RELEASE</spring-framework.version>
<!-- Logging -->
<logback.version>1.0.13</logback.version>
<slf4j.version>1.7.5</slf4j.version>
</properties>
<dependencies>
<!-- Spring MVC -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring-framework.version}</version>
</dependency>
<!-- Other Web dependencies -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>${jstl.version}</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>${servlet.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>${jsp.version}</version>
<scope>provided</scope>
</dependency>
<!-- Spring and Transactions -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring-framework.version}</version>
</dependency>
<!-- Logging with SLF4J & LogBack -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>${logback.version}</version>
<scope>runtime</scope>
</dependency>
<!-- RDBMS : MariaDb driver -->
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.2.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.0.0.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.11.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
</dependencies>
</project>
view 폴더
** insform.jsp **
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>** 자료 추가 **</h2>
<sform:form commandName="command">
아이디 : <sform:input path="id"/><br>
비밀번호 : <sform:input path="passwd"/><br>
회원명 : <sform:input path="name"/><br>
<input type="submit" value="추가">
</sform:form>
</body>
</html>
** list.jsp **
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<!--
** 회원정보(jdbcSupport) ** <p/>
<a href="insert">추가</a>
<table border="1">
<tr>
<th>id</th><th>name</th>
</tr>
<c:forEach var="m" items="${list}">
<tr>
<td>${m.id}</td>
<td><a href="detail?id=${m.id}">${m.name}</a></td>
</tr>
</c:forEach>
</table>
-->
*회원정보(jdbcDaoSupport - paging) * <p/>
<c:if test="${count == 0}">
출력 자료가 없습니다!! <a href="insert">추가</a>
</c:if>
<c:if test="${count > 0}">
<a href="insert">추가</a>
<table border="1">
<tr>
<th>id</th><th>name</th>
</tr>
<c:forEach var="m" items="${list}">
<tr>
<td>${m.id}</td>
<td><a href="detail?id=${m.id}">${m.name}</a></td>
</tr>
</c:forEach>
<!-- page link 처리 -->
<tr>
<td colspan="2" style="text-align: center; height: 30px">
<c:set var="pageCount" value="${(count - 1) / pageSize + 1}"/>
<c:forEach var="p" begin="1" end="${pageCount}">
<c:if test="${currentPage == p}">${p}</c:if>
<c:if test="${currentPage != p}">
<a href="list?pageNum=${p}">${p}</a>
</c:if>
</c:forEach>
</td>
</tr>
</table>
</c:if>
</body>
</html>
결과 이미지
본 포스팅은 KIC 캠퍼스에서 박영권 강사의 지도하에 공부하며 작성한 리포트입니다.
혹시 잘못된 내용이 있거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.
'03_Language' 카테고리의 다른 글
| Spring - 미니 게시판 만들기 (0) | 2018.04.09 |
|---|---|
| Spring - MyBatis, Annotation을 활용하여 db 불러오기 예제 (0) | 2018.04.06 |
| Spring - 웹에서 db 수정 및 삭제하기 (0) | 2018.04.05 |
| Spring - 웹에서 db 추가하고 페이지 나누기 (0) | 2018.04.04 |
| Spring - 웹에서 기존 방식으로 db 출력하기 예제 (0) | 2018.04.04 |
| Spring - 스프링을 사용한 웹 출력 예제 (0) | 2018.04.03 |
| Spring - AOP 개념을 활용한 db 출력 (0) | 2018.04.03 |
댓글 영역