Druid数据源

Druid 数据源

Druid是阿里开源出来的数据库连接池，性能非常好，还自带日志监控。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>${druid-version}</version>
</dependency>

# 基础配置
spring.datasource.url=jdbc:mysql://localhost:3306/biubiu?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
# druid数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 初始化大小，最小，最大
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 'x'
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid() {
        return new DruidDataSource();
    }

    /**
     * 配置Druid监控
     * 1. 配置一个管理后台的Servlet
     */
    @Bean
    public ServletRegistrationBean<? extends Servlet> statViewServlet() {
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "123456");

        bean.setInitParameters(initParams);
        return bean;
    }

    /**
     * 配置Druid监控
     * 2. 配置一个监控的Filter
     */
    @Bean
    public FilterRegistrationBean<? extends Filter> webStatFilter() {
        FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<>(new WebStatFilter());
        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Collections.singletonList("/*"));
        return bean;
    }


}

http://localhost/druid/index.html

MyBatis ORM框架

<!-- mybatis-framework -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>${mybatis-version}</version>
</dependency>

注解版本

@Mapper
public interface CityMapper {

    /**
     * 城市ID查城市
     *
     * @param cityId 城市Id
     * @return City
     */
    @Select("SELECT * FROM BS_CITY WHERE CITY_ID = #{cityID}")
    public City getCityByCityId(Integer cityId);

    /**
     * 插入信息
     *
     * @param city 城市Id
     * @return int
     */
    @Options(useGeneratedKeys = true, keyProperty = "cityId")
    @Select("INSERT INTO BS_CITY(CITY_CODE, CITY_NAME, SHORT_NAME)" +
            "VALUES (#{cityCode}, #{cityName}, #{shortName});")
    public int insertCity(City city);

}

备注：

• 1、生成主键：@Options(useGeneratedKeys = true, keyProperty = "cityId")
• 2、包扫描 @Mapper 和 @MapperScan

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.biubiu.common.mapper.CountryMapper">

    <select id="selectCountryById" resultType="com.biubiu.common.entity.Country">
        SELECT * FROM country WHERE Code = #{code}
    </select>
</mapper>

对应的接口

public interface CountryMapper {

    /**
     * 查询国家根据Code
     * @param code code
     * @return Country
     */
    public Country selectCountryById(Integer code);
    
}

mybatis.mapper-locations=classpath:mapper/*.xml
# 驼峰
# mybatis.configuration.map-underscore-to-camel-case=true
# mybatis.config-location=#mybatis配置文件路径
# 执行的sql语句日志输出
# logging.level.com.biubiu.common.mapper=debug
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl