이안의 평일코딩

Spring 8일차 - MyBatis 연동 본문

Back-end/Spring

Spring 8일차 - MyBatis 연동

이안92 2020. 11. 3. 09:57
반응형

2020.11.03(화)

OnLineSpringMVCStudy3

src/main/webapp/

web.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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee https://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
   <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <!-- 
            XML파일이 여러개를 사용할 때 
         -->
         <init-param>
           <param-name>contextConfigLocation</param-name>
           <param-value>/WEB-INF/config/application-*.xml</param-value>
           <!-- 
                         사용자 정의 클래스 등록 : application-context.xml
                                데이터베이스 등록  : application-datasource.xml
                               보안
                               웹 소켓
                                몽고디비 
                                
               <param-value>
                  /WEB-INF/config/application1.xml,
                  /WEB-INF/config/application2.xml,
                  /WEB-INF/config/application3.xml
               </param-value>
            -->
         </init-param>
    </servlet>
    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>*.do</url-pattern>
    </servlet-mapping>
    <!-- 한글 변환 ( request를 사용하지 않는다  )-->
      <filter>
            <filter-name>encodingFilter</filter-name>
            <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
            <init-param>
                <param-name>encoding</param-name>
                <param-value>UTF-8</param-value>
            </init-param>
            <init-param>
                <param-name>forceEncoding</param-name>
                <param-value>true</param-value>
            </init-param>
        </filter>
        
        <!-- /의 형식으로 시작하는 url에 대하여 UTF-8로 인코딩 -->
        <filter-mapping>
            <filter-name>encodingFilter</filter-name>
            <url-pattern>/*</url-pattern>
        </filter-mapping>
</web-app>
 cs

 

dispatcher-servlet.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?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:p="http://www.springframework.org/schema/p"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
    <context:component-scan base-package="com.sist.*"/>
    <bean id="viewResolver"
        class="org.springframework.web.servlet.view.InternalResourceViewResolver"
        p:prefix="/main/"
        p:suffix=".jsp"
    />
</beans>
 
 cs

 

src/main/webapp/config

application-context.xml (beans, c, context, p)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?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:c="http://www.springframework.org/schema/c"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:p="http://www.springframework.org/schema/p"
    xsi:schemaLocation="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">
    <!-- 
        사용자가 만든 클래스 등록 = 패키지 단위로 등록
     -->
    <context:component-scan base-package="com.sist.*"/>
</beans>
 
 cs

 

application-web.xml (beans, c, p)

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
<?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:c="http://www.springframework.org/schema/c"
    xmlns:p="http://www.springframework.org/schema/p"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    <!-- 
        1. JSP 찾기
            ViewResolver
        2. 파일 업로드
            MultipartResolver
        3. Tiles
            TilesView => include (템플릿 제작)
     -->
     <!-- ViewResolver : JSP찾기 request를 전송
         경로명 지정 p:prefix="/"
         확장자 지정 p:suffix=".jsp"
         
         Model => return
             return "main/main" => /main/main.jsp
             
             prefix+return값+suffix
      -->
    <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"
        p:prefix="/"
        p:suffix=".jsp"
    />
</beans>
 
 cs

 

db.properties (config폴더에 New-File로 생성)

maxActive, maxIdle 기본은 8, 8 / maxWait=-1 반환될 때 까지 기다려라

1
2
3
4
5
6
7
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@211.238.142.000:1521:XE
username=hr
password=happy
maxActive=20
maxIdle=10
maxWait=-1
 cs

 

application-datasource.xml (aop, beans, c, context, p, tx, util)

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
<?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:p="http://www.springframework.org/schema/p"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:util="http://www.springframework.org/schema/util"
    xmlns:c="http://www.springframework.org/schema/c"
    xsi:schemaLocation="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
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.3.xsd">
    <!-- DataBase 관련 : JDBC, ORM(MyBatis, Hibernate), Spring-Data(MongoDB:NoSQL) -->
    <!-- MyBatis연결 -->
    <!-- properties 파일 읽기 -->
    <util:properties id="db" location="/WEB-INF/config/db.properties"/>
    <!-- 데이터베이스 연결 정보를 모아서  -->
    <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource"
        p:driverClassName="#{db['driver']}"
        p:url="#{db['url']}"
        p:username="#{db['username']}"
        p:password="#{db['password']}"
        p:maxActive="#{db['maxActive']}"
        p:maxIdle="#{db['maxIdle']}"
        p:maxWait="#{db['maxWait']}"
    />
    <!-- 마이바티스로 전송 -->
    <bean id="ssf" class="org.mybatis.spring.SqlSessionFactoryBean"
        p:dataSource-ref="ds"
        p:configLocation="/WEB-INF/config/Config.xml"
    />
</beans>
 
 cs

 

Config.xml (MyBatis와 관련되는 파일 other-XML File로 생성)

1
2
3
4
5
6
7
8
9
10
11
12
<?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>
        <typeAlias type="com.sist.dao.RecipeVO" alias="RecipeVO"/>
    </typeAliases>
    <mappers>
        <mapper resource="com/sist/dao/recipe-mapper.xml"/>
    </mappers>
</configuration>
 cs

 

src/main/java

com.sist.dao

RecipeVO.java

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
package com.sist.dao;
 
public class RecipeVO {
    private int no;
    private String title;
    private String poster;
    private String chef;
    private String link;
    public int getNo() {
        return no;
    }
    public void setNo(int no) {
        this.no = no;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getPoster() {
        return poster;
    }
    public void setPoster(String poster) {
        this.poster = poster;
    }
    public String getChef() {
        return chef;
    }
    public void setChef(String chef) {
        this.chef = chef;
    }
    public String getLink() {
        return link;
    }
    public void setLink(String link) {
        this.link = link;
    }
    
}
 
 cs

 

recipe-mapper.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
<?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.sist.dao.recipe-mapper">
  <select id="recipeListData" resultType="RecipeVO" parameterType="hashmap">
    SELECT no,title,chef,poster,num 
    FROM (SELECT no,title,chef,poster,rownum as num 
    FROM (SELECT no,title,chef,poster 
    FROM recipe))
    WHERE num BETWEEN #{start} AND #{end}
  </select>
  <!-- 총페이지  -->
  <select id="recipeTotalPage" resultType="int">
    SELECT CEIL(COUNT(*)/20.0) FROM recipe
  </select>
  <!-- chef 데이터 -->
  <select id="recipeChefData" resultType="RecipeVO" parameterType="hashmap">
    SELECT no,title,chef,poster,num 
    FROM (SELECT no,title,chef,poster,rownum as num 
    FROM (SELECT no,title,chef,poster 
    FROM recipe WHERE chef=#{chef}))
    WHERE num BETWEEN #{start} AND #{end}
  </select>
  <select id="recipeChefTotalPage" resultType="int" parameterType="string">
    SELECT CEIL(COUNT(*)/20.0) FROM recipe
    WHERE chef=#{chef}
  </select>
</mapper>
 
 cs

 

RecipeDAO.java

extends SqlSessionDaoSupport 한 후에 Source-Override/Implement Methods

 
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
package com.sist.dao;
 
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.*;
// 스프링에 메모리 할당 요청 
@Repository
public class RecipeDAO extends SqlSessionDaoSupport{
    @Autowired
    public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
        // TODO Auto-generated method stub
        super.setSqlSessionFactory(sqlSessionFactory);
    }
    /*
     *   <select id="recipeListData" resultType="RecipeVO" parameterType="hashmap">
            SELECT no,title,chef,poster,num 
            FROM (SELECT no,title,chef,poster,rownum as num 
            FROM (SELECT no,title,chef,poster 
            FROM recipe))
            WHERE num BETWEEN #{start} AND #{end}
          </select>
     */
    public List<RecipeVO> recipeListData(Map map)
    {
        /*
         *   getSqlSession() => 1. session=ssf.openSession()
         *                      2. session.close()
         */
        return  getSqlSession().selectList("recipeListData",map);
    }
    /*
     *  <!-- 총페이지  -->
          <select id="recipeTotalPage" resultType="int">
            SELECT CEIL(COUNT(*)/20.0) FROM recipe
          </select>
     */
    public int recipeTotalPage()
    {
        return getSqlSession().selectOne("recipeTotalPage");
    }
    
    /*
     *  <select id="recipeChefData" resultType="RecipeVO" parameterType="hashmap">
            SELECT no,title,chef,poster,num 
            FROM (SELECT no,title,chef,poster,rownum as num 
            FROM (SELECT no,title,chef,poster 
            FROM recipe WHERE chef=#{chef}))
            WHERE num BETWEEN #{start} AND #{end}
          </select>
   */
    public List<RecipeVO> recipeChefData(Map map)
    {
        return getSqlSession().selectList("recipeChefData",map);
    }
    /*
          <select id="recipeChefTotalPage" resultType="int" parameterType="string">
            SELECT CEIL(COUNT(*)/20.0) FROM recipe
            WHERE chef=#{chef}
          </select>
     */
    public int recipeChefTotalPage(String chef)
    {
        return getSqlSession().selectOne("recipeChefTotalPage", chef);
    }
}
 
 cs

<Spring 담당>

1.데이터베이스 설정 (BasicDataSource)

2.설정된 데이터를 받아서 SqlSessionFactory : getConnection, disConnection()

3.RecipeDAO

 

src/main/java

com.sist.web

RecipeController.java

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
package com.sist.web;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
 
import com.sist.dao.RecipeDAO;
import com.sist.dao.RecipeVO;
 
/*
 *      MVC 
 *      ====
 *        음식점 
 *        =====
 *               DispatcherServlet
 *               ========== 배달부 
 *          손님 ==== 서빙  ========> 주방 (Model)
 *              주문              주문서 
 *          손님<====     <======== 주방
 *                        음식 (request,session)
 *          xml,annotation => 메뉴판 
 */
import java.util.*;
@Controller
public class RecipeController {
    @Autowired
    private RecipeDAO dao;
    
    // 사용자가 요청이 있을 때 처리 
    // 웹 => 사용자 요청 (URL주소)  ==> 서버주소(X) ==> URI  ==> .do
    @RequestMapping("recipe/list.do")
    // 사용자 요청 처리 
    public String recipe_list(String page,Model model)
    {
        if(page==null)
            page="1";
        int curpage=Integer.parseInt(page);
        // 데이터 받기 => 스프링에서 메모리 할당=>한개만 저장 (재사용) = 싱글턴
        int rowSize=20;
        int start=(rowSize*curpage)-(rowSize-1);
        int end=rowSize*curpage;
        // Map에서 채워서 => DAO로 전송 
        Map map=new HashMap();
        map.put("start", start);
        map.put("end", end);
        List<RecipeVO> list=dao.recipeListData(map);
        for(RecipeVO vo:list)
        {
            String title=vo.getTitle();
            if(title.length()>22)
            {
                title=title.substring(0,22);
                title+="...";
            }
            vo.setTitle(title);
        }
        // 총페이지 
        int totalpage=dao.recipeTotalPage();
        
        // 전송 
        model.addAttribute("curpage", curpage);
        model.addAttribute("totalpage", totalpage);
        model.addAttribute("list", list);
        return "recipe/list";// forward => list.jsp=>request를 전송 
    }
    /*
     *    @RequestMapping() => 통합 (GET,POST)
     *    @GetMapping => Get방식으로 전송 
     *                   <a> ,sendRedirect() , location.href
     *    @PostMapping => ajax , form
     */
    @GetMapping("recipe/chef.do")
    public String recipe_chef(String page,String chef,Model model)
    {
        // DB연동
        if(page==null)
            page="1";
        int curpage=Integer.parseInt(page);
        int rowSize=20;
        int start=(rowSize*curpage)-(rowSize-1);
        int end=rowSize*curpage;
        Map map=new HashMap();
        map.put("start", start);
        map.put("end", end);
        map.put("chef", chef);
        List<RecipeVO> list=dao.recipeChefData(map);
        for(RecipeVO vo:list)
        {
            String title=vo.getTitle();
            if(title.length()>22)
            {
                title=title.substring(0,22);
                title+="...";
            }
            vo.setTitle(title);
        }
        // 총페이지 
        int totalpage=dao.recipeChefTotalPage(chef);
        
        // 전송 
        model.addAttribute("curpage", curpage);
        model.addAttribute("totalpage", totalpage);
        model.addAttribute("chef", chef);
        model.addAttribute("list", list);
        return "recipe/chef";
    }
    
}
 cs

 

 

오류(ㅠㅠ)

### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'oracle.jdbc.driver.OracleDriver'
### The error may exist in com/sist/dao/recipe-mapper.xml
### The error may involve com.sist.dao.recipe-mapper.recipeListData
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'oracle.jdbc.driver.OracleDriver']을(를) 발생시켰습니다.
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

 

JQuery dialog코드

jqueryui.com/dialog/

 

src/main/webapp/recipe

list.jsp

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
<%@ 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 PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
  <div class="container">
    <div class="row">
      <h3 class="text-center">레시피 목록</h3>
      <h5 class="text-right">
        ${curpage } page / ${totalpage } pages
      </h5>
      <c:forEach var="vo" items="${list }">
        <div class="col-md-3">
          <div class="thumbnail">
            <a href="#">
              <img src="${vo.poster }" alt="Lights" style="width:100%">
            </a>
              <div class="caption">
                <p style="font-size: 8pt">${vo.title }</p>
                <p style="font-size: 8pt">
                 <a href="chef.do?chef=${vo.chef }">${vo.chef }</a>
                </p>
              </div>
            
          </div>
        </div>
      </c:forEach>
    </div>
    <div class="row">
       <ul class="pager">
          <li class="previous"><a href="list.do?page=${curpage>1?curpage-1:curpage }">이전</a></li>
          <li class="next"><a href="list.do?page=${curpage<totalpage?curpage+1:curpage }">다음</a></li>
        </ul>
    </div>
  </div>
</body>
</html>
 cs

 

chef.jsp

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
<%@ 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 PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
 <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
 <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
 <script>
  $( function(){
      
    $('#info').css("cursor","pointer");
    $('#info').click(function(){
        $( "#dialog" ).dialog();
    })
    
  });
  </script>
</head>
<body>
  <div class="container">
    <div style="height: 30px"></div>
    <h3 class="text-center" id="info">${chef }님의 레시피</h3>
    
    <div class="row">
      <div class="text-center">
        <input type=text id="fd" size=20 class="input-sm">
        <input type=button id="findBtn" class="btn btn-sm btn-primary" value="검색">
      </div>
    </div>
    <div style="height: 30px"></div>
    <div class="row">
     <c:forEach var="vo" items="${list }">
        <div class="col-md-3">
          <div class="panel panel-danger">
              <div class="panel-heading" style="font-size: 8pt">${vo.title }</div>
              <div class="panel-body">
                <div class="thumbnail">
                  <img src="${vo.poster }" width="100%">
                </div>
              </div>
           </div>
        </div>
      </c:forEach>
    </div>
    <div class="row">
      <ul class="pager">
          <li class="previous"><a href="chef.do?chef=${chef }&page=${curpage>1?curpage-1:curpage }">이전</a></li>
          <li class="next"><a href="chef.do?chef=${chef }&page=${curpage<totalpage?curpage+1:curpage }">다음</a></li>
        </ul>
    </div>
    <div id="dialog" title="${vo.chef }님의 정보" style="display:none">
     <p>This is the default dialog which is useful for displaying information. The dialog window can be moved, resized and closed with the 'x' icon.</p>
    </div>
  </div>
</body>
</html>
 cs

 

반응형

'Back-end > Spring' 카테고리의 다른 글

Spring 11일차 - React  (0) 2020.11.11
Spring 10일차 - 스프링 MVC  (2) 2020.11.09
Spring 7일차 - XML MVC구조  (0) 2020.11.02
Spring 6일차 - ComponentScan  (0) 2020.10.31
Spring 5일차 - Repository  (0) 2020.10.31
'Back-end/Spring' Related Articles more
Comments