Native database support with MyBatis + Spring 3

Say, we may be building a new application while keeping the old database where a bunch of stored procedures are used (so, the database has most of the business logic too), or building a new whole application and we don't want to use an ORM like Hibernate cause we don't care about database coupling; we just want to have control of all of our queries... Well, in these cases we may want to take a look at MyBatis which really makes it easy to use native queries while still allowing us to take full control of queries being executed. We are not looking at MyBatis alone, we also look at it's integration with Spring which eases the whole configuration of this product.

So, we will see an example of a persistence layer being implemented with MyBatis + Spring with some explanation on the relevant sections. The full example can be downloaded too. So, let's see the example!

Versions used:

• Spring 3.1.1-RELEASE
• MyBatis-spring 1.1.0

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>

 <groupId>ar.com.pabloExample</groupId>
 <artifactId>spring-mybatis-example</artifactId>
 <version>1.0-SNAPSHOT</version>
 <packaging>jar</packaging>

 <name>spring-mybatis-example</name>
 <url>http://maven.apache.org</url>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 </properties>

 <repositories>
  <repository>
   <id>springsource-repo</id>
   <name>SpringSource Repository</name>
   <url>http://repo.springsource.org/release</url>
  </repository>
 </repositories>

 <dependencies>

  <dependency>
   <groupId>commons-dbcp</groupId>
   <artifactId>commons-dbcp</artifactId>
   <version>1.4</version>
   <scope>test</scope>
  </dependency>

  <dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis-spring</artifactId>
   <version>1.1.0</version>
   <scope>test</scope>
  </dependency>

  <!-- Here we use PostgreSQL, you may use another database but you must 
   check -->
  <!-- all your sql sentences cause you are attached to the database -->
  <dependency>
   <groupId>postgresql</groupId>
   <artifactId>postgresql</artifactId>
   <version>9.1-901.jdbc4</version>
   <scope>test</scope>
  </dependency>

  <!-- Spring dependencies -->

  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-context</artifactId>
   <version>3.1.1.RELEASE</version>
   <scope>test</scope>
  </dependency>

  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-test</artifactId>
   <version>3.1.1.RELEASE</version>
   <scope>test</scope>
  </dependency>

  <dependency>
   <groupId>junit</groupId>
   <artifactId>junit</artifactId>
   <version>4.8.2</version>
   <scope>test</scope>
  </dependency>

 </dependencies>

 <build>
  <plugins>
   <plugin>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>2.3.2</version>
    <configuration>
     <source>1.6</source>
     <target>1.6</target>
    </configuration>
   </plugin>
  </plugins>
 </build>

</project>

Book.java

public class Book {
 
 private Integer id;
 private String name;
 private List<Author> authors;

 //Getters and setters ommited but must exist
}

Author.java

public class Author {
 
 private Integer id;
 private String name;
 
 //Getters and setters ommited but must exist
}

BookDao.java

public interface BookDao {

 public Book getBook(Integer id);

 public List<book> getAllBooks();
 
 public int returnNumberOfBooks(Map<String, Object> map);
}

We will only create the DAO interfaces. No need to create any DAO implementation, it is done automatically by MyBatis!

BookDao.xml

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="ar.com.pabloExample.interfaces.BookDao">

 <!-- This example returns a book by id -->
 
 <select id="getBook" resultType="ar.com.pabloExample.model.Book" parameterType="int" >
  SELECT p_id as ID, a_name as NAME
  FROM "T_BOOK"
  WHERE p_id = #{id}
 </select>
 
 
 <!-- This example returns a list of books and shows usage of resultMap -->
 
 <select id="getAllBooks" resultMap="resultMap_getAllBooks" parameterType="int" >
  SELECT p_id, a_name
  FROM "T_BOOK"
 </select>
 
 <resultMap id="resultMap_getAllBooks" type="ar.com.pabloExample.model.Book">
  <id property="id" column="p_id"/>
  <result property="name" column="a_name"/>
  <collection property="authors" column="p_id" select="getAuthorsByBookId" />
 </resultMap>
 
 <select id="getAuthorsByBookId" parameterType="int" resultType="ar.com.pabloExample.model.Author" >
  SELECT p_id as ID, a_name as NAME
  FROM "T_AUTHOR_BOOK", "T_BOOK"
  WHERE p_id = #{id} and f_book_id = p_id
 </select>
 
 
 <!-- This example shows usage of input map -->
 
 <select id="returnNumberOfBooks" parameterType="map" resultType="int">
  SELECT COUNT(*) FROM "T_BOOK" WHERE 1 = #{param1} AND 2 = #{param2}
 </select>
 
</mapper>

In order MyBatis to automatically create DAO implementations behind the scene, each <select ...> tag has to match a DAO interface method.

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
  http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

 <bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="org.postgresql.Driver" />
  <property name="url" value="jdbc:postgresql://localhost:5432/mybatistest" />
  <property name="username" value="postgres" />
  <property name="password" value="123456" />
 </bean>

 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="configLocation" value="mybatis-config.xml"/>
  <property name="dataSource" ref="datasource" />
 </bean>

 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  <property name="basePackage" value="ar.com.pabloExample.interfaces" />
 </bean>

 <!-- This is a more manual way to create each DAO impl beans with  MapperFactoryBean  -->
 <!-- instead of using MapperScannerConfigurer -->

 <!--  <bean id="bookDao2" class="org.mybatis.spring.mapper.MapperFactoryBean"> -->
 <!--   <property name="mapperInterface" value="ar.com.pabloExample.interfaces.BookDao" /> -->
 <!--   <property name="sqlSessionFactory" ref="sqlSessionFactory" /> -->
 <!--  </bean> -->

 </beans>

The first bean is the datasource, the others are for mybatis. The SqlSessionFactoryBean contains a reference to the datasource and also a file where we can set parameters for mybatis. The MapperScannerConfigurer will automatically scan the whole classpath for xmls.

mybatis-config.xml

<?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>
 <settings>
  <setting name="cacheEnabled" value="false" />
  <setting name="lazyLoadingEnabled" value="false" />
 </settings>
</configuration>

These are just some settings for the configuration file. Remember this file is not a must.

Let's test all the DAO methods!

BookDaoTest.java

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "/applicationContext.xml" })
public class BookDaoTest {
 
 @Autowired
// @Qualifier("bookDao2") add this only if using MapperFactoryBean instead of MapperScannerConfigurer
 private BookDao bookDao;
 
 @Autowired
 @Qualifier("datasource")
 private BasicDataSource datasource;

 
 @Before
 public void connectionTest() throws SQLException {
  
  datasource.getConnection();
 }
 
 @Test
 public void getBookByIdTest() {
  
  Assert.assertNotNull(bookDao);
  Book book1 = bookDao.getBook(0);
  
  Assert.assertNotNull("Book returned is null", book1);
  Assert.assertTrue("Returned another book", book1.getName().equals("EJB3 in Action"));
 }
 
 @Test
 public void getAllBooksTest() {
  
  Assert.assertNotNull(bookDao);
  List<Book> list = bookDao.getAllBooks();
  
  Assert.assertNotNull("List of books returned null", list);
  Assert.assertTrue("List of books is empty", !list.isEmpty());
  Assert.assertNotNull("Valid book", list.get(0));
  Assert.assertNotNull("ID not loaded", list.get(0).getId());
  Assert.assertNotNull("NAME not loaded", list.get(0).getName());
  Assert.assertNotNull("List of authors null", list.get(0).getAuthors());
  Assert.assertTrue("List of authors is empty", !list.get(0).getAuthors().isEmpty());
  Assert.assertNotNull("ID of author is null", list.get(0).getAuthors().get(0).getId());
  Assert.assertNotNull("NAME of author is null", list.get(0).getAuthors().get(0).getName());
 }
 
 @Test
 public void returnNumberOfBooksTest() {
  
  Assert.assertNotNull(bookDao);
  
  Map<String, Object> map = new HashMap<String, Object>();
  map.put("param1", 1);
  map.put("param2", 2);
  int result = bookDao.returnNumberOfBooks(map);
  
  Assert.assertTrue(result > 0);
 }
 
}

You may have noticed we inject "datasource" which can be found as a bean in applicationContext.xml but we inject "bookDao" (No @Qualifier will take "bookDao" implicitly) too but cannot see any bean of BookDao type in any XML file. Why? Well, remember this is because "bookDao" implementation bean is automatically created by MyBatis at runtime (we didn't create any DAO implementation!). How it is achieved? When the MapperScannerConfigurer scans for the DAOs.

Before running the example run the database script generation for Postgresql and add some data to the tables!
Then run it through your IDE or run from a console: mvn test

Get the complete code (including the database script generator) at:

https://subversion.assembla.com/svn/pablo-examples/spring-mybatis-example

You can also check the official example in mybatis site here