jdbc基础2

稍微总结下上篇的知识点，并记录一些提高部分的知识

jdbc

一、jdbc知识回顾

1. 基本概念

java 数据库连接, 是java提供的一套api接口，以一种一致的方式，访问不同的数据库(mysql,oracle,sqlserver)

java.sql.*
java.sql.Driver 驱动(如何连接数据库)
java.sql.Connection 连接（代表java程序和数据库之间连接通道）
java.sql.Statement 执行sql语句
java.sql.PreparedStatement 使用占位符，有效防止sql注入
java.sql.CallableStatement
java.sql.ResultSet 结果集 代表的是从数据库查询结果
java.sql.DriverManager 工具类，用来获取Connection
java.sql.SQLException 代表执行sql过程中出现的异常

具体的实现由数据库厂商来提供

MySQL Connector/J 是mysql提供的针对jdbc接口的实现，以jar包方式提供

使用前需要在编译器中加入mysql驱动jar包

2. 使用jdbc编程的步骤

1. 加载驱动 （在新版的jdbc中可以省略此步骤）
2. 创建连接，创建Connection对象
3. 创建Statement 对象
4. 执行sql语句（执行增删改或查询）
5. 关闭释放资源

3. 重要接口API

3.1 ResultSet

.next() 方法，移动到结果集的下一条记录，如果返回true表示有下一条记录，否则返回false

getXXX(int 列下标) 用来获取结果集中某一列的数据，其中XXX为数据类型，如果是字符串使用 getString 如果是整数，使用getInt … ，列下标从1开始

getXXX(int 列名) 用来获取结果集中某一列的数据，其中XXX为数据类型，如果是字符串使用 getString 如果是整数，使用getInt …

3.2 PreparedStatement

要求SQL语句中的值使用?占位符来占位，然后通过一系列的setXXX
方法来给?赋值，XXX根据值的类型决定，例如?为字符串调用setString,?为整数调用setInt
setXXX(？下标, 值） 下标也是从1开始

4. 编程的编码规范：

像连接字符串，数据库用户名密码都可以定义为静态常量，静态常量名大写，如果有多个单词中间用下划线分隔

二、jdbc的高级知识

12、提升性能

12.1 重用sql

默认情况下，通过jdbc执行一条sql语句流程如下：

1. 将sql语句从客户端程序发送给数据库服务器 executeUpdate, exeucteQuery
2. 由命令解析器进行词法分析、语法分析、生成解析树
3. 如果是查询，还要生成查询计划，对sql执行进行优化
4. 由访问控制模块检查权限、生成新的解析树
5. 进入表管理模块，打开对应的表文件
6. 调用存储引擎，执行
7. 将结果返回给客户端程序

其中2. 3. 4. 步每次都要执行，即使sql语句很类似：

select * from student where id = 1001;
select * from student where id = 1002;

如果希望2. 3. 4. 步能够被重用，可以利用预编译的sql：

prepare sql1 from 'select * from student where sid = ?';

set @param:=1001;
execute sql1 using @param;

set @param:=1002;
execute sql1 using @param;

这样只有在执行prepare的时候进行了2. 3. 4. 步，而后面的execute执行时就不必执行2. 3. 4. 步了。

jdbc中要利用预编译的功能，需要使用PreparedStatement，普通Statement不行，另外对于MySQL来说，要在jdbc 连接字符串中加入参数：useServerPrepStmts=true&cachePrepStmts=true
其中：

• useServerPrepStmts=true是开启MySQL的预编译功能，即PreparedStatement对象会利用prepare语句
• cachePrepStmts=true是同一个连接的多个PreparedStatement对象能够被缓存，否则一旦PreparedStatement对象关闭，则下一个PreparedStatement对象执行相同sql时，还是会重新执行prepare

12.2 批处理

批处理的好处是可以一次性执行多条sql语句

addBatch
executeBatch

MySQL必须添加rewriteBatchedStatements=true才能真正启用批处理功能

12.3 游标支持

默认情况下，MySQL Connector/J 查询时是一次将所有结果返回，这样记录特别大时就会出现OOM异常，可以通过添加useCursorFetch=true&defaultFetchSize=100来启用游标的方式来使每次返回部分结果，返回的数量由语句中数字决定

12.4 启用日志

my.cnf文件：

[mysqld]
log-output=FILE
log_timestamps=SYSTEM
general-log=1
general_log_file="E:\mysql-8.0.12-winx64\data\mysql.log"
slow-query-log=1
slow_query_log_file="E:\mysql-8.0.12-winx64\data\mysql_slow.log"
long_query_time=2

在安装服务时采用：

mysqld --install MySql --defaults-file=E:\mysql-8.0.12-winx64\my.cnf

12.5 连接池

因为每次创建连接会浪费大量的时间，连接池大大增加了程序执行效率

1. 测试获取连接的时间
2. 测试执行sql的时间
3. 使用成熟的连接池实现

配置	缺省值	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。如果没有配置，将会生成一个名字，格式是：“DataSource-” + System.identityHashCode(this). 另外配置此属性至少在1.0.5版本中是不起作用的，强行设置name会出错。详情-点此处。
url		连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		连接数据库的用户名
password		连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里
driverClassName	根据url自动识别	这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName
initialSize	0	初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive	8	最大连接池数量
maxIdle	8	已经不再使用，配置了也没效果
minIdle		最小连接池数量
maxWait		获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements	false	是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxPoolPreparedStatementPerConnectionSize	-1	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句，常用select ‘x’。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
validationQueryTimeout		单位：秒，检测连接是否有效的超时时间。底层调用jdbc Statement对象的void setQueryTimeout(int seconds)方法
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
keepAlive	false（1.0.28）	连接池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作。
timeBetweenEvictionRunsMillis	1分钟（1.0.14）	有两个含义：1) Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun	30分钟（1.0.14）	不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis		连接保持空闲而不被驱逐的最小时间
connectionInitSqls		物理连接初始化的时候执行的sql
exceptionSorter	根据dbType自动识别	当数据库抛出一些不可恢复的异常时，抛弃连接
filters		属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall
proxyFilters		类型是List<com.alibaba.druid.filter.Filter>，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系