'스프링 디비' 태그의 글 목록

스프링 디비

7. 스프링 DB 연동하기 2018.06.24 3

7. 스프링 DB 연동하기

왕초보코딩탈출 2018. 6. 24. 18:01

2018. 6. 24. 18:01

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

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

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

1
2
3
4
5
6
    <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>
Colored by Color Scripter
cs

Pom.xml 에 추가해줍니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
        <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>
Colored by Color Scripter
cs

root-context.xml

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?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>
Colored by Color Scripter
cs

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

LottoController.Class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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";
    }
}
 
Colored by Color Scripter
cs

LottoService.Class

1
2
3
4
5
6
7
8
9
10
package com.sjh.rotto;
 
import java.util.List;
 
public interface LottoService {
    
    public List<LottoVO> lottoInfo(LottoVO vo);         //로또 정보 번호 조회
 
}
 
Colored by Color Scripter
cs

LottoServiceImpl.Class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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);
    }
    
    
 
}
 
Colored by Color Scripter
cs

LottoDAO.Class

1
2
3
4
5
6
7
8
9
package com.sjh.rotto;
 
import java.util.List;
 
public interface LottoDAO {
 
    public List<LottoVO> lottoInfo(LottoVO vo);
}
 
Colored by Color Scripter
cs

LottoDAOImpl.Class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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);
    }
 
    
}
 
Colored by Color Scripter
cs

LottoVO.Class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
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;
    }
 
    
    
}
Colored by Color Scripter
cs

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

lotto.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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>
Colored by Color Scripter
cs

log4jdbc.log4j2.properties

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

logback.xml

1
2
3
4
5
6
7
8
9
10
11
12
<?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>
Colored by Color Scripter
cs

mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<?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>
Colored by Color Scripter
cs

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
<%@ 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>
 
Colored by Color Scripter
cs

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

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

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

저작자표시 비영리

'Spring+mariaDB+mybatis 프로젝트' 카테고리의 다른 글

6. 데이터 베이스 준비하기 (0)	2018.06.23
5. 스프링 디자인 작업하기 (0)	2018.06.22
4. 스프링 톰캣서버 설치 및 첫 화면 띄우기 (0)	2018.06.21
3. 스프링 프로젝트 생성 및 버전 변경 (0)	2018.06.20
2. 스토리보드 작성하기 (0)	2018.06.20

PREV 이전 1 NEXT 다음

왕초보코딩탈출