티스토리 뷰
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 |