数据库连接池

数据库连接池

数据库连接是一种关键的、有限的、昂贵的资源，这一点在多用户的应用中体现得尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正是针对这个问题提出来的

• 在进行JDBC操作过程中，几乎一半以上的时间是用来获取数据库连接。没有办法减少一次使用数据库连接的代价，所以考虑使用池化资源的方式降低连接的平均使用成本

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

概述连接池

1、什么是数据库连池

连接Connection对象的缓冲区。由连接池的管理器负责申请、分配、管理、释放连接的操作。

2、为什么要使用数据库连接池

• 不使用数据库连接池，每次都通过DriverManager获取新连接，用完直接抛弃断开，连接的利用率太低，太浪费
• 对于数据库服务器来说，压力太大了。数据库服务器和Java程序对连接数也无法控制，很容易导致数据库服务器崩溃
• 希望能管理连接。可以建立一个连接池，这个池中可以容纳一定数量的连接对象，一开始可以先替用户先创建好一些连接对象，等用户要拿连接对象时，就直接从池中拿，不用新建了，这样也可以节省时间。然后用户用完后，放回去，别人可以接着用。可以提高连接的使用率。当池中的现有的连接都用完了，那么连接池可以向服务器申请新的连接放到池中。直到池中的连接达到最大连接数，就不能在申请新的连接了，如果没有拿到连接的用户只能等待

3、市面上有很多现成的数据库连接池组件

• JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口（通常被称为数据源），该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
  • DBCP 是Apache提供的数据库连接池，速度相对c3较快，但因自身存在BUG，Hibernate从3+以后就不再提供支持
  • C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以
  • Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
  • BoneCP 是一个开源组织提供的数据库连接池，速度快
  • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，并且提供了防火墙、慢查询统计等功能

德鲁伊连接池

依赖： druid-1.2.8.jar

第一步：建立一个数据库连接池

第二步：设置连接池的参数

第三步：获取连接

public class TestPool {
    public static void main(String[] args) throws SQLException { 
        //1、创建数据源（数据库连接池）对象 
        DruidDataSource ds =new DruidDataSource(); 
        //2、设置参数 
        //(1)设置数据库连接的基本参数 
       ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/test");
        ds.setUsername("root"); 
        ds.setPassword("123456"); 
        //(2)设置连接池的连接参数等相关参数 
        ds.setInitialSize(5);
        //一开始提前申请好5个连接，不够了，重新申请 
        ds.setMaxActive(10);//最多不超过10个，如果10都用完了，还没还回来，就会出现等待
        ds.setMaxWait(1000);//用户最多等1000毫秒，如果1000毫秒还没有人还回来，就异常了
        //3、获取连接 
        for (int i = 1; i <=15; i++) {
            Connection conn = ds.getConnection(); 
            System.out.println("第"+i+"个：" + conn);
            //如果这里没有关闭，就相当于没有还 
            // conn.close();#这里关闭，是还回池中 
        }
    }
}

常见配置参数

配置	缺省	说明
proxyFilters		类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分 开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” +System.identityHashCode(this)
jdbcUrl		连接数据库的url，不同数据库不一样。如mysql数据库为jdbc:mysql://10.20.153.104:3306/druid2，针对oracle数据库 dbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		连接数据库的用户名
password		连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用 ConfigFilter
driverClassName		根据url自动识别，可配可不配，如果不配置druid会根据url自动识别dbType，然 后选择相应的driverClassName
initialSize	0	初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次 getConnection时
maxActive	8	最大连接池数量
minIdle		最小连接池数量
maxWait		获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并 发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements	false	是否缓存preparedStatement，PSCache对支持游标的数据库性能提升巨大，比如 说oracle。在mysql下建议关闭
maxOpenPreparedStatements	-1	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触 发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为 null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲 时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效
timeBetweenEvictionRunsMillis		有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据
minEvictableIdleTimeMillis		一个链接的最小存活时间，单位为毫秒
connectionInitSqls		物理连接初始化的时候执行的sql
exceptionSorter		根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接
filters		属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的 filter:stat，日志用的filter:log4j，防御sql注入的filter:wall

连接池的使用

在一个线程的处理过程中可能会出现多次访问数据库，线程使用连接对象时没有必要频繁的从池中获取，再归还，再获取。可以在一个线程开始执行时获取对应的连接，处理完毕后再归还连接

配置文件：src/jdbc.properties

#key=value
driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC 
username=root 
password=123456 
initialSize=5 
maxActive=10
maxWait=1000

JDBCTools工具类：

package Jdbutils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class JdbcUtils {
	private JdbcUtils() {
	}

	private static DataSource dataSource;
	private static final ThreadLocal<Connection> ts = new ThreadLocal<>();
	static {
		try {
			InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties ps = new Properties();
			ps.load(is);
			dataSource = DruidDataSourceFactory.createDataSource(ps);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static int executeUpdate(String sql, Object... params) {
		try {
			return createPreparedStatement(sql, params).executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
	}

	public static ResultSet executeQuery(String sql, Object... params) {
		try {
			return createPreparedStatement(sql, params).executeQuery();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
	}

	public static PreparedStatement createPreparedStatement(String sql, Object... params) {
		try {
			Connection conn = getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0)
				for (int i = 0; i < params.length; i++)
					ps.setObject(i + 1, params[i]);
			return ps;
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
	}

	// 获取连接的方法
	public static Connection getConnection() {
		Connection res = ts.get();
		// 从当前线程中获取和当前线程相管理的连接对象
		if (res == null) {
			try {
				res = dataSource.getConnection();
				// 从连接池中获取一个连接
				ts.set(res);
				// 将连接对象与当前线程绑定
			} catch (Exception e) {
				throw new RuntimeException(e.getMessage());
			}
		}
		return res;
	}

	// 释放连接的方法
	public static void releaseConnection() {
		Connection conn = ts.get();
		// 获取当前线程中的所存儲的连接
		if (conn != null) {
			try {
				conn.close();
				// 向连接池归还连接对象
				ts.remove();
				// 将已经关闭的连接从当前线程中移除
			} catch (Exception e) {
				throw new RuntimeException(e.getMessage());
			}

		}
	}

}

ThreadLocal

JDK 1.2的版本中就提供java.lang.ThreadLocal，为解决多线程程序的并发问题提供了一种新的思路。使用这个工具类可以很简洁地编写出优美的多线程程序。通常用来在在多线程中管理共享数据库连接、Session等

ThreadLocal用于实现保存某个线程独享的变量，原因是在Java1.8+中每个线程对象中都有一个ThreadLocalMap<ThreadLocal, Object> ，其key就是一个ThreadLocal，而Object即为该线程的变量。而这个map是通过ThreadLocal的set和get方法操作的。对于同一个staticThreadLocal，不同线程只能从中get，set，remove自己的变量，而不会影响其他线程的变量。

1、ThreadLocal.get: 获取ThreadLocal中当前线程共享变量的值。

2、ThreadLocal.set: 设置ThreadLocal中当前线程共享变量的值。

3、ThreadLocal.remove: 移除ThreadLocal中当前线程共享变量的值

DAO模式

一般的DAO最少需要实现CRUD方法

一个接口、若干实现、一个工厂、一个值bean

接口定义

public interface IBaseDao<T extends Serializable> { 
    public int save(T t);
    //还应该有方法
    public int delete(T t); // 自行实现
    public int update(T t); // 自行实现 
    public List<T> query(String sql,Object...params); 
}

父类

package IDao;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import Dao.UserDao;
import Jdbutils.JdbcUtils;
import entity.User;

public class BaseDaoImpl<T extends Serializable> implements IBaseDao<T>{
	protected Class<T> clazz;
	@SuppressWarnings({ "unchecked", "rawtypes" }) 
    public BaseDaoImpl() {
        Class<? extends IBaseDao> clz = this.getClass(); 
        ParameterizedType pt = (ParameterizedType) clz.getGenericSuperclass();
        clazz = (Class) pt.getActualTypeArguments()[0];
    }
	@Override
	public int save(T obj) {
		StringBuilder sb1 = new StringBuilder("insert into ");
		sb1.append(clazz.getSimpleName().toLowerCase()).append("(");
		StringBuilder sb2 = new StringBuilder(") values(");
		Field[] fs = clazz.getDeclaredFields();
		List<Object> params = new ArrayList<>();
		try {
			for (int i = 0; i < fs.length; i++) {
				Field f = fs[i];
				f.setAccessible(true);
				Object val = f.get(obj);
				if (val != null) {
					sb1.append(f.getName()).append(",");
					sb2.append("?,");
					params.add(val);
				}
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		if (sb1.toString().endsWith(","))
			sb1.deleteCharAt(sb1.length() - 1);
		if (sb2.toString().endsWith(","))
			sb2.deleteCharAt(sb2.length() - 1);
		String sql = sb1.toString() + sb2.toString() + ")";
		return update(sql, params.toArray());
	}

	@Override
	public int update(String sql, Object... params) {
		int res = 0;
		try {
			res = JdbcUtils.executeUpdate(sql, params);
		} finally {
			JdbcUtils.releaseConnection();
		}
		return res;
	}

	@Override
	public List<T> find(User obj) {
		List<T> res = new ArrayList<>();
		try {
			StringBuilder sb = new StringBuilder("select * from ");
			sb.append(clazz.getSimpleName().toLowerCase()).append(" where 1=1 ");
			// 生成sql语句
			Field[] fs = clazz.getDeclaredFields();
			List<Object> params = new ArrayList<>();
			for (int i = 0; i < fs.length; i++) {
				Field f = fs[i];
				f.setAccessible(true);
				Object val = f.get(obj);
				if (val != null) {
					sb.append(" and ").append(f.getName()).append("=? ");
					params.add(val);
				}
				System.out.println(params);
			}

			System.out.println(sb.toString());
			ResultSet rs = JdbcUtils.executeQuery(sb.toString(), params.toArray());
			while (rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				User t = clazz.newInstance();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String fieldName = rsmd.getColumnLabel(i);
					// 获取列名称
					Field f = clazz.getDeclaredField(fieldName);
					f.setAccessible(true);
					f.set(t, rs.getObject(i));
					// 对t对象中对应的属性进行赋值
				}
				res.add(t);
			}
			rs.close();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		} finally {
			JdbcUtils.releaseConnection();
		}
		return res;
	}

}

获取数据表的元数据

ResultSetMetaData对象保存了所有ResultSet对象中关于字段的信息，提供了对应的方法获取字段相关的信息

• int getColumnCount()获取ResultSet对象中的字段个数

• String getColumnName(int index)获取ResultSet对象中指定序号对应字段的名称

获取表的元数据信息

ResultSet rs=ps.executeQuery(); 
ResultSetMetaData rsmd= rs.getMetaData(); 
int len=rsmd.getColumnCount();
for(int i=1;i<=len;i++){
    String columnName=rsmd.getColumnName(i);
    String typeName=rsmd.getColumnTypeName(i);
    System.out.println(columnName+"\t"+typeName);
}

SQLException

调用JDBC API方法时经常会有一个受检型异常/非运行时异常，必须在编码过程中针对异常进行处理。但是大部分出现的异常是不能通过编码解决，例如SQL语句语法错误、关闭Connection对象时出错。而且SQLException的颗粒度太粗，不能明确表达异常的原因。

所以常见的处理方案有2种：1、向上抛出异常或者记录报错信息。2、将SQLException转换为运行时异常，由上层调用方进行处理

XxxDAO

使用BaseDaoImpl实现具体类的增删改查

create table tb_user( 
    id bigint primary key auto_increment,
    username varchar(20) not null unique,
    password varchar(20) not null 
)engine=innodb default charset utf8;

根据对应的表结构定义实体类

• 注意规则：类名称和表名称对应，属性名称和字段名称对应

• 类中的属性不要使用简单类型，例如long

  ​ 判断的需要

  ​ setObject

public class User implements Serializable{
    private Long id;
    private String username;
    private String password; 
}

定义对应的接口

public interface IUserDao extends IBaseDao<User>{ 
    //如果有userDao中需要添加的特殊方法，声明在这里
}

定义对应的实现类

public class UserDaoImpl extends BaseDaoImpl<User> implements IUserDao{
    //如果有特殊方法，则在这里实现，实际上通用的CRUD方法已经从BaseDao中继承
}

定义工厂类用于创建具体的实现类对象

public class DaoFactory{ 
    public static IUserDao getUserDao(){
        return new UserDaoImpl(); 
    } 
}

操作方法补充

JDBC 指 Java 数据库连接，是一种标准Java应用编程接口JAVA API，用来连接 Java 编程语言和广泛的数据库

create table tb_student(
    id bigint primary key auto_increment comment '学生编号', 
    name varchar(32) not null
)engine=innodb default charset utf8mb4;

获取自增长键值

通过JDBC往数据库的表格中添加一条记录，其中有一个字段是自增的 id bigint primary key

auto_increment ，那么在JDBC这边怎么在添加之后直接获取到这个自增的值。

PreparedStatement是Statement的子接口。Statement接口中有一些常量值，如Statement.RETURN_GENERATED_KEYS

要先添加后获取到自增的key值：

（1）PreparedStatement pst =

conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

（2）添加sql执行完成后,通过PreparedStatement的对象调用getGeneratedKeys()方法来获取自增长键值，遍历结果集 ResultSet rs = pst.getGeneratedKeys();

批处理

批处理：批量处理sql。例如1）订单明细表的多条记录的添加。2）批量添加模拟数据 等

不用批处理，和用批处理有什么不同？

• 批处理的效率很多

如何进行批处理操作？

ps.addBatch(); 

int[] all = ps.executeBatch(); 

如果批处理优势不明显，则没有必要使用批处理了，因为限制太多了

可滚动结果集

默认情况下ResultSet结果集是单向的，也就是只能通过rs.next方法单向的读取数据；如果在读取数据库中的记录时不用逐个逐个的读取，而可以根据具体需要跳过几条记录再进行读取，或者从后向前读取。

PreparedStatement prepareStatement(String sql, int resultSetType,int resultSetConcurrency) throws SQLException;

• 参数1为要指定的sql语句

• 参数2为结果集的类型，可以使用的ResultSet接口中的3个常量值

  • ​ TYPE_SCROLL_INSENSITIVE 可滚动不敏感
  • TYPE_SCROLL_SENSITIVE 可滚动敏感的
  • TYPE_FORWARD_ONLY 单向滚动，只能调用next

• 参数3用于设置结果集的并发性支持

  • ​ CONCUR_READ_ONLY 并发访问时只读。其中concur译作同时发生，即并发，即当并发访问结果集的时候只能够只读访问，也就是只能读取里面的内容，不能做更改
  • CONCUR_UPDATABLE 访问的同时允许修改

要做到这点就是在创建Statement对象时使用两个属性做参数 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)

跨数据库平台分页显示

真正在数据显示时，不能一次性加载所有数据，建议采用的是按需加载

limit

MySQL针对获取部分数据提供了关键字limit，可以用于获取查询结果集中的部分数据

例如获取前10条数据 select * from tb_student limit 10;

• 实际上等价于 select * from tb_student limit 0,10

获取从第5行开始的3条数据 select * from tb_student limit 5-1,3;

• 行数从0开始

可修改的结果集

由于执行性能的问题，一般不建议使用

// MySQL驱动支持JDBC4 
try (Connection conn = DriverManager.getConnection("jdbc:mysql:///test? serverTimezone=UTC", "root", "123456")
    ) {
    PreparedStatement ps = conn.prepareStatement("select * from tb_student", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = ps.executeQuery(); 
    //获取第3行数据 
    rs.absolute(3); 
    //在当前行上执行修改指定列数据的操作 
    rs.updateString("name","zz"); 
    rs.updateRow();//修改修改 
    // 插入数据 r
    s.moveToInsertRow();//将结果集指针移动到插入行上 
    rs.updateString("name","张三");
    rs.insertRow();//插入数据 
    //删除当前行数据,删除第5行数据
    rs.absolute(5);
    rs.deleteRow();//删除当前行
    rs.close(); ps.close(); 
}

特殊占位符

• 模糊查询， select * from t_user where username like ‘%a%’;使用 concat(‘%a%’)

select * from t_user where username like concat('%',?,'%')

• 批量删除 String ids = "1,2,3"; delete from t_user where id in('1,2,3');

  • 方法一：用 in 并且字符串拼接。 String ids = "1,2,3"; ，执行sql String sql="delete from t_user where id in(" + ids + ")";

  • 方法二：用 or ，并且用 1 = 1 或 1 != 1

    在 where 的条件判断中，可以使用 1 = 1 更好的拼接 and 条件，使用 1 != 1 更好的拼接or 条件

    呈现结果如： delete from t_user where 1!=1 or id = 1 or id = 2 or id = 3