第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 { 
    
    @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(); 回滚事务

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

事务的特点:

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

安全问题

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

隔离级别

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先开启事务.

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

隔离级别有四个哦

1. 读未提交
2. 读已提交
3. 可重复读
4. 可串行化

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

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

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

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

事务_隔离级别小结

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

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

安全隐患和隔离级别

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

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

写: 丢失更新...

隔离级别

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

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

写的问题_丢失更新

丢失更新

1. 乐观锁
2. 悲观锁

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

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

写的问题_丢失更新

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

案例控制面板,我的a:

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

丢失更新的问题

听说丢失更新

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

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

解决丢失更新的方法

1. 悲观锁
2. 乐观锁

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

1. 导入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