思考1:原始JDBC查询大致分为六个步骤:
(1) 注册数据库驱动(动态加载类)
(2) 创建数据库连接
(3) 执行SQL脚本
(4) 运行数据处理逻辑
(5) 关闭资源
(6) finally关闭句柄,关闭连接
package com.cisco.eStore.test.dao;
import org.apache.log4j.Logger;
import java.sql.*;
public class UserDao {
static Logger logger = Logger.getLogger(UserDao.class);
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/estore";
// Database credentials
static final String USER = "clops";
static final String PASS = "cscocmse";
public UserDao(){
}
public static void main(String [] args){
UserDao userDao = new UserDao();
try {
userDao.getUserList();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public void getUserList() throws SQLException, ClassNotFoundException {
// 1. Register Driver
Class.forName(JDBC_DRIVER);
// 2. Open Connection
Connection con = DriverManager.getConnection(DB_URL, USER, PASS);
PreparedStatement pstmt = null;
Statement stat = null;
try {
// 3. Execute Query Sql
con.setAutoCommit(false);
String sql = "select name, password, email from user where uuid = ? ";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "1");
ResultSet rs = pstmt.executeQuery();
// 4. do Data logic
while(rs.next()){
logger.error("name: "+ rs.getString("name"));
}
logger.error("End Query");
// 5. close Resource
rs.close();
con.commit();
pstmt.close();
} catch(Exception e){
logger.error(e.getStackTrace());
} finally {
// 6. Always close connection
try{
if(pstmt!=null){
pstmt.close();
}
}catch(SQLException e){
}
try{
if (con != null){
con.close();
}
}catch (SQLException e){}
}
}
}
思考2: 如何使用配置文件配置用户名密码
(1) 建立配置文件:db.properties
db.url=jdbc:mysql://localhost:3306/estore
db.username=clops
db.password=cscocmse
db.driver=com.mysql.jdbc.Driver
(2) Properties读取相应字段名称
static {
Properties prop = new Properties(); //第一步:新建一个配置文件对象
try {
Reader in = new FileReader("C:\\Develop\\IDEA\\eStore\\target\\classes\\db.properties"); //第二步:指定输入流为文件输入流
prop.load(in);
} catch (Exception e) {
e.printStackTrace();
}
JDBC_DRIVER = prop.getProperty("db.driver"); // getProperty(“driver”)用于获取配置文件 driver= 后的值
DB_URL = prop.getProperty("db.url");
USER = prop.getProperty("db.username");
PASS = prop.getProperty("db.password");
}
思考3:为何使用Class.forName("com.mysql.jdbc.driver"), 动态加载类,可以直接注册数据库驱动?
(1) 查看Driver类源码,发现在类静态块中调用了Driver.register()的注册方法
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
思考4: 如何复用重复的建立连接,Connection的创建 方式, 除了DriverManager,还有什么?
(1) 跟据oracle官网的描述,还可以使用DataSource Objects来生成相应的Connections, 这也是官网推荐的从数据源更好的获取connection的方式。DataSource提供了Pool(池)和Distribute transactions(分布式事务)的功能实现,这些对于分布式系统有非常重要的作用。
ref: https://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html
思考5:如何使用数据库连接池,分配Connection?
基本的实现原理为:使用某种容器(连接池)存储所有打开的Connection,需要的时候分配池中的Connection给调用方,close时返回给数据库连接池。这么做的原因是:打开,关闭数据库连接是耗资源的,所以复用Connection在高并发情况下,有重要意义。
是否使用连接池的性能测试:
private void test(DbcpUtil dbcpUtil, int count){
BasicDataSource datasource = dbcpUtil.initDataSource();
// Testing 1w in single thread
for(int i=0; i<count; i++){
long startTime=System.currentTimeMillis();
dbcpUtil.testDB(datasource);
long endTime=System.currentTimeMillis();
long time = endTime-startTime;
System.out.println("Num: "+i+" Time: "+ time + "ms");
}
}
private void testNoPool(int count){
try {
Class.forName("com.mysql.jdbc.Driver");
for(int i=0; i<count; i++){
long startTime=System.currentTimeMillis();
testDBNoPool();
long endTime=System.currentTimeMillis();
long time = endTime-startTime;
System.out.println("Num: "+i+" Time: "+ time + "ms");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private void testDBNoPool(){
try {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/estore", "clops", "cscocmse");
String sql="select * from user where name=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "sky");
ResultSet rs = pstmt.executeQuery();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private void testDB(BasicDataSource datasource){
// 3. Get Connection
try {
Connection conn = datasource.getConnection();
String sql="select * from user where name=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "sky");
ResultSet rs = pstmt.executeQuery();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
前提,localhost的mysql, 单线程循环测试Connection执行,使用不使用pool,DBCP和C3P0的时间对比:
NO POOL
|
DBCP
|
C3P0
| |
Time |
16ms
|
1ms
|
2ms
|
(1) 开源connection pool主要使用几种:DBCP和C3P0的数据库连接池
(2) DBCP是Apache的开源Connection Pool
package com.cisco.eStore.test.util;
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DbcpUtil {
public static void main(String[] args){
DbcpUtil dbcpUtil = new DbcpUtil();
dbcpUtil.testDB();
}
private void testDB(){
BasicDataSource dataSource = new BasicDataSource();
// 1. Set jdbc configuration
dataSource.setUsername("clops");
dataSource.setPassword("cscocmse");
dataSource.setUrl("jdbc:mysql://localhost:3306/estore");
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
// 2. Set connection configuration
dataSource.setInitialSize(10);
dataSource.setMaxActive(100);
// 3. Get Connection
try {
Connection conn = dataSource.getConnection();
String sql="select * from user where name=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "sky");
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
String name = rs.getString("name");
System.out.println("name: " + name);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
查看内部源码,发现内部存储数据库连接是使用GenericObjectPool存储的, GenericObjectPool到底有那些功能呢?
(3) C3P0
package com.cisco.eStore.test.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class C3P0Util {
public static void main(String[] args){
C3P0Util c3P0Util = new C3P0Util();
c3P0Util.testDB();
}
private void testDB() {
ComboPooledDataSource ds = new ComboPooledDataSource();
try {
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/estore");
ds.setUser("clops");
ds.setPassword("cscocmse");
ds.setInitialPoolSize(10);
ds.setMaxPoolSize(20);
ds.setMinPoolSize(5);
Connection conn = ds.getConnection();
String sql="select * from user where name=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "sky");
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
String name = rs.getString("name");
System.out.println("name: " + name);
}
} catch (PropertyVetoException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
(4)使用connection后如何归还connection给 connection pool呢?
I. DBCP中,实现的connection接口的PooledConnection中,在执行完数据库查询后,执行conn.close后,在内部会将连接归还给Pool。
_pool.returnObject(this); // XXX should be guarded to happen at most once
II. C3P0中,实现connection pool的是自定义的BasicResourcePool类,
/* keys are all valid, managed resources, value is a PunchCard */
HashMap managed = new HashMap();
/* all valid, managed resources currently available for checkout */
LinkedList unused = new LinkedList();
/* resources which have been invalidated somehow, but which are */
/* still checked out and in use. */
HashSet excluded = new HashSet();
Map formerResources = new WeakHashMap();
Set idleCheckResources = new HashSet();
boolean force_kill_acquires = false;
boolean broken = false;
// long total_acquired = 0;
调用connection.close()的时候,会触发:
public synchronized void close() throws SQLException {
try {
if (!this.isDetached()) {
NewPooledConnection npc = this.parentPooledConnection;
this.detach();
npc.markClosedProxyConnection(this, this.txn_known_resolved);
this.inner = null;
} else if (logger.isLoggable(MLevel.FINE)) {
logger.log(MLevel.FINE, this + ": close() called more than once.");
}
}
}
思考6:transaction(事务)的实现方式,几种隔离等级?
(1) conn.setAutoCommit(false)会取消自动提交执行句柄动作,直到conn.commit()才会按照一个事务,执行数据库处理
(2)事务处理失败后,如何回滚?可以在catch(Exception e)中,执行连接内回滚动作
try {
updateSales = con.prepareStatement(updateString);
updateTotal = con.prepareStatement(updateStatement);
con.setAutoCommit(false);
for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
updateSales.setInt(1, e.getValue().intValue());
updateSales.setString(2, e.getKey());
updateSales.executeUpdate();
updateTotal.setInt(1, e.getValue().intValue());
updateTotal.setString(2, e.getKey());
updateTotal.executeUpdate();
conn.commit();
}
} catch (SQLException e ) {
JDBCTutorialUtilities.printSQLException(e);
if (con != null) {
try {
System.err.print("Transaction is being rolled back");
conn.rollback(); /// roll back here
} catch(SQLException excep) {
JDBCTutorialUtilities.printSQLException(excep);
}
}
} finally {
if (updateSales != null) {
updateSales.close();
}
if (updateTotal != null) {
updateTotal.close();
}
con.setAutoCommit(true);
}
思考4: 避免每次重复建立释放连接?
思考3: SQL 连接查询的数量限制?如何测试?
思考4: 如何使用数据库事务,如何保证不死锁?