JDBC技术2（事务设置隔离级别、数据库连接池、DBUtils实现CRUD操作）

数据库事务

JDBC事务处理

@Test
	public void testTransactionSelect() throws Exception{
		
		Connection conn = JDBCUtils.getConnection();
		//获取当前连接的隔离级别
		System.out.println(conn.getTransactionIsolation());
		//设置数据库的隔离级别：
		conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		//取消自动提交数据
		conn.setAutoCommit(false);
		
		String sql = "select user,password,balance from user_table where user = ?";
		User user = getInstance(conn, User.class, sql, "CC");
		
		System.out.println(user);
		
	}
	
	@Test
	public void testTransactionUpdate() throws Exception{
		Connection conn = JDBCUtils.getConnection();
		
		//取消自动提交数据
		conn.setAutoCommit(false);
		String sql = "update user_table set balance = ? where user = ?";
		update(conn, sql, 5000,"CC");
		
		Thread.sleep(15000);
		System.out.println("修改结束");
	}
	
	//通用的查询操作，用于返回数据表中的一条记录（version 2.0：考虑上事务）
	public <T> T getInstance(Connection conn,Class<T> clazz,String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			rs = ps.executeQuery();
			// 获取结果集的元数据 :ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// 通过ResultSetMetaData获取结果集中的列数
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列
				for (int i = 0; i < columnCount; i++) {
					// 获取列值
					Object columValue = rs.getObject(i + 1);

					// 获取每个列的列名
					// String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// 给t对象指定的columnName属性，赋值为columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);

		}

		return null;
	}

DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、
Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO
作用：为了实现功能的模块化，更有利于代码的维护和升级。

数据库连接池

在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：
在主程序（如servlet、beans）中建立数据库连接
进行sql操作
断开数据库连接

这种模式开发，存在的问题:

普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection
加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求
一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。 数据库的连接资源并没有得到很好的重复利用。 若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严
重的甚至会造成服务器的崩溃。

对于每一次数据库连接，使用完后都得断开。 否则，如果程序出现异常而未能关闭，将会导致数据库系统
中的内存泄漏，最终将导致重启数据库。（回忆：何为Java的内存泄漏？）

这种开发不能控制被创建的连接对象数， 系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内 存泄漏，服务器崩溃。

数据库连接池技术
为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。
数据库连接池的基本思想： 就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要
建立数据库连接 时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
数据库连接池负责分配、管理和释放数据库连接，它 允许应用程序重复使用一个现有的数据库连接，而不是重
新建立一个。
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库
连接数 来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池
的 最大数据库连接数量 限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连
接数量时，这些请求将被加入到等待队列中。

数据库连接池技术的优点

多种开源的数据库连接池
JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器
(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：

DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因
自身存在BUG，Hibernate3已不再提供支持。

C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。 hibernate官方推荐使用

Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点

BoneCP 是一个开源组织提供的数据库连接池，速度快

Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是
速度不确定是否有BoneCP快

DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接
池
DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速
度。
特别注意：
数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数
据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

利用c3p0 数据库连接池来建立连接

首先也还是要导入的c3p0 的jar包

xml配置文件

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>

	<named-config name="helloc3p0">
		<!-- 提供获取连接的四个基本信息 -->>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///test</property>
		<property name="user">root</property>
		<property name="password">7777</property>
		
		<!-- 进行数据库连接池管理的基本信息 -->
		<!-- 当我们数据库连接池中连接数不够时，c3p0一次性向数据库服务器申请的连接数 -->
		<property name="acquireIncrement">5</property>
		<!-- c3p0数据库连接池中初始化的连接数 -->
		<property name="initialPoolSize">10</property>
		<!-- c3p0数据库连接池维护的最少的连接数 -->
		<property name="minPoolSize">10</property>
		<!-- c3p0数据库连接池维护的最多的连接数 -->
		<property name="maxPoolSize">100</property>
		<!-- 数据库连接池最多维护的Statements的个数 -->
		<property name="maxStatements">50</property>
		<!-- 每一个连接中可以最多使用的Statement的个数 -->
		<property name="maxStatementsPerConnection">2</property>


	</named-config>
</c3p0-config>

测试

//使用配置文件
	private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
	//将方法声明为静态的是为了在整个的程序中只创建一个 连接池，如果我们 将创建的 连接池的代码放在方法内，
	//那么我们每次去调用Connection方法的时候都会去创建一个c3p0的连接池.
	@Test
	public void testGetConnection1() throws SQLException {
		Connection conn = cpds.getConnection();
		System.out.println(conn);
	}

利用DBCP数据库连接池来建立连接

DBCP 是 Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Commonpool。如需使用该连接池实现，应在系统中增加如下两个 jar 文件：
Commons-dbcp.jar：连接池的实现
Commons-pool.jar：连接池实现的依赖库

首先我们需要导入两个jar包

配置属性说明

JDBCUtils类：

	/** 
	 * 
	 * @Description 使用DBCP数据库连接池技术获取数据库连接
	 * @author shkstart
	 * @date 下午3:35:25
	 * @return
	 * @throws Exception
	 */
	//创建一个DBCP数据库连接池
	private static DataSource source;
	//利用静态代码块使连接池在程序中只创建一次
	static{
		try {
			Properties pros = new Properties();
			FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
			pros.load(is);
			source = BasicDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection2() throws Exception{
		
		Connection conn = source.getConnection();
		
		return conn;
	}

dbcp.properties配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=7777
initialSize=10

测试类：

@Test
	public void testGetCustomerById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection3();
			
			Customer cust = dao.getCustomerById(conn, 19);
			System.out.println(cust);
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, null);
			
		}
	}

Druid（德鲁伊）数据库连接池

Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了
日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的
连接池之一。

详细配置参数：

JDBCUtils类：

	/**
	 * 使用的Druid数据库连接池技术
	 */
	private static DataSource source1;
	static {
		try {
			Properties pros = new Properties();

			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");

			pros.load(is);
			source1 = DruidDataSourceFactory.createDataSource(pros);
		}catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static Connection getConnection3() throws SQLException {

		Connection conn = source.getConnection();
		return conn;
	}

配置文件：

url=jdbc:mysql:///test
username=root
password=7777
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

测试类：

	@Test
	public void testGetCustomerById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection3();
			
			Customer cust = dao.getCustomerById(conn, 19);
			System.out.println(cust);
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, null);
			
		}
	}

Apache-DBUtils实现CRUD操作

Apache-DBUtils简介
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

主要API的使用
1.DbUtils
DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法
如下：

public static void close(…) throws java.sql.SQLException： DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。

public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。

public static void commitAndClose(Connection conn)throws SQLException： 用来提交连接的事务，然后关闭连接

public static void commitAndCloseQuietly(Connection conn)： 用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。

public static void rollback(Connection conn)throws SQLException：允许conn为null，因为方法内部做了判断

public static void rollbackAndClose(Connection conn)throws SQLException 

rollbackAndCloseQuietly(Connection)

public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。

2.QueryRunner类

测试插入

	// 测试插入
	@Test
	public void testInsert() throws Exception {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "insert into customers(name,email)values(?,?)";
			int insertCount = runner.update(conn, sql, "蔡徐坤", "caxvkun@126.caom");
			System.out.println("添加了" + insertCount + "条记录");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			JDBCUtils.closeResource(conn, null);
		}

	}

测试查询

	// 测试查询
	// 返回一条记录
	@Test
	public void testQuery() throws Exception {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "select id,name,email,birth from customers where id = ?";

			BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
			Customer customer = runner.query(conn, sql, handler, 23);
			System.out.println(customer);// Customer [id=23, name=哪吒, email=nezha@qq.com, birth=1000-01-01]
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			JDBCUtils.closeResource(conn, null);
		}

	}

	// 返回多条记录
	@Test
	public void testQuery2() throws Exception {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "select id,name,email,birth from customers where id < ?";

			BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);

			List<Customer> list = runner.query(conn, sql, listHandler, 23);
			list.forEach(System.out::println);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			JDBCUtils.closeResource(conn, null);
		}

	}

3.ResultSetHandler接口及实现类

MapHandler

/*
	 * MapHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录 将字段及相应字段的值作为map中key和value
	 */
	@Test
	public void testQuery3() throws Exception {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "select id,name,email,birth from customers where id = ?";

			MapHandler handler = new MapHandler();

			Map<String, Object> map = runner.query(conn, sql, handler, 23);
			System.out.println(map);// {name=哪吒, birth=1000-01-01, id=23, email=nezha@qq.com}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			JDBCUtils.closeResource(conn, null);
		}

	}

MapListHandler

	@Test
	public void testQuery4() throws Exception {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "select id,name,email,birth from customers where id < ?";

			MapListHandler handler = new MapListHandler();

			List<Map<String, Object>> list = runner.query(conn, sql, handler, 23);
			list.forEach(System.out::println);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			JDBCUtils.closeResource(conn, null);
		}

	}

ScalarHandler

	/*
	 * ScalarHandler:用于查询特殊值   查询类似于最大的，最小的，平均的，总和，个数相关的数据，
	 */
	@Test
	public void testQuery5() throws Exception {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "select count(*) from customers";

			ScalarHandler handler = new ScalarHandler();

			Long count = (Long) runner.query(conn, sql, handler);
			System.out.println(count);// 24

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			JDBCUtils.closeResource(conn, null);
		}

	}


	@Test
	public void testQuery6() throws Exception {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();

			String sql = "select max(birth) from customers";

			ScalarHandler handler = new ScalarHandler();
			Date maxBirth = (Date) runner.query(conn, sql, handler);
			System.out.println(maxBirth);// 2014-01-17

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			JDBCUtils.closeResource(conn, null);
		}

	}

自定义ResultSetHandler的实现类

	/*
	 * 自定义ResultSetHandler的实现类
	 */

	@Test
	public void testQuery7() throws Exception {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();

			String sql = "select id,name,email,birth from customers where id = ?";
			ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {

				@Override
				public Customer handle(ResultSet rs) throws SQLException {

					if (rs.next()) {
						int id = rs.getInt("id");
						String name = rs.getString("name");
						String email = rs.getString("email");
						Date birth = rs.getDate("birth");
						Customer customer = new Customer(id, name, email, birth);
						return customer;

					}
					return null;
				}

			};

			Customer customer = runner.query(conn, sql, handler, 23);
			System.out.println(customer);// 2014-01-17

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			JDBCUtils.closeResource(conn, null);
		}

	}
}

JDBC总结

总结

	@Test
	public void testUpdateWithTx1() {
		Connection conn = null;
		try {
			// 1.获取连接的操作（
			// ① 手写的连接：JDBCUtils.getConnection();
			// ② 使用数据库连接池：C3P0;DBCP;Druid
			// 2.对数据表进行一系列CRUD操作
			// ① 使用PreparedStatement实现通用的增删改、查询操作（version 1.0 \ version 2.0)
			// version2.0的增删改public void update(Connection conn,String sql,Object ...
			// args){}
			// version2.0的查询 public <T> T getInstance(Connection conn,Class<T>
			// clazz,Stringsql,Object ... args){}
			// ② 使用dbutils提供的jar包中提供的QueryRunner类
			// 提交数据
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				// 回滚数据
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			// 3.关闭连接等操作
			// ① JDBCUtils.closeResource();
			// ② 使用dbutils提供的jar包中提供的DbUtils类提供了关闭的相关操作
		}
	}