상세 컨텐츠

본문 제목

Spring - 웹에서 기존 방식으로 db 출력하기 예제

Archive

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

본문






늘은 Spring을 활용하여 웹에서 SQL DB를 출력하는 예제입니다.
지난번의 db 출력 예제와 비슷한 점이 있지만 이번에는 스프링 체계에서 진행한다는 점이 조금 다르겠네요.

파일 구조는 아래와 같이 되어 있습니다. 그리고 아래의 Github 주소에서 관련 파일 확인 가능합니다.




pack.controller 패키지

** ListController.java **
package pack.controller;

import java.util.ArrayList;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import pack.model.DataDao;
import pack.model.SangpumDto;

@Controller
public class ListController {
    @Autowired
    private DataDao dataDao;
    
    
    @RequestMapping("testdb")
    public ModelAndView abc() {
        ArrayList<SangpumDto>list = dataDao.getDataAll();
        
        return new ModelAndView("showMessage", "message", list);
    }
}





pack.model 패키지

** DataDao.java **
package pack.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class DataDao {
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
    
    @Autowired
    private DataSource dataSource;
    
    public DataDao() {
        
    }
    
    public ArrayList<SangpumDto> getDataAll(){
        ArrayList<SangpumDto> list = new ArrayList<SangpumDto>();
        try {
            String sql = "select * from sangdata";
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                SangpumDto dto = new SangpumDto();
                dto.setCode(rs.getString("code"));
                dto.setSang(rs.getString("sang"));
                dto.setSu(rs.getString("su"));
                dto.setDan(rs.getString("dan"));
                list.add(dto);
            }
        } catch (Exception e) {
            System.out.println("getDataAll err : " + e);
        }finally {
            try {
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
                if(conn != null) conn.close();
            } catch (Exception e2) {
                // TODO: handle exception
            }
        }
        return list;
    }
    
}

** 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");

    }

}

** SangpumDto.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>
        <a href="testdb">Go data</a>
    </body>
</html>



** showMessage.jsp **
<!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" %>
<html>
    <head>
        <meta charset="utf-8">
        <title>Welcome</title>
    </head>
    <body>
        <h2>상품 자료(Spring-legacy로 출력)</h2>
<table border="1">
    <tr>
        <th>코드</th><th>상품</th><th>수량</th><th>단가</th>
    </tr>
    <c:forEach var="s" items="${message}">
    <tr>
        <td>${s.code}</td>
        <td>${s.sang}</td>
        <td>${s.su}</td>
        <td>${s.dan}</td>
    </tr>
    </c:forEach>
</table>        
    </body>
</html>



** mvc-config.xml **
<?xml version="1.0" encoding="UTF-8"?>


    <!-- Uncomment and your base-package here:
         <context:component-scan
            base-package="org.springframework.samples.web"/>  -->
    <context:component-scan base-package="pack.controller"/>
    <context:component-scan base-package="pack.model"/>

    <mvc:annotation-driven />

    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
            <!-- Example: a logical view name of 'showMessage' is mapped to '/WEB-INF/jsp/showMessage.jsp' -->
            <property name="prefix" value="/WEB-INF/view/"/>
            <property name="suffix" value=".jsp"/>
    </bean>

</beans>

  
** pom.xml **
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.springframework.samples.service.service</groupId>
  <artifactId>sweb14_db_legacy</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>



결과 이미지



본 포스팅은 KIC 캠퍼스에서 박영권 강사의 지도하에 공부하며 작성한 리포트입니다.
혹시 잘못된 내용이 있거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.






태그

관련글 더보기

댓글 영역