数据库MySQL基础---事务相关特性--连接池DBCP--C3P0--JavaBean--DBUtils工具

事务相关特性–连接池DBCP–C3P0–JavaBean–DBUtils工具

事务的概念

事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。
完成一个需求，需要执行多条SQL语句时，我们就可以用事务将这几条SQL语句绑定成一个逻辑单元；要么全部执行成功，如果中任意一条出现问题，则全部失败，已执行部分要回滚（回到未执行状态）。

事务的特性(ACID)：
1）原子性Atomicity ：
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做。

2）一致性Consistency：
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。比如，当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统在运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。

3）隔离性Isolation：
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4）持久性Durability：
指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

MySQL中事务的操作：
1、start transaction;
2、写SQL语句（多条）
3、成功：提交事务commit;
4、失败：回滚事务rollback；

JDBC事务的操作：
1、设置手动提交conn.setAutoCommit(false);
2、如果成功，提交数据conn.commit();
3、如果失败，进行回滚conn.rollback();

完整代码

package com.offcn.demo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;
public class TransactionTest {
	@Test
	public void giveMoney() {
		Connection conn = JDBCUtil.getConn();
		PreparedStatement pstmt = null;
		String sql1 = "update account set umoney=umoney+200 where uid=2";
		String sql2 = "update account set umoney=umoney-200 where uid=1";
		try {
			//值为false，手动提交；值为ture，代表自动提交。
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql1);
			int rows1 = pstmt.executeUpdate();
			System.out.println("收钱的："+rows1);
			pstmt = conn.prepareStatement(sql2);
			int rows2 = pstmt.executeUpdate();
			System.out.println("给钱的："+rows2);
			//提交数据
			conn.commit();
		} catch (SQLException e) {
			System.out.println("出错啦，开始回滚");
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			System.out.println("回滚成功");
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResources(null, pstmt, conn);
		}
	}
}

事务的作用

1、维护数据库数据的完整性
2、减少数据库中的冗余数据（脏数据）
3、程序员控制数据库操作的重要途径

事务的隔离级别

1、Read uncommitted：未提交读，就是一个事务可以读取另一个未提交事务的数据。
此时可能会出现脏读，比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务–>取走100元，此时切换回事务A，事务A读取的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交，数据库里面的账务余额肯定还是原始余额，这就是脏读。

2、Read committed：已提交读，就是一个事务要等另一个事务提交后才能读取数据。
此时可能会出现不可重复读，就是指在一个事务里面读取了两次某个数据，读出来的数据不一致。还是以银行取钱为例，事务A开启事务–>查出银行卡余额为1000元，此时切换到事务B事务B开启事务–>事务B取走100元–>提交，数据库里面余额变为900元，此时切换回事务A，事务A再查一次查出账户余额为900元，这样对事务A而言，在同一个事务内两次读取账户余额数据不一致。此情形发生让事务并行时的修改操作。

3、Repeatable read：可重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。
此时可能会出现幻读，就是指在一个事务里面的操作中发现了未被操作的数据。比如学生信息，事务A开启事务–>修改所有学生当天签到状况为false，此时切换到事务B，事务B开启事务–>事务B插入了一条学生数据，此时切换回事务A，事务A提交的时候发现了一条自己没有修改过的数据，这就是幻读，就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。

4、Serializable 序列化
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

连接池

早起进行的数据库操作存在很多问题，首先，每一次web请求都要建立一次数据库连接，频繁的进行数据库连接操作势必占用很多的系统资源，网站的响应速度必定下降，严重的甚至会造成服务器的崩溃。其次，对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将不得不重启数据库。还有，这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。为了解决资源的频繁分配﹑释放所造成的问题，可以采用数据库连接池技术。
连接池优势：
1、程序启动时，就创建好一定数量的连接放到池子里，不需要用户发出请求时才创建，减轻服务器压力。
2、用户需要操作数据库的时候，直接从连接池中获取一个空闲的连接来使用，用完以后再还回去供后面的用户使用，实现了连接的循环利用。
3、用户获取连接时，连接池中的连接都被其他用户拿去使用了，如果当前池子容量未达上限，那么就创建一批新的连接放进去；如果当前池子容量已达上限，用户就必须等待其他用户归还再使用。
4、如果池子中有长期处于空闲状态的连接，关闭一批空闲连接，优化系统性能。

手动实现连接池

连接池代码

package com.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ConnectionPoolUtil {
	    //初始化连接池
		private static List<Connection> pool = new ArrayList<Connection>();
		//初始化连接数量
		private static int poolSize = 5;
		static{
			for(int i=0;i<poolSize;i++) {
	            try {
		            //加载mysql驱动
					Class.forName("com.mysql.cj.jdbc.Driver");
					//创建连接
					Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db01?serverTimezone=GMT%2B8","root","000");
		            //将连接加入连接池中
					pool.add(conn);
					System.out.println(conn+"放进池子里面了，当前池子中有"+pool.size()+"个连接");
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		//获取连接的方法
		public static Connection getConnByPool() {
			Connection conn = pool.remove(0);
			System.out.println(conn+"被拿去使用了，当前池子中有"+pool.size()+"个连接");
			return conn;
		}	
		//归还连接的方法
		public static void returnConn(ResultSet rs,PreparedStatement pstmt,Connection conn) {
			try {
				if(rs!=null) {
					rs.close();
				}
				if(pstmt!=null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			pool.add(conn);
			System.out.println(conn+"被还回来了，当前池子中有"+pool.size()+"个连接");
		}
}

测试代码

package com.ujiuye;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.jupiter.api.Test;

import com.util.ConnectionPoolUtil;

public class TestConnPool {
	@Test
	public void test() {
		Connection conn = ConnectionPoolUtil.getConnByPool();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from student";
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString("sname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionPoolUtil.returnConn(rs,pstmt,conn);
		}
	}
}


结果：
com.mysql.cj.jdbc.ConnectionImpl@161479c6放进池子里面了，当前池子中有1个连接
com.mysql.cj.jdbc.ConnectionImpl@7f010382放进池子里面了，当前池子中有2个连接
com.mysql.cj.jdbc.ConnectionImpl@2b6faea6放进池子里面了，当前池子中有3个连接
com.mysql.cj.jdbc.ConnectionImpl@670002放进池子里面了，当前池子中有4个连接
com.mysql.cj.jdbc.ConnectionImpl@49c386c8放进池子里面了，当前池子中有5个连接
com.mysql.cj.jdbc.ConnectionImpl@161479c6被拿去使用了，当前池子中有4个连接
大毛
吉祥
三毛
四毛
大强
光头
阿萨德
com.mysql.cj.jdbc.ConnectionImpl@161479c6被还回来了，当前池子中有5个连接

DBCP连接池

1、导入jar包

2、创建配置文件dbcp.properties（等号前面的不能改）

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db01?serverTimezone=GMT%2B8&characterEncoding=UTF-8
username=root
password=000
initialSize=10
maxActive=30

3、创建DBCPUtil.java

package com.offcn.demo;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPUtil {
	//初始化池子（数据源）
	private static DataSource ds= null;
	static {
		/* InputStream in = new FileInputStream("src/dbcp.properties"); */
		//针对src下的配置文件的读取方式
		InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");
		Properties prop = new Properties();
		try {
			prop.load(in);
			//初始化ds
			ds = BasicDataSourceFactory.createDataSource(prop);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//获取连接的方法
	public static Connection getConnByDbcp() {
		Connection conn = null;
		try {
			conn = ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	//释放资源
	public static void returnResources(ResultSet rs,PreparedStatement pstmt,Connection conn) {
		try {
			if(rs!=null) {
				rs.close();
			}
			if(pstmt!=null) {
				pstmt.close();
			}
			if(conn!=null) {
				//归还连接而不是关闭连接
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

4、测试代码

package com.ujiuye;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;

import com.util.DBCPUtil;
import com.util.JDBCUtil;

public class DBCPTest {
	@Test
	public void test() {
		Connection conn = DBCPUtil.getConnByDbcp();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from student";
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString("sname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBCPUtil.returnResources(rs,pstmt,conn);
		}
	}
}


结果：
大毛
吉祥
三毛
四毛
大强
光头
阿萨德

c3p0连接池

1、导入jar包

2、创建配置文件c3p0.properties，一定要放在src下，文件名不能改，key不能改

c3p0.driverClass=com.mysql.cj.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/school?serverTimezone=GMT%2B8
c3p0.user=root 
c3p0.password=000
c3p0.acquireIncrement=3 
c3p0.initialPoolSize=10 
c3p0.maxIdleTime=60 
c3p0.maxPoolSize=150 
c3p0.minPoolSize=5 

3、创建C3p0Util.java

package com.offcn.demos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3p0Util {
	private static ComboPooledDataSource ds = null;
//调用ComboPooledDataSource()这个构造方法时，内部已经读取了配置文件，并且读取了相关数据，再创建了连接放到了数据源中
	static {
		ds = new ComboPooledDataSource();
	}
	
	public static Connection getConnByC3p0() {
		Connection conn = null;
		try {
			conn = ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void returnResources(ResultSet rs,PreparedStatement pstmt,Connection conn) {
		try {
			if(rs != null) {
				rs.close();
			}
			if(pstmt != null) {
				pstmt.close();
			}
			if(conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}	
}

4、测试代码

package com.offcn.demos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.jupiter.api.Test;

public class C3p0Test {
	@Test
	public void test() {
		Connection conn = C3p0Util.getConnByC3p0();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select * from student" );
			rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString("sname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			C3p0Util.returnResources(rs, pstmt, conn);
		}
	}
}

结果：
大力娃
千里眼
喷火娃
吐水娃
隐身娃
宝葫芦娃
蛇精
玉兔精
琵琶精

JavaBean

JavaBeans是Java中一种特殊的类，可以将多个对象封装到一个对象（bean）中。特点是可序列化，提供无参构造器，提供getter方法和setter方法访问对象的属性。名称中的“Bean”是用于Java的可重用软件组件的惯用叫法。
—以上源自维基百科

亦称实体类，在JDBC操作中，一个实体类会对应数据库中的一张表，实体类中的属性会与表中的列一一对应。在与数据库连接之中，JavaBean其的作用是将获取的数据库的记录封装到JavaBean中。
JavaBean的设计原则：
1、类必须是公有的
2、类中的属性必须是私有的
3、必须为每个属性创建对应的get/set方法
4、类中必须要有无参构造方法
5、需要实现接口：java.io.Serializable ，可以省略不写

代码（对应我的数据库中的表student）

package com.offcn.javabean;

public class Student {
	private int sid;
	private String sname;
	private int sage;
	private String ssex;
	private String semail;
	public Student() {

	}
	public Student(int sid, String sname, int sage, String ssex, String semail)     {
		this.sid = sid;
		this.sname = sname;
		this.sage = sage;
		this.ssex = ssex;
		this.semail = semail;
	}
	public int getSid() {
		return sid;
	}
	public void setSid(int sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public int getSage() {
		return sage;
	}
	public void setSage(int sage) {
		this.sage = sage;
	}
	public String getSsex() {
		return ssex;
	}
	public void setSsex(String ssex) {
		this.ssex = ssex;
	}
	public String getSemail() {
		return semail;
	}
	public void setSemail(String semail) {
		this.semail = semail;
	}
}

DBUtils工具简介

Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库。
如前所述，Java与数据库的连接包括：导包、注册驱动、获取与数据库的连接对象、获取SQL语句的执行者对象、获取结果集对象、关闭连接等。其中连接池包含了注册驱动和获取与数据库连接两个步骤，而dbutils简化了其他步骤。

DBUtils的使用

Commons DbUtils的核心是两个类一个接口：
1、DBUtils类：是一个工具类，定义了关闭资源和装载JDBC驱动程序等的常规工作的方法，都是静态的方法。
2、QueryRunner类：提供对sql语句操作的API，为我们提供两个重要方法，调用方法之前需要先创建一个QueryRunner的对象。
QueryRunner qRunner=new QueryRunner(new ComboPooledDataSource());
创建对象时需要传入一个连接池的数据源，这里结合c3p0连接池来完成。
qRunner.update(String sql, Object… params)：支持DML操作
qRunner.query(String sql, ResultSetHandler rsh, Object… params)：支持DQL操作
两个方法都有多个重载，可以根据需求选择不同的重载方法。
3、ResultSetHandler接口：用于处理ResultSet结果集，将结果集的的数据转换成不同形式。
该接口的实现类有很多：

ScalarHandler	它是用于单列数据查询。例如：select count(*) from users 操作。
ColumnListHandler	将结果集中指定的列的字段值，封装到一个List集合中
BeanHandler	将结果集中第一条记录封装到一个指定的javaBean中。
BeanListHandler	将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中。
ArrayHandler	将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值。
ArrayListHandler	将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。
MapHandler	将结果集中第一条记录封装到Map集合中,Key代表列名, Value代表该列数据。
MapListHandler	将结果集中每一条记录封装到Map集合中,Key代表列名, Value代表该列数据,Map集合再存储到List集合

DBUtils相关操作

1、导入jar包（dbutils）
2、DBUtils+c3p0实现数据的增删改查

案例代码：

	//新增
	public int insert(String uname,double umoney) {
		int rows = 0;
		ComboPooledDataSource ds = new ComboPooledDataSource();
		//创建QueryRunner对象
		QueryRunner qr = new QueryRunner(ds);
		//创建SQL语句
		String sql  ="insert into account(uname,umoney) values(?,?)";
		try {
			rows = qr.update(sql,uname,umoney);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rows;
	}
	
	//删除
	public int delete(int uid) {
		int rows = 0;
		ComboPooledDataSource ds = new ComboPooledDataSource();
		QueryRunner qr = new QueryRunner(ds);
		String sql = "delete from account where uid=?";
		try {
			rows = qr.update(sql,uid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rows;
	}
	
	//修改
	public int update(int uid,String uname,double umoney) {
		int rows = 0;
		ComboPooledDataSource ds = new ComboPooledDataSource();
		QueryRunner qr = new QueryRunner(ds);
		String sql = "update account set uname=?,umoney=? where uid=?";
		try {
			rows = qr.update(sql,uname,umoney,uid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rows;
	}
	
	//查询所有用户信息
	public List<Account> getAllAccount(){
		List<Account> list = null;
		ComboPooledDataSource ds = new ComboPooledDataSource();
		QueryRunner qr = new QueryRunner(ds);
		String sql = "select * from account";
		try {
			//将查询的每一行数据封装成一个Account对象，再将这些对象放到集合当中
			//查询的是什么表，封装的对象就是这个表对应的对象
			list = qr.query(sql, new BeanListHandler<>(Account.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//根据uid查询用户信息，因为uid是主键，所以查询结果要么查不到，查到了也只会有一条数据，只需要返回一个对象就行了
	public Account getAccountByUid(int uid) {
		Account account = null;
		ComboPooledDataSource ds = new ComboPooledDataSource();
		QueryRunner qr = new QueryRunner(ds);
		String sql = "select * from account where uid=?";
		try {
			//将查询的第一行数据封装成一个Account对象
			account = qr.query(sql, new BeanHandler<>(Account.class),uid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return account;
	}
	
	//查询account表有多少条数据
	public int getCountRows() {
		int countRows = 0;
		ComboPooledDataSource ds = new ComboPooledDataSource();
		QueryRunner qr = new QueryRunner(ds);
		String sql = "select count(*) from account";
		try {
			//Object类型需要先转换为long，再转换成int
			countRows = (int)(long)qr.query(sql, new ScalarHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return countRows;
	}