티스토리 뷰

Framework/Spring

[Spring & ibatis] 수업 6일

gray.yoon 2013. 4. 29. 19:02

2주차 강의 진행 방향

 

6일차 : ibatis / web 환경

7일차 : servlet/jsp 원리, model 1, model2, springMVC

8일차 : springMVC(MultiActionController),DI+AOP,*ibatis 통합

9일차 : 비동기통신+springMVC+ibatis

10일차 : annotation(DI,AOP,MVC), transaction, Tiles

 

[ibatis]

config -> DBCP(DB일반정보) - SqlMapConfig.xml

  sql 정의.xml****(여러개)

하드코딩 된 부분을 xml파일로 모두 뺀다.

-----------------------------------------------

SqlMapFactory(SingleTon) config 파일을 모두 읽어 로딩

ibatis lib : SqlMapClient (SqlMapFactory객체에서 반환된 정보를 담는다.)

 

 

 

[실습]

 

1. 기본 실습

product.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
53
54
55
56
57
58
59
60
61
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" 
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
<typeAlias alias="pro" type="model.ProductVO"/>
<insert id="insertProduct" parameterClass="pro">
    insert into product(id,name,maker,price)
    values(#id#,#name#,#maker#,#price#)
</insert>
<select id="selectProductById" resultClass="pro"
 parameterClass="java.lang.String">
    select id,name,maker,price from product where id=#value#
</select>
<update id="updateProduct" parameterClass="pro">
    update product set name=#name#,maker=#maker#,price=#price#
    where id=#id#
</update>
<update id="updateProductTest" parameterClass="hashmap">
    update product set name=#name#,maker=#maker#
    where price=#price#
</update>
<select id="selectAllProductList" resultClass="pro" >
    select id,name,maker,price from product
</select>
<select id="selectProductListByMaker" resultClass="pro">
    select id,name,maker,price from product where maker=#value#
</select>
<insert id="insertProductParamMap" parameterClass="hashmap">
    insert into product(id,name,maker,price)
    values(#ID#,#NAME#,#MAKER#,#PRICE#)
</insert>
<sql id="selectProduct">
select id,name,maker,price from product 
</sql>
<select id="getProductMapById" resultClass="hashmap"
 parameterClass="string">
    <include refid="selectProduct"/>
    where id=#value#
</select>
<select id="getProductListByParamMap" resultClass="hashmap"
 parameterClass="hashmap">
<include refid="selectProduct"/>
where name=#NAME# and maker=#MAKER#
</select>
<select id="getProductListLikeName" parameterClass="string" resultClass="pro">
<include refid="selectProduct"/>
  where name like '%$value$%'
</select>
<select id="getProductTotalPrice" parameterClass="string" 
resultClass="java.lang.Integer">
    select sum(price) from product 
    where maker=#value#
</select>
<select id="selectProductListByPrice" parameterClass="java.lang.Integer" resultClass="hashmap">
<include refid="selectProduct"/>
<![CDATA[
    where price<=#value# 
]]>
</select>
<!-- Character DATA 문자 데이터: parsing 되지 않는 문자 데이터 -->
</sqlMap>

 

SqlMapConfig.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" ?>
<!DOCTYPE sqlMapConfig      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<!-- database 일반 정보 설정 -->
<transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/>
      <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:xe"/>
      <property name="JDBC.Username" value="spring"/>
      <property name="JDBC.Password" value="oracle"/>
    </dataSource>
  </transactionManager>
  <!-- SQL 정의 XML을 링크  -->
   <sqlMap resource="config/product.xml"/>
</sqlMapConfig>

 

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
package factory;
 
import java.io.IOException;
import java.io.Reader;
 
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
 
/**
 * ibatis 설정 파일(SqlMapConfig.xml)을 로딩하여
 * SqlMapClient 를 생성하는 클래스 
 * @author admin
 *
 */
public class SqlMapFactory {
    private static SqlMapFactory factory=new SqlMapFactory();
    private SqlMapClient sqlMapClient;
    private SqlMapFactory(){
        // 설정 파일을 로딩해서 SqlMapClient 객체를 얻어 온다.  
        try {
            Reader reader
            =Resources.getResourceAsReader("config/SqlMapConfig.xml");
            this.sqlMapClient=SqlMapClientBuilder.buildSqlMapClient(reader);
            System.out.println("ibatis config loading.."+sqlMapClient);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlMapFactory getInstance(){
        return factory;
    }
    public SqlMapClient getSqlMapClient() {
        return sqlMapClient;
    }
    
}

 

 

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
package model;
 
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import com.ibatis.sqlmap.client.SqlMapClient;
 
import factory.SqlMapFactory;
 
/**
 * ibatis framework 을 이용한 Dao (Data Access Object) 를 구현 
 * @author admin
 *
 */
public class ProductDao {
    private static ProductDao dao=new ProductDao();
    private SqlMapClient sqlMapClient;
    private ProductDao(){
        this.sqlMapClient=SqlMapFactory.getInstance().getSqlMapClient();
    }
    public static ProductDao getInstance(){
        return dao;
    }
    public void insertProduct(ProductVO vo) throws SQLException {
        sqlMapClient.insert("insertProduct", vo);
    }
    public ProductVO selectProductById(String id) throws SQLException {
        return (ProductVO)sqlMapClient.queryForObject("selectProductById",id);    
    }
    public void updateProduct(ProductVO productVO) throws SQLException {
        sqlMapClient.update("updateProduct", productVO);    
    }
    public List selectAllProductList() throws SQLException {
        return sqlMapClient.queryForList("selectAllProductList");
    }
    public List selectProductListByMaker(String maker) throws SQLException {
        return sqlMapClient.queryForList("selectProductListByMaker",maker);
    }
    public void insertProductParamMap(HashMap map) throws SQLException {
        sqlMapClient.insert("insertProductParamMap", map);
    }
    public Map getProductMapById(String id) throws SQLException {
        return (Map) sqlMapClient.queryForObject("getProductMapById",id);
    }
    public List<Map> getProductListByParamMap(HashMap paramMap) throws SQLException {
        // TODO Auto-generated method stub
        return sqlMapClient.queryForList("getProductListByParamMap",paramMap);
    }
    public List<ProductVO> getProductListLikeName(String str) throws SQLException {
        // TODO Auto-generated method stub
        return sqlMapClient.queryForList("getProductListLikeName",str);
    }
    public int getProductTotalPrice(String maker) throws SQLException {
        // TODO Auto-generated method stub
        return (Integer)sqlMapClient.queryForObject("getProductTotalPrice",maker);
    }
    public List<Map> selectProductListByPrice(int price) throws SQLException {
        // TODO Auto-generated method stub
        return sqlMapClient.queryForList("selectProductListByPrice",price);
    }
    public void updateProductTest(HashMap map) throws SQLException {
        sqlMapClient.update("updateProductTest", map);
    }    
}

 

 

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
package model;
 
public class ProductVO {
    private String id;
    private String name;
    private String maker;
    private int price;
    public ProductVO() {
        super();
        // TODO Auto-generated constructor stub
    }
    public ProductVO(String id, String name, String maker, int price) {
        super();
        this.id = id;
        this.name = name;
        this.maker = maker;
        this.price = price;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getMaker() {
        return maker;
    }
    public void setMaker(String maker) {
        this.maker = maker;
    }
    public int getPrice() {
        return price;
    }
    public void setPrice(int price) {
        this.price = price;
    }
    @Override
    public String toString() {
        return "ProductVO [id=" + id + ", name=" + name + ", maker=" + maker
                + ", price=" + price + "]";
    }
    
}
 

 

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
package test;
 
import java.sql.SQLException;
 
import model.ProductDao;
import model.ProductVO;
 
public class TestProductDao {
    public static void main(String[] args) throws Exception{
        ProductDao dao=ProductDao.getInstance();
        /*try {
            dao.insertProduct(new ProductVO("p6","옵티머스G","LG",300));
            System.out.println("insert ok!");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }*/
        /* try {
            dao.updateProduct(new ProductVO("p6","옵티머스H","LG",400));
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            System.out.println(dao.selectProductById("p6"));
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }*/
        /*try {
            List<ProductVO> list=dao.selectAllProductList();
            for(ProductVO vo:list){
                System.out.println(vo);
            }
        } catch (SQLException e) {
            
            e.printStackTrace();
        }*/
        /*try {
            List<ProductVO> list=dao.selectProductListByMaker("LG");
            for(ProductVO vo:list){
                System.out.println(vo);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }*/
        ////////////////1차//////////////////////
        
        /*HashMap map=new HashMap();
        map.put("ID", "p7");
        map.put("NAME", "아이폰5");
        map.put("MAKER", "애플");
        map.put("PRICE", 500);
        //1번
        dao.insertProductParamMap(map);
        System.out.println("insert ok.."+map);*/
        //2번 
    /*    Map m1=dao.getProductMapById("p7");
        System.out.println(m1);*/
        //3번 
        /*HashMap paramMap=new HashMap();
        paramMap.put("NAME","아이폰5");
        paramMap.put("MAKER","애플");
        List<Map> list1=
                dao.getProductListByParamMap(paramMap);
        System.out.println(list1);*/
        //4번 
        /*List<ProductVO> list2
        =dao.getProductListLikeName("아이");
        System.out.println(list2);*/
        //5번 
        /*int sum=dao.getProductTotalPrice("애플");
        System.out.println("애플 상품 총액:"+sum);*/
        //6번 
        // 상품 가격이 200 이하인 상품 리스트를 조회
        /*List<Map> list3=dao.selectProductListByPrice(200);
        System.out.println(list3);*/
        /* Character DATA 문자 데이터: parsing 되지 않는 문자 데이터 
         *     xml 작성시 < , > 와 같은 특수문자를 
         *  문자 그대로 표현하는 형식 
         *  CDATA 로 선언된 영역에서는 태그가 없음을 
         *  xml 파서에 알린다. 
         *  <select id=
         *  <![CDATA[
         *      select......
         *  ]]>
         *  </select>
         */
        
    }
}

 

 

info

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
**ibatis Framework 를 이용해 ProductDao 구성하기**
1. 환경 설정 
    ibatis lib - classpath 를 추가 
    src/config/SqlMapConfig.xml 
                 product.xml 
2. 구현 
    1) 설정 파일을 로딩     
    factory.SqlMapFactory
    2) application 
    model.ProductVO -> 기본생성자와 인자값있는 생성자 오버로딩
    model.ProductDao
    test.TestProductDao
    
    3) 구현 테스트 
    test.TestProductDao
        main 메서드 
        dao.insertProduct(new ProductVO("p6","옵티머스G","LG",300));
        dao.selectProductById("p6");->ProductVO 로 확인 
        dao.updateProduct(new ProductVO("p6","옵티머스H","LG",400));
        //업데이트 확인 
        dao.selectProductById("p6");
        dao.selectAllProductList(); 
        List<ProductVO> 로 리턴받아 출력 
        dao.selectProductListByMaker("LG");
        List<ProductVO> 로 리턴받아 출력          

 

1
2
3
4
5
6
7
8
9
select * from product;
drop table product;
-- id,name,maker,price
create table product(
 id varchar2(50) primary key,
 name varchar2(50) not null,
 maker varchar2(50) not null,
 price number not null
)

 

 

2. resultMap을 이용한 실습(Bean 객체와 쿼리의 이름이 다를 경우)

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
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" 
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
<select id="getAllDeptList" resultClass="hashmap">
    select deptno,dname,loc from dept
</select>
<!-- 컬럼명과 vo 속성명이 불일치될 경우 
      컬럼 별칭으로 해결 -->
<select id="getAllDeptVOList" resultClass="model.DeptVO">
    select deptno,dname,loc as location from dept
</select>
<!-- 컬럼명과 vo 속성명이 불일치될 경우 
      resultMap 을 정의해 재사용    
 -->
<resultMap class="model.DeptVO" id="deptRM">
    <result property="deptno" column="deptno"/>
    <result property="dname" column="dname"/>
    <!-- 속성명과 컬럼명을 일치시킨다 -->
    <result property="location" column="loc"/>
</resultMap>
<select id="getAllDeptListByRM" resultMap="deptRM">
    select * from dept
</select>
<resultMap class="model.EmpVO" id="empRM">
<result property="empno" column="empno"/>
<result property="ename" column="ename"/>
<result property="job" column="job"/>
<!-- 불일치되는 컬럼명과 속성명을 일치시킨다 -->
<result property="manager" column="mgr"/>
<result property="hiredate" column="hiredate"/>
<!-- 일치 -->
<result property="salary" column="sal"/>
<!-- 일치 :  java 의 primitive data type 인 변수와 매칭되는 경우
                db에 null 값이 존재하면 반드시 nullValue로 초기값 지정해야 한다.
 -->
<result property="commission" column="comm" nullValue="0.0"/>
<result property="dept" column="deptno"/>
</resultMap>
<select id="getAllEmpListByRM" resultMap="empRM">
select * from emp
</select>
<select id="selectEmpByDynamicSql" parameterClass="model.EmpVO" resultMap="empRM">
    SELECT * FROM EMP
    <dynamic prepend="WHERE">
    <isNotNull prepend="and" property="ename">
    ENAME Like '%$ename$%'
    </isNotNull>
    <isNotEqual prepend="and" property="dept" compareValue="0">
    DEPTNO=#dept#
    </isNotEqual>
    </dynamic>
</select>
</sqlMap>

 

 

 

3. Sql Query xml을 여러개 분리하여 사용할 경우

 

customer.xml

1
2
3
4
5
6
7
8
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" 
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="customer">
<select id="find" resultClass="hashmap">
    select * from customer where ssn=#value#
</select>
</sqlMap>

 

member.xml

1
2
3
4
5
6
7
8
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" 
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="member">
<select id="find" resultClass="hashmap">
    select * from member where id=#value#
</select>
</sqlMap>

 

SqlMapConfig.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<!-- 여러 sql 정의 xml 을 구분해서 사용하기 위해 
      네임 스페이스를 사용한다. 
 -->
<settings useStatementNamespaces="true"/>
<!-- database 일반 정보 설정 -->
<transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/>
      <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:xe"/>
      <property name="JDBC.Username" value="spring"/>
      <property name="JDBC.Password" value="oracle"/>
    </dataSource>
  </transactionManager>
  <!-- SQL 정의 XML을 링크  -->
   <sqlMap resource="config/member.xml"/>
   <sqlMap resource="config/customer.xml"/>
</sqlMapConfig>

 

 

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
package factory;
 
import java.io.IOException;
import java.io.Reader;
 
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
 
/**
 * ibatis 설정 파일(SqlMapConfig.xml)을 로딩하여
 * SqlMapClient 를 생성하는 클래스 
 * @author admin
 *
 */
public class SqlMapFactory {
    private static SqlMapFactory factory=new SqlMapFactory();
    private SqlMapClient sqlMapClient;
    private SqlMapFactory(){
        // 설정 파일을 로딩해서 SqlMapClient 객체를 얻어 온다.  
        try {
            Reader reader
            =Resources.getResourceAsReader("config/SqlMapConfig.xml");
            this.sqlMapClient=SqlMapClientBuilder.buildSqlMapClient(reader);
            System.out.println("ibatis config loading.."+sqlMapClient);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlMapFactory getInstance(){
        return factory;
    }
    public SqlMapClient getSqlMapClient() {
        return sqlMapClient;
    }
    
}

 

 

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
package model;
 
import java.sql.SQLException;
import java.util.Map;
 
import com.ibatis.sqlmap.client.SqlMapClient;
 
import factory.SqlMapFactory;
 
public class NameSpaceDao {
    private static NameSpaceDao dao=new NameSpaceDao();
    private SqlMapClient sqlMapClient;
    private NameSpaceDao(){
        sqlMapClient=SqlMapFactory.getInstance().getSqlMapClient();
    }
    public static NameSpaceDao getInstance(){
        return dao;
    }
    public Map findCustomer(String ssn) throws SQLException{
        //namespace 를 이용해 sql id 에 접근한다. 
        return (Map) sqlMapClient.queryForObject("customer.find", ssn);
    }
    public Map findMember(String id) throws SQLException{
        return (Map)sqlMapClient.queryForObject("member.find",id);
    }
}

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
package test;
 
import model.NameSpaceDao;
 
public class TestNameSpaceDao {
    /**
     * ibatis namespace를 활용하여 여러 sql 정의 xml을 사용 
     */
    public static void main(String[] args) throws Exception{
        NameSpaceDao dao=NameSpaceDao.getInstance();
        System.out.println(dao.findCustomer("90"));
        System.out.println(dao.findMember("java"));
    }
}

 

 

 

 

 

'Framework > Spring' 카테고리의 다른 글

[Spring & ibatis] 수업 7일-2(web)  (0) 2013.04.30
[Spring & ibatis] 수업 7일-1(ibatis 끝)  (0) 2013.04.30
[Spring & ibatis] 수업 5일  (0) 2013.04.26
[Spring & ibatis] 수업 4일  (0) 2013.04.25
[Spring & ibatis] 수업 3일  (0) 2013.04.24
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
링크
TAG
more
«   2024/05   »
일 월 화 수 목 금 토
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
글 보관함