数据库连接池和连接串

数据库连接池和连接串

1、数据库连接池

在java中有非常多优秀的开源数据源可以供大家选择，比如：DBCP、C3P0、Druid、HikariCP等。下图是开源数据源的对比图：

在性能方面：HikariCP因为细节方面优化力度较大，性能方面强于Druid。

hikariCP>druid>tomcat-jdbc>proxool>dbcp>c3p0

Hikari 是Springboot 2.0 默认的连接池，所以不用加额外的依赖，现在C3P0已经很久没有更新了， 而Druid 和 HikariCP 处于活跃状态的更新中。

11.1、Hikari

Hikari 是Springboot 2.0 默认的连接池，所以不用加额外的依赖。

application.yml文件：

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    username: shdxhl
    password: shdxhl
    url: jdbc:mysql://192.168.254.128:3306/sys_xhl?characterEncoding=utf-8&useSSL=false
    hikari:
      # 连接只读数据库时配置为true， 保证安全
      read-only: true
      # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
      connection-timeout: 30000
      # 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
      idle-timeout: 600000
      # 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒
      max-lifetime: 1800000
      # 连接池中允许的最大连接数。缺省值：10
      maximum-pool-size: 60
      minimum-idle: 10

如果在spring中使用，需要在spring配置文件中配置，如下所示： 

<!-- 注册数据源，使用 hikari 配置数据库连接池 -->
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">

	<property name="driverClassName" value="${db.driverClassName}" />
	<property name="jdbcUrl" value="${db.url}" />
	<property name="username" value="${db.username}" />
	<property name="password" value="${db.password}" />
	<property name="connectionTimeout" value="${db.connectionTimeout}" />
	<property name="readOnly" value="${db.readOnly}" />
	<property name="maxLifetime" value="${db.maxLifetime}" />
	<property name="maximumPoolSize" value="${db.maximumPoolSize}" />
	<property name="idleTimeout" value="${db.idleTimeout}" />
	<property name="poolName" value="${db.poolName}" />
	<property name="connectionTestQuery" value="${db.connectionTestQuery}" />

	<property name="dataSourceProperties">
		<props>
			<prop key="cachePrepStmts">${db.cachePrepStmts}</prop>
			<prop key="prepStmtCacheSize">${db.prepStmtCacheSize}</prop>
			<prop key="prepStmtCacheSqlLimit">${db.prepStmtCacheSqlLimit}</prop>
			<prop key="useServerPrepStmts">${db.useServerPrepStmts}</prop>
			<prop key="useLocalSessionState">${db.useLocalSessionState}</prop>
			<prop key="useLocalTransactionState">${db.useLocalTransactionState}</prop>
			<prop key="rewriteBatchedStatements">${db.rewriteBatchedStatements}</prop>
			<prop key="cacheResultSetMetadata">${db.cacheResultSetMetadata}</prop>
			<prop key="cacheServerConfiguration">${db.cacheServerConfiguration}</prop>
			<prop key="elideSetAutoCommits">${db.elideSetAutoCommits}</prop>
			<prop key="maintainTimeStats">${db.maintainTimeStats}</prop>
		</props>
	</property>
</bean>

11.2、Druid

Druid是阿里巴巴开源平台上一个数据库连接池实现，结合了 C3P0、DBCP 等 DB 池的优点，同时加入了日志监控。Druid 提供性能卓越的连接池功能外，还集成了SQL监控，黑名单拦截等功能，强大的监控特性，通过 Druid 提供的监控功能，可以清楚知道连接池和 SQL 的工作情况。

1.Springboot要集成Druid需要在pom.xml引用：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.16</version>
</dependency>

2.然后在application.yml中配置druid，并指定对应的type:

spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.254.128:3306/sys_xhl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
            username: shdxhl
            password: shdxhl
            initial-size: 10
            max-active: 100
            min-idle: 10
            max-wait: 60000
            pool-prepared-statements: true
            max-pool-prepared-statement-per-connection-size: 20
            time-between-eviction-runs-millis: 60000
            min-evictable-idle-time-millis: 300000
            #Oracle需要打开注释
            #validation-query: SELECT 1 FROM DUAL
            test-while-idle: true
            test-on-borrow: false
            test-on-return: false
            stat-view-servlet:
                enabled: true
                url-pattern: /druid/*
                #login-username: admin
                #login-password: admin
            filter:
                stat:
                    log-slow-sql: true
                    slow-sql-millis: 1000
                    merge-sql: false
                wall:
                    config:
                        multi-statement-allow: true

如果在spring中使用，需要在spring配置文件中配置，如下所示：

<!-- 注册数据源，使用 druid 配置数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
    destroy-method="close" lazy-init="false">
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
    <property name="initialSize" value="1" />
    <property name="maxActive" value="50" />
    <property name="maxWait" value="30000" />
	<!-- 配置扩展插件，监控统计用的filter:stat，日志用的filter:log4j，防御sql注入的filter:wall -->
    <property name="filters" value="stat,wall" />
    <property name="timeBetweenEvictionRunsMillis" value="3000" />
    <property name="minEvictableIdleTimeMillis" value="300000" />
    <property name="validationQuery" value="SELECT 'x'" />
    <property name="testWhileIdle" value="true" />
    <property name="testOnBorrow" value="false" />
    <property name="testOnReturn" value="false" />
    <property name="poolPreparedStatements" value="true" />
    <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
</bean>

11.3、dbcp

如果在spring中使用，需要在spring配置文件中配置，如下所示：

<!-- 注册数据源，使用 dbcp 配置数据库连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
	<property name="driverClassName" value="${jdbc.driver}" />
	<property name="url" value="${jdbc.url}" />
	<property name="username" value="${jdbc.username}" />
	<property name="password" value="${jdbc.password}" />
</bean>

11.4、c3p0

如果在spring中使用，需要在spring配置文件中配置，如下所示：

<!-- 注册数据源，使用 c3p0 配置数据库连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"  >
	<property name="driverClass" value="${jdbc.driverClass}"></property>
	<property name="jdbcUrl"  value="${jdbc.url}"></property>
    <property name="user"  value="${jdbc.username}"></property>
    <property name="password" value="${jdbc.password}"></property>
</bean>

2、数据库驱动和连接字符串

不同类型数据库的驱动和连接字符串是不一样的，即使同一种类型的数据库，不同版本之间的驱动和连接串有时也不一样。

1、mysql

在mysql数据库驱动jar包文件下的MENIFEST.MF文件中，Implementation-Version字段表示数据库驱动版本。

mysql JDBC 驱动常用的有两个，一个是gjt（Giant Java Tree）组织提供的mysql驱动，其JDBC Driver名称（JAVA类名）为：org.gjt.mm.mysql.Driver，详情请参见网站：http://www.gjt.org/

另一个是mysql官方提供的JDBC Driver，其JAVA类名为：com.mysql.jdbc.Driver，驱动下载网址：http://dev.mysql.com/downloads/

另外，com.mysql.jdbc.Driver 是 mysql-connector-java 5中的，com.mysql.cj.jdbc.Driver 是 mysql-connector-java 6中的。

1.1、org.gjt.mm.mysql.Driver

org.gjt.mm.mysql.Driver是早期的驱动名称，后来就改名为com.mysql.jdbc.Driver，现在一般都推荐使用com.mysql.jdbc.Driver。在最新版本的mysql jdbc驱动中，为了保持对老版本的兼容，仍然保留了org.gjt.mm.mysql.Driver。org.gjt.mm.mysql.Diver.java代码如下：

package org.gjt.mm.mysql;
import java.sql.SQLException;
public class Driver extends com.mysql.jdbc.Driver {
    public Driver()  throws SQLException{ }
} 

org.git.mm.mysql.Driver 只是简单的继承了 com.mysql.jdbc.driver，并没有实现其他逻辑。

1.2、com.mysql.jdbc.Driver

com.mysql.jdbc.Driver 是 mysql-connector-java 5中的，com.mysql.jdbc.Driver 继承了com.mysql.cj.jdbc.Driver，如下所示：

package com.mysql.jdbc;
import java.sql.SQLException;

public class Driver extends com.mysql.cj.jdbc.Driver {
    public Driver() throws SQLException {
    }

    static {
        System.err.println("Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.");
    }
}

如果maven依赖中使用的mysql-connector-java是6.0以上的版本，而jdbc driver使用的是com.mysql.jdbc.Driver，就会报上面绿色字体的错误。

<dependency> 
    <groupId>mysql</groupId> 
    <artifactId>mysql-connector-java</artifactId> 
    <version>6.0.6</version> 
</dependency>

1.3、com.mysql.cj.jdbc.Driver

com.mysql.cj.jdbc.Driver 是 mysql-connector-java 6中的，使用该jdbc驱动时，在连接字符串中需要指定时区serverTimezone，否则会报如下错误：

org.mybatis.spring.MyBatisSystemException:

nested exception is org.apache.ibatis.exceptions.PersistenceException:### Error querying database.

Cause: org.springframework.jdbc.CannotGetJdbcConnectionException:Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException:

Cannot create PoolableConnectionFactory (The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)

在设定时区的时候，如果设定serverTimezone=UTC，会比中国时间早8个小时，如果在中国，可以选择Asia/Shanghai或者Asia/Hongkong，例如serverTimezone=Shanghai

1.4、连接字符串

mysql JDBC URL格式如下：

jdbc:mysql://[host:port]/[database][?参数名1][=参数值1][&参数名2][=参数值2]...

参数名称	参数说明	缺省值	最低版本要求
user	数据库用户名（用于连接数据库）		所有版本
password	用户密码（用于连接数据库）		所有版本
useUnicode	是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk，本参数值必须设置为true	false	1.1g
characterEncoding	当useUnicode设置为true时，指定字符编码。比如可设置为gb2312或gbk	false	1.1g
autoReconnect	当数据库连接异常中断时，是否自动重新连接	false	1.1
autoReconnectForPools	是否使用针对数据库连接池的重连策略	false	3.1.3
failOverReadOnly	自动重连成功后，连接是否设置为只读	true	3.0.12
maxReconnects	autoReconnect设置为true时，重试连接的次数	3	1.1
initialTimeout	autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒	2	1.1
connectTimeout	和数据库服务器建立socket连接时的超时，单位：毫秒。 0表示永不超时，适用于JDK 1.4及更高版本	0	3.0.1
socketTimeout	socket操作（读写）超时，单位：毫秒。 0表示永不超时	0	3.0.1
serverTimezone	指定时区		6.0
useSSL	是否使用SSL连接（Secure Sockets Layer 安全套接层）	true	5.5.45

通常mysql连接字符串可以设置成如下格式：

jdbc:mysql://ip:3306/databaseName?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&failOverReadOnly=false

在使用数据库连接池的情况下，最好设置如下两个参数：autoReconnect=true&failOverReadOnly=false

使用mysql-connector-java 6的连接字符串建议使用如下：

jdbc:mysql://ip:port/databaseName?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false

更详细的连接字符串如下所示：（可将serverTimezone=UTC改成serverTimezone=Shanghai）

jdbc:mysql://ip:3306/dbname?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true

如果不显示指定useSSL的值，会有如下警告提示：

WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
不推荐不使用服务器身份验证来建立SSL连接，如果未明确设置，MySQL 5.5.45+, 5.6.26+ and 5.7.6+版本默认要求建立SSL连接。 为了符合当前不使用SSL连接的应用程序，verifyServerCertificate属性设置为’false’。 如果你不需要使用SSL连接，你需要通过设置useSSL=false来显式禁用SSL连接。 如果你需要用SSL连接，就要为服务器证书验证提供信任库，并设置useSSL=true。

{
  "serverTimezone": "shanghai",
  "useUnicode": "true",
  "characterEncoding": "utf8",
  "characterSetResults": "utf8",
  "useSSL": "false",
  "autoReconnect": "true",
  "failOverReadOnly": "false",
  "verifyServerCertificate": "false",
  "autoReconnectForPools": "true",
  "allowMultiQueries": "true"
}

2、oracle

在oracle数据库驱动jar包文件下的MENIFEST.MF文件中，Implementation-Version字段表示数据库驱动版本。

Java连接oracle数据库通常有两种方式：thin和oci

1）从使用上来说，oci必须在客户机上安装oracle客户端才能连接，而thin就不需要，因此从使用上来讲thin还是更加方便，这也是thin比较常见的原因。
2）原理上来看，thin是纯java实现tcp/ip的c/s通讯；而oci方式客户端通过native java method调用c library访问服务端，而这个c library就是oci(oracle called interface)，因此这个oci总是需要随着oracle客户端安装（从oracle10.1.0开始，单独提供OCI Instant Client，不用再完整的安装client）
3）它们分别是不同的驱动类别，oci是二类驱动， thin是四类驱动，但它们在功能上并无差异。

4）oci的性能比thin的高

使用thin方式：

driver：oracle.jdbc.driver.OracleDriver

url：jdbc:oracle:thin:@ip:port:orcl1

使用oci方式：

driver：oracle.jdbc.driver.OracleDriver

url：jdbc:oracle:oci:@dbname   （dbname是数据库名）

oracle.jdbc.OracleDriver继承了oracle.jdbc.driver.OracleDriver：

package oracle.jdbc;

public class OracleDriver extends oracle.jdbc.driver.OracleDriver {
    public OracleDriver() {
    }

    public static final boolean isDMS() {
        return false;
    }

    public static final boolean isInServer() {
        return false;
    }

    public static final boolean isJDK14() {
        return true;
    }

    public static final boolean isDebug() {
        return false;
    }

    public static final boolean isPrivateDebug() {
        return false;
    }
}

2.1、oracle 11g/10g

连接oracle 11g/10g的方式：

datasource:
  driver-class-name: oracle.jdbc.driver.OracleDriver
  url: jdbc:oracle:thin:@ip:port:SID
  username: xxx
  password: xxx

SID是数据库实例名，一般是orcl或orcl1

2.2、oracle 12c

连接oracle 12c的方式：

datasource:
  driver-class-name: oracle.jdbc.driver.OracleDriver
  url: jdbc:oracle:thin:@//ip:port/SID
  username: xxx
  password: xxx

oracle 12c连接方式的url与oracle 11g的区别在于：ip之前是双斜线(//)，在端口号后面把冒号(:)改成斜线(/)。

3、SQL Server

在SQL Server数据库驱动jar包文件下的MENIFEST.MF文件中没有与数据库驱动版本有关的信息。

3.1、SQL Server 2005/2008/2017

driver：com.microsoft.sqlserver.jdbc.SQLServerDriver

url：jdbc:sqlserver://localhost:1433; databasename=dbname

3.2、SQL Server 2000

driver：com.microsoft.jdbc.sqlserver.SQLServerDriver

url：jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbname

加载SQL server2000驱动，需要导入sqljdbc.jar包，同时还需要导入msbase.jar、mssqlserver.jar、msutil.jar包。

自从sqljdbc4.jar包之后，微软不再提供SQL server 2000的驱动包下载，2000和2005/2008的驱动包都使用同一个了，即SQL server 2000与SQL server 2005的驱动类和url都是一样的了，即driver：com.microsoft.sqlserver.jdbc.SQLServerDriver

url：jdbc:sqlserver://ip:port;database=dbname

目前较新的SQL server驱动jar包是：sqljdbc42.jar

4、DB2

在DB2数据库驱动jar包文件下的MENIFEST.MF文件中没有与数据库驱动版本有关的信息。

driverC：com.ibm.db2.jcc.DB2Driver

url：jdbc:db2://127.0.0.1:50000/dbname

第一种：通用连接方式
    驱动类：com.ibm.db2.jcc.DB2Driver
    JDBC连接URL：jdbc:db2://ip:port/dbname 
    说明：这种连接方式不需要DB2 V9开始分离出了客户端和服务器端，先前的版本没有单独的客户端软件，推荐使用这种连接方式。

第二种：客户端软件连接方式
    驱动类：com.ibm.db2.jcc.DB2Driver
    JDBC连接URL：jdbc:db2:<数据库在本地别名>
    说明：这种连接方式需要用户安装DB2客户端或者服务器端软件。然后利用DB2软件，连接到服务器上，并给所连接的数据库启一个别名，作为数据库表示，公给本地应用软件使用，这对本地软件来说是透明的。这适合小型局域网内。

1、COM.ibm.db2.jdbc.app.DB2Driver

这是一种 Type 2 的 JDBC 驱动程序，它通过 DB2 本地客户机库的帮助建立和 DB2本地数据库或是远程数据库的连接（事先将远程数据库编目到本地）。因此，我们必须在应用系统所在的机器上同时部署 DB2 本地客户机库，这也许是它最大的一个不足之处。

使用格式如下：

Driver Name：COM.ibm.db2.jdbc.app.DB2Driver

URL Pattern：jdbc:db2:databasename

databasename: 需要访问的数据库名

2、COM.ibm.db2.jdbc.net.DB2Driver

这是一种 Type 3 的 JDBC 驱动程序，它通过与一台已经部署了 DB2 本地客户机库的机器通信来建立和 DB2 远程数据库的连接。

使用格式如下：

Driver Name：COM.ibm.db2.jdbc.net.DB2Driver

URL Pattern：jdbc:db2:ServerIP:databasename

ServerIP: 需要访问的数据库所在机器IP地址

databasename: 需要访问的数据库名

5、sybase

sybase数据库驱动jar包没有下载到。

driver：com.sybase.jdbc.SybDriver

url：jdbc:sybase:Tds:localhost:5007/dbname

6、PostgreSQL

在postgresql数据库驱动jar包文件下的MENIFEST.MF文件中，Bundle-Version字段表示数据库驱动版本。

driver：org.postgresql.Driver

url：jdbc:postgresql://localhost/dbname

7、Informix

在postgresql数据库驱动jar包文件下的MENIFEST.MF文件中，Bundle-Version字段表示数据库驱动版本。

连接字符串格式：

jdbc:数据库驱动名称:数据库服务器ip:端口号/数据库名称:INFORMIXSERVER=服务器名

jdbc:informix-sqli://ip:port/dbname:INFORMIXSERVER=myserver

其中，myserver是安装informix数据库的服务器名称

driver：com.informix.jdbc.IfxDriver

url：jdbc:informix-sqli://123.45.67.89:1533/myDB:INFORMIXSERVER=myserver

8、odbc桥接

driver：sun.jdbc.odbc.JdbcOdbcDriver

url：jdbc:odbc:dbname – 设置数据库连接字符串

url：jdbc:odbc:;Driver={Microsoft Access Driver (*.mdb)};DBQ=路径\数据库名称 – 匿名连接，不需要配置ODBC数据源

url：jdbc:odbc:;Driver={SQL Server};server=主机名\实例名;database=数据库名称 – 匿名连接，不需要配置ODBC数据源