MyBatis DAO Example Code Tutorial

r囧r小猫 2022-08-09 05:16 93阅读 0赞

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][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>{
    
     publicT get(PK id) throwsPersistenceException;//get obj of type T by the primary key 'id'
    
     publicT getByName(String name) throwsPersistenceException;//get obj of type T by the 'name' field, if one exists for that table
    
     publicArrayList<T> getAll() throwsPersistenceException;//get all objects of type T
    
     publicint create(T objInstance)throws PersistenceException;//insert an object of type T into the database
    
     intupdate(T transientObject) throwsPersistenceException; //update an object of type T   
    
     intdelete(PK id)  throwsPersistenceException;//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

<table> 
 <tbody> 
  <tr> 
   <th>Java Method</th> 
   <th>MyBatis Query Id</th> 
   <th>Convention</th> 
  </tr> 
  <tr> 
   <td>dao.get(1)</td> 
   <td>&lt;select id="getStatus" ...</td> 
   <td>get<strong>ClassName</strong></td> 
  </tr> 
  <tr> 
   <td>dao.getAll()</td> 
   <td>&lt;select id="getAllStatus" ...</td> 
   <td>getAll<strong>ClassName</strong></td> 
  </tr> 
  <tr> 
   <td>dao.getByName(String name)</td> 
   <td>&lt;select id="getStatusByName" ...</td> 
   <td>get<strong>ClassName</strong>ByName</td> 
  </tr> 
  <tr> 
   <td>dao.create(obj)</td> 
   <td>&lt;insert id="createStatus" ...</td> 
   <td>create<strong>ClassName</strong></td> 
  </tr> 
  <tr> 
   <td>dao.update(obj)</td> 
   <td>&lt;update id="updateStatus" ...</td> 
   <td>update<strong>ClassName</strong></td> 
  </tr> 
  <tr> 
   <td>dao.delete(id)</td> 
   <td>&lt;delete id="deleteStatus" ...</td> 
   <td>delete<strong>ClassName</strong></td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <th>ID</th> 
   <th>NAME</th> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Done</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>In Progress</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Not Started</td> 
  </tr> 
 </tbody> 
</table>

And the java class (or DTO object) would look like this (Status.java)

public  class Status 
    
    {
    
     privateintid;
    
     privateString name;
    
      
    
     publicintgetId() {
    
      returnid;
    
     }
    
     publicvoidsetId(intid) {
    
      this.id = id;
    
     }
    
     publicString getName() {
    
      returnname;
    
     }
    
     publicvoidsetName(String name) {
    
      this.name = name;
    
     }
    
      
    
     @Override
    
     publicString 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 extendsMyBatisDAO<Status,Integer>{
    
      
    
     //Don't forget to define the default custom constructor when implementing a new
    
     //child DAO class, and set the class type accordingly
    
     publicStatusDAO(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][github here].  
*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**

<?xmlversion="1.0"encoding="UTF-8"?>
    
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
     
    
    <mappernamespace="mappers">
    
      
    
     <selectid="getStatus"parameterType="int"resultType="Status">
    
      select
    
       status_id as "id",
    
       status_name as "name"
    
      from daohelper.status where status_id = #{id}
    
     </select>
    
      
    
     <selectid="getAllStatus"parameterType="int"resultType="Status">
    
      select
    
       status_id as "id",
    
       status_name as "name"
    
      from daohelper.status order by status_id
    
     </select>
    
      
    
     <selectid="getStatusByName"parameterType="int"resultType="Status">
    
      select
    
       status_id as "id",
    
       status_name as "name"
    
      from daohelper.status where status_name = #{name}
    
     </select>
    
      
    
     <insertid="createStatus"keyColumn="status_id"useGeneratedKeys="true"parameterType="Status">
    
      insert into daohelper.status (status_name)
    
      values (#{name}) 
    
     </insert>
    
      
    
     <updateid="updateStatus"parameterType="Status">
    
      update daohelper.status set status_name = #{name} where status_id = #{id} 
    
     </update>
    
      
    
     <deleteid="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][github here] 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][]

### Hope you found this useful. Improvements or suggestions are welcome. ###

[Generic implementation of the DAO pattern]: http://www.ibm.com/developerworks/java/library/j-genericdao.html
[github here]: https://github.com/idleworx/MyBatisDAOHelper
[Logback]: http://logback.qos.ch/
[defining a servletcontextlistener]: http://blog.idleworx.com/2010/06/initialize-mybatis-servletcontextlisten.html