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());
      }

7 comments :

  1. Nice Article
    But in the First step the the update site for eclipse is not mentioned
    it has to be

    1. First of all, install the ibator plugin for eclipse using the update site http://ibatis.apache.org/tools/ibator

    ReplyDelete
  2. @avishekh, thanks for the suggestion. i added it.

    ReplyDelete
  3. Mr. Tiwari,

    do you have full code for this tutorial ?

    -Thanks.

    ReplyDelete
  4. Yes i do have .. I simulated the same example

    ReplyDelete
  5. @khawaldar i don't know how but the some part of article was missing in blog. now i have corrected this. download the code from here http://www.4shared.com/file/xF2wIpSV/Ibatis_Ibator_Example_eclipse_.html

    ReplyDelete
  6. Do you mind if I quote a couple of your posts as long as I
    provide credit and sources back to your weblog? My blog site is in the exact same
    niche as yours and my users would certainly benefit from a lot of the information you present here.
    Please let me know if this ok with you. Thank you!
    Feel free to visit my web blog ; search engine

    ReplyDelete
  7. Thanks Ganesh for post.
    It is indeed very useful.

    One more thing...
    I faced one issue....
    Unexpected error while running Ibator. Cannot instantiate object of type.

    This happens if you don't set the value for daoGenerator if you set the type attribute correctly then you will overcome this issue. E.g. type="GENERIC-SI"

    Also you can use any latest spring-core.jar

    Happy Coding,
    Arvind Rathod :)

    ReplyDelete

Your Comment and Question will help to make this blog better...