Object relational mapping with iBATIS
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. I chose iBATIS 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.
Commmand 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 it's 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 it's color 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 a 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.
