Spring Boot 2.7.0 Mybatis入门

什么是Mybatis

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

在mybatis这类框架没有出现之前，大多通过 jdbc连接数据库的方式，在java代码中拼接sql语句然后执行,具体如下：

1.加载JDBC驱动程序:

Class.forName("com.mysql.jdbc.Driver") ;

2.创建数据库的连接:

Connection con = DriverManager.getConnection(url , username , password ) ;

3.执行预编译语句:

Statement stmt = con.createStatement() ;
//PreparedStatement pstmt = con.prepareStatement(sql) ;
ResultSet rs = stmt.executeQuery("SELECt * FROM ...") ;

4.遍历结果集进行数据解析:

while(rs.next()){
   String name = rs.getString("name") ;
   String pass = rs.getString(1) ; 
}

mybatis框架对以上过程进行封装和优化，包括:可以配置数据源建立连接池；将java和 sql语句分离，sql语句可以写在xml配置文件中，接口定义在java中；自定义结果集和实体对象的转换。极大提高了开发人员的编码效率，提高了代码的可读性，节约数据库的性能。

本教程使用如下版本

spring boot 2.7.0

mybatis 2.1.3

druid 1.2.1

mysql 5.6

整合创建项目

新建一个spring boot 项目。可以通过我之前的教程创建一个空项目。

Spring Boot 新手入门

编辑pom文件

在idea中新建一个maven项目后，首先需要在pom文件中引入如下依赖。

主要包含：mybatis依赖，druid依赖，mysql依赖

 
        1.8
    
    
    
        org.springframework.boot
        spring-boot-starter-parent
        2.7.0
        
    

    
        
        
            org.springframework.boot
            spring-boot-starter-web
        

        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        

        
        
            org.springframework.boot
            spring-boot-starter-jdbc
        

        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.1.3
        

        
        
            com.alibaba
            druid
            1.2.1
        

        
        
            mysql
            mysql-connector-java
        
    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
                
                    com.study.MainStarter
                    -Dfile.encoding=UTF-8
                
                
                    
                        
                            repackage

不要忘记重新加载一下依赖，防止找不到jar包！依次点击maven，项目名称=》右键=》Reimport

目录结构

建立好如下图的包目录结构，具体如下:

建表语句

在mysql5.6 数据库中执行我们设计好的建表语句。

CREATE DATABASE studydb ;
USE studydb;
DROp TABLE IF EXISTS ser_company;
CREATE TABLE ser_company (
  company_id int(11) NOT NULL AUTO_INCREMENT COMMENT '公司id',
  company_name varchar(64) DEFAULT NULL COMMENT '公司名称',
  company_code varchar(4) DEFAULT NULL COMMENT '公司编号',
  company_msg text COMMENT '公司简介',
  company_crdt timestamp NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (company_id)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

配置数据源及mybatis

在 application.properties 配置文件中增加如下配置：

#druid数据库连接池
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#数据库地址
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/studydb?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
#数据库账号
spring.datasource.username=root
#数据库密码
spring.datasource.password=root

#清除缓存
spring.thymeleaf.cache=false
#配置mapper映射路径
mybatis.mapper-locations=classpath:mapper
    List selectSerCompanyList();

    
    SerCompanyVO  selectSerCompanyById(Integer companyId);

    
    int insertSerCompanyVO(SerCompanyVO serCompanyVO);

    
    int insertSerCompanyDynamic(SerCompanyVO serCompanyVO);

    
    int insertCompanyBatch(List list);

    
    int updateCompany(SerCompanyVO serCompanyVO);

    
    int updateCompanyDynamic(SerCompanyVO serCompanyVO);

    
    int deleteCompanyById(Integer companyId);
}

xml

在mapper目录下，新建SerCompanyMapper.xml并编写如下代码：

namespace 为 dao层接口类的路径。

resultMap对查询的结果集进行封装为SerCompanyVO对象。

foreach标签可以对list参数进行遍历。

useGeneratedKeys、keyProperty在插入后可以返回主键。





    
        
        
        
        
        
    

    
    
        insert into ser_company(
            company_name,
            company_code,
            company_msg,
            company_crdt
        ) values (
            #{companyName, jdbcType=VARCHAR},
            #{companyCode, jdbcType=VARCHAR},
            #{companyMsg, jdbcType=VARCHAR},
            #{companyCrdt, jdbcType=TIMESTAMP}
        )
    

    
    
        insert into ser_company(
            
                company_name,
            
            
                company_code,
            
            
                company_msg,
            
            
                company_crdt
            
        ) values (
            
                #{companyName, jdbcType=VARCHAR},
            
            
                #{companyCode, jdbcType=VARCHAR},
            
            
                #{companyMsg, jdbcType=VARCHAR},
            
            
                #{companyCrdt, jdbcType=TIMESTAMP}
            
        )
    

    
    
        insert into ser_company(
            company_name,
            company_code,
            company_msg,
            company_crdt
        ) values
        
            (
                #{item.companyName, jdbcType=VARCHAR},
                #{item.companyCode, jdbcType=VARCHAR},
                #{item.companyMsg, jdbcType=VARCHAR},
                #{item.companyCrdt, jdbcType=TIMESTAMP}
            )
        
    

    
        update ser_company set
        
            company_name = #{companyName, jdbcType=VARCHAR},
        
        
            company_code = #{companyCode, jdbcType=VARCHAR},
        
        
            company_msg = #{companyMsg, jdbcType=VARCHAR},
        
        
            company_crdt =  #{companyCrdt, jdbcType=TIMESTAMP}
        
        where company_id = #{companyId, jdbcType=INTEGER}
    

    
        update ser_company set
        company_name = #{companyName, jdbcType=VARCHAR},
        company_code = #{companyCode, jdbcType=VARCHAR},
        company_msg = #{companyMsg, jdbcType=VARCHAR},
        company_crdt = #{companyCrdt, jdbcType=TIMESTAMP}
        where company_id = #{companyId, jdbcType=INTEGER}
    

    
    
        delete from ser_company where company_id = #{companyId, jdbcType=INTEGER}
    

    
    
        select company_id,company_name, company_code, company_msg, company_crdt from ser_company where company_id=#{companyId, jdbcType=INTEGER}

service及实现类

在com.study.service包下新建 SerCompanyService接口，代码如下：

package com.study.service;

import com.study.model.SerCompanyVO;

import java.util.List;

public interface SerCompanyService {

    List querySerCompanyList();

    int addCompany(SerCompanyVO serCompanyVO);

    int addCompanyDynamic(SerCompanyVO serCompanyVO);

    int addCompanyBatch(List list);

    SerCompanyVO querySerCompanyById(Integer companyId);

    int updateCompany(SerCompanyVO serCompanyVO);

    int updateCompanyDynamic(SerCompanyVO serCompanyVO);

    int deleteCompanyById(Integer companyId);
}

在com.study.service.impl包下新建SerCompanyServiceImpl实现类，代码如下：

这里使用了我们刚刚写好的SerCompanyMapper。通过该类进行mybatis xml中语句的调用。

package com.study.service.impl;

import com.study.dao.SerCompanyMapper;
import com.study.model.SerCompanyVO;
import com.study.service.SerCompanyService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class SerCompanyServiceImpl implements SerCompanyService {

    private Logger log = LoggerFactory.getLogger(this.getClass().getName());

    @Autowired
    private SerCompanyMapper serCompanyMapper;

    @Override
    public List querySerCompanyList() {
        return serCompanyMapper.selectSerCompanyList();
    }

    @Override
    public int addCompany(SerCompanyVO serCompanyVO) {
        return serCompanyMapper.insertSerCompanyVO(serCompanyVO);
    }

    @Override
    public int addCompanyDynamic(SerCompanyVO serCompanyVO) {
        return serCompanyMapper.insertSerCompanyDynamic(serCompanyVO);
    }

    @Override
    public int addCompanyBatch(List list) {
        return serCompanyMapper.insertCompanyBatch(list);
    }

    @Override
    public SerCompanyVO querySerCompanyById(Integer companyId) {
        return serCompanyMapper.selectSerCompanyById(companyId);
    }

    @Override
    public int updateCompany(SerCompanyVO serCompanyVO) {
        return serCompanyMapper.updateCompany(serCompanyVO);
    }

    @Override
    public int updateCompanyDynamic(SerCompanyVO serCompanyVO) {
        return serCompanyMapper.updateCompanyDynamic(serCompanyVO);
    }

    @Override
    public int deleteCompanyById(Integer companyId) {
        return serCompanyMapper.deleteCompanyById(companyId);
    }
}

controller

在com.study.controller中新建如下类：

package com.study.controller;

import com.study.model.SerCompanyVO;
import com.study.service.SerCompanyService;
import com.study.util.ResultUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Controller
public class SerCompanyController {

    private Logger log = LoggerFactory.getLogger(this.getClass().getName());

    @Autowired
    private SerCompanyService serCompanyService;

    
    @RequestMapping(method= RequestMethod.GET, value = "querySerCompanyList")
    @ResponseBody
    public List querySerCompanyList(){
        if(log.isDebugEnabled()){
            log.debug("do querySerCompanyList begin.");
        }
        return serCompanyService.querySerCompanyList();
    }

    
    @RequestMapping(method= RequestMethod.GET, value = "querySerCompanyById")
    @ResponseBody
    public SerCompanyVO querySerCompanyById(Integer companyId){
        return serCompanyService.querySerCompanyById(companyId);
    }

    
    @RequestMapping(method= RequestMethod.GET, value = "addCompany")
    @ResponseBody
    public Map addCompany(SerCompanyVO serCompanyVO){
        if(log.isDebugEnabled()){
            log.debug("companyName:{}", serCompanyVO.getCompanyName());
        }
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        serCompanyVO.setCompanyCrdt(timestamp.toString());
        int num = serCompanyService.addCompany(serCompanyVO);
        Map map =  ResultUtil.getRsMap(num);
        map.put("serCompanyVO", serCompanyVO);
        return map;
    }

    
    @RequestMapping(method= RequestMethod.GET, value = "addCompanyDynamic")
    @ResponseBody
    public Map addCompanyDynamic(SerCompanyVO serCompanyVO){
        if (log.isDebugEnabled()){
            log.debug("companyName:{}", serCompanyVO.getCompanyName());
        }
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        serCompanyVO.setCompanyCrdt(timestamp.toString());
        int num = serCompanyService.addCompanyDynamic(serCompanyVO);
        Map map =  ResultUtil.getRsMap(num);
        map.put("serCompanyVO", serCompanyVO);
        return map;
    }

    
    @RequestMapping(method = RequestMethod.GET, value = "addCompanyBatch")
    @ResponseBody
    public Map addCompanyBatch(){
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        String timeStr = timestamp.toString();
        SerCompanyVO serCompanyVO = new SerCompanyVO();
        serCompanyVO.setCompanyName("导入1");
        serCompanyVO.setCompanyCode("3301");
        serCompanyVO.setCompanyCrdt(timeStr);
        SerCompanyVO serCompanyVO1 = new SerCompanyVO();
        serCompanyVO1.setCompanyName("导入2");
        serCompanyVO1.setCompanyCode("3302");
        serCompanyVO1.setCompanyCrdt(timeStr);
        List list = new ArrayList<>();
        list.add(serCompanyVO);
        list.add(serCompanyVO1);
        int num = serCompanyService.addCompanyBatch(list);
        return ResultUtil.getRsMap(num);
    }

    
    @RequestMapping(method = RequestMethod.GET, value = "updateCompany")
    @ResponseBody
    public Map updateCompany(SerCompanyVO serCompanyVO){
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        String timeStr = timestamp.toString();
        serCompanyVO.setCompanyCrdt(timeStr);
        int num = serCompanyService.updateCompany(serCompanyVO);
        return  ResultUtil.getRsMap(num);
    }

    
    @RequestMapping(method = RequestMethod.GET, value = "updateCompanyDynamic")
    @ResponseBody
    public Map updateCompanyDynamic(SerCompanyVO serCompanyVO){
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        String timeStr = timestamp.toString();
        serCompanyVO.setCompanyCrdt(timeStr);
        int num = serCompanyService.updateCompanyDynamic(serCompanyVO);
        return  ResultUtil.getRsMap(num);
    }

    
    @RequestMapping(method = RequestMethod.GET, value = "deleteCompanyById")
    @ResponseBody
    public Map deleteCompanyById(Integer companyId){
        int num = serCompanyService.deleteCompanyById(companyId);
        return  ResultUtil.getRsMap(num);
    }
}

启动类

在com.study 中编写MainStarter类，用于spring boot启动，代码如下：

package com.study;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class MainStarter {

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

}

测试

右键运行MainStarter类。观察控制台输出如下，观察项目正常启动：

打开浏览器，输入如下地址进行接口测试，选择一个查询数据的接口：

http://localhost:8080/querySerCompanyList

结果如下

其他的接口地址都在controller类中可以找到，依次测试即可。

好了，spring boot整合 mybatis基本完成。

写在最后

开源是一种美德，尽早加入开源社区，共建美好生态！

Spring Boot 2.7.0 Mybatis入门

Java相关栏目本月热门文章