SpringBoot连接MySQL数据库实例【tk.mybatis连接mysql数据库】

系列文章目录

一、SpringBoot连接MySQL数据库实例【tk.mybatis连接mysql数据库】
二、SpringBoot连接Redis与Redisson【代码】
三、SpringBoot整合WebSocket【代码】
四、SpringBoot整合ElasticEearch【代码示例】

---

---

项目下载

SpringBoot连接MySQL数据库实例

---

一、数据库表

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(63) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称',
  `age` int(3) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

二、引入依赖

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <version>2.1.1.RELEASE</version>
    </dependency>
    
    <!--  tk.myabtis  -->
    <dependency>
      <groupId>tk.mybatis</groupId>
      <artifactId>mapper-spring-boot-starter</artifactId>
      <version>2.1.5</version>
    </dependency>

    <!--  分页插件  -->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper-spring-boot-starter</artifactId>
      <version>1.2.5</version>
    </dependency>

    <!--  mysql  -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.17</version>
    </dependency>

三、修改配置文件

application.yml

server:
  # 服务端口
  port: 8051
  servlet:
    # 项目contextPath
    context-path: /test
  tomcat:
    # tomcat的URI编码
    uri-encoding: UTF-8
    # tomcat最大线程数，默认为200
    max-threads: 800
    # Tomcat启动初始化的线程数，默认值25
    min-spare-threads: 30

# Spring配置
spring:
  # 环境 dev|pre|prod
  profiles:
    active: dev
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss

# MyBatis
mybatis:
  # 搜索指定包别名
  type-aliases-package: org.example.domain
  mapper-locations: classpath*:mapper/*Mapper.xml
  #配置驼峰下划线
  configuration:
    map-underscore-to-camel-case: true

# PageHelper分页插件
pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

application-dev.yml

spring:
  # 数据源配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    filters: wall,mergeStat
    url: jdbc:mysql://127.0.0.1:3306/netty?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=CTT&useSSL=false
    username: root
    password: 123456

---

四、公共组件

1、BaseController

package org.example.base;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Supplier;

/**
 * Controller公共组件
 */
public abstract class BaseController {

    /**
     * 封装分页返回信息
     *
     * @param pageInfo
     * @return
     */
    private Map<String, Object> getDataTable(PageInfo<?> pageInfo) {
        Map<String, Object> rspData = new HashMap<>();
        rspData.put("list", pageInfo.getList());
        rspData.put("total", pageInfo.getTotal());
        rspData.put("pageNumber", pageInfo.getPageNum());
        rspData.put("pageSize", pageInfo.getPageSize());
        return rspData;
    }

    /**
     * 分页查询
     *
     * @param request
     * @param s
     * @return
     */
    protected Map<String, Object> findPages(QueryRequest request, Supplier<?> s) {
        PageHelper.startPage(request.getPageNum(), request.getPageSize());
        PageInfo<?> pageInfo = new PageInfo<>((List<?>) s.get());
        PageHelper.clearPage();
        return getDataTable(pageInfo);
    }

}

2、BaseService

package org.example.base;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.entity.Example;

import java.util.List;

public abstract class BaseService<T> implements IService<T> {

    @Autowired
    protected Mapper<T> mapper;

    public Mapper<T> getMapper() {
        return mapper;
    }

    @Override
    public List<T> selectAll() {
        return mapper.selectAll();
    }

    @Override
    public T selectByKey(Object key) {
        return mapper.selectByPrimaryKey(key);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int insert(T entity) {
        return mapper.insert(entity);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int insertSelective(T entity) {
        return mapper.insertSelective(entity);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int delete(Object key) {
        return mapper.deleteByPrimaryKey(key);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int batchDelete(List<String> list, String property, Class<T> clazz) {
        Example example = new Example(clazz);
        example.createCriteria().andIn(property, list);
        return this.mapper.deleteByExample(example);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int updateAll(T entity) {
        return mapper.updateByPrimaryKey(entity);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int updateNotNull(T entity) {
        return mapper.updateByPrimaryKeySelective(entity);
    }

    @Override
    public List<T> selectByExample(Object example) {
        return mapper.selectByExample(example);
    }

}

3、IService

package org.example.base;

import org.springframework.stereotype.Service;

import java.util.List;

@Service
public interface IService<T> {

    List<T> selectAll();

    T selectByKey(Object key);

    int insert(T entity);

    int insertSelective(T entity);

    int delete(Object key);

    int batchDelete(List<String> list, String property, Class<T> clazz);

    int updateAll(T entity);

    int updateNotNull(T entity);

    List<T> selectByExample(Object example);

}

4、BaseMapper

package org.example.base;

import tk.mybatis.mapper.common.Mapper;

/**
 * mybatis通用Mapper
 * 特别注意，该接口不能被扫描到，否则会出错
 */
public interface BaseMapper<T> extends Mapper<T> {

}

5、QueryRequest

package org.example.base;

import java.io.Serializable;

/**
 * 封装分页请求参数
 */
public class QueryRequest implements Serializable {

    private static final long serialVersionUID = -4869594085374385813L;

    /**
     * 当前页
     */

//    @NotNull(message = "{required}")
    private Integer pageNum;

    /**
     * 每页展示条数
     */
//    @NotNull(message = "{required}")
    private Integer pageSize;

//    /**
//     * 排序字段
//     */
//    private String sortField;
//
//    /**
//     * 排序方式，ASC/DESC
//     */
//    private String sortOrder;

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        //设置默认值:1
        this.pageNum = pageNum == null ? 1 : pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        //设置默认值:10
        this.pageSize = pageSize == null ? 10 : pageSize;
    }

//    public String getSortField() {
//        return sortField;
//    }
//
//    public void setSortField(String sortField) {
//        this.sortField = sortField;
//    }
//
//    public String getSortOrder() {
//        return sortOrder;
//    }
//
//    public void setSortOrder(String sortOrder) {
//        this.sortOrder = sortOrder;
//    }
}

五、代码

1、Application

package org.example;

import org.example.server.IMServer;
import tk.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication()
@MapperScan("org.example.mapper")
public class Application {
    public static void main( String[] args ) {
        SpringApplication.run(Application.class, args);
    }
}

2、Student实体类

package org.example.domain;

import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;

@Data
@Table(name = "student")
public class Student implements Serializable {

    /**
     * id
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    /**
     * 预约id
     */
    @Column(name = "name")
    private String name;

    /**
     * 预约id
     */
    @Column(name = "age")
    private Integer age;
}

3、Controller

package org.example.controller;

import org.example.base.BaseController;
import org.example.domain.Student;
import org.example.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/Student")
public class StudentController extends BaseController {

    @Autowired
    private StudentService studentService;

    @PostMapping("/insert")
    public void insert () {
        Student student = new Student();
        student.setAge(20);
        student.setName("李子木");
        studentService.insert(student);
    }
}

4、Service

package org.example.service;

import org.example.base.IService;
import org.example.domain.Student;

public interface StudentService extends IService<Student> {
}

5、ServiceImpl

package org.example.service.impl;

import org.example.base.BaseService;
import org.example.domain.Student;
import org.example.service.StudentService;
import org.springframework.stereotype.Service;

@Service("StudentService")
public class StudentServiceImpl extends BaseService<Student> implements StudentService {
}

6、Mapper

package org.example.mapper;

import org.example.base.BaseMapper;
import org.example.domain.Student;
import org.springframework.stereotype.Repository;

@Repository
public interface StudentMapper extends BaseMapper<Student> {
}

7、Mapper.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="org.example.mapper.StudentMapper">

</mapper>