第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 {
@Test
public void testTransaction() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConn();
//查询数据
/*String sql = "select * from account";
ps = conn.prepareStatement(sql);*/
String sql = "update account set money = money - ? where id = ?";
ps = conn.prepareStatement(sql);
// 扣钱
ps.setInt(1, 100);
ps.setInt(2, 1);
ps.executeUpdate();
/*ps.setInt(1, -100);
ps.setInt(2, 1);
ps.executeUpdate();*/
// 查询数据
/*rs = ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getString("name")+"=="+rs.getInt("money"));
}*/
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
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 {
@Test
public void test02(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConn();
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 ;
String s = null;
s.length();
//加钱， 给ID为2 加100块钱
ps.setInt(1, -100);
ps.setInt(2, 2);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps, rs);
}
}
@Test
public void testTransaction() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConn();
// 连接
conn.setAutoCommit(false);
//查询数据
/*String sql = "select * from account";
ps = conn.prepareStatement(sql);*/
String sql = "update account set money = money - ? where id = ?";
ps = conn.prepareStatement(sql);
// 扣钱
ps.setInt(1, 100);
ps.setInt(2, 1);
ps.executeUpdate();
int a = 10 /0 ;
ps.setInt(1, -100);
ps.setInt(2, 2);
ps.executeUpdate();
// 提交事务
conn.commit();
// 查询数据
/*rs = ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getString("name")+"=="+rs.getInt("money"));
}*/
} catch (SQLException e) {
// TODO Auto-generated catch block
// 回滚事务
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}finally {
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 {
@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, 'xilali', 10)";
ps = conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 归还连接
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 {
Waiter watier = null;
public WaitressWrap(Waiter watier) {
this.watier = watier;
}
@Override
public void service() {
// TODO Auto-generated method stub
System.out.println("微笑");
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{
// 创建连接池
List<Connection> list = new ArrayList<Connection>();
public MyDataSource() {
// 10个连接
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);
}
}
// TODO Auto-generated method stub
// 那一个 给连接 如果有10个,0,1,2,3,->10 这样给
// 确保给
// 移除就给了嘛
// remove(0) -> 移除第一个
Connection conn = list.remove(0);
// 把对象抛出去的时候,对这个对象进行包装
Connection connection = new ConnectionWrap(conn,list);
return connection;
}
// 用完后记得归还
public void addBack(Connection conn) {
list.add(conn);
}
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@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 void setLogWriter(PrintWriter arg0) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int arg0) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Connection getConnection(String arg0, String arg1) throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
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;
@Test
public void testDBCP01(){
// 数据库的连接池, 作用创建和连接
try {
// 构建数据源对象
BasicDataSource dataSource = new BasicDataSource();
// 连接什么数据库,用户名和密码
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost/bank");
dataSource.setUsername("你的账户");
dataSource.setPassword("你的密码");
// 得到连接对象
conn = dataSource.getConnection();
String sql = "insert into account values(null, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "dashucoding");
ps.setInt(2, 10000);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
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 {
@Test
public void testDBCP02() {
/*BasicDataSource dataSource = new BasicDataSource();
dataSource.setConnectionProperties("dbcpconfig.properties");*/
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);
// 得到连接对象
conn = dataSource.getConnection();
String sql = "insert into account values(null, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "liuliuliu");
ps.setInt(2, 10000);
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
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() {
Connection conn = null;
PreparedStatement ps = null;
try {
// 创建dataSource
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl( "jdbc:mysql://localhost/bank" );
dataSource.setUser("root");
dataSource.setPassword("admin");
// 得到连接对象
conn = dataSource.getConnection();
String sql = "insert into account values(null, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "aaa");
ps.setInt(2, 10000);
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
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>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</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>
<!-- intergalactoApp adopts a different approach to configuring statement caching -->
<property name="maxStatements">0</property>
<property name="maxStatementsPerConnection">5</property>
<!-- he's important, but there's only one of him -->
<user-overrides user="master-of-the-universe">
<property name="acquireIncrement">1</property>
<property name="initialPoolSize">1</property>
<property name="minPoolSize">1</property>
<property name="maxPoolSize">5</property>
<property name="maxStatementsPerConnection">50</property>
</user-overrides>
</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 {
static ComboPooledDataSource dataSource = null;
static {
dataSource = new ComboPooledDataSource();
}
/**
* 获取连接对象
* @return
* @throws SQLException
*/
public static Connection getConn() throws SQLException{
return dataSource.getConnection();
}
/**
* 释放资源
* @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;
}
}
}
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();
// dbutils 简化了CRUD的代码
QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
/*
* queryRunner.update(sql);
* 增删改
* queryRunner.query(sql, rsh);
* 查
* */
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();
// 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();
// 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());
/*
* 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);*/
}
}
查询优化
一个对象: 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();
// 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());*/
/*List<Account> account = queryRunner.query("select * from account",
new BeanListHandler<Account>(Account.class));*/
// 查询多个对象
List<Account> list = queryRunner.query("select * from account",
new BeanListHandler<Account>(Account.class));
for(Account account : list) {
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 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 点赞