일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
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 |
- 상속예제
- 다형성
- angular2
- 전체
- 페이징
- full text indexing
- 로또
- Full text
- 단축키
- IBatis procedure
- 추상클래스
- 다운캐스팅
- 이클립스
- 자바 야구게임
- 상속
- 형변환
- jquery
- 업캐스팅
- Random
- 자바
- Login with OAuth Authentication
- 25가지 효율적인 sql작성법
- 전체텍스트
- while
- 야구게임
- 스프링
- 전자정부
- 가변인자
- Validations
- Today
- Total
nalaolla
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>
- <?xml version="1.0" encoding="UTF-8"?>
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
- <!-- Root Context: defines shared resources visible to all other web components -->
- <bean id="propertyPlaceholderConfigurer"
- class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
- <property name="locations">
- <value>classpath:jdbc/oracle.properties</value>
- </property>
- </bean>
- <!--1. pom.xml commons-dbcp.jar -->
- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
- <property name="driverClassName" value="${oracle.DRIVER_NAME}" />
- <property name="url" value="${oracle.URL}" />
- <property name="username" value="${oracle.USER_ID}" />
- <property name="password" value="${oracle.USER_PW}" />
- </bean>
- <!-- 2. JDBC 드라이버 연동 & URL 커넥션 pom.xml spring-jdbc.jar -->
- <!-- sqlSession을 사용할것이므로 jdbcTemplate는 사용하지 않는다. -->
- <!-- <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
- <property name="dataSource" ref="dataSource"></property>
- </bean> -->
- <!-- 3.mybatis -->
- <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
- <property name="dataSource" ref="dataSource" />
- <!--
- 모든 xml을 읽을 경우 사용..안읽힐 가능성도 있으므로 아래처럼 각각 기술해주는것이 좋다.
- <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>
- </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>
- <?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} -->
- <!-- #{name},#{tel}은 TestVO에 멤버변수명과 같아야 사용할 수 있다. -->
- <insert id="insert" parameterType="test.com.spring03.TestVO">
- insert into stest(num,name,tel) values (seq_stest_num.nextval,#{name},#{tel})
- </insert>
- <update id="update" parameterType="test.com.spring03.TestVO">
- update stest set name=#{name},tel=#{tel} where num = #{num}
- </update>
- <delete id="delete" parameterType="int">
- delete from stest where num = #{num}
- </delete>
- <select id="select" resultType="test.com.spring03.TestVO">
- select * from stest order by num desc
- </select>
- <!-- select * from member where id=? -->
- <select id="search" resultType="test.com.spring03.TestVO">
- select * from stest where num = #{num}
- </select>
- <!-- select * from member where name like ?"; -->
- <select id="searchList" resultType="test.com.spring03.TestVO" parameterType="hashmap">
- select * from stest
- <!-- where name LIKE #{searchWord} -->
- <if test="searchKey =='name'">
- where name LIKE #{searchWord}
- </if>
- <if test="searchKey !='name'">
- where tel LIKE #{searchWord}
- </if>
- </select>
- </mapper>
========================================================================
DAOImpl.java 예제
========================================================================
- package test.com.spring03;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.apache.ibatis.session.SqlSession;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.jdbc.core.JdbcTemplate;
- import org.springframework.jdbc.core.RowMapper;
- import org.springframework.stereotype.Repository;
- @Repository
- public class TestDAOimpl implements TestDAO {
- private static final Logger logger = LoggerFactory.getLogger(TestDAOimpl.class);
- //3. sqlSession 사용시 쿼리문은 sqlMapper xml파일에서 적용한다.
- // private final String SQL_INSERT = "insert into stest(num,name,tel) values(seq_stest_num.nextval,?,?)";
- //
- // private final String SQL_SELECT = "select * from stest order by num desc";
- //
- // private final String SQL_SEARCH = "select * from stest where num = ?";
- // private final String SQL_SEARCH_NAME = "select * from stest where name like ?";
- // private final String SQL_SEARCH_TEL = "select * from stest where tel like ?";
- //
- // private final String SQL_UPDATE = "update stest set name=?,tel=? where num = ?";
- //
- // private final String SQL_DELETE = "delete from stest where num = ?";
- // private Connection conn;
- // private PreparedStatement pstmt;
- // private ResultSet rs;
- @Autowired
- SqlSession sqlSession;
- @Override
- public int insert(TestVO vo) {
- System.out.println("TestDAOimpl:insert()...");
- System.out.println(vo.getName());
- System.out.println(vo.getTel());
- int flag = 0;
- //3. sqlSession 사용예
- flag = sqlSession.insert("insert",vo);
- //2. JdbcTemplate사용예
- //flag = JdbcTemplate.update(SQL_INSERT, vo.getName(), vo.getTel());
- //flag = jdbcTemplate.update(SQL_INSERT, new Object[]{vo.getName(), vo.getTel()});
- // try {
- // conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
- // System.out.println("conn successed...");
- //
- // pstmt = conn.prepareStatement(SQL_INSERT);
- // pstmt.setString(1, vo.getName());
- // pstmt.setString(2, vo.getTel());
- // flag = pstmt.executeUpdate();
- //
- // if (flag == 1) {
- // System.out.println("insert successed...");
- // }
- // } catch (SQLException e) {
- // System.out.println("conn failed...");
- // e.printStackTrace();
- // } finally {
- // if (pstmt != null) {
- // try {
- // pstmt.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (conn != null) {
- // try {
- // conn.close();
- // } catch (SQLException e) {
- // }
- // }
- // }
- return flag;
- }
- @Override
- public List<TestVO> select() {
- List<TestVO> list = null;
- //3. sqlSession 사용예
- list = sqlSession.selectList("select");
- //2. JdbcTemplate사용예
- // List<TestVO> list = jdbcTemplate.query(SQL_SELECT, new RowMapper<TestVO>() {
- //
- // @Override
- // //mapRow는 리턴타입이 List이며, add와 rs.next()역활을 수행해준다.
- // public TestVO mapRow(ResultSet rs, int index) throws SQLException {
- // TestVO vo = new TestVO();
- // vo.setNum(rs.getInt("num"));
- // vo.setName(rs.getString("name"));
- // vo.setTel(rs.getString("tel"));
- // return vo;
- // }
- //
- // });
- //
- // try {
- // conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
- // System.out.println("conn successed...");
- //
- // pstmt = conn.prepareStatement(SQL_SELECT);
- //
- // rs = pstmt.executeQuery();
- //
- // while (rs.next()) {
- // TestVO vo = new TestVO();
- // vo.setNum(rs.getInt("num"));
- // vo.setName(rs.getString("name"));
- // vo.setTel(rs.getString("tel"));
- // list.add(vo);
- // }
- //
- // } catch (SQLException e) {
- // System.out.println("conn failed...");
- // e.printStackTrace();
- // } finally {
- // if (rs != null) {
- // try {
- // rs.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (pstmt != null) {
- // try {
- // pstmt.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (conn != null) {
- // try {
- // conn.close();
- // } catch (SQLException e) {
- // }
- // }
- // }
- return list;
- }
- @Override
- public int update(TestVO vo) {
- System.out.println(vo.getNum());
- System.out.println(vo.getName());
- System.out.println(vo.getTel());
- int flag = 0;
- //3. sqlSession 사용예
- flag = sqlSession.update("update", vo);
- //2. JdbcTemplate사용예
- //Object[] update_data = new Object[]{vo.getName(), vo.getTel(), vo.getNum()};
- //flag = jdbcTemplate.update(SQL_UPDATE, update_data);
- // try {
- // conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
- // System.out.println("conn successed...");
- //
- // pstmt = conn.prepareStatement(SQL_UPDATE);
- // pstmt.setString(1, vo.getName());
- // pstmt.setString(2, vo.getTel());
- // pstmt.setInt(3, vo.getNum());
- // flag = pstmt.executeUpdate();
- //
- // if (flag == 1) {
- // System.out.println("update successed...");
- // }
- // } catch (SQLException e) {
- // System.out.println("conn failed...");
- // e.printStackTrace();
- // } finally {
- // if (pstmt != null) {
- // try {
- // pstmt.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (conn != null) {
- // try {
- // conn.close();
- // } catch (SQLException e) {
- // }
- // }
- // }
- return flag;
- }
- @Override
- public int delete(TestVO vo) {
- System.out.println(vo.getNum());
- int flag = 0;
- //3. sqlSession 사용예
- flag = sqlSession.delete("delete", vo.getNum());
- //2. JdbcTemplate사용예
- //flag = jdbcTemplate.update(SQL_DELETE, new Object[]{vo.getNum()});
- // try {
- // conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
- // System.out.println("conn successed...");
- //
- // pstmt = conn.prepareStatement(SQL_DELETE);
- // pstmt.setInt(1, vo.getNum());
- // flag = pstmt.executeUpdate();
- //
- // if (flag == 1) {
- // System.out.println("delete successed...");
- // }
- // } catch (SQLException e) {
- // System.out.println("conn failed...");
- // e.printStackTrace();
- // } finally {
- // if (pstmt != null) {
- // try {
- // pstmt.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (conn != null) {
- // try {
- // conn.close();
- // } catch (SQLException e) {
- // }
- // }
- // }
- return flag;
- }
- @Override
- public List<TestVO> search(String searchKey, String searchWord) {
- logger.info("search()...searchKey:" + searchKey);
- logger.info("search()...searchWord:" + searchWord);
- List<TestVO> list = null;
- Map<String, String> map = new HashMap<String, String>();
- map.put("searchKey", searchKey);
- map.put("searchWord", "%"+searchWord+"%");
- //3. sqlSession 사용예
- list = sqlSession.selectList("searchList", map );
- //2. JdbcTemplate사용예
- // Object[] search_data = new Object[]{"%" + searchWord + "%"};
- //
- // String sql = "";
- // if (searchKey.equals("name")) {
- // sql = SQL_SEARCH_NAME;
- // } else {
- // sql = SQL_SEARCH_TEL;
- // }
- // sql += " order by num desc";
- // List<TestVO> list = jdbcTemplate.query(sql, search_data, new RowMapper<TestVO>(){
- //
- // @Override
- // public TestVO mapRow(ResultSet rs, int index) throws SQLException {
- // TestVO vo = new TestVO();
- // vo.setNum(rs.getInt("num"));
- // vo.setName(rs.getString("name"));
- // vo.setTel(rs.getString("tel"));
- // return vo;
- // }
- //
- // });
- // try {
- // conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
- // System.out.println("conn successed...");
- //
- // String sql = "";
- // if (searchKey.equals("name")) {
- // sql = SQL_SEARCH_NAME;
- // } else {
- // sql = SQL_SEARCH_TEL;
- // }
- // sql += " order by num desc";
- //
- // pstmt = conn.prepareStatement(sql);
- // pstmt.setString(1, "%" + searchWord + "%");
- // rs = pstmt.executeQuery();
- //
- // while (rs.next()) {
- // TestVO vo = new TestVO();
- // vo.setNum(rs.getInt("num"));
- // vo.setName(rs.getString("name"));
- // vo.setTel(rs.getString("tel"));
- // list.add(vo);
- // }
- //
- // } catch (SQLException e) {
- // System.out.println("conn failed...");
- // e.printStackTrace();
- // } finally {
- // if (rs != null) {
- // try {
- // rs.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (pstmt != null) {
- // try {
- // pstmt.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (conn != null) {
- // try {
- // conn.close();
- // } catch (SQLException e) {
- // }
- // }
- // }
- return list;
- }
- @Override
- public TestVO search(TestVO vo) {
- logger.info("search()..." + vo.getNum());
- TestVO tvo = null;
- //3. sqlSession 사용예
- tvo = sqlSession.selectOne("search", vo.getNum());
- //2. JdbcTemplate사용예
- // TestVO tvo = jdbcTemplate.queryForObject(SQL_SEARCH, new Object[]{vo.getNum()}, new RowMapper<TestVO>(){
- //
- // @Override
- // public TestVO mapRow(ResultSet rs, int index) throws SQLException {
- // // TODO Auto-generated method stub
- // TestVO x = new TestVO();
- // x.setNum(rs.getInt("num"));
- // x.setName(rs.getString("name"));
- // x.setTel(rs.getString("tel"));
- // return x;
- // }
- //
- // });
- //1. JdbcTemplate사용예
- // try {
- // conn = DriverManager.getConnection(URL, USER_ID, USER_PW);
- // System.out.println("conn successed...");
- //
- // pstmt = conn.prepareStatement(SQL_SEARCH);
- // pstmt.setInt(1, vo.getNum());
- // rs = pstmt.executeQuery();
- //
- // while (rs.next()) {
- // tvo.setNum(rs.getInt("num"));
- // tvo.setName(rs.getString("name"));
- // tvo.setTel(rs.getString("tel"));
- // }
- //
- // } catch (SQLException e) {
- // System.out.println("conn failed...");
- // e.printStackTrace();
- // } finally {
- // if (rs != null) {
- // try {
- // rs.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (pstmt != null) {
- // try {
- // pstmt.close();
- // } catch (SQLException e) {
- // }
- // }
- // if (conn != null) {
- // try {
- // conn.close();
- // } catch (SQLException e) {
- // }
- // }
- // }
- return tvo;
- }
- }
'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 |