관리 메뉴

nalaolla

mybatis사용하기 본문

SPRING

mybatis사용하기

날아올라↗↗ 2016. 3. 16. 15:56
728x90
반응형

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. }


728x90
반응형

'SPRING' 카테고리의 다른 글

인터셉터(Interceptor) 사용하기  (0) 2016.03.18
log4j 사용 및 적용방법  (0) 2016.03.17
BasicDataSource 적용하기  (0) 2016.03.14
JSON 파싱 dependency 추가  (0) 2016.03.08
Spring 한글처리  (0) 2016.03.08