Spring + SpringMVC integration MyBatis

On the basis of the previous article IDEA uses maven to build Spring + SpringMVC environment, we will integrate the MyBatis framework, so that we can build a complete SSM environment.

1. Add mysql, jdcb, mybatis dependencies and import dependencies in pom.xml, all dependencies are as follows:

<properties>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
      <maven.compiler.source>1.7</maven.compiler.source>
      <maven.compiler.target>1.7</maven.compiler.target>
      <springVersion>4.2.6.RELEASE</springVersion>
      <mysqlVersion>5.1.38</mysqlVersion>
      <c3p0Version>0.9.1.2</c3p0Version>
      <myBatisVersion>3.4.1</myBatisVersion>
</properties>

<dependencies>

      <!--spring package base package -->
      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-core</artifactId>
          <version>${springVersion}</version>
      </dependency>

      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-beans</artifactId>
          <version>${springVersion}</version>
      </dependency>

      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-context</artifactId>
          <version>${springVersion}</version>
      </dependency>

      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-aop</artifactId>
          <version>${springVersion}</version>
      </dependency>

      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-tx</artifactId>
          <version>${springVersion}</version>
      </dependency>

      <!--Two packages for springMVC -->
      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-web</artifactId>
          <version>${springVersion}</version>
      </dependency>

      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-webmvc</artifactId>
          <version>${springVersion}</version>
      </dependency>

      <!--jdbc dependency -->
      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-jdbc</artifactId>
          <version>${springVersion}</version>
      </dependency>

      <!--mysql dependency -->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>${mysqlVersion}</version>
          <scope>runtime</scope>
      </dependency>

      <!--myBaits depends on -->
      <dependency>
          <groupId>org.mybatis</groupId>
          <artifactId>mybatis</artifactId>
          <version>${myBatisVersion}</version>
      </dependency>

      <dependency>
          <groupId>org.mybatis</groupId>
          <artifactId>mybatis-spring</artifactId>
          <version>1.3.0</version>
      </dependency>

      <!--Connection pool-->
      <dependency>
          <groupId>c3p0</groupId>
          <artifactId>c3p0</artifactId>
          <version>${c3p0Version}</version>
      </dependency>

      <!--junit package -->
      <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.11</version>
          <scope>test</scope>
      </dependency>

     <!--Test dependency package -->
      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-test</artifactId>
          <version>${springVersion}</version>
          <scope>test</scope>
      </dependency>
      
  </dependencies>

2. Add a Book data table in the database and populate the data. The sql statement is as follows:

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `book_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Book ID',
  `name` varchar(100) NOT NULL COMMENT 'book name',
  `number` int(11) NOT NULL COMMENT 'the number of collections',
  PRIMARY KEY (`book_id`)
ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8 COMMENT='Book List';

#
# Data for table "book"
#

INSERT INTO `book` VALUES (1000, 'Java Programming', 9), (1001, 'Data Structure', 7), (1002, 'Design Pattern', 7), (1003, 'Compilation Principle', 9) ;

3.Create a pojo folder and create a new Book.java class

Package pojo;

/**
 * Created By Seven.wk
 * Description: Book object
 * Created At 2018/09/13
 */
Public class Book {

    Private int bookId;// book ID

    Private String name;// book name

    Private int number;// number of collections

    Public Book() {
    }

    Public int getBookId() {
        Return bookId;
    }

    Public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    Public String getName() {
        Return name;
    }

    Public void setName(String name) {
        This.name = name;
    }

    Public int getNumber() {
        Return number;
    }

    Public void setNumber(int number) {
        This.number = number;
    }

    @Override
    Public String toString() {
        Return "Book{" +
                "bookId=" + bookId +
                ", name='" + name + '\'' +
                ", number=" + number +
                '}';
    }
}

4. Create a dao layer folder and create a dao layer interface

package dao;

Import org.apache.ibatis.annotations.Param;
Import pojo.Book;

Import java.util.List;

/**
 * Created By Seven.wk
 * Description: dao layer interface
 * Created At 2018/09/13
 */
Public interface BookMapper {
    /**
     * Query a single book by ID
     *
     * @param bookId
     * @return
     */
    Book queryById(int bookId);

    /**
     * Query all books
     *
     * @param offset query start position
     * @param limit number of queries
     * @return
     */
    List<Book> queryAll(@Param("offset") int offset, @Param("limit") int limit);

    /**
     * Reduce the number of collections
     *
     * @param bookId
     * @return indicates the number of updated rows if the number of affected rows is equal to >1
     */
    Int reduceNumber(int bookId);
}

5. Create a new mappers folder in the resources folder and store the dao layer mapping file

6. Create a new BookMapper.xml mapping file

<?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="dao.BookMapper">
    <resultMap id="BaseResultMap" type="pojo.Book">
        <id column="book_id" property="bookId" jdbcType="BIGINT"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="number" property="number" jdbcType="INTEGER"/>
    </resultMap>

    <sql id="Base_Column_List">
        book_id, name, number
    </sql>

    <select id="queryById" parameterType="INTEGER" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from book
        where book_id = #{id}
    </select>

    <select id="queryAll" parameterType="Map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from book
        limit #{limit}
        offset #{offset}
    </select>

    <update id="reduceNumber" parameterType="INTEGER">
        update book
        set number = number - 1
        where
        book_id = #{bookId}
    </update>

</mapper>

7. Create a new jdbc folder in the resources folder. Store jdbc.properties as the database configuration file

jdbc.properties

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/servlet?useUnicode=true&characterEncoding=utf8
jdbc.username = root
jdbc.password = root

8. Integrate the configuration in applicationContext.xml, the configuration file is as follows:

<?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:context="http://www.springframework.org/schema/context"
       Xsi:schemaLocation="http://www.springframework.org/schema/beans
       Http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context. Xsd">

    <!--Scan the package under the service folder -->
    <context:component-scan base-package="service"/>

    <!--Load jdbc configuration file -->
    <context:property-placeholder location="classpath:jdbc/jdbc.properties"/>

    <!--Configure database connection pool -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!--Connection pool properties -->
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <!--c3p0 connection pool private property -->
        <property name="maxPoolSize" value="30"/>
        <property name="minPoolSize" value="10"/>
        <!--Do not automatically commit after the connection is closed-->
        <property name="autoCommitOnClose" value="false"/>
        <!--Connection timeout -->
        <property name="checkoutTimeout" value="10000"/>
        <!--Connection failed retry times-->
        <property name="acquireRetryAttempts" value="2"/>
    </bean>

    <!--Configure the SqlSessionFactory object -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--Inject database connection pool -->
        <property name="dataSource" ref="dataSource"/>
        <!--Scan Entity Pack-->
        <property name="typeAliasesPackage" value="pojo"/>
        <!--Scan sql configuration file: xml file required by mapper -->
        <property name="mapperLocations" value="classpath:mappers/*.xml"/>
    </bean>

    <!--Configure the scan Dao interface package, dynamically implement the Dao interface, and inject it into the Spring container -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--Inject to sqlSessionFactory-->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <!--Need to scan the Dao interface package -->
        <property name="basePackage" value="dao"/>
    </bean>

    <!--Configure Transaction Manager -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <!--Inject database connection pool -->
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

At this point, if there is no problem in front, a simple integration of the ssm environment is completed! Below we build a test environment test dao layer interface

9. Introduce test dependencies in pom.xml

<!--Test dependency package -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>${springVersion}</version>
    <scope>test</scope>
</dependency>

10. New BookMapper.java test class BookMapperTest.java, and add context, the code is as follows:

package dao;

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 pojo.Book;

import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:spring/applicationContext.xml"})
public class BookMapperTest {

    @Autowired
    private BookMapper bookMapper;

    @Test
    public void queryById() {
        int id = 1003;
        Book book = bookMapper.queryById(id);
        System.out.println(book.toString());
    }

    @Test
    public void queryAll() {
        List<Book> bookList = bookMapper.queryAll(1, 3);
        for(Book book : bookList)
            System.out.println(book);
    }

    @Test
    public void reduceNumber() {
        int result = bookMapper.reduceNumber(1000);
        System.out.println(result);
    }

}

11. Test, test passed, then the environment integration is completed!

12. The overall directory structure of the project is as follows: