"[图片] 第 77 节:Java 中的事务和数据库连接池和 DBUtiles 前言看哭你, 字数:8803, 承蒙关照, 谢谢朋友点赞! [图片] 事务 Transaction事务, 什么是事务, 事务是包含一组操作, 这组操作里面包含许多个单一的逻辑, 只要有一个逻辑没有执行成功就算失败, 导致 ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 4 浏览 • 4 个月前

第77节:Java中的事务和数据库连接池和DBUtiles

标题图

第 77 节:Java 中的事务和数据库连接池和 DBUtiles

前言

看哭你, 字数:8803, 承蒙关照, 谢谢朋友点赞!

事务

Transaction事务, 什么是事务, 事务是包含一组操作, 这组操作里面包含许多个单一的逻辑, 只要有一个逻辑没有执行成功就算失败, 导致回滚就是指所有的数据都会回到最初的状态.

有事务, 是为了保证逻辑一定要成功, 如银行转账.

回顾一下

什么是jsp,jsp的三大指令.

page: 定义当前页面的信息
include: 包含其他页面
taglib: 引入标签库

三大动作标签:

<jsp:forward page="">:跳转
<jsp:param name="" value="">:跳转到具体页面或是某个页面被包含的时候,可以指定参数
<jsp:include page="">:包含

九大内置对象:

// 四个作用域 pageContext 当前页 request 请求一次请求响应后就不行了 session 会话一次会话 application 都可以整个项目,直到关闭服务器

// 5 response out exception config -> ServletConfig page -> this 翻译成java文件的类对象

`EL`

${表达式};

取值

<% User user = new User(); %>
${user.ame}
从四个作用域里面找 
pageContext -> request -> session -> application

<%
 User user = new User();
 pageContext.setAttribute("user",user);
%>
${sessionScope.user.name}强制用session找

EL的 11 个隐式对象:

pageContext // 作用域 pageScope requestScope sessionScope applicationScope

头相关 header headerValues 参数 param paramValues cookie initparam

`JSTL`

导包哦, 引入标签库是 1.1 的:

<c:set>: 存储到作用域
<c:set var="" value="" scope="">
<c: if>: 用来判断
<c:forEach items="" var="">:用于遍历

数据库

数据库命令行:

关闭自动提交.

关闭了提交, 再减 100:

会到数据库查看:

值没改变!

添加改变代码:

package com.dashucoding.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.dashucoding.util.JDBCUtil;
public class TestDemo {
<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">testTransaction</span><span class="hljs-params">()</span> {
	<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">ps</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-keyword">try</span> {
	    conn = JDBCUtil.getConn();
	    <span class="hljs-comment">//查询数据</span>
		<span class="hljs-comment">/*String sql = "select * from account";
	    ps = conn.prepareStatement(sql);*/</span>
		
	    <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money = money - ? where id = ?"</span>;
		ps = conn.prepareStatement(sql);
	    
	    <span class="hljs-comment">// 扣钱</span>
		ps.setInt(<span class="hljs-number">1</span>, <span class="hljs-number">100</span>);
		ps.setInt(<span class="hljs-number">2</span>, <span class="hljs-number">1</span>);
		ps.executeUpdate();
		
		<span class="hljs-comment">/*ps.setInt(1, -100);
		ps.setInt(2, 1);
		ps.executeUpdate();*/</span>
		
		<span class="hljs-comment">// 查询数据 </span>
		<span class="hljs-comment">/*rs = ps.executeQuery();
		while(rs.next()) {
			System.out.println(rs.getString("name")+"=="+rs.getInt("money"));
		}*/</span>
		
	} <span class="hljs-keyword">catch</span> (SQLException e) {
		<span class="hljs-comment">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	}<span class="hljs-keyword">finally</span> {
		JDBCUtil.release(conn, ps, rs);
	}
}

}

package com.dashucoding.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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 {
			//1. 创建一个属性配置对象
			Properties properties = new Properties();
			InputStream is = new FileInputStream("jdbc.properties");
			
			
			// 使用类加载器，去读取 src 底下的资源文件。 后面在 servlet
//			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();
		}
	}
	
	/**
	 * 获取连接对象
	 * @return
	 */
	public static Connection getConn(){
		Connection conn = null;
		try {
			Class.forName(driverClass);
			// 静态代码块 ---> 类加载了，就执行。 java.sql.DriverManager.registerDriver(new Driver());
			//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			//DriverManager.getConnection("jdbc:mysql://localhost/test?user=monty&password=greatsqldb");
			//2. 建立连接 参数一： 协议 + 访问的数据库 ， 参数二： 用户名 ， 参数三： 密码。
			conn = DriverManager.getConnection(url, name, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 释放资源
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void release(Connection conn , Statement st , ResultSet rs){
		closeRs(rs);
		closeSt(st);
		closeConn(conn);
	}
	public static void release(Connection conn , Statement st){
		closeSt(st);
		closeConn(conn);
	}

	
	private static void closeRs(ResultSet rs){
		try {
			if(rs != null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			rs = null;
		}
	}
	
	private static void closeSt(Statement st){
		try {
			if(st != null){
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			st = null;
		}
	}
	
	private static void closeConn(Connection conn){
		try {
			if(conn != null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			conn = null;
		}
	}
}

事务只针对连接.

package com.dashucoding.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.dashucoding.util.JDBCUtil;
public class TestDemo {
<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">test02</span><span class="hljs-params">()</span>{
	<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">ps</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-keyword">try</span> {
		conn = JDBCUtil.getConn();
		
		<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money = money - ? where id = ?"</span>;
		ps = conn.prepareStatement(sql);
		
		<span class="hljs-comment">//扣钱， 扣ID为1 的100块钱</span>
		ps.setInt(<span class="hljs-number">1</span>, <span class="hljs-number">100</span>);
		ps.setInt(<span class="hljs-number">2</span>, <span class="hljs-number">1</span>);
		ps.executeUpdate();
		
		
		<span class="hljs-comment">//int a = 10 /0 ;</span>
		<span class="hljs-type">String</span> <span class="hljs-variable">s</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
		s.length();
		
		<span class="hljs-comment">//加钱， 给ID为2 加100块钱</span>
		ps.setInt(<span class="hljs-number">1</span>, -<span class="hljs-number">100</span>);
		ps.setInt(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>);
		ps.executeUpdate();
		
		
	} <span class="hljs-keyword">catch</span> (SQLException e) {
		
		e.printStackTrace();
		
	}<span class="hljs-keyword">finally</span> {
		JDBCUtil.release(conn, ps, rs);
	}
}

<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">testTransaction</span><span class="hljs-params">()</span> {
	<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">ps</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-keyword">try</span> {
	    conn = JDBCUtil.getConn();
	    
	    <span class="hljs-comment">// 连接</span>
	    conn.setAutoCommit(<span class="hljs-literal">false</span>);
	    
	    <span class="hljs-comment">//查询数据</span>
		<span class="hljs-comment">/*String sql = "select * from account";
	    ps = conn.prepareStatement(sql);*/</span>
		
	    <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money = money - ? where id = ?"</span>;
		ps = conn.prepareStatement(sql);
	    
	    <span class="hljs-comment">// 扣钱</span>
		ps.setInt(<span class="hljs-number">1</span>, <span class="hljs-number">100</span>);
		ps.setInt(<span class="hljs-number">2</span>, <span class="hljs-number">1</span>);
		ps.executeUpdate();
		
		<span class="hljs-type">int</span> <span class="hljs-variable">a</span> <span class="hljs-operator">=</span> <span class="hljs-number">10</span> /<span class="hljs-number">0</span> ;
		
		ps.setInt(<span class="hljs-number">1</span>, -<span class="hljs-number">100</span>);
		ps.setInt(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>);
		ps.executeUpdate();
		
		<span class="hljs-comment">// 提交事务</span>
		conn.commit();
		
		<span class="hljs-comment">// 查询数据 </span>
		<span class="hljs-comment">/*rs = ps.executeQuery();
		while(rs.next()) {
			System.out.println(rs.getString("name")+"=="+rs.getInt("money"));
		}*/</span>
		
	} <span class="hljs-keyword">catch</span> (SQLException e) {
		<span class="hljs-comment">// TODO Auto-generated catch block</span>
		<span class="hljs-comment">// 回滚事务</span>
		<span class="hljs-keyword">try</span> {
			conn.rollback();
		} <span class="hljs-keyword">catch</span> (SQLException e1) {
			<span class="hljs-comment">// TODO Auto-generated catch block</span>
			e1.printStackTrace();
		}
		e.printStackTrace();
	}<span class="hljs-keyword">finally</span> {
		JDBCUtil.release(conn, ps, rs);
	}
}

}

事务

conn.setAutoCommit(false)来关闭自动提交的设置
conn.commit(); 提交事务
conn.rollback(); 回滚事务

记住:
什么是事务, 事务有什么用处, 事务怎么用.

事务的特点:

原子性: 指的是事务中包含的逻辑, 不可以分割 (事务中逻辑不可分)
一致性: 事务执行前和执行后, 保持数据的完整性一致 (执行前后, 数据保持一致)
隔离性: 事务在执行期间不受到其他事务的影响 (隔离不受影响)
持久性: 事务执行结束, 提交或回滚, 数据都应该持久化到数据中 (数据持久化在数据中)

安全问题

读问题
脏读, 不可重复读, 幻读
写问题
丢失更新, 解决丢失更新的方法, 一悲观锁和乐观锁

隔离级别

Read Uncommitted 读未提交
Read Committed 读已提交
Repeatable Read 重复读
Serializable 可串行化

脏读:

一条连接:

另一条连接:

use bank;
start transaction;
select * from account;
select * from account;

use bank;
start transaction;
update account set money = money + 300 where id = 1;
commit;

读未提交

例子

这里查询, 然后再看看数据库中的表:

命令提交:

不可重复读的问题

`Repeatable Read`重复读

可串化

读未提交, 引发脏读, 读已提交解决脏读, 引发不可重复读, 可重复读解决脏读解决了脏读, 不可重复读,

asss

小结

读:
脏读不可重读读幻读

脏读:
一个事务读到另外一个事务还未提交的数据

不可重复读:
一个事务读到另外一个事务提交的数据, 导致前后两次查询结果不一致

幻读:
一个事务读到了另外一个事务已提交的插入的数据, 导致多次查询结果不一致.

读未提交,
会导致丢失更新

读已提交,
能够屏蔽脏读的现象, 但是引发不可重复读

事务的安全隐患脏读

隔离级别:

read uncommitted 读未提交
read committed 读已提交
repeatable read 重复读
serializable 可串化

mysql默认的是重复读.

设置 A 窗口的隔离级别为读未提交

两个窗口都分别开启事务

读未提交是一个事务可以读取到另一个事务还没有提交的数据, 会引发脏读现象, 读取到的是数据库内存中的数据, 并不是真正的磁盘上的数据.

还未提交时, 数据库内存中的数据是不会改变的, 只有到 commit 提交后, 数据库中的数据才会提交, 从而读取真正在磁盘上的数据.

`Read Committed`读已提交

在 a 发生了变化, 屏蔽了脏读, 引发不可重复读, 读已提交. 读已提交, 在 a 窗口设置的是读已提交, 两边开启事务. 在 b 窗口更新操作.

在 a 窗口查询结果不一致, 一次是在 b 窗口提交事务之前, 一次的提交之后.

引发了不可重复读

不可重复读, 一个事务读取到了另一个事务提交的数据, 导致前后两次查询结果不一致.

会造成问题是前后读取到的结果不一样, 发生了不可重复读, 就是不可以重复读取, 就是不能执行多次读取, 否则会导致结果不一致, 这下好了, 读取已提交导致了重复读取, 结果还不一致, 就出现了叫不可重复读现象.

安全隐患 _ 可重复读

Repeatable Read重复读, 重复读就是 mysql 默认的隔离级别, 可以让食物在自己的会话中重复读取数据, 并且不会出现结果不一致的现象, 就算其他事务提交了, 也还是会在窗口中出现以前的数据, 这就是可重复读了.

重复读, 就是让它可以重复查询, 结果还是和以前一样的效果出现.

设置重复读

安全隐患 _ 可串行化

幻读: 什么是幻读呢?
就是一个事务读到另一个事务已提交的插入的数据, 会导致多次查询结果不一致. 就是幻读, 是不是幻读理解为我幻想了, 事务已提交的插入数据, 导致幻想了,(幻读) 导致每次查询结果不一样.

事务已提交, 多次查询结果不一致.

幻读 ->Serializable可串行化

该事务的级别是最高的事务级别, 我是可串行化, 是最高的. 可以解决如下小弟的问题, 如脏读, 不可重复读, 幻读, 因为我是可串行化, 是大佬, 但作为大佬, 还是会有缺点的.

是的,a 提交才动. 现在我们让 b 先开启事务.

可串行化, 谁先打开事务, 就谁有权利, 这个隔离级别, 先打开就有权利让别人等着, 等先打开事务的那个家伙, 提交或者回滚后, 才能进行, 这种级别是用得比较少的, 因为容易导致性能上效率低下.

隔离级别有四个哦

读未提交
读已提交
可重复读
可串行化

如果按照效率划分, 从高到低, 排个名次如下:

读未提交 -> 脏读
读已提交 -> 不可重复读
可重复读 -> 解决重复读
可串行化 -> 以上都是我小弟来着

按照拦截程度来划分, 从高到底, 排名如下:

可串行化 -> 我是大哥
可重复读 -> 我是二哥
读已提交 -> 我是三弟
读未提交 -> 我是小弟

事务 _ 隔离级别小结

// 使用事务
conn.setAutoCommit(false);
// 提交
conn.commit();
// 回滚
conn.rollback();

事务只是针对连接对象. 事务是会自动提交的.

安全隐患和隔离级别

安全隐患: 读的安全隐患和写的安全隐患

读:
脏读, 读到未提交的数据, 一个事务读到了另一个事务未提交的数据;
不可重复读, 就是一个事务读到了另一个事务已经提交的数据, 导致前后两次查询的结果不一致;
幻读, 就是一个事务读到了另一个事务添加的数据, 导致前后查询结果不一致.

写: 丢失更新...

隔离级别

读未提交, 导致脏读
读已提交, 解决脏读, 导致不可重复读
可重复读, 解决脏读和不可重复读, 导致幻读
可串行化, 解决脏读, 不可重复读, 幻读

默认的 mysql 是可重复读,oracle 默认是读已提交

写的问题 _ 丢失更新

丢失更新

乐观锁
悲观锁

安全问题包含读的问题和写的问题

事务的特性是什么?
原子性, 一致性, 隔离性, 持久性

写的问题 _ 丢失更新

b 窗口没有提交. 等待提交中:

案例控制面板, 我的 a:

哭了, 这是设置默认的重复读啊!

丢失更新的问题

听说丢失更新

a 事务和 b 事务同时查询一个表,a 开始修改并提交name字段的名字, 然后 b 事务开始修改该行的money的字段, 如果 b 事务提交, 那么之前 a 事务修改的名字没有了, 变回去了哦, 当然 b 事务回滚, 也同样导致 a 事务更新没有了哦. 回滚也会把之前 b 事务的最初的数据还原.

这里的情况处理序列化级别外, 就是可串行化级别大佬哦!

解决丢失更新的方法

悲观锁
乐观锁

select * from account;

悲观锁的态度, 它是悲观的态度, 它是一定会丢失更新, 它的想法是我一定会出错.

而乐观锁, 它的态度是我一定不会丢失更新.

悲观锁

数据库的锁机制, 排他锁

select * from account for update;

丢失更新的问题

不考虑隔离性, 产生写入数据导致的问题为丢失更新的问题, 两个事务同时对某一条记录做修改, 然后会导致丢失更新的问题.

a,b 两个事务同时获取一条数据, 同时做了修改,a 事务修改后, 提交了事务,b 事务修改后, 不管是提交还是回滚, 都会对数据发生影响.

悲观锁记住用了这句:

select * from account for update;
for update;
数据库锁机制,排他锁

乐观锁

a 事务先提交, 数据库版本version变为 1,b 事务在提交的时候, 比对数据库version和自己的version是不一样的, 不允许提交, 要先更新.

a 提交的时候版本变为 1,b 提交的时候, 发现版本不一致就无法提交, 要进行更新后提交.

数据库连接池

什么是连接池, 连接池的作用是什么, 自定义连接池, 开源的连接池?

那么什么是数据库连接池?

数据库连接池是 Java 程序和数据连接的中介媒介, 以前一个 Java 程序对应一个连接, 然后就可以连接到数据了, 可以一旦多了呢?

就有人发明了数据库连接池, 可以一下连接多个, 但是是有限制的, 一旦多了, 就会扩容, 额外增加 3 到 5 个, 不会增幅太大, 也有最大值的限制.

数据库的连接对象创建工作比较消耗性能
一开始在内存中会开辟一块空间, 用于是这个数据库连接池的空间, 可以在池子里放置很多个连接对象, 数据库连接池里有很多个连接对象, 后面需要连接的话会直接从池子里面去, 就不用自己去创建连接了, 因为数据库的连接对象创建工作是比较耗时的, 使用完, 数据库连接池中的连接对象 , 是要进行归还的, 确保连接对象可以循环连接使用.

创建数据库的连接池

// 简单使用数据库连接池
package com.dashucoding.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
public class TestPool {
<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">testPool</span><span class="hljs-params">()</span> {
	<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">ps</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">MyDataSource</span> <span class="hljs-variable">dataSource</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">MyDataSource</span>();
	<span class="hljs-keyword">try</span> {

		conn = dataSource.getConnection();
		
		<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"insert into account values (null, 'xilali', 10)"</span>;
		ps = conn.prepareStatement(sql);
		ps.executeUpdate();
		
	} <span class="hljs-keyword">catch</span> (SQLException e) {
		<span class="hljs-comment">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	}<span class="hljs-keyword">finally</span> {
		<span class="hljs-keyword">try</span> {
			ps.close();
		} <span class="hljs-keyword">catch</span> (SQLException e) {
			<span class="hljs-comment">// TODO Auto-generated catch block</span>
			e.printStackTrace();
		}
		<span class="hljs-comment">// 归还连接</span>
		dataSource.addBack(conn);
	}
}

}

自定义数据库连接池

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

解决自定义数据库连接池问题

因为多了一个addBack方法, 要记住这个方法, 且不能用面向接口编程. 修改close方法, 改成不是关闭而是归还连接对象.

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);
     return conn;
  }
  // 用完后归还 
  public void addBack(Connection conn){
    list.add(conn);
  }
}

public class TestPool{
 @Test
  public void testPool(){
   Connection conn = null;
   PreparedStatement ps = null;
   MyDataSource dataSource = new MyDataSource();
  try {
   conn = dataSource.getConnection();
   String sql = "insert into account values(null,'dashu', 10)";
   ps = conn.prepareStatement(sql);
   ps.executeUpdate();
}catch(SQLException e){
e.printStackTrace();
}finally{
try{
ps.close();
}catch(SQLException e){
e.printStackTrace();
}
// 归还连接
dataSource.addBack(conn);
}
}
}

如何扩展方法

装饰者模式

package com.dashucoding.test;
public interface Waiter {
void service();
}

package com.dashucoding.test;

public class Waitress implements Waiter {
	@Override
	public void service() {
		System.out.println("在服务...");
	}
}

package com.dashucoding.test;

public class MainTest {
	public static void main(String[] args) {
		Waiter waiter = new Waitress();
		waiter.service();
	}
}

装饰者模式:

package com.dashucoding.test;
public class WaitressWrap implements Waiter {
<span class="hljs-type">Waiter</span> <span class="hljs-variable">watier</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
<span class="hljs-keyword">public</span> <span class="hljs-title function_">WaitressWrap</span><span class="hljs-params">(Waiter watier)</span> {
	<span class="hljs-built_in">this</span>.watier = watier;
}

<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">service</span><span class="hljs-params">()</span> {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	System.out.println(<span class="hljs-string">"微笑"</span>);
	watier.service();
}

}

package com.dashucoding.test;

public class MainTest {
	public static void main(String[] args) {
		/*Waiter waiter = new Waitress();
		waiter.service();*/
		WaitressWrap waitressWrap = new WaitressWrap(new Waitress());
		waitressWrap.service();
	}
}

分析

Waiter -> service()
Waitress -> service()
WaitressWrap -> service()
Waiter waiter;
public Waitress Wrap(Waiter waiter){
 this.waiter = watier;
}
void service(){
syso
waiter.service();
}

装饰者模式

package com.dashucoding.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;
// 这是一个数据库连接池
// 一开始 先往连接池放入 10 个连接
public class MyDataSource implements DataSource{
<span class="hljs-comment">// 创建连接池</span>
List&lt;Connection&gt; list = <span class="hljs-keyword">new</span> <span class="hljs-title class_">ArrayList</span>&lt;Connection&gt;();
<span class="hljs-keyword">public</span> <span class="hljs-title function_">MyDataSource</span><span class="hljs-params">()</span> {
	<span class="hljs-comment">// 10个连接</span>
	<span class="hljs-keyword">for</span>(<span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> <span class="hljs-number">0</span>; i &lt; <span class="hljs-number">10</span>; i++) {
		<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> JDBCUtil.getConn();
		list.add(conn);
	}
}

<span class="hljs-comment">// 该连接池对外公布的获取连接的方法</span>
<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> Connection <span class="hljs-title function_">getConnection</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException {
	<span class="hljs-comment">// 来拿连接的时候,看看,池子里面有没有</span>
	<span class="hljs-keyword">if</span>(list.size() == <span class="hljs-number">0</span>) {
		<span class="hljs-keyword">for</span>(<span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> <span class="hljs-number">0</span>; i &lt; <span class="hljs-number">5</span>; i++) {
			<span class="hljs-type">Connection</span>  <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> JDBCUtil.getConn();
			list.add(conn);
		}
	}
	
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	<span class="hljs-comment">// 那一个 给连接 如果有10个,0,1,2,3,-&gt;10 这样给</span>
	<span class="hljs-comment">// 确保给</span>
	<span class="hljs-comment">// 移除就给了嘛</span>
	<span class="hljs-comment">// remove(0) -&gt; 移除第一个</span>
	<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> list.remove(<span class="hljs-number">0</span>);
	<span class="hljs-comment">// 把对象抛出去的时候,对这个对象进行包装</span>
	
	<span class="hljs-type">Connection</span> <span class="hljs-variable">connection</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">ConnectionWrap</span>(conn,list);
	
	<span class="hljs-keyword">return</span> connection;
}

<span class="hljs-comment">// 用完后记得归还</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">addBack</span><span class="hljs-params">(Connection conn)</span> {
	list.add(conn);
}







<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> PrintWriter <span class="hljs-title function_">getLogWriter</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	<span class="hljs-keyword">return</span> <span class="hljs-literal">null</span>;
}

<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> <span class="hljs-type">int</span> <span class="hljs-title function_">getLoginTimeout</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	<span class="hljs-keyword">return</span> <span class="hljs-number">0</span>;
}

<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> Logger <span class="hljs-title function_">getParentLogger</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLFeatureNotSupportedException {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	<span class="hljs-keyword">return</span> <span class="hljs-literal">null</span>;
}

<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">setLogWriter</span><span class="hljs-params">(PrintWriter arg0)</span> <span class="hljs-keyword">throws</span> SQLException {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	
}

<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">setLoginTimeout</span><span class="hljs-params">(<span class="hljs-type">int</span> arg0)</span> <span class="hljs-keyword">throws</span> SQLException {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	
}

<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> <span class="hljs-type">boolean</span> <span class="hljs-title function_">isWrapperFor</span><span class="hljs-params">(Class&lt;?&gt; arg0)</span> <span class="hljs-keyword">throws</span> SQLException {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	<span class="hljs-keyword">return</span> <span class="hljs-literal">false</span>;
}

<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> &lt;T&gt; T <span class="hljs-title function_">unwrap</span><span class="hljs-params">(Class&lt;T&gt; arg0)</span> <span class="hljs-keyword">throws</span> SQLException {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	<span class="hljs-keyword">return</span> <span class="hljs-literal">null</span>;
}



<span class="hljs-meta">@Override</span>
<span class="hljs-keyword">public</span> Connection <span class="hljs-title function_">getConnection</span><span class="hljs-params">(String arg0, String arg1)</span> <span class="hljs-keyword">throws</span> SQLException {
	<span class="hljs-comment">// TODO Auto-generated method stub</span>
	<span class="hljs-keyword">return</span> <span class="hljs-literal">null</span>;
}

}

package com.dashucoding.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 {
		// TODO Auto-generated method stub
		//connection.close();
		System.out.println("有人归还连接对象了, 归还之前"+list.size());
		list.add(connection);
		System.out.println("有人归还连接对象了, 归还之后"+list.size());
	}
	
	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return connection.prepareStatement(sql);
	}
	
	
	
	
	
	
	

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

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

	@Override
	public void abort(Executor executor) throws SQLException {
		// TODO Auto-generated method stub
		
	}

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



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

	@Override
	public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

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

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

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

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

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

	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean getAutoCommit() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

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

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

	@Override
	public String getClientInfo(String name) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

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

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

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

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

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

	@Override
	public Map<String, Class<?>> getTypeMap() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

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

	@Override
	public boolean isClosed() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean isReadOnly() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean isValid(int timeout) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public String nativeSQL(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}



	@Override
	public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void releaseSavepoint(Savepoint savepoint) throws SQLException {
		// TODO Auto-generated method stub
		
	}

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

	@Override
	public void rollback(Savepoint savepoint) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setCatalog(String catalog) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setClientInfo(Properties properties) throws SQLClientInfoException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setClientInfo(String name, String value) throws SQLClientInfoException {
		// TODO Auto-generated method stub
		
	}

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

	@Override
	public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setReadOnly(boolean readOnly) throws SQLException {
		// TODO Auto-generated method stub
		
	}

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

	@Override
	public Savepoint setSavepoint(String name) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setSchema(String schema) throws SQLException {
		// TODO Auto-generated method stub
		
	}

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

	@Override
	public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
		// TODO Auto-generated method stub
		
	}
	
}

数据库连接池 _DBCP

DBCP 开源连接池
C3P0, 什么是 C3P0, 怎么用

DBCP 为数据库连接池, 是 java 数据库连接池的一种是 Apache 开发的, 通过数据库连接池可以让程序自动管理数据库的连接.

DataBase Connection Pool数据库连接池

C3P0 也是一种开源的连接池, 实现了数据库和 JNDI 绑定, 使用它的开源项目:

Spring, Hibernate

怎么用 DBCP

导入 jar 包

commons-dbcp.jar
commons-pool.jar

// 不使用配置文件
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://loclhost/users");
dataSource.setUsername("root");
dataSource.setPassword("root");
conn = dataSource.getConnection();
String sql = "select * from user";
pstmt = conn.prepareStatement(sql);

DBCP 连接数据库使用

package com.dashucoding.dbcp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Test;
import com.dashucoding.util.JDBCUtil;
// 这个连接池要连接数据库, 账号, 密码
public class DBCPDemo {
// ctrl + 2 f
private Connection conn;
private PreparedStatement ps;
<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">testDBCP01</span><span class="hljs-params">()</span>{
	<span class="hljs-comment">// 数据库的连接池, 作用创建和连接</span>
	
	<span class="hljs-keyword">try</span> {
		<span class="hljs-comment">// 构建数据源对象</span>
		<span class="hljs-type">BasicDataSource</span> <span class="hljs-variable">dataSource</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">BasicDataSource</span>();
		<span class="hljs-comment">// 连接什么数据库,用户名和密码</span>
		dataSource.setDriverClassName(<span class="hljs-string">"com.mysql.jdbc.Driver"</span>);
		dataSource.setUrl(<span class="hljs-string">"jdbc:mysql://localhost/bank"</span>);
		dataSource.setUsername(<span class="hljs-string">"你的账户"</span>);
		dataSource.setPassword(<span class="hljs-string">"你的密码"</span>);
		
		<span class="hljs-comment">// 得到连接对象</span>
		conn = dataSource.getConnection();
		
		<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"insert into account values(null, ?, ?)"</span>;
		
		ps = conn.prepareStatement(sql);
		
		ps.setString(<span class="hljs-number">1</span>, <span class="hljs-string">"dashucoding"</span>);
		ps.setInt(<span class="hljs-number">2</span>, <span class="hljs-number">10000</span>);
		
		ps.executeUpdate();
		
	} <span class="hljs-keyword">catch</span> (SQLException e) {
		<span class="hljs-comment">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	}<span class="hljs-keyword">finally</span> {
		JDBCUtil.release(conn, ps);
	}
	
}

}

以上是不使用配置文件的情况.

DBCP 使用配置文件方式

package com.dashucoding.dbcp;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
import com.dashucoding.util.JDBCUtil;
public class DBCPDemo02 {
<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">testDBCP02</span><span class="hljs-params">()</span> {
	<span class="hljs-comment">/*BasicDataSource dataSource = new BasicDataSource();
	dataSource.setConnectionProperties("dbcpconfig.properties");*/</span>

	
	<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">ps</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

	<span class="hljs-comment">// 数据库的连接池, 作用创建和连接</span>
	<span class="hljs-keyword">try</span> {
		<span class="hljs-type">BasicDataSourceFactory</span> <span class="hljs-variable">factory</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">BasicDataSourceFactory</span>();
		<span class="hljs-type">Properties</span> <span class="hljs-variable">properties</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Properties</span>();
		<span class="hljs-type">InputStream</span> <span class="hljs-variable">is</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">FileInputStream</span>(<span class="hljs-string">"src//dbcpconfig.properties"</span>);
		properties.load(is);
		<span class="hljs-type">DataSource</span> <span class="hljs-variable">dataSource</span> <span class="hljs-operator">=</span> factory.createDataSource(properties);

		<span class="hljs-comment">// 得到连接对象</span>
		conn = dataSource.getConnection();

		<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"insert into account values(null, ?, ?)"</span>;

		ps = conn.prepareStatement(sql);

		ps.setString(<span class="hljs-number">1</span>, <span class="hljs-string">"liuliuliu"</span>);
		ps.setInt(<span class="hljs-number">2</span>, <span class="hljs-number">10000</span>);

		ps.executeUpdate();

	} <span class="hljs-keyword">catch</span> (Exception e) {
		<span class="hljs-comment">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	} <span class="hljs-keyword">finally</span> {
		JDBCUtil.release(conn, ps);
	}

}

}

数据库连接池 _C3P0

不使用配置方式

拷贝jar c3p0...jar

 Just put the jar file [lib/c3p0-0.9.1.2.jar] in your application's effective CLASSPATH

ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver            
cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
cpds.setUser("dbuser");                                  
cpds.setPassword("dbpassword");

package com.dashucoding.c3p0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Test;
import com.dashucoding.util.JDBCUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Demo {
@Test
public void testC3P0() {
	<span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">ps</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
	<span class="hljs-keyword">try</span> {
		<span class="hljs-comment">// 创建dataSource</span>
		<span class="hljs-type">ComboPooledDataSource</span> <span class="hljs-variable">dataSource</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">ComboPooledDataSource</span>();
		
		dataSource.setDriverClass(<span class="hljs-string">"com.mysql.jdbc.Driver"</span>);        
		dataSource.setJdbcUrl( <span class="hljs-string">"jdbc:mysql://localhost/bank"</span> );
		dataSource.setUser(<span class="hljs-string">"root"</span>);                                  
		dataSource.setPassword(<span class="hljs-string">"admin"</span>);  
		

		<span class="hljs-comment">// 得到连接对象</span>
		conn = dataSource.getConnection();

		<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"insert into account values(null, ?, ?)"</span>;

		ps = conn.prepareStatement(sql);

		ps.setString(<span class="hljs-number">1</span>, <span class="hljs-string">"aaa"</span>);
		ps.setInt(<span class="hljs-number">2</span>, <span class="hljs-number">10000</span>);

		ps.executeUpdate();

	} <span class="hljs-keyword">catch</span> (Exception e) {
		<span class="hljs-comment">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	} <span class="hljs-keyword">finally</span> {
		JDBCUtil.release(conn, ps);
	}

}

}

C3P0 使用配置文件的方式

c3p0-config.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost/bank</property>
    <property name="user">你的账户</property>
    <property name="password">你的密码</property>
<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"initialPoolSize"</span>&gt;</span>10<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxIdleTime"</span>&gt;</span>30<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxPoolSize"</span>&gt;</span>100<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"minPoolSize"</span>&gt;</span>10<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxStatements"</span>&gt;</span>200<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>

</default-config>
</c3p0-config>

package com.dashucoding.c3p0;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

import com.dashucoding.util.JDBCUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
// 使用配置文件
public class C3P0Demo02 {

	@Test
	public void testC3P0() {

		Connection conn = null;
		PreparedStatement ps = null;
		try {
			// 配置文件 new 了一个对象
			ComboPooledDataSource dataSource = new ComboPooledDataSource(); 
			
			// 得到连接对象
			conn = dataSource.getConnection();

			String sql = "insert into account values(null, ?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "bbb");
			ps.setInt(2, 10000);
			ps.executeUpdate();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}

	}
}

可以弄 oracle:

<!-- This app is massive! -->
  <named-config name="oracle"> 
    <property name="acquireIncrement">50</property>
    <property name="initialPoolSize">100</property>
    <property name="minPoolSize">50</property>
    <property name="maxPoolSize">1000</property>
<span class="hljs-comment">&lt;!-- intergalactoApp adopts a different approach to configuring statement caching --&gt;</span>
<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxStatements"</span>&gt;</span>0<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span> 
<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxStatementsPerConnection"</span>&gt;</span>5<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>

<span class="hljs-comment">&lt;!-- he's important, but there's only one of him --&gt;</span>
<span class="hljs-tag">&lt;<span class="hljs-name">user-overrides</span> <span class="hljs-attr">user</span>=<span class="hljs-string">"master-of-the-universe"</span>&gt;</span> 
  <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"acquireIncrement"</span>&gt;</span>1<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
  <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"initialPoolSize"</span>&gt;</span>1<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
  <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"minPoolSize"</span>&gt;</span>1<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
  <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxPoolSize"</span>&gt;</span>5<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
  <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxStatementsPerConnection"</span>&gt;</span>50<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span>
<span class="hljs-tag">&lt;/<span class="hljs-name">user-overrides</span>&gt;</span>

</named-config>

oracle:

ComboPooledDataSource dataSource = new ComboPooledDataSource();

`DBUtils`

什么是 DBUtils 呢? 怎么用呢?

优化数据库连接, 使用 C3P0:

package com.dashucoding.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtil02 {
<span class="hljs-keyword">static</span> <span class="hljs-type">ComboPooledDataSource</span> <span class="hljs-variable">dataSource</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

<span class="hljs-keyword">static</span> {
	dataSource = <span class="hljs-keyword">new</span> <span class="hljs-title class_">ComboPooledDataSource</span>();
}
<span class="hljs-comment">/**
 * 获取连接对象
 * <span class="hljs-doctag">@return</span>
 * <span class="hljs-doctag">@throws</span> SQLException 
 */</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> Connection <span class="hljs-title function_">getConn</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException{
	
	<span class="hljs-keyword">return</span> dataSource.getConnection();
}

<span class="hljs-comment">/**
 * 释放资源
 * <span class="hljs-doctag">@param</span> conn
 * <span class="hljs-doctag">@param</span> st
 * <span class="hljs-doctag">@param</span> rs
 */</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">release</span><span class="hljs-params">(Connection conn , Statement st , ResultSet rs)</span>{
	closeRs(rs);
	closeSt(st);
	closeConn(conn);
}
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">release</span><span class="hljs-params">(Connection conn , Statement st)</span>{
	closeSt(st);
	closeConn(conn);
}


<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">closeRs</span><span class="hljs-params">(ResultSet rs)</span>{
	<span class="hljs-keyword">try</span> {
		<span class="hljs-keyword">if</span>(rs != <span class="hljs-literal">null</span>){
			rs.close();
		}
	} <span class="hljs-keyword">catch</span> (SQLException e) {
		e.printStackTrace();
	}<span class="hljs-keyword">finally</span>{
		rs = <span class="hljs-literal">null</span>;
	}
}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">closeSt</span><span class="hljs-params">(Statement st)</span>{
	<span class="hljs-keyword">try</span> {
		<span class="hljs-keyword">if</span>(st != <span class="hljs-literal">null</span>){
			st.close();
		}
	} <span class="hljs-keyword">catch</span> (SQLException e) {
		e.printStackTrace();
	}<span class="hljs-keyword">finally</span>{
		st = <span class="hljs-literal">null</span>;
	}
}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">closeConn</span><span class="hljs-params">(Connection conn)</span>{
	<span class="hljs-keyword">try</span> {
		<span class="hljs-keyword">if</span>(conn != <span class="hljs-literal">null</span>){
			conn.close();
		}
	} <span class="hljs-keyword">catch</span> (SQLException e) {
		e.printStackTrace();
	}<span class="hljs-keyword">finally</span>{
		conn = <span class="hljs-literal">null</span>;
	}
}

}

DBUtils 优化增删改查方法

导包, 两行代码交你增删改查,6666!

QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
queryRunner.update("insert into account values(null, ?, ?)", "bbb", 10000);

两行.

package com.dashucoding.dbutils;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class TestDBUtils {
@Test
public void testInsert() throws SQLException {
//		ComboPooledDataSource dataSource = new ComboPooledDataSource(); 
	<span class="hljs-comment">// dbutils 简化了CRUD的代码</span>
	<span class="hljs-type">QueryRunner</span> <span class="hljs-variable">queryRunner</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">QueryRunner</span>(<span class="hljs-keyword">new</span> <span class="hljs-title class_">ComboPooledDataSource</span>());
	
	<span class="hljs-comment">/*
	 * queryRunner.update(sql);
	 * 增删改
	 * queryRunner.query(sql, rsh);
	 * 查
	 * */</span>
	
	queryRunner.update(<span class="hljs-string">"insert into account values(null, ?, ?)"</span>, <span class="hljs-string">"bbb"</span>, <span class="hljs-number">10000</span>);
}

}

package com.dashucoding.dbutils;

import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class TestDBUtils {
	@Test
	public void testInsert() throws SQLException {
//		ComboPooledDataSource dataSource = new ComboPooledDataSource(); 

		// dbutils 简化了 CRUD 的代码
		QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
		
		/*
		 * queryRunner.update(sql);
		 * 增删改
		 * queryRunner.query(sql, rsh);
		 * 查
		 * */
		
		queryRunner.update("insert into account values(null, ?, ?)", "bbb", 10000);
		queryRunner.update("delete from account where id = ?", 8);
		queryRunner.update("update account set money = ? where id = ?", 0, 10);
	}

}

查询

package com.dashucoding.dbutils;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.junit.Test;
import com.dashucoding.domain.Account;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class TestDBUtils {
@Test
public void testInsert() throws SQLException {
//		ComboPooledDataSource dataSource = new ComboPooledDataSource(); 
	<span class="hljs-comment">// dbutils 简化了CRUD的代码</span>
	<span class="hljs-type">QueryRunner</span> <span class="hljs-variable">queryRunner</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">QueryRunner</span>(<span class="hljs-keyword">new</span> <span class="hljs-title class_">ComboPooledDataSource</span>());
	<span class="hljs-comment">// 查询的是一个Bean对象</span>
	<span class="hljs-comment">// new 匿名实现类-&gt;new接口的匿名实现类</span>
	
	<span class="hljs-type">Account</span> <span class="hljs-variable">account</span> <span class="hljs-operator">=</span> queryRunner.query(<span class="hljs-string">"select * from account where id = ?"</span>, <span class="hljs-keyword">new</span> <span class="hljs-title class_">ResultSetHandler</span>&lt;Account&gt;() {
		<span class="hljs-comment">// 去执行查询,查询到的数据在result里面,然后调用handle方法,用户手动去封装</span>
		<span class="hljs-meta">@Override</span>
		<span class="hljs-keyword">public</span> Account <span class="hljs-title function_">handle</span><span class="hljs-params">(ResultSet rs)</span> <span class="hljs-keyword">throws</span> SQLException {
			<span class="hljs-comment">// TODO Auto-generated method stub</span>
			<span class="hljs-type">Account</span> <span class="hljs-variable">account</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Account</span>();
			
			<span class="hljs-keyword">while</span>(rs.next()) {
				<span class="hljs-type">String</span> <span class="hljs-variable">name</span> <span class="hljs-operator">=</span> rs.getString(<span class="hljs-string">"name"</span>);
				<span class="hljs-type">int</span> <span class="hljs-variable">money</span> <span class="hljs-operator">=</span> rs.getInt(<span class="hljs-string">"money"</span>);
				account.setName(name);
				account.setMoney(money);
			}
			<span class="hljs-keyword">return</span> account;
		}
		
	}, <span class="hljs-number">1</span>);
	System.out.println(account.toString());
	
	<span class="hljs-comment">/*
	 * queryRunner.update(sql);
	 * 增删改
	 * queryRunner.query(sql, rsh);
	 * 查
	 * */</span>
	
	<span class="hljs-comment">/*queryRunner.update("insert into account values(null, ?, ?)", "bbb", 10000);
	queryRunner.update("delete from account where id = ?", 8);
	queryRunner.update("update account set money = ? where id = ?", 0, 10);*/</span>
}

}

查询优化

一个对象: BeanHandler

一个集合里面有很多对象: BeanListHandler

package com.dashucoding.domain;
public class Account {
private String name;
private int money;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getMoney() {
return money;
}
public void setMoney(int money) {
this.money = money;
}
@Override
public String toString() {
return "Account [name=" + name + ", money=" + money + "]";
}
}

package com.dashucoding.dbutils;

import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;

import com.dashucoding.domain.Account;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class TestDBUtils {
	@Test
	public void testInsert() throws SQLException {
//		ComboPooledDataSource dataSource = new ComboPooledDataSource(); 

		// dbutils 简化了 CRUD 的代码
		QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
		// 查询的是一个 Bean 对象
		// new 匿名实现类 ->new 接口的匿名实现类
		
		/*Account account = queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>() {
			// 去执行查询, 查询到的数据在 result 里面, 然后调用 handle 方法, 用户手动去封装
			@Override
			public Account handle(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				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;
			}
			
		}, 1);
		System.out.println(account.toString());*/
		
		// 接口的实现类 -> ResultSetHandler 的实现类
		// Ctrl + t 实现类
		//ResultSetHandler
		
		// 一个对象查询一个对象使用 BeanHandler
		Account account = queryRunner.query("select * from account where id = ?", new BeanHandler<Account>(Account.class), 1);
		System.out.println(account.toString());
		/*
		 * queryRunner.update(sql);
		 * 增删改
		 * queryRunner.query(sql, rsh);
		 * 查
		 * */
		
		/*queryRunner.update("insert into account values(null, ?, ?)", "bbb", 10000);
		queryRunner.update("delete from account where id = ?", 8);
		queryRunner.update("update account set money = ? where id = ?", 0, 10);*/
	}

}

优化成两行代码:

Account account = queryRunner.query("select * from account where id = ?", new BeanHandler<Account>(Account.class), 1);
System.out.println(account.toString());

查询多个数据

// 查询多个对象
List<Account> list = queryRunner.query("select * from account",
				new BeanListHandler<Account>(Account.class));
for(Account account : list) {
 System.out.println(account.toString());
}

package com.dashucoding.dbutils;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import com.dashucoding.domain.Account;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class TestDBUtils {
@Test
public void testInsert() throws SQLException {
//		ComboPooledDataSource dataSource = new ComboPooledDataSource(); 
	<span class="hljs-comment">// dbutils 简化了CRUD的代码</span>
	<span class="hljs-type">QueryRunner</span> <span class="hljs-variable">queryRunner</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">QueryRunner</span>(<span class="hljs-keyword">new</span> <span class="hljs-title class_">ComboPooledDataSource</span>());
	<span class="hljs-comment">// 查询的是一个Bean对象</span>
	<span class="hljs-comment">// new 匿名实现类-&gt;new接口的匿名实现类</span>
	
	<span class="hljs-comment">/*Account account = queryRunner.query("select * from account where id = ?", new ResultSetHandler&lt;Account&gt;() {
		// 去执行查询,查询到的数据在result里面,然后调用handle方法,用户手动去封装
		@Override
		public Account handle(ResultSet rs) throws SQLException {
			// TODO Auto-generated method stub
			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;
		}
		
	}, 1);
	System.out.println(account.toString());*/</span>
	
	<span class="hljs-comment">// 接口的实现类-&gt; ResultSetHandler的实现类</span>
	<span class="hljs-comment">// Ctrl + t 实现类</span>
	<span class="hljs-comment">//ResultSetHandler</span>
	
	<span class="hljs-comment">// 一个对象查询一个对象使用BeanHandler</span>
	<span class="hljs-comment">/*Account account = queryRunner.query("select * from account where id = ?",
			new BeanHandler&lt;Account&gt;(Account.class), 1);
	System.out.println(account.toString());*/</span>
	
	<span class="hljs-comment">/*List&lt;Account&gt; account = queryRunner.query("select * from account",
			new BeanListHandler&lt;Account&gt;(Account.class));*/</span>
	<span class="hljs-comment">// 查询多个对象</span>
	List&lt;Account&gt; list = queryRunner.query(<span class="hljs-string">"select * from account"</span>,
			<span class="hljs-keyword">new</span> <span class="hljs-title class_">BeanListHandler</span>&lt;Account&gt;(Account.class));
	<span class="hljs-keyword">for</span>(Account account : list) {
		System.out.println(account.toString());
	}
	
	<span class="hljs-comment">/*
	 * queryRunner.update(sql);
	 * 增删改
	 * queryRunner.query(sql, rsh);
	 * 查
	 * */</span>
	
	<span class="hljs-comment">/*queryRunner.update("insert into account values(null, ?, ?)", "bbb", 10000);
	queryRunner.update("delete from account where id = ?", 8);
	queryRunner.update("update account set money = ? where id = ?", 0, 10);*/</span>
}

}

小结

// 通过类的字节码, 获得该类的实例:
Account a = new Account();
Account a1 = Account.class.newInstance();
new BeanHandler<Account>(Account.class)
new BeanListHandler<Account>(Account.class)

`ResultSetHandler<T>`

实现类:

总结

事务, 连接池,DBUtils

查询:

QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
queryRunner.update();
queryRunner.query

事务
脏读
不可重复读
幻读

丢失更新写的问题
悲观锁 for update
乐观锁 , 添加字段版本级别

4 个隔离级别
读未提交
读已提交
可重复读
可串行化

读未提交, 引发脏读
读已提交, 引发不可重复读, 解决了脏读

可重复读, 引发了幻读, 解决了脏读, 不可重复读
可串行化 (序列化), 解决了脏读, 不可重复读, 幻读, 引发效率低下问题

数据库连接池

BCP -> 不使用配置使用配置
C3P0 -> 不使用配置使用配置
自定义连接池 -> 饰者模式

结言

可在评论发表你的总结内容, 做功课哦!

如果看了觉得不错

点赞！转发！

达叔小生：往后余生，唯独有你
You and me, we are family !
90 后帅气小伙，良好的开发习惯；独立思考的能力；主动并且善于沟通
简书博客：达叔小生
https://www.jianshu.com/u/c785ece603d1

结语

下面我将继续对其他知识深入讲解，有兴趣可以继续关注
小礼物走一走 or 点赞

第77节:Java中的事务和数据库连接池和DBUtiles

前言

事务

回顾一下

`EL`

`JSTL`

数据库

`Repeatable Read`重复读

可串化

小结

事务的安全隐患脏读

`Read Committed`读已提交

安全隐患 _ 可重复读

设置重复读

安全隐患 _ 可串行化

事务 _ 隔离级别小结

写的问题 _ 丢失更新

写的问题 _ 丢失更新

丢失更新的问题

数据库连接池

数据库连接池 _C3P0

`DBUtils`

小结

`ResultSetHandler<T>`

总结

结言

结语

相关帖子

随便看看