은성이의 프로그램X파일

import java.util.HashMap;
import java.util.List;

import hr.dao.EmployeeDAO;
import hr.dto.DepartmentDTO;
import hr.dto.EmployeeDTO;

public class TestEmployee {

 public static void main(String[] args)throws Exception {
  DepartmentDTO deptDTO = new DepartmentDTO();
  deptDTO.setDepartmentId("D0001");
  EmployeeDTO empDTO = new EmployeeDTO("00013", "김건모", "abc@abc.com", "과장", 403000, deptDTO);
  EmployeeDAO dao = EmployeeDAO.getInstance();
  
 // dao.insertEmployee(empDTO);
  empDTO.setSalary(5405050);
  empDTO.setJobPosition("부장");
  int cnt = dao.updateEmployee(empDTO);
  System.out.println("변경된 row 수 : "+cnt);
  cnt = dao.deleteEmployeeById("00013");
  System.out.println("삭제된 row 수 : "+cnt);
  System.out.println("-----전체 직원 부서 조회하기------");
  List list = dao.selectAllEmployee();
  for(Object obj:list){
   EmployeeDTO dto = (EmployeeDTO)obj;
   System.out.println(dto.getEmployeeName()+" - "+dto.getDepartmentDTO().getDepartmentName());
   }
  System.out.println("-----부서 조회하기-------");
  list = dao.selectAllDepartment();
  for(Object obj:list){
   System.out.println(obj);
  }
  System.out.println("------봉급으로 조회-------");
  HashMap map = new HashMap();
  //map.put("start_salary", 2000000);
  map.put("end_salary", 10000000);
  
  List list1 = dao.selectEmployeeBySalaryRange(map);
  for(Object obj:list1){
   System.out.println(obj);
  }
  System.out.println("-----이름과 부서이름으로 조회--------");
  EmployeeDTO edto1 = new EmployeeDTO();
//  edto1.setEmployeeName("장");
  edto1.setJobPosition("부");
  List list2 = dao.selectEmployeeByNameOrPosition(edto1);
  for(Object obj:list2){
   System.out.println(obj);
  }
  
  System.out.println("-----iterate사용해서 여러명의 이름 중 있는 것만 조회하기------");
  HashMap names =  new HashMap();
  String[] ns = {"한석규","길무성","김건모"};
  names.put("names", ns);
     list = dao.selectEmployeeByNames(names);
  System.out.println(list.size()+"명 조회");
  for(Object obj:list){
   System.out.println(obj);
  }
 }
}

 <?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 namespace="dept">
 <select id="selectAllDepartment" resultClass="hr.dto.DepartmentDTO">
  select department_id as departmentId,
     department_name as departmentName,
     location
  from department
 </select>
</sqlMap>

<?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 namespace="hr">
 <typeAlias alias="empDTO" type="hr.dto.EmployeeDTO"/>
 <typeAlias alias="deptDTO" type="hr.dto.DepartmentDTO"/>
 <!-- insert -->
 <insert id="insertEmployee" parameterClass="empDTO">
 <!--
  <selectKey resultClass="int" keyProperty="employeeId">
  select aaaa from dual
 </selectKey>
 -->
  insert into employee
  (employee_id,employee_name, email, job_position, salary, department_id)
  values(#employeeId#,#employeeName#,#email#,#jobPosition#,#salary#,#departmentDTO.departmentId#)
 </insert>
 <update id="updateEmployee" parameterClass="empDTO">
  update employee
  set   employee_name=#employeeName#,
      email=#email#,
      job_position=#jobPosition#,
      salary=#salary#,
      department_id=#departmentDTO.departmentId#
  where  employee_id=#employeeId#
 </update>
 <delete id="deleteEmployeeById">
  delete from employee
  where employee_id=#id#
 </delete>
 <resultMap class="empDTO" id="employeeResultMap">
  <result property="employeeId" columnIndex="1"/><!--<result property="employeeId" column="employee_id"/>이렇게 줘도 되고  -->
  <result property="employeeName" columnIndex="2"/>
  <result property="email" columnIndex="3"/>
  <result property="jobPosition" columnIndex="4"/>
  <result property="salary" columnIndex="5"/>
  <result property="departmentDTO.departmentId" columnIndex="6"/>
  <result property="departmentDTO.departmentName" columnIndex="7"/>
  <result property="departmentDTO.location" columnIndex="8"/>
 </resultMap>
 <select id="selectAllEmployee" resultMap="employeeResultMap">
  select emp.employee_id ,
     emp.employee_name,
     emp.email,
     emp.job_position,
     emp.salary,
     dept.department_id,
     dept.department_name,
     dept.location
  from   employee emp, department dept
  where emp.department_id = dept.department_id
 </select>
 <select id="selectEmployeeBySalaryRange" resultMap="employeeResultMap" parameterClass="hashmap">
    select emp.employee_id ,
     emp.employee_name,
     emp.email,
     emp.job_position,
     emp.salary,
     dept.department_id,
     dept.department_name,
     dept.location
  from   employee emp, department dept
  where emp.department_id = dept.department_id
  <!--
  and    emp.salary &gt; #start_salary#
  and    emp.salary &lt; #end_salary#
   -->
  
   <isNotEmpty property="start_salary" prepend="and">
    emp.salary &gt; #start_salary#
   </isNotEmpty>
   <isNotEmpty property="end_salary" prepend="and">
    emp.salary &lt; #end_salary#
   </isNotEmpty>
  
 </select>
 <select id="selectEmployeeByNameAndJobPosition" resultMap="employeeResultMap" parameterClass="empDTO">
  select emp.employee_id ,
     emp.employee_name,
     emp.email,
     emp.job_position,
     emp.salary,
     dept.department_id,
     dept.department_name,
     dept.location
  from   employee emp, department dept
  where emp.department_id = dept.department_id
  <isNotEmpty property="employeeName" prepend="and">
   emp.employee_name Like '%$employeeName$%'
  </isNotEmpty>
  <isNotEmpty property="jobPosition" prepend="and">
   emp.job_position Like '%'||#jobPosition#||'%'<!-- '%'||#jobPosition#||'%' -->
  </isNotEmpty>
 </select>
 <select id="selectEmployeeByNames" resultMap="employeeResultMap" parameterClass="hashmap">
 select emp.employee_id ,
     emp.employee_name,
     emp.email,
     emp.job_position,
     emp.salary,
     dept.department_id,
     dept.department_name,
     dept.location
  from   employee emp, department dept
  where emp.department_id = dept.department_id
 <iterate property="names" conjunction="OR" open="(" close=")" prepend="and" >
  employee_name = #names[]#
 </iterate>
 <!-- and(name='홍길동' OR name='이순신') -->
 </select>
</sqlMap>

<?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 -->
  <settings useStatementNamespaces="true"/><!-- namespace를 사용하겠다. true -->
  <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="hr/config/hr.xml"/>
<sqlMap resource="hr/config/dept.xml"/>
</sqlMapConfig>

package hr.dao;

import hr.dto.EmployeeDTO;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class EmployeeDAO {
 private SqlMapClient sqlMap;
 private static EmployeeDAO instance = new EmployeeDAO();
 private EmployeeDAO(){
  //SqlMapClient객체 생성
  try {
   Reader reader = Resources.getResourceAsReader("hr/config/SqlMapConfig.xml");
   sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
 public static EmployeeDAO getInstance(){
  return instance;
 }
 //insert처리 메소드
 public void insertEmployee(EmployeeDTO empDTO) throws SQLException{
  sqlMap.insert("hr.insertEmployee",empDTO);
 }
 //update처리 메소드
 public int updateEmployee(EmployeeDTO empDTO) throws SQLException {
  return sqlMap.update("hr.updateEmployee", empDTO);
 }
 //delete처리 메소드
 public int deleteEmployeeById(String employeeId)throws SQLException{
  return sqlMap.delete("hr.deleteEmployeeById", employeeId);
 }
 //select처리 메소드
 public List selectAllEmployee() throws SQLException{
  return sqlMap.queryForList("hr.selectAllEmployee");//namespace를 사용했기 때문에 hr이 붙는다.
 }
 public List selectAllDepartment() throws SQLException{
  return sqlMap.queryForList("dept.selectAllDepartment");//namespace를 쓸 경우에는 반드시 namespace를 써 넣어야 한다.
 }
 public List selectEmployeeBySalaryRange(HashMap map) throws SQLException{
  return sqlMap.queryForList("hr.selectEmployeeBySalaryRange", map);
 }
 public List selectEmployeeByNameOrPosition(EmployeeDTO edto)throws SQLException{
  return sqlMap.queryForList("hr.selectEmployeeByNameAndJobPosition", edto);
 }
 public List selectEmployeeByNames(HashMap names)throws SQLException{
  return sqlMap.queryForList("hr.selectEmployeeByNames", names);
 }
}

package hr.dto;

public class DepartmentDTO {

 private String departmentId;
 private String departmentName;
 private String location;
 
 public DepartmentDTO() {}

 public DepartmentDTO(String departmentId, String departmentName,
   String location) {
  this.departmentId = departmentId;
  this.departmentName = departmentName;
  this.location = location;
 }

 public String getDepartmentId() {
  return departmentId;
 }

 public void setDepartmentId(String departmentId) {
  this.departmentId = departmentId;
 }

 public String getDepartmentName() {
  return departmentName;
 }

 public void setDepartmentName(String departmentName) {
  this.departmentName = departmentName;
 }

 public String getLocation() {
  return location;
 }

 public void setLocation(String location) {
  this.location = location;
 }

 @Override
 public String toString() {
  return "DepartmentDTO [departmentId=" + departmentId
    + ", departmentName=" + departmentName + ", location="
    + location + "]";
 }
 
 
}

package hr.dto;

public class EmployeeDTO {
 private String employeeId;
 private String employeeName;
 private String email;
 private String jobPosition;
 private int salary;
 private DepartmentDTO departmentDTO;
 public EmployeeDTO() {}
 public EmployeeDTO(String employeeId, String employeeName, String email,
   String jobPosition, int salary, DepartmentDTO departmentDTO) {
  this.employeeId = employeeId;
  this.employeeName = employeeName;
  this.email = email;
  this.jobPosition = jobPosition;
  this.salary = salary;
  this.departmentDTO = departmentDTO;
 }
 public String getEmployeeId() {
  return employeeId;
 }
 public void setEmployeeId(String employeeId) {
  this.employeeId = employeeId;
 }
 public String getEmployeeName() {
  return employeeName;
 }
 public void setEmployeeName(String employeeName) {
  this.employeeName = employeeName;
 }
 public String getEmail() {
  return email;
 }
 public void setEmail(String email) {
  this.email = email;
 }
 public String getJobPosition() {
  return jobPosition;
 }
 public void setJobPosition(String jobPosition) {
  this.jobPosition = jobPosition;
 }
 public int getSalary() {
  return salary;
 }
 public void setSalary(int salary) {
  this.salary = salary;
 }
 public DepartmentDTO getDepartmentDTO() {
  return departmentDTO;
 }
 public void setDepartmentDTO(DepartmentDTO departmentDTO) {
  this.departmentDTO = departmentDTO;
 }
 @Override
 public String toString() {
  return "EmployeeDTO [employeeId=" + employeeId + ", employeeName="
    + employeeName + ", email=" + email + ", jobPosition="
    + jobPosition + ", salary=" + salary + ", departmentDTO="
    + departmentDTO + "]";
 }
 
 
}

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

</sqlMap>

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

<sqlMap resource="hr/config/hr.xml"/>
</sqlMapConfig>

import java.util.List;

import hr.dao.EmployeeDAO;
import hr.dto.DepartmentDTO;
import hr.dto.EmployeeDTO;

public class TestEmployee {

 public static void main(String[] args)throws Exception {
  DepartmentDTO deptDTO = new DepartmentDTO();
  deptDTO.setDepartmentId("D0001");
  EmployeeDTO empDTO = new EmployeeDTO("00013", "김건모", "abc@abc.com", "과장", 403000, deptDTO);
  EmployeeDAO dao = EmployeeDAO.getInstance();
  
 // dao.insertEmployee(empDTO);
  empDTO.setSalary(5405050);
  empDTO.setJobPosition("부장");
  int cnt = dao.updateEmployee(empDTO);
  System.out.println("변경된 row 수 : "+cnt);
  cnt = dao.deleteEmployeeById("00013");
  System.out.println("삭제된 row 수 : "+cnt);
  System.out.println("-----전체 직원 부서 조회하기------");
  List list = dao.selectAllEmployee();
  for(Object obj:list){
   EmployeeDTO dto = (EmployeeDTO)obj;
   System.out.println(dto.getEmployeeName()+" - "+dto.getDepartmentDTO().getDepartmentName());
   }
  System.out.println("-----부서 조회하기-------");
  list = dao.selectAllDepartment();
  for(Object obj:list){
   System.out.println(obj);
  }
  }
}


 

<?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 namespace="dept">
 <select id="selectAllDepartment" resultClass="hr.dto.DepartmentDTO">
  select department_id as departmentId,
     department_name as departmentName,
     location
  from department
 </select>
</sqlMap>

<!DOCTYPE sqlMap     
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"     
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="hr">
 <typeAlias alias="empDTO" type="hr.dto.EmployeeDTO"/>
 <typeAlias alias="deptDTO" type="hr.dto.DepartmentDTO"/>
 <!-- insert -->
 <insert id="insertEmployee" parameterClass="empDTO">
 <!--
  <selectKey resultClass="int" keyProperty="employeeId">
  select aaaa from dual
 </selectKey>
 -->
  insert into employee
  (employee_id,employee_name, email, job_position, salary, department_id)
  values(#employeeId#,#employeeName#,#email#,#jobPosition#,#salary#,#departmentDTO.departmentId#)
 </insert>
 <update id="updateEmployee" parameterClass="empDTO">
  update employee
  set   employee_name=#employeeName#,
      email=#email#,
      job_position=#jobPosition#,
      salary=#salary#,
      department_id=#departmentDTO.departmentId#
  where  employee_id=#employeeId#
 </update>
 <delete id="deleteEmployeeById">
  delete from employee
  where employee_id=#id#
 </delete>
 <resultMap class="empDTO" id="employeeResultMap">
  <result property="employeeId" columnIndex="1"/><!--<result property="employeeId" column="employee_id"/>이렇게 줘도 되고  -->
  <result property="employeeName" columnIndex="2"/>
  <result property="email" columnIndex="3"/>
  <result property="jobPosition" columnIndex="4"/>
  <result property="salary" columnIndex="5"/>
  <result property="departmentDTO.departmentId" columnIndex="6"/>
  <result property="departmentDTO.departmentName" columnIndex="7"/>
  <result property="departmentDTO.location" columnIndex="8"/>
 </resultMap>
 <select id="selectAllEmployee" resultMap="employeeResultMap">
  select emp.employee_id ,
     emp.employee_name,
     emp.email,
     emp.job_position,
     emp.salary,
     dept.department_id,
     dept.department_name,
     dept.location
  from   employee emp, department dept
  where emp.department_id = dept.department_id
 </select>
 
</sqlMap>

<?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 -->
  <settings useStatementNamespaces="true"/><!-- namespace를 사용하겠다. true -->
  <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="hr/config/hr.xml"/>
<sqlMap resource="hr/config/dept.xml"/>
</sqlMapConfig>

package hr.dao;

import hr.dto.EmployeeDTO;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.List;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class EmployeeDAO {
 private SqlMapClient sqlMap;
 private static EmployeeDAO instance = new EmployeeDAO();
 private EmployeeDAO(){
  //SqlMapClient객체 생성
  try {
   Reader reader = Resources.getResourceAsReader("hr/config/SqlMapConfig.xml");
   sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
 public static EmployeeDAO getInstance(){
  return instance;
 }
 //insert처리 메소드
 public void insertEmployee(EmployeeDTO empDTO) throws SQLException{
  sqlMap.insert("hr.insertEmployee",empDTO);
 }
 //update처리 메소드
 public int updateEmployee(EmployeeDTO empDTO) throws SQLException {
  return sqlMap.update("hr.updateEmployee", empDTO);
 }
 //delete처리 메소드
 public int deleteEmployeeById(String employeeId)throws SQLException{
  return sqlMap.delete("hr.deleteEmployeeById", employeeId);
 }
 //select처리 메소드
 public List selectAllEmployee() throws SQLException{
  return sqlMap.queryForList("hr.selectAllEmployee");//namespace를 사용했기 때문에 hr이 붙는다.
 }
 public List selectAllDepartment() throws SQLException{
  return sqlMap.queryForList("dept.selectAllDepartment");//namespace를 쓸 경우에는 반드시 namespace를 써 넣어야 한다.
 }
}

package hr.dto;

public class DepartmentDTO {

 private String departmentId;
 private String departmentName;
 private String location;
 
 public DepartmentDTO() {}

 public DepartmentDTO(String departmentId, String departmentName,
   String location) {
  this.departmentId = departmentId;
  this.departmentName = departmentName;
  this.location = location;
 }

 public String getDepartmentId() {
  return departmentId;
 }

 public void setDepartmentId(String departmentId) {
  this.departmentId = departmentId;
 }

 public String getDepartmentName() {
  return departmentName;
 }

 public void setDepartmentName(String departmentName) {
  this.departmentName = departmentName;
 }

 public String getLocation() {
  return location;
 }

 public void setLocation(String location) {
  this.location = location;
 }

 @Override
 public String toString() {
  return "DepartmentDTO [departmentId=" + departmentId
    + ", departmentName=" + departmentName + ", location="
    + location + "]";
 }
 
 
}

package hr.dto;

public class EmployeeDTO {
 private String employeeId;
 private String employeeName;
 private String email;
 private String jobPosition;
 private int salary;
 private DepartmentDTO departmentDTO;
 public EmployeeDTO() {}
 public EmployeeDTO(String employeeId, String employeeName, String email,
   String jobPosition, int salary, DepartmentDTO departmentDTO) {
  this.employeeId = employeeId;
  this.employeeName = employeeName;
  this.email = email;
  this.jobPosition = jobPosition;
  this.salary = salary;
  this.departmentDTO = departmentDTO;
 }
 public String getEmployeeId() {
  return employeeId;
 }
 public void setEmployeeId(String employeeId) {
  this.employeeId = employeeId;
 }
 public String getEmployeeName() {
  return employeeName;
 }
 public void setEmployeeName(String employeeName) {
  this.employeeName = employeeName;
 }
 public String getEmail() {
  return email;
 }
 public void setEmail(String email) {
  this.email = email;
 }
 public String getJobPosition() {
  return jobPosition;
 }
 public void setJobPosition(String jobPosition) {
  this.jobPosition = jobPosition;
 }
 public int getSalary() {
  return salary;
 }
 public void setSalary(int salary) {
  this.salary = salary;
 }
 public DepartmentDTO getDepartmentDTO() {
  return departmentDTO;
 }
 public void setDepartmentDTO(DepartmentDTO departmentDTO) {
  this.departmentDTO = departmentDTO;
 }
 @Override
 public String toString() {
  return "EmployeeDTO [employeeId=" + employeeId + ", employeeName="
    + employeeName + ", email=" + email + ", jobPosition="
    + jobPosition + ", salary=" + salary + ", departmentDTO="
    + departmentDTO + "]";
 }
 
 
}