Setting MyBatis in Spring

—

by

Luca Zanini
in

MyBatis, a free software distributed under the Apache License 2.0, helps to connect Spring with relational databases using XML or annotations.
Here’s a simple example loading a jsp page to display data from a mysql table.

  1. Create a mysql database named mybatis containing a table numbers whose dump is 
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

CREATE TABLE IF NOT EXISTS `numbers` (
  `num` int(10) DEFAULT NULL,
  `eng` varchar(20) DEFAULT NULL,
  `ita` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `numbers` (`num`, `eng`, `ita`) VALUES
(1, 'one', 'uno'),
(2, 'two', 'due'),
(3, 'three', 'tre'),
(4, 'four', 'quattro'),
(5, 'five', 'cinque'),
(6, 'six', 'sei'),
(7, 'seven', 'sette'),
(8, 'eight', 'otto'),
(9, 'nine', 'nove'),
(10, 'ten', 'dieci');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  1. Create a project in NetBeans or in your development environment named HelloWorldMyBatis
  1. Add the following libraries in the directory lib of Tomcat
    • commons-dbcp-1.4.jar
    • commons-pool-1.5.6.jar

    from Apache Commons and the library

    • mysql-connector-java-5.1.17-bin.jar

    from MySQLYou need these libraries to connect with mysql.

  1. Add the following libraries in the directory /WEB-INF/lib
    • jstl-api-1.2.jar
    • jstl-impl-1.2.jar
  1. Add the following libraries in the directory /WEB-INF/lib
    • org.springframework.aop-3.0.6.RELEASE.jar
    • org.springframework.asm-3.0.6.RELEASE.jar
    • org.springframework.aspects-3.0.6.RELEASE.jar
    • org.springframework.beans-3.0.6.RELEASE.jar
    • org.springframework.context-3.0.6.RELEASE.jar
    • org.springframework.context.support-3.0.6.RELEASE.jar
    • org.springframework.core-3.0.6.RELEASE.jar
    • org.springframework.expression-3.0.6.RELEASE.jar
    • org.springframework.instrument-3.0.6.RELEASE.jar
    • org.springframework.instrument.tomcat-3.0.6.RELEASE.jar
    • org.springframework.jdbc-3.0.6.RELEASE.jar
    • org.springframework.jms-3.0.6.RELEASE.jar
    • org.springframework.orm-3.0.6.RELEASE.jar
    • org.springframework.oxm-3.0.6.RELEASE.jar
    • org.springframework.test-3.0.6.RELEASE.jar
    • org.springframework.transaction-3.0.6.RELEASE.jar
    • org.springframework.web-3.0.6.RELEASE.jar
    • org.springframework.web.portlet-3.0.6.RELEASE.jar
    • org.springframework.web.servlet-3.0.6.RELEASE.jar
    • org.springframework.web.struts-3.0.6.RELEASE.jar
  1. Add the following libraries in the directory /WEB-INF/lib
    • mybatis-3.0.6-SNAPSHOT.jar
    • mybatis-spring-1.0.2-SNAPSHOT.jar
  1. Create the file /WEB-INF/web.xml 
    <?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

    <display-name>HelloWorldMyBatis</display-name>

    <context-param>
        <param-name>log4jConfigLocation</param-name>
        <param-value>/WEB-INF/classes/log4j-helloworld.xml</param-value>
    </context-param>

    <listener>
        <listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
    </listener>

    <servlet>
        <servlet-name>HelloWorldMyBatis</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>HelloWorldMyBatis</servlet-name>
        <url-pattern>*.html</url-pattern>
    </servlet-mapping>

    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>

</web-app>
  1. Create the /WEB-INF/HelloWorldMyBatis-servlet.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" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd">

<context:component-scan base-package="it.helloworld" />

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="it.helloworld.mybatis" />
</bean>

    <bean id="viewResolver"
    class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix">
            <value>/WEB-INF/views/</value>
        </property>
        <property name="suffix">
            <value>.jsp</value>
        </property>
    </bean>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
        <property name="username" value="luca"/>
        <property name="password" value="mysql"/>
    </bean>

    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
    </bean>

</beans>
  1. Create the file /WEB-INF/classes/log4j-helloworld.xml 
    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd" >
<log4j:configuration>
    <appender name="stdout" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %-5p %c{1}:%L %m %n" />
<!--
ConversionPattern format specification
%d      inserts the date; you can specify the format (%d{yyyy-MM-dd HH:mm:ss,SSS})
%-5p    inserts the priority log level, 5 characters, left justified
%c{1}   inserts the name of the class
%L      inserts the line number
%m      inserts the user message
%n      inserts the separator (for example, a new line)
-->
        </layout>
    </appender>

    <appender name="fileAppender" class="org.apache.log4j.RollingFileAppender">
        <param name="Threshold" value="INFO" />
        <param name="MaxFileSize" value="512KB" />
        <param name="MaxBackupIndex" value="10" />
        <param name="File" value="${webapp.root}/WEB-INF/logs/helloworld.log"/>
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %-5p %c{1}:%L %m %n" />
        </layout>
    </appender>

<!--sets the priority log level for org.springframework-->
    <logger name="org.springframework">
        <level value="info"/>
    </logger>

<!--sets the priority log level for it.helloworld.controller-->
    <logger name= "it.helloworld.controller">
        <level value="debug"/>
    </logger>

<!--sets the default priority log level-->
    <root>
        <priority value="info"></priority>
        <appender-ref ref="stdout"/>
        <appender-ref ref="fileAppender"/>
    </root>
</log4j:configuration>
  1. Create the file index.jsp in the application root 
    <%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Welcome Page</title>
    </head>
    <body>
        <jsp:forward page="helloWorld.html" />
    </body>
</html>
  1. Create the directory /WEB-INF/views and the file /WEB-INF/views/helloWorld.jsp 
    <%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Hello World!</h1>
        <br />
        <table>
            <c:forEach items="${numbers}" var="v">
                <tr>
                    <td><c:out value="${v.num}" /></td>
                    <td><c:out value="${v.eng}" /></td>
                    <td><c:out value="${v.ita}" /></td>
                </tr>
            </c:forEach>
        </table>
    </body>
</html>
  1. Create the file /WEB-INF/classes/it/helloworld/controller/HelloWorldController.java 
    package it.helloworld.controller;

import it.helloworld.dao.model.Numbers;
import it.helloworld.service.NumbersService;
import java.util.List;
import javax.annotation.Resource;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class HelloWorldController {

    private static org.apache.log4j.Logger log = Logger.getLogger(HelloWorldController.class);
    @Resource
    NumbersService numbersService;

    @RequestMapping(value = {"/index", "/helloWorld"})
    public ModelAndView helloWorld(Model model) {

        List<Numbers> l = numbersService.getNumbers();

        model.addAttribute("numbers", l);

        log.trace("Trace");
        log.debug("Debug");
        log.info("Info");
        log.warn("Warn");
        log.error("Error");
        log.fatal("Fatal");

        return new ModelAndView("helloWorld");
    }
}
  1. Create the file /WEB-INF/classes/it/helloworld/dao/model/Numbers.java 
    package it.helloworld.dao.model;

public class Numbers {

    Integer num;
    String ita, eng;

    public String getEng() {
        return eng;
    }

    public void setEng(String eng) {
        this.eng = eng;
    }

    public String getIta() {
        return ita;
    }

    public void setIta(String ita) {
        this.ita = ita;
    }

    public Integer getNum() {
        return num;
    }

    public void setNum(Integer num) {
        this.num = num;
    }
}
  1. Create the interface /WEB-INF/classes/it/helloworld/dao/NumbersDao.java and its implementation /WEB-INF/classes/it/helloworld/dao/impl/NumbersDaoImpl.java 
    package it.helloworld.dao;

import it.helloworld.dao.model.Numbers;
import java.util.List;

public interface NumbersDao {

    List<Numbers> getNumbers();
}

    package it.helloworld.dao.impl;

import it.helloworld.dao.NumbersDao;
import it.helloworld.dao.model.Numbers;
import java.util.List;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.stereotype.Repository;

@Repository
public class NumbersDaoImpl extends SqlSessionDaoSupport implements NumbersDao {

    @Override
    public List<Numbers> getNumbers() {
        return getSqlSession().selectList("it.helloworld.mybatis.Mapper.getNumbers");
    }
}
  1. Create the interface /WEB-INF/classes/it/helloworld/service/NumbersService.java and its implementation /WEB-INF/classes/it/helloworld/service/impl/NumbersServiceImpl.java 
    package it.helloworld.service;

import it.helloworld.dao.model.Numbers;
import java.util.List;

public interface NumbersService {

    List<Numbers> getNumbers();
}

    package it.helloworld.service.impl;

import it.helloworld.dao.NumbersDao;
import it.helloworld.dao.model.Numbers;
import it.helloworld.service.NumbersService;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;

@Service
public class NumbersServiceImpl implements NumbersService {

    @Resource
    private NumbersDao numbersDao;

    @Override
    public List<Numbers> getNumbers() {
        return numbersDao.getNumbers();
    }
}
  1. Create the interface /WEB-INF/classes/it/helloworld/mybatis/Mapper.java 
    package it.helloworld.mybatis;

import it.helloworld.dao.model.Numbers;
import java.util.List;
import org.apache.ibatis.annotations.Select;

public interface Mapper {

    @Select("SELECT * FROM numbers")
    List<Numbers> getNumbers();
}
  1. Launch the application

Comments

3 responses to “Setting MyBatis in Spring”

  1. Anand Avatar
    Anand

    Really really nice tutorial. Although I would like to suggest a few changes to the code as I struggled with it for hours.

    1.) Change the for log4j-helloworld to

    ** ** (Ignore the asterisks)

    2.) Add the following beans to the HelloWorldMyBatis-servlet.xml :

    3.) Add the following to NumbersDaoImpl.java :

    private SqlSession sqlSession;

    public void setsqlSession(SqlSession sqlSession)
    {
    this.sqlSession=sqlSession;

    }

    4.) Remove destroy-method from the dataSource bean.

    Reply
    1. Anand Avatar
      Anand

      Change DOCTYPE of log4j-helloworld.xml to .

      Replace the ‘(‘ in the above to ‘!’.

      Reply
    2. Luca Zanini Avatar
      Luca Zanini

      I approved this comment but I think something has been lost, maybe because of formatting, sorry.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.