JavaWeb之事务&数据库连接池-原创手记-慕课网

1. 事务

Transaction 其实指的就是一组操作，里面包含许多单一的逻辑，如果有一个逻辑没有执行成功，那么

个事务就是执行失败，所有的数据都会回滚到未执行前的状态。

事务是为解决数据安全操作提出的，事务控制实际上就是控制数据的安全访问，比如银行转账。

2. 事务的使用

命令行方式

# 开启事务

start transaction;

# 提交事务

commit;

# 回滚事务

rollback;

代码方式

import org.junit.Test;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class Demo01 {

@Test

public void transactionDemo(){

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

conn = ConnectionUtil.getConnection();

//关闭事务自动提交

conn.setAutoCommit(false);

String sql = "select * from account";

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next()){

System.out.println(rs.getString("name") + "==" + rs.getInt("money"));

}

//所有操作执行完成后手动的提交一下

conn.commit();

} catch (SQLException e) {

e.printStackTrace();

try {

//回滚事务

conn.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

} finally {

ConnectionUtil.release(rs, pstmt, conn);

}

3. 事务特性(ACID)

原子性事务中包含的逻辑不可再分

一致性事务执行前后，数据的完整性保持一直

隔离性事务在执行期间，不能受到其他事务的影响

持久性事务执行成功，应该持久化到磁盘上

4. 安全问题

读

脏读

在A窗口中设置隔离级别为读未提交，在A、B两个窗口中开启事务，在B窗口中修改数据。在A中可以查询到B窗口中还未提交的数据。一个事务中读取到另一个事务还未提交的数据，就是脏读。读到的是数据库内存中的数据，并非磁盘上真正的数据。

不可以重复读

在A窗口中设置隔离级别为读已提交，在A、B两个窗口中开启事务，在B窗口中修改数据。在A中就不可以查询到B窗口还未提交的数据了，这样就解决了脏读的问题，但是这样会引发一个新的问题，那就是只能读取到已经提交的数据。这样的话，前后读取到的结果是不一致的，发生了不可重复读，所谓不可重复度，就是不能执行多次读取，否则会出现查询结果不一致。

将隔离级别设置为重复读，就可以解决整个问题了。

幻读

一个事务读取到另一个事务已插入的数据，导致查询结果不一致。

将隔离级别设置为可串行化，就可以解决这些问题了，到底可串行化是怎么解决这个问题的呢？

在A窗口中设置隔离级别为可串行化，在A、B两个窗口中开启事务，在B窗口中修改数据，只有B窗口执行commit，A窗口才可以查询数据。这个级别一般比较少用，因为它的效率比较低。

写

丢失更新

两个不同的事务在某一时刻对同一数据执行修改操作，导致第一次操作数据丢失

乐观锁

l乐观锁认为事务不一定会产生丢失更新，让事务进行并发修改，不对事务进行锁定。发现并发修改某行数据时，乐观锁抛出异常。让用户解决。可以通过给数据表添加自增的version字段进行数据修改时，数据库会检测version字段和事务中的version字段是否一致。若不一致，抛出异常，交给程序猿自己处理。

悲观锁

悲观锁认为一定会发生丢失更新，所以悲观锁要求一个事务执行提交之后，其他事务才能查询修改数据。

5. 隔离级别

Read Uncommitted 读未提交，引发脏读问题

Read Committed 读已提交，解决脏读，引发不可重复读问题(Oracle默认隔离级别)

Repeatable Read 重复读，解决不可重复读，未解决幻读(MySQL默认隔离级别)

Serializable，可串行化解决所有问题

隔离级别分类

按性能从高到低可划分为：读未提交>读已提交>重复读>可串行化

按拦截程序从高到低可划分为：可串行化>重复读>读已提交>读未提交

6. 数据库连接池

数据库在使用的时候再去创建连接，这是一件非常耗时的操作，为了改善用户体验，我们可以在程序开始的时候，在内存中开辟一块空间，称为数据库连接池，一开始往池子里放多个连接对象，如果有用户需要使用数据库连接，就从池子里取对象，当操作完成后将连接归还，这样就可以做到连接复用

自定义数据库连接池

import javax.sql.DataSource;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.SQLFeatureNotSupportedException;

import java.util.ArrayList;

import java.util.List;

import java.util.logging.Logger;

/**

* 数据库连接池

public class MyDataSource implements DataSource {

List<Connection> dataSoucePool = new ArrayList<Connection>();

public MyDataSource() {

for (int i = 0; i < 10;i++){

//将10个连接放到连接池中

Connection conn = ConnectionUtil.getConnection();

dataSoucePool.add(conn);

}

@Override

public Connection getConnection() throws SQLException {

if (dataSoucePool.size() == 0){

//如果连接池已经没有空闲的连接了，扩容

for (int i = 0; i < 5;i++){

Connection conn = ConnectionUtil.getConnection();

dataSoucePool.add(conn);

}

//每次都移出连接池第一个连接对象

Connection conn = dataSoucePool.remove(0);

Connection connection = new ConnectionWrap(conn,dataSoucePool);

return connection;

}

public Connection backConnectuon(Connection conn){

dataSourcePool.add(conn);

}

@Override

public Connection getConnection(String username, String password) throws SQLException {

return null;

}

@Override

public <T> T unwrap(Class<T> iface) throws SQLException {

return null;

}

@Override

public boolean isWrapperFor(Class<?> iface) throws SQLException {

return false;

}

@Override

public PrintWriter getLogWriter() throws SQLException {

return null;

}

@Override

public void setLogWriter(PrintWriter out) throws SQLException {

}

@Override

public void setLoginTimeout(int seconds) throws SQLException {

}

@Override

public int getLoginTimeout() throws SQLException {

return 0;

}

@Override

public Logger getParentLogger() throws SQLFeatureNotSupportedException {

return null;

}

解决自定义连接池出现的问题

自定义连接池出现了什么问题呢？

自定义连接池多增加了一个backConnection方法来归还连接，违背了面向接口编程的规范。我们可以使用装饰者模式来包装Connection类，以符合面向接口的规范。

import java.sql.*;

import java.util.List;

import java.util.Map;

import java.util.Properties;

import java.util.concurrent.Executor;

/**

* 包装Connection类

public class ConnectionWrap implements java.sql.Connection {

Connection connection = null;

List<Connection> list = null;

public ConnectionWrap(Connection connection, List<Connection> list) {

this.connection = connection;

this.list = list;

}

@Override

public Statement createStatement() throws SQLException {

return null;

}

@Override

public PreparedStatement prepareStatement(String sql) throws SQLException {

return connection.prepareStatement(sql);

}

@Override

public CallableStatement prepareCall(String sql) throws SQLException {

return null;

}

@Override

public String nativeSQL(String sql) throws SQLException {

return null;

}

@Override

public void setAutoCommit(boolean autoCommit) throws SQLException {

}

@Override

public boolean getAutoCommit() throws SQLException {

return false;

}

@Override

public void commit() throws SQLException {

}

@Override

public void rollback() throws SQLException {

}

@Override

public void close() throws SQLException {

System.out.println(list.size());

list.add(connection);

System.out.println(list.size());

}

@Override

public boolean isClosed() throws SQLException {

return false;

}

@Override

public DatabaseMetaData getMetaData() throws SQLException {

return null;

}

@Override

public void setReadOnly(boolean readOnly) throws SQLException {

}

@Override

public boolean isReadOnly() throws SQLException {

return false;

}

@Override

public void setCatalog(String catalog) throws SQLException {

}

@Override

public String getCatalog() throws SQLException {

return null;

}

@Override

public void setTransactionIsolation(int level) throws SQLException {

}

@Override

public int getTransactionIsolation() throws SQLException {

return 0;

}

@Override

public SQLWarning getWarnings() throws SQLException {

return null;

}

@Override

public void clearWarnings() throws SQLException {

}

@Override

public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {

return null;

}

@Override

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

return null;

}

@Override

public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {

return null;

}

@Override

public Map<String, Class<?>> getTypeMap() throws SQLException {

return null;

}

@Override

public void setTypeMap(Map<String, Class<?>> map) throws SQLException {

}

@Override

public void setHoldability(int holdability) throws SQLException {

}

@Override

public int getHoldability() throws SQLException {

return 0;

}

@Override

public Savepoint setSavepoint() throws SQLException {

return null;

}

@Override

public Savepoint setSavepoint(String name) throws SQLException {

return null;

}

@Override

public void rollback(Savepoint savepoint) throws SQLException {

}

@Override

public void releaseSavepoint(Savepoint savepoint) throws SQLException {

}

@Override

public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {

return null;

}

@Override

public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {

return null;

}

@Override

public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {

return null;

}

@Override

public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {

return null;

}

@Override

public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {

return null;

}

@Override

public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {

return null;

}

@Override

public Clob createClob() throws SQLException {

return null;

}

@Override

public Blob createBlob() throws SQLException {

return null;

}

@Override

public NClob createNClob() throws SQLException {

return null;

}

@Override

public SQLXML createSQLXML() throws SQLException {

return null;

}

@Override

public boolean isValid(int timeout) throws SQLException {

return false;

}

@Override

public void setClientInfo(String name, String value) throws SQLClientInfoException {

}

@Override

public void setClientInfo(Properties properties) throws SQLClientInfoException {

}

@Override

public String getClientInfo(String name) throws SQLException {

return null;

}

@Override

public Properties getClientInfo() throws SQLException {

return null;

}

@Override

public Array createArrayOf(String typeName, Object[] elements) throws SQLException {

return null;

}

@Override

public Struct createStruct(String typeName, Object[] attributes) throws SQLException {

return null;

}

@Override

public void setSchema(String schema) throws SQLException {

}

@Override

public String getSchema() throws SQLException {

return null;

}

@Override

public void abort(Executor executor) throws SQLException {

}

@Override

public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {

}

@Override

public int getNetworkTimeout() throws SQLException {

return 0;

}

@Override

public <T> T unwrap(Class<T> iface) throws SQLException {

return null;

}

@Override

public boolean isWrapperFor(Class<?> iface) throws SQLException {

return false;

}

7. 常用开源数据库连接池有哪些？

DBCP

# 不使用配置文件方式

public void demo(){

BasicDataSource basicDataSource = new BasicDataSource();

basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");

basicDataSource.setUrl("jdbc:mysql://localhost:3306/bank");

basicDataSource.setUsername("root");

basicDataSource.setPassword("123456");

Connection connection = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try{

connection = basicDataSource.getConnection();

String sql = "select * from account";

pstmt = connection.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next()){

System.out.println(rs.getString("name") + rs.getInt("money"));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

ConnectionUtil.release(rs, pstmt, connection);

}

# 使用配置文件方式

public void demo() {

BasicDataSourceFactory factory = new BasicDataSourceFactory();

Properties properties = new Properties();

Connection connection = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

InputStream inputStream = new FileInputStream("src\\dbcpconfig.properties");

properties.load(inputStream);

DataSource dataSource = factory.createDataSource(properties);

connection = dataSource.getConnection();

String sql = "select * from account";

pstmt = connection.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next()){

System.out.println(rs.getString("name") + rs.getInt("money"));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

ConnectionUtil.release(rs, pstmt, connection);

}

C3P0

# 不使用配置文件方式

public void demo(){

ComboPooledDataSource dataSource = new ComboPooledDataSource();

try {

dataSource.setDriverClass("com.mysql.jdbc.Driver");

} catch (PropertyVetoException e) {

e.printStackTrace();

}

dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/bank");

dataSource.setUser("root");

dataSource.setPassword("123456");

Connection connection = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try{

connection = dataSource.getConnection();

String sql = "select * from account";

pstmt = connection.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next()){

System.out.println(rs.getString("name") + rs.getInt("money"));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

ConnectionUtil.release(rs, pstmt, connection);

}

# 使用配置文件方式

public void demo() {

ComboPooledDataSource dataSource = new ComboPooledDataSource();

Connection connection = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

connection = dataSource.getConnection();

String sql = "select * from account";

pstmt = connection.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next()){

System.out.println(rs.getString("name") + rs.getInt("money"));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

ConnectionUtil.release(rs, pstmt, connection);

}

8. DbUtils

增删改

QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());

//增加

queryRunner.update("insert into account values (null , ? , ? )", "aa" ,1000);

//删除

queryRunner.update("delete from account where id = ?", 5);

//更新

queryRunner.update("update account set money = ? where id = ?", 10000000 , 6);

查询

直接new接口的匿名实现类

public void demo(){

QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());

Account account = null;

try {

account = queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){

@Override

public Account handle(ResultSet rs) throws SQLException {

Account account = new Account();

while(rs.next()){

String name = rs.getString("name");

int money = rs.getInt("money");

account.setName(name);

account.setMoney(money);

}

return account;

}

}, 3);

} catch (SQLException e) {

e.printStackTrace();

}

System.out.println(account.toString());

}

直接使用框架已经写好的实现类

# 查询单个对象

QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());

Account account = queryRunner.query("select * from account where id = ?",

new BeanHandler<Account>(Account.class), 8);

# 查询多个对象

QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());

List<Account> list = queryRunner.query("select * from account ",

new BeanListHandler<Account>(Account.class));

ResultSetHandler 常用的实现类

BeanHandler：查询到的单个数据封装成一个对象

BeanListHandler：查询到的多个数据封装成一个List<对象>

ArrayHandler：查询到的单个数据封装成一个数组

ArrayListHandler,：查询到的多个数据封装成一个集合，集合里面的元素是数组

MapListHandler：查询到的多个数据封装成一个集合，集合里面的元素是map