This tutorial will show how how to integrate MyBatis with the Data Access Object pattern (DAO) and MySQL for use in Java Web Apps.
In case you're not familiar, MyBatis is the new version of the iBatis Data Mapper Java Framework, which allows you to use a relational database with object-oriented applications.
If you're not familiar with the DAO pattern or the benefits of using it read more about it here Generic implementation of the DAO pattern.
Full code of this mybatis dao example tutorial is available on github here.
Step 1 - Define the interface
Create an IParentDAO interface to define the main method calls for all objects. This interface can be used as the parent for various DAO implementations. Here are the default CRUD methods that all objects will have
get(id) getByName(name) getAll() create(obj) update(object) delete(id)
Create the IParentDAO.java class
public interface IParentDAO<T, PK>{
public T get(PK id) throws PersistenceException;//get obj of type T by the primary key 'id'
public T getByName(String name) throws PersistenceException;//get obj of type T by the 'name' field, if one exists for that table
public ArrayList<T> getAll() throws PersistenceException;//get all objects of type T
public int create(T objInstance) throws PersistenceException;//insert an object of type T into the database
int update(T transientObject) throws PersistenceException; //update an object of type T
int delete(PK id) throws PersistenceException;//delete an object of type T
}
Step 2 - Create the base class
Create the MyBatisDAO abstract base class. This will be the default MyBatis implementation of the DAO. You can of course write a different one for JDBC, Spring etc. Feel free to remove the logging code if you don't need it. I used Logback for that.
import java.util.ArrayList;
import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Class contains all the basic CRUD related methods which are inherited by all objects
* Children daos should generally not overwrite these method but add extra ones as needed.
*/
public abstract class MyBatisDAO<T, PK> implements IParentDAO<T, PK>{
private static Logger log = LoggerFactory.getLogger(MyBatisDAO.class);
private static final String NAMESPACE = "mappers";
private SqlSessionFactory sf; //reference to mybatis session factory
private Class<T> type;
/**
* Define prefixes for easier naming convetions between XML mapper files and the DAO class
**/
public static final String PREFIX_SELECT_QUERY = "get"; //prefix of select queries in mapper files (eg. getAddressType)
public static final String PREFIX_INSERT_QUERY = "create"; //prefix of create queries in mapper files (eg. createAddressType)
public static final String PREFIX_UPDATE_QUERY = "update"; //prefix of update queries in mapper files (eg. updateAddressType)
public static final String PREFIX_DELETE_QUERY = "delete"; //prefix of delete queries in mapper files (eg. deleteAddressType)
/** Default Constructor */
public MyBatisDAO(Class<T> type,SqlSessionFactory sf) {
this.type = type;
this.sf = sf;
if(sf==null)
log.error("Error: Could not instantiate MyBatisDAO. Loading myBatis sessionFactory failed.");
}
/** Use this method to get a session factory for using in any methods impelmented in child dao classes */
protected SqlSessionFactory getSessionFactory() {
return sf;
}
/**
* Default get by id method.
* </br></br>
* Almost all objects in the db will
* need this (except mapping tables for multiple joins, which you
* probably shouldn't even have as objects in your model, since proper
* MyBatis mappings can take care of that).
* </br></br>
* Example:
* </br>
* If your DAO object is called CarInfo.java,
* the corresponding mapper query id should be: <select id="getCarInfo" ...
*/
public T get(PK id) throws PersistenceException {
SqlSession session = sf.openSession();
T obj = null;
try
{
String query = NAMESPACE+"."+PREFIX_SELECT_QUERY+this.type.getSimpleName(); //If the object's calls name is AddressType.java, this matches the mapper query id: "namespace.getAddressType"
obj = (T)session.selectOne(query,id);
}
finally
{
session.close();
}
return obj;
}
/**
* Method returns all rows for this object.
* </br></br>
* Example:
* </br>
* If your DAO object is called CarInfo.java,
* the corresponding mapper query id should be: <select id="getAllCarInfo" ...
* </br></br>
* SQL Executed: select * from [tablename]
* </br></br>
* Notes:
* </br>
* Consider overdiding this method in order to handle large numbers of objects
* with multiple references.
* LAZY LOADING should be enabled in this case, otherwise you might run out of memory (eg. get all UserAccounts if the table has 1,000,000 rows)
* look into the aggresiveLazyLoading property
* */
public ArrayList<T> getAll() throws PersistenceException {
SqlSession session = sf.openSession();
ArrayList<T> list = null;
try
{
String query = NAMESPACE+"."+PREFIX_SELECT_QUERY+"All"+this.type.getSimpleName();
list = (ArrayList<T>)session.selectList(query);
}
finally
{
session.close();
}
return list;
}
/**
* Method returns first object which matches the given name (exact match).
* </br></br>
* It's up to you to decide what constitutes an object's name. Typically you would have a
* NAME column in the table, but not all objects have this. Generally this method should be overriden (if you need it at all)
* in the child dao class.
* </br></br>
* Example:
* </br>
* If your DAO object is called CarInfo.java,
* the corresponding mapper query id should be: <select id="getCarInfoByName" ...
* </br></br>
* SQL Executed (example): select * from [tablename] where NAME = ?
*
*/
public T getByName(String name) throws PersistenceException {
SqlSession session = sf.openSession();
T obj = null;
try
{
String query = NAMESPACE+"."+PREFIX_SELECT_QUERY+this.type.getSimpleName()+"ByName";
obj = (T)session.selectOne(query,name);
}
finally
{
session.close();
}
return obj;
}
/**
* Method inserts the object into the table.
* </br></br>
* You will usually override this method, especially if you're inserting associated objects.
* </br>
* Example:
* </br>
* If your DAO object is called CarInfo.java,
* the corresponding mapper query id should be: <insert id="createCarInfo" ...
* </br></br>
* SQL Executed (example): insert into [tablename] (fieldname1,fieldname2,...) values(value1,value2...) ...
*
*/
public int create(T o) throws PersistenceException{
SqlSession session = sf.openSession();
Integer status = null;
try
{
String query = NAMESPACE+"."+PREFIX_INSERT_QUERY+o.getClass().getSimpleName();
status = (Integer)session.insert(query,o);
session.commit();
}
finally
{
session.close();
}
return status;
}
/**
* Method updates the object by id.
* </br></br>
* You will usually override this method. But it can be used for simple objects.
* </br>
* Example:
* </br>
* If your DAO object is called CarInfo.java,
* the corresponding mapper query id should be: <update id="updateCarInfo" ...
* </br></br>
* SQL Executed (example): update [tablename] set fieldname1 = value1 where id = #{id}
*
*/
public int update(T o)throws PersistenceException {
SqlSession session = sf.openSession();
Integer status = null;
try
{
String query = NAMESPACE+"."+PREFIX_UPDATE_QUERY+o.getClass().getSimpleName();
status = session.update(query,o);
session.commit();
}
finally
{
session.close();
}
return status;
}
/**
* Method deletes the object by id.
* </br></br>
* Example:
* </br>
* If your DAO object is called CarInfo.java,
* the corresponding mapper query id should be: <delete id="deleteCarInfo" ...
* </br></br>
* SQL Executed (example): update [tablename] set fieldname1 = value1 where id = #{id}
*
*/
public int delete(PK id) throws PersistenceException{
SqlSession session = sf.openSession();
Integer status = null;
try
{
String query = NAMESPACE+"."+PREFIX_DELETE_QUERY+this.type.getSimpleName();
status = session.delete(query,id);
session.commit();
}
finally
{
session.close();
}
return status;
}
}
Naming Convetions
You'll notice there are four prefix constants defined in the class above.
The reason for this is to keep consistency between the sql query ids you will define in the mybatis mapper.xml files (see Step 4) and the method names defined in the MyBatisDAO class we're implementing.
This won't work exactly like ActiveRecord or similar frameworks where there is a pluralization engine but it will still simplify things a lot.
For example, if you have an object called Status for which you will create a DAO, you will have to define the following mybatis querries
| Java Method | MyBatis Query Id | Convention |
|---|---|---|
| dao.get(1) | <select id="getStatus" ... | getClassName |
| dao.getAll() | <select id="getAllStatus" ... | getAllClassName |
| dao.getByName(String name) | <select id="getStatusByName" ... | getClassNameByName |
| dao.create(obj) | <insert id="createStatus" ... | createClassName |
| dao.update(obj) | <update id="updateStatus" ... | updateClassName |
| dao.delete(id) | <delete id="deleteStatus" ... | deleteClassName |
Don't worry, this will make a lot more sense once you get to Step 4
Step 3 - Write the actual DAO classes
Now you need to implement your concrete DAO classes.
Let's say you have a simple object called Status which maps to a simple table, and has 2 attributes, an ID and a NAME. Think of it as an object you could use to represent the status of a task. I chose this to illustrate a very basic example here
The table for this object would look like this
| ID | NAME |
|---|---|
| 1 | Done |
| 2 | In Progress |
| 3 | Not Started |
And the java class (or DTO object) would look like this (Status.java)
public class Status
{
private int id;
private String 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;
}
@Override
public String toString() {
return "[Status] " + "(" + id + ") " + name;
}
}
Writing the DAO class for the Status object now becomes trivial since you're inheriting all the default CRUD methods from MyBatisDAO.java defined above.
Here's how the StatusDAO.java should look:
import org.apache.ibatis.session.SqlSessionFactory;
import com.idleworx.mybatisdao.MyBatisDAO;
import com.idleworx.mybatisdao.tests.objects.Status;
public class StatusDAO extends MyBatisDAO<Status,Integer>{
//Don't forget to define the default custom constructor when implementing a new
//child DAO class, and set the class type accordingly
public StatusDAO(Class<Status> type,SqlSessionFactory containerSessionFactory) {
super(type,containerSessionFactory);
}
}
You'll notice here that all you need to do is call the constructor from the parent MyBatisDAO.java class. Now all the default CRUD methods are available for the StatusDAO.java class.
You are free of course to add any additional methods as needed here that go beyond the CRUD methods that were defined. For example something like
getStatusForBlog(Integer blogId).
See the full code example.
Both the MyBatisDAO.java and IParentDAO.java classes are included.
Of course you will still have to define the MyBatis mapper SQL statements for the default CRUD methods defined in MyBatisDAO.java, as well as additional SQLs for other DAO methods you choose to implement. Which brings us to the next step ...
Step 4 - Defining the mybatis mapper
The last major step in the puzzle, is writing the actual SQL that will implement all the default CRUD methods and any other methods you've implemented for the Status object.
Your code may differ of course, but note the use of the naming conventions for naming the sql statements which I talked about earlier.
That is the key to this whole thing, and it will make your life a lot easier when writing other DAOs which extend the MyBatisDAO class.
Here is an example of how the mapper file for the StatusDAO object would look like.
StatusMapper.xml
<?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="mappers">
<select id="getStatus" parameterType="int" resultType="Status">
select
status_id as "id",
status_name as "name"
from daohelper.status where status_id = #{id}
</select>
<select id="getAllStatus" parameterType="int" resultType="Status">
select
status_id as "id",
status_name as "name"
from daohelper.status order by status_id
</select>
<select id="getStatusByName" parameterType="int" resultType="Status">
select
status_id as "id",
status_name as "name"
from daohelper.status where status_name = #{name}
</select>
<insert id="createStatus" keyColumn="status_id" useGeneratedKeys="true" parameterType="Status">
insert into daohelper.status (status_name)
values (#{name})
</insert>
<update id="updateStatus" parameterType="Status">
update daohelper.status set status_name = #{name} where status_id = #{id}
</update>
<delete id="deleteStatus" parameterType="int">
delete from daohelper.status where status_id = #{id}
</delete>
</mapper>
Note here the use of the mapper namespace which is the same as referenced by the MyBatisDAO abstract class.
It's used for convenience in this case, and generally you should be able to use multiple mapper files with the same namespace as long as the you don't have multiple queries with the same id across several xml mapper files (which would be a bad idea anyway)
Errors
Just a side note on some errors you may encounter.
If you call one of the default CRUD methods but don't define the appropriate query in the mapper.xml file, you will get an error like this:
org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for mappers.getAllFrog ### Cause: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for mappers.getAllFrog
Wrapping it up
By this point I hope you've managed to build yourself a small but working DAO implementation based on the MyBatisDAO abstract class.
You can download the full code example for more a better overview.
It also contains a jUnit example, the mybatis configuration file (mybatis.config.xml) as well as the simple DDL statments for setting up the MySQL table.
Note:If you need to use this from a Servlet or Spring controller, see my other blog post about defining a servletcontextlistener