mybatis사용하기

mybatis사용하기

JdbcTemplate를 사용하지 않고 mybatis를 사용하여 데이터 처리하는 방법입니다.

==================================================

pom.xml에 Mybatis dependency추가

==================================================

<!-- Mybatis -->

<dependency>

<groupId>org.mybatis</groupId>

<artifactId>mybatis-spring</artifactId>

<version>1.1.1</version>

</dependency>

===============================================================================================

root-context.xml 에 빈등록(SqlSessionFactoryBean, SqlSessionTemplate)

===============================================================================================

<!-- 3.mybatis -->

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSource" />

<!-- <property name="mapperLocations" value="classpath:jdbc/sqlMapper_*.xml" 

/> -->

<property name="mapperLocations">

<list>

<value>classpath:jdbc/sqlMapper_test.xml</value>

</list>

</property>

</bean>

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">

<constructor-arg ref="sqlSessionFactory" />

</bean>

1. <?xml version="1.0" encoding="UTF-8"?>
2. <beans xmlns="http://www.springframework.org/schema/beans"
3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4.     xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
5.  
6.     <!-- Root Context: defines shared resources visible to all other web components -->
7.  
8.  
9.     <bean id="propertyPlaceholderConfigurer"
10.         class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
11.         <property name="locations">
12.             <value>classpath:jdbc/oracle.properties</value>
13.         </property>
14.     </bean>
15.    
16.     <!--1. pom.xml commons-dbcp.jar -->
17.     <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
18.         <property name="driverClassName" value="${oracle.DRIVER_NAME}" />
19.         <property name="url" value="${oracle.URL}" />
20.         <property name="username" value="${oracle.USER_ID}" />
21.         <property name="password" value="${oracle.USER_PW}" />
22.     </bean>
23.    
24.     <!-- 2. JDBC 드라이버 연동 & URL 커넥션 pom.xml spring-jdbc.jar -->
25.     <!-- sqlSession을 사용할것이므로 jdbcTemplate는 사용하지 않는다. -->
26.     <!-- <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
27.         <property name="dataSource" ref="dataSource"></property>
28.     </bean> -->
29.    
30.    
31.     <!-- 3.mybatis -->
32.     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
33.         <property name="dataSource" ref="dataSource" />
34.         <!--
35.         모든 xml을 읽을 경우 사용..안읽힐 가능성도 있으므로 아래처럼 각각 기술해주는것이 좋다.
36.         <property name="mapperLocations" value="classpath:jdbc/sqlMapper_*.xml"/>
37.         -->
38.         <property name="mapperLocations">
39.             <list>
40.                 <value>classpath:jdbc/sqlMapper_test.xml</value>
41.             </list>
42.         </property>
43.     </bean>
44.  
45.     <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
46.         <constructor-arg ref="sqlSessionFactory" />
47.     </bean>
48.    
49. </beans>

========================================================================

sqlMapper_test.xml 추가

명칭은 상관없음..root-context.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="test.com.spring03">

<!-- #{name},#{tel} -->

<insert id="insert" parameterType="test.com.spring03.TestVO">

</insert>

<update id="update" parameterType="test.com.spring03.TestVO">

</update>

<delete id="delete" parameterType="int">

</delete>

<select id="select" resultType="test.com.spring03.TestVO">

</select>

<!-- select * from member where id=? -->

<select id="search" resultType="test.com.spring03.TestVO">

</select>

<!-- select * from member where name like ?"; -->

<select id="searchList" resultType="test.com.spring03.TestVO" parameterType="hashmap">

<!-- select * from member -->

<!-- where name LIKE #{searchWord} -->

<!-- <if test="searchKey =='name'"> -->

<!-- where name LIKE #{searchWord} -->

<!-- </if> -->

<!-- <if test="searchKey !='name'"> -->

<!-- where tel LIKE #{searchWord} -->

<!-- </if> -->

</select>

</mapper>

1. <?xml version="1.0" encoding="UTF-8"?>
2.  
3. <!DOCTYPE mapper
4.   PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
5.   "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
6.  
7. <mapper namespace="test.com.spring03">
8.     <!-- #{name},#{tel} -->
9.     <!-- #{name},#{tel}은 TestVO에 멤버변수명과 같아야 사용할 수 있다. -->
10.    
11.     <insert id="insert" parameterType="test.com.spring03.TestVO">
12.         insert into stest(num,name,tel) values (seq_stest_num.nextval,#{name},#{tel})
13.     </insert>
14.  
15.     <update id="update" parameterType="test.com.spring03.TestVO">
16.         update stest set name=#{name},tel=#{tel} where num = #{num}
17.     </update>
18.  
19.     <delete id="delete" parameterType="int">
20.         delete from stest where num = #{num}
21.     </delete>
22.  
23.     <select id="select" resultType="test.com.spring03.TestVO">
24.         select * from stest order by num desc
25.     </select>
26.  
27.  
28.     <!-- select * from member where id=? -->
29.     <select id="search" resultType="test.com.spring03.TestVO">
30.         select * from stest where num = #{num}
31.     </select>
32.  
33.     <!-- select * from member where name like ?"; -->
34.     <select id="searchList" resultType="test.com.spring03.TestVO" parameterType="hashmap">
35.         select * from stest
36.         <!-- where name LIKE #{searchWord} -->
37.         <if test="searchKey =='name'">
38.          where name LIKE #{searchWord}
39.         </if>
40.         <if test="searchKey !='name'">
41.         where tel LIKE #{searchWord}
42.         </if>
43.     </select>
44. </mapper>

========================================================================

DAOImpl.java 예제

========================================================================

1. package test.com.spring03;
2.  
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.util.ArrayList;
6. import java.util.HashMap;
7. import java.util.List;
8. import java.util.Map;
9.  
10. import org.apache.ibatis.session.SqlSession;
11. import org.slf4j.Logger;
12. import org.slf4j.LoggerFactory;
13. import org.springframework.beans.factory.annotation.Autowired;
14. import org.springframework.jdbc.core.JdbcTemplate;
15. import org.springframework.jdbc.core.RowMapper;
16. import org.springframework.stereotype.Repository;
17.  
18. @Repository
19. public class TestDAOimpl implements TestDAO {
20.  
21.     private static final Logger logger = LoggerFactory.getLogger(TestDAOimpl.class);
22.  
23.     //3. sqlSession 사용시 쿼리문은 sqlMapper xml파일에서 적용한다.
24. //  private final String SQL_INSERT = "insert into stest(num,name,tel) values(seq_stest_num.nextval,?,?)";
25. //
26. //  private final String SQL_SELECT = "select * from stest order by num desc";
27. //
28. //  private final String SQL_SEARCH = "select * from stest where num = ?";
29. //  private final String SQL_SEARCH_NAME = "select * from stest where name like ?";
30. //  private final String SQL_SEARCH_TEL = "select * from stest where tel like ?";
31. //
32. //  private final String SQL_UPDATE = "update stest set name=?,tel=? where num = ?";
33. //
34. //  private final String SQL_DELETE = "delete from stest where num = ?";
35.  
36. //  private Connection conn;
37. //  private PreparedStatement pstmt;
38. //  private ResultSet rs;
39.    
40.     @Autowired
41.     SqlSession sqlSession;
42.    
43.     @Override
44.     public int insert(TestVO vo) {
45.         System.out.println("TestDAOimpl:insert()...");
46.         System.out.println(vo.getName());
47.         System.out.println(vo.getTel());
48.  
49.         int flag = 0;
50.         //3. sqlSession 사용예
51.         flag = sqlSession.insert("insert",vo);
52.        
53.        
54.         //2. JdbcTemplate사용예
55.         //flag = JdbcTemplate.update(SQL_INSERT, vo.getName(), vo.getTel());
56.         //flag = jdbcTemplate.update(SQL_INSERT, new Object[]{vo.getName(), vo.getTel()});
57.  
58. //    try {
59. //      conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
60. //      System.out.println("conn successed...");
61. //
62. //      pstmt = conn.prepareStatement(SQL_INSERT);
63. //      pstmt.setString(1, vo.getName());
64. //      pstmt.setString(2, vo.getTel());
65. //      flag = pstmt.executeUpdate();
66. //
67. //      if (flag == 1) {
68. //        System.out.println("insert successed...");
69. //      }
70. //    } catch (SQLException e) {
71. //      System.out.println("conn failed...");
72. //      e.printStackTrace();
73. //    } finally {
74. //      if (pstmt != null) {
75. //        try {
76. //          pstmt.close();
77. //        } catch (SQLException e) {
78. //        }
79. //      }
80. //      if (conn != null) {
81. //        try {
82. //          conn.close();
83. //        } catch (SQLException e) {
84. //        }
85. //      }
86. //    }
87.  
88.         return flag;
89.     }
90.  
91.     @Override
92.     public List<TestVO> select() {
93.         List<TestVO> list = null;
94.        
95.         //3. sqlSession 사용예
96.         list = sqlSession.selectList("select");
97.        
98.         //2. JdbcTemplate사용예
99. //    List<TestVO> list = jdbcTemplate.query(SQL_SELECT, new RowMapper<TestVO>() {
100. //
101. //      @Override
102. //      //mapRow는 리턴타입이 List이며, add와 rs.next()역활을 수행해준다.
103. //      public TestVO mapRow(ResultSet rs, int index) throws SQLException {
104. //        TestVO vo = new TestVO();
105. //        vo.setNum(rs.getInt("num"));
106. //        vo.setName(rs.getString("name"));
107. //        vo.setTel(rs.getString("tel"));
108. //        return vo;
109. //      }
110. //     
111. //    });
112. //   
113.        
114. //    try {
115. //      conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
116. //      System.out.println("conn successed...");
117. //
118. //      pstmt = conn.prepareStatement(SQL_SELECT);
119. //
120. //      rs = pstmt.executeQuery();
121. //
122. //      while (rs.next()) {
123. //        TestVO vo = new TestVO();
124. //        vo.setNum(rs.getInt("num"));
125. //        vo.setName(rs.getString("name"));
126. //        vo.setTel(rs.getString("tel"));
127. //        list.add(vo);
128. //      }
129. //
130. //    } catch (SQLException e) {
131. //      System.out.println("conn failed...");
132. //      e.printStackTrace();
133. //    } finally {
134. //      if (rs != null) {
135. //        try {
136. //          rs.close();
137. //        } catch (SQLException e) {
138. //        }
139. //      }
140. //      if (pstmt != null) {
141. //        try {
142. //          pstmt.close();
143. //        } catch (SQLException e) {
144. //        }
145. //      }
146. //      if (conn != null) {
147. //        try {
148. //          conn.close();
149. //        } catch (SQLException e) {
150. //        }
151. //      }
152. //    }
153.  
154.         return list;
155.     }
156.  
157.     @Override
158.     public int update(TestVO vo) {
159.         System.out.println(vo.getNum());
160.         System.out.println(vo.getName());
161.         System.out.println(vo.getTel());
162.  
163.         int flag = 0;
164.        
165.        
166.         //3. sqlSession 사용예
167.         flag = sqlSession.update("update", vo);
168.        
169.         //2. JdbcTemplate사용예
170.         //Object[] update_data = new Object[]{vo.getName(), vo.getTel(), vo.getNum()};
171.         //flag = jdbcTemplate.update(SQL_UPDATE, update_data);
172.  
173. //    try {
174. //      conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
175. //      System.out.println("conn successed...");
176. //
177. //      pstmt = conn.prepareStatement(SQL_UPDATE);
178. //      pstmt.setString(1, vo.getName());
179. //      pstmt.setString(2, vo.getTel());
180. //      pstmt.setInt(3, vo.getNum());
181. //      flag = pstmt.executeUpdate();
182. //
183. //      if (flag == 1) {
184. //        System.out.println("update successed...");
185. //      }
186. //    } catch (SQLException e) {
187. //      System.out.println("conn failed...");
188. //      e.printStackTrace();
189. //    } finally {
190. //      if (pstmt != null) {
191. //        try {
192. //          pstmt.close();
193. //        } catch (SQLException e) {
194. //        }
195. //      }
196. //      if (conn != null) {
197. //        try {
198. //          conn.close();
199. //        } catch (SQLException e) {
200. //        }
201. //      }
202. //    }
203.  
204.         return flag;
205.     }
206.  
207.     @Override
208.     public int delete(TestVO vo) {
209.         System.out.println(vo.getNum());
210.  
211.         int flag = 0;
212.        
213.         //3. sqlSession 사용예
214.         flag = sqlSession.delete("delete", vo.getNum());
215.        
216.         //2. JdbcTemplate사용예
217.         //flag = jdbcTemplate.update(SQL_DELETE, new Object[]{vo.getNum()});
218.  
219. //    try {
220. //      conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
221. //      System.out.println("conn successed...");
222. //
223. //      pstmt = conn.prepareStatement(SQL_DELETE);
224. //      pstmt.setInt(1, vo.getNum());
225. //      flag = pstmt.executeUpdate();
226. //
227. //      if (flag == 1) {
228. //        System.out.println("delete successed...");
229. //      }
230. //    } catch (SQLException e) {
231. //      System.out.println("conn failed...");
232. //      e.printStackTrace();
233. //    } finally {
234. //      if (pstmt != null) {
235. //        try {
236. //          pstmt.close();
237. //        } catch (SQLException e) {
238. //        }
239. //      }
240. //      if (conn != null) {
241. //        try {
242. //          conn.close();
243. //        } catch (SQLException e) {
244. //        }
245. //      }
246. //    }
247.  
248.         return flag;
249.     }
250.  
251.     @Override
252.     public List<TestVO> search(String searchKey, String searchWord) {
253.         logger.info("search()...searchKey:" + searchKey);
254.         logger.info("search()...searchWord:" + searchWord);
255.          
256.         List<TestVO> list = null;
257.         
258.         Map<String, String> map = new HashMap<String, String>();
259.         map.put("searchKey", searchKey);
260.         map.put("searchWord", "%"+searchWord+"%");
261.        //3. sqlSession 사용예
262.         list = sqlSession.selectList("searchList", map );
263.            
264.         //2. JdbcTemplate사용예
265. //    Object[] search_data = new Object[]{"%" + searchWord + "%"};
266. //   
267. //    String sql = "";
268. //    if (searchKey.equals("name")) {
269. //      sql = SQL_SEARCH_NAME;
270. //    } else {
271. //      sql = SQL_SEARCH_TEL;
272. //    }
273. //    sql += " order by num desc";
274. //     List<TestVO> list = jdbcTemplate.query(sql, search_data, new RowMapper<TestVO>(){
275. //
276. //      @Override
277. //      public TestVO mapRow(ResultSet rs, int index) throws SQLException {
278. //        TestVO vo = new TestVO();
279. //        vo.setNum(rs.getInt("num"));
280. //        vo.setName(rs.getString("name"));
281. //        vo.setTel(rs.getString("tel"));
282. //        return vo;
283. //      }
284. //     
285. //    });
286.  
287. //    try {
288. //      conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
289. //      System.out.println("conn successed...");
290. //
291. //      String sql = "";
292. //      if (searchKey.equals("name")) {
293. //        sql = SQL_SEARCH_NAME;
294. //      } else {
295. //        sql = SQL_SEARCH_TEL;
296. //      }
297. //      sql += " order by num desc";
298. //
299. //      pstmt = conn.prepareStatement(sql);
300. //      pstmt.setString(1, "%" + searchWord + "%");
301. //      rs = pstmt.executeQuery();
302. //
303. //      while (rs.next()) {
304. //        TestVO vo = new TestVO();
305. //        vo.setNum(rs.getInt("num"));
306. //        vo.setName(rs.getString("name"));
307. //        vo.setTel(rs.getString("tel"));
308. //        list.add(vo);
309. //      }
310. //
311. //    } catch (SQLException e) {
312. //      System.out.println("conn failed...");
313. //      e.printStackTrace();
314. //    } finally {
315. //      if (rs != null) {
316. //        try {
317. //          rs.close();
318. //        } catch (SQLException e) {
319. //        }
320. //      }
321. //      if (pstmt != null) {
322. //        try {
323. //          pstmt.close();
324. //        } catch (SQLException e) {
325. //        }
326. //      }
327. //      if (conn != null) {
328. //        try {
329. //          conn.close();
330. //        } catch (SQLException e) {
331. //        }
332. //      }
333. //    }
334.  
335.         return list;
336.     }
337.  
338.     @Override
339.     public TestVO search(TestVO vo) {
340.         logger.info("search()..." + vo.getNum());
341.         TestVO tvo = null;
342.        
343.         //3. sqlSession 사용예
344.         tvo = sqlSession.selectOne("search", vo.getNum());
345.        
346.         //2. JdbcTemplate사용예
347. //    TestVO tvo = jdbcTemplate.queryForObject(SQL_SEARCH, new Object[]{vo.getNum()}, new RowMapper<TestVO>(){
348. //
349. //      @Override
350. //      public TestVO mapRow(ResultSet rs, int index) throws SQLException {
351. //        // TODO Auto-generated method stub
352. //        TestVO x = new TestVO();
353. //        x.setNum(rs.getInt("num"));
354. //        x.setName(rs.getString("name"));
355. //        x.setTel(rs.getString("tel"));
356. //        return x;
357. //      }
358. //     
359. //    });
360.        
361.         //1. JdbcTemplate사용예
362. //    try {
363. //      conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
364. //      System.out.println("conn successed...");
365. //
366. //      pstmt = conn.prepareStatement(SQL_SEARCH);
367. //      pstmt.setInt(1, vo.getNum());
368. //      rs = pstmt.executeQuery();
369. //
370. //      while (rs.next()) {
371. //        tvo.setNum(rs.getInt("num"));
372. //        tvo.setName(rs.getString("name"));
373. //        tvo.setTel(rs.getString("tel"));
374. //      }
375. //
376. //    } catch (SQLException e) {
377. //      System.out.println("conn failed...");
378. //      e.printStackTrace();
379. //    } finally {
380. //      if (rs != null) {
381. //        try {
382. //          rs.close();
383. //        } catch (SQLException e) {
384. //        }
385. //      }
386. //      if (pstmt != null) {
387. //        try {
388. //          pstmt.close();
389. //        } catch (SQLException e) {
390. //        }
391. //      }
392. //      if (conn != null) {
393. //        try {
394. //          conn.close();
395. //        } catch (SQLException e) {
396. //        }
397. //      }
398. //    }
399.  
400.         return tvo;
401.     }
402.  
403. }