IDEA SpringBoot整合Mybatis和Druid数据源实现增删改查操作

1.新建一个maven项目project

注意：也可以在线创建一个springboot项目，这样比较方便。

项目名称： springboot-druid-mybatis

2.添加依赖的pom:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.6</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>org.example</groupId>
    <artifactId>springboot-druid-mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <!--对应自己电脑安装的mysql版本-->
            <version>8.0.22</version>
        </dependency>
        <!--druid-spring-boot连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>
        <!--mybatis-spring-boot-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!--spring-boot-starter-web核心模块-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.5.0</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>
    </dependencies>

</project>

3.编写application.yml文件：

文件名字：application.yml
内容如下：

server:
  port: 8080

spring:
  datasource:
    driver-class-name:  com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource

mybatis:
  mapper-locations: classpath:mapper/*.xml #sql映射文件的位置
  configuration:
    map-underscore-to-camel-case: true #驼峰命名开启
  type-aliases-package: com.example.entity #实体类所在的包，自动扫描并映射实体类
 
logging:  #为了打印sql语句
  level:
    root: debug

4.写主启动类：

com.example.SpringbootMybatisMain：
在主启动类上家注解@MapperScan(“com.example.entity”)和@SpringBootApplication

package com.example;

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

@MapperScan("com.example.mapper")//扫描mapper接口所在的包
@SpringBootApplication
public class SpringbootMybatisMain {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatisMain.class,args);
    }
}

5.数据库准备：

数据库test,表t_book.

然后可以插入2条数据备用。

6.实体类：

entity.Book：

package com.example.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {
    private int id;
    private String bookName;
    private String bookAuthor;
    public Book(String bookName,String bookAuthor){
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
    }
}

7.mapper接口：

mapper.BookMapper:

package com.example.mapper;

import com.example.entity.Book;
import org.apache.ibatis.annotations.Mapper;

@Mapper//指定这是一个操作数据库的mapper
@Repository //dao层组件注解
public interface BookMapper {
    //新增图书的接口
    public int addBook(Book book);
    //删除图书的接口,根据id删除
    public int delBook(int id);
    //修改图书
    public int updateBook(Book book);
    //根据id查找
    public Book selectBook(int id);
    //动态条件查询
    public List<Book> getBooksByConditionIf(Book book);
}

8.sql映射文件：

目录结构：

sql语句：（带动态查询等）

<?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">
<!--namespace对应mapper接口所在的位置-->
<mapper namespace="com.example.mapper.BookMapper">
    <!--resultMap自定义结果映射规则-->
    <resultMap id="BaseresultMap" type="com.example.entity.Book">
        <id column="id" property="id" jdbcType="INTEGER"></id>
        <result column="book_name" property="bookName" jdbcType="VARCHAR"></result>
        <result column="book_author" property="bookAuthor" jdbcType="VARCHAR"></result>
    </resultMap>
    <!--定义经常重用到的sql片段，方便后面使用includ标签引用-->
    <sql id="BaseColumn">id,book_name,book_author</sql>
    <sql id="InsertBaseColumn">book_name,book_author</sql>

    <!--对应mapper接口中的方法public int addBook(Book book);useGeneratedKeys和keyProperty用于获取自增主键;-->
    <insert id="addBook" parameterType="com.example.entity.Book" useGeneratedKeys="true" keyProperty="id">
        insert into t_book (<include refid="InsertBaseColumn"></include>)
        values (#{bookName},#{bookAuthor})
    </insert>

    <!-- public int delBook(int id);-->
    <delete id="delBook" parameterType="integer">
        delete from t_book where id = #{id}
    </delete>

    <!--public int updateBook(Book book);带动态sql，set标签为了去掉多余的逗号；useGeneratedKeys和keyProperty用于获取自增主键;-->
    <update id="updateBook" parameterType="com.example.entity.Book" useGeneratedKeys="true" keyProperty="id">
        update t_book
        <set>
            <if test="bookName!=null and bookName!='' ">book_name = #{bookName},</if>
            <if test="bookAuthor!=null and bookAuthor!='' ">book_author=#{bookAuthor}</if>
        </set>
        where id = #{id}
    </update>

    <!--public Book selectBook(int id);根据id 查询-->
    <select id="selectBook" resultMap="BaseresultMap">
        select <include refid="BaseColumn"></include>
         from t_book where id = #{id}
    </select>

    <!--public List<Book> getBooksByConditionIf(Book book);拼接动态sql,进行多条件查询-->
    <select id="getBooksByConditionIf" parameterType="com.example.entity.Book" resultMap="BaseresultMap" >
        <!--include refid="BaseColumn" 用来引用sql片段 -->
        select <include refid="BaseColumn"></include> from t_book
        <where>
            <if test="id!=null and id!='' "> id = #{id}</if>
            <if test="bookName!=null and bookName!='' ">
                <bind name="bookName" value=" '%' + bookName + '%' "/>
                and book_name like #{bookName};
            </if>
            <if test="bookAuthor!=null and bookAuthor!='' ">
                <!--bind绑定变量，不为空的时候才去绑定-->
                <bind name="bookAuthor" value=" '%'+bookAuthor+'%' "/>
                and book_author like #{bookAuthor};
            </if>
        </where>
    </select>

</mapper>

9.service层：

service.BookService接口：

package com.example.service;
import com.example.entity.Book;
import java.util.List;

public interface BookService {
    //新增图书的接口
    public int addBook(Book book);
    //删除图书的接口,根据id删除
    public int delBook(int id);
    //修改图书
    public int updateBook(Book book);
    //根据id查找
    public Book selectBook(int id);
    //动态条件查询
    public List<Book> getBooksByConditionIf(Book book);
}

service的实现类：impl.BookServiceImpl

package com.example.service.impl;

import com.example.entity.Book;
import com.example.mapper.BookMapper;
import com.example.service.BookService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class BookServiceImpl implements BookService {
    //注入mapper
    @Resource
    private BookMapper bookMapper;
    @Override
    public int addBook(Book book) {
        int i = bookMapper.addBook(book);
        return i;
    }

    @Override
    public int delBook(int id) {
        return bookMapper.delBook(id);
    }

    @Override
    public int updateBook(Book book) {
        return bookMapper.updateBook(book);
    }

    @Override
    public Book selectBook(int id) {
        return bookMapper.selectBook(id);
    }

    @Override
    public List<Book> getBooksByConditionIf(Book book) {
        List<Book> booksByConditionIf = bookMapper.getBooksByConditionIf(book);
        return booksByConditionIf;
    }
}

10.controller层：

controller.BookController:

package com.example.controller;

import com.example.entity.Book;
import com.example.service.BookService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;


@RestController
@Slf4j
public class BookController {
    @Resource
    private BookService bookService;
    //增方法
    @RequestMapping(value = "/addBook",method = RequestMethod.POST)
    public String addBook( Book book){
        int i = bookService.addBook(book);
        if(i >= 1){
            return "增加成功";
        }else{
            return "增加失败";
        }
    }

    //删方法
    @RequestMapping(value = "/delBook",method = RequestMethod.GET)
    public String delBook( Integer id){
        int i = bookService.delBook(id);
        if(i >= 1){
            return "删除成功";
        }else{
            return "删除失败";
        }
    }

    //改方法
    @RequestMapping(value = "/updateBook",method = RequestMethod.POST)
    public String updateBook( Book book){
        int i = bookService.updateBook(book);
        if(i >= 1){
            return "修改成功";
        }else{
            return "修改失败";
        }
    }

    //查方法
    @RequestMapping(value = "/selectBook",method = RequestMethod.GET)
    public Book selectBook( Integer id){
        Book book = bookService.selectBook(id);
        return book;
    }
    //getBooksByConditionIf查方法
    @RequestMapping(value = "/getBooksByConditionIf",method = RequestMethod.POST)
    public List<Book> getBooksByConditionIf(Book book){
        List<Book> booksByConditionIf = bookService.getBooksByConditionIf(book);
        return booksByConditionIf;
    }
}

测试：使用postman测试：

数据库表：

增加测试：

删除测试：

修改测试：

查找测试：