MySQL事务&连接池

  • 2019-05-05
  • 277
  • 0

事务

Transaction  其实指的一组操作,里面包含许多个单一的逻辑。只要有一个逻辑没有执行成功,那么都算失败。 所有的数据都回归到最初的状态(回滚)。开启事务:start transaction;提交或者回滚事务:commit; 提交事务, 数据将会写到磁盘上的数据库rollback ;  数据回滚,回到最初的状态,可以设置 关闭自动提交事务功能。

@Test
	public void testTransaction(){
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConn();
			
			//连接,事务默认就是自动提交的。 关闭自动提交。
			conn.setAutoCommit(false);
			
			String sql = "update account set money = money - ? where id = ?";
			ps = conn.prepareStatement(sql);
			
			//扣钱, 扣ID为1 的100块钱
			ps.setInt(1, 100);
			ps.setInt(2, 1);
			ps.executeUpdate();
			
			
			int a = 10 /0 ;
			
			//加钱, 给ID为2 加100块钱
			ps.setInt(1, -100);
			ps.setInt(2, 2);
			ps.executeUpdate();
			
			//成功: 提交事务。
			conn.commit();
			
		} catch (SQLException e) {
			try {
				//事变: 回滚事务
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			
		}finally {
			JDBCUtil.release(conn, ps, rs);
		}
	}

事务的特性ACID

  •  原子性(Atomicity): 指事务包含的所有操作要么全部成功,要么全部失败回滚,事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。。 
  • 一致性(Consistency): 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。
  • 隔离性(Isolation): 隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。
  • 持久性(Durability): 持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。


事务的安全隐患 

1.读安全之脏读

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当一个事务正在多次修改某个数据,而在这个事务中这多次的修改都还未提交,这时一个并发的事务来访问该数据,就会造成两个事务得到的数据不一致。例如:用户A向用户B转账100元,对应SQL命令如下

    update account set money=money+100 where name=’B’;  (此时A通知B)

    update account set money=money - 100 where name=’A’;

当只执行第一条SQL时,A通知B查看账户,B发现确实钱已到账(此时即发生了脏读),而之后无论第二条SQL是否执行,只要该事务不提交,则所有操作都将回滚,那么当B以后再次查看账户时就会发现钱其实并没有转。

2.读安全之不可重复读

不可重复读是指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了。例如事务T1在读取某一数据,而事务T2立马修改了这个数据并且提交事务给数据库,事务T1再次读取该数据就得到了不同的结果,发送了不可重复读。不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。

3.读安全之幻读

幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)。

MySQlL四种隔离级别

  1. Serializable (串行化):可避免脏读、不可重复读、幻读的发生。
  2. Repeatable read (可重复读):可避免脏读、不可重复读的发生。
  3. Read committed (读已提交):可避免脏读的发生。
  4. Read uncommitted (读未提交):最低级别,任何情况都无法保证。

以上四种隔离级别最高的是Serializable级别,最低的是Read uncommitted级别,当然级别越高,执行效率就越低。像Serializable这样的级别,就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待,所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

在MySQL数据库中,支持上面四种隔离级别,默认的为Repeatable read (可重复读);而在Oracle数据库中,只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别,其中默认的为Read committed级别。

在MySQL数据库中查看当前事务的隔离级别:

    select @@tx_isolation;
  在MySQL数据库中设置事务的隔离 级别:

    set  [glogal | session]  transaction isolation level 隔离级别名称;

    set tx_isolation=’隔离级别名称;’

设置数据库的隔离级别一定要是在开启事务之前!

如果是使用JDBC对数据库的事务设置隔离级别的话,也应该是在调用Connection对象的setAutoCommit(false)方法之前。调用Connection对象的setTransactionIsolation(level)即可设置当前链接的隔离级别。隔离级别的设置只对当前链接有效。对于使用MySQL命令窗口而言,一个窗口就相当于一个链接,当前窗口设置的隔离级别只对当前窗口中的事务有效;对于JDBC操作数据库来说,一个Connection对象相当于一个链接,而对于Connection对象设置的隔离级别只对该Connection对象有效,与其他链接Connection对象无关。

可串行化

如果有一个连接的隔离级别设置为了串行化 ,那么谁先打开了事务, 谁就有了先执行的权利, 谁后打开事务,谁就只能得着,等前面的那个事务,提交或者回滚后,才能执行。  但是这种隔离级别一般比较少用。 容易造成性能上的问题。 效率比较低。

写安全之丢失更新问题

一条查询语句两个事务T1,T2,两个事务查询出来的结果都是name=>lisi,money=>100;这时T1修改name=>wangwu, T2修改money=>800;这就发生了丢失更新问题,因为第一种情况,T2事务如果提交,那么会造成修改的姓名没了,第二种情况,如果T2回滚,那么会造成T1事务更新没了,因为T2事务记得最初拿出来的数据是lisi,1000

1.乐观锁解决

乐观锁是开发者自己设计处理逻辑,A事务先提交,数据库version由0变成1,B事务在提交的时候,比对数据库version和自己的version,如果不一样,不允许提交,要先更新。

2.悲观锁解决

select * from account for update;

悲观锁:知道你会发生问题,查询的时候加上for update,这时数据库锁机制中的排他锁,一个事务提交完毕,才会让下一个操作这个资源的事务开始执行。

连接池

自定义数据库连接池 

MyDataSource .java

package com.husaky.util;

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;

import javax.sql.DataSource;


public class MyDataSource implements DataSource {
	
	List <Connection> list = new ArrayList<Connection>();
	
	public  MyDataSource() {
		for (int i = 0; i < 10; i++) {
			Connection conn = JDBCUtil.getConn();
			list.add(conn);
		}
	}
	
	
//	该连接池对外公布的获取连接的方法
	@Override
	public Connection getConnection() throws SQLException {
		//来拿连接的时候,先看看,池子里面还有没有。
		if(list.size() == 0 ){
			for (int i = 0; i < 5; i++) {
				Connection conn = JDBCUtil.getConn();
				list.add(conn);
			}
		}
		
		//移除第一个。 移除的是集合中的第一个
		Connection conn = list.remove(0);
		
		//在把这个对象抛出去的时候, 对这个对象进行包装。
		Connection connection = new ConnectionWrap(conn, list);
		
		return connection;
	}
	
	/**
	 * 用完之后,记得归还。
	 * @param conn
	 */
	public void addBack(Connection conn){
		list.add(conn);
	}
	
	//----------------------------

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setLoginTimeout(int seconds) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	

	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

ConnectionWrap.java

package com.husaky.util;

import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

// 装饰者模式包装一下
public class ConnectionWrap  implements Connection{
	
	Connection connection = null;
	List <Connection> list ;
	public ConnectionWrap(Connection connection , List <Connection> list) {
		super();
		this.connection = connection;
		this.list = list;
	}

	@Override
	public void close() throws SQLException {
		//connection.close();
		System.out.println("有人来归还连接对象了。 归还之前,池子里面是:"+list.size());
		list.add(connection);
		System.out.println("有人来归还连接对象了。 归还之后...,池子里面是:"+list.size());
	}
	
	
	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return connection.prepareStatement(sql);
	}


	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

// 后面很多重写的方法....
}

JDBCUtil.java

package com.husaky.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    static String driverClass = null;
    static String url = null;
    static String name = null;
    static String password =null;


    static {
        try {
            Properties properties = new Properties();
            //使用类加载器,去读取src底下的资源文件
            InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");

            properties.load(is);
            driverClass  = properties.getProperty("driverClass");
            url  = properties.getProperty("url");
            name  = properties.getProperty("name");
            password  = properties.getProperty("password");

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static Connection getConn() {
        Connection conn = null;
        try {
            // 1.注册驱动 底层静态代码块已经通过这种方式注册过了,不要再重复注册
//            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            // 警告,这里不要搞成字符串了 "driverClass"
            Class.forName(driverClass); // 这句可以不写,默认已经加载了
            // 2.建立连接
//            DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=root");
            conn = DriverManager.getConnection(url, name, password);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void release(ResultSet rs, PreparedStatement ps, Connection conn) {
        closeConn(conn);
        closeRs(rs);
        closeSt(ps);
    }

    private static void closeRs(ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            rs = null;
        }
    }

    private static void closeSt(PreparedStatement ps) {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ps = null;
        }
    }

    private static void closeConn(Connection conn) {
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn = null;
        }
    }
}

由于多了一个addBack 方法,所以使用这个连接池的地方,需要额外记住这个方法,并且还不能面向接口编程。我们打算修改接口中的那个close方法。  原来的Connection对象的close方法,是真的关闭连接。 打算修改这个close方法,以后在调用close, 并不是真的关闭,而是归还连接对象。


开源连接池

1.DBCP(Apache)

 导入jar文件

public void testDBCP01(){
		
			
			Connection conn = null;
			PreparedStatement ps = null;
			try {
				
				//1. 构建数据源对象
				BasicDataSource dataSource = new BasicDataSource();
				//连的是什么类型的数据库, 访问的是哪个数据库 , 用户名, 密码。。
				//jdbc:mysql://localhost/bank 主协议:子协议 ://本地/数据库
				dataSource.setDriverClassName("com.mysql.jdbc.Driver");
				dataSource.setUrl("jdbc:mysql://localhost/bank");
				dataSource.setUsername("root");
				dataSource.setPassword("root");
				
				
				//2. 得到连接对象
				conn = dataSource.getConnection();
				String sql = "insert into account values(null , ? , ?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, "admin");
				ps.setInt(2, 1000);
				
				ps.executeUpdate();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				JDBCUtil.release(conn, ps);
			}
			
		}

2. 使用配置文件方式:


		Connection conn = null;
		PreparedStatement ps = null;
		try {
			BasicDataSourceFactory factory = new BasicDataSourceFactory();
			Properties properties = new Properties();
			InputStream is = new FileInputStream("src//dbcpconfig.properties");
			properties.load(is);
			DataSource dataSource = factory.createDataSource(properties);
			
			//2. 得到连接对象
			conn = dataSource.getConnection();
			String sql = "insert into account values(null , ? , ?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "liangchaowei");
			ps.setInt(2, 100);
			
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(conn, ps);
		}

2. C3P0(开源,流行)

 拷贝jar文件 到 lib目录

不使用配置文件方式


		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1. 创建datasource
			ComboPooledDataSource dataSource = new ComboPooledDataSource();
			//2. 设置连接数据的信息
			dataSource.setDriverClass("com.mysql.jdbc.Driver");
			
			//忘记了---> 去以前的代码 ---> jdbc的文档
			dataSource.setJdbcUrl("jdbc:mysql://localhost/bank");
			dataSource.setUser("root");
			dataSource.setPassword("root");
			
			//2. 得到连接对象
			conn = dataSource.getConnection();
			String sql = "insert into account values(null , ? , ?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "admi234n");
			ps.setInt(2, 103200);
			
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(conn, ps);
		}


###使用配置文件方式


			//默认会找 xml 中的 default-config 分支。 
			ComboPooledDataSource dataSource = new ComboPooledDataSource();
			//2. 设置连接数据的信息
			dataSource.setDriverClass("com.mysql.jdbc.Driver");
			

			//忘记了---> 去以前的代码 ---> jdbc的文档
			dataSource.setJdbcUrl("jdbc:mysql://localhost/bank");
			dataSource.setUser("root");
			dataSource.setPassword("root");
			
			//2. 得到连接对象
			conn = dataSource.getConnection();
			String sql = "insert into account values(null , ? , ?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "admi234n");
			ps.setInt(2, 103200);


DBUtils增删查改

是Apache提供的一个对JDBC简单封装的开源工具库类库,可以简化JDBC应用程序的开发,同时也不会影响程序的性能。


增删改

				//dbutils 只是帮我们简化了CRUD 的代码, 但是连接的创建以及获取工作。 不在他的考虑范围
		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);

查询

1. 直接new接口的匿名实现类


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

		Account  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;
			}
			 
		 }, 6);
		
		System.out.println(account.toString());

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


	* 查询单个对象

		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 常用的实现类,前两个是使用频率最高的

  1. BeanHandler,  查询到的单个数据封装成一个对象
  2. BeanListHandler, 查询到的多个数据封装 成一个List<对象>
  3. ArrayHandler,  查询到的单个数据封装成一个数组
  4. ArrayListHandler,  查询到的多个数据封装成一个集合 ,集合里面的元素是数组。 
  5. MapHandler,  查询到的单个数据封装成一个map
  6. MapListHandler,查询到的多个数据封装成一个集合 ,集合里面的元素是map。 

评论

还没有任何评论,你来说两句吧

粤ICP备17155863号

- 友情链接 - Theme by Qzhai