iBATIS example (insert, update, delete)

iBATIS/insert, update, delete

iBATIS example (insert, update, delete)

Soul-Learner 2009. 12. 2. 18:59

iBATIS 2.3.4버전을 이용하여 테스트한 것이다. 자바애플리케이션 형태이므로 웹애플리케이션에 적용하려면 아주 약간만(한 두 라인 정도) 변경하면 웹 컨테이너에서도 실행될 것이다.

Employee.xml (SQL문장 등록)

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

<typeAlias alias="Employee" type="Employee"/>

<resultMap id="EmployeeResult" class="Employee">
<result property="empno" column="EMPNO"/>
<result property="ename" column="ENAME"/>
<result property="hiredate" column="HIREDATE"/>
</resultMap>

<select id="selectAllEmployee" resultMap="EmployeeResult">
select * from EMPLOYEE
</select>

<select id="selectEmployeeByEmpno" parameterClass="int" resultClass="Employee">
select
EMPNO as empno,
ENAME as ename,
HIREDATE as hiredate
from EMPLOYEE
where EMPNO = #empno#
</select>


<insert id="insertEmployee" parameterClass="Employee">
insert into Employee (
EMPNO,
      ENAME,
      HIREDATE
)values (
#empno#, #ename#, #hiredate#
)
</insert>

<update id="updateEmployee" parameterClass="Employee">
update EMPLOYEE set
EMPNO = #empno#,
ENAME = #ename#,
HIREDATE = #hiredate#
where
EMPNO = #empno#
</update>

<delete id="deleteEmployeeByEmpno" parameterClass="int">
delete from Employee where EMPNO = #empno#
</delete>

</sqlMap>

SqlMapConfig.xml(주설정 파일, 데이터베이스연결 정보 및 위의 파일포함 )

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

<transactionManager type="JDBC" commitRequired="false">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="oracle.jdbc.OracleDriver"/>
<property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@micropilot.co.kr:1521:ORCL"/>
<property name="JDBC.Username" value="scott"/>
<property name="JDBC.Password" value="tiger"/>
</dataSource>
</transactionManager>

<sqlMap resource="Employee.xml"/>

</sqlMapConfig>

Employee.java (레코드와 맵핑될 클래스)

//package test;
import java.sql.Date;

public class Employee {

private int empno;
private String ename;
private java.sql.Date hiredate;

public Employee() {}

public Employee(int empno, String ename, Date hiredate) {
super();
this.empno = empno;
this.ename = ename;
this.hiredate = hiredate;
}

public int getEmpno() {
return empno;
}

public void setEmpno(int empno) {
this.empno = empno;
}

public String getEname() {
return ename;
}

public void setEname(String ename) {
this.ename = ename;
}

public java.sql.Date getHiredate() {
return hiredate;
}

public void setHiredate(java.sql.Date hiredate) {
this.hiredate = hiredate;
}
}

SimpleExample.java (위에서 선언한 클래스와 설정파일을 사용하여 데이터베이스 입출력을 테스트함)
아래의 프로그램은 웹컨테이너에서 실행되는 것이 아니라 자바애플리케이션이므로 모든 소스와 설정파일을 한 디렉토리에 모아놓고 아래의 클래스를 실행하면 된다. 웹 애플리케이션으로 전환하는 것도 거의 소스는 변경이 필요 없고, 설정파일(SqlMapConfig.xml)을 읽어들이는 한 줄의 코드만 변경해 주면 될 것이다.

SimpleExample.java

//package test;

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

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

/**
* This is not a best practices class. It's just an example
* to give you an idea of how iBATIS works. For a more complete
* example, see JPetStore 5.0 at http://www.ibatis.com.
*/
public class SimpleExample {

/**
   * SqlMapClient instances are thread safe, so you only need one.
   * In this case, we'll use a static singleton. So sue me. ;-)
   */
private static SqlMapClient sqlMapper;

/**
   * It's not a good idea to put code that can fail in a class initializer,
   * but for sake of argument, here's how you configure an SQL Map.
   */
static {
try {
//Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
Reader reader = new java.io.FileReader("SqlMapConfig.xml");
sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
reader.close();
} catch (IOException e) {
// Fail fast.
throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);
}
}

public static void main(String[] args) throws Exception {
   System.out.println("selectAllEmployee()를 호출하여 리턴된 값--------------------------");
   List list = selectAllEmployee();
   for(int i=0;i<list.size();i++){
Employee emp = (Employee)list.get(i);
int empno = emp.getEmpno();
String ename = emp.getEname();
java.sql.Date hiredate = emp.getHiredate();
System.out.println("사번:"+empno+", 이름:"+ename+", 입사일:"+hiredate);
   }

   System.out.println("selectEmployeeByEmpno()를 호출하여 리턴된 값--------------------------");
   Employee e = selectEmployeeByEmpno(7369);
   System.out.println("사번:"+e.getEmpno()+", 이름:"+e.getEname()+", 입사일:"+e.getHiredate());

   System.out.println("insertEmployee()를 호출하여 '홍길동'을 입력함--------------------------");
   Employee emp = new Employee(100, "'홍길동'", new java.sql.Date(System.currentTimeMillis()));
   insertEmployee(emp);

System.out.println("updateEmployee()를 호출하여 레코드를 갱신함--------------------------");
emp.setEname("홍범도");
updateEmployee(emp);

System.out.println("deleteEmployee()를 호출하여 레코드를 삭제함--------------------------");
deleteEmployeeByEmpno(100);

}

public static List selectAllEmployee () throws SQLException {
return sqlMapper.queryForList("selectAllEmployee");
}

public static Employee selectEmployeeByEmpno(int id) throws SQLException {
return (Employee) sqlMapper.queryForObject("selectEmployeeByEmpno", id);
}

public static void insertEmployee(Employee emp) throws SQLException {
sqlMapper.insert("insertEmployee", emp);
}

public static void updateEmployee(Employee emp) throws SQLException {
sqlMapper.update("updateEmployee", emp);
}

public static void deleteEmployeeByEmpno(int id) throws SQLException {
sqlMapper.delete("deleteEmployeeByEmpno", id);
}
}