Spring Boot 整合 mybatis

作者: 沧海一粟谦 | 来源:发表于2018-04-09 17:00 被阅读119次

Spring boot + Mybatis 整合（Intelli
Spring Boot - XML配置的方式整合MyBatis
Spring Boot整合MyBatis
简单快捷的在SpringBoot中搭建MyBatis(只需四步)
Spring Boot教程 - 整合Mybatis（注解方式）
Spring Boot中使用MyBatis
2019-03-18
Spring Boot整合Mybatis-plus
Spring Boot：数据访问和视图技术
spring boot和mybatis整合时报org.apach

The Matrix

整合Mybatis分为两种模式，一种是xml配置，一种是注解。（类似JPA）这篇文章主要讲xml配置

mybatis逆向工程

逆向工程方式很多，我目前接触到的就两种，一种是借助于ide开发工具，一种是在cmd中执行命令。二者原理都一样，都是执行maven的generator命令。

pom.xml

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
        <!-- alibaba的druid数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.20</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.45</version>
        </dependency>
        <!-- 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
                <configuration>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

逆向所需配置文件generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!-- 数据库驱动:选择你的本地硬盘上面的数据库驱动包-->
    <classPathEntry  location="E:\mysql-connector-java-5.1.45.jar"/>
    <context id="DB2Tables"  targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--数据库链接URL，用户名、密码 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1/spring?characterEncoding=UTF-8" userId="root" password="123456">
        </jdbcConnection>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!-- 生成模型的包名和位置-->
        <javaModelGenerator targetPackage="com.example.mybatis.model" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!-- 生成映射文件的包名和位置-->
        <sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!-- 生成DAO的包名和位置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.example.mybatis.mapper" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!-- 要生成的表 tableName是数据库中的表名或视图名 domainObjectName是实体类名-->
        <table tableName="User" domainObjectName="User" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

利用IDE创建逆向工程启动类

添加一个Maven configuration

application.yml配置文件

server:
# 服务器监听端口
  port: 8080

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/spring?characterEncoding=UTF-8&useSSL=true
    # druid 连接池
    type: com.alibaba.druid.pool.DruidDataSource
    #配置监控统计拦截的filters，去掉后监控界面sql将无法统计,'wall'用于防火墙
    filters: stat
    #最大活跃数
    maxActive: 20
    initialSize: 1
    #最大连接等待超时时间
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20

mybatis:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: com.example.mybatis.model

#pagehelper分页插件
#pagehelper:
#    helperDialect: mysql
#   reasonable: true
#    supportMethodsArguments: true
#    params: count=countSql

运行generator工程自动生成以下代码

User.java

public class User {
    private Long id;

    private String username;

    private String password;

    private Integer age;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username == null ? null : username.trim();
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password == null ? null : password.trim();
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

UserMapper .java

import com.example.mybatis.model.User;
import java.util.List;

public interface UserMapper {
    int deleteByPrimaryKey(Long id);

    int insert(User record);

    int insertSelective(User record);

    User selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);

    List<User> selectAll();
}

UserMapper.xml

<?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.example.mybatis.mapper.UserMapper" >
  <resultMap id="BaseResultMap" type="com.example.mybatis.model.User" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="userName" property="username" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, userName, password, age
  </sql>
  <select id="selectAll" resultMap="BaseResultMap" >
    select
    <include refid="Base_Column_List" />
    from user
  </select>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from user
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.example.mybatis.model.User" >
    insert into user (id, userName, password, 
      age)
    values (#{id,jdbcType=BIGINT}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 
      #{age,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" parameterType="com.example.mybatis.model.User" >
    insert into user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="username != null" >
        userName,
      </if>
      <if test="password != null" >
        password,
      </if>
      <if test="age != null" >
        age,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=BIGINT},
      </if>
      <if test="username != null" >
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="password != null" >
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        #{age,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.example.mybatis.model.User" >
    update user
    <set >
      <if test="username != null" >
        userName = #{username,jdbcType=VARCHAR},
      </if>
      <if test="password != null" >
        password = #{password,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        age = #{age,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.example.mybatis.model.User" >
    update user
    set userName = #{username,jdbcType=VARCHAR},
      password = #{password,jdbcType=VARCHAR},
      age = #{age,jdbcType=INTEGER}
    where id = #{id,jdbcType=BIGINT}
  </update>
</mapper>

修改启动类

逆向生成代码后，我们还需要在启动类上添加一个@MapperScan("com.example.mybatis.mapper")注解，告诉我们的Mapper需要扫描的包，这样就不用每个Mapper上都添加@Mapper注解了。

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.mybatis.mapper")
public class MybatisApplication {

    //配置mybatis的分页插件pageHelper
    @Bean
    public PageHelper pageHelper(){
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("offsetAsPageNum","true");
        properties.setProperty("rowBoundsWithCount","true");
        properties.setProperty("reasonable","true");
        properties.setProperty("dialect","mysql");    //配置mysql数据库的方言
        pageHelper.setProperties(properties);
        return pageHelper;
    }

    public static void main(String[] args) {
        SpringApplication.run(MybatisApplication.class, args);
    }
}

完善controller和service

public interface UserService {
    public void delete(Long id);
    public void insert(User user);
    public int update(User user);
    public User findById(Long id);
    public List<User> selectAll(int pageNum,int pageSize);
}

import com.example.mybatis.mapper.UserMapper;
import com.example.mybatis.model.User;
import com.example.mybatis.service.UserService;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService{

    @Autowired
    private UserMapper userMapper;

    @Override
    public void delete(Long id){
        userMapper.deleteByPrimaryKey(id);
    }
    @Override
    public void insert(User user){
        userMapper.insert(user);
    }
    @Override
    public int update(User user){
        return userMapper.updateByPrimaryKey(user);
    }
    @Override
    public User findById(Long id){
        return userMapper.selectByPrimaryKey(id);
    }
    @Override
    public List<User> selectAll(int pageNum,int pageSize){
        PageHelper.startPage(pageNum,pageSize);
        return userMapper.selectAll();
    }
}

import com.example.mybatis.model.User;
import com.example.mybatis.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @RequestMapping(method = RequestMethod.GET,value = "/hello")
    public String index(){
        return "hello word";
    }
    @RequestMapping(method = RequestMethod.GET,value = "/{id}/select")
    public User findById(@PathVariable("id")Long id){
        return userService.findById(id);
    }
    @RequestMapping(method = RequestMethod.GET,value = "/delete/{id}")
    public void delete(@PathVariable("id")Long id){
        userService.delete(id);
    }
    @RequestMapping(method = RequestMethod.GET,value = "/update/{id}")
    public void update(@RequestParam User user){
        userService.update(user);
    }
    @RequestMapping(method = RequestMethod.GET,value = "/insert")
    public void insert(User user){
        userService.insert(user);
    }
    @RequestMapping(method = RequestMethod.GET,value="/list/{pageNum}/{pageSize}")
    public List<User> selectAll(@PathVariable("pageNum")int pageNum,@PathVariable("pageSize")int pageSize){
        return userService.selectAll(pageNum,pageSize);
    }
}

整个项目文件

数据库文件

create database spring character set utf8 collate utf8_general_ci;
use spring;
create table User(
    id bigint not null primary key,
    userName varchar(255),
    password varchar(255),
    age int
)character set utf8 collate utf8_general_ci;

注解方式

UserMapper.java

public interface UserMapper {  
    //@Result 修饰返回的结果集，关联实体类属性和数据库字段一一对应，如果实体类属性和数据库属性名保持一致，则不需要这个属性来修饰。
    @Select("SELECT * FROM user")
    @Results({
            @Result(property = "username", column = "userName"),
            @Result(property = "password",  column = "password"),
            @Result(property = "age",column = "age")
    })
    List<User> selectAll();

    @Select("SELECT * FROM user WHERE id = #{id}")
    @Results({
            @Result(property = "username", column = "userName"),
            @Result(property = "password",  column = "password"),
            @Result(property = "age",column = "age")
    })
    User selectByPrimaryKey(int id);

    @Insert({"INSERT INTO user(userName,password,age}) VALUES(#{userName}, #{password},,#{age})"})
    void insert(User user);

    @Update("UPDATE user SET userName=#{userName},password = #{password}, age = #{age} WHERE id =#{id}")
    int updateByPrimaryKey(User user);

    @Delete("DELETE FROM user WHERE id =#{id}")
    int deleteByPrimaryKey(int id);
}

参考
mybatis java api

源码地址：https://github.com/WE666/spring-boot-project

Spring boot + Mybatis 整合（Intelli
Spring boot + Mybatis 整合 Spring boot+Mybatis，相对于原来的Spring...
Spring Boot - XML配置的方式整合MyBatis
上篇Spring Boot - 注解的方式整合MyBatis中，我们知道怎么利用注解的方式整合MyBatis，可以...
Spring Boot整合MyBatis
Spring中整合MyBatis就不多说了，最近大量使用Spring Boot，因此整理一下Spring Boot...
简单快捷的在SpringBoot中搭建MyBatis(只需四步)
Spring Boot整合Mybatis 部署Mybatis 1.引入依赖 2.Mybatis配置在applic...
Spring Boot教程 - 整合Mybatis（注解方式）
上一篇文章：Spring Boot教程 - 整合Mybatis（基于XML配置）介绍了 Spring Boot集...
Spring Boot中使用MyBatis
整合MyBatis之前，先搭建一个基本的Spring Boot项目[。然后引入spring-boot-mybati...
2019-03-18
Springboot整合Mybatis进行CRUD 1.Spring-boot-Mybatis模块结构 2.内部各...
Spring Boot整合Mybatis-plus
将上一篇Spring Boot整合Mybatis 修改为Mybatis-plus Mybatis-plus我就不多...
Spring Boot：数据访问和视图技术
SpringBoot 数据访问 Spring Boot 整合 MyBatis MyBatis 是一款优秀的持久层框...
spring boot和mybatis整合时报org.apach
问题： spring boot和mybatis整合时报：org.apache.ibatis.binding.Bin...