SpringBoot连接MySQL数据库实例【tk.mybatis连接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: 8051servlet:# 项目contextPathcontext-path: /testtomcat:# tomcat的URI编码uri-encoding: UTF-8# tomcat最大线程数，默认为200max-threads: 800# Tomcat启动初始化的线程数，默认值25min-spare-threads: 30# Spring配置
spring:# 环境 dev|pre|prodprofiles:active: devjackson:time-zone: GMT+8date-format: yyyy-MM-dd HH:mm:ss# MyBatis
mybatis:# 搜索指定包别名type-aliases-package: org.example.domainmapper-locations: classpath*:mapper/*Mapper.xml#配置驼峰下划线configuration:map-underscore-to-camel-case: true# PageHelper分页插件
pagehelper:helperDialect: mysqlreasonable: truesupportMethodsArguments: trueparams: count=countSql

application-dev.yml

spring:# 数据源配置datasource:driver-class-name: com.mysql.cj.jdbc.Driverfilters: wall,mergeStaturl: jdbc:mysql://127.0.0.1:3306/netty?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=CTT&useSSL=falseusername: rootpassword: 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> {@Autowiredprotected Mapper<T> mapper;public Mapper<T> getMapper() {return mapper;}@Overridepublic List<T> selectAll() {return mapper.selectAll();}@Overridepublic 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);}@Overridepublic 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> {}

五、代码

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 {@Autowiredprivate 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>