How to perform CRUD operations in iBatis ?

To perform any CRUD ( Create, Write, Update and Delete) operation using iBATIS, you would need to create a POJOs class corresponding to the table. This class describes the objects that will "model" database table rows.

EMPLOYEE table in MySQL

CREATE TABLE EMPLOYEE (

  id INT NOT NULL auto_increment,

  first_name VARCHAR(20) default NULL,

  last_name VARCHAR(20) default NULL,

  salary INT default NULL, PRIMARY KEY (id)

);

Employee POJO Class

We would create Employee class in Employee.java file as follows:

public class Employee {

  private int id;

  private String first_name;

  private String last_name;

  private int salary;

  /* Define constructors for the Employee class. */

  public Employee() {}

  public Employee(String fname, String lname, int salary) {

    this.first_name = fname;

    this.last_name = lname;

    this.salary = salary; }

  }

Employee.xml File

To define SQL mapping statement using iBATIS, we would use insert tag and inside this tag definition we would define an "id" which will be used in IbatisInsert.java file for executing SQL INSERT query on database.

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

  <insert id="insert" parameterClass="Employee">

    insert into EMPLOYEE(first_name, last_name, salary) values (#first_name#, #last_name#, #salary#)

    <selectKey resultClass = "int" keyProperty="id">

        select last_insert_id() as id

     </selectKey>

  </insert>

  <select id="getAll" resultClass="Employee">

       SELECT * FROM EMPLOYEE

  </select>

  <update id="update" parameterClass="Employee">

      UPDATE EMPLOYEE SET first_name = #first_name# WHERE id = #id#

   </update>

   <delete id="delete" parameterClass="int">

      DELETE FROM EMPLOYEE WHERE id = #id#

   </delete>

</sqlMap>

Here, parameterClass could take a value as string, int, float, double or any class object based on requirement.

In this example we would pass Employee object as a parameter while calling insert method of SqlMap class.

If your database table uses an IDENTITY, AUTO_INCREMENT, or SERIAL column or you have defined a SEQUENCE/GENERATOR, you can use the <selectKey> element in an <insert> statement to use or return that database-generated value.

READ OPERATION

Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");

SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

/* This would read all records from the Employee table. */

System.out.println("Going to read records.....");

List <Employee> ems = (List<Employee>)smc.queryForList("Employee.getAll", null);

Employee em = null;

for (Employee e : ems) {

  System.out.print(" " + e.getId() + " " + e.getFirstName() + " " + e.getLastName() + " " + e.getSalary());

  em = e;

  System.out.println();

}

System.out.println("Records Read Successfully ");

INSERT OPERATION

Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");

SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

/* This would insert one record in Employee table. */

System.out.println("Going to insert record.....");

Employee em = new Employee("Zara", "Ali", 5000);

smc.insert("Employee.insert", em);

System.out.println("Record Inserted Successfully ");

/* Logic to read all records, in order to check record insertion */

DELETE OPERATION

Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");

SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

/* This would delete one record in Employee table. */

System.out.println("Going to delete record.....");

int id = 1;

smc.delete("Employee.delete", id );

System.out.println("Record deleted Successfully ");

/* Logic to read all records, in order to check record deletion */

UPDATE OPERATION

Reader rd = Resources.getResourceAsReader("SqlMapConfig.xml");

SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

/* This would update one record in Employee table. */

System.out.println("Going to update record.....");

Employee rec = new Employee();

rec.setId(1);

rec.setFirstName( "Roma");

smc.update("Employee.update", rec );

System.out.println("Record updated Successfully ");

/* Logic to read all records, in order to check record update */