Setting MyBatis in Spring

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
   PgSQL

   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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

   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
   XHTML

   <?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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

   <?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
   XHTML

   <?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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

   <?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
   XHTML

   <?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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

   <?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
   PHP

   <%@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 2 3 4 5 6 7 8 9 10 11 12 13

   <%@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
   PHP

   <%@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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

   <%@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
   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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

   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
   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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

   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
   Java

   package it.helloworld.dao; import it.helloworld.dao.model.Numbers; import java.util.List; public interface NumbersDao { List<Numbers> getNumbers(); }

   1 2 3 4 5 6 7 8 9

   package it.helloworld.dao;   import it.helloworld.dao.model.Numbers; import java.util.List;   public interface NumbersDao {       List<Numbers> getNumbers(); }

   Java

   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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   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
   Java

   package it.helloworld.service; import it.helloworld.dao.model.Numbers; import java.util.List; public interface NumbersService { List<Numbers> getNumbers(); }

   1 2 3 4 5 6 7 8 9

   package it.helloworld.service;   import it.helloworld.dao.model.Numbers; import java.util.List;   public interface NumbersService {       List<Numbers> getNumbers(); }

   Java

   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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   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
   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 2 3 4 5 6 7 8 9 10 11

   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