iBatis를 통한 SQL문 실행(ibatis의 javaBean, select, insert,Parameter매핑,Result 매핑)

<?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>
</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"/>

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

 

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.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 obj = map.get("id-77");
  System.out.println(obj);
//  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);
 }
}

 

* 결과