ibatis join실습하기- ibatis_join, namespace

 

DepartmentDTO.java

EmployeeDTO.java

sql_ins.sql

java project : ibatis_join

package : hr.dto
package : hr.config

DTO*2를 hr.dto에 카피

기존에 ibatis_basic에서 사용했던 member.xml->hr.xml로 변경. SqlMapConfig.xml를 config 패키지로 복사해와서

SqlMapConfig.xml
member.xml 파일을 hr.config에 카피
member.xml -> hr.xml

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

 

class : hr.dao.EmployeeDAO

class : TestEmployee

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>

 

 

<?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>
 
</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 + "]";
 }
 
 
}

 

* 결과

변경된 row 수 : 0
삭제된 row 수 : 0
-----전체 직원 부서 조회하기------
김건모 - 개발부
김건모 - 개발부
김건모 - 개발부
김건모 - 개발부
최순이 - 영업1부
이영수 - 영업1부
한석규 - 영업1부
신세경 - 영업2부
장혁 - 영업2부
이은미 - 영업2부
윤도현 - 영업2부
조관우 - 영업2부
DepartmentDTO [departmentId=D0001, departmentName=개발부, location=서울지점]
DepartmentDTO [departmentId=D0002, departmentName=영업1부, location=서울지점]
DepartmentDTO [departmentId=D0003, departmentName=영업2부, location=부산지점]