在Spring Boot框架内使用Mybatis

Posted on | In | 阅读

Mybatis是一个基于Java的持久层框架，帮助将SQL数据可以中的实体映射到Java实例中，方便增删改查的实现。本篇笔记记录如何将Mybatis与Spring Boot框架相结合进行使用

首先需要创建一个Spring Boot项目 然后以用户（User）为例来使用Mybatis

  1. Maven依赖

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    <properties>
        <mybatis.version>3.2.7</mybatis.version>
        <mybatis-spring.version>1.2.2</mybatis-spring.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>${mybatis-spring.version}</version>
        </dependency>
    </dependencies>

    ​

  2. 创建实体类

    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

    package org.phoenix.bean;

    public class User {
        private Integer uid;
        private String username;

        //不返回password
    //    @JsonIgnore
        private String password;

        public Integer getId() {
            return uid;
        }
     
        public void setId(Integer id){
            this.uid = id;
        }
     
        public String getUsername(){
            return username;
        }
     
        public void setUsername(String username) {
            this.username = username;
        }
     
        public String getPassword() {
            return password;
        }
     
        public void setPassword(String password) {
            this.password = password;
        }
     
        @Override
        public String toString(){
            return "UserDao{" +
                    "id=" + uid +
                    ", username=" + username +
                    ", password=" + password +
                    "}";
        }
    }

    ​

  3. DAO（Data Access Object）层

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    package org.phoenix.dao;

    import org.phoenix.bean.User;
    import org.springframework.dao.DataAccessException;

    import java.util.List;
    import java.util.Map;

    public interface UserDao {
        List<User> getByMap(Map<String, Object> map);
        User getById(Integer id);
        Integer create(User user) throws DataAccessException;
        int update(User user);
        int delete(Integer id);
        User getByUserName(String userNmae);
    }

    ​

  4. Service层

    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
    package org.phoenix.Service;

    import org.phoenix.bean.User;
    import org.phoenix.dao.UserDao;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.dao.DataAccessException;
    import org.springframework.stereotype.Service;

    import java.util.List;
    import java.util.Map;

    @Service
    public class UserService {
        @Autowired
        private UserDao userDao;

        public List<User> getByMap(Map<String, Object> map){
            return userDao.getByMap(map);
        }

        public User getById(Integer id){
            return userDao.getById(id);
        }

        public User create(User user)throws DataAccessException {
            userDao.create(user);
            return user;
        }

        public User update(User user){
            userDao.update(user);
            return user;
        }

        public int delete(Integer id){
            return userDao.delete(id);
        }

        public User getByUserName(String userName){
            return userDao.getByUserName(userName);
        }
    }

    ​

  5. UserDaoMapper，连接了Dao层与Mapper，就是Mybatis工作的地方，这里面用XML语法编写了所有操作要用到的SQL语句，并与DAO层内的接口一一对应，当调用DAO层内接口的时候，就会完成对数据库的操作

    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
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    <?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="org.phoenix.dao.UserDao">
        <resultMap id="userMap" type="org.phoenix.bean.User">
            <id property="uid" column="uid"/>
            <result property="username" column="username"/>
            <result property="password" column="password"/>
        </resultMap>

        <sql id="queryCondition">
            <where>
                <if test="uid != null and uid != ''">
                    and uid = #{uid}
                </if>
                <if test="username != null and username != ''">
                    and username = #{username}
                </if>
                <if test="password != null and password != ''">
                    and password = #{password}
                </if>
                <if test="keywords != null and keywords != ''">
                    and username like CONCAT('%', #{keywords},'%')
                </if>
            </where>
        </sql>

        <select id="getByMap" parameterType="map" resultMap="userMap">
            SELECT * FROM user
            <include refid="queryCondition" />
        </select>

        <select id="getById" parameterType="int" resultMap="userMap">
            SELECT * FROM user WHERE uid =#{uid}
        </select>

        <select id="getByUserName" parameterType="String" resultMap="userMap">
            SELECT * FROM user WHERE username =#{username}
        </select>

        <insert id="create" parameterType="org.phoenix.bean.User">
            <!--<selectKey resultType="int"  order="AFTER" keyProperty="id" >-->
            <!--SELECT LAST_INSERT_ID()-->
            <!--</selectKey>-->
            INSERT INTO user(
            username,
            password
            )VALUES(
            #{username},
            #{password}
            )
        </insert>

        <update id="update" parameterType="org.phoenix.bean.User">
            UPDATE user SET
            username = #{username},
            password = #{password}
            WHERE uid = #{uid}
        </update>

        <delete id="delete" parameterType="int">
            DELETE FROM user WHERE uid = #{uid}
        </delete>

    </mapper>

    ​

  6. 接下来就是要配置Mybatis，让它知道要在哪里找Dao层，哪里找Mapper，用什么数据库，数据库地址是什么，用户密码是什么，在用哪个database等等

    配置数据连接，提供数据库连接的信息

    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 org.phoenix.config;

    import com.mchange.v2.c3p0.ComboPooledDataSource;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.env.Environment;

    import java.beans.PropertyVetoException;

    /**
     * 配置数据库连接
     */
    @Configuration
    public class DataSourceConfig {

        @Autowired
        private Environment env;

        @Bean(name = "dataSource")
        public ComboPooledDataSource dataSource() throws PropertyVetoException{
            ComboPooledDataSource dataSource = new ComboPooledDataSource();
            dataSource.setDriverClass(env.getProperty("jdbc.driverClassName"));
            dataSource.setJdbcUrl(env.getProperty("jdbc.url"));
            dataSource.setUser(env.getProperty("jdbc.username"));
            dataSource.setPassword(env.getProperty("jdbc.password"));
            dataSource.setMaxPoolSize(20);
            dataSource.setMinPoolSize(5);
            dataSource.setInitialPoolSize(10);
            dataSource.setMaxIdleTime(300);
            dataSource.setAcquireIncrement(5);
            dataSource.setIdleConnectionTestPeriod(60);

            return dataSource;
        }
    }

    然后需要提供DAO层所在的位置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    package org.phoenix.config;

    import org.mybatis.spring.mapper.MapperScannerConfigurer;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;

    /**
     * 扫描DAO文件
     */
    @Configuration
    public class MapperSacnnerConfig {

        @Bean
        public MapperScannerConfigurer mapperSacnnerConfigurer(){
            MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
            mapperScannerConfigurer.setBasePackage("**.dao");
            mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
            return mapperScannerConfigurer;
        }
    }

    用上面的信息来设置MyBatis，并告诉Mybatis映射文件Mapper的位置，MyBatis就会自动将UserDao自动对应到同前缀文件UserDaoMapper.xml上

    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
    package org.phoenix.config;

    import org.apache.ibatis.type.JdbcType;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;

    import javax.sql.DataSource;
    /**
     * 配置Mybatis映射文件
     */
    @Configuration
    public class MyBatisConfig {

        @Autowired
        private DataSource dataSource;
     
        @Bean(name = "sqlSessionFactory")
        public SqlSessionFactoryBean sqlSessionFactory(
                ApplicationContext applicationContext) throws Exception{
     
            SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(dataSource);
     
            org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            configuration.setMapUnderscoreToCamelCase(true);
            configuration.setJdbcTypeForNull(JdbcType.NULL);
            sessionFactory.setMapperLocations(applicationContext.getResources("classpath:mapper/*.xml"));
     
            return sessionFactory;
     
        }
    }

    ​

  7. 最后配置Spring Boot的REST接口来通过UserService调用UserDao

    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
    package org.phoenix.controller;

    import org.phoenix.Service.UserService;
    import org.phoenix.bean.User;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;

    import javax.servlet.http.HttpServletRequest;
    import java.util.List;

    @RequestMapping(value = "/users")
    @RestController
    public class UserController {

        @Autowired
        private UserService userService;

        @RequestMapping(method = RequestMethod.GET)
        public List<User> list(HttpServletRequest request){
            return userService.getByMap(null);
        }

        @RequestMapping(value = "/{id}", method = RequestMethod.GET)
        public User detail(@PathVariable Integer id){
            return userService.getById(id);
        }
    }

    ​

  8. 启动应用，在浏览器地址栏内输入http://localhost:8080/users 就会出现预先在数据库里存号的用户信息

    1
    [{"username":"123","password":"af17a6d2be6676b4cf53b3ae81796fa6","id":1},{"username":"admin","password":"c0e024d9200b5705bc4804722636378a","id":2}]

    如果要按id查找，例如查找id=1的用户，就输入http://localhost:8080/users/1 就可以了

项目的Github地址