<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
<typeAlias alias="mdto" type="ibatis.dto.MemberDTO"/>
<sql id="select">
select id,
password,
name,
register_number1 as registerNumber1,
register_number2 as registerNumber2,
mileage
from member
</sql>
<!-- insert태그 작성 -->
<insert id="insertMember" parameterClass="ibatis.dto.MemberDTO">
insert into member(id, password, name, register_number1, register_number2, mileage) values(#id#,#password#,#name#,#registerNumber1#,#registerNumber2#,#mileage#)
</insert>
<!-- select태그 작성 -->
<select id="selectMemberById" parameterClass="java.lang.String" resultClass="mdto" >
select id,
password,
name,
register_number1 as registerNumber1,
register_number2 as registerNumber2,
mileage
from member
where id=#id#
</select>
<!-- 주민번호로 조회 -->
<select id="selectMemberByRegisterNumber" parameterClass="java.util.HashMap" resultClass="mdto">
<include refid="select"/>
where register_number1 = #registerNumber1# and register_number2 = #registerNumber2#
</select>
<select id="selectAllMember" resultClass="mdto">
<include refid="select"/>
</select>
<select id="selectMemberByMileageRange" parameterClass="hashmap" resultClass="mdto">
<include refid="select"/>
<!-- where mileage >= #firstMileage# and mileage <= #endMileage# --><!-- where mileage between #firstMileage# and #endMileage# -->
<!-- CDATA는 <>를 테크로 읽히지 않게 해준다. -->
<![CDATA[
where mileage >= #firstMileage#
and mileage <= #endMileage#
]]>
</select>
</sqlMap>
<!-- xxxx.insert("insertMember", mdto); -->
<!-- Object obj = xxx.queryForObject("selectMemberById","abcde"); 추후 MemberDTO로 캐스팅해서 사용 -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
<typeAlias alias="mdto" type="ibatis.dto.MemberDTO"/>
<sql id="select">
select id,
password,
name,
register_number1,
register_number2 as registerNumber2,
mileage
from member
</sql>
<!-- resultMap 설정 -->
<resultMap id="memberResultMap" class="mdto">
<result property="id" column="id" />
<result property="password" columnIndex="2" />
<result property="name" columnIndex="3" />
<result property="registerNumber1" column="register_number1" />
<result property="registerNumber2" column="registerNumber2" />
<result property="mileage" columnIndex="6" nullValue="0"/>
</resultMap>
<!-- insert태그 작성 -->
<insert id="insertMember" parameterClass="ibatis.dto.MemberDTO">
insert into member(id, password, name, register_number1, register_number2, mileage) values(#id#,#password#,#name#,#registerNumber1#,#registerNumber2#,#mileage#)
</insert>
<!-- select태그 작성 -->
<select id="selectMemberById" parameterClass="java.lang.String" resultMap="memberResultMap">
select id,
password,
name,
register_number1,
register_number2 as registerNumber2,
mileage
from member
where id=#id#
</select>
<!-- 주민번호로 조회 -->
<select id="selectMemberByRegisterNumber" parameterClass="java.util.HashMap" resultMap="memberResultMap">
<include refid="select"/>
where register_number1 = #registerNumber1# and register_number2 = #registerNumber2#
</select>
<select id="selectAllMember" resultMap="memberResultMap">
<include refid="select"/>
</select>
<select id="selectMemberByMileageRange" parameterClass="hashmap" resultMap="memberResultMap">
<include refid="select"/>
<!-- where mileage >= #firstMileage# and mileage <= #endMileage# --><!-- where mileage between #firstMileage# and #endMileage# -->
<!-- CDATA는 <>를 테크로 읽히지 않게 해준다. -->
<![CDATA[
where mileage >= #firstMileage#
and mileage <= #endMileage#
]]>
</select>
<!-- update -->
<update id="updateMemberId" parameterClass="mdto">
update member
set password=#password#,
name=#name#,
register_number1=#registerNumber1#,
register_number2=#registerNumber2#,
mileage=#mileage#
where id=#id#
</update>
<delete id="deleteMemberById" parameterClass="string">
delete from member
where id=#id#
</delete>
</sqlMap>
<!-- xxxx.insert("insertMember", mdto); -->
<!-- Object obj = xxx.queryForObject("selectMemberById","abcde"); 추후 MemberDTO로 캐스팅해서 사용 -->
<?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>
<!-- Configure a built-in transaction manager. If you're using an
app server, you probably want to use its transaction manager
and a managed datasource -->
<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:@127.0.0.1:1521:XE"/>
<property name="JDBC.Username" value="scott"/>
<property name="JDBC.Password" value="tiger"/>
</dataSource>
</transactionManager>
<!-- List the SQL Map XML files. They can be loaded from the
classpath, as they are here (com.domain.data...) -->
<!-- <sqlMap resource="config/member.xml"/> -->
<sqlMap resource="config/member2.xml"/>
</sqlMapConfig>
package ibatis.dao;
import ibatis.dto.MemberDTO;
import java.io.Reader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class MemberDAO {
//싱글턴 패턴
private static MemberDAO instance = new MemberDAO();
private SqlMapClient sqlMap;//sql문 실행 메소드를 가진 객체
private MemberDAO(){
try {
Reader reader = Resources.getResourceAsReader("config/SqlMapConfig.xml");//SqlMapConfig.xml과 연결되어있는 reader를 하나 읽어옴
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
public static MemberDAO getInstance(){
return instance;
}
//회원데이터 insert메소드
public void insertMember(MemberDTO mdto) throws SQLException{
sqlMap.insert("insertMember", mdto);
}
//id로 회원 정보 조회
public MemberDTO selectMemberById(String id) throws SQLException{
return (MemberDTO)sqlMap.queryForObject("selectMemberById", id);
}
//주민번호로 조회
public MemberDTO selectMemberByRegisterNumber(String rN1, String rN2) throws SQLException{
HashMap<String, String> map = new HashMap<String, String>();
map.put("registerNumber1", rN1);
map.put("registerNumber2", rN2);
return (MemberDTO)sqlMap.queryForObject("selectMemberByRegisterNumber",map);
}
//전체 회원 정보 조회
public ArrayList<MemberDTO> selectAllMember() throws SQLException{
return (ArrayList<MemberDTO>)sqlMap.queryForList("selectAllMember");
}
public Map selectAllMemberMap() throws SQLException{
return sqlMap.queryForMap("selectAllMember", null, "id");//selectAllMember는 DB조회 id, null은 파라미터, id(property)가 키 값
}
public Map selectAllMemberMap2() throws SQLException{
return sqlMap.queryForMap("selectAllMember", null, "id","name");//selectAllMember는 DB조회 id, null은 파라미터, id(property)가 키 값 , name(property)가 value
}
public List selectMemberByMileageRange(int firstMileage,int endMileage) throws SQLException{
HashMap<String, Integer> map = new HashMap<String, Integer>();
map.put("firstMileage", firstMileage);
map.put("endMileage", endMileage);
return sqlMap.queryForList("selectMemberByMileageRange", map);
}
public Map selectMemberByMileageRange2(int firstMileage, int endMileage) throws SQLException {
HashMap<String, Integer> map = new HashMap<String, Integer>();
map.put("firstMileage", firstMileage);
map.put("endMileage", endMileage);
return sqlMap.queryForMap("selectMemberByMileageRange", map,"id","name");
}
//update 메소드
public int updateMemberId(MemberDTO mto) throws SQLException {
return sqlMap.update("updateMemberId",mto);
}
//delete메소드
public int deleteMemberById(String id)throws SQLException{
return sqlMap.delete("deleteMemberById", id);
}
}
package ibatis.dto;
public class MemberDTO {
private String id;
private String password;
private String name;
private String registerNumber1; //db: register_number1
private String registerNumber2; //register_number2
private int mileage;
//생성자
public MemberDTO() {
super();
}
public MemberDTO(String id, String password, String name,
String registerNumber1, String registerNumber2, int mileage) {
super();
this.id = id;
this.password = password;
this.name = name;
this.registerNumber1 = registerNumber1;
this.registerNumber2 = registerNumber2;
this.mileage = mileage;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRegisterNumber1() {
return registerNumber1;
}
public void setRegisterNumber1(String registerNumber1) {
this.registerNumber1 = registerNumber1;
}
public String getRegisterNumber2() {
return registerNumber2;
}
public void setRegisterNumber2(String registerNumber2) {
this.registerNumber2 = registerNumber2;
}
public int getMileage() {
return mileage;
}
public void setMileage(int mileage) {
this.mileage = mileage;
}
@Override
public String toString() {
return "MemberDTO [id=" + id + ", password=" + password + ", name="
+ name + ", registerNumber1=" + registerNumber1
+ ", registerNumber2=" + registerNumber2 + ", mileage="
+ mileage + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((id == null) ? 0 : id.hashCode());
result = prime * result + mileage;
result = prime * result + ((name == null) ? 0 : name.hashCode());
result = prime * result
+ ((password == null) ? 0 : password.hashCode());
result = prime * result
+ ((registerNumber1 == null) ? 0 : registerNumber1.hashCode());
result = prime * result
+ ((registerNumber2 == null) ? 0 : registerNumber2.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
MemberDTO other = (MemberDTO) obj;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
if (mileage != other.mileage)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
if (password == null) {
if (other.password != null)
return false;
} else if (!password.equals(other.password))
return false;
if (registerNumber1 == null) {
if (other.registerNumber1 != null)
return false;
} else if (!registerNumber1.equals(other.registerNumber1))
return false;
if (registerNumber2 == null) {
if (other.registerNumber2 != null)
return false;
} else if (!registerNumber2.equals(other.registerNumber2))
return false;
return true;
}
}
package ibatis.main;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import ibatis.dao.MemberDAO;
import ibatis.dto.MemberDTO;
public class TestMember {
public static void main(String[] args) throws SQLException {
MemberDTO mdto1 = new MemberDTO("id-66", "1111", "이상운", "111111", "1111111", 10000);
MemberDTO mdto2 = new MemberDTO("id-55", "2222", "장동건", "222222", "2222222", 20000);
MemberDTO mdto3 = new MemberDTO("id-77", "3333", "원빈", "333333", "3333333", 30000);
MemberDAO dao = MemberDAO.getInstance();
// dao.insertMember(mdto1);
// dao.insertMember(mdto2);
// dao.insertMember(mdto3);
// MemberDTO mdto4 = dao.selectMemberById("id-22");
// System.out.println(mdto4);
MemberDTO mdto5 = dao.selectMemberByRegisterNumber("545454", "5454545");
System.out.println("주민번호로 조회 결과 : "+mdto5);
ArrayList<MemberDTO> mdto6 = dao.selectAllMember();
System.out.println("--------------전체 회원 목록 조회------------------");
for(int i=0;i<mdto6.size();i++){
System.out.println(mdto6.get(i));
}
// System.out.println("---------------전체회원목록조회- map----------");
// Map map = dao.selectAllMemberMap();
// System.out.println(map);
// System.out.println("---------------전체회원목록조회- map(id가 id-77인 회원 찾기)----------");
// Object obj1 = map.get("id-77");
// System.out.println(obj1);
// Set set = map.keySet();
// Iterator itr = set.iterator();
// if(itr.next()){
//
// }
// System.out.println("---------------전체회원목록조회2- map----------");
// Map map2 = dao.selectAllMemberMap2();
// System.out.println(map2);
// System.out.println("---------------전체회원목록조회2- map(id가 id-77인 회원 이름찾기)----------");
// Object obj2 = map2.get("id-77");
// System.out.println(obj2);
System.out.println("---------------마일리지로 범위로 조회하기(List)----------");
List list = dao.selectMemberByMileageRange(0, 1000);
for(Object obj : list){
System.out.println(obj);
}
System.out.println("---------------마일리지로 범위로 조회하기(Map)----------");
Map map4 = dao.selectMemberByMileageRange2(0, 1000);
Set entryset = map4.entrySet();
//set을 이용한 key-value 뽑기
for(Object obj:entryset){
System.out.println(obj);
}
System.out.println("-----------ID로 회원 정보 수정하기(update)------------");
mdto3.setMileage(40000);
int cnt = dao.updateMemberId(mdto3);
System.out.println(cnt+"개의 행이 수정되었습니다.");
System.out.println(dao.selectMemberById("id-77"));
System.out.println("-----------ID로 회원 정보 삭제하기(delete)------------");
cnt = dao.deleteMemberById("id-66");
System.out.println(cnt+"개의 행이 삭제되었습니다.");
System.out.println(dao.selectMemberById("id-66"));
list = dao.selectAllMember();
for(Object obj:list){
System.out.println(obj);
}
}
}
* 결과
주민번호로 조회 결과 : MemberDTO [id=5454, password=5454, name=5454, registerNumber1=545454, registerNumber2=5454545, mileage=1000]
--------------전체 회원 목록 조회------------------
MemberDTO [id=2244, password=2244, name=홍, registerNumber1=224444, registerNumber2=4444444, mileage=0]
MemberDTO [id=1111, password=1111, name=1111, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
MemberDTO [id=2233, password=2233, name=í, registerNumber1=2233, registerNumber2=2233, mileage=1000]
MemberDTO [id=1717, password=1717, name=171717, registerNumber1=171717, registerNumber2=1717171, mileage=1000]
MemberDTO [id=5757, password=5757, name=5757, registerNumber1=575757, registerNumber2=5757575, mileage=1000]
MemberDTO [id=6868, password=6868, name=6868, registerNumber1=686868, registerNumber2=6868686, mileage=1000]
MemberDTO [id=7979, password=7979, name=7979, registerNumber1=797979, registerNumber2=7979797, mileage=1000]
MemberDTO [id=3333, password=3333, name=정형돈, registerNumber1=282882, registerNumber2=8327893, mileage=1000]
MemberDTO [id=5436, password=5436, name=5436, registerNumber1=543454, registerNumber2=3453534, mileage=1000]
MemberDTO [id=5454, password=5454, name=5454, registerNumber1=545454, registerNumber2=5454545, mileage=1000]
MemberDTO [id=6565, password=6565, name=홍길동, registerNumber1=111111, registerNumber2=1111122, mileage=1000]
MemberDTO [id=id-33, password=3333, name=원빈, registerNumber1=333333, registerNumber2=3333333, mileage=1000]
MemberDTO [id=2222, password=1111, name=홍길동, registerNumber1=111111, registerNumber2=2222222, mileage=1000]
MemberDTO [id=id-11, password=1111, name=이상운, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
MemberDTO [id=id-22, password=2222, name=장동건, registerNumber1=222222, registerNumber2=2222222, mileage=1000]
MemberDTO [id=id-44, password=1111, name=이상운, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
MemberDTO [id=id-77, password=3333, name=원빈, registerNumber1=333333, registerNumber2=3333333, mileage=40000]
---------------마일리지로 범위로 조회하기(List)----------
MemberDTO [id=1111, password=1111, name=1111, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
MemberDTO [id=2233, password=2233, name=í, registerNumber1=2233, registerNumber2=2233, mileage=1000]
MemberDTO [id=1717, password=1717, name=171717, registerNumber1=171717, registerNumber2=1717171, mileage=1000]
MemberDTO [id=5757, password=5757, name=5757, registerNumber1=575757, registerNumber2=5757575, mileage=1000]
MemberDTO [id=6868, password=6868, name=6868, registerNumber1=686868, registerNumber2=6868686, mileage=1000]
MemberDTO [id=7979, password=7979, name=7979, registerNumber1=797979, registerNumber2=7979797, mileage=1000]
MemberDTO [id=3333, password=3333, name=정형돈, registerNumber1=282882, registerNumber2=8327893, mileage=1000]
MemberDTO [id=5436, password=5436, name=5436, registerNumber1=543454, registerNumber2=3453534, mileage=1000]
MemberDTO [id=5454, password=5454, name=5454, registerNumber1=545454, registerNumber2=5454545, mileage=1000]
MemberDTO [id=6565, password=6565, name=홍길동, registerNumber1=111111, registerNumber2=1111122, mileage=1000]
MemberDTO [id=id-33, password=3333, name=원빈, registerNumber1=333333, registerNumber2=3333333, mileage=1000]
MemberDTO [id=2222, password=1111, name=홍길동, registerNumber1=111111, registerNumber2=2222222, mileage=1000]
MemberDTO [id=id-11, password=1111, name=이상운, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
MemberDTO [id=id-22, password=2222, name=장동건, registerNumber1=222222, registerNumber2=2222222, mileage=1000]
MemberDTO [id=id-44, password=1111, name=이상운, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
---------------마일리지로 범위로 조회하기(Map)----------
5436=5436
5757=5757
id-44=이상운
1111=1111
id-33=원빈
id-22=장동건
7979=7979
3333=정형돈
id-11=이상운
2233=í
1717=171717
2222=홍길동
6868=6868
6565=홍길동
5454=5454
-----------ID로 회원 정보 수정하기(update)------------
1개의 행이 수정되었습니다.
MemberDTO [id=id-77, password=3333, name=원빈, registerNumber1=333333, registerNumber2=3333333, mileage=40000]
-----------ID로 회원 정보 삭제하기(delete)------------
0개의 행이 삭제되었습니다.
null
MemberDTO [id=2244, password=2244, name=홍, registerNumber1=224444, registerNumber2=4444444, mileage=0]
MemberDTO [id=1111, password=1111, name=1111, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
MemberDTO [id=2233, password=2233, name=í, registerNumber1=2233, registerNumber2=2233, mileage=1000]
MemberDTO [id=1717, password=1717, name=171717, registerNumber1=171717, registerNumber2=1717171, mileage=1000]
MemberDTO [id=5757, password=5757, name=5757, registerNumber1=575757, registerNumber2=5757575, mileage=1000]
MemberDTO [id=6868, password=6868, name=6868, registerNumber1=686868, registerNumber2=6868686, mileage=1000]
MemberDTO [id=7979, password=7979, name=7979, registerNumber1=797979, registerNumber2=7979797, mileage=1000]
MemberDTO [id=3333, password=3333, name=정형돈, registerNumber1=282882, registerNumber2=8327893, mileage=1000]
MemberDTO [id=5436, password=5436, name=5436, registerNumber1=543454, registerNumber2=3453534, mileage=1000]
MemberDTO [id=5454, password=5454, name=5454, registerNumber1=545454, registerNumber2=5454545, mileage=1000]
MemberDTO [id=6565, password=6565, name=홍길동, registerNumber1=111111, registerNumber2=1111122, mileage=1000]
MemberDTO [id=id-33, password=3333, name=원빈, registerNumber1=333333, registerNumber2=3333333, mileage=1000]
MemberDTO [id=2222, password=1111, name=홍길동, registerNumber1=111111, registerNumber2=2222222, mileage=1000]
MemberDTO [id=id-11, password=1111, name=이상운, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
MemberDTO [id=id-22, password=2222, name=장동건, registerNumber1=222222, registerNumber2=2222222, mileage=1000]
MemberDTO [id=id-44, password=1111, name=이상운, registerNumber1=111111, registerNumber2=1111111, mileage=1000]
MemberDTO [id=id-77, password=3333, name=원빈, registerNumber1=333333, registerNumber2=3333333, mileage=40000]
'프로그래밍 > ibatis(Mybatis)' 카테고리의 다른 글
| Dynamic(start,end 사용하기) - 봉급, 이름, 부서로 조회하기 (0) | 2012.06.18 |
|---|---|
| ibatis join실습하기- ibatis_join, namespace (0) | 2012.06.18 |
| ibatis resultMap,typeAlias,insert,select (0) | 2012.06.15 |
| iBatis를 통한 SQL문 실행(ibatis의 javaBean, select, insert,Parameter매핑,Result 매핑) (0) | 2012.06.15 |
| iBATIS를 통한 SQL 실행 (0) | 2012.06.15 |
