SpringBoot整合MyBatis注解版和配置版

1 环境

• pom.xml

	<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.10</version>
		</dependency>


		<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.2</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

• application.yaml

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.137.140:3306/test
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
#   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
#    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

#    如果你配置文件没有指定执行文件的名称而是使用默认的schema.sql或者schema-all.sql的话就在配置文件中加上spring.datasource.initialization-mode=always
    initialization-mode: always
    schema:
     - classpath:sql/*


mybatis:
  # 指定全局配置文件位置
  config-location: classpath:mybatis/mybatis-config.xml
  # 指定sql映射文件位置
  mapper-locations: classpath:mybatis/mapper/*.xml

2 编写bean

• Department

package cn.zhangyu.bean;

import java.io.Serializable;

public class Department implements Serializable{

    private Integer id;

    private String departmentName;

    public Integer getId() {
        return id;
    }

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

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
}

• Employee

package cn.zhangyu.bean;

import java.io.Serializable;

public class Employee implements Serializable {

    private Integer id;

    private String lastName;

    private Integer  gender;

    private String email;

    private Integer dId;

    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getdId() {
        return dId;
    }

    public void setdId(Integer dId) {
        this.dId = dId;
    }
}

3 注解版

1. DepartmentMapper

package cn.zhangyu.mapper;

import cn.zhangyu.bean.Department;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

/**
 * 指定操作数据库表department
 */
//@Mapper
@Repository
public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    public Department getDepartmentById(Integer id);

    @Select("select * from department where department_name=#{departmentName}")
    public Department getDepartmentByName(String name);

    @Delete("delete from department where id=#{id}")
    public int deleteDepartment(Integer id);

    //@Options 确认使用自增主键 、返回主键id 不使用@Options返回的id=null
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(String departmentName);

    @Update("update department set department_name=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}

 

2. DepartmentController

package cn.zhangyu.controller;

import cn.zhangyu.bean.Department;
import cn.zhangyu.mapper.DepartmentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class DepartmentController {

    @Autowired
    DepartmentMapper departmentMapper;

    //查询department
    @GetMapping("/dept/{id}")
    public Department getDepartment(@PathVariable("id") Integer id){
        return departmentMapper.getDepartmentById(id);
    }

    //插入数据
    @GetMapping("/dept")
    public Department insertDepartment(@RequestParam("departmentName") String departmentName){
        departmentMapper.insertDept(departmentName);
        Department department = departmentMapper.getDepartmentByName(departmentName);
        return department;
    }
}

我们可以发送请求http://localhost:8080/dept/?departmentName=aaa
测试结果：{"id":1,"departmentName":"aaa"}

注意： 这里我们可能存在多个mapper 那么我们就要为每个类加上@mapper注解，就比较麻烦，我们可以在application中加入@MapperScan(value = "cn.zhangyu.mapper") 注解。

4 配置版

1. EmployeeMapper

package cn.zhangyu.mapper;


import cn.zhangyu.bean.Employee;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

//@Mapper或者@MapperScan将接口扫描装配到容器中
@Mapper
@Repository
public interface EmployeeMapper {

    //@Select("select * from employee where id=#{id}")
    public Employee getEmpById(Integer id);

   // @Select("insert into employee(lastName) values(#{lastName})")
    public void insertEmp(Employee employee);
}

2. resources目录下加入如下配置
   

• mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <!--适应驼峰-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

• EmployeeMapper.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="cn.zhangyu.mapper.EmployeeMapper">
   <!--    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);-->
    <select id="getEmpById" resultType="cn.zhangyu.bean.Employee">
        SELECT * FROM employee WHERE id=#{id}
    </select>

    <insert id="insertEmp">
        INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
    </insert>
</mapper>

加上如上配置我们启动项目就可以发送请求了，我们事先可以先插入一条数据：insert into employee(lastName,email,gender,d_id) values('aa','123@qq.com',1,1);
我们来查询看看：http://localhost:8080/emp/1
结果：{"id":1,"lastName":"aa","gender":1,"email":"123@qq.com","dId":1}

项目完整代码地址：https://github.com/ordinary-zhang/java