7. 스프링 DB 연동하기

우선 테스트 도중에 에러가 좀 있어서 버전 변경을 좀 했습니다 ㅠㅠ 저도 아직 초보라 이해좀 부탁드려요 ㅎㅎ

우선 스프링 버전을 4.2.3 버전으로 바꿧어요 ㅠㅠ 컨트롤러를 못찾나 ? 여튼 아시는 분 있으면 조언 부탁드리며

저도 다시 테스트 해볼게요 ㅎㅎ

    <properties>
        <java-version>1.8</java-version>
        <org.springframework-version>4.2.3.RELEASE</org.springframework-version>
        <org.aspectj-version>1.6.10</org.aspectj-version>
        <org.slf4j-version>1.6.6</org.slf4j-version>
    </properties>

Pom.xml 에 추가해줍니다.

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.3</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.8.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>4.3.8.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.bgee.log4jdbc-log4j2</groupId>
            <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
            <version>1.16</version>
        </dependency>

root-context.xml

* 계정과 비밀번호는 수정 바랍니다.~

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd
        http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">
    
    <!-- Root Context: defines shared resources visible to all other web components -->
            <!-- MariaDB JDBC DataSource -->
 
 
  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy" />
        <property name="url" value="jdbc:log4jdbc:mysql://127.0.0.1:3306/test?useSSL=false" />
        <property name="username" value="root" />
        <property name="password" value="비밀번호" />
    </bean>
    
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/mybatis-config.xml" />
        <property name="mapperLocations" value="classpath:/mappers/**/**/*.xml" />
    </bean>
    
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate" destroy-method="clearCache">
        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
    </bean>  
 
    <context:component-scan base-package="com"></context:component-scan>
 
 
</beans>

다음과 같이 만들어 줍니다. 

LottoController.Class

package com.sjh.rotto;
 
import java.util.List;
 
import javax.inject.Inject;
 
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
 
@Controller
public class LottoController {
    
    @Inject
    private LottoService service;
    
    @RequestMapping("/lotto.do")
    public String lottoInfo(Model model,LottoVO vo) {
        
        List<LottoVO> list = service.lottoInfo(vo);
        model.addAttribute("list", list);
        
        return "lotto/lotto";
    }
}
 

LottoService.Class

package com.sjh.rotto;
 
import java.util.List;
 
public interface LottoService {
    
    public List<LottoVO> lottoInfo(LottoVO vo);         //로또 정보 번호 조회
 
}
 

LottoServiceImpl.Class

package com.sjh.rotto;
 
import java.util.List;
 
import javax.inject.Inject;
 
import org.springframework.stereotype.Service;
 
@Service
public class LottoServiceImpl implements LottoService{
 
    @Inject
    private LottoDAO dao;
 
    @Override
    public List<LottoVO> lottoInfo(LottoVO vo) {
        
        return dao.lottoInfo(vo);
    }
    
    
 
}
 

LottoDAO.Class

package com.sjh.rotto;
 
import java.util.List;
 
public interface LottoDAO {
 
    public List<LottoVO> lottoInfo(LottoVO vo);
}
 

LottoDAOImpl.Class

package com.sjh.rotto;
 
import java.util.List;
 
import javax.inject.Inject;
 
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Repository;
 
@Repository
public class LottoDAOImpl implements LottoDAO {
 
    @Inject
    private SqlSession session;
 
    @Override
    public List<LottoVO> lottoInfo(LottoVO vo) {
        
        return session.selectList("lottoList",vo);
    }
 
    
}
 

LottoVO.Class

package com.sjh.rotto;
 
public class LottoVO {
 
    private int YEAR;                //년도
    private int TIMES;                //회차
    private String LOTTERY_DAY;        //추첨일
    private String WINNER_1;            //1등 당첨자
    private String WIN_MONEY_1;            //1등 당첨금액
    private String WINNER_2;            //2등 당첨자
    private String WIN_MONEY_2;            //1등 당첨금액
    private String WINNER_3;            //3등 당첨자
    private String WIN_MONEY_3;            //1등 당첨금액
    private String WINNER_4;            //4등 당첨자
    private String WIN_MONEY_4;            //1등 당첨금액
    private String WINNER_5;            //5등 당첨자
    private String WIN_MONEY_5;            //1등 당첨금액
    private String ROTO_NUM1;            //당첨번호1
    private String ROTO_NUM2;            //당첨번호2
    private String ROTO_NUM3;            //당첨번호3
    private String ROTO_NUM4;            //당첨번호4
    private String ROTO_NUM5;            //당첨번호5
    private String ROTO_NUMB;            //보너스번호
    
    public int getYEAR() {
        return YEAR;
    }
    public void setYEAR(int yEAR) {
        YEAR = yEAR;
    }
    public int getTIMES() {
        return TIMES;
    }
    public void setTIMES(int tIMES) {
        TIMES = tIMES;
    }
    public String getLOTTERY_DAY() {
        return LOTTERY_DAY;
    }
    public void setLOTTERY_DAY(String lOTTERY_DAY) {
        LOTTERY_DAY = lOTTERY_DAY;
    }
    public String getWINNER_1() {
        return WINNER_1;
    }
    public void setWINNER_1(String wINNER_1) {
        WINNER_1 = wINNER_1;
    }
    public String getWIN_MONEY_1() {
        return WIN_MONEY_1;
    }
    public void setWIN_MONEY_1(String wIN_MONEY_1) {
        WIN_MONEY_1 = wIN_MONEY_1;
    }
    public String getWINNER_2() {
        return WINNER_2;
    }
    public void setWINNER_2(String wINNER_2) {
        WINNER_2 = wINNER_2;
    }
    public String getWIN_MONEY_2() {
        return WIN_MONEY_2;
    }
    public void setWIN_MONEY_2(String wIN_MONEY_2) {
        WIN_MONEY_2 = wIN_MONEY_2;
    }
    public String getWINNER_3() {
        return WINNER_3;
    }
    public void setWINNER_3(String wINNER_3) {
        WINNER_3 = wINNER_3;
    }
    public String getWIN_MONEY_3() {
        return WIN_MONEY_3;
    }
    public void setWIN_MONEY_3(String wIN_MONEY_3) {
        WIN_MONEY_3 = wIN_MONEY_3;
    }
    public String getWINNER_4() {
        return WINNER_4;
    }
    public void setWINNER_4(String wINNER_4) {
        WINNER_4 = wINNER_4;
    }
    public String getWIN_MONEY_4() {
        return WIN_MONEY_4;
    }
    public void setWIN_MONEY_4(String wIN_MONEY_4) {
        WIN_MONEY_4 = wIN_MONEY_4;
    }
    public String getWINNER_5() {
        return WINNER_5;
    }
    public void setWINNER_5(String wINNER_5) {
        WINNER_5 = wINNER_5;
    }
    public String getWIN_MONEY_5() {
        return WIN_MONEY_5;
    }
    public void setWIN_MONEY_5(String wIN_MONEY_5) {
        WIN_MONEY_5 = wIN_MONEY_5;
    }
    public String getROTO_NUM1() {
        return ROTO_NUM1;
    }
    public void setROTO_NUM1(String rOTO_NUM1) {
        ROTO_NUM1 = rOTO_NUM1;
    }
    public String getROTO_NUM2() {
        return ROTO_NUM2;
    }
    public void setROTO_NUM2(String rOTO_NUM2) {
        ROTO_NUM2 = rOTO_NUM2;
    }
    public String getROTO_NUM3() {
        return ROTO_NUM3;
    }
    public void setROTO_NUM3(String rOTO_NUM3) {
        ROTO_NUM3 = rOTO_NUM3;
    }
    public String getROTO_NUM4() {
        return ROTO_NUM4;
    }
    public void setROTO_NUM4(String rOTO_NUM4) {
        ROTO_NUM4 = rOTO_NUM4;
    }
    public String getROTO_NUM5() {
        return ROTO_NUM5;
    }
    public void setROTO_NUM5(String rOTO_NUM5) {
        ROTO_NUM5 = rOTO_NUM5;
    }
    public String getROTO_NUMB() {
        return ROTO_NUMB;
    }
    public void setROTO_NUMB(String rOTO_NUMB) {
        ROTO_NUMB = rOTO_NUMB;
    }
 
    
    
}

mybatis 설정 부분입니다. 다음과 같이 만들어주세요

lotto.xml

<?xml version="1.0" encoding="UTF-8"?>
 
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
<mapper namespace="com.sjh.rotto">
 
    <select id="lottoList"  resultType="com.sjh.rotto.LottoVO">
    
        select * from tbl_lotto
    
    </select>
    
    
</mapper>

log4jdbc.log4j2.properties

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

logback.xml

<?xml version="1.0" encoding="UTF-8"?>
 
<configuration>
    <include resource="org/springframework/boot/logging/logback/base.xml"/>
    <!-- log4jdbc-log4j2 -->
    <logger name="jdbc.sqlonly" level="DEBUG"/>
    <logger name="jdbc.sqltiming" level="INFO"/>
    <logger name="jdbc.audit" level="WARN"/>
    <logger name="jdbc.resultset" level="OFF"/>
    <logger name="jdbc.resultsettable" level="ERROR"/>
    <logger name="jdbc.connection" level="INFO"/>
</configuration>

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
 
 
 
<configuration>
 
    <typeAliases>        
         <package name="com.sjh.rotto"/>
    </typeAliases>
</configuration>

이제 화면에서 넘어온 값을 뿌려주기만 하면 됩니다,

상단에 JSTL 추가하시고 테이블에 forEach문을 이용해서 출력해주면 됩니다.

<%@ 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>
<%@include file="../include/meta.jsp" %>
<body class="hold-transition sidebar-mini">
<!-- Site wrapper -->
<div class="wrapper">
  <!-- Navbar -->
  <%@include file="../include/navbar.jsp" %>
  <!-- Main Sidebar Container -->
  <%@include file="../include/sidebar.jsp" %>
  <!-- Content Wrapper. Contains page content -->
  <div class="content-wrapper">
    <!-- Content Header (Page header) -->
    <section class="content-header">
      <div class="container-fluid">
        <div class="row mb-2">
          <div class="col-sm-6">
            <h1>First_Project</h1>
          </div>
          <div class="col-sm-6">
            <ol class="breadcrumb float-sm-right">
              <li class="breadcrumb-item"><a href="#">Home</a></li>
              <li class="breadcrumb-item active">First_Project</li>
            </ol>
          </div>
        </div>
      </div><!-- /.container-fluid -->
    </section>
 
    <!-- Main content -->
    <section class="content">
 
      <!-- Default box -->
      <div class="card">
        <div class="card-header">
          <h3 class="card-title">Title</h3>
 
          <div class="card-tools">
            <button type="button" class="btn btn-tool" data-widget="collapse" data-toggle="tooltip" title="Collapse">
              <i class="fa fa-minus"></i></button>
            <button type="button" class="btn btn-tool" data-widget="remove" data-toggle="tooltip" title="Remove">
              <i class="fa fa-times"></i></button>
          </div>
        </div>
        <div class="card-body">
             <div class="table-responsive">
                                <table class="table table-hover">
                                    <thead>
                                        <tr>
                                            <th>년도</th>
                                            <th>회차</th>
                                            <th>추첨일</th>
                                            <th>1등당첨자</th>
                                            <th>1등당첨금액</th>
                                            <th>2등당첨자</th>
                                            <th>2등당첨금액</th>
                                            <th>3등당첨자</th>
                                            <th>3등당첨금액</th>
                                            <th>4등당첨자</th>
                                            <th>4등당첨금액</th>
                                            <th>5등당첨자</th>
                                            <th>5등당첨금액</th>
                                            <th>당첨번호1</th>
                                            <th>당첨번호2</th>
                                            <th>당첨번호3</th>
                                            <th>당첨번호4</th>
                                            <th>당첨번호5</th>
                                            <th>보너스번호</th>    
                                        </tr>
                                    </thead>
                                    <tbody>
                                    <c:forEach items="${list}" var="list">
                                        <tr>
                                            <td>${list.YEAR}</td>
                                            <td>${list.TIMES}</td>
                                            <td>${list.LOTTERY_DAY}</td>
                                            <td>${list.WINNER_1}</td>
                                            <td>${list.WIN_MONEY_1}</td>
                                            <td>${list.WINNER_2}</td>
                                            <td>${list.WIN_MONEY_2}</td>
                                            <td>${list.WINNER_3}</td>
                                            <td>${list.WIN_MONEY_3}</td>
                                            <td>${list.WINNER_4}</td>
                                            <td>${list.WIN_MONEY_4}</td>
                                            <td>${list.WINNER_5}</td>
                                            <td>${list.WIN_MONEY_5}</td>
                                            <td>${list.ROTO_NUM1}</td>
                                            <td>${list.ROTO_NUM2}</td>
                                            <td>${list.ROTO_NUM3}</td>
                                            <td>${list.ROTO_NUM4}</td>
                                            <td>${list.ROTO_NUM5}</td>
                                            <td>${list.ROTO_NUMB}</td>
                                        </tr>
                                    </c:forEach>
                                    </tbody>
                                </table>
                            </div>
                            <!-- /.table-responsive -->
        </div>
        <!-- /.card-body -->
        <div class="card-footer">
          Footer
        </div>
        <!-- /.card-footer-->
      </div>
      <!-- /.card -->
 
    </section>
    <!-- /.content -->
  </div>
  <!-- /.content-wrapper -->
 
  
</div>
<!-- ./wrapper -->
<%@include file="../include/footer.jsp" %>
<!-- jQuery -->
<%@include file="../include/script.jsp" %>
</body>
</html>
 

다음과 같이 출력되면 성공!!!

저도 테스트를 하면서 진행다보니 설명을 제대로 정리하지 못했네요 ㅠㅠ 

스프링 카테고리에 정리해둔게 있으니 참고 바랍니다.