MyBatis + Spring + Junit (The Missing Part)

We have seen in Mybatis + Spring (XML | Annotation) Mapper how to write a simple mybatis-spring app and what is actually needed to get it up and running. Here I will extend that sample code to cover more stuff that you may face in a real “coding”. We will see how to make a single select that will fetch the one and the many of a “one-to-many” relationship, who to use mybatis config file with spring (if you need it), how to test mybatis-spring code using junit and things you need to watch out!

H2 embedded database has been used to make this example easy to run. But you still can use it with other databases e.g. “MySql, Oracle,….”

mybatis-spring-junit

Objectives:

  • How to use make a single select that will fetch all one to many records?
  • How to reference mybatis config file in spring xml config file?
  • How to test using junit?

Environment:

  • Eclipse
  • Maven

Libraries:

  • Spring Framework
  • MyBatis
  • MyBatis for Spring
  • Junit (4.11)
  • H2
  • For complete list of jar files: .classpath
  • For complete list of <dependencies>: pom.xml

( 1 ) Project Structure

This project was generated using Maven and converted into Eclipse.

Database tables structure

  • Two tables (parent & child)
CREATE TABLE parent (parentId INT PRIMARY KEY AUTO_INCREMENT, parentName VARCHAR(255));
CREATE TABLE child (childId INT PRIMARY KEY AUTO_INCREMENT, parentId INT, childName VARCHAR(255));

( 2 ) What to Code?

  • spring-config.xml (required)
  • Mapper.java & Mapper.xml (required)
  • mybatis-config.xml (optional) as needed!
  • Service.java (decorator) make our code cleaner! you may call it e.g. Factory.java
  • Parent.java & Child.java (model, beans or entities) for this example.
  • Schema.sql & test-data.sq (data model & sample data) for this example. Used by H2 db on instantiation! (H2 create tables and fill it with data when we start the app and destroy them when we close the app)
  • App.java (main class) run our example
  • AppTest.java (test class)
  • src/main/resources/spring-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:oxm="http://www.springframework.org/schema/oxm"
	xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:jee="http://www.springframework.org/schema/jee"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-3.1.xsd


http://www.springframework.org/schema/context


http://www.springframework.org/schema/context/spring-context-3.1.xsd


http://www.springframework.org/schema/tx


http://www.springframework.org/schema/tx/spring-tx-3.1.xsd


http://www.springframework.org/schema/aop


http://www.springframework.org/schema/aop/spring-aop-3.1.xsd


http://www.springframework.org/schema/oxm


http://www.springframework.org/schema/oxm/spring-oxm-3.1.xsd


http://www.springframework.org/schema/jdbc


http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd"

       >

	<context:annotation-config />
	<tx:annotation-driven />

	<!-- For MySQL -->
    <!-- bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <property name="driverClassName" value="com.mysql.jdbc.Driver" />  
       <property name="url" value="jdbc:mysql://localhost:3306/database" />  
       <property name="username" value="" />  
       <property name="password" value ="" /> 
    </bean -->

	<!-- ( 1 ) datasource -->
    <jdbc:embedded-database id="dataSource" type="H2" >
        <jdbc:script location="classpath:schema.sql"/>
        <jdbc:script location="classpath:test-data.sql"/>
    </jdbc:embedded-database>

    <!-- ( 2 ) sqlSessionFactory (dataSource, configLocation "optional") -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  		<property name="dataSource" ref="dataSource" />
  		<property name="configLocation" value="classpath:com/hmkcode/spring/mybatis/mybatis-config.xml" />  
	</bean>

	<!-- ( 3 ) mapper (sqlSessionFactory, Mapper) -->
	<bean id="mapper" class="org.mybatis.spring.mapper.MapperFactoryBean" >
  		<property name="mapperInterface" value="com.hmkcode.spring.mybatis.mapper.Mapper" />
  		<property name="sqlSessionFactory" ref="sqlSessionFactory" />
	</bean>

	<bean id="service" class="com.hmkcode.spring.mybatis.service.Service" />

</beans>
  • src/main/java/com/hmkcode/spring/mybatis/mapper/Mapper.java
package com.hmkcode.spring.mybatis.mapper;

import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.hmkcode.spring.mybatis.vo.Child;
import com.hmkcode.spring.mybatis.vo.Parent;;

public interface Mapper {

	//if @Select is used table/column name and class/property name should be the same

	//SQL query in "Mapper.xml"
	public List<Parent> selectAllParent();

	//SQL query in "Mapper.xml"
	public Parent selectParent(@Param("id") int id);

	@Insert("INSERT INTO parent (parentName) VALUES (#{parentName})")
	public int insertParent(Parent parent);

	@Select("SELECT * FROM child WHERE parentId = #{id}")
	public List<Child> selectAllChildren(@Param("id") int id);

	@Select("SELECT * FROM child WHERE childId = #{id}")
	public Child selectChild(@Param("id") int id);

	//SQL query in "Mapper.xml"
	public int insertchild(Child child);		
}
  • src/main/resources/com/hmkcode/spring/mybatis/mapper/Mapper.xml

Notice the use of <collection…> this will fetch all children of when each parent is fetched

<?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="com.hmkcode.spring.mybatis.mapper.Mapper">

	<!-- Mapping Parent -->
	<resultMap type="com.hmkcode.spring.mybatis.vo.Parent" id="Parent">
		<id column="parentId" property="parentId"  />
		<result column="parentName" property="parentName"  />	

		<!-- if collection is used SQL should be in XML -->
		<collection property="children" column="parentId" select="selectAllChildren" javaType="ArrayList" />
	</resultMap>

	<!-- Mapping Child -->
	<resultMap type="com.hmkcode.spring.mybatis.vo.Child" id="Child">
		<id column="childId" property="childId"  />
		<result column="parentId" property="parentId"  />
		<result column="childName" property="childName"  />
	</resultMap>

	<!-- ************************************************************************************* -->

	<!-- Mapper.selectAllParent() -->
	<select id="selectAllParent" resultMap="Parent" timeout="10">
		SELECT * FROM parent
	</select>

	<!-- Mapper.selectParent() -->
	<select id="selectParent" resultMap="Parent" parameterType="int" timeout="10">
		SELECT * FROM parent WHERE parentId= #{id}
	</select>

	<!-- Mapper.insertchild() -->
	<insert id="insertchild" parameterType="Child" timeout="10">
		INSERT INTO child
			(parentId,childName)
			VALUES 
			(#{parentId},#{childName})
	</insert>
</mapper>
  • src/main/resources/com/hmkcode/spring/mybatis//mybatis-config.xml

Read more about MyBatis Configuration

Why we you need this file?

We want to create aliases for “model” classes (Parent.java & Child.java) so when we need to refer to them we don’t need to write the complete namespace “package+class name” e.g. in the Mapper.xml we refer to Child in <insert id=”insertchild” parameterType=”Child” timeout=”10″> if we did not create the aliases in mybatis-config.xml we would write the following <insert id=”insertchild” parameterType=”com.hmkcode.spring.mybatis.vo.Child” timeout=”10″>

Now, for the aliases either to specify classes full name or the their package. If you specify the package, classes should be annotated with @Alias(“….”).

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

<configuration>
	<typeAliases>

	 <!-- use package to scan all classes annotated with @Aliase -->
	  <package name="com.hmkcode.spring.mybatis.vo"/> 

	  <!--  to specify alias explicitly
	  	<typeAlias alias="Parent" type="com.hmkcode.spring.mybatis.vo.Parent"/>
  	  	<typeAlias alias="Child" type="com.hmkcode.spring.mybatis.vo.Child"/>
  	   --> 
	</typeAliases>
</configuration>
  • src/main/java/com/hmkcode/spring/mybatis/service/Service.java
package com.hmkcode.spring.mybatis.service;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import com.hmkcode.spring.mybatis.mapper.Mapper;
import com.hmkcode.spring.mybatis.vo.Child;
import com.hmkcode.spring.mybatis.vo.Parent;

public class Service {

	@Autowired 
	private Mapper mapper;

	public List<Parent> selectAllParent(){
		return mapper.selectAllParent();
	}

	public Parent selectParent(int id){
		return mapper.selectParent(id);
	}

	public int insertParent(Parent parent){
		return mapper.insertParent(parent);
	}

	public List<Child> selectAllChildren(int parnetId){
		return mapper.selectAllChildren(parnetId);
	}

	public Child selectChild(int id){
		return mapper.selectChild(id);
	}

	public int insertchild(Child child){
		return mapper.insertchild(child);
	}
}
  • src/main/java/com/hmkcode/spring/mybatis/vo/Parent.java
package com.hmkcode.spring.mybatis.vo;

import java.util.List;
import org.apache.ibatis.type.Alias;

@Alias("Parent")
public class Parent {

	private int parentId;
	private String parentName;

	private List<Child> children;

	//setters & getters

	public String toString(){
		return "\nParent [ id: "+this.parentId+" - name: "+this.parentName+" \n" +
						"\tchildren: "+this.children+
				"\n]"; 
	}
}
  • src/main/java/com/hmkcode/spring/mybatis/vo/Child.java
package com.hmkcode.spring.mybatis.vo;

import org.apache.ibatis.type.Alias;

@Alias("Child")
public class Child extends Parent
{
	private int childId;
   	private String childName;

   	//setters & getters

	public String toString(){
		return "Child [ id: "+this.childId+" - name: "+this.childName+" (PARENT_ID :"+this.getParentId()+") ]"; 
	}
}
  • src/main/resources/schema.xml
CREATE TABLE parent (parentId INT PRIMARY KEY AUTO_INCREMENT, parentName VARCHAR(255));
CREATE TABLE child (childId INT PRIMARY KEY AUTO_INCREMENT, parentId INT, childName VARCHAR(255));
  • src/main/resources/test-data.xml
INSERT INTO parent (parentName) VALUES ('parent-1')
INSERT INTO parent (parentName) VALUES ('parent-2')
INSERT INTO parent (parentName) VALUES ('parent-3')

INSERT INTO child (childName,parentId) VALUES ('child-1',1)
INSERT INTO child (childName,parentId) VALUES ('child-2',1)
INSERT INTO child (childName,parentId) VALUES ('child-3',2)
  • src/main/java/com/hmkcode/spring/mybatis/App.java
package com.hmkcode.spring.mybatis;

import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.hmkcode.spring.mybatis.service.Service;
import com.hmkcode.spring.mybatis.vo.Child;
import com.hmkcode.spring.mybatis.vo.Parent;

public class App 
{
	private static Log log = LogFactory.getLog(App.class);

    public static void main( String[] args )
    {

    	ApplicationContext cxt = new ClassPathXmlApplicationContext("spring-config.xml");
    	Service service = (Service) cxt.getBean("service");

    	log.info("Running App...");

    	//( 1 ) SELECT ALL PARENTS
    	System.out.println("( 1 ) selectAllParent()");
    	List<Parent> parents = service.selectAllParent();
    	System.out.println("-> "+parents);

    	System.out.println("****************************************************");

    	//( 2 ) SELECT ONE PARENT
    	System.out.println("( 2 ) selectParent(2)");
    	Parent parent = service.selectParent(2);
    	System.out.println("-> "+parent);

    	System.out.println("****************************************************");

    	//( 3 ) INSERT ONE PARENT
    	System.out.println("( 3 ) insertParent(parent)");
    	parent = new Parent();
    	parent.setParentName("parent-4 inserted");
    	service.insertParent(parent);
    	System.out.println("-> parent inserted");

    	System.out.println("****************************************************");

    	//( 4 ) SELECT ALL CHILDREN OF A GIVEN PARENT
    	System.out.println("( 4 ) selectAllChildren(parentId)");
    	List<Child> children = service.selectAllChildren(1);
    	System.out.println("-> "+children);

    	System.out.println("****************************************************");

    	//( 5 ) SELECT ONE CHILD
    	System.out.println("( 5 ) selectChild(1)");
    	Child child = service.selectChild(1);
    	System.out.println("-> "+child);

    	System.out.println("****************************************************");

    	//( 6 ) INSERT ONE CHILD
    	System.out.println("( 6 ) selectAllChildren(parentId)");
    	child = new Child();
    	child.setChildName("child-4 inserted");
    	child.setParentId(2);
    	service.insertchild(child);
    	System.out.println("-> child inserted");

    	System.out.println("****************************************************");

    	//( 7 ) SELECT ALL PARENTS (AGAIN!)
    	System.out.println("( 7 ) selectAllParent()");
    	parents = service.selectAllParent();
    	System.out.println("-> "+parents);

    }
}
  • src/test/java/com/hmkcode/spring/mybatis/AppTest.java
package com.hmkcode.spring.mybatis;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.hmkcode.spring.mybatis.service.Service;
import com.hmkcode.spring.mybatis.vo.Parent;

import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;;

/**
 * Unit test for simple App.
 */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "/spring-config.xml")
public class AppTest {

	private static Log log = LogFactory.getLog(AppTest.class);

	@Autowired private Service service;

	@Test
	public void testSelectAllParent(){
		log.info("testing selectAllParent()...");

		List<Parent> parents = service.selectAllParent();

		assertNotNull("failure - parents is null", parents);

		assertTrue("failure - expected 3 parents", parents.size() == 3);

		assertTrue("failure - expected 2 children", parents.get(0).getChildren().size() == 2);

		log.info(parents);
	}

	@Test
	public void testSelectParent(){
		log.info("testing selectParent()...");
		int parentId = 2;
		String parentName = "parent-2";
		Parent parent = service.selectParent(parentId);

		assertNotNull("failure - parents is null", parent);

		assertTrue("failure - parent name is not right", parent.getParentName().equals(parentName));

		assertTrue("failure - expected 1 child", parent.getChildren().size() == 1);
	}

	@Test
	public void testInsertParent(){

		log.info("testing insertParent()...");

		int parentId = 4;
		String parentName = "parent-4 inserted";

		Parent parent = new Parent();
		parent.setParentName(parentName);

		service.insertParent(parent);

		parent = service.selectParent(4);

		assertNotNull("failure - parents is null", parent);

		assertTrue("failure - parent name is not right", parent.getParentName().equals(parentName));

		assertTrue("failure - expected 0 child", parent.getChildren().size() == 0);

	}
}

( 3 ) Run & Test

Now you have many ways to run or test this sample code. This sample code is ready to run, no need to make any configuration if you have Maven setup on you machine. If you don’t, you need to add the needed jar files to the classpath.

Using Maven:

To run App.java

../spring-mybatis-junit>mvn exec:java

To run AppTest.java

../spring-mybatis-junit>mvn test

Using Eclipse:

Convert Maven project to Eclipse

../spring-mybatis-junit>mvn eclipse:eclipse

To run App.java

Right Click App.java >> Run As >> Java Application

To run AppTest.java

Right click AppTest.java >> Run As >> Junit Test

Source Code @ GitHub