Getting Started with iBatis (MyBatis): XML Configuration

February 14, 2011 | By | 9 Comments

This tutorial will walk you through how to setup iBatis (MyBatis) in a simple Java project and will present examples using simple insert, update, select and delete statements.

ibatis mybatis loiane Getting Started with iBatis (MyBatis): XML Configuration

Pre-Requisites

For this tutorial I am using:

IDE: Eclipse (you can use your favorite one)
DataBase: MySQL
Libs/jars: Mybatis, MySQL conector and JUnit (for testing)

This is how your project should look like:

ibatis mybatis helloworld loiane Getting Started with iBatis (MyBatis): XML Configuration

Sample Database

Please run this script into your database before getting started with the project implementation:

DROP TABLE IF EXISTS `blog`.`contact`;
CREATE TABLE  `blog`.`contact` (
  `CONTACT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `CONTACT_EMAIL` varchar(255) NOT NULL,
  `CONTACT_NAME` varchar(255) NOT NULL,
  `CONTACT_PHONE` varchar(255) NOT NULL,
  PRIMARY KEY (`CONTACT_ID`)
)
ENGINE=InnoDB;

insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact0','(000) 000-0000', 'contact0@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact1', '(000) 000-0000', 'contact1@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact2', '(000) 000-0000', 'contact2@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact3', '(000) 000-0000', 'contact3@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact4', '(000) 000-0000', 'contact4@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact5', '(000) 000-0000', 'contact5@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact6', '(000) 000-0000', 'contact6@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact7', '(000) 000-0000', 'contact7@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact8', '(000) 000-0000', 'contact8@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact9', '(000) 000-0000', 'contact9@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact10', '(000) 000-0000', 'contact10@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact11', '(000) 000-0000', 'contact11@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact12', '(000) 000-0000', 'contact12@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact13', '(000) 000-0000', 'contact13@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact14', '(000) 000-0000', 'contact14@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact15', '(000) 000-0000', 'contact15@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact16', '(000) 000-0000', 'contact16@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact17', '(000) 000-0000', 'contact17@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact18', '(000) 000-0000', 'contact18@loianetest.com');
insert into CONTACT (CONTACT_NAME, CONTACT_PHONE, CONTACT_EMAIL) values ('Contact19', '(000) 000-0000', 'contact19@loianetest.com');

1 – Contact POJO

We will create a POJO class first to respresent a contact with id, name, phone number and email address:

package com.loiane.model;

public class Contact {

	private int id;
	private String name;
	private String phone;
	private String email;

	public Contact(int id, String name, String phone, String email) {
		super();
		this.id = id;
		this.name = name;
		this.phone = phone;
		this.email = email;
	}

	public Contact() {}

	//getters and setters
}

2 – Contact.xml

This is the iBatis/myBatis SQL map configuration file for Contact class. We are going to write all the SQL queries, map a query to object in this file – here is where all the magic happens!

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="Contact">

	<resultMap id="result" type="Contact">
	    <result property="id" column="CONTACT_ID"/>
	    <result property="name" column="CONTACT_NAME"/>
	    <result property="phone" column="CONTACT_PHONE"/>
	    <result property="email" column="CONTACT_EMAIL"/>
    </resultMap>

    <select id="getAll" resultMap="result">
    	SELECT * FROM CONTACT
    </select>

    <select id="getById" parameterType="int" resultMap="result">
    	SELECT * FROM CONTACT WHERE CONTACT_ID = #{id}
  	</select>

    <delete id="deleteById" parameterType="int">
    	DELETE from CONTACT WHERE CONTACT_ID = #{id};
    </delete>

    <insert id="insert" parameterType="Contact">
    INSERT INTO CONTACT (CONTACT_EMAIL, CONTACT_NAME, CONTACT_PHONE)
		VALUES (#{name}, #{phone}, #{email});
      <selectKey keyProperty="id" resultType="int" order="AFTER">
		select last_insert_id() as id
	  </selectKey>
    </insert>

	<update id="update" parameterType="Contact">
	  	UPDATE CONTACT
		SET
			CONTACT_EMAIL = #{email},
			CONTACT_NAME = #{name},
			CONTACT_PHONE = #{phone}
		WHERE CONTACT_ID = #{id};
  </update>

</mapper>

What this file contains:

  • resultMap – The most complicated and powerful element that describes how to load your objects from the database result sets.
  • insert – A mapped INSERT statement.
  • update – A mapped UPDATE statement.
  • delete – A mapped DELEETE statement.
  • select – A mapped SELECT statement.

Result Map

The resultMap element is the most important and powerful element in MyBatis. It’s what allows you to do away with 90% of the code that JDBC requires to retrieve data from ResultSets, and in some cases allows you to do things that JDBC does not even support. In fact, to write the equivalent code for something like a join mapping for a complex statement could probably span thousands of lines of code. The design of the ResultMaps is such that simple statements don’t require explicit result mappings at all, and more complex statements require no more than is absolutely necessary to describe the relationships.

In this example, the name of the table column is different from the Contact class. That is why we have to map the column with the class property. If the column name is the same as the property, you do not need to use the column=”" option in the result map.

And remember that TypeAliases are your friend. Use them so that you don’t have to keep typing the fully qualified path of your class out. – we are going to set it in the myBatis main configuration file.

Select statment

The first select statment in this example is called “getAll“, and it means we are going to use this id to call the statment in DAO class. The other option we set is the resultMap, which we mapped to contact class, and it means the statment is going to return a list of contacts (List).

The second select statment in this example is called “getById“. We set a option called parameter of type int (or Integer) and it returns a object of type Contact. Notice the parameter notation #{id}. This tells MyBatis to create a PreparedStatement parameter. With JDBC, such a parameter would be identified by a “?” in SQL passed to a new PreparedStatement.

Delete Statment

The delete statment is also very simple. We set a parameter type called id (same thing as getById statment) so we can filter what it is going to be deleted.

Update Statment

In the update statement we ser a parameter of type Contact, which means we are going to pass a contact object as parameter to the update method in DAO class. Note the parameter notation #{name}, #{phone}, #{email} and #{id}. All the parameters must have the same name as contact properties, otherwise myBatis will not be able to map the object-parameters.

Insert Statment

Insert is a little bit more rich in that it has a few extra attributes and sub-elements that allow it to deal with key generation in a number of ways. First, if your database supports auto-generated key fields (e.g. MySQL and SQL Server), then you can simply set useGeneratedKeys=”true” and set the keyProperty to the target property and you’re done.

MyBatis has another way to deal with key generation for databases that don’t support auto-generated column types, or perhaps don’t yet support the JDBC driver support for auto-generated keys.

In this example, we are going to set manually the generated id in the object with the selectKey option. In this example, the selectKey would be run after the insert statment and with the last_insert_id() function we will get the last generated key (of type int) and set it to the id property.

3 – Mapper Configuration File

The MyBatis XML configuration file contains settings and properties that have a dramatic effect on how MyBatis behaves. The high level structure of the document is as follows:

  • Configuration
    • properties
    • settings
    • typeAliases
    • typeHandlers
    • objectFactory
    • plugins
    • environments
      • environment
        • transactionManager
        • dataSource
  • mappers

Hint: you have to follow the order above, otherwise you will get an exception.

The SqlMapConfig.xml from our project:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
	PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
	"http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

	<typeAliases>
		<typeAlias alias="Contact" type="com.loiane.model.Contact"/>
	</typeAliases>

	<environments default="development">
		<environment id="development">
		  <transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/blog"/>
				<property name="username" value="root"/>
				<property name="password" value="root"/>
			</dataSource>
	   </environment>
	</environments>

    <mappers>
  	   <mapper resource="com/loiane/data/Contact.xml"/>
    </mappers>

</configuration>

Let’s take a look at the configuration properties we are using.

Type Aliases

A type alias is simply a shorter name for a Java type. It’s only relevant to the XML configuration and simply exists to reduce redundant typing of fully qualified classnames.

Remember we used Contact as type in the resultMap property in Contact.xml ()? This is a great help!

Environments

MyBatis can be configured with multiple environments. This helps you to apply your SQL Maps to multiple databases for any number of reasons. For example, you might have a different configuration for your Development, Test and Production environments. Or, you may have multiple production databases that share the same schema, and you’d like to use the same SQL maps for both. There are many use cases.

One important thing to remember though: While you can configure multiple environments, you can only choose ONE per SqlSessionFactory instance.

The default environment and the environment IDs are self explanatory. Name them whatever you like, just make sure the default matches one of them.

Transaction Manager

There are two TransactionManager types (i.e. type=”[JDBC|MANAGED]”) that are included with MyBatis:

  • JDBC – This configuration simply makes use of the JDBC commit and rollback facilities directly. It relies on the connection retrieved from the dataSource to manage the scope of the transaction.
  • MANAGED – This configuration simply does almost nothing. It never commits, or rolls back a connection. Instead, it lets the container manage the full lifecycle of the transaction (e.g. Spring or a JEE Application Server context). By default it does close the connection. However, some containers don’t expect this, and thus if you need to stop it from closing the connection, set the closeConnection property to false.

In this example we are going to use JDBC.

Data Source

The dataSource element configures the source of JDBC Connection objects using the standard JDBC DataSource interface.

  • driver – This is the fully qualified Java class of the JDBC driver (NOT of the DataSource class if your driver includes one).
  • url – This is the JDBC URL for your database instance.
  • username – The database username to log in with.
  • password – The database password to log in with.

4 – MyBatisConnectionFactory

Every MyBatis application centers around an instance of SqlSessionFactory. A SqlSessionFactory instance can be acquired by using the SqlSessionFactoryBuilder. SqlSessionFactoryBuilder can build a SqlSessionFactory instance from an XML configuration file, of from a custom prepared instance of the Configuration class.

package com.loiane.dao;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisConnectionFactory {

	private static SqlSessionFactory sqlSessionFactory;

	static {
		try {

			String resource = "SqlMapConfig.xml";
			Reader reader = Resources.getResourceAsReader(resource);

			if (sqlSessionFactory == null) {
				sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
			}
		}
		catch (FileNotFoundException fileNotFoundException) {
			fileNotFoundException.printStackTrace();
		}
		catch (IOException iOException) {
			iOException.printStackTrace();
		}
	}

	public static SqlSessionFactory getSqlSessionFactory() {

		return sqlSessionFactory;
	}
}

5 – ContactDAO

Now that we set up everything needed, let’s create our DAO. To call the sql statments, we need to call the namespace and the name of the SQl statment as follows:

package com.loiane.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.loiane.model.Contact;

public class ContactDAO {

	private SqlSessionFactory sqlSessionFactory;

	public ContactDAO(){
		sqlSessionFactory = MyBatisConnectionFactory.getSqlSessionFactory();
	}

	/**
	 * Returns the list of all Contact instances from the database.
	 * @return the list of all Contact instances from the database.
	 */
	@SuppressWarnings("unchecked")
	public List<Contact> selectAll(){

		SqlSession session = sqlSessionFactory.openSession();

		try {
			List<Contact> list = session.selectList("Contact.getAll");
			return list;
		} finally {
			session.close();
		}
	}

	/**
	 * Returns a Contact instance from the database.
	 * @param id primary key value used for lookup.
	 * @return A Contact instance with a primary key value equals to pk. null if there is no matching row.
	 */
	public Contact selectById(int id){

		SqlSession session = sqlSessionFactory.openSession();

		try {
			Contact contact = (Contact) session.selectOne("Contact.getById",id);
			return contact;
		} finally {
			session.close();
		}
	}

	/**
	 * Updates an instance of Contact in the database.
	 * @param contact the instance to be updated.
	 */
	public void update(Contact contact){

		SqlSession session = sqlSessionFactory.openSession();

		try {
			session.update("Contact.update", contact);
			session.commit();
		} finally {
			session.close();
		}
	}

	/**
	 * Insert an instance of Contact into the database.
	 * @param contact the instance to be persisted.
	 */
	public void insert(Contact contact){

		SqlSession session = sqlSessionFactory.openSession();

		try {
			session.insert("Contact.insert", contact);
			session.commit();
		} finally {
			session.close();
		}
	}

	/**
	 * Delete an instance of Contact from the database.
	 * @param id primary key value of the instance to be deleted.
	 */
	public void delete(int id){

		SqlSession session = sqlSessionFactory.openSession();

		try {
			session.delete("Contact.deleteById", id);
			session.commit();
		} finally {
			session.close();
		}
	}
}

Download

If you want to learn more about the MyBatis configuration options, please read the User Guide. You will find everything you need there. All the quoted sentences are from the MyBatis 3 User Guid. I also used it as reference to implement this sample project.

I also created a TestCase class. If you want to download the complete sample project, you can get it from my GitHub account: https://github.com/loiane/ibatis-helloworld

If you want to download the zip file of the project, just click on download:

donwload github example loiane Getting Started with iBatis (MyBatis): XML Configuration

Next articles we are going to explore more iBatis/MyBatis options! icon smile Getting Started with iBatis (MyBatis): XML Configuration

Happy Coding!

Filed in: iBatis (MyBatis) | Tags: , , , , , , , ,

Comments (9)

  1. Carlos E. Morales M.

    Excelente guía de primeros pasos, bastante completa y fácil de entender. Gracias por tomar parte de tu tiempo en hacer semejante aporte.

    Saludos

  2. Thanks for this example
    There is a bug: wrong order, must be: VALUES (#{email}, #{name}, #{phone});

  3. Supriya

    Hi Loiane,
    Great job once again! Keep it up, All the best! – supriya

  4. Madz

    Hi,
    Thankx a lot for the tutorial. Helped me a great deal in writting my first mybatis java project. :)

  5. Beschi

    Great article and good learning point

Leave a Reply

Trackback URL | RSS Feed for This Entry