iBatis

iBatis

iBatis data mapping tool assists Java Developers with data access. It is a light weight data mapping JDBC framework which abstracts low level details involved in database communication and helps generating a database persistence  layer for your Java application ( which involves mapping of objects to SQL calls, data type conversion, support for static queries as well as dynamic queries).

Why iBatis

Now the question comes,

can we use both technologies together?

The answer is YES, we can.

Working of iBatis Frame work

A set of XML encoded SQL Map files–one for each database table–holds SQL templates that are executed as prepared statements and map the expected results to Java domain classes. From application code, a layer of iBatis Data Access Objects (DAO) acts as the API that executes the SQL Map templates and assigns the results to the corresponding Java domain classes. In other words we can say that iBatis use XML to loosely couple Java objects and SQL commands

Layers of iBatis

The iBatis Layer consists of the following two libraries:

•iBatis Data Access Objects

•iBatis Data Mapper

  iBatis Data Mapper: This library makes iBatis special. It allows developers to flexibly map pure SQL to Java collections or custom domain objects. In doing so, development responsibilities are cleanly divided. DBAs can work with pure SQL devoid of language-specific constructs, and business developers can write pure OO business logic without regard for database semantics

  iBatis Data Access objects: Commonly known as DAO is used to abstract the communication with Persistence Layer. But this layer is so flexible it can be switched to Hibernate, JDO etc.

Installation

DownLoad:

To install iBatis for use with Java, download "iBatis Java {version} Binaries, Source, and documentation" from here and unzip it. This will result in a directory name "ibatis-2".

    • The doc directory contains a PDF version of the Developer Guide and zipped javadoc HTML files.

    • The lib directory contains the only JAR file needed, ibatis-{version}.jar. 

ClassPath:

  To compile and run code that uses iBatis, the following JARs must be in the classpath.

• ibatis-{version}.jar - in iBatis lib directory

• JAR containing the JDBC driver to be used

Sample

Our Database is having a table Employee with Employee Id, Employee name and Employee Department  as columns and a table Department with columns Department Id and Department Name.

We will be selecting data from tables based on employee name or Department Name, updating table records for employee department and deleting records from the database tables.

We will be interacting with DB using iBatis with SQL queries written in SQL Maps as well as written in Stored Procedures.

Tables

Create table Employee and Department as mentioned below:

Create table Employee

(

EmpId                 number     not null auto_increment,

EmpName           varchar2   not null,

deptId  number,

primary key (EmpId)

);

Create table Department

(

DeptId                   number    not null auto_ increment,

DeptName              varchar2  not null,

primary key (DeptId)

    );

Stored Procedure

/* * Package specification */

CREATE OR REPLACE PACKAGE Employee

AS

TYPE refCursor REF CURSOR; //to get the output list. 

PROCEDURE addEmployee (name IN VARCHAR2, dept IN VARCHAR2,

  retSts OUT VARCHAR2); 

PROCEDURE selAllEmp(EmpList out refCursor); 

PROCEDURE selEmpbyId (id IN NUMBER, EmpName OUT VARCHAR2); 

PROCEDURE selEmpbyDept (DeptName IN VARCHAR2, EmpList out refCursor); 

PROCEDURE delEmployee (EmpName IN, retSts OUT VARCHAR2);  

END Employee

/* * Package specification */

CREATE OR REPLACE PACKAGE Department

AS

TYPE refCursor REF CURSOR; //to get the output list. 

PROCEDURE addDept (name IN VARCHAR2, retSts OUT VARCHAR2); 

PROCEDURE selAllDept(DeptList out refCursor); 

PROCEDURE selDeptforEmp (EmpName IN VARCHAR2, DeptName OUT VARCHAR2); 

PROCEDURE delDept (DeptName IN, retSts OUT VARCHAR2);  

END Employee;

Note: you need complete the body part of the Procedure, here is just outline is given for your reference

iBatis Configuration

Configuration details for the iBatis are mentioned in the SqlMapConfig.xml file.

  Before explaining the configuration file I will like to mention the following: 

Ø Database will be accessed using JDBC.

Ø Namespace prefixes will be used when referring to "mapped statements" in our Java code.

Ø All the mappings are done through the files Employee_SqlMap.xml and Department_SqlMap.xml.

Ø We will use SqlMapConfig.properties file to write all the database properties so that configuration file can be used independent of database.

Properties File

driver = com.oracle.jdbc.driver.OracleDriver

url = jdbc:oracle:thin:@localhost:8082:Employee

username = username

password = password

Configuration File

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

   <properties resource="com/college/config/SqlMapConfig.properties"/>  

  <settings useStatementNamespaces="true"/>  

  <transactionManager type="JDBC">   

  <dataSource type="SIMPLE">     

  <property name="JDBC.Driver" value="${driver}"/>     

  <property name="JDBC.ConnectionURL" value="${url}"/>     

  <property name="JDBC.Username" value="${username}"/>     

  <property name="JDBC.Password" value="${password}"/>   

  </dataSource> 

  </transactionManager>   # any xml file created for mapping needs to be added here   

  <sqlMap resource=“employee_SqlMap.xml"/> 

  <sqlMap resource=“department_SqlMap.xml"/> 

  </sqlMapConfig>

SQL Maps

Mapped statements give ids to SQL statements. These ids are referenced in the code that invokes those using methods in the org.ibatis.sqlmap.client.SqlMapClient interface such as queryForObject and queryForList. Because we specified that we wanted to "useStatementNamespaces"  in our configuration file, our references to statements will have the syntax "{namespace}.{statement-id}".

Mapped statements are defined in XML files that are referenced from the SqlMapConfig.xml file  To use “statementNamespaces”

  All the SQL Maps mentioned in Configuration file will be compiled into one file after code compiles.

Employee_SqlMap.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 namespace=“emp">

  <parameterMap id=“emp.delEmpParams" class="map">

       <parameter property=“empName" jdbcType="VARCHAR" mode="IN"/>

       <parameter property="retSts" jdbcType="VARCHAR” mode="OUT"/>

  </parameterMap> 

  <procedure id=“emp.delemp"             

          parameterMap=“emp.delEmpParams">    

         { call Employee.delEmp(?,?) }

  </procedure> 

  <parameterMap id=“emp.addEmpParams" class="map">   

          <parameter property=“empName" jdbcType="VARCHAR" mode="IN"/>   

          <parameter property="retSts" jdbcType="VARCHAR" mode="OUT"/>

  </parameterMap> 

  <procedure id=“emp.addEmp"      

           parameterMap=“emp.addEmpParams">   

          {call Employee.addEmployee(?,?) }

      </procedure> 

<resultMap id=“emp.selEmpResults" class="com.bean.employee">   

      <result property="id" column="id"/>   

      <result property="name" column="name"/>

</resultMap> 

<parameterMap id=“emp.selAllEmpParams" class="map">   

      <parameter property=“empList" jdbcType="ORACLECURSOR" mode="OUT"/>

</parameterMap> 

<procedure id=“emp.selAllEmp"            

      parameterMap=“emp.selAllEmpParams“

      resultMap=“emp.selEmpResults">   

     {call Employee.selAllEmp(?) }

</procedure>

<parameterMap id=“emp.selEmpByDeptParams" class="map">   

     <parameter property=“DeptName" jdbcType="INTEGER" mode="IN"/>   

     <parameter property=“empList" jdbcType="ORACLECURSOR" mode="OUT"/>

</parameterMap> 

<procedure id=“emp.selEmpByDept"

    parameterMap=" emp.selEmpByDeptParams"      

    resultMap=" emp.selEmpResults"> 

    {call Employee.selEmpByDept(?,?) }

</procedure>

</sqlMap>

Department_SqlMap.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 namespace=“dept"> 

      <resultMap id=“dept.selDeptResults" class="com.bean.department">   

     <result property="id" column="id"/>   

     <result property="name" column="name"/>

  </resultMap> 

     <parameterMap id=“dept.selEmpByDeptParams" class="map">   

        <parameter property=“EmptName" jdbcType=“VARCHAR2" mode="IN"/>   

   <parameter property=“deptName" jdbcType=“VARCHAR2" mode="OUT"/>

    </parameterMap> 

   <procedure id=“dept.selDeptforEmp"

       parameterMap="dept.selEmpByDeptParams"      

       resultMap=" dept.selDeptResults "> 

      {call Employee.selEmpByDept(?,?) }

   </procedure>

</sqlMap>

Call to SqlMaps

•import com.ibatis.dao.client.DaoManager;

•import com.ibatis.dao.client.template.SqlMapDaoTemplate;

  private void delEmp(EmpName)  throws SQLException {

      HashMap params = new HashMap();

      params.add(“EmpName”, EmpName);     

      queryForObject(“emp.delEmp”,params);   

      String returnStatus = new String("");   

          returnStatus = (String)params.get("retSts"); 

  }

  private void addEmployee(String empName, String deptName)  throws SQLException {   

    HashMap params = new HashMap();   

    Params.put(“name”, empName);   

    Params.put(“Emp_id”, deptName);   

    queryForObject(“emp.addEmp”,params);   

    String returnStatus = new String("");   

    returnStatus = (String)params.get("retSts");

  }

  private List getEmp() throws SQLException {   

    HashMap params = new HashMap();   

    List EmpList = queryForList(“emp.selAllEmp”,params);   

    return EmpList; 

  }

.....Will be Continued