Java JDBC_DBCP_DbUtils源码解析

Java JDBC

Java 和数据库的连接方法
1. Native AIP - 无法跨平台
2. ODBC/JDBC-ODBC (效率很差,无法跨平台)
3. JDBC(主流): Java Database Connectitivy
```
  * JDBC 1(1.1) / JDBC 2(1.3~1.4) / JDBC 3(5) / JDBC 4 (6) 4.1(7) 4.2(8)
  * java.sql.* && javax.sql.* 主要是接口
```

JDBC 执行步骤

构建桥接（搭桥）

  1. 注册驱动，寻找材质
  2. 确定对岸目标，建桥 Connection

执行操作(派人过桥，提着篮子，去拿数据)

  1. Statement
      1. executeQuery() Process Query, Return Into ResultSet
      2. executeUpdate() Process Inerst/Update/Delete, Return Work Rows
      3. One Statment Query One Time
  2. ResultSet
      1. next: check next record
      2. getInt/getString/getDouble

释放连接
```
  1. connection.close()
```

注意事项

  1. ResultSet 不能多个做笛卡尔积
  2. ResultSet 最好不要超过百条，否则影响性能
  3. ResultSet 也不是一口气加载所有的 Select 结果数据
  4. Connection 很昂贵，需要及时 close
  5. Connection 所用的 Jar 包和数据库要匹配

JDBC 高级操作

事务处理 - Database Transaction

  1. 作为单个逻辑工作单位执行一系列操作，要么完全执行要么完全不执行
  2. 事务必须满足ACID（原子性、一致性、隔离性和持久性）
  3. 事务时数据库运行中的逻辑工作单位，由 DBMS 中的事务管理子系统负责事务的处理

JDBC 事务

  1. 关闭自动提交，实现多语句同一事务
  2. connection.setAutoCommit(false)
  3. connection.commit() -> submit Transaction
  4. connection.rollback() -. rollback Transaction
  5. 保存点机制
      1. connection.setSavepoint()
      2. connection.rollback(SavePoint)

PreparedStatement - 拼接 SQL 字符串很危险

  1. 与 Statement 相比，使用“？”来代替字符串拼接
  2. 使用 setXXX（int, Object)的函数来时间对于 ？ 的替换
      * 不需要考虑字符串两侧的单引号
      * 参数赋值，清晰明了，拒绝凭借错误
  3. 提供 addBatch 批量更新功能
  4. Select 语句一样用 ResultSet 接收结果
  5. 优势
      1. 防止注入攻击
      2. 防止繁琐的字符串拼接和错误
      3. 直接设置对象而不需要转换为字符串
      4. PrepareStatement 使用预编译速度相对 Statement 速度更快

ResultSetMetaData: 获取 ResultSet 返回的信息

  1. getColumnCount(i): 返回结果的列数
  2. getColumnClassName(i): 返回第 i 列的数据的 Java 类名
  3. getColumnTypeName(i): 返回第 i 列的数据库类型名称
  4. getColumnType(i); 返回第 i 列的 SQL 类型

数据库连接池

Connection 是 Java 和数据库两个平行系统的桥梁
桥梁构建不易，成本很高，单次使用成本昂贵

运用共享技术实现数据库连接池（亨元模式 Flyweight Pattern->一个系统中存在大量的相同对象，减少系统中对象的数量）

  1. 降低系统中数据库连接 Connection 对象的数量
  2. 降低数据库服务器的连接响应消耗
  3. 提供 Connnection 获取的响应速度

理解池 Pool 的概念

  1. 初始数、最大数、增量、超时时间等参数
  2. 常用的数据库连接池： DBCP / C3P0 / Druid
  3. 数据库连接池不能代替数据库连接

Apache DBCP - Database Connection and Pooling Questions

What is a Connection Pool?
- When connecting to a database, a network connection needs to be established. This takes time, and if an area of code is called multiple times without using the same connection, the time delay becomes very noticeable.
- A better solution is to use a connection pool , and be sure to close connections when you are finished with them. This also provides a large performance boost, because connections can be re-used.

Do I need to call Connection.close()? Won't the connection be closed automatically when the session is destroyed?

You must always call Connection.close() when using a connection pool. Physical database connections will only free up if the java object is destroyed.

  1. If you are using a connection pool, the pool will retain a reference to the connection, meaning the garbage collector never sees it as an orphaned object, so never destroys it. 
  2. If your VM has a large heap (eg: 512mb RAM), it might take a long time for the garbage collection to even run at all 
  3. Even if the connection did close automatically, if you have a session timeout of 60 minutes, and have 100 people visit the site during an hour, those 100 connections would stay connected for the next hour. 
  4. Warning: if you do not close connections, a Connection Leak will occur, and your pool will run out of connections. When this happens, your application will pause indefinitely waiting for a connection to be freed up, and will need to be restarted manually

Apache DbUtils

未使用 DbUtils 的，使用 JDBC 的弊端
1. connection, statement, resultset repeat code
2. a lot of ineffective close cdoe
DbUtils 三个核心的类
1. DbUtils: A collection of JDBC helper methods / All methods are static
```
  * loadDriver / close / commit / rollback 
```
2. QueryRunner: Executes SQL queries with pluggable strategies for handling ResultSet
```
  1. int Update
  2. T Query / insert
  3. int[] batch
```
3. ResultSetHandler: Implementations of this interface convert ResultSets into other objects
ResultSetHanlder OverView
1. AbstractKeyedHandler<K,V> ResultSetHandler implementation that returns a Map.
```
  * 将ResultSet转换为Map<Map>的ResultSetHandler实现类
```
2. AbstractListHandler Abstract class that simplify development of ResultSetHandler classes that convert ResultSet into List.
```
  * ResultSet转为List的抽象类
```
3. ArrayHandler ResultSetHandler implementation that converts a ResultSet into an Object[].
```
  * 将ResultSet转为一个Object[]的ResultSetHandler实现类
```
4. ArrayListHandler ResultSetHandler implementation that converts the ResultSet into a List of Object[]s.
```
  * 将ResultSet转换为List<Object[]>的ResultSetHandler实现类
```
5. BeanHandler ResultSetHandler implementation that converts the first ResultSet row into a JavaBean.
```
  * T 对应 Class
```
6. BeanListHandler ResultSetHandler implementation that converts a ResultSet into a List of beans.
```
  * 将ResultSet转换为List<JavaBean>的ResultSetHandler实现类
```
7. BeanMapHandler<K,V> ResultSetHandler implementation that returns a Map of Beans.
```
  * 将ResultSet首行转换为一个JavaBean的ResultSetHandler实现类
```
8. ColumnListHandler ResultSetHandler implementation that converts one ResultSet column into a List of Objects.
```
  * 将ResultSet的一个列转换为List<Object>的ResultSetHandler实现类
```
9. KeyedHandler ResultSetHandler implementation that returns a Map of Maps
```
  * 将ResultSet转换为Map<Map>的ResultSetHandler实现类
```
10. MapHandler ResultSetHandler implementation that converts the first ResultSet row into a Map.
```
  * 将ResultSet的首行转换为一个Map的ResultSetHandler实现类
```
11. MapListHandler ResultSetHandler implementation that converts a ResultSet into a List of Maps.
```
  * 将ResultSet转换为List<Map>的ResultSetHandler实现类
```
12. ScalarHandler ResultSetHandler implementation that converts one ResultSet column into an Object.
```
  * 将ResultSet的一个列到一个对象
```

Java JDBC_DBCP_DbUtils源码解析

Java JDBC_DBCP_DbUtils源码解析

Java JDBC

Apache DBCP - Database Connection and Pooling Questions

Apache DbUtils

添加新评论

最新文章

最近回复

分类

其它