상세 컨텐츠

본문 제목

Spring - 웹에서 db 추가하고 페이지 나누기

Archive

by 에스프리터 2018. 4. 4. 18:08

본문






늘은 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");
        setUrl("jdbc:mysql://localhost:3306/test");
        setUsername("root");
        setPassword("123");

    }

}

src 폴더

** index.html **
<!DOCTYPE html>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
    
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags"%>

<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 **
  <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"%>
    <%@taglib prefix="sform" uri="http://www.springframework.org/tags/form" %>
<!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"%>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!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 캠퍼스에서 박영권 강사의 지도하에 공부하며 작성한 리포트입니다.
혹시 잘못된 내용이 있거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.







태그

관련글 더보기

댓글 영역