Question

In this part of the JEE programming tutorial, we will talk about the object relational mapping with iBATIS.

Object Relational Mapping (ORM) is a programming technique for converting data between relational databases and object oriented programming languages. Data is handled differently in both systems. The problem that arises from these differences is called the object-relational impedance mismatch. The ORM tools were created to help application programmers to cope with these issues. Hibernate or Toplink are two of such tools. In large JEE applications developers mostly do not work directly with SQL, but they use ORM tools.

iBATIS is another ORM mapping tool. iBATIS was chosen for these tutorials because it is simple and easy to use. iBATIS can be used with Java, .NET, or Ruby. It is developed by the Apache Software Foundation. Simplicity is the biggest advantage over other ORM tools.

Command line example

The first example will be command line. The example will get all data from a database table using iBATIS. We will use books database and books table again.

mysql> describe books;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra      |
+--------+--------------+------+-----+---------+----------------+
| id   | int(11)    | NO   | PRI | NULL  | auto_increment | 
| author | varchar(30)  | YES  |   | NULL  |        | 
| title  | varchar(40)  | YES  |   | NULL  |        | 
| year   | int(11)    | YES  |   | NULL  |        | 
| remark | varchar(100) | YES  |   | NULL  |        | 
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.23 sec)

This is the books table.

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>

 <transactionManager type="JDBC" commitRequired="false">
  <dataSource type="SIMPLE">
   <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
   <property name="JDBC.ConnectionURL" 
     value="jdbc:mysql://localhost:3306/books"/>
   <property name="JDBC.Username" value="root"/>
   <property name="JDBC.Password" value=""/>
   </dataSource>
 </transactionManager>

 <sqlMap resource="ibatis/Books.xml"/>

</sqlMapConfig>

This is the configuration file for the iBATIS. We can give it an arbitrary name. Inside the configuration file, we define the datasource an various SQL map files. We use the MySQL database.

Book.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="Book">
  
<typeAlias alias="Book" type="ibatis.Book"/>

  <select id="selectAllBooks" resultClass="ibatis.Book">
  select * from books
  </select>

</sqlMap>

This is the SQL map file. This file will map a Java class to a database table. In our case the Java class is Book.java and the table is books in the books database.

Book.java

package ibatis;

public class Book {

  private int id;
  private String author;
  private String title;
  private String year;
  private String remark;

  public String getAuthor() {
    return author;
  }

  public void setAuthor(String author) {
    this.author = author;
  }

  public int getBookId() {
    return id;
  }

  public void setBookId(int id) {
    this.id = id;
  }

  public String getRemark() {
    return remark;
  }

  public void setRemark(String remark) {
    this.remark = remark;
  }

  public String getTitle() {
    return title;
  }

  public void setTitle(String title) {
    this.title = title;
  }

  public String getYear() {
    return year;
  }

  public void setYear(String year) {
    this.year = year;
  }
}

This is the Books bean Java class with all its properties and setter and getter methods.

Main.java

package ibatis;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.List;

public class Main {

  public static void main(String[] args)
      throws IOException, SQLException {

  Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml");
  SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

  List<Book> books = (List<Book>) 
    sqlMap.queryForList("selectAllBooks");

  for (Book a : books) {
    System.out.println(a.getAuthor() + " : " + a.getTitle());
  }
  }
}

This code loads the configuration file, receives the data using the queryForList() method call and prints authors and titles of books to the console.

$ java -jar ibatis.jar 
Leo Tolstoy : War and Peace
Leo Tolstoy : Anna Karenina
ralf reuth : rommel
Balzac : Goriot
David Schwartz : The magic of thinking big
Johannes Leeb : Der Nuernberger prozess
Siegfried Knappe : German Soldier
Kertész Imre : Sorstalanság
Napoleon Hill : Think and grow rich
Brett Spell : Professional Java Programming

This is the result that we get.

Web example

The next example will enhance the previous one. In addition to selecting all books, we will also have the ability to insert and delete books.

style.css

* { font-size: 12px; font-family: Verdana }

input { border: 1px solid #ccc }

a#currentTab {
  border-bottom:1px solid #fff;
}

a { color: black; text-decoration:none;
  padding:5px; border: 1px solid #aaa;
}

a:hover { background: #ccc; cursor: pointer; }

td { border: 1px solid #ccc; padding: 3px }
th { border: 1px solid #ccc; padding: 3px; 
  background: #009999; color: white }

.navigator { border-bottom:1px solid #aaa; width:300px; padding:5px }

.hovered { background-color: #c4dcff }
.selected { background-color: #a5ffb8 }
.highlighted { background-color: #e33146 }
.unselected { background-color: #ffffff }

Simple css file used in our example.

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5">
  <servlet>
    <servlet-name>GetAllBooks</servlet-name>
    <servlet-class>com.zetcode.GetAllBooks</servlet-class>
  </servlet>
  <servlet>
    <servlet-name>InsertBook</servlet-name>
    <servlet-class>com.zetcode.InsertBook</servlet-class>
  </servlet>
  <servlet>
    <servlet-name>DeleteBooks</servlet-name>
    <servlet-class>com.zetcode.DeleteBooks</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>GetAllBooks</servlet-name>
    <url-pattern>/GetAllBooks</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>InsertBook</servlet-name>
    <url-pattern>/InsertBook</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>DeleteBooks</servlet-name>
    <url-pattern>/DeleteBooks</url-pattern>
  </servlet-mapping>
  <session-config>
    <session-timeout>
      30
    </session-timeout>
  </session-config>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
</web-app>

This is the web.xml file. Here we configure our three servlets.

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>

 <transactionManager type="JDBC" commitRequired="false">
  <dataSource type="SIMPLE">
   <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
   <property name="JDBC.ConnectionURL" 
     value="jdbc:mysql://localhost:3306/books"/>
   <property name="JDBC.Username" value="root"/>
   <property name="JDBC.Password" value=""/>
   </dataSource>
 </transactionManager>

 <sqlMap resource="ibatis/Books.xml"/>

</sqlMapConfig>

The sqlMapConfig.xml file is unchanged.

Book.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="Book">
  
<typeAlias alias="Book" type="com.zetcode.Book"/>

  <select id="selectAllBooks" resultClass="com.zetcode.Book">
  select * from books
  </select>

  <insert id="insertBook" parameterClass="com.zetcode.Book">
  insert into books (
    author,
    title,
    year,
    remark )
  values (
    #author#, #title#, #year#, #remark#
  )
  </insert>

  <delete id="deleteBooks" parameterClass="String">
  delete from books where id = #id#
  </delete>
  
</sqlMap>

The Book.xml file has three statements. It enables to select, insert and delete data. The values between the # characters are parameters to the SQL map client.

Book.java

package com.zetcode;

import java.io.Serializable;

public class Book implements Serializable {

  private String id;
  private String author;
  private String title;
  private String year;
  private String remark;

  public String getId() {
    return id;
  }

  public void setId(String id) {
    this.id = id;
  }

  public String getAuthor() {
    return author;
  }

  public void setAuthor(String author) {
    this.author = author;
  }

  public String getTitle() {
    return title;
  }

  public void setTitle(String title) {
    this.title = title;
  }

  public String getYear() {
    return year;
  }

  public void setYear(String year) {
    this.year = year;
  }

  public String getRemark() {
    return remark;
  }

  public void setRemark(String remark) {
    this.remark = remark;
  }
}

The Book.java file is the same as in the first example.

DriverManagerExample.java

function getBooks() {
  form = window.document.getElementById("form");
  form.action="GetAllBooks";
  form.submit();
}

function insertBook() {
  form = window.document.getElementById("form");
  form.action="InsertBook";
  form.submit();
}


function OnDelete() {

  var tbl = document.getElementById("books");
  var tds =  tbl.getElementsByTagName("td");

  var id;

  for(var i=0; i < tds.length; ++i )
  {
    if (!(i%5))
      if (tds[i].parentNode.className == "selected") {
        id = tds[i].innerHTML;
      }
  }

  var form = document.getElementById("form");
  var input = document.getElementById("bookId");
  input.value=id;

  form.action="DeleteBooks";
  form.submit();
}

function select(obj) {
  if (obj.className != "selected") 
  obj.className = "hovered";

 }

function unselect(obj) {
  if (obj.className == "hovered")
  obj.className = "unselected";
}

function clicked(obj) {

  var tbl = document.getElementById("books");
  var trs = tbl.getElementsByTagName("tr");

  for(var l=0; l < trs.length; ++l )
  {
  if (trs[l].className == "selected")
    trs[l].className = "unselected";
  }

  obj.className = "selected";
}

This is the JavaScript that we use in our example.

function getBooks() {
  form = window.document.getElementById("form");
  form.action="GetAllBooks";
  form.submit();
}

The getBooks() function will call the GetAllBooks servlet.

The insertBook() function will call the InsertBook servlet. The OnDelete() function will figure out, what row is currently selected and call the DeleteBooks servlet afterwards.

If we hover a mouse pointer over a row in a table, we change its colour to light blue. We do this using select() and unselect() JavaScript functions.

function clicked(obj) {

  var tbl = document.getElementById("books");
  var trs = tbl.getElementsByTagName("tr");

  for(var l=0; l < trs.length; ++l )
  {
  if (trs[l].className == "selected")
    trs[l].className = "unselected";
  }

  obj.className = "selected";
}

If we click on a specific row, we change its colour to dark blue. The for loop makes sure that only one row is selected at a time. All previously selected rows get unselected.

index.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<html>
   <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Books database</title>
    <link rel="stylesheet" href="style.css" type="text/css">
   </head>

   <script src="scripts.js"></script>

 <body>
  <br>

  <div class="navigator">
   <a id="currenttab" href="index.jsp">Add</a>
   <a onclick="getBooks();">Show</a>
  </div>

  <br> <br> <br>


  <form method="post" name="form" id="form">
   <table>
    <tr>  
      <td>Author</td><td><input type="text" name="author"></td>
    </tr>  
    <tr>
      <td>Title</td><td><input type="text" name="title"></td>
    </tr>
    <tr>
      <td>Year</td><td> <input type="text" name="year"></td>
    </tr>
    <tr>
      <td>Remark</td><td> <input type="text" name="remark"></td>
    </tr>

  </table>

  <input type="button" value="Insert" onclick="insertBook()">

  <br>  
  </form>
</body>
</html>

This is the introdutory JSP file. It has an HTML form to add a new book to our database.

show.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.util.*" %>
<%@page import="com.zetcode.Book" %>


<html>
  <head>
  <title>View</title>
  <link rel="stylesheet" href="style.css" type="text/css">
  <script src="scripts.js"></script>
  </head>

 <body>

   <br>

  <div class="navigator">
     <a href="index.jsp">Add</a>
     <a id="currenttab" href="show.jsp">Show</a>
  </div>

  <br> <br> <br>
  <form  method="get" id="form">
  <input type="hidden" name="bookId" id="bookId">
  <table id="books">
    <tr>
    <th>Id</th>
    <th>Author</th>
    <th>Title</th>
    <th>Year</th>
    <th>Remark</th>
    </tr> 
    <%

    List<com.zetcode.Book> list = (List<com.zetcode.Book>) 
        session.getAttribute("books");

    for (Book a : list) {
      out.print("<tr class='unselected' id='row' onclick='clicked(this)' " +
          "onmouseout='unselect(this)' onmouseover='select(this)'>");
      out.print("<td id='id'>");
      out.print(a.getId());
      out.print("</td>");
      out.print("<td>");
      out.print(a.getAuthor());
      out.print("</td>");
      out.print("<td>");
      out.print(a.getTitle());
      out.print("</td>");
      out.print("<td>");
      out.print(a.getYear());
      out.print("</td>");
      out.print("<td>");
      out.print(a.getRemark());
      out.print("</td>");
      out.print("</tr>");
    }
    %>

    </table>
    <br>
    <input type="button" value="Delete" onclick="OnDelete()">

    </form>

    <br>

  </body>
</html>

The show.jsp file shows the data from the books table. It also enables to delete a currently selected book from the database.

DeleteBooks.java

package com.zetcode;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.*;
import java.net.*;

import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.*;
import javax.servlet.http.*;


public class DeleteBooks extends HttpServlet {


  protected void processRequest(HttpServletRequest request, 
      HttpServletResponse response)
  throws ServletException, IOException {

  response.setContentType("text/html;charset=UTF-8");


  try {
  Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml");
  SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

  String id = request.getParameter("bookId");
  sqlMap.delete("deleteBooks", id);


  } catch (SQLException ex) {
    Logger.getLogger(GetAllBooks.class.getName()
        ).log(Level.SEVERE, null, ex);

  } finally {

    RequestDispatcher dispatcher = 
           request.getRequestDispatcher("/GetAllBooks");
    dispatcher.forward(request, response);
  }
 }


  protected void doGet(HttpServletRequest request, 
    HttpServletResponse response)
  throws ServletException, IOException {
    processRequest(request, response);
  } 


  protected void doPost(HttpServletRequest request, 
    HttpServletResponse response)
  throws ServletException, IOException {
    processRequest(request, response);
  }
}

The DeleteBooks servlet deletes a book from the database.

String id = request.getParameter("bookId");
sqlMap.delete("deleteBooks", id);

We get the id of the book from the request. The id is given to the SQL map client delete statement.

RequestDispatcher dispatcher = request.getRequestDispatcher("/GetAllBooks");
dispatcher.forward(request, response);

After we delete the book, we call the GetAllBooks servlet.

GetAllBooks.java

package com.zetcode;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.sql.*;
import java.io.*;
import java.net.*;

import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.*;
import javax.servlet.http.*;

public class GetAllBooks extends HttpServlet {


  protected void processRequest(HttpServletRequest request,
      HttpServletResponse response)
     throws ServletException, IOException {

  response.setContentType("text/html;charset=UTF-8");

  try {

    Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml");
    SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

    List<Book> books = (List<Book>) sqlMap.queryForList("selectAllBooks");
    request.getSession().setAttribute("books", books);

    } catch (SQLException ex) {
      Logger.getLogger(GetAllBooks.class.getName()).log(
          Level.SEVERE, null, ex);

    } finally {
      RequestDispatcher dispatcher = 
             request.getRequestDispatcher("/show.jsp");
      dispatcher.forward(request, response);
    }
  }

  protected void doGet(HttpServletRequest request, 
    HttpServletResponse response)
      throws ServletException, IOException {
    processRequest(request, response);
  }

  protected void doPost(HttpServletRequest request, 
    HttpServletResponse response)
      throws ServletException, IOException {
    processRequest(request, response);
  }
}

The GetAllBooks servlet selects all data from the books table and put it into the session object. Later in the show.jsp file we retrieve this data.

List<Book> books = (List<Book>) sqlMap.queryForList("selectAllBooks");
request.getSession().setAttribute("books", books);

Here we select the data and put it into the session.

InsertBook.java

package com.zetcode;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.sql.*;
import java.io.*;
import java.net.*;

import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.*;
import javax.servlet.http.*;

public class InsertBook extends HttpServlet {


  protected void processRequest(HttpServletRequest request,
      HttpServletResponse response)
      throws ServletException, IOException {

  response.setContentType("text/html;charset=UTF-8");

  String author = request.getParameter("author");
  String title = request.getParameter("title");
  String year = request.getParameter("year");
  String remark = request.getParameter("remark");

  try {

  Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml");
  SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);


  Book book = new Book();

  book.setAuthor(author);
  book.setTitle(title);
  book.setYear(year);
  book.setRemark(remark);

  sqlMap.insert("insertBook", book);


  } catch (SQLException ex) {
  Logger.getLogger(GetAllBooks.class.getName()).log(
             Level.SEVERE, null, ex);

  } finally {

  RequestDispatcher dispatcher = 
             request.getRequestDispatcher("/GetAllBooks");
  dispatcher.forward(request, response);
  }
  }

  protected void doGet(HttpServletRequest request, 
    HttpServletResponse response)
      throws ServletException, IOException {
    processRequest(request, response);
  }

  protected void doPost(HttpServletRequest request, 
    HttpServletResponse response)
      throws ServletException, IOException {
    processRequest(request, response);
  }
}

The InsertBook servlet inserts a new book into the database.

String author = request.getParameter("author");
String title = request.getParameter("title");
String year = request.getParameter("year");
String remark = request.getParameter("remark");

We get the necessary data from the request.

Book book = new Book();

book.setAuthor(author);
book.setTitle(title);
book.setYear(year);
book.setRemark(remark);

Create and fill the Book class.

sqlMap.insert("insertBook", book);

Insert the data into the database using the SQL map client.

Figure: iBATIS

In this chapter we have shortly talked about ORM with iBATIS.