springboot集成mybatis

最新推荐文章于 2024-01-12 09:34:57 发布

原创最新推荐文章于 2024-01-12 09:34:57 发布 · 267 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#springboot集成mybatis

L01-SpringBoot 专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了如何在SpringBoot环境中配置并使用MyBatis进行数据库操作，包括项目搭建、依赖配置、数据源设置、SQL映射文件编写、接口定义、服务实现及控制器设计，最后通过实际请求演示了增删改查功能。

1.本例环境
springboot + mybatis + IntelliJ IDEA
2.创建Spring Initializr项目
3.pom.xml配置
引入mybatis,druid,mysql-connector-java,spring-boot-starter-jdbc,spring-boot-starter-web等模块
4.application.yml配置
1>.配置数据源相关

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://127.0.0.1:3306/zbzdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
    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

2>.配置mybaits

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

5.配置mybaits相关配置
在resources目录下创建如下目录与文件
mybatis/mapper/EmployeeMapper.xml
mybatis/mybatis-config.xml
1>.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>
    <!-- 开启驼峰命名 读取数据库字段自动映射到对象 2019-3-14 -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

2>.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="com.zbz.mapper.EmployeeMapper">
    <!-- 自动生成主键返回-->
    <insert id="insertEmployee" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO employee(last_name,email,gender,card_number,post,department_id)
        VALUES (#{lastName},#{email},#{gender},#{cardNumber},#{post},#{departmentId})
    </insert>
    <select id="getEmployeeById" resultType="com.zbz.bean.Employee">
        SELECT * FROM employee WHERE id=#{id}
    </select>
    <update id="updateEmployee">
        UPDATE employee SET
        last_name=#{lastName},
        email=#{email},
        gender=#{gender},
        card_number=#{cardNumber},
        post=#{post},
        department_id=#{departmentId}
        WHERE id=#{id}
    </update>
    <delete id="deleteEmployeeById">
        DELETE FROM employee WHERE id = #{id}
    </delete>
</mapper>

本例中EmployeeMapper.xml是Employee实例对应的增删改查sql
6.与EmployeeMapper.xml映射接口类

@Mapper
public interface EmployeeMapper {

    public int insertEmployee(Employee employee);
    public Employee getEmployeeById(Integer id);
    public int updateEmployee(Employee employee);
    public int deleteEmployeeById(Integer id);
}

7.服务类

@Service
public class EmployeeService {

    @Autowired
    EmployeeMapper employeeMapper;

    /**增*/
    public Employee insertEmployee(Employee employee) {
        System.out.println("插入数据");
        employeeMapper.insertEmployee(employee);
        return employee;
    }
    /**查*/
    public Employee getEmployee( Integer id) {
        Employee employee = employeeMapper.getEmployeeById(id);
        return employee;
    }
    /**改*/
    public Employee updateEmployee(Employee employee) {
        int result = employeeMapper.updateEmployee(employee);
        Employee employee1 = employeeMapper.getEmployeeById(employee.getId());
        return employee1;
    }
    /**删*/
    public Employee deleteEmployee(Integer id) {
        employeeMapper.deleteEmployeeById(id);
        return null;
    }
}

8.controller类

@RestController
@RequestMapping("/emp")
public class EmployeeController {

    @Autowired
    EmployeeService employeeService;

    /**增*/
    @GetMapping("/insert")
    public Employee insertEmployee(Employee employee) {
        employeeService.insertEmployee(employee);
        return employee;
    }
    /**
     * 查
     */
    @GetMapping("/query/{id}")
    public Employee getEmployee(@PathVariable("id") Integer id) {
        Employee employee = employeeService.getEmployee(id);
        return employee;
    }
    /**改*/
    @GetMapping("/update")
    public Employee updateEmployee(Employee employee) {
        Employee employee1 = employeeService.updateEmployee(employee);
        return employee1;
    }
    /**删*/
    @GetMapping("/delete/{id}")
    public Employee deleteEmployee(@PathVariable("id") Integer id) {
        employeeService.deleteEmployee(id);
        return new Employee();
    }
}

9.Druid数据源自动配置类

@Configuration
public class DruidConfig {

    /**注入配置文件信息- application.yml */
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return new DruidDataSource();
    }

    /**配置Druid监控*/
    /**1. 配置管理后台的servlet*/
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
        Map<String,String> initParams = new HashMap<>();
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");
        bean.setInitParameters(initParams);
        return bean;
    }

    /**2. 配置一个web监控的filter*/
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

10.实体类

public class Employee implements Serializable {
    private Integer id;
    private String lastName;
    private String email;
    private Integer gender;
    private String cardNumber;
    /** 岗位 */
    private String post;
    /** 数据库字段: department_id 开启驼峰命名后可以自动匹配*/
    private Integer departmentId;

    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 String getEmail() {
        return email;
    }

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

    public Integer getGender() {
        return gender;
    }

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

    public String getCardNumber() {
        return cardNumber;
    }

    public void setCardNumber(String cardNumber) {
        this.cardNumber = cardNumber;
    }

    public String getPost() {
        return post;
    }

    public void setPost(String post) {
        this.post = post;
    }

    public Integer getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentId(Integer departmentId) {
        this.departmentId = departmentId;
    }
}

11.建表语句

drop table if exists employee;
CREATE TABLE `employee` (
  `id` INT(16) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `last_name` VARCHAR(128) DEFAULT NULL COMMENT '姓名',
  `email` VARCHAR(128) DEFAULT NULL COMMENT '邮件',
  `gender` INT(2) DEFAULT NULL COMMENT '性别',
  `card_number` VARCHAR(64) DEFAULT NULL COMMENT '胸卡编号',
  `post` VARCHAR(128) DEFAULT NULL COMMENT '岗位',
  `department_id` INT(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表' ;

12.测试使用
http://127.0.0.1:8090/demo/emp/insert?lastName=张三&email=zhangsan@163.com&gender=1&cardNumber=20190314002&post=程序猿&departmentId=1
http://127.0.0.1:8090/demo/emp/insert?lastName=李四&email=lisi@163.com&gender=1&cardNumber=20190314002&post=程序猿&departmentId=1
http://127.0.0.1:8090/demo/emp/query/1
http://127.0.0.1:8090/demo/emp/update?id=1&lastName=王五&email=wangwu@163.com&gender=1&cardNumber=20190001&post=程序猿&departmentId=1
http://127.0.0.1:8090/demo/emp/delete/2
13.本例源码下载地址: https://github.com/zhangbeizhen/springboot-mybatis
以上,TKS.