Druid資料來源使用（一）---單獨使用與整合springboot+mybatis

在springboot中使用（不整合mybatis）：

	@Bean
	public ServletRegistrationBean createRegistrationBean() {
		ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/stat/*");
		Map<String, String> initParams = new HashMap<String, String>();
		initParams.put("loginUsername", "hurricane");
		initParams.put("loginPassword", "1003");
		bean.setInitParameters(initParams);
		return bean;
	}
	
	@Bean(initMethod="init",destroyMethod="close")
	public DruidDataSource createDataSource() throws SQLException {
		DruidDataSource druidDataSource = new DruidDataSource();
		druidDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/temp");
		druidDataSource.setUsername("root");
		druidDataSource.setPassword("root");
//		druidDataSource.setTimeBetweenLogStatsMillis(3000);
		
		druidDataSource.setMaxActive(15);
		druidDataSource.setInitialSize(5);
		druidDataSource.setMaxWait(60000);
		druidDataSource.setMinIdle(1);
		
		druidDataSource.setFilters("stat");
		druidDataSource.setConnectionProperties(""
				+ "druid.stat.mergeSql=true;"
				+ "druid.stat.slowSqlMillis=1;"
				+ "druid.stat.logSlowSql=true");
		return druidDataSource;
	}

第一個bean是用來檢視統計資訊的頁面展示，可不使用，第二個是資料來源，在需要的地方注入即可使用。

package com.hurricane.app.file.server.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

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

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.hurricane.app.file.server.entity.User;

@Repository
public class UserDao {
	@Autowired
	private DruidDataSource dataSource;

	
	public User getUserById(int id) throws Exception{
		DruidPooledConnection connection = dataSource.getConnection();
		PreparedStatement prepareStatement = connection.prepareStatement("select * from user where id =  ?");
		prepareStatement.setInt(1, id);
		ResultSet executeQuery = prepareStatement.executeQuery();
		List<User> users = new ArrayList<User>();
		while (executeQuery.next()) {
			String username = executeQuery.getString("username");
			String password = executeQuery.getString("password");
			String desc = executeQuery.getString("description");
			User user  = new User();
			user.setUsername(username);
			user.setPassword(password);
			users.add(user);
		}
		executeQuery.close();
		if (users.size()>0) {
			return users.get(0);
		}
		return null;
	}

}

這種方式注入的資料來源建立的連線會一直處於active狀態，不知道怎麼讓連線池回收，解決方法待進一步學習。

springboot+mybatis+druid：

引入mybatis對springboot的支援依賴：

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>

Dao層介面：

package com.hurricane.app.file.server.dao;

import org.apache.ibatis.annotations.Mapper;

import com.hurricane.app.file.server.entity.User;

@Mapper
public interface UserDao {
	public User getUserById(int id);
}

對映檔案src/main/resources/mapper/UserMapper.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.hurricane.app.file.server.dao.UserDao">


    <select id="getUserById" resultType="com.hurricane.app.file.server.entity.User">
        select * from user where id=#{id}
    </select>

</mapper>

application.properties的內容為：

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/temp?useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driverClassName = com.mysql.jdbc.Driver

# 連線池配置,下面配置說明請參考Druid Github Wiki，配置_DruidDataSource參考配置
spring.datasource.druid.initial-size=5
spring.datasource.druid.max-active=20
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-wait=30000
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.druid.validation-query-timeout=3000
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.time-between-eviction-runs-millis=6000
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.max-evictable-idle-time-millis=600000
spring.datasource.druid.time-between-log-stats-millis=3000
# MyBatis 配置
mybatis.mapper-locations=classpath:mapper/*.xml

注意只是通過上面的properties檔案，僅僅連線資訊（url，username，password，driverClassName）可以正常傳遞到連線池，但是下面的通用配置無法傳遞到連線池，分別嘗試瞭如下格式，均無法成功將屬性注入：

spring.datasource.druid.max-active=20
spring.datasource.druid.maxActive=20
spring.datasource.maxActive=20
spring.datasource.max-active=20

參考druid官方提供的demo，將DruidDataSource進行一層封裝，如下：

package com.hurricane.app.file.server.datasource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;

import com.alibaba.druid.pool.DruidDataSource;

@ConfigurationProperties("spring.datasource.druid")
class DruidDataSourceWrapper extends DruidDataSource implements InitializingBean {
    @Autowired
    private DataSourceProperties basicProperties;

    @Override
    public void afterPropertiesSet() throws Exception {
        //if not found prefix 'spring.datasource.druid' jdbc properties ,'spring.datasource' prefix jdbc properties will be used.
    	System.out.println("============afterPropertiesSet============");
    	System.out.println(getUsername()+"---"+getPassword()+"---"+getUrl()+"---"+getDriverClassName());
    	if (super.getUsername() == null) {
            super.setUsername(basicProperties.determineUsername());
        }
        if (super.getPassword() == null) {
            super.setPassword(basicProperties.determinePassword());
        }
        if (super.getUrl() == null) {
            super.setUrl(basicProperties.determineUrl());
        }
        if(super.getDriverClassName() == null){
            super.setDriverClassName(basicProperties.getDriverClassName());
        }

    }

}

之後將spring.properties的檔案配置資料來源型別指定為封裝過後的資料來源：

spring.datasource.type=com.hurricane.app.file.server.datasource.DruidDataSourceWrapper

這樣，資料來源的其他配置屬性就可以正常注入使用了。

參考：

        https://blog.csdn.net/u011244202/article/details/54709060（測試發現其中的資料來源基本配置無法正常注入，解決方法見上面）

        https://github.com/alibaba/druid/wiki/

        http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/