Spring Boot 整合MyBatis、Druid连接池、PageHelper插件

宇智波波奶茶

于 2021-11-21 17:56:50 发布

阅读量437

点赞数

分类专栏： springboot 文章标签： spring boot

本文链接：https://blog.csdn.net/layonly/article/details/121456836

版权

springboot 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

SpringBoot整合MyBatis

1 导入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--  数据库相关 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

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

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-autoconfigure</artifactId>
            <version>1.4.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</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>
                <version>1.5.4.RELEASE</version>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <version>2.4</version>
            </plugin>
        </plugins>
    </build>

2 在application.properties或者application.yml中配置数据库信息

我这里是在application.yml中配置的

#配置数据库信息 数据源为DruidDataSource
spring:
  datasource:
    username: root
    password: 1234
    url: jdbc:mysql:///emp_db?serverTimezone=Asia/Shanghai&characterEconding=utf8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
#mybatis扫描
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:mapper/*.xml
#端口号的更改
server:
  port: 8085
#pagehelper插件的配置
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true

3 根据数据库创建实体类

数据库中两张表为这样的

import lombok.Data;
import java.io.Serializable;
import java.util.Date;

@Data
public class Emp implements Serializable {

    private Integer empno;

    private String ename;

    private String job;

    private Integer deptno;

    private Date hireday;
}

import lombok.Data;
import java.io.Serializable;

@Data
public class DepartMent implements Serializable {

    private Integer deptno;

    private String dname;

    private String loc;
}

因为需要有连表查询，所以我多建了一个工具类

import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.util.Date;

@Data
public class Emp_Department implements Serializable {

    private Integer empno;

    private String ename;

    private String job;

    private Integer deptno;

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date hireday;

    private String dname;

    private String loc;
}

4 创建mapper

EmpMapper

import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmpMapper {

    List<Emp_Department> findAll();

    void deleteAll(Integer[] empnos);

    void updateById(Emp emp);

    void deleteById(Integer empno);

    void add(Emp emp);

    Emp_Department findById(Integer empno);
}

DeptMapper

import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface DeptMapper {
    DepartMent findById(Integer deptno);

    List<DepartMent> findAll();
}

5 在src\main\resources\mapper路径下创建对应的Mapper.xml文件

EmpMapper.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.qf.mapper.EmpMapper">

    <resultMap id="empMap" type="com.qf.pojo.Emp_Department">
        <id column="empno" property="empno"></id>
        <result column="ename" property="ename"></result>
        <result column="job" property="job"></result>
        <result column="deptno" property="deptno"></result>
        <result column="hireday" property="hireday"></result>
        <result column="dname" property="dname"></result>
        <result column="loc" property="loc"></result>
    </resultMap>

    <sql id="baseSql">
        empno,ename,job,deptno,hireday,dname,loc
    </sql>

    <select id="findAll" resultMap="empMap">
        select empno,ename,job,a.deptno,hireday,dname,loc
        from emp_tb a
        inner join department_tb b
        on a.deptno = b.deptno
    </select>

    <select id="findById" resultMap="empMap">
        select empno,ename,job,a.deptno,hireday,dname,loc
        from emp_tb a
        inner join department_tb b
        on a.deptno = b.deptno
        where empno = #{empno}
    </select>

    <delete id="deleteAll">
        delete from emp_tb
        where empno in
        <foreach collection="array" separator="," open="(" close=")" item="empno">
            #{empno}
        </foreach>
    </delete>

    <delete id="deleteById">
        delete from emp_tb
        where empno = #{empno}
    </delete>

    <insert id="add">
        insert into emp_tb(ename,job,deptno,hireday)
        values (#{ename},#{job},#{deptno},#{hireday})
    </insert>

    <update id="updateById">
        update emp_tb
        set ename = #{ename},job = #{job},deptno = #{deptno},hireday = #{hireday}
        where empno = #{empno}
    </update>

</mapper>

DeptMapper.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.qf.mapper.DeptMapper">

    <resultMap id="deptMap" type="com.qf.pojo.DepartMent">
        <id column="deptno" property="deptno"></id>
        <result column="dname" property="dname"></result>
        <result column="loc" property="loc"></result>
    </resultMap>

    <sql id="baseSql">
        deptno,dname,loc
    </sql>

    <select id="findAll" resultMap="deptMap">
        select <include refid="baseSql"></include>
        from department_tb
    </select>

    <select id="findById" resultMap="deptMap">
        select <include refid="baseSql"></include>
        from department_tb
        where deptno = #{deptno}
    </select>

</mapper>

6.创建service

EmpService

import java.util.List;

public interface EmpService {
    List<Emp_Department> findAll();

    void add(Emp emp);

    void deleteAll(Integer[] empnos);

    void deleteById(Integer empno);

    void updateById(Emp emp);

    Emp_Department findById(Integer empno);
}

import java.util.List;

public interface DeptService {
    DepartMent findById(Integer deptno);

    List<DepartMent> findAll();
}

7 创建serviceImpl

mport org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpMapper empMapper;

    @Override
    public List<Emp_Department> findAll() {
        return empMapper.findAll();
    }

    @Override
    public void add(Emp emp) {
        empMapper.add(emp);
    }

    @Override
    public void deleteAll(Integer[] empnos) {
        empMapper.deleteAll(empnos);
    }

    @Override
    public void deleteById(Integer empno) {
        empMapper.deleteById(empno);
    }

    @Override
    public void updateById(Emp emp) {
        empMapper.updateById(emp);
    }

    @Override
    public Emp_Department findById(Integer empno) {
        Emp_Department emp_department = empMapper.findById(empno);
        return emp_department;
    }


}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class DeptServiceImpl implements DeptService {

    @Autowired
    private DeptMapper deptMapper;

    @Override
    public DepartMent findById(Integer deptno) {
        DepartMent departMent = deptMapper.findById(deptno);
        return departMent;
    }

    @Override
    public List<DepartMent> findAll() {
      List<DepartMent> list = deptMapper.findAll();
      return list;
    }
}

8 创建controller

EmpController

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("emp")
public class EmpController {

    @Autowired
    private EmpService empService;


    @RequestMapping("findAll")
    public List<Emp_Department> findAll(){
        return empService.findAll();
    }

    @RequestMapping("findByPage")
    public PageInfo findByPage(@RequestParam(value = "pageNum",required = false,defaultValue = "1") Integer pageNum,
                               @RequestParam(value = "pageSize",required = false,defaultValue = "3") Integer pageSize){
        PageHelper.startPage(pageNum,pageSize);

        List<Emp_Department> list = empService.findAll();

        PageInfo<Emp_Department> listPageInfo = new PageInfo<>(list);

        return listPageInfo;
    }

    @RequestMapping("findById")
    public Emp_Department findById(@RequestParam(value = "empno") Integer empno){
        Emp_Department emp_department = empService.findById(empno);
        return emp_department;
    }

    @RequestMapping("addEmp")
    public String add(@RequestBody  Emp emp){
        empService.add(emp);
        return "Success";
    }

    @RequestMapping("deleteById")
    public String deleteById(Integer empno){
        empService.deleteById(empno);
        return "Success";
    }

    @RequestMapping("deleteAll")
    public String deleteAll(Integer[] empnos){
        empService.deleteAll(empnos);
        return "Success";
    }

    @RequestMapping("update")
    public String updateById(@RequestBody Emp emp){
        empService.updateById(emp);
        return "Success";
    }

}

DeptController

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("dept")
public class DeptController {

    @Autowired
    private DeptService deptService;

    @RequestMapping("findAll")
    public List<DepartMent> findAll(){
        List<DepartMent> list = deptService.findAll();
        return list;
    }

    @RequestMapping("findById")
    public DepartMent findById(@RequestParam("deptno") Integer deptno){
        DepartMent departMent = deptService.findById(deptno);
        return departMent;
    }

}

9启动配置类，进行测试（注意：需要在启动类上添加@MapperScan扫描Mapper）

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

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@MapperScan("com.qf.mapper")
public class ManagerApplication {

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

}