1. Spring 3.0+JDBC(Web 환경) 


1) SpirngWeb 프로젝트 생성


Web에서 DB를 연결하는 방법은 (JDBC - Spring JDBC - Mybatis) 이렇게 3가지가 있다.


-Spring3.0을 웹으로 JDBC와 연결해보겠다.


-기본세팅파일

SpringWeb_기본세팅.zip


-폴더 구조


2) BoardController.java 작성 - created, list(글 작성, 글목록 기능)


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
package com.jdbc.springweb;
 
 
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.List;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
 
import com.jdbc.dao.BoardDAO;
import com.jdbc.dto.BoardDTO;
import com.jdbc.util.MyUtil;
 
@Controller
public class BoardController {
    
    @Autowired
    @Qualifier("boardDAO")
    BoardDAO dao;
    
    @Autowired
    MyUtil myUtil;
    
    @RequestMapping(value = "/created.action")
    public ModelAndView created() {
        ModelAndView mav=new ModelAndView();
        mav.setViewName("bbs/created");
        
        return mav;
    }
    
    @RequestMapping(value="/created_ok.action",method = {RequestMethod.GET,RequestMethod.POST})
    public String created_ok(BoardDTO dto, HttpServletRequest request, HttpServletResponse response) throws Exception{
        
        int maxNum=dao.getMaxNum();
        
        dto.setNum(maxNum+1);
        dto.setIpAddr(request.getRemoteAddr());
        
        dao.insertData(dto);
        
        return "redirect:/list.action";
    }
    
    @RequestMapping(value="/list.action",method = {RequestMethod.GET,RequestMethod.POST})
    public String list(HttpServletRequest request, HttpServletResponse response) throws Exception{
        
        String cp = request.getContextPath();
        
        String pageNum = request.getParameter("pageNum");
        int currentPage = 1;
        
        if(pageNum != null)
            currentPage = Integer.parseInt(pageNum);
        
        String searchKey = request.getParameter("searchKey");
        String searchValue = request.getParameter("searchValue");
        
        if(searchKey == null){
            
            searchKey = "subject";
            searchValue = "";
            
        }else{
            
            if(request.getMethod().equalsIgnoreCase("GET"))
                searchValue =
                    URLDecoder.decode(searchValue, "UTF-8");
            
        }
        
        //전체데이터갯수
        int dataCount = dao.getDataCount(searchKey, searchValue);
        
        //전체페이지수
        int numPerPage = 10;
        int totalPage = myUtil.getPageCount(numPerPage, dataCount);
        
        if(currentPage > totalPage)
            currentPage = totalPage;
        
        int start = (currentPage-1)*numPerPage+1;
        int end = currentPage*numPerPage;
        
        List<BoardDTO> lists =
            dao.getList(start, end, searchKey, searchValue);
        
        //페이징 처리
        String param = "";
        if(!searchValue.equals("")){
            param = "searchKey=" + searchKey;
            param+= "&searchValue=" 
                + URLEncoder.encode(searchValue, "UTF-8");
        }
        
        String listUrl = cp + "/list.action";
        if(!param.equals("")){
            listUrl = listUrl + "?" + param;                
        }
        
        String pageIndexList =
            myUtil.pageIndexList(currentPage, totalPage, listUrl);
        
        //글보기 주소 정리
        String articleUrl = 
            cp + "/article.action?pageNum=" + currentPage;
            
        if(!param.equals(""))
            articleUrl = articleUrl + "&" + param;
        
        //포워딩 될 페이지에 데이터를 넘긴다
        request.setAttribute("lists", lists);
        request.setAttribute("pageIndexList",pageIndexList);
        request.setAttribute("dataCount",dataCount);
        request.setAttribute("articleUrl",articleUrl);
        
        return "bbs/list";
    }
}
cs

created, list 기능만 추가한 코드이다.


-실행화면


3) article 추가(게시글 보기 기능)

컨트롤러에 article 추가한다.


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
    @RequestMapping(value="/article.action",method = {RequestMethod.GET,RequestMethod.POST})
    public String article(HttpServletRequest request, HttpServletResponse response) throws Exception{
        
        String cp = request.getContextPath();
        
        int num = Integer.parseInt(request.getParameter("num"));
        String pageNum = request.getParameter("pageNum");
        
        String searchKey = request.getParameter("searchKey");
        String searchValue = request.getParameter("searchValue");
        
        if(searchKey != null)
            searchValue = URLDecoder.decode(searchValue, "UTF-8");
        
        //조회수 증가
        dao.updateHitCount(num);
        
        BoardDTO dto = dao.getReadData(num);
        
        if(dto==null){
            return "redirect:/list.action";
        }
        
        int lineSu = dto.getContent().split("\n").length;
        
        dto.setContent(dto.getContent().replaceAll("\n", "<br/>"));
        
        String param = "pageNum=" + pageNum;
        if(searchKey!=null){
            param += "&searchKey=" + searchKey;
            param += "&searchValue=" 
                + URLEncoder.encode(searchValue, "UTF-8");
        }
        
        request.setAttribute("dto", dto);
        request.setAttribute("params",param);
        request.setAttribute("lineSu",lineSu);
        request.setAttribute("pageNum",pageNum);        
        
        return "bbs/article";    
    }
cs


**여기서 return값을 ModelAndView로 바꿔줄경우!

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
    @RequestMapping(value="/article.action",method = {RequestMethod.GET,RequestMethod.POST})
    public ModelAndView article(HttpServletRequest request, HttpServletResponse response) throws Exception{
        
        String cp = request.getContextPath();
        
        int num = Integer.parseInt(request.getParameter("num"));
        String pageNum = request.getParameter("pageNum");
        
        String searchKey = request.getParameter("searchKey");
        String searchValue = request.getParameter("searchValue");
        
        if(searchKey != null)
            searchValue = URLDecoder.decode(searchValue, "UTF-8");
        
        //조회수 증가
        dao.updateHitCount(num);
        
        BoardDTO dto = dao.getReadData(num);
        
        if(dto==null){
            //return "redirect:/list.action";
        }
        
        int lineSu = dto.getContent().split("\n").length;
        
        dto.setContent(dto.getContent().replaceAll("\n", "<br/>"));
        
        String param = "pageNum=" + pageNum;
        if(searchKey!=null){
            param += "&searchKey=" + searchKey;
            param += "&searchValue=" 
                + URLEncoder.encode(searchValue, "UTF-8");
        }
        /*
        //model
        request.setAttribute("dto", dto);
        request.setAttribute("params",param);
        request.setAttribute("lineSu",lineSu);
        request.setAttribute("pageNum",pageNum);        
        
        //view
        //return "bbs/article";    
        */
        
        ModelAndView mav=new ModelAndView();
        mav.setViewName("bbs/article");
        mav.addObject("dto",dto);
        mav.addObject("params",param);
        mav.addObject("lineSu",lineSu);
        mav.addObject("pageNum",pageNum);
        
        return mav;
    }
cs

반환값의 자료형을 ModelAndView로 바꾸고 아래쪽에 request.setAttribute~ 주석친 부분을 그  아래 코드처럼 써주면 된다.


4) update 추가(수정 기능)


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
    @RequestMapping(value="/updated.action",method = {RequestMethod.GET,RequestMethod.POST})
    public String updated(HttpServletRequest request, HttpServletResponse response) throws Exception{
 
        String cp=request.getContextPath();
        
        int num = Integer.parseInt(request.getParameter("num"));
        String pageNum = request.getParameter("pageNum");
        
        BoardDTO dto = dao.getReadData(num);
        
        if(dto == null){
            return "redirect:/list.action";
        }
        
        request.setAttribute("dto", dto);
        request.setAttribute("pageNum", pageNum);
        
        return "bbs/updated";    
    }
    
    @RequestMapping(value="/updated_ok.action",method = {RequestMethod.GET,RequestMethod.POST})
    public String updated_ok(BoardDTO dto,HttpServletRequest request, HttpServletResponse response) throws Exception{
    
        String pageNum = request.getParameter("pageNum");
        
        dao.updateData(dto);
        
        return "redirect:/list.action?pageNum="+pageNum;
    }
cs


5)delete 추가


1
2
3
4
5
6
7
8
9
10
    @RequestMapping(value="/deleted.action",method = {RequestMethod.GET,RequestMethod.POST})
    public String deleted(BoardDTO dto,HttpServletRequest request, HttpServletResponse response) throws Exception{
    
        String pageNum = request.getParameter("pageNum");
        int num =Integer.parseInt(request.getParameter("num"));
        
        dao.deleteData(num);
        
        return "redirect:/list.action?pageNum="+pageNum;
    }
cs


6) 전체 실행화면



2. Spring 3.0+Spring JDBC(Web 환경) 


1) Spring JDBC lib 다운

링크 : https://mvnrepository.com/

Spring JDBC 다운받아서 pom.xml에 붙여넣는다.


-pom.xml

1
2
3
4
5
6
        <!-- spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
cs


2) DAO 파일 생성 및 작성

먼저 xml파일에 객체 생성해주고 메소드를 통한 의존성 주입을 위한 세팅을 먼저 해주자.


-servlet-context.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
    <beans:bean id="boardDAO2" class="com.jdbc.dao.BoardDAO2">
        <beans:property name="jdbcTemplate" ref="jdbcTemplate"/>
    </beans:bean>
    
    <beans:bean id="jdbcTemplate"
    class="org.springframework.jdbc.core.JdbcTemplate">
        <beans:constructor-arg ref="dataSource"/>
    </beans:bean>
    
    <beans:bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
        <beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        <beans:property name="url" value="jdbc:oracle:thin:@192.168.16.12:1521:TestDB"/>
        <beans:property name="username" value="suzi"/>
        <beans:property name="password" value="a123"/>
    </beans:bean>
cs

boardDAO2를 만들고 의존성 주입을 위한 셋팅.


-BoardDAO2.java

1
2
3
4
5
6
    //DI(의존성 주입)
    private JdbcTemplate jdbcTemplate;
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) throws Exception{
        this.jdbcTemplate = jdbcTemplate;
    }
cs

메소드로 의존성주입을 하였다.


-아래는 BoardDAO2의 DB를 다루는 코드 전문.

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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
package com.jdbc.dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import javax.sql.DataSource;
 
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
 
import com.jdbc.dto.BoardDTO;
 
public class BoardDAO2 {
    
    private JdbcTemplate jdbcTemplate;
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) throws Exception{
        this.jdbcTemplate = jdbcTemplate;
    }
    
    public int getMaxNum(){
        
        int maxNum = 0;
        
        StringBuilder sql=new StringBuilder(100);//100자 저장하겠다.
        
        sql.append("select nvl(max(num),0) from board");
        maxNum=jdbcTemplate.queryForInt(sql.toString());
 
        return maxNum;
    }
    
    public void insertData(BoardDTO dto){
        
        StringBuilder sql=new StringBuilder(200);
            
        sql.append("insert into board (num,name,pwd,email,subject,content,");
        sql.append("ipAddr,hitCount,created) ") ;
        sql.append( "values(?,?,?,?,?,?,?,0,sysdate)");
        
        jdbcTemplate.update(sql.toString(),dto.getNum(),dto.getName(),dto.getPwd()
                ,dto.getEmail(),dto.getSubject(),dto.getContent(),dto.getIpAddr());
    
    }
    
    public List<BoardDTO> getList(int start, int end,
            String searchKey, String searchValue){
 
        StringBuilder sql=new StringBuilder(500);
            
        searchValue = "%" + searchValue + "%";
        
        sql.append("select * from (")
        .append("select rownum rnum,data.* from(")
        .append("select num,name,subject,hitCount,")
        .append("to_char(created,'YYYY-MM-DD') created ")
        .append("from board where " + searchKey + " like ? order by num desc) data) ")
        .append("where rnum >= ? and rnum <= ?");
        
        List<BoardDTO> lists=jdbcTemplate.query(sql.toString(), 
                new Object[] {searchValue,start,end},
                new RowMapper<BoardDTO>() {
 
                    @Override
                    public BoardDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                        
                        BoardDTO dto=new BoardDTO();
                        
                        dto.setNum(rs.getInt("num"));
                        dto.setName(rs.getString("name"));
                        dto.setSubject(rs.getString("subject"));
                        dto.setHitCount(rs.getInt("hitCount"));
                        dto.setCreated(rs.getString("created"));
                        
                        return dto;
                    }
            
        });
        
        return lists;
        
    }
    
    public int getDataCount(String searchKey,String searchValue){
        
        int result = 0;
        
        StringBuilder sql=new StringBuilder(200);
            
        searchValue = "%" + searchValue + "%";
        
        sql.append("select nvl(count(*),0) from board ")
        .append("where " + searchKey + " like ?");
            
        result=jdbcTemplate.queryForInt(sql.toString(),searchValue);
 
        return result;
    }
    
 
    public void updateHitCount(int num){
 
        StringBuilder sql=new StringBuilder(500);
        sql.append("update board set hitCount=hitCount + 1 where num=?");
            
        jdbcTemplate.update(sql.toString(),num);
    }
    
    public BoardDTO getReadData(int num){
        
        StringBuilder sql=new StringBuilder(100);
            
        sql.append("select num,name,pwd,email,subject,content,ipAddr,")
        .append("hitCount,created from board where num=?");
 
        BoardDTO dtoOne=jdbcTemplate.queryForObject(sql.toString(),
                new RowMapper<BoardDTO>(){
 
                    @Override
                    public BoardDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                        
                        BoardDTO dto = new BoardDTO();
                        
                        dto.setNum(rs.getInt("num"));
                        dto.setName(rs.getString("name"));
                        dto.setPwd(rs.getString("pwd"));
                        dto.setEmail(rs.getString("email"));
                        dto.setSubject(rs.getString("subject"));
                        dto.setContent(rs.getString("content"));
                        dto.setIpAddr(rs.getString("ipAddr"));
                        dto.setHitCount(rs.getInt("hitCount"));
                        dto.setCreated(rs.getString("created"));
                        
                        return dto;
                    }
            
            },num);
 
        return dtoOne;
    }
    
    public void deleteData(int num){
        
        StringBuilder sql=new StringBuilder(100);
        
        sql.append("delete board where num=?");
        
        jdbcTemplate.update(sql.toString(),num);
        
    }
 
    public void updateData(BoardDTO dto){
        
        StringBuilder sql=new StringBuilder(100);
        
        sql.append("update board set name=?, pwd=?, email=?, subject=?,")
        .append("content=? where num=?");
        
        jdbcTemplate.update(sql.toString(),dto.getName(),dto.getPwd(),
                dto.getEmail(),dto.getSubject(),dto.getContent(),
                dto.getNum());        
    }
}
cs


-그리고 아까의 BoardController.java에서 dao 연결부분을 바꿔준다.

1
2
3
4
5
6
@Controller
public class BoardController {
    
    @Autowired
    @Qualifier("boardDAO2")
    BoardDAO2 dao;
cs

BoardDAO에서 BoardDAO2를 연결


-그리고 실행하면 게시판이 잘 작동한다.



3. Spring 3.0+Mybatis(Web 환경) 


1) 프로젝트 복사해서 생성(SpringWebMybatis)

SpringWeb을 복사해서 SpringWebMybatis로 생성


-폴더기본셋팅

SpringWebMybatis.zip


2) mvnrepository에서 mybatis,mybatis spring 다운


링크 : https://mvnrepository.com/

-pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.2</version>
        </dependency>
        
        <!-- mybatis-spring -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.1</version>
        </dependency>
cs


3) servlet-context.xml에서 mybatis설정


-servlet-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
39
40
41
42
43
44
45
46
47
48
49
50
51
<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
 
    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
    
    <!-- Enables the Spring MVC @Controller programming model -->
    <annotation-driven />
 
    <!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
    <resources mapping="/resources/**" location="/resources/" />
 
    <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
    <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <beans:property name="prefix" value="/WEB-INF/views/" />
        <beans:property name="suffix" value=".jsp" />
    </beans:bean>
    
    <context:component-scan base-package="com.jdbc.springweb" />
    
    <beans:bean id="boardDAO2" class="com.jdbc.dao.BoardDAO2">
        <beans:property name="sessionTemplate" ref="sessionTemplate"/>
    </beans:bean>
    
    <beans:bean id="sessionTemplate"
    class="org.mybatis.spring.SqlSessionTemplate">
        <beans:constructor-arg ref="sessionFactory"/>    
    </beans:bean>
    
    <beans:bean id="sessionFactory" 
    class="org.mybatis.spring.SqlSessionFactoryBean">
        <beans:property name="dataSource" ref="dataSource"></beans:property>
    </beans:bean>
    
    <beans:bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
        <beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        <beans:property name="url" value="jdbc:oracle:thin:@192.168.16.12:1521:TestDB"/>
        <beans:property name="username" value="suzi"/>
        <beans:property name="password" value="a123"/>
    </beans:bean>
    
    <beans:bean id="myUtil" class="com.jdbc.util.MyUtil"/>
    
</beans:beans>
cs


4) mybats-config.xml 파일 생성 및 작성


src/main/java에 mybatis-config.xml 파일 생성

1
2
3
4
<!-- spring하고 연결할때는 얘만 필요 -->
<mappers>
    <mapper resource="com/exe/mybatis/boardMapper.xml"/>
</mappers>
cs



그리고 servlet-context.xml에 sessionFactory 객체 생성부분에 property추가 해준다.

1
2
3
4
5
6
    <beans:bean id="sessionFactory" 
    class="org.mybatis.spring.SqlSessionFactoryBean">
        <beans:property name="dataSource" ref="dataSource"></beans:property>
        <beans:property name="configLocation"
        value="classpath:/mybatis-config.xml"/>
    </beans:bean>
cs

configLocation으로 sql문을 써놓은 xml 파일의 경로를 알려준다.


5) boardMapper.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"?>
 
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 
<mapper namespace="boardMapper">
 
<select id="maxNum" resultType="int">
    select nvl(max(num),0) from board
</select>
 
<insert id="insertData" parameterType="com.jdbc.dto.BoardDTO">
    insert into board(num,name,pwd,email,subject,content,ipAddr,hitCount,created) 
    values(#{num},#{name},#{pwd},#{email},#{subject},#{content},#{ipAddr},0,sysdate)
</insert>
 
<select id="getDataCount" parameterType="hashMap" resultType="int">
    select nvl(count(*),0) from board
    where ${searchKey} like '%' || #{searchValue} || '%'
</select>
 
<select id="getLists" parameterType="hashMap" resultType="com.jdbc.dto.BoardDTO">
    select * from(
    select rownum rnum, data.* from(
    select num,name,subject,hitCount,to_char(created,'YYYY-MM-DD') created
    from board where ${searchKey} like '%' || #{searchValue} || '%'
    order by num desc) data)
<![CDATA[
    where rnum>=#{start} and rnum<=#{end}
]]>
</select>
 
<select id="getReadData" parameterType="int" resultType="com.jdbc.dto.BoardDTO">
    select num,name,pwd,email,subject,content,ipAddr,hitCount,created
    from board where num=#{num}
</select>
 
<update id="updateHitCount" parameterType="int">
    update board set hitCount=hitCount+1 where num=#{num}
</update>
 
<update id="updateData" parameterType="com.jdbc.dto.BoardDTO">
    update board set name=#{name},pwd=#{pwd},email=#{email},subject=#{subject},
    content=#{content} where num=#{num}
</update>
 
<delete id="delteData" parameterType="int">
    delete board where num=#{num}
</delete>
 
</mapper>
cs


6) DAO 파일 mybatis에 맞게 바꿔준다. 


-BoardDAO2.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
package com.jdbc.dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
 
import javax.sql.DataSource;
 
import org.mybatis.spring.SqlSessionTemplate;
 
import com.jdbc.dto.BoardDTO;
 
public class BoardDAO2 {
    
    private SqlSessionTemplate sessionTemplate;
    
    public void setSessionTemplate(SqlSessionTemplate sessionTemplate) throws Exception{
        this.sessionTemplate = sessionTemplate;
    }
    
    Connection conn=null;
    
    public int getMaxNum(){
        
        int maxNum = 0;
        
        maxNum=sessionTemplate.selectOne("boardMapper.maxNum");
        
        return maxNum;
    }
    
    public void insertData(BoardDTO dto){
        
        sessionTemplate.insert("boardMapper.insertData",dto);
    }
    
    public List<BoardDTO> getList(int start, int end,
            String searchKey, String searchValue){
        
        HashMap<String, Object> params=new HashMap<String, Object>();
        params.put("start", start);
        params.put("end", end);
        params.put("searchKey",searchKey);
        params.put("searchValue",searchValue);
    
        List<BoardDTO> lists=sessionTemplate.selectList("boardMapper.getLists",params);
        
        return lists;
    }
    
    public int getDataCount(String searchKey,String searchValue){
        
        int result=0;
        
        HashMap<String, Object> params=new HashMap<String, Object>();
 
        params.put("searchKey",searchKey);
        params.put("searchValue",searchValue);
 
        result=sessionTemplate.selectOne("boardMapper.getDataCount",params);
        
        return result;
    }
    
    public void updateHitCount(int num){
        
        sessionTemplate.update("boardMapper.updateHitCount",num);
    }
    
    public BoardDTO getReadData(int num){
        
        BoardDTO dto = sessionTemplate.selectOne("boardMapper.getReadData",num);
        
        return dto;
    }
    
    public void deleteData(int num){
        
        sessionTemplate.delete("boardMapper.deleteData",num);
    }
    
    public void updateData(BoardDTO dto){
 
        sessionTemplate.update("boardMapper.updateData",dto);    
    }
}
cs


- BoardController.java파일은 저번거 그대로.

그러면 Mybatis를 이용한 게시판도 잘 작동한다.


-실행화면



저작자표시 비영리 변경금지 (새창열림)

이전 글의 프로젝트의 DB연결을 Mybatis로 바꿔주자.

전의 프로젝트의 일부를 가져와서 아래처럼 폴더 셋팅을 해주자.


이전 글 링크 : https://welcomto-dd.tistory.com/45



저번 프로젝트(SpringjdbcTemplate)에서 

① CustomDAO.java,

② CustomDTO.java,

③ CustomMain.java,

④ app-context.xml,pom.xml 파일을 가져온다.


1. Mybatis 환경 세팅 


링크 : https://mvnrepository.com/


1) mybatis 검색 후 mybaits, mybatis Spring의 코드를 긁어오자.



-pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
    <dependencies>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.2</version>
        </dependency>
        
        <!-- mybatis-spring -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.2</version>
        </dependency>
    </dependencies>
cs

긁어 온 코드를 pom.xml에 붙여넣고 저장하면 pom.xml을 읽어 라이브러리를 자동으로 받는다.


2) app-context.xml 파일 작성


-app-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
    <!-- mybatis setting -->
 
    <!-- 사용자 -->
    <bean id="customDAO"
    class="com.exe.springmybatis.CustomDAO">
        <property name="sessionTemplate" ref="sessionTemplate"></property>
    </bean>
 
    <!-- 제품 -->
    <bean id="sessionTemplate"
    class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg ref="sessionFactory"/>
    </bean>
    
    <!-- dataSource를 연결해주는 역할을 하는애(sessionFactory) -->
    <bean id="sessionFactory"
    class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <!-- DB 연결 -->
    <bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close"
    p:driverClassName="oracle.jdbc.driver.OracleDriver"
    p:url="jdbc:oracle:thin:@192.168.16.12:1521:TestDB"
    p:username="suzi"
    p:password="a123"
    />
cs

여기서 sessionFactory 부분에 property를 하나 추가해준다.


1
2
3
4
5
6
7
    <!-- dataSource를 연결해주는 역할을 하는애(sessionFactory) -->
    <bean id="sessionFactory"
    class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="configLocation"
        value="classpath:/com/exe/springmybatis/mybatis-config.xml"/>
    </bean>
cs

property name="configLocation" 바로 이부분이다.

sql 경로 설정에 관한 코드이다. 그럼 이 파일을 만들어주러 가자.


3) mybatis-config 파일 생성 및 작성


-mybatis-config.xml 파일 생성


-mybatis-config.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
<?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>
 
<!-- 다른 struts와 연결할 때 사용 -->
<properties>
    <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@192.168.16.12:1521:TestDB"/>
    <property name="username" value="suzi"/>
    <property name="password" value="a123"/>
</properties>
 
<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
            <property name="driver" value="${driver}"/>
            <property name="url" value="${url}"/>
            <property name="username" value="${username}"/>
            <property name="password" value="${password}"/>
        </dataSource>
    </environment>
</environments>
 
<!-- spring하고 연결할때는 얘만 필요 -->
<mappers>
    <mapper resource="com/exe/mapper/customMapper.xml"/>
</mappers>
 
</configuration>
cs

Spring 연결에서는 mappers 부분이 sql문을 담고있는 xml파일과의 연결을 담당한다.

customMapper.xml이라는 sql을 모아놓은 파일을 작성해주러 가자.


4) customMapper.xml 파일 생성 및 작성


-패키지 및 파일 생성


-customMapper.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
<?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.exe.mapper">
 
<insert id="insert" parameterType="com.exe.springmybatis.CustomDTO">
    insert into custom(id,name,age) values(#{id},#{name},#{age})
</insert>
 
<select id="list" resultType="com.exe.springmybatis.CustomDTO">
    select id,name,age from custom
</select>
 
<select id="listOne" parameterType="int" resultType="com.exe.springmybatis.CustomDTO">
    select id,name,age from custom where id=#{id}
</select>
 
<update id="update" parameterType="com.exe.springmybatis.CustomDTO">
    update custom set name=#{name}, age=#{age} where id=#{id}
</update>
 
<delete id="delete" parameterType="int">
    delete from custom where id=#{id}
</delete>
 
</mapper>
cs


5) CustomDAO.java 파일 작성


-CustomDAO.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
package com.exe.springmybatis;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
 
import javax.sql.DataSource;
 
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 
public class CustomDAO {
 
    private SqlSessionTemplate sessionTemplate;
    
    //의존성 주입
    public void setSessionTemplate(SqlSessionTemplate sessionTemplate) {
        this.sessionTemplate=sessionTemplate;
    }
    
    Connection conn=null;
    
    //insert
    public void insertData(CustomDTO dto) {
        sessionTemplate.insert("com.exe.mapper.insert",dto);
    }
    
    //select 모든 데이터
    public List<CustomDTO> getList(){
        
        List<CustomDTO> lists=sessionTemplate.selectList("com.exe.mapper.list");
        
        return lists;
    }
    
    //select 1개의 데이터
    public CustomDTO getReadData(int id){
        
        CustomDTO dto=sessionTemplate.selectOne("com.exe.mapper.listOne",id);
        
        return dto;
    }
        
    //update
    public void updateData(CustomDTO dto) {
        
        sessionTemplate.update("com.exe.mapper.update",dto);
    }
    
    //delete
    public void deleteDate(int id) {
        sessionTemplate.delete("com.exe.mapper.delete",id);
    }
}
cs


6) 마지막으로 Main에서 실행


-CustomMain.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
package com.exe.springmybatis;
 
import java.util.List;
 
import org.springframework.context.support.GenericXmlApplicationContext;
 
public class CustomMain {
 
    public static void main(String[] args) {
        
        GenericXmlApplicationContext context=new GenericXmlApplicationContext("app-context.xml");
        
        CustomDAO dao=(CustomDAO)context.getBean("customDAO");
        
        CustomDTO dto;
        
        /*
        //insert
        dto=new CustomDTO();
        dto.setId(333);
        dto.setName("안녕");
        dto.setAge(25);
        
        dao.insertData(dto);
        
        System.out.println("insert 완료!");
        */
        
        /*
        //Oneselect
        dto=dao.getReadData(111);
        
        if(dto!=null) {
            System.out.printf("%d %s %d\n",dto.getId(),dto.getName(),dto.getAge());
        }
        
        System.out.println("OneSelect 완료!");
        */
 
        //update
        dto = new CustomDTO();
        
        dto.setId(555);
        dto.setName("이지은");
        dto.setAge(27);
        
        dao.updateData(dto);
 
        
        //delete
        //dao.deleteDate(333);
 
        //select
        List<CustomDTO> lists=dao.getList();
        
        for(CustomDTO dto1:lists) {
            System.out.printf("%d %s %d\n",dto1.getId(),dto1.getName(),dto1.getAge());
        }
        
        System.out.println("select 완료!");
    }
}
cs


-실행화면

하나씩 주석 풀어주면서 테스트해보면 잘 실행된다.



저작자표시 비영리 변경금지 (새창열림)

+ Recent posts

티스토리툴바