IT-eye

Here’s the first part in the Using iBatis with Oracle series. In this post i’ll show you how to setup iBatis and how to execute a simple select statement.

I’ll be using the following table in all my examples:

create table employees (
  id number primary key
, name varchar2(100) not null
, date_of_birth date
);

Configuration

First thing we need to do is create a configuration file which specifies how to connect to the database. Ofcourse, if you’re running this on an application server, you probably want to specify a preconfigured datasource. You’ll also need to specify how you want to handle transactions. Finally this configuration file contains one ore more sqlMap entries, which link to the configuration file containing the sql statements.

< ?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>
  <transactionmanager type="JDBC" commitRequired="false">
    <datasource type="SIMPLE">
      <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver"/>
      <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@xesoa10g:1521:xe"/>
      <property name="JDBC.Username" value="test"/>
      <property name="JDBC.Password" value="test"/>
    </datasource>
  </transactionmanager>
  <sqlmap resource="nl/iteye/ibatis/example1/Employee.xml"/>
</sqlmapconfig>

The first thing you need to do in your java application is to load the configuration as specified above:

Reader reader =
  Resources.getResourceAsReader("nl/iteye/ibatis/example1/Config.xml");
SqlMapClient sqlMapper =
  SqlMapClientBuilder.buildSqlMapClient(reader);
reader.close();

Regarding libraries, you need the oracle jdbc jars, the iBatis jar, and the log4j jar.

Select statement

I want to select all records in the employees table as defined above, and get every record in the following Employee pojo:

package nl.iteye.ibatis.example1;

public class Employee {
    private Long id;
    private String name;
    private Date dateOfBirth;

    /* getters and setters */
}

Here’s the sqlMap configuration file, which i linked to from the main configuration file. First i defined a resultMap, which specified how to map queried columns to properties in my Employee pojo. Next i defined a select statement. You need the id, to refer to it from you java code. You also need to specify a resultMap so that iBatis can map the queried columns to your pojo.

< ?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="Employee">
  <resultmap id="EmployeeResult" class="nl.iteye.ibatis.example1.Employee">
    <result property="id" jdbcType="NUMBER" javaType="java.lang.Long"
            column="ID"/>
    <result property="name" jdbcType="VARCHAR2" javaType="java.lang.String"
            column="NAME"/>
    <result property="dateOfBirth" jdbcType="DATE" javaType="java.util.Date"
            column="DATE_OF_BIRTH"/>
  </resultmap>
  <!--
    ============================================================================
    -->
  <select id="selectAllEmployees" resultMap="EmployeeResult">
    < ![CDATA[
    select id
    ,      name
    ,      date_of_birth
    from   employees
    order  by id asc
    ]]>
  </select>
</sqlmap>

Here’s how you can execute the select statement in your java program:

List<employee> emps =
  (List</employee><employee>)sqlMapper.queryForList("selectAllEmployees");

In the next post i’ll show you some simple deletes and inserts using stored procedures.