Tutorial on Ibatis-Using Eclipse Ibator plugin to generate Persistence POJO Models, SqlMap and DAO :: with coverage of Dynamic SQL and working downloadable example

IBatis is a lightweight persistence framework that maps objects to SQL statements. The SQL is decoupled from the application logic by storing in XML descriptor file . SQL mapping is done by data mapper to persist objects to a relational database. 
In this article i am going to discuss on how to use ibator plugin in eclipse to generate Java model objects from your database, the DAO interface and its auto implementation to access your db and auto generated SQL map xml files. And also we will deal with fun of dynamic SQL.  The Link for downloading the working eclipse project is given at the end of article. For more detail about ibatis visit the references at last of this article.


Part A: Ibator/DB Configuration

Step 1. Installing IBator

Visit this link and download ibator plugin for eclipse

http://code.google.com/p/mybatis/wiki/Downloads?tm=2

Or From Eclipse Update Site :

http://ibatis.apache.org/tools/ibator

Step 2. Requirements and Project Configuration: 

Adding Ibator Library to Build Path

Create a java project ‘MyIbatisTestPr’ on your eclipse IDE. To setup ibator to build path, right click on project àchoose ‘Add Ibator to Build Path’. 

This will automatically add the IBATOR_JAR to your referenced libraries. You must manually add all the other library shown in figure below.

Step 3. Now create bookstore database on MySQL.

Run the following SQL.

CREATE database IF NOT EXISTS `bookstore`;

-- Tables

CREATE TABLE IF NOT EXISTS `author` (

  `authorid` varchar(10) NOT NULL,

  `authname` varchar(40) NOT NULL,


`authaddress` varchar(40) DEFAULT NULL,

  PRIMARY KEY (`authorid`)

);

CREATE TABLE IF NOT EXISTS `book` (

  `bookid` varchar(10) NOT NULL,

  `bookname` varchar(50) DEFAULT NULL,

  `authorid` varchar(10) NOT NULL,

  PRIMARY KEY (`bookid`)

);

-- Values

INSERT INTO `author` (`authorid`, `authname`, `authaddress`) VALUES

('A001', 'Ram', 'KTM'),

('A002', 'Shyam', 'NGT');

('A003', 'Hari', 'BRT');

('A004', 'David', 'UK');

('A005', 'Micheal', 'USA');

('A006', 'John', 'AUS');

INSERT INTO `book` (`bookid`, `bookname`, `authorid`) VALUES

('B001', 'Rams'' New Book', 'A001'),

('B002', 'Rams'' second book', 'A001'),

('B003', 'Hariyo dada mathi', 'A002'),

('B004', 'Halo jotne sathi', 'A002'); 

Step 4: Settings for ibator

Now Create ibatorConfig.xml  file on your project folder. and define appropriate target packages for javaModelGenerator, sqlMapGenerator and daoGenerators.

<ibatorConfiguration>

<classPathEntry

location="E:\LAB\ALL JARs\mysql-connector-java-5.1.7-bin.jar/>

<ibatorContext id="ibatorContext" targetRuntime="Ibatis2Java5"

      defaultModelType="flat">

      <ibatorPlugin            type="org.apache.ibatis.ibator.plugins.SerializablePlugin/>

      <!-- Setting database connection -->

      <jdbcConnection driverClass="com.mysql.jdbc.Driver"

            connectionURL="jdbc:mysql://localhost:3306/bookstore"

            userId="user"

            password="">

      </jdbcConnection>

      <!-- Settings for generating persistence models -->

      <javaModelGenerator targetPackage="com.bookStore.model"

            targetProject="MyIbatisTestPr">

            <property name="enableSubPackages" value="true/>

            <property name="trimStrings" value="true/>

      </javaModelGenerator>

      <!-- For Auto-generated SqlMaps -->

      <sqlMapGenerator targetPackage="com.bookStore.dbMaps"

            targetProject="MyIbatisTestPr">



Step 5: Now ready for using IBator. 

Just right click ibatorConfig.xml on package explorer. And choose ‘Generate iBATIS Artifacts’. If your MySql server is running and all settings is correct, ibator will generate the models, DAO and sqlmap into corresponding directory.

The generated Files




Part B: Test Application
You can find the download link for running code at the end of this tutorial.
Step1.Spring Application Context Configuration (ApplicationContext.xml)

DataSource :

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"

      destroy-method="close">

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

      <property name="url" value="jdbc:mysql://localhost:3306/bookstore/>

      <property name="username" value="user/>

      <property name="password" value="/>

</bean>

Spring Bean wiring:

<!-- SqlMap Client Configuration, to read generated SqlMaps, as well as

      Custom Sqls in separate xml file -->

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">

      <property name="configLocation" value="sqlmap-config.xml/>

      <property name="dataSource" ref="dataSource/>

</bean>

<bean id="bookService" class="com.bookStore.service.impl.BookDAOImpl"><property name="sqlMapClient" ref="sqlMapClient/>

</bean>

      <bean id="authorService" class="com.bookStore.service.impl.AuthorDAOImpl">

      <property name="sqlMapClient" ref="sqlMapClient/>

</bean>

Step2.SqlMap configuration

<sqlMapConfig>

<settings useStatementNamespaces="true/>

<!-- Transcation Manager Settings... -->

<transactionManager type="JDBC">

      <dataSource type="SIMPLE">

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

      <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost:3306/bookstore/>

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

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

      </dataSource>

</transactionManager>

<!-- add generated sql maps -->

<sqlMap resource="com/bookStore/dbMaps/author_SqlMap.xml/>

<sqlMap resource="com/bookStore/dbMaps/book_SqlMap.xml/>

<!-- <sqlMap resource="customSqlMap.xml/> -->

</sqlMapConfig>

Step3. Java Application

Be sure the project directory structure is as follows

                                                                                                                                                                                                                                                        

i)Inserting/Reading rows into DB Programmatically

Getting context session and DAO Objects in TestIbatis.java

      ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("ApplicationContext.xml");

      //-- getting services from context --//

      BookDAO bdao= (BookDAO) ctx.getBean("bookService");

      AuthorDAO adao= (AuthorDAO) ctx.getBean("authorService");

Adding Author:

Author a= new Author();

a.setAuthorid("A013");

a.setAuthname("Ganesh");

a.setAuthaddress("NGT");

adao.insertAuthor(a);

Adding Book:

b= new Book();

b.setBookid("B006");

b.setBookname("Digital Signal Processing");

b.setAuthorid("A013");

bdao.insertBook(b);    

Reading by generated Sql

//read Authors detail

AuthorExample aex= new AuthorExample();

com.bookStore.model.AuthorExample.Criteria acrt= aex.createCriteria();

//adding criteria, WHERE clause in SQL

acrt.andAuthoridEqualTo("A001");

List<Author> retList=adao.selectAuthorByExample(aex);

if(retList!=null && retList.size()>0){

      for (Author athr : retList) {

            System.out.println("ID   :"+athr.getAuthorid());

            System.out.println("Name :"+athr.getAuthname());

            System.out.println("Addr :"+athr.getAuthaddress());

      }

}

ii)Reading Data By Dynamic Query

a.First Create customSqlMap.xml file. 

The select query ‘getBooks’in namespace ‘BookStoreSqlMap’ is a dynamic query, it returns list of books depending upon the combinations of three dynamic criteria.

<sqlMap namespace="BookStoreSqlMap">

<!-- custom sqls -->

<select id="getBooks" parameterClass="com.bookStore.model.Book"

      resultClass="com.bookStore.model.Book">

      SELECT * FROM book

      WHERE

      1=1

      <dynamic>

            <isNotNull prepend="AND" property="bookid">

                  bookid=#bookid#

      </isNotNull>

            <isNotNull prepend="AND" property="bookname">

                  bookname=#bookname#

      </isNotNull>

            <isNotNull prepend="AND" property="authorid">

                  authorid=#authorid#

      </isNotNull>

      </dynamic>

</select>

</sqlMap>

b.Add map in sqlMap-config.xml

      <sqlMap resource="customSqlMap.xml/>

c. And the Test application

Reader rd = Resources.getResourceAsReader("sqlMap-config.xml");

SqlMapClient smc = SqlMapClientBuilder.buildSqlMapClient(rd);

Book param = new Book();

param.setAuthorid("A001");

// the value of parameters will make diffenrent combinations of sql query

// param.setBookid("B001");

// run query getBooks from BookStoreSqlMap namespace

List<Object> retList = smc.queryForList("BookStoreSqlMap.getBooks", param);

if (retList != null && retList.size() > 0) {

      for (int i = 0; i < retList.size(); i++) {

            Book b = (Book) retList.get(i);

            System.out.println("ID  : " + b.getBookid());

            System.out.println("Name: " + b.getBookname());

            System.out.println("Addr: " + b.getAuthorid());

      }

}


You can download the working example from here.