Java数据库连接--JDBC调用存储过程，事务管理和高级应用

相关链接：Jdbc调用存储过程

 一、JDBC常用的API深入详解及存储过程的调用

 1、存储过程的介绍

　　我们常用的操作数据库语言SQL语句在执行的时候要先进行编译，然后执行，而存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过制定存储过程的名字并给出参数(如果该存储过程带有参数) 来执行它。存储过程是数据库中 的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

　　一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或者平台上执行相同函数，或者封装特定功能时，存储过程非常有用。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

存储过程有以下优点：

　　(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

　(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库
专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

　　(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。

因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL

语句在每次运行时都要进行编译和优化，速度相对要慢一些。

　　(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，

那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

　　(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免

了非授权用户对数据的访问，保证了数据的安全。

 简单说，好处主要：

　　1、由于数据执行动作时，先编译后执行，然而存储过程是一个编译过的代码块，所以执行效率比T-SQL高。

　　2、一个存储过程在程序中交互可以替代大队的T-SQL语句，所以也能降低网络的通信量，提高通信效率。

　　3、通过存储过程能够使没有权限的用户在控制之下间接存取数据库，保证数据安全。

 2、Jdbc调用无参数存储过程

存储过程代码：
    CREATE PROCEDURE imooc_db.sp_select_nofilter()
    BEGIN
            select * from imooc_goddess;
    END;

调用代码：
    Connection conn=DBUtil.getConnection();
    CallableStatement c=conn.prepareCall("call sp_select_nofilter()");
    c.execute();
    ResultSet rs=c.getResultSet();

 在数据库中新建存储过程：

          　　　

注意：创建的存储过程名称不要加"()",不然在调用存储过程会出错。

代码示例：

package produceDao;

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

import sql.Dao.DBUtil;

public class ProduceDAO {
    public void select_nofilter() throws SQLException{
        //1、获得连接
        Connection connection=DBUtil.getConnection();
        //2、获得CallableStatement
        CallableStatement cs=connection.prepareCall("call sp_select_nofilter()");
        //3、执行存储过程
        cs.execute();
        //4、处理返回结果：结果集，出参
        ResultSet rs=cs.getResultSet();
        //遍历结果集
        while (rs.next()){
            System.out.println(rs.getString("user_name")+":"+rs.getString("email"));
        }
    }
}

package producetest;

import java.sql.SQLException;
import produceDao.ProduceDAO;

public class JdbcProduceTest {
    public static void main(String []args) throws SQLException{
        ProduceDAO dao=new ProduceDAO();
        dao.select_nofilter();
    }
}

运行结果：

          

3、Jdbc调用含输入参数存储过程

新建一个过程：

        

如果输入空白字符，会全部显示，结果如下：

           

如果传入一个'蔡'，则结果如下：

//只有输入in，没有输出的存储过程
    public List<Goddess> select_filter(String sp_name) throws SQLException{
        List<Goddess> result=new ArrayList<>();
        //1、获得连接
        Connection connection=DBUtil.getConnection();
        //2、获得CallableStatement
        CallableStatement cs=connection.prepareCall("call sp_select_filter(?)");
        cs.setString(1, sp_name);
        //3、执行存储过程
        cs.execute();
        //4、处理返回结果：结果集，出参
        ResultSet rs=cs.getResultSet();
        Goddess goddess=null;
        //遍历结果集
        while (rs.next()){
            goddess=new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUserName(rs.getString("user_name"));
            goddess.setAge(rs.getInt("age"));
            result.add(goddess);
        }
        return result;
    }

 测试：

public class JdbcProduceTest {
    public static void main(String []args) throws SQLException{
        ProduceDAO dao=new ProduceDAO();
        //dao.select_nofilter();
        String sp_name="白";
        List<Goddess> res=null;
        res=dao.select_filter(sp_name);
        for(int i=0;i<res.size();i++){
            System.out.println(res.get(i).getId()+":"+res.get(i).getUserName()+":"+res.get(i).getAge());
        }
    }
}

运行结果：

          

4、Jdbc调用含输出参数存储过程

 建立一个过程

         

调用存储过程：显示出一共多少记录，结果如下：

          

含有输出参数的存储过程

//Jdbc调用含有输出参数的的存储过程
    public Integer select_count()throws SQLException{
        Integer count=0;
        Connection connection=DBUtil.getConnection();
        CallableStatement cs=connection.prepareCall("call sp_select_count(?)");
        cs.registerOutParameter(1, Types.INTEGER);//注册输出参数，第二个参数时告诉jdbc，输出参数的类型。
        cs.execute();
        //处理返回的结果：这个结果不是结果集，而是出参
        count=cs.getInt(1);
        return count;
    }

 测试：

public class JdbcProduceTest {
    public static void main(String []args) throws SQLException{
        ProduceDAO dao=new ProduceDAO();
//        //dao.select_nofilter();
//        String sp_name="蔡";
//        List<Goddess> res=null;ss
//        res=dao.select_filter(sp_name);
//        for(int i=0;i<res.size();i++){
//            System.out.println(res.get(i).getId()+":"+res.get(i).getUserName()+":"+res.get(i).getAge());
//        }
        String sp_name="蔡";
        List<Goddess> res=null;
        Integer count=0;
        
        //带输入参数的存储过程
        res=select_filter(sp_name);
        showResult(res);
        
        count=select_count();
        System.out.println("一共有"+count+"个女神！");
    }
    
    public static List<Goddess> select_filter(String sp_name)throws SQLException{
        ProduceDAO dao=new ProduceDAO();
        return dao.select_filter(sp_name);
    }
    public static Integer select_count()throws SQLException{
        ProduceDAO dao=new ProduceDAO();
        return dao.select_count();
    }
    public static void showResult(List<Goddess> result){
        for(int i=0;i<result.size();i++){
            System.out.println(result.get(i).getId()+":"+result.get(i).getUserName()+":"+result.get(i).getAge());
        }
    }
    
}

运行结果：

        

 二、JDBC的事务管理

事务的概念：

　　事务是作为单个逻辑工作单元执行的一系列操作。这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行。

事务的特点：

　　1、原子性：事务是一个完整的操作。不能对它进行再分割，是最小的一个单元。

　　2、一致性：当事务完成时，数据必须处于一致状态。（例如银行转账，张三要给李四转100元。则第一步张三的账户需要减去100元，第二步李四的账户需要加上100元。这是两个操作，但是应该在一个事务里面。如果没有在一个事务里面，张三减去100，李四并没有增加100，那这样数据就出现了不一致性，张三的钱跑哪去了呢 ）

　　3、隔离性：对数据进行修改的所有并发事务是彼此隔离的。（比如业务A:张三减100，李四加100；同时业务B也是张三减100，李四加100进行操作。业务A和B是同时的，这时候就出现了并发，这个时候是怎么变化的呢？当业务员A进行操作的时候，业务员B就要等待……就是同一时间对数据库的操作要保持一个事务的锁定。也就是说我在做的时候，别人是不能做的。我做完了之后别人才能做，彼此之间是隔离的）

　　4、永久性：事务完成之后，它对数据库的修改是永久保存的。

1、Jdbc实现事务管理

(1)、我们通过提交commit()或者回退rollback()来管理事务的操作。

　　当事务完成之后，我们通过commit将事务提交到数据库之中，然后数据库会变成持久化的，我们的数据就会永久保存了。

　　如果采用rollback的话，事务回滚，比如说我们插入的数据、更新的数据都会变成原来没有更新、没有插入的样子。

(2)、事务操作默认是自动提交的

　　当我们调用完insert语句，不用调用commit语句，自己就自动提交了。

(3)、可以调用setAutoCommit(false)来禁止自动提交。

2、通过代码实现事务的管理

　　首先我们要注意，在JDBC中，事务操作默认是自动提交。也就是说，一条对数据库的更新表达式代表一项事务操作。操作成功后，系统将自动调用commit()来提交，否则将调用rollback()来回退。

　　其次，在JDBC中，可以通过调用setAutoCommit(false)来禁止自动提交。之后就可以把多个数据库操作的表达式作为一个事务，在操作完成 后调用commit()来进行整体提交。倘若其中一个表达式操作失败，都不会执行到commit()，并且将产生响应的异常。此时就可以在异常捕获时调用 rollback()进行回退。这样做可以保持多次更新操作后，相关数据的一致性。

private static final String URL="jdbc:mysql://localhost:3306/mydb";
 2 private static final String NAME="root";
private static final String PASSWORD="mysql123";
try{
        conn=DriverManager.getConnection(URL,NAME,PASSWORD);
        conn.setAutoCommit(false);//禁止自动提交，设置回退
        stmt=conn.createStatement();
        //数据库更新操作1
        stmt.executeUpdate("update firsttable Set Name='testTransaction' where ID=1");
        //数据库更新操作2
        stmt.executeUpdate("insert into firsttable ID=12,Name='testTransaction2'");
        //事务提交
        conn.commit();
    }catch(Exception e){
        e.printStackTrace();
        try {
            //操作不成功则回退
            conn.rollback();
        } catch (Exception ex) {
            // TODO: handle exception
            ex.printStackTrace();
        }
    }

 三、数据库连接池(dbcp、c3p0)

连接池产生的背景：

　　数据库连接是一种重要资源。大部分很重要的数据都存在数据库里，那么在产生连接池之前，我们连接数据库的方式：直连。（获取连接->使用->关闭连接）程序小的话可以采用这种方式，但是如果程序很大，比如大型网站，它可能每分钟或者每秒变化量在100万次，就是说同时访问数据库有100万个用户，这时候如果我们不用连接池的话，我们就需要创建100万个连接这样的话就会对数据库造成很大的压力，如果数据库承受不了的话就崩溃了，服务器也崩溃了，网站就瘫痪了。

即：

①数据库连接是一种重要资源；

②频繁的连接数据库会增加数据库的压力；

③为解决以上问题出现连接池技术。

（池子里保持一定数量的连接，当使用时就从池子中拿一个连接出来，当使用完连接后就把它释放到池子里。当你同时访问数据库人很多的时候，这个时候连接不够用，就需要等待，减少数据库的压力）

常用的开源数据库连接池：

1. dbcp
2. c3p0

1、dbcp的使用步骤

　　1、首先，导入相关jar包：

　　　　　　commons-dbcp-1.4.jar

　　　　　　commons-pool-1.5.6.jar

　　　　　　commons-logging-1.1.1.jar

　　2、根目录下设置配置文件(/src下)：dbcp.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb
username=root
password=mysql123
 
maxIdle=30
maxIdle=10
maxWait=1000

removeAbandoned=true
removeAbandonedTimeout=180

　　3、配置并测试dbcp连接

案例一：dbcptest.java

package db;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

public class testjdbc {
    public static void main(String []args) throws SQLException{
        BasicDataSource dataSource=null;
        dataSource=new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mydb");
        dataSource.setUsername("root");
        dataSource.setPassword("mysql123");
        Connection connection=dataSource.getConnection();
        System.out.println(connection);
    }
}

运行结果：

　　       

案例二：

DBCPUtil.java

package db;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPUtil {
    private static DataSource DS;
    private static final String configFile="dbcp.properties";
    
    /**
     * 从数据源获得一个连接
     * @throws SQLException 
     * */
    
    public Connection getConn() throws SQLException,Exception{
        Connection conn=null;
        if(DS!=null){
            conn=DS.getConnection();//从数据源里拿到连接
            conn.setAutoCommit(false);//关闭连接的自动提交
            return conn;    
        }
        return conn;
    }
    
    
    /**
     * 默认的构造函数
     * @throws Exception 
     * **/
    public DBCPUtil() throws Exception{
        initDbcp();
    }
    
    private static void initDbcp() throws Exception{
        Properties pops=new Properties();
        pops.load(DBCPUtil.class.getClassLoader().getResourceAsStream(configFile));
        
        DS=BasicDataSourceFactory.createDataSource(pops);//通过BasicDataSourceFactory提供的工厂类，拿到DataSource数据源
    }
    
    //构造函数，初始化了DS，指定数据库
    public DBCPUtil(String url){
        initDS(url);
    }
    
    //构造函数，初始化了DS，指定了所有参数
    public DBCPUtil(String connectURL,String username,String password,String driverClass,int initialSize, 
            int maxIdle,int minIdle,int maxWait){
        initDS(connectURL,username,password,driverClass,initialSize,maxIdle,minIdle,maxWait);
    }
    
    public static void initDS(String url){
        initDS(url,"root","mysql123","com.mysql.jdbc.Driver",10,20,5,1000);
    }
    public static void initDS(String connectURL,String userName,String password,String driverClass,int initialSize,int maxIdle,int minIdle,int maxWait){
        BasicDataSource ds=new BasicDataSource();//new一个数据源
        ds.setDriverClassName(driverClass);
        ds.setUsername(userName);
        ds.setPassword(password);
        ds.setUrl(connectURL);
        ds.setInitialSize(initialSize);
        ds.setMaxIdle(maxIdle);
        ds.setMaxWait(maxWait);
        ds.setMinIdle(minIdle);
        DS=ds;
    }
}

GoddessDao.java

//查询单个女神
    public Goddess get(Integer id) throws SQLException{
        Goddess goddess=null;
        Connection connection=DBUtil.getConnection();
        String sql=""+
                "select * from imooc_goddess "+
                "where id=?";
        PreparedStatement psmt=connection.prepareStatement(sql);
        psmt.setInt(1, id);
        //psmt.execute();execute()是执行更改数据库操作(包括新增、修改、删除)；executeQuery()是执行查询操作
        ResultSet rsResultSet=psmt.executeQuery();
        while(rsResultSet.next()){
            goddess=new Goddess();
            goddess.setId(rsResultSet.getInt("id"));
            goddess.setUserName(rsResultSet.getString("user_name"));
            goddess.setSex(rsResultSet.getString("sex"));
            goddess.setAge(rsResultSet.getInt("age"));
            goddess.setBirthday(rsResultSet.getDate("birthday"));
            goddess.setEmail(rsResultSet.getString("email"));
            goddess.setMobile(rsResultSet.getString("mobile"));
            goddess.setCreateUser(rsResultSet.getString("create_user"));
            goddess.setCreateDate(rsResultSet.getDate("create_date"));
            goddess.setUpdateUser(rsResultSet.getString("update_user"));
            goddess.setUpdateDate(rsResultSet.getDate("update_date"));
            goddess.setIsDel(rsResultSet.getInt("isdel"));
        }
        return goddess;
    }
//查询单个女神(根据id去查询，DBCP连接池的方式)
    public Goddess getByDbcp(Integer id) throws Exception{
        // TODO Auto-generated method stub
        DBCPUtil db=new DBCPUtil();//
        Goddess goddess=null;
        Connection connection=db.getConn();//拿到数据库的连接，通过DBCP
        String sql=""+
                "select * from imooc_goddess "+
                "where id=?";
        PreparedStatement psmt=connection.prepareStatement(sql);
        psmt.setInt(1, id);
        //psmt.execute();execute()是执行更改数据库操作(包括新增、修改、删除)；executeQuery()是执行查询操作
        ResultSet rsResultSet=psmt.executeQuery();
        while(rsResultSet.next()){
            goddess=new Goddess();
            goddess.setId(rsResultSet.getInt("id"));
            goddess.setUserName(rsResultSet.getString("user_name"));
            goddess.setSex(rsResultSet.getString("sex"));
            goddess.setAge(rsResultSet.getInt("age"));
            goddess.setBirthday(rsResultSet.getDate("birthday"));
            goddess.setEmail(rsResultSet.getString("email"));
            goddess.setMobile(rsResultSet.getString("mobile"));
            goddess.setCreateUser(rsResultSet.getString("create_user"));
            goddess.setCreateDate(rsResultSet.getDate("create_date"));
            goddess.setUpdateUser(rsResultSet.getString("update_user"));
            goddess.setUpdateDate(rsResultSet.getDate("update_date"));
            goddess.setIsDel(rsResultSet.getInt("isdel"));
        }
        return goddess;
    }

测试：DbcpTest.java

package db;

import java.sql.SQLException;

import java.util.Date;

import sql.Dao.GoddessDao;
import sql.model.Goddess;
public class DbcpTest {
    public static void main(String []args) throws Exception{
        //1、通过普通的方式操作数据库
        Date a=new Date();
        get();
        Date b=new Date();
        System.out.println(b.getTime()-a.getTime());
        
        //2、通过DBCP连接池的方式操作数据库
        Date c=new Date();
        getByDbcp();
        Date d=new Date();
        System.out.println(d.getTime()-c.getTime());
        
        //通过运行发现，第一种方式要比第二种方式花费时间要多
        
    }

    private static void get() throws SQLException {
        // TODO Auto-generated method stub
        GoddessDao dao=new GoddessDao();
        Goddess goddess=dao.get(2);
        System.out.println("普通连接："+goddess.toString());
    }
    private static void getByDbcp() throws Exception {
        // TODO Auto-generated method stub
        GoddessDao dao=new GoddessDao();
        Goddess goddess=dao.getByDbcp(2);
        System.out.println("dbcp连接："+goddess.toString());
    }
}

运行结果：

　　

 2、c3p0使用步骤

　　c3p0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。

默认情况下（即没有配置连接池的情况下），Hibernate会采用内建的连接池。但这个连接池性能不佳，因此官方也只是建议仅在开发环境下使用。Hibernate支持第三方的连接池，官方推荐的连接池是C3P0,Proxool。

　　1、导入相关jar包

　　　　c3p0-0.9.2.jar

　　　　mchange-commons-java-0.2.3.4.jar

　　2、配置根目录配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="helloC3p0">
       <property name="driverClass">com.mysql.jdbc.Driver</property>
       <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/mydb</property> 
       <property name="user">root</property>
       <property name="password">mysql123</property>
       
       <property name="acquireIncrement">50</property>
       <!-- 若数据库中连接数不足时，一次向数据库服务器申请多少个链接。 -->
       
       <property name="initialPoolSize">5</property>
       <!-- 初始化数据库连接时，连接的个数 -->
       
       <property name="minPoolSize">50</property>
       <!-- 数据库连接池中的最小的数据库连接数 -->
       <property name="maxPoolSize">1000</property>
       <!-- 数据库连接池中的最大的数据库连接数 -->
       
       <property name="maxStatements">20</property>
       <!-- c3p0数据库连接池可以使用的Statement对象的个数 -->
       <property name="maxStatementsPerConnection">5</property>
       <!-- c3p0数据库连接池每个连接可以使用的Statement对象的个数 -->
    </named-config>
</c3p0-config>

　　3、测试

实例一：C3P0Test.java

package c3p0;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3poTest {
    private static final String helloC3p0="c3p0-config.xml";
    public static void main(String []args) throws Exception{
//        ComboPooledDataSource cpds=new ComboPooledDataSource();
//        cpds.setDriverClass("com.mysql.jdbc.Driver");
//        cpds.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mydb");
//        cpds.setUser("root");
//        cpds.setPassword("mysql123");
        DataSource dataSource=new ComboPooledDataSource("helloC3p0");
        System.out.println(dataSource.getConnection());
    }
}

 运行结果：

　　

 3、连接池总结

　　

四、JDBC的替代产品(Hibernate、MyBatis)

　　上面介绍的都是手工的连接数据库，写SQL语句。这部分的替代产品会代替我们的这些工作。

替代工具：

　　　　Commons-dbutils

　　　　Hibernate;

　　　　Mybatis;

1、Commons-dbutils

　　字面的意思可以理解为：通用的数据库工具类。

　　Apache组织提供的一个开源JDBC工具类库，对传统操作数据库的类进行二次封装，可以把结果集转化为List。

特点：

杜绝了资源泄露。修正了JDBC代码并不难，但是这通常导致连接泄露并且难以跟踪到。
大段的持久化数据到数据库代码彻底精简，剩下的代码清晰地表达了编码的意图。
不需要手工从ResultSet里set值到JavaBean中，每一行数据都将会以一个Bean示例的形式出现。

 核心接口：

　　1、DbUtils：提供如关闭、装载JDBC驱动程序等常规工作的工具类；

　　2、QueryRunner:该类简化了Sql查询，它常与ResultSetHandler组合在一起。

　　3、ResultSetHandler：执行处理一个java.sql.ResultSet，将数据转变并处理为任何一种形式，这样有益于其应用而且使用起来容易。

具体的参考  ：Apache的DBUtils框架学习

2、Hibernate

具体参考： 新手上路第一个Hibernate例子。 以及  Hibernate框架学习

3、Mybatis

具体参考： mybatis实战教程

转载：JAVA数据库连接--JDBC调用存储过程，事务管理和高级应用