jdbc基础2

谁践踏了优雅 2022-05-10 10:14 141阅读 0赞

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

# 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.  使用成熟的连接池实现

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