iBatis - Generating iBatis artifcats using abator

Abator for Ibatis Tutorial

Introduction:


Abator is a code generator for iBATIS. Abator will introspect a database table (or many tables) and will generate iBATIS artifacts that can be used to access the table(s). This lessens the initial nuisance of setting up objects and configuration files to interact with database tables. Abator seeks to make a major impact on the large percentage of database operations that are simple CRUD (Create, Retrieve, Update, Delete). You will still need to hand code SQL and objects for join queries, or stored procedures.

This document shows a sample project which uses abator. I use MySql as the database and eclipse as the ide.

Before starting install the abator plugin from his update site http://ibatis.apache.org/tools/abator


Step 1: Create tables:

I created a database named testdb and created the tables below.

CREATE TABLE `testdb`.`User` (

`id` DOUBLE NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 40 ) NULL ,
`last_name` VARCHAR( 40 ) NULL ,
`email` VARCHAR( 40 ) NULL

) ENGINE = MYISAM ;

CREATE TABLE `testdb`.`accounts` (

`id` DOUBLE NOT NULL ,
`account_num` VARCHAR( 40 ) NOT NULL ,
`prod` VARCHAR( 10 ) NOT NULL ,
`entity` VARCHAR( 10 ) NOT NULL ,
PRIMARY KEY ( `account_num` , `prod` , `entity` )

) ENGINE = MYISAM ;

Step 2: Create Project and abator configuration file:

Create a sample client app project named “SampleIbatisProj”.
Right click on project -> new -> other -> Abator for iBatis Wizards -> Abator for iBatis configuration file
Select  “Abator for iBatis configuration file”. Click next and finish. Edit it to the following code.

abatorConfig.xml:

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE abatorConfiguration PUBLIC "-//Apache Software Foundation//DTD Abator for iBATIS Configuration 1.0//EN" "http://ibatis.apache.org/dtd/abator-config_1_0.dtd" >

<abatorConfiguration>

  <abatorContex>

    <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/testdb" suserId="root" password="" >

<classPathEntry location="D:\java_progs\iBatis\sampleibatisproj\lib\mysql-  connector-java-5.1.14-bin.jar" />

    </jdbcConnection>

    <javaModelGenerator targetPackage="com.sampleibatisproj.model" targetProject="sampleibatisproj" />

    <sqlMapGenerator targetPackage="com.sampleibatisproj.maps" targetProject="sampleibatisproj" />

    <daoGenerator targetPackage="com.sampleibatisproj.dao" targetProject="sampleibatisproj" type="GENERIC-CI" />

    <table schema="testdb" tableName="user" >

      <columnOverride column="first_name" property="firstName" />

      <columnOverride column="last_name" property="lastName" />

    </table>

    <table schema="testdb" tableName="accounts" >

      <columnOverride column="account_num" property="accountNum" />     

    </table>

  </abatorContext>

</abatorConfiguration>

Observe the entry

<table schema="testdb" tableName="user" >

      <columnOverride column="first_name" property="firstName" />

      <columnOverride column="last_name" property="lastName" />

</table>

We need to override the column name entry to suit the java naming convention. Otherwise abator will create  properties first_name and last_name in the model class.

Put mysql-connector-java-5.1.14-bin.jar in the lib. This is the mysql-jdbc driver. This is needed for ibatis to connect to db specified in the abatorConfig.xml file and generate the code.


Step 3: Create an external tool to run abator:

Create a build.xml file in the project root with the contents below.

Build.xml:

<?xml version="1.0" encoding="UTF-8"?>

<project default="runAbator">

 

<target name="runAbator">

  <eclipse.convertPath resourcepath="sampleibatisproj/abatorConfig.xml" property="thePath"/>

  <abator.generate configfile="${thePath}" ></abator.generate>

</target>

</project>

In eclipse go to Run-> external tools -> external tools configurations.

Create a new Ant build configuration. In the main tab, select your build.xml file in the build file part. Add your mysql-connector-java-5.1.14-bin.jar file in the classpath tab (if it is not already included in the build path). In the JRE tab, check 'Run in the same JRE as the workspace'.

This will set up the build.xml.

Run the build.xml by right clicking on build file ->run as -> ant build.

This would generate the model, dao and the sql maps. Add ibatis-2.3.4.726.jar to your build path to remove the compilation errors in dao classes.


Step 4 : Insert SqlMapConfig.xml

In com.sampleibatisproj.maps include this file

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

<sqlMapConfig>

   <settings useStatementNamespaces="true"/>

     <transactionManager type="JDBC">

        <dataSource type="SIMPLE">

          <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>

          <property name="JDBC.ConnectionURL"

               value="jdbc:mysql://127.0.0.1:3306/testdb"/>

          <property name="JDBC.Username" value="root"/>

          <property name="JDBC.Password" value=""/>

        </dataSource>

      </transactionManager>

     

     <sqlMap resource="com/sampleibatisproj/maps/user_SqlMap.xml"/>

     <sqlMap resource="com/sampleibatisproj/maps/accounts_SqlMap.xml"/>

     

</sqlMapConfig>

Include both the sql maps generated in the com.sampleibatisproj.maps package in the SqlMapConfig.xml file
    

<sqlMap resource="com/sampleibatisproj/maps/user_SqlMap.xml"/>

<sqlMap resource="com/sampleibatisproj/maps/accounts_SqlMap.xml"/>

Step 5 : Talk to db using SqlMapClient class:

import java.io.Reader;

import com.sampleibatisproj.dao.*;

import com.sampleibatisproj.model.*;

import com.ibatis.common.resources.Resources;

import com.ibatis.sqlmap.client.SqlMapClient;

import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class Main {

      public static void main(String[] args) {

        try

          {

Reader reader = Resources.getResourceAsReader("com/sampleibatisproj/maps/SqlMapConfig.xml");

SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

      UserDAO userDAO = new UserDAOImpl(sqlMap);

      User user = (User)userDAO.selectByPrimaryKey(new Double(2));

System.out.println(user.getEmail() + " " + user.getFirstName() + " " + user.getLastName());     

           }

        catch(Exception e){System.out.println(e.getMessage());}

      }

      public Main() {

            super();

      }

}


Use the above main class which uses SqlMapClient to talk to database.

All the methods in this class SqlMapClient that are inherited from SqlMapExecutor throw java.sql.SQLException which we have to handle every time that method is called. They are handled in UserDAOImpl and AccountsDAOImpl.

Reference:
http://ibatis.apache.org/docs/java/user/com/ibatis/sqlmap/client/SqlMapClient.html

Step 6: Talk to db using SqlMapDaoTemplate:

A DaoTemplate for SQL Map implementations that provides a convenient method to access the SqlMapExecutor. This class also provides SqlMapExecutor method wrappers that conveniently wrap SQLExceptions with DAO Exceptions. DAO Exceptions are unchecked exeptions where SQLExceptions are checked.

Reference: http://ibatis.apache.org/docs/java/user/com/ibatis/dao/client/template/SqlMapDaoTemplate.html

DaoManager Class : http://ibatis.apache.org/docs/java/user/com/ibatis/dao/client/DaoManager.html
Include ibatis-dao-2.jar in build path.

Method1:

One way of using SqlMapDaoTemplate is to generate all the classes using again using the abatorConfig.xml. In abatorconfig.xml change the type in the line below to ibatis from GENERIC-CI

<daoGenerator targetPackage="com.sampleibatisproj.dao" targetProject="sampleibatisproj" type="GENERIC-CI" />

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE abatorConfiguration PUBLIC "-//Apache Software Foundation//DTD Abator for iBATIS Configuration 1.0//EN" "http://ibatis.apache.org/dtd/abator-config_1_0.dtd" >

<abatorConfiguration>

  <abatorContex>

    <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/testdb" suserId="root" password="" >

<classPathEntry location="D:\java_progs\iBatis\sampleibatisproj\lib\mysql-  connector-java-5.1.14-bin.jar" />

    </jdbcConnection>

    <javaModelGenerator targetPackage="com.sampleibatisproj.model" targetProject="sampleibatisproj" />

    <sqlMapGenerator targetPackage="com.sampleibatisproj.maps" targetProject="sampleibatisproj" />

    <daoGenerator targetPackage="com.sampleibatisproj.dao" targetProject="sampleibatisproj" type="GENERIC-CI" />

    <table schema="testdb" tableName="user" >

      <columnOverride column="first_name" property="firstName" />

      <columnOverride column="last_name" property="lastName" />

    </table>

    <table schema="testdb" tableName="accounts" >

      <columnOverride column="account_num" property="accountNum" />     

    </table>

  </abatorContext>

</abatorConfiguration>

The main class is provided below.

Method 2:

Refactor the DAO classes generated when type="GENERIC-CI"

<daoGenerator targetPackage="com.sampleibatisproj.dao" targetProject="sampleibatisproj" type="GENERIC-CI" />

Change the DAO implementation classes to extend sqlMapdaoTemplate

Add these 2 import statements

import com.ibatis.dao.client.DaoManager;

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

public class AccountsDAOImpl extends SqlMapDaoTemplate implements AccountsDAO

public class UserDAOImpl extends SqlMapDaoTemplate implements UserDAO

In all the impl classes delete this line private SqlMapClient sqlMapClient;  Also delete the constructors that take SqlMapClient as param. Include the constructor that takes DaoManager as param.

Now all the insert, update and other methods from SqlMapExecutor (called from SqlMapClient object) can now be called directly.
Ex:

int rows = sqlMapClient.update("accounts.abatorgenerated_updateByPrimaryKeySelective", record);

this can be made

int rows = update("accounts.abatorgenerated_updateByPrimaryKeySelective", record);

public UserDAOImpl(DaoManager daoManager){

            super(daoManager);

      }

public AccountsDAOImpl(DaoManager daoManager){

            super(daoManager);

      }

Do this in all the methods in all the impl classes.
Now all the methods need not throw or handle the SqlException and BatchException. This makes the code look cleaner.

Example:

public void insert(Accounts record) throws SQLException {

        insert("accounts.abatorgenerated_insert", record);

    }

Is changed to

public void insert(Accounts record)   {

        insert("accounts.abatorgenerated_insert", record);

    }

So now when insert method is called from main class we don’t have to handle the SqlException. This is one of the main advantages of using SqlMapDaoTemplate class.

Now there are some methods in SqlMapExecutor that abator didn’t stub out. We have to manaully stub them out.

Right click on impl class ->Source->Override/implement methods. Do this for all impl classes.
Include the following dao.xml in com.sampleibatisproj.maps package

dao.xml:

<?xml version="1.0" encoding="UTF-8"?>

  <!DOCTYPE daoConfig

   PUBLIC "-//ibatis.apache.org//DTD DAO Configuration 2.0//EN"

   "http://ibatis.apache.org/dtd/dao-2.dtd">

  <daoConfig>

    <context>

      <transactionManager type="SQLMAP">

        <property name="SqlMapConfigResource"

                  value="com/sampleibatisproj/maps/SqlMapConfig.xml"/>

      </transactionManager>

      <!-- DAO interfaces and implementations

              should be listed here -->

      <dao interface="com.sampleibatisproj.dao.AccountsDAO"

           implementation="com.sampleibatisproj.dao.AccountsDAOImpl" />

     

      <dao interface="com.sampleibatisproj.dao.UserDAO"

           implementation="com.sampleibatisproj.dao.UserDAOImpl" />

    </context>

  </daoConfig>

Observe these configurations below

<dao interface="com.sampleibatisproj.dao.AccountsDAO"

           implementation="com.sampleibatisproj.dao.AccountsDAOImpl" />

     

<dao interface="com.sampleibatisproj.dao.UserDAO"

           implementation="com.sampleibatisproj.dao.UserDAOImpl" />

Change the main class to

Main.java:

import java.io.Reader;

import com.sampleibatisproj.dao.*;

import com.sampleibatisproj.model.*;

import com.ibatis.common.resources.Resources;

import com.ibatis.dao.client.DaoManager;

import com.ibatis.dao.client.DaoManagerBuilder;

public class Main {

      public static void main(String[] args) {

           

            try

            {

                  Reader reader = Resources.getResourceAsReader("com/sampleibatisproj/maps/dao.xml");

                  DaoManager daoManager = DaoManagerBuilder.buildDaoManager(reader);

                

              UserDAO userDAO = (UserDAO)daoManager.getDao(UserDAO.class);

                User user = (User)userDAO.selectByPrimaryKey(new Double(2));

                System.out.println(user.getEmail() + " " + user.getFirstName() + " " + user.getLastName());

            }

            catch(Exception e){System.out.println(e.getMessage());}

           

      }

     

      public Main() {

            super();

      }

}

Observe all the statements in the Main class.

If you run this class now you will get java.lang.NoClassDefFoundError: com/ibatis/common/exception/NestedRuntimeException.

So add ibatis-db.jar to remove this error. NestedRuntimeException class is in this jar.

Step 7 : Using Example classes:
Look at example class usage notes at the end of this document.

INSERT INTO `testdb`.`user` (

`id` ,
`first_name` ,
`last_name` ,
`email`

)
VALUES

(4 , 'Robin', 'Scherbatsky', 'Robin@gmail.com'),

(3 , 'Lily', 'Ericksson', 'ted@gmail.com'),

(2 , 'Barney', 'Stinson', 'ted@gmail.com'),

(1 , 'Ted', 'Mosby', 'ted@gmail.com');

I want to get the rows for which id > 1 and email !=’ted@gmail.com’. Here is the main program for that.

UserExample userExample = new UserExample();

                userExample.createCriteria().andEmailNotEqualTo("ted@gmail.com").andIdGreaterThan(new Double(1));

Main.java

import java.io.Reader;

import java.util.List;

import com.sampleibatisproj.dao.*;

import com.sampleibatisproj.model.*;

import com.ibatis.common.resources.Resources;

import com.ibatis.dao.client.DaoManager;

import com.ibatis.dao.client.DaoManagerBuilder;

public class Main {

      public static void main(String[] args) {

        try

            {

Reader reader = Resources.getResourceAsReader("com/sampleibatisproj/maps/dao.xml");

DaoManager daoManager = DaoManagerBuilder.buildDaoManager(reader);             

UserDAO userDAO = (UserDAO)daoManager.getDao(UserDAO.class);

UserExample userExample = new UserExample();

userExample.createCriteria().andEmailNotEqualTo("ted@gmail.com").andIdGreaterThan(new Double(1));

List<User> users = (List<User>)userDAO.selectByExample(userExample);

for(User user: users){

System.out.println(user.getEmail() + " " + user.getFirstName() + " " + user.getLastName());

     }

           

  } catch(Exception e){System.out.println(e.getMessage());}

           

}

 public Main() {

   super();

 }

}

What has Abator bought us in this case?

Several things:
•    Reuse - it is likely that some tables will need to be accessed from multiple different DAOs or service methods. Creating a DAO for each table promotes reuse and consistency within the application.
•    Database abstraction - a service layer typically defines persistence in your application. Those methods can be stabilized fairly quickly. As database design evolves:
1.    Abator can quickly regenerate the DAOs as the tables change
2.    The service methods can be modified as necessary
3.    Higher layers in the application remain unchanged
•    Developer productivity - generating table based DAOs is quick and repeatable and error free. Developers can concentrate on Object persistence, and on complex join queries if needed.
•    Fewer defects - because the most tedious and error prone part of any application (getting the SQL to match the objects) is automated.
Reference: http://ibatis.apache.org/docs/tools/abator/philosophy.html

Example Class Usage Notes:
With these generator sets the example class can be used to generate a virtually unlimited where clauses. These generator sets require that you are using iBATIS version 2.2.0 or higher.
The example classes created with these generator sets contain an inner static class called Criteria that holds a list of conditions that will be anded together in the where clause. The outer class holds a list of Criteria objects and all the clauses from the inner classes will be ored together. Using different sets of Criteria classes allows you to generate virtually unlimited types of where clauses.
Criteria objects must be created with the createCriteria method in the example class. When the first Criteria object is created it is automatically added to the list of Criteria objects - this makes it easy to write a simple where clause if you don't need to or several other clauses together.
This example shows how to generate a simple WHERE clause using the generated example class:
  TestTableExample example = new TestTableExample();

  example.createCriteria().andField1EqualTo(5);
In the above example, the dynamically generated where clause will effectively be:
  where field1 = 5
The next example shows how to generate a complex WHERE clause using the generated example class (using JSE 5.0 parameterized types):
  TestTableExample example = new TestTableExample();

  example.createCriteria()
    .andField1EqualTo(5)
    .andField2IsNull();

  example.or(example.createCriteria()
    .andField3NotEqualTo(9)
    .andField4IsNotNull());

  List<Integer> field5Values = new ArrayList<Integer>();
  field5Values.add(8);
  field5Values.add(11);
  field5Values.add(14);
  field5Values.add(22);

  example.or(example.createCriteria()
    .andField5In(field5Values));

  example.or(example.createCriteria()
    .andField6Between(3, 7));

In the above example, the dynamically generated where clause will effectively be:
  where (field1 = 5 and field2 is null)
     or (field3 <> 9 and field4 is not null)
     or (field5 in (8, 11, 14, 22))
     or (field6 between 3 and 7)
Returned records will meet these criteria.
The Criteria inner class includes andXXX methods for each field, and each possible SQL predicate including:
•    IS NULL - meaning the related column must be NULL
•    IS NOT NULL - meaning the related column must not be NULL
•    = (equal) - meaning the related column must be equal to the value passed in on the method call
•    <> (not equal) - meaning the related column must not be equal to the value passed in on the method call
•    > (greater than) - meaning the related column must be greater than the value passed in on the method call
•    >= (greater than or equal) - meaning the related column must be greater than or equal to the value passed in on the method call
•    < (less than) - meaning the related column must be less than the value passed in on the method call
•    <= (less than or equal) - meaning the related column must be less than or equal to the value passed in on the method call
•    LIKE - meaning the related column must be "like" the value passed in on the method call. Abator does not add the required '%', you must set that value yourself in the value you pass in on the method call.
•    NOT LIKE - meaning the related column must be "not like" the value passed in on the method call. Abator does not add the required '%', you must set that value yourself in the value you pass in on the method call.
•    BETWEEN - meaning the related column must be "between" the two values passed in on the method call.
•    NOT BETWEEN - meaning the related column must be "not between" the two values passed in on the method call.
•    IN - meaning the related column must be one of the list of values passed in on the method call.
•    NOT IN - meaning the related column must not be one of the list of values passed in on the method call.


Reference: http://ibatis.apache.org/docs/tools/abator/