您的位置: 首页  >  文章  >  微服务spring boot的使用入门-使用Druid连接池整合Mybatis-Plus连接Mysql数据库

微服务spring boot的使用入门-使用Druid连接池整合Mybatis-Plus连接Mysql数据库

分类: 文章 • 2024-01-04 09:11:47

使用Druid连接池整合Mybatis-Plus

1、添加依赖

		<!--Mysql-->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<!-- Druid依赖 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.25</version>
		</dependency>
		<!-- log4j 不加此依赖Druid的filters拦截会报错 在properties配置文件中filters中有体现 -->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<!-- MyBatisPlus -->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus</artifactId>
			<version>2.1.9</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatisplus-spring-boot-starter</artifactId>
			<version>1.0.5</version>
		</dependency>

2、修改application.properties配置文件

######################################
###spring datasource
######################################
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=60000
 
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,log4j
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000


######################################
###MyBatis-Plus
######################################
mybatis-plus.mapper-locations=classpath:mappers/*.xml 
mybatis-plus.type-aliases-package=com.example.demo.model

3、Druid 部分代码

1 DruidConfiguration 配置类

package com.example.demo.config;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

@Configuration
public class DruidConfiguration {

	private static final Logger log = LoggerFactory.getLogger(DruidConfiguration.class);

	@Bean(destroyMethod = "close", initMethod = "init")
	@ConfigurationProperties(prefix = "spring.datasource")
	public DataSource druidDataSource() {
		DruidDataSource druidDataSource = new DruidDataSource();
		return druidDataSource;
	}
	/**
	 * 注册一个StatViewServlet
	 */
	@Bean
	public ServletRegistrationBean<StatViewServlet> druidStatViewServlet() {
		log.info("init Druid Servlet Configuration");
		// org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
		ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<StatViewServlet>(
				new StatViewServlet(), "/druid/*");

		// 添加初始化参数：initParams
		// 白名单：
		servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
		// IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to
		// view this page.
		servletRegistrationBean.addInitParameter("deny", "192.168.1.73");
		// 登录查看信息的账号密码.
		servletRegistrationBean.addInitParameter("loginUsername", "admin");
		servletRegistrationBean.addInitParameter("loginPassword", "password");
		// 是否能够重置数据.
		servletRegistrationBean.addInitParameter("resetEnable", "false");
		return servletRegistrationBean;
	}
	/**
	 * 注册一个：filterRegistrationBean
	 */
	@Bean
	public FilterRegistrationBean<WebStatFilter> druidStatFilter() {

		FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<WebStatFilter>(
				new WebStatFilter());

		// 添加过滤规则.
		filterRegistrationBean.addUrlPatterns("/*");

		// 添加不需要忽略的格式信息.
		filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
		return filterRegistrationBean;
	}
}

4、Mybatis-Plus部分

说明：Mybatis-Plus（简称MP）是一个Mybatis的增强工具，在Mybatis的基础上只做了增强，不做改变。使用MP既可以使用MP的特有功能，又能够正常使用Mybatis的原生功能。MP是为简化开发、提高开发效率而生，它也提供了一些特有的插件，比如SQL性能监控、乐观锁、执行分析等。其中一个功能Generator后续会单独写个文章说明

package com.example.demo.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.plugins.PerformanceInterceptor;

@Configuration
@EnableTransactionManagement
public class MybatisPlusConfig {

	/***
	 * plus 的性能优化
	 */
	@Bean
	public PerformanceInterceptor performanceInterceptor() {
		PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
		/* <!-- SQL 执行性能分析，开发环境使用，线上不推荐。 maxTime 指的是 sql 最大执行时长 --> */
		//performanceInterceptor.setMaxTime(1000);
		/* <!--SQL是否格式化 默认false--> */
		performanceInterceptor.setFormat(false);
		return performanceInterceptor;
	}
	/**
	 * mybatis-plus 分页插件
	 */
	@Bean
	public PaginationInterceptor paginationInterceptor() {
		PaginationInterceptor page = new PaginationInterceptor();
		page.setDialectType("mysql");
		return page;
	}
}

添加mapper扫描↓

package com.example.demo;

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

@SpringBootApplication
@MapperScan("com.example.demo.mapper")
public class DemoApplication {

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

5、其他部分代码

Model

package com.example.demo.model;

import com.baomidou.mybatisplus.enums.IdType;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.activerecord.Model;
import com.baomidou.mybatisplus.annotations.TableName;
import java.io.Serializable;

/**
 * <p>
 * 实体类
 * </p>
 *
 * @author only3c
 * @since 2019-02-28
 */
@TableName("city")
public class City extends Model<City> {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    @TableField("city_name")
    private String cityName;
    @TableField("parent_id")
    private Integer parentId;
    @TableField("parent_name")
    private String parentName;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public Integer getParentId() {
        return parentId;
    }

    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    public String getParentName() {
        return parentName;
    }

    public void setParentName(String parentName) {
        this.parentName = parentName;
    }

    @Override
    protected Serializable pkVal() {
        return this.id;
    }

    @Override
    public String toString() {
        return "City{" +
        ", id=" + id +
        ", cityName=" + cityName +
        ", parentId=" + parentId +
        ", parentName=" + parentName +
        "}";
    }
}

Mapper 和 Mapper.xml

package com.example.demo.mapper;

import com.example.demo.model.City;
import com.baomidou.mybatisplus.mapper.BaseMapper;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author only3c
 * @since 2019-02-28
 */
public interface CityMapper extends BaseMapper<City> {

}

<?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.example.demo.mapper.CityMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.demo.model.City">
        <id column="id" property="id" />
        <result column="city_name" property="cityName" />
        <result column="parent_id" property="parentId" />
        <result column="parent_name" property="parentName" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, city_name AS cityName, parent_id AS parentId, parent_name AS parentName
    </sql>
</mapper>

Service 和 ServiceImpl

package com.example.demo.service;

import com.example.demo.model.City;
import com.baomidou.mybatisplus.service.IService;

/**
 * <p>
 *  服务类
 * </p>
 *
 * @author only3c
 * @since 2019-02-28
 */
public interface CityService extends IService<City> {

}

package com.example.demo.service.impl;

import com.example.demo.model.City;
import com.example.demo.mapper.CityMapper;
import com.example.demo.service.CityService;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author only3c
 * @since 2019-02-28
 */
@Service
public class CityServiceImpl extends ServiceImpl<CityMapper, City> implements CityService {

}

Controller

package com.example.demo.controller;

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

import com.example.demo.mapper.CityMapper;
import com.example.demo.model.City;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author only3c
 * @since 2019-02-28
 */
@RestController
@RequestMapping("/city")
public class CityController {

	@Autowired
	private CityMapper cityMapper;

	@RequestMapping("/get/{id}")
	public City selectUser(@PathVariable("id") String id) {
		City city = cityMapper.selectById(id);
		return city;
	}
}

6、工程结构图

微服务spring boot的使用入门-使用Druid连接池整合Mybatis-Plus连接Mysql数据库

7、测试

发送请求

在浏览器输入 http://localhost:8080/city/get/1
微服务spring boot的使用入门-使用Druid连接池整合Mybatis-Plus连接Mysql数据库

druid管理界面

在浏览器输入 http://localhost:8080/druid/login.html
用户名密码是在DruidConfiguration配置中的loginUsername和loginPassword
微服务spring boot的使用入门-使用Druid连接池整合Mybatis-Plus连接Mysql数据库
主界面
微服务spring boot的使用入门-使用Druid连接池整合Mybatis-Plus连接Mysql数据库

8、数据库表

CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `parent_id` int(11) NOT NULL,
  `parent_name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

insert into `city` (`id`, `city_name`, `parent_id`, `parent_name`) values('1','北京','0','北京');