iBATIS is a persistence framework that maps objects to SQL statements. The SQL is stored in XML files. There are implementations for a variety of programming languages including Java, Ruby, and .NET languages.
The iBATIS (pronounced eye-bat-iss) project started in 2001 and initially focused on cryptography software. The name is a combination of the words "internet" and "abatis". Abatis means "a defensive obstacle made by laying felled trees on top of each other with branches, sometimes sharpened, facing the enemy." So it originally focused on internet security.
A significant difference between iBATIS and other persistence frameworks such as Hibernate is that iBATIS emphasizes use of SQL, while other frameworks typically use a custom query language such has the Hibernate Query Language (HQL) or Enterprise JavaBeans Query Language (EJB QL).
iBATIS may be significantly faster than Hibernate. See the performance discussion here.
A book on iBATIS titled "iBATIS in Action" was released in January 2007.
Javadoc for the iBATIS library is here.
Here's a list of some of the companies that are using iBATIS. Much of this information was found here.
Some feedback on using iBATIS can be found here.
To install iBATIS for use with Java, download "iBATIS Java {version} Binaries, Source, and Documentation" from here and unzip it. This will result in a directory name "ibatis-2".
The doc directory contains a PDF version of the Developer Guide and zipped javadoc HTML files.
The lib directory contains the only JAR file needed, ibatis-{version}.jar.
Our example database is named "music". It has two tables. The "artists" table has the columns "id" and "name". The "recordings" table has the columns "id", "name", "year" and "artist_id". The id columns are the primary keys of their tables. The recordings table artist_id column is a foreign key reference to the artists table id column. SQL for creating the database is in the following file named "createTables.sql".
Note that SQL is sometimes not portable between databases. In this example, were using the type "text" which is supported by MySQL, but not by all relational databases. Also, the way that primary keys are configured to "auto increment" varies across databases.
drop database if exists music; create database music; use music; drop table if exists artists; create table artists ( id int not null auto_increment, name text, primary key (id) ); drop table if exists recordings; create table recordings ( id int not null auto_increment, name text, year int, artist_id int, primary key (id) );
When using MySQL, this can be run with a command like the following.
mysql -uroot < createTables.sql
POJO classes must be created to describe the objects that will "model" database table rows. We'll need an Artist class and a Recording class. They do not need to implement the Comparable interface, but we're doing that because we want to sort these objects. Alternately, the SQL queries could be written to specify the desired sort order of the rows that are returned.
Here's the Artist class. Note that an Artist holds a collection of its Recordings.
package com.ociweb.music; import java.util.List; public class Artist implements Comparable { private int id; private List<Recording> recordings; private String name; public Artist() {} public Artist(String name) { this.name = name; } public int compareTo(Object obj) { Artist r = (Artist) obj; return name.compareTo(r.name); } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public List<Recording> getRecordings() { return recordings; } public void setRecordings(List<Recording> recordings) { this.recordings = recordings; } }
Here's the Recording class. Note that a Recording holds a reference to its Artist.
package com.ociweb.music; public class Recording implements Comparable { private int id; private int year; private Artist artist; private String name; public Recording() {} public Recording(Artist artist, String name, int year) { this.artist = artist; this.name = name; this.year = year; } public int compareTo(Object obj) { Recording r = (Recording) obj; return name.compareTo(r.name); } public Artist getArtist() { return artist; } public void setArtist(Artist artist) { this.artist = artist; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getYear() { return year; } public void setYear(int year) { this.year = year; } }
Configuration is specified in the file SqlMapConfig.xml. We want to specify the following things:
com.mysql.jdbc.Driver.jdbc:mysql://localhost:3306/music.Here's our configuration file.
<?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://localhost:3306/music"/> <property name="JDBC.Username" value="root"/> <property name="JDBC.Password" value=""/> </dataSource> </transactionManager> <sqlMap resource="Artist.xml"/> <sqlMap resource="Recording.xml"/> </sqlMapConfig>
Update from Rich Reese
Mapped statements give ids to SQL statements.
These ids are referenced in the code that invokes them
using methods in the
org.ibatis.sqlmap.client.SqlMapClient interface
such as queryForObject and queryForList.
Because we specified that we wanted to "useStatementNamespaces"
in our configuration file, our references to statements
will have the syntax "{namespace}.{statement-id}".
Mapped statements are defined in XML files that are referenced from the SqlMapConfig.xml file.
Here's the file that specifies statements related to our artists table.
<?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="Artist"> <delete id="deleteAll"> delete from artists </delete> <insert id="insert" parameterClass="com.ociweb.music.Artist"> insert into artists (name) values (#name#) <selectKey resultClass="int" keyProperty="id"> select last_insert_id() as id </selectKey> </insert> <resultMap id="result" class="com.ociweb.music.Artist"> <result property="id" column="id"/> <result property="name" column="name"/> <!-- This results in N+1 selects. To avoid this, see page 40 in the iBATIS Developer Guide. --> <result property="recordings" column="id" select="Recording.getByArtist"/> </resultMap> <select id="getById" parameterClass="java.lang.Integer" resultMap="result"> select * from artists where id=#id# </select> <select id="getAll" resultClass="com.ociweb.music.Artist"> select * from artists </select> </sqlMap>
Here's the file that specifies statements related to our recordings table.
<?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="Recording"> <delete id="deleteAll"> delete from recordings </delete> <insert id="insert" parameterClass="com.ociweb.music.Recording"> insert into recordings (name, year, artist_id) values (#name#, #year#, #artist.id#) <selectKey resultClass="int" keyProperty="id"> select last_insert_id() as id </selectKey> </insert> <resultMap id="resultWithoutArtist" class="com.ociweb.music.Recording"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="year" column="year"/> </resultMap> <resultMap id="result" class="com.ociweb.music.Recording" extends="resultWithoutArtist"> <result property="artist" column="artist_id" select="Artist.getById"/> </resultMap> <select id="getAll" resultClass="com.ociweb.music.Recording"> select * from recordings </select> <!-- resultWithoutArtist is used here to avoid a circular dependency when Artist.result (in Artist.xml) is used. --> <select id="getByArtist" parameterClass="java.lang.Integer" resultMap="resultWithoutArtist"> select * from recordings where artist_id=#artistId# </select> <select id="getByYear" parameterClass="java.lang.Integer" resultMap="result"> select * from recordings where year=#year# </select> </sqlMap>
Now we need to put some data in the database.
The following Java application demonstrates how to do this.
The methods getArtist and addRecording
are used to simplify the process.
package com.ociweb.music; import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder; import java.io.*; import java.sql.SQLException; import java.util.*; public class LoadDB { private static SqlMapClient sqlMap; private Map<String, Artist> artists = new HashMap<String, Artist>(); public static void main(String[] args) throws IOException, SQLException { Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml"); sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader); new LoadDB(); } private LoadDB() throws SQLException { sqlMap.delete("Artist.deleteAll"); sqlMap.delete("Recording.deleteAll"); String name = "Deathcab For Cutie"; addRecording(name, "We Have the Facts and We're Voting Yes", 2000); addRecording(name, "The Photo Album", 2001); addRecording(name, "You Can Play These Songs With Chords", 2002); addRecording(name, "Transatlanticism", 2003); addRecording(name, "Plans", 2005); name = "Regina Spektor"; addRecording(name, "Begin To Hope", 2006); addRecording(name, "Soviet Kitch", 2003); } private Artist getArtist(String name) throws SQLException { Artist artist = artists.get(name); if (artist == null) { artist = new Artist(name); Object key = sqlMap.insert("Artist.insert", artist); artists.put(name, artist); } return artist; } private void addRecording(String artistName, String name, int year) throws SQLException { Artist artist = getArtist(artistName); Recording recording = new Recording(artist, name, year); sqlMap.insert("Recording.insert", recording); } }
We're ready to write queries now. The following application demonstrates a few queries.
package com.ociweb.music; import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder; import java.io.*; import java.sql.SQLException; import java.util.*; public class Client { public static void main(String[] args) throws IOException, SQLException { Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml"); SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader); // Output the names of all the Artists in ascending order. System.out.println("All artists"); List<Artist> artists = (List<Artist>) sqlMap.queryForList("Artist.getAll"); Collections.sort(artists); Artist artist = null; for (Artist a : artists) { System.out.println(" " + a.getName()); artist = a; // retain a reference to the last Artist } // Get the id of the last Artist. int id = artist.getId(); // Output the name of the artists with a given id. System.out.println("\nArtist with id=" + id); artist = (Artist) sqlMap.queryForObject("Artist.getById", id); System.out.println(" " + artist.getName()); // Output the names of all recordings from 2003 in ascending order. System.out.println("\n2003 Recordings"); List<Recording> recordings = (List<Recording>) sqlMap.queryForList("Recording.getByYear", 2003); Collections.sort(recordings); for (Recording r : recordings) { System.out.println(" " + r.getName() + " by " + r.getArtist().getName()); } } }
The output from this code follows.
All artists Deathcab For Cutie Regina Spektor Artist with id=2 Regina Spektor 2003 Recordings Soviet Kitch by Regina Spektor Transatlanticism by Deathcab For Cutie
iBATIS has built-in logging that works with the following logging libraries and searches for them in this order.
To use Log4J,
Here's an example of a log4j.properties file. Note that some of the lines are commented out and are preceded by a comment describing what will be output if they are uncommented.
# Global logging configuration log4j.rootLogger=ERROR, stdout #log4j.logger.com.ibatis=DEBUG # shows SQL of prepared statements #log4j.logger.java.sql.Connection=DEBUG # shows parameters inserted into prepared statements #log4j.logger.java.sql.PreparedStatement=DEBUG # shows query results #log4j.logger.java.sql.ResultSet=DEBUG #log4j.logger.java.sql.Statement=DEBUG # Console output log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
To compile and run code that uses iBATIS, the following JARs must be in the classpath.
If logging is desired, a JAR containing the logging library to be used must also be in the classpath.
Copyright © 2007 Object Computing, Inc. All rights reserved.