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
来源: http://www.bubuko.com/infodetail-2881393.html